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