1 PACKAGE BODY AMS_ListDedupe_PVT as
2 /* $Header: amsvlddb.pls 120.4.12020000.3 2013/02/01 06:47:33 bchaturv ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_ListDedupe_PVT
7 --
8 -- PURPOSE
9 -- This package is a Private API for managing List Deduplication information in
10 -- AMS. It contains specification for pl/sql records and tables
11 --
12 -- Functions:
13 -- Exec_Sql_Stmt (see below for specification)
14 -- Filter_Word (see below for specification)
15 -- Dedupe_List (see below for specification)
16 --
17 -- Procedures:
18 -- Write_To_Act_Log (see below for specification)
19 --
20 --
21 -- NOTES
22 --
23 --
24 -- HISTORY
25 --
26 -- 06/29/1999 khung created
27 -- 07/07/1999 khung modify -> only one rule per list
28 -- 07/22/1999 khung changed package name and file name
29 -- 08/02/1999 khung add write_to_act_log funtion (8i)
30 -- 09/30/1999 khung add the capability to dedupe the entries
31 -- from AMS_IMP_SOURCE_LINE table
32 -- 11/11/1999 choang Moved Generate_Key from AMS_PartyImport_PVT.
33 -- 01/11/2000 khung Add more debug code for LIST_RULE checking
34 -- 09/27/2000 vbhandar Made changes to dedupe_list and c_dedupe_keys cursor
35 -- 10/19/2000 vbhandar Made changes to dedupe_list to fix problem with dedup not working for more than 1 rank
36 -- 11/07/2000 vbhandar Made changes to dedupe_list to synchronize enabled flag with non deduped entries
37 -- 11/15/2000 vbhandar Made changes to filter word to do case insensitive comparison
38 -- 26/06/2001 gjoby changed the selection query - For Hornet
39 -- 01/02/2013 bchaturv Removed hardcoded replace of all special characters in word replacements. They are taken care by replacement words in HZ_WORD_REPLACEMENTS table.
40 --
41 -- End of Comments
42
43 -- global constants
44 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_ListDedupe_PVT';
45 g_original_text VARCHAR2(50) := 'z!"#$%&''()*+,-./:;<=>?@[\]^_`{|}~';
46 g_replace_text VARCHAR2(50) := 'z';
47
48 TYPE original_key IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
49 TYPE replacement_key IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
50
51 g_original_key original_key;
52 g_replacement_key replacement_key;
53 g_special_enabled VARCHAR2(1);
54
55
56 /*****************************************************************************************/
57 -- Start of Comments
58 --
59 -- Name : Exec_Sql_Stmt
60 -- Type : Private
61 -- Function : This function takes a dunamic SQL stmt and executes it
62 --
63 -- Pre-reqs : None
64 -- Paramaeters :
65 -- IN :
66 -- p_stmt VARCHAR2
67 --
68 -- End Of Comments
69
70 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
71 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
72 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
73
74 FUNCTION Exec_Sql_Stmt (p_stmt VARCHAR2)
75 RETURN NUMBER
76 IS
77
78 -- l_sqlerrm VARCHAR2(1000);
79 -- l_sqlcode VARCHAR2(1000);
80 l_cursor_name INTEGER;
81 l_rows_processed INTEGER;
82 BEGIN
83
84 --executing dynamic sql statement to populate the merge_key field in ams_list_entries.
85
86 l_cursor_name := DBMS_SQL.open_cursor;
87 --insert into temp(text2) values('after open cursor');commit;
88
89 DBMS_SQL.parse (l_cursor_name, p_stmt, DBMS_SQL.native);
90 --insert into temp(text2) values('after sql parse');commit;
91
92 l_rows_processed := DBMS_SQL.execute (l_cursor_name);
93 --insert into temp(text2) values('after execute');commit;
94
95 DBMS_SQL.close_cursor (l_cursor_name);
96 --insert into temp(text2) values('after close cursor');commit;
97
98 RETURN (l_rows_processed);
99
100 END Exec_Sql_Stmt;
101
102 /*****************************************************************************************/
103 -- Start of Comments
104 --
105 -- NAME
106 -- Write_To_Act_Log
107
108 -- PURPOSE
109 -- Any log messages we write to the ams_act_logs table are commited even if
110 -- the whole process is ROLLED BACK because of a processing error.
111 -- (8i new feature -- AUTONOMOUS TRANSACTIONS)
112 --
113 -- Pre-reqs : None
114 -- Paramaeters :
115 -- IN :
116 -- p_list_header_id NUMBER
117 -- p_msg_data VARCHAR2
118 --
119 -- NOTES
120 --
121 --
122 -- HISTORY
123 -- 08/02/1999 khung created
124
125 -- End Of Comments
126
127 PROCEDURE Write_To_Act_Log
128 (p_list_header_id IN NUMBER
129 ,p_msg_data IN VARCHAR2,
130 p_level in varchar2 default 'LOW'
131 ) IS
132
133 --PRAGMA AUTONOMOUS_TRANSACTION;
134
135 BEGIN
136
137 if nvl(ams_listgeneration_pkg.g_log_level,'HIGH') = 'HIGH' and p_level = 'LOW' then
138 return;
139 end if;
140
141 ams_listgeneration_pkg.write_to_act_log(p_msg_data,'LIST',p_list_header_id,p_level);
142
143 /*
144 ams_listgeneration_pkg.g_message_table(ams_listgeneration_pkg.g_count) := p_msg_data;
145 ams_listgeneration_pkg.g_date(ams_listgeneration_pkg.g_count) := sysdate;
146 ams_listgeneration_pkg.g_count := ams_listgeneration_pkg.g_count + 1;
147 */
148
149 /* INSERT INTO ams_act_logs
150 ( activity_log_id
151 ,last_update_date
152 ,last_updated_by
153 ,creation_date
154 ,created_by
155 ,last_update_login
156 ,object_version_number
157 ,act_log_used_by_id
158 ,arc_act_log_used_by
159 ,log_transaction_id
160 ,log_message_text
161 ,log_message_level
162 ,log_message_type
163 ,description
164 )
165 VALUES
166 (
167 ams_act_logs_s.nextval,
168 sysdate,
169 fnd_global.user_id,
170 sysdate,
171 fnd_global.user_id,
172 fnd_global.conc_login_id,
173 1, -- object_version_number
174 p_list_header_id,
175 'LIST',
176 ams_act_logs_transaction_id_s.nextval,
177 p_msg_data,
178 null,
179 null,
180 null);
181
182 COMMIT; */
183
184 END Write_To_Act_Log;
185
186 /*****************************************************************************************/
187 -- Start of Comments
188 --
189 -- Name : Filter_Word
190 -- Type : Private
191 -- Function : Replaces all noise words for the relevant fields in AMS_LIST_ENTRIES
192 --
193 -- Pre-reqs : None
194 -- Paramaeters :
195 -- IN :
196 -- p_word VARCHAR2
197 -- p_substr_len AMS_LIST_RULE_FIELDS.SUBSTRING_LEN%TYPE
198 -- p_table_name AMS_LIST_RULE_FIELDS.FIELD_TABLE_NAME%TYPE
199 -- p_column_name AMS_LIST_RULE_FIELDS.FIELD_COLUMN_NAME%TYPE
200 --
201 -- End Of Comments
202
203 FUNCTION Filter_Word
204 (p_word VARCHAR2
205 ,p_substr_len AMS_LIST_RULE_FIELDS.SUBSTRING_LENGTH%TYPE
206 ,p_field_table_name AMS_LIST_RULE_FIELDS.FIELD_TABLE_NAME%TYPE
207 ,p_field_column_name AMS_LIST_RULE_FIELDS.FIELD_COLUMN_NAME%TYPE
208 )
209 RETURN VARCHAR2
210
211 IS
212 -- PL/SQL Block
213 -- this will select all noise words for the relevant field in list_entries
214 -- table and column name are the PK fields for this table.
215 CURSOR c_noise_words (my_field_table_name IN VARCHAR, my_field_column_name IN VARCHAR)
216 IS
217 SELECT w.original_word, w.replacement_word
218 FROM ams_list_word_fields w
219 WHERE w.field_table_name = my_field_table_name
220 AND w.field_column_name = my_field_column_name;
221
222 l_original_word ams_list_word_fields.original_word%TYPE;
223 l_replacement_word ams_list_word_fields.replacement_word%TYPE;
224 l_word VARCHAR2(500);
225
226 BEGIN
227 --we always compare in uppercase.
228
229 l_word := UPPER (p_word);
230
231 OPEN c_noise_words (p_field_table_name, p_field_column_name);
232 LOOP
233 FETCH c_noise_words INTO l_original_word, l_replacement_word;
234 EXIT WHEN c_noise_words%notfound;
235
236 --substituting the original with the replacement words.
237 --pv_word := replace(pv_word,pv_original_word,NVL(pv_replacement_word,'NULL'));
238 --09/28/2000 VB modified
239 --change case to UPPER before comparison because l_word was made UPPER case!!
240 IF (l_word = UPPER(l_original_word))
241 THEN
242 l_word := NVL (UPPER(l_replacement_word), 'NULL');-- modified vb 11/15/2000
243 END IF;
244 END LOOP;
245 CLOSE c_noise_words;
246
247 -- if the rule specifies to only take into account 1..p_substr_len characters
248 IF (p_substr_len IS NULL)
249 THEN
250 RETURN (l_word);
251 ELSE
252 RETURN (SUBSTR (l_word, 1, p_substr_len));
253 END IF;
254
255 END Filter_Word;
256
257 /*****************************************************************************************/
258 -- Start of Comments
259 --
260 -- Name : Dedupe_List
261 -- Type : Private
262 -- Function : Replaces all noise words for the relevant fields in AMS_LIST_ENTRIES
263 --
264 -- Pre-reqs : None
265 -- Paramaeters :
266 -- IN :
267 -- p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
268 -- p_enabled_wordreplacement_flag AMS_LIST_HEADERS_ALL.ENABLED_WORDREPLACEMENT_FLAG%TYPE
269 -- p_send_to_log VARCHAR2 := 'N'
270 -- p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
271 --
272 -- HISTORY
273 -- 08/02/1999 khung created
274 -- 09/30/1999 khung add the capability to dedupe the entries
275 -- from AMS_IMP_SOURCE_LINE table
276 -- End Of Comments
277
278 FUNCTION Dedupe_List
279 (p_list_header_id AMS_LIST_HEADERS_ALL.LIST_HEADER_ID%TYPE
280 ,p_enable_word_replacement_flag
281 AMS_LIST_HEADERS_ALL.ENABLE_WORD_REPLACEMENT_FLAG%TYPE
282 ,p_send_to_log VARCHAR2 := 'N'
283 ,p_object_name VARCHAR2 := 'AMS_LIST_ENTRIES'
284 )
285 RETURN NUMBER IS
286 -- the set of rules associated with a list.
287 CURSOR c_list_rules (my_list_header_id IN NUMBER)
288 IS SELECT list_rule_id
289 FROM ams_list_rule_usages
290 WHERE list_header_id = my_list_header_id
291 ORDER BY priority;
292
293 -- the list of fields for the list rule which are used to generate the key.
294 CURSOR c_rule_fields
295 (my_list_rule_id IN
296 ams_list_rules_all.list_rule_id%TYPE)
297 IS
298 SELECT field_table_name,
299 field_column_name,
300 substring_length,
301 word_replacement_code
302 FROM ams_list_rule_fields
303 WHERE list_rule_id = my_list_rule_id;
304
305 -- perform a check to see if this list has been deduped already.
306 CURSOR c_deduped_before (my_list_header_id IN NUMBER)
307 IS
308 SELECT last_deduped_by_user_id
309 FROM ams_list_headers_all
310 WHERE list_header_id = my_list_header_id;
311
312 -- get a distinct list of merge keys for the list and a
313 -- count of the occurance of each key
314 -- we also exclude any records where the dedupe flag is already set.
315 CURSOR c_dedupe_keys (my_list_header_id IN NUMBER)
316 IS
317 SELECT DISTINCT dedupe_key, COUNT (dedupe_key)
318 FROM ams_list_entries
319 WHERE list_header_id = my_list_header_id
320 -- AND marked_as_duplicate_flag IS NULL --commented by VB
321 -- 09/30/2000 because this is a not null column in database
322 GROUP BY dedupe_key;
323
324
325 l_sql_stmt1 VARCHAR2(10000);
326 l_sql_stmt2 VARCHAR2(10000);
327
328 --l_sqlerrm VARCHAR2(1000);
329 --l_sqlcode VARCHAR2(1000);
330 l_fields VARCHAR2(10000);
331 --l_temp_fields VARCHAR2(10000);
332 l_no_of_masters NUMBER := 0;
333 l_list_rule_id ams_list_rules_all.list_rule_id%TYPE;
334 --l_list_entry_id ams_list_entries.list_entry_id%TYPE;
335 l_last_dedupe_by ams_list_headers_all.last_deduped_by_user_id%TYPE;
336 l_dedupe_key ams_list_entries.dedupe_key%TYPE;
337 l_dedupe_key_count NUMBER;
338 l_rank_count NUMBER;
339 i BINARY_INTEGER := 1;
340
341 TYPE rule_details
342 IS TABLE OF c_rule_fields%ROWTYPE
343 INDEX BY BINARY_INTEGER;
344
345 list_rules rule_details;
346 empty_list_rules rule_details;
347 l_no_of_duplicates number := 0;
348 BEGIN
349
350 IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
351 l_sql_stmt1 := 'update ams_list_entries set dedupe_key = ';
352 ELSIF (p_object_name = 'AMS_IMP_SOURCE_LINES') THEN
353 l_sql_stmt1 := 'update ams_imp_source_lines set dedupe_key = ';
354 ELSE
355 RETURN 0;
356 END IF;
357
358 --performing check to see if this list has been deduped before.
359 OPEN c_deduped_before (p_list_header_id);
360 FETCH c_deduped_before INTO l_last_dedupe_by;
361 CLOSE c_deduped_before;
362
363 IF (p_send_to_log = 'Y') THEN
364 Write_To_Act_Log (p_list_header_id,
365 'Executing dedupe_list procedure' ,'LOW' );
366 IF (p_enable_word_replacement_flag = 'Y') THEN
367 Write_To_Act_Log (p_list_header_id,'Enable word replacement flag has been set','LOW' );
368 END IF;
369 COMMIT;
370
371 END IF;
372
373 -- we must ensure that this flag gets reset to NULL for the list to
374 -- ensure accurate results.
375 -- if a dedupe has never been perfomed then this field will contains
376 -- NULLS and there is no
377 -- need to perform this update
378 IF (l_last_dedupe_by IS NOT NULL) THEN
379 UPDATE ams_list_entries
380 SET dedupe_key = NULL
381 -- ,marked_as_duplicate_flag = NULL
382 -- because column is not null in database
383 WHERE list_header_id = p_list_header_id;
384
385 IF (p_send_to_log = 'Y') THEN
386 Write_To_Act_Log (p_list_header_id,'Dedupe already done. Dedupe key reset to NULL.','LOW') ;
387 END IF;
388
389 END IF;
390
391 -- checking to see if there are any List Source Ranks associated
392 -- with the List.
393 SELECT COUNT (rank)
394 INTO l_rank_count
395 FROM ams_list_select_actions
396 WHERE action_used_by_id = p_list_header_id
397 and arc_action_used_by = 'LIST';
398
399 IF (p_send_to_log = 'Y')
400 THEN
401 Write_To_Act_Log (p_list_header_id,'No of Ranks for this list = ' ||TO_CHAR (l_rank_count), 'LOW' );
402 END IF;
403
404 --getting the list rules for the list.
405 OPEN c_list_rules (p_list_header_id);
406 LOOP
407 FETCH c_list_rules INTO l_list_rule_id;
408
409 IF (p_send_to_log = 'Y') THEN
410 Write_To_Act_Log (p_list_header_id,
411 'List rule id = ' ||TO_CHAR (l_list_rule_id),'LOW');
412 END IF;
413
414 IF (c_list_rules%notfound) THEN
415 -- DBMS_OUTPUT.PUT_LINE('no list rule provided.');
416 IF (p_send_to_log = 'Y')
417 THEN
418 Write_To_Act_Log (p_list_header_id,'No more list rule associated with the list' ,'LOW' );
419 END IF;
420
421 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
422 THEN
423 FND_MESSAGE.set_name('AMS', 'AMS_LIST_NO_LIST_RULE');
424 FND_MSG_PUB.add;
425 END IF;
426
427 CLOSE c_list_rules;
428 RETURN 0;
429 END IF;
430
431
432 IF (c_list_rules%rowcount > 1) THEN
436 -- removed khung 07/07/1999
433 --we have more than one rule for this list
434 --we must ensure that the key gets reset to NULL for the list to
435 -- ensure accurate results.
437 IF (p_object_name = 'AMS_LIST_ENTRIES') THEN
438 UPDATE ams_list_entries
439 SET dedupe_key = NULL
440 WHERE list_header_id = p_list_header_id
441 AND marked_as_duplicate_flag IS NULL;
442 COMMIT;
443 END IF;
444
445 IF (p_send_to_log = 'Y') THEN
446 Write_To_Act_Log (p_list_header_id,'Only one rule can be associated with the list','LOW' );
447 END IF;
448
449 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
450 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ONLY_ONE_LIST_RULE');
451 FND_MSG_PUB.add;
452 END IF;
453
454 CLOSE c_list_rules;
455 RETURN 0;
456 END IF; -- End of if for more than 1 rule count
457
458 Write_To_Act_Log (p_list_header_id, 'starting rule attributes','LOW' );
459 --fetch the rule entries associated with this list.
460 OPEN c_rule_fields (l_list_rule_id);
461 LOOP
462 FETCH c_rule_fields INTO
463 list_rules (i).field_table_name,
464 list_rules (i).field_column_name,
465 list_rules (i).substring_length,
466 list_rules (i).word_replacement_code;
467 EXIT WHEN c_rule_fields%notfound;
468
469
470 -- if the enable word replacement flag is set we construct the sql
471 -- to call the filter word function.
472 IF (p_enable_word_replacement_flag = 'Y') THEN
473 Write_To_Act_Log (p_list_header_id, 'Calling replace word procedure','LOW' );
474 l_fields :=
475 l_fields ||
476 'AMS_ListDedupe_PVT.replace_word(' ||
477 upper(list_rules (i).field_column_name) ||
478 ',' ||
479 '''' ||
480 list_rules (i).word_replacement_code||
481 '''' ||
482 ')' ||
483 '||' ||
484 '''' ||
485 '.' ||
486 '''' ||
487 '||';
488
489 ELSE
490 --no substr specified for the rule field.
491 IF (list_rules (i).substring_length IS NULL)
492 THEN
493 l_fields :=
494 l_fields ||
495 'upper(' ||
496 list_rules (i).field_column_name ||
497 ')||' ||
498 '''' ||
499 '.' ||
500 '''' ||
501 '||';
502 ELSE
503 l_fields :=
504 l_fields ||
505 'upper(substr(' ||
506 list_rules (i).field_column_name ||
507 ',1,' ||
508 TO_CHAR (list_rules (i).substring_length) ||
509 '))||' ||
510 '''' ||
511 '.' ||
512 '''' ||
513 '||';
514 END IF;
515 END IF;
516
517 i := i + 1;
518
519 END LOOP; --c_rule_fields
520
521 i := 1; --reseting to one.
522 list_rules := empty_list_rules; --re-initializing because we can have many rules.
523 if c_rule_fields%rowcount = 0 then
524 write_to_act_log(p_list_header_id, 'No attribute defined for the dedupe rule attached to this list/tg. Aborting deduplication process','HIGH');
525 return -1;
526 end if;
527 CLOSE c_rule_fields;
528
529
530 -- removing the last '.' from the string as this will cause an invalid syntax error
531 -- in the query.
532
533 l_fields := SUBSTR (l_fields, 1, LENGTH (l_fields) - 7);
534
535 --constucting the valid sql to be executed.
536 -- 08/02/99 khung, modified using Native Dynamic SQL
537
538 -- removed by khung 09/30/1999 to support AMS_IMP_SOURCE_LINES
539 --l_sql_stmt1 := 'update ams_list_entries set dedupe_key =';
540 l_sql_stmt2 := l_sql_stmt1;
541 l_sql_stmt2 := l_sql_stmt2 ||
542 l_fields ||
543 ' where list_header_id =' ||
544 TO_CHAR (p_list_header_id);
545
546
547 IF (p_send_to_log = 'Y')
548 THEN
549 Write_To_Act_Log (p_list_header_id,'SQL to generate Merge Keys = ' ||l_sql_stmt2,'LOW');
550 END IF;
551
552 -- executing the sql to generate the dedupe_key which has also been filtered.
553
554 -- l_no_of_masters := exec_sql_stmt (l_sql_stmt2);
555
556 -- 08/02/99 khung, modified using Native Dynamic SQL
557 --EXECUTE IMMEDIATE l_sql_stmt2
558 -- INTO l_no_of_masters;
559
560 --09/27/2000 vbhandar,modified Execute immediate , INTO should only be used for single row queries
561 --notice that l_no_of_masters is not really used, left it there to keep the signature of the function Dedupe_LIST unchanged.
562 EXECUTE IMMEDIATE l_sql_stmt2;
563
564 COMMIT;
565
566 -- getting a distinct set of merge keys for this list and the count of each occurance of the key.
567 -- if there is only one key then we know that there are no duplicates for this key as defined by
568 -- the rule.
572 OPEN c_dedupe_keys (p_list_header_id);
569 -- if there is more than one occurance then we need to choose one of the candidates as the master
570 -- and flag the rest as duplicates, we use the RANK for the List Sources to choose a Master
571 -- Entries, if no Ranks Exist then we arbitrarily choose a Master.
573 LOOP
574 FETCH c_dedupe_keys INTO l_dedupe_key, l_dedupe_key_count;
575 EXIT WHEN c_dedupe_keys%notfound;
576
577 --there are duplicates, we must choose the master entry and flag the rest as duplicates.
578 IF (l_dedupe_key_count > 1)
579 THEN
580
581 IF (l_rank_count = 0)
582 THEN -- there are no ranks assoociated with the list source so we can choose at random.
583
584 UPDATE ams_list_entries
585 SET marked_as_duplicate_flag = 'Y',
586 enabled_flag='N' --modified vb 11/7/2000
587 WHERE list_header_id = p_list_header_id
588 AND dedupe_key = l_dedupe_key
589 AND list_entry_id <> ( SELECT MIN (c.list_entry_id)
590 FROM ams_list_entries c
591 WHERE c.list_header_id = p_list_header_id
592 AND c.dedupe_key = l_dedupe_key
593 AND enabled_flag='Y' -- added by hbandi for the BUG #8358168
594 );
595 ELSE --there are ranks associated with at least one list source.
596 --modified vb 10/19/2000 to fix bug where dedup not working with more than 1 rank
597 UPDATE ams_list_entries
598 SET marked_as_duplicate_flag = 'Y',enabled_flag='N'--modified vb 11/7/2000
599 WHERE list_header_id = p_list_header_id
600 AND dedupe_key = l_dedupe_key
601 AND list_entry_id <> ( SELECT MIN (c.list_entry_id)
602 --selecting the master.
603 FROM ams_list_entries c,
604 ams_list_select_actions a
605 WHERE c.list_header_id =
606 p_list_header_id
607 AND c.dedupe_key = l_dedupe_key
608 AND enabled_flag='Y' -- added by hbandi for the BUG #8605416
609 AND c.list_select_action_id = a.list_select_action_id);
610 /*
611 AND a.rank =(SELECT min(b.rank)
612 FROM ams_list_select_actions b
613 WHERE b.action_used_by_id = p_list_header_id
614 and b.arc_action_used_by = 'LIST')
615 GROUP BY a.rank); */
616 --even if there are no ranks for this sql stmt we will still
617 --return one row.
618
619 END IF;
620 END IF;
621 END LOOP; --c_dedupe_keys loop
622 CLOSE c_dedupe_keys;
623 --initializing as we may have more than one rule.
624
625 l_fields := NULL;
626 l_sql_stmt2 := l_sql_stmt1;
627
628 IF (p_send_to_log = 'Y') THEN
629 write_to_act_log(p_list_header_id, 'Duplicates identified and marked for this rule','HIGH');
630 END IF;
631
632 END LOOP; --c_list_rules loop
633
634 /* IF (p_send_to_log = 'Y')
635 THEN
636 Write_To_Act_Log (p_list_header_id,
637 'All the rules are applied' ||
638 TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MM:SS') );
639 END IF;*/
640
641 --recording who performed the deduplication and at what time.
642 UPDATE ams_list_headers_all
643 SET last_deduped_by_user_id = FND_GLOBAL.User_Id
644 ,last_dedupe_date = SYSDATE
645 WHERE list_header_id = p_list_header_id;
646
647 /* IF (p_send_to_log = 'Y')
648 THEN
649 Write_To_Act_Log (p_list_header_id,
650 'DEDUPE LIST: RECORDING WHO DEUPED THE LIST AND THE DATE' ||
651 TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MM:SS') );
652 END IF;*/
653
654 --recording the number of duplicates found.
655 UPDATE ams_list_headers_all
656 SET no_of_rows_duplicates = (
657 SELECT COUNT (*)
658 FROM ams_list_entries
659 WHERE list_header_id = p_list_header_id
660 AND marked_as_duplicate_flag = 'Y')
661 WHERE list_header_id = p_list_header_id
662 returning no_of_rows_duplicates into l_no_of_duplicates;
663
664 IF (p_send_to_log = 'Y')
665 THEN
666 Write_To_Act_Log (p_list_header_id,'No of duplicates found for this list is ' ||l_no_of_duplicates,'HIGH');
667 END IF;
668
669 COMMIT;
670
671 RETURN (l_no_of_masters);
672
673 END Dedupe_List;
674
675
676 --------------------------------------------------------------------
677 -- PROCEDURE
678 -- Generate_Key
679 -- HISTORY
680 -- 10-Nov-1999 choang Created.
681 -- 11-Nov-1999 choang Moved to AMS_ListDedupe_PVT.
682 --------------------------------------------------------------------
683 PROCEDURE Generate_Key (
684 p_api_version IN NUMBER,
685 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
686 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
687
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_count OUT NOCOPY NUMBER,
690 x_msg_data OUT NOCOPY VARCHAR2,
691
692 p_list_rule_id IN NUMBER,
696 x_dedupe_key OUT NOCOPY VARCHAR2
693 p_sys_object_id IN NUMBER,
694 p_sys_object_id_field IN VARCHAR2,
695 p_word_replacement_flag IN VARCHAR2,
697 )
698 IS
699 L_KEY_LENGTH CONSTANT NUMBER := 500;
700 L_API_VERSION CONSTANT NUMBER := 1.0;
701 L_API_NAME CONSTANT VARCHAR2(30) := 'Generate_Key';
702 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
703
704 l_dedupe_key VARCHAR2(4000);
705 l_field_column_name VARCHAR2(30);
706 l_field_table_name VARCHAR2(30);
707 l_substring_length NUMBER;
708 --
709 -- The following 3 variables used to construct
710 -- a SQL statement which has the format:
711 -- SELECT word1, word2, etc.
712 -- FROM field_table_name
713 -- WHERE id = p_sys_object_id
714 -- This statement is used to retrive the
715 -- dedupe key for the specified record.
716 l_select_sql VARCHAR2(4000);
717 l_from_sql VARCHAR2(4000);
718 l_where_sql VARCHAR2(4000);
719
720 l_return_status VARCHAR2(1);
721
722 --
723 -- LIST_RULE_FIELDS defines the tables and columns involved
724 -- in generating a key. The key can also be a substring of
725 -- the specified column.
726 -- NOTE: Only substring is needed; we do not need to pad
727 -- the data if it is shorter than the specified substring
728 -- length.
729 CURSOR c_fields IS
730 SELECT field_table_name,
731 field_column_name,
732 substring_length
733 FROM ams_list_rule_fields
734 WHERE list_rule_id = p_list_rule_id;
735 BEGIN
736 --------------------- initialize -----------------------
737 IF (AMS_DEBUG_HIGH_ON) THEN
738
739 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
740 END IF;
741
742 IF FND_API.to_boolean (p_init_msg_list) THEN
743 -- Clear out the message buffer.
744 FND_MSG_PUB.initialize;
745 END IF;
746
747 IF NOT FND_API.compatible_api_call (
748 l_api_version,
749 p_api_version,
750 l_api_name,
751 g_pkg_name
752 ) THEN
753 RAISE FND_API.g_exc_unexpected_error;
754 END IF;
755
756 x_return_status := FND_API.g_ret_sts_success;
757
758 ----------------------- generate -----------------------
759 IF (AMS_DEBUG_HIGH_ON) THEN
760
761 AMS_Utility_PVT.debug_message (l_full_name || ': Generate');
762 END IF;
763
764 OPEN c_fields;
765 LOOP
766 FETCH c_fields INTO l_field_table_name, l_field_column_name, l_substring_length;
767 IF (c_fields%ROWCOUNT = 0) THEN
768 CLOSE c_fields;
769 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
770 FND_MESSAGE.set_name ('AMS', 'AMS_LIST_BAD_DEDUPE_RULE');
771 FND_MSG_PUB.add;
772 END IF;
773 RAISE FND_API.g_exc_error;
774 END IF;
775 EXIT WHEN c_fields%NOTFOUND;
776
777 -- if the enable word replacement flag is set we construct the sql
778 -- to call the filter word function.
779 IF (p_word_replacement_flag = 'Y') THEN
780 --
781 -- Construct select SQL clause in the format:
782 -- SELECT AMS_ListDedupe_PVT.filter_word ( the_word, the_length, the_table, the_column )
783 -- The final result of the query will look like: A.B.C.D
784 --
785 l_select_sql :=
786 l_select_sql ||
787 'AMS_ListDedupe_PVT.filter_word (' ||
788 l_field_column_name ||
789 ',' ||
790 NVL (TO_CHAR (l_substring_length), 'NULL') ||
791 ',' ||
792 '''' ||
793 l_field_table_name ||
794 '''' ||
795 ',' ||
796 '''' ||
797 l_field_column_name ||
798 '''' ||
799 ')' ||
800 '||' ||
801 '''' ||
802 '.' ||
803 '''' ||
804 '||';
805 ELSE
806 --no substr specified for the rule field.
807 IF (NVL (l_substring_length, 0) = 0) THEN
808 l_select_sql :=
809 l_select_sql ||
810 'UPPER (' ||
811 l_field_column_name ||
812 ')||' ||
813 '''' ||
814 '.' ||
815 '''' ||
816 '||';
817 ELSE
818 l_select_sql :=
819 l_select_sql ||
820 'UPPER(SUBSTR(' ||
821 l_field_column_name ||
822 ',1,' ||
823 TO_CHAR (l_substring_length) ||
824 '))||' ||
825 '''' ||
826 '.' ||
827 '''' ||
828 '||';
829 END IF;
830 END IF;
831
832 /***
833 IF p_word_replacement_flag = 'Y' THEN
834 l_select_sql := l_select_sql ||
835 'AMS_ListDedupe_PVT.filter_word (' || l_field_column_name || ', '
836 || l_substring_length || ', '
837 || l_field_table_name || ', '
838 || '''' || l_field_column_name || '''),'
839 ELSE
840 END IF;
841 ***/
842
843 END LOOP;
844 CLOSE c_fields;
845
846 -- removing the last '.' from the string as this will cause an invalid syntax error
850 l_from_sql := 'FROM ' || l_field_table_name || ' ';
847 -- in the query.
848 l_select_sql := 'SELECT ' || SUBSTR (l_select_sql, 1, LENGTH (l_select_sql) - 7) || ' ';
849
851
852 l_where_sql := 'WHERE ' || p_sys_object_id_field || ' = :p_sys_object_id';
853
854 EXECUTE IMMEDIATE l_select_sql || l_from_sql || l_where_sql
855 INTO l_dedupe_key
856 USING p_sys_object_id
857 ;
858 -------------------- finish --------------------------
859 --
860 -- Set the out variable.
861 -- The returned key may be of greater length than
862 -- the allowable key length.
863 x_dedupe_key := SUBSTR (l_dedupe_key, 1, L_KEY_LENGTH);
864
865 FND_MSG_PUB.count_and_get (
866 p_encoded => FND_API.g_false,
867 p_count => x_msg_count,
868 p_data => x_msg_data
869 );
870
871 IF (AMS_DEBUG_HIGH_ON) THEN
872
873
874
875 AMS_Utility_PVT.debug_message (l_full_name || ': End');
876
877 END IF;
878
879 EXCEPTION
880 WHEN FND_API.g_exc_error THEN
881 x_return_status := FND_API.g_ret_sts_error;
882 FND_MSG_PUB.count_and_get (
883 p_encoded => FND_API.g_false,
884 p_count => x_msg_count,
885 p_data => x_msg_data
886 );
887 WHEN FND_API.g_exc_unexpected_error THEN
888 x_return_status := FND_API.g_ret_sts_unexp_error ;
889 FND_MSG_PUB.count_and_get (
890 p_encoded => FND_API.g_false,
891 p_count => x_msg_count,
892 p_data => x_msg_data
893 );
894 WHEN OTHERS THEN
895 x_return_status := FND_API.g_ret_sts_unexp_error;
896 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
897 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
898 END IF;
899
900 FND_MSG_PUB.count_and_get (
901 p_encoded => FND_API.g_false,
902 p_count => x_msg_count,
903 p_data => x_msg_data
904 );
905 END Generate_Key;
906
907 FUNCTION Replace_Word(p_word VARCHAR2,
908 p_replacement_type VARCHAR2)
909 RETURN VARCHAR2
910 IS
911 l_source_text VARCHAR2(2000);
912 l_text_length NUMBER;
913 l_current_word VARCHAR2(2000);
914 l_key VARCHAR2(2000);
915 l_old_word VARCHAR2(2000);
916 l_count NUMBER := 0;
917
918 CURSOR C_Word_Rep (x_current_word VARCHAR2,
919 x_replacement_type VARCHAR2) IS
920 SELECT upper(replacement_word)
921 FROM hz_word_replacements
922 WHERE upper(original_word) = x_current_word
923 AND type = x_replacement_type;
924
925 CURSOR c_key IS
926 SELECT ORIGINAL_WORD,
927 REPLACEMENT_WORD
928 FROM HZ_WORD_REPLACEMENTS
929 WHERE TYPE = 'KEY';
930
931 BEGIN
932
933 -- Steps mentioned here are in the context of complete fuzzy key
934 -- generation process. (Step 1 is in Generate_Key)
935 -- Step 2.
936 -- We need to remove 'S so that WILLIAM'S becomes WILLIAM and
937 -- it can become BILL if there is a replacement rule from
938 -- original word WILLIAM to replacement word BILL
939 l_source_text := replace(p_word, '''S ', ' ');
940
941 -- Step 3.
942 -- We need to remove any punctuation characters etc.
943 -- For example, this will make 134/3, 134-3 etc mapped to 1343 in key for address.
944 -- Changes for bug 16210118, hardcoded replace of all special characters restrict modifying Key modfiers
945 -- l_source_text := ltrim(translate(l_source_text, g_original_text, g_replace_text));
946
947 -- Step 3.5.
948 -- This step is for removal of special characters.
949 -- The special characters will only be replaced if user has
950 -- has set up Key Modifiers rules.
951 -- This will replace any number of characters to any number of characters mapping for
952 -- many european language. See bug 1868161 for detail.
953
954 IF g_special_enabled IS NULL THEN
955 OPEN c_key;
956 FETCH c_key INTO g_original_key(l_count), g_replacement_key(l_count);
957 IF c_key%NOTFOUND THEN
958 g_special_enabled := 'N';
959 ELSE
960 g_special_enabled := 'Y';
961 END IF;
962
963 WHILE c_key%FOUND LOOP
964 l_count := l_count + 1;
965 FETCH c_key INTO g_original_key(l_count), g_replacement_key(l_count);
966 END LOOP;
967 CLOSE c_key;
968 END IF;
969 IF g_special_enabled = 'Y' THEN
970 FOR i IN g_original_key.FIRST..g_original_key.LAST LOOP
971 l_source_text := REPLACE(l_source_text, g_original_key(i), g_replacement_key(i));
972 END LOOP;
973 END IF;
974
975 -- Step 4.
976 -- We need to continue further processing on each word if a group
977 -- of words is the input parameter.
978 -- For example INTERNATIONAL BUSINESS MACHINES should have rules
979 -- applied to each word (INTERNATIONAL, BUSINESS, MACHINES) individually.
980 -- Append a blank space on the end of the text so that the loop can
981 -- always end with the last word.
982 l_source_text := l_source_text || ' ';
983 LOOP
984 l_text_length := NVL(length(l_source_text),0);
985 IF l_text_length = 0
986 THEN
987 EXIT;
988 END IF;
989 FOR i IN 1..l_text_length LOOP
990 IF substr(l_source_text,i,1) = ' '
991 THEN
992 l_current_word := substr(l_source_text,0,i-1);
993 l_old_word := l_current_word;
997 --
994 -- Fetch the replacement word for the current word.
995 -- If no replacement word is found, then use the original
996 -- word
998 -- Step 5.
999 -- Search a replacement word for the original word.
1000 -- For example WILLIAM will be replaced by BILL if there is such rule.
1001 -- If a replacement found, substitute the original word by it
1002 OPEN C_Word_Rep(l_current_word, p_replacement_type);
1003 FETCH C_Word_Rep INTO l_current_word;
1004 IF (C_Word_Rep%NOTFOUND)
1005 THEN
1006 l_current_word := l_old_word;
1007 END IF;
1008 CLOSE C_Word_Rep;
1009
1010 -- Step 7.
1011 -- If profile for cleansing is set, then cleanse the word.
1012 -- Cleanse converts double letters to single letter, removes
1013 -- vowels inside a word.
1014 -- For example : UNIVERSAL - UNVRSL, LITTLE - LTL etc.
1015 if fnd_profile.value('HZ_CLEANSE_FUZZY_KEY') = 'Y' then
1016 l_current_word := hz_common_pub.cleanse(l_current_word);
1017 end if;
1018
1019 -- Step 8.
1020 -- Build the key in a local variable
1021 -- This removes the white spaces
1022 l_key := l_key || l_current_word;
1023 l_source_text := substr(l_source_text,i);
1024 l_source_text := ltrim(l_source_text);
1025 EXIT;
1026 END IF;
1027 END LOOP;
1028 END LOOP;
1029 RETURN l_key;
1030 END Replace_Word;
1031
1032
1033 END AMS_ListDedupe_PVT;
[Home] [Help]