1 PACKAGE BODY HZ_DQM_DUP_ID_PKG AS
2 /* $Header: ARHDUPIB.pls 120.28 2006/07/26 09:04:08 rarajend noship $ */
3 /*=======================================================================+
4 | Copyright (c) 2003 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | NAME
8 | HZ_DQM_DUP_ID_PKG
9 |
10 | DESCRIPTION
11 | The new system dup identification package, that would identify duplicates, using
12 | B-tree indices.
13 |
14 |
15 | PUBLIC PROCEDURES
16 |
17 | HISTORY
18 | 13-MAY-2003 : VJN Created
19 | 7-JUL-2005 Ramesh Ch Bug No: 4244529.Modified insert stmts to denormalize dup_batch_id
20 | into hz_dup_set_parties.
21 | 18-OCT-2005 Ravi Epuri : Bug No: 4669400. Modified the 2 instances of OPEN pt_cur CURSOR, in tca_dup_id_worker
22 | Procedure, to make sure it does not consider Merged and Inactive Parties for duplicate
23 | identification, by adding the filter condition 'Status = A', in the where clause.
24 | 12-JUL-2006 : Raj Bug 5393826: Made changed to procedure report_int_dup_party_osrs.
25 | Deleting the records from hz_imp_int_dedup_results for which
26 | no records exists in import party interface table.
27 | 18-JUL-2006 : Raj Bug 5393863: Made changes to tca_dup_id_worker procedure.
28 | Instead of opening a cursor on hz_parties,inserted all the parties in to
29 | HZ_MATCHED_PARTIES_GT table and then opened a cursor on HZ_MATCHED_PARTIES_GT table.
30 *=======================================================================*/
31 --check
32 -- Definitions:
33 -- A pair of parties (a,b) denotes two party ids a, b such that
34 -- b is a duplicate of a, subject to a given match rule.
35 -- a will be called the source party and b will be called the duplicate party.
36 -- Given 2 pairs of duplicate parties (a, b) and (c,d), we define the following terms:
37 -- Identical pairs: a = c and b =d ex: (1,2) and (1,2)
38 -- Reversed pairs: a = d and b = c ex: (1,2) and (2,1)
39 -- Transitive pairs: b = c ex: (1,2) and (2,3)
40 -- Indirect Transitives: (a,b) (c,b)
41 -- Direct Transitives: (a,b) (b,c)
42 -- Pre Union Phase : The phase before or during the query that does the union of entities.
43 -- Post Union Phase : The phase after we do the union of entities.
44 -- Trivial Dup Set: If a party "a" is found as a duplicate of a party "b" adn there are no other duplicates
45 -- for either a or b, then the dup set { a, b} will be called a trivial dup set.
46 -- These will be of cardinality 2 and they don't need any transitive derivations.
47 -- Non Trivial Dup Set: These are the ones that have cardinality > 3. For example, let us say, we find the following:
48 -- 1 duplicate of 2
49 -- 2 duplicate of 3
50 -- 2 duplicate of 6
51 -- 6 duplicate of 5
52 -- 6 duplicate of 7
53 -- 6 duplicate of 8
54 -- 8 duplicate of 12
55 -- 8 duplicate of 13
56 -- 13 duplicate of 1
57 -- 7 duplicate of 0
58 -- The dup set would be { 1,2,3,5,6,7,8,12,13,0 } after transitive derivations.
59
60
61
62 ------------------------------------------------------------------------------------
63 ------------------------------------------------------------------------------------
64 -- TCA DUPLICATE IDENTIFICATION
65 ------------------------------------------------------------------------------------
66 ------------------------------------------------------------------------------------
67
68
69 /**
70 * PROCEDURE update_hz_dup_results
71 *
72 * DESCRIPTION
73 *
74 *
75 *
76 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
77 *
78 *
79 * ARGUMENTS
80 *
81 *
82 * NOTES
83 *
84 * MODIFICATION HISTORY
85 *
86 * 04-09-2003 Colathur Vijayan o Created.
87 *
88 */
89
90 --------------------------------------------------------------------------------------
91 -- update_hz_dup_results ::: This is a generic procedure, that would do a bulk update
92 -- of hz_dup_results, using a passed in open cursor
93 --------------------------------------------------------------------------------------
94
95
96 PROCEDURE update_hz_dup_results (
97 p_cur IN EntityCur )
98 is
99 l_limit NUMBER := 200;
100 l_last_fetch BOOLEAN := FALSE;
101 H_FID NumberList;
102 H_TID NumberList ;
103 H_SCORE NumberList ;
104
105 BEGIN
106
107 -- LOOP THROUGH THE PASSED IN OPEN CURSOR
108 LOOP
109 FETCH p_cur BULK COLLECT INTO
110 H_FID
111 , H_TID
112 ,H_SCORE
113 LIMIT l_limit;
114
115 IF p_cur%NOTFOUND THEN
116 l_last_fetch:=TRUE;
117 END IF;
118
122
119 IF H_FID.COUNT = 0 and l_last_fetch THEN
120 EXIT;
121 END IF;
123 BEGIN
124
125 FORALL I in H_FID.FIRST..H_FID.LAST
126 UPDATE HZ_DUP_RESULTS A
127 SET A.SCORE = A.SCORE + H_SCORE(I)
128 WHERE
129 ( A.FID = H_FID(I) and
130 A.TID = H_TID(I)
131 );
132 END;
133
134 IF l_last_fetch THEN
135 EXIT;
136
137 END IF;
138 END LOOP;
139 ---------- exception block ---------------
140 EXCEPTION
141 WHEN OTHERS THEN
142 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
143 FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_DUP_RESULTS');
144 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
145 FND_MSG_PUB.ADD;
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END;
148
149
150
151 PROCEDURE sanitize_tca_dup_parties (
152 p_threshold IN NUMBER,
153 p_auto_merge_threshold IN NUMBER,
154 p_subset_sql IN VARCHAR2,
155 p_within_subset IN VARCHAR2
156 )
157 IS
158 p_count number ;
159 BEGIN
160
161 select count(1) into p_count
162 from hz_dup_results;
163
164 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
165 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before sanitization '|| p_count );
166 FND_FILE.put_line(FND_FILE.log,'Parties ::: Delete based on subset sql');
167 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of delete '||to_char(sysdate,'hh24:mi:ss'));
168
169
170
171
172 ----------------------------------------------------------------------------------------------
173 -- 1. USE THE SUBSET SQL AND REMOVE ROWS THAT DO NOT QUALIFY.
174 -- 2. APPLY THE THRESHOLD AND REMOVE ROWS THAT DON'T SATISFY THRESHOLD.
175 -- 3. REMOVE APPROPRIATE ROWS TO MAKE SURE THAT THERE ARE NO REVERSED PAIRS.
176 -- 4. REMOVE APPROPRIATE ROWS TO MAKE SURE THAT THERE ARE NO INDIRECT TRANSITIVES.
177 ----------------------------------------------------------------------------------------------
178
179 -- The first filter will be on the basis of the subset defined
180 -- Filter only if the flag is 'Y' and the subset sql is not null
181
182 IF p_within_subset = 'Y' and p_subset_sql is not null
183 THEN
184 EXECUTE IMMEDIATE 'delete from hz_dup_results a where ' ||
185 'not exists ' ||
186 '(Select 1 from hz_dup_results b, hz_parties parties ' ||
187 'where b.ord_tid = parties.party_id ' ||
188 'and ' ||
189 p_subset_sql || ')' ;
190 END IF;
191
192 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
193 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);
194 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of delete '||to_char(sysdate,'hh24:mi:ss'));
195
196 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
197 FND_FILE.put_line(FND_FILE.log,'Parties ::: Beginning delete on HZ_DUP_RESULTS, based on threshold, reversed pairs and indirect transitives '||SQL%ROWCOUNT);
198
199
200 -- At the end of this, we would have the following:
201
202 -- Only rows with score >= threshold.
203 -- The pair with fid < tid, if both the pairs have the same score.
204 -- The pair with the higher score, if in case, the pairs of different scores.
205
206
207 delete from hz_dup_results a
208 where
209 -- delete anything less than the threshold
210 a.score < p_threshold
211 or
212 -- if scores are same, delete the one with highest source
213 -- or if scores are different, delete the one with lower score
214 (exists
215 (Select 1 from hz_dup_results b
216 where
217
218 (
219 (
220 -- APPLY THE ABOVE PRINCIPLE TO REVERSED PAIRS
221 a.fid=b.tid and b.fid=a.tid and
222 ( (a.score = b.score and a.fid > b.fid) or (a.score < b.score) )
223 )
224
225 or
226 -- APPLY THE ABOVE PRINCIPLE TO INDIRECT TRANSITIVES
227 ( a.ord_tid=b.ord_tid and ((a.score = b.score and a.ord_fid > b.ord_fid) or (a.score < b.score)) )
228
229 )
230 ));
231
232 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
233 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);
234 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of delete '||to_char(sysdate,'hh24:mi:ss'));
235
236
237
238
239 ---------- exception block ---------------
240 EXCEPTION
241 WHEN OTHERS THEN
242 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
243 FND_MESSAGE.SET_TOKEN('PROC','SANITIZE_TCA_DUP_PARTIES');
244 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
245 FND_MSG_PUB.ADD;
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247
248 END;
249
250
251 PROCEDURE update_hz_int_dup_results (
252 p_batch_id IN number,
253 p_cur IN EntityCur )
254 is
255 l_limit NUMBER := 200;
256 l_last_fetch BOOLEAN := FALSE;
257 H_F_OSR CharList;
261 BEGIN
258 H_T_OSR CharList ;
259 H_SCORE NumberList ;
260
262
263 -- LOOP THROUGH THE PASSED IN OPEN CURSOR
264 LOOP
265 FETCH p_cur BULK COLLECT INTO
266 H_F_OSR
267 , H_T_OSR
268 ,H_SCORE
269 LIMIT l_limit;
270
271 IF p_cur%NOTFOUND THEN
272 l_last_fetch:=TRUE;
273 END IF;
274
275 IF H_F_OSR.COUNT = 0 and l_last_fetch THEN
276 EXIT;
277 END IF;
278
279 BEGIN
280
281 FORALL I in H_F_OSR.FIRST..H_F_OSR.LAST
282 UPDATE HZ_INT_DUP_RESULTS A
283 SET A.SCORE = A.SCORE + H_SCORE(I)
284 WHERE
285 ( A.F_OSR = H_F_OSR(I) and
286 A.T_OSR = H_T_OSR(I) and
287 A.BATCH_ID = p_batch_id
288 );
289 END;
290
291 IF l_last_fetch THEN
292 EXIT;
293
294 END IF;
295 END LOOP;
296
297 ---------- exception block ---------------
298 EXCEPTION
299 WHEN OTHERS THEN
300 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
301 FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_INT_DUP_RESULTS');
302 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
303 FND_MSG_PUB.ADD;
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END;
306
307
308
309 PROCEDURE sanitize_int_dup_party_osrs (
310 p_threshold IN NUMBER,
311 p_batch_id IN NUMBER
312 )
313 IS
314 l_owner VARCHAR2(30);
315 p_count number ;
316 BEGIN
317
318 ----------------------------------------------------------------------------------------------
319 -- 1. APPLY THE THRESHOLD AND REMOVE ROWS THAT DON'T SATISFY THRESHOLD.
320 -- 2. REMOVE APPROPRIATE ROWS TO MAKE SURE THAT THERE ARE NO REVERSED PAIRS.
321 -- 3. REMOVE APPROPRIATE ROWS TO MAKE SURE THAT THERE ARE NO INDIRECT TRANSITIVES.
322 ----------------------------------------------------------------------------------------------
323
324 -- At the end of this, we would have the following:
325
326 -- Only rows with score >= threshold.
327 -- The pair with f_osr < t_osr, if both the pairs have the same score.
328 -- The pair with the higher score, if in case, the pairs of different scores.
329
330 /* -- WE WILL BE REPLACING THIS BY AN INSERT TO A TEMP TABLE
331 -- SINCE DELETE IS PERFORMANCE PROHIBITIVE.
332 delete from hz_int_dup_results a
333 where
334 -- delete anything less than the threshold
335 (a.score < p_threshold and a.batch_id = p_batch_id);
336
337 delete from hz_int_dup_results a
338 where
339 a.batch_id = p_batch_id
340 and
341 -- if scores are same, delete the one with highest source
342 -- or if scores are different, delete the one with lower score
343 (exists
344 (Select 1 from hz_int_dup_results b
345 where
346 (
347 -- APPLY THE ABOVE PRINCIPLE TO REVERSED PAIRS
348 a.f_osr=b.t_osr and b.f_osr=a.t_osr and b.batch_id = p_batch_id and
349 ( (a.score = b.score and a.f_osr > b.f_osr) or (a.score < b.score) )
350 )
351
352 or
353 -- APPLY THE ABOVE PRINCIPLE TO INDIRECT TRANSITIVES
354 ( a.t_osr=b.t_osr and b.batch_id = p_batch_id and
355 ((a.score = b.score and a.f_osr > b.f_osr) or (a.score < b.score))
356 )
357 )
358 ) ;
359
360 */
361
362
363
364 -- for a to be inserted into the temporary table the folowing should be true:
365 -- 1. a should exceed the threshold
366 -- 2. There should not be any b in the same batch
367 -- which
368 -- EITHER
369 -- is reversed and has the (same score and whose f_osr is small) or (whose score exceeds a)
370 -- OR
371 -- is transitive and has the (same score and whose f_osr is small) or (whose score exceeds a)
372
373
374 select count(1) into p_count
375 from hz_int_dup_results
376 where batch_id = p_batch_id;
377
378 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
379 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_INT_DUP_RESULTS before sanitization '|| p_count );
380 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
381
382
383 -- Bug fix for 3639346 :::: Need to take scores that are >= threshold
384 insert into hz_int_dup_results_gt (batch_id, f_osr, f_os, t_osr, t_os, ord_f_osr, ord_t_osr, score)
385 select a.batch_id, a.f_osr, a.f_os, a.t_osr, a.t_os, a.ord_f_osr, a.ord_t_osr, a.score
386 from hz_int_dup_results a
387 where
388 (a.score >= p_threshold and a.batch_id = p_batch_id)
389 and
390 not exists
391 (select 1 from hz_int_dup_results b
392 where
393 b.batch_id = p_batch_id
394 and
395 (
396 (
397 a.f_osr=b.t_osr and b.f_osr=a.t_osr and
398 ( (a.score = b.score and b.f_osr < a.f_osr) or (a.score < b.score) )
399 )
400
401 or
402
403 (
404 a.t_osr=b.t_osr and
405 ((a.score = b.score and b.f_osr < a.f_osr ) or (a.score < b.score))
406 )
407 )
408 );
409
413
410 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
411 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to HZ_INT_DUP_RESULTS_GT '||SQL%ROWCOUNT);
412 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
418 FND_MESSAGE.SET_TOKEN('PROC' ,'SANITIZE_INT_DUP_PARTY_OSRS');
419 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
420 FND_MSG_PUB.ADD;
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422
423 END;
424
425
426 PROCEDURE final_process_int_dup_id(p_batch_id number)
427 IS
428 l_count number;
429 BEGIN
430
431 -- we need to populate the batch summary table with all the counts
432 -- update party count
433 update hz_imp_batch_summary h
434 set dup_parties_in_batch =
435 (select count(1) from
436 (select distinct winner_record_osr
437 from hz_imp_int_dedup_results
438 where batch_id = p_batch_id
439 and entity = 'PARTY'
440 union
441 select distinct dup_record_osr
442 from hz_imp_int_dedup_results
443 where batch_id = p_batch_id
444 and entity = 'PARTY'
445 )
446 )
447 where
448 h.batch_id = p_batch_id ;
449
450 -- update party set count
451 update hz_imp_batch_summary h
452 set party_dup_sets_in_batch =
453 (select count(1) from
454 (select distinct winner_record_osr
455 from hz_imp_int_dedup_results
456 where batch_id = p_batch_id
457 and entity = 'PARTY'
458 )
459 )
460 where
461 h.batch_id = p_batch_id ;
462
463 -- update party site count
464 update hz_imp_batch_summary h
465 set dup_addresses_in_batch =
466 (select count(1) from
467 (select distinct winner_record_osr
468 from hz_imp_int_dedup_results
469 where batch_id = p_batch_id
470 and entity = 'PARTY_SITES'
471 union
472 select distinct dup_record_osr
473 from hz_imp_int_dedup_results
474 where batch_id = p_batch_id
475 and entity = 'PARTY_SITES'
476 )
477 )
478 where
479 h.batch_id = p_batch_id ;
480
481 -- update party site set count
482 update hz_imp_batch_summary h
483 set address_dup_sets_in_batch =
484 (select count(1) from
485 (select distinct winner_record_osr
486 from hz_imp_int_dedup_results
487 where batch_id = p_batch_id
488 and entity = 'PARTY_SITES'
489 )
490 )
491 where
492 h.batch_id = p_batch_id ;
493
494 -- update contacts count
495 update hz_imp_batch_summary h
496 set dup_contacts_in_batch =
497 (select count(1) from
498 (select distinct winner_record_osr
499 from hz_imp_int_dedup_results
500 where batch_id = p_batch_id
501 and entity = 'CONTACTS'
502 union
503 select distinct dup_record_osr
504 from hz_imp_int_dedup_results
505 where batch_id = p_batch_id
506 and entity = 'CONTACTS'
507 )
508 )
509 where
510 h.batch_id = p_batch_id ;
511
512 -- update contacts set count
513 update hz_imp_batch_summary h
514 set contact_dup_sets_in_batch =
515 (select count(1) from
516 (select distinct winner_record_osr
517 from hz_imp_int_dedup_results
518 where batch_id = p_batch_id
519 and entity = 'CONTACTS'
520 )
521 )
522 where
523 h.batch_id = p_batch_id ;
524
525
526 -- update contact point count
527 update hz_imp_batch_summary h
528 set dup_contactpoints_in_batch =
529 (select count(1) from
530 (select distinct winner_record_osr
531 from hz_imp_int_dedup_results
532 where batch_id = p_batch_id
533 and entity = 'CONTACT_POINTS'
534 union
535 select distinct dup_record_osr
536 from hz_imp_int_dedup_results
537 where batch_id = p_batch_id
538 and entity = 'CONTACT_POINTS'
539 )
540 )
541 where
542 h.batch_id = p_batch_id ;
543
544 -- update contact point set count
545 update hz_imp_batch_summary h
546 set contactpoint_dup_sets_in_batch =
547 (select count(1) from
548 (select distinct winner_record_osr
549 from hz_imp_int_dedup_results
550 where batch_id = p_batch_id
551 and entity = 'CONTACT_POINTS'
552 )
553 )
554 where
555 h.batch_id = p_batch_id ;
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
560 FND_MESSAGE.SET_TOKEN('PROC' ,'FINAL_PROCESS_INT_TCA_DUP_ID');
561 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
565 END;
562 FND_MSG_PUB.ADD;
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564
566 PROCEDURE sanitize_int_dup_detail_osrs(p_batch_id number)
567 IS
568 p_count number;
569 BEGIN
570 select count(1) into p_count
571 from hz_imp_int_dedup_results
572 where batch_id = p_batch_id
573 and entity <> 'PARTY' ;
574
575 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
576 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of details in HZ_IMP_INT_DEDUP_RESULTS before sanitization '|| p_count );
577 FND_FILE.put_line(FND_FILE.log,'Details ::: Begin time of delete '||to_char(sysdate,'hh24:mi:ss'));
578
579
580 delete from hz_imp_int_dedup_results a
581 where
582 (exists
583 (Select 1 from hz_imp_int_dedup_results b
584 where
585 (
586 -- DELETE DIRECT TRANSITIVE DETAIL OSRS FOR THIS BATCH
587 -- WE BASICALLY MAKE SURE THAT A DETAIL RECORD OSR
588 -- CANNOT BE A WINNER RECORD OSR, FOR A GIVEN DETAIL PARTY OSR
589
590 a.batch_id = p_batch_id and
591 a.entity <> 'PARTY' and
592 a.batch_id = b.batch_id and
593 a.entity = b.entity AND -- bug 5393826
594 a.winner_record_osr=b.dup_record_osr -- bug 5393826
595 )
596 )
597 );
598
599 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
600 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of details deleted in HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);
601 FND_FILE.put_line(FND_FILE.log,'Details ::: End time of delete '||to_char(sysdate,'hh24:mi:ss'));
602
603 -- bug 5393826
604 FND_FILE.put_line(FND_FILE.log,'Details ::: Start time of insert of Winner Detail OSRS '||to_char(sysdate,'hh24:mi:ss'));
605 insert into hz_imp_int_dedup_results
606 (batch_id,
607 winner_record_osr,
608 winner_record_os,
609 dup_record_osr,
610 dup_record_os,
611 detail_party_osr,
612 entity,
613 score
614 )
615 select distinct p_batch_id,
616 winner_record_osr,
617 winner_record_os,
618 winner_record_osr,
619 winner_record_os,
620 detail_party_osr,
621 entity,
622 0
623 from hz_imp_int_dedup_results a
624 where a.entity <> 'PARTY'
625 and a.batch_id = p_batch_id ;
626
627 FND_FILE.put_line(FND_FILE.log,'Details ::: End time of insert of Winner Detail OSRs '||to_char(sysdate,'hh24:mi:ss'));
628 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of Winner Detail OSRs inserted '||SQL%ROWCOUNT);
629
630 -- bug 5393826
631
632
633 -- Bug Fix 3588873 :: Need to report the import interface table dates for all the duplicates
634
635
636 FND_FILE.put_line(FND_FILE.log,'Details ::: End time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));
637
638 -- We take of the "PARTY SITES" entity here.
639 update hz_imp_int_dedup_results a
640 set (a.dup_creation_date, a.dup_last_update_date)
641 = (select b.creation_date, b.last_update_date
642 from hz_imp_addresses_int b
643 where b.batch_id = p_batch_id
644 and b.site_orig_system_reference = a.dup_record_osr
645 and b.site_orig_system = a.dup_record_os
646 )
647 where a.entity = 'PARTY_SITES' and a.batch_id = p_batch_id ;
648
649 -- We take of the "CONTACTS" entity here.
650 update hz_imp_int_dedup_results a
651 set (a.dup_creation_date, a.dup_last_update_date)
652 = (select b.creation_date, b.last_update_date
653 from hz_imp_contacts_int b
654 where b.batch_id = p_batch_id
655 and b.contact_orig_system_reference = a.dup_record_osr
656 and b.contact_orig_system = a.dup_record_os
657 )
658 where a.entity = 'CONTACTS' and a.batch_id = p_batch_id ;
659
660 -- We take of the "CONTACT POINTS" entity here.
661 update hz_imp_int_dedup_results a
662 set (a.dup_creation_date, a.dup_last_update_date)
663 = (select b.creation_date, b.last_update_date
664 from hz_imp_contactpts_int b
665 where b.batch_id = p_batch_id
666 and b.cp_orig_system_reference = a.dup_record_osr
667 and b.cp_orig_system = a.dup_record_os
668 )
669 where a.entity = 'CONTACT_POINTS' and a.batch_id = p_batch_id ;
670
671 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of duplicate details updated in HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);
672 FND_FILE.put_line(FND_FILE.log,'Details ::: End time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));
673
674 EXCEPTION
675 WHEN OTHERS THEN
676 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
677 FND_MESSAGE.SET_TOKEN('PROC' ,'SANITIZE_INT_DUP_DETAIL_OSRS');
678 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
679 FND_MSG_PUB.ADD;
680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
681
682 END;
683
684
685 PROCEDURE report_tca_dup_parties (
689 )
686 p_batch_id IN NUMBER,
687 p_threshold IN NUMBER,
688 p_auto_merge_threshold IN NUMBER
690 IS
691 p_count number ;
692 BEGIN
693
694 -- FIRST, REPORT WINNER PARTIES TO DUP SETS
695 -- THESE ARE ALL PRECISELY THE ORD_FIDs IN HZ_DUP_RESULTS WHICH ARE NOT ORD_TIDs
696 -- OF ANY ROW AND OCCUR AT ODD LEVELS IN THE CONNECT BY.
697
698 ----------------------------------------------------
699 --- EXAMPLE :
700 -- 1 2 LEVEL 1
701 -- 2 3 LEVEL 2
702 -- 3 4 LEVEL 3
703 -- 1 7 LEVEL 1
704 -- WOULD RESULT IN 1 AND 2 BEING CHOSEN AS WINNER PARTIES
705 -- AFTER THE CONNECT BY. SO THE CONNECT BY NOT ONLY CHOOSES
706 -- THE WINNER, BUT ALSO MAKES SURE THAT ODD LEVELS ARE TAKEN
707 -- INTO ACCOUNT.
708 -----------------------------------------------------
709
710 select count(1) into p_count
711 from hz_dup_results ;
712
713 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
714 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before reporting '|| p_count );
715 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
716
717
718 insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
719 status, merge_type, created_by, creation_date, last_update_login,
720 last_update_date, last_updated_by)
721 select win_party_id, HZ_MERGE_BATCH_S.nextval, p_batch_id,
722 'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
723 hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
724 hz_utility_pub.user_id
725 from
726 (
727 select distinct d.ord_fid as win_party_id, level as levelu
728 from hz_dup_results d
729 start with d.ord_fid not in
730 (
731 select c.ord_tid
732 from hz_dup_results c
733 )
734 connect by prior ord_tid = ord_fid
735 )
736 where mod(levelu, 2) = 1 ;
737
738 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
739 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Sets '||SQL%ROWCOUNT);
740 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
741
742
743 -- REPORT WINNER AND ALL ITS DUPLICATES ( ONE OR MORE) TO HZ_DUP_SET_PARTIES
744
745 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
746 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
747
748
749 -- this is the part for the winner
750 insert into hz_dup_set_parties (dup_party_id, dup_set_id,merge_seq_id,
751 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
752 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
753 select a.winner_party_id, a.dup_set_id, 0, 0, 100 ,'Y',
754 hz_utility_pub.created_by,hz_utility_pub.creation_date,
755 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
756 hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
757 from hz_dup_sets a
758 where a.dup_batch_id = p_batch_id
759 union all
760 -- this is the part for all the duplicates of the winner
761 -- basically compare the winner from dup set to any row which has the winner has its ord_fid
762 -- and pick up its ord_tid.
763 select b.ord_tid, a.dup_set_id, 0, 0, b.score ,decode( sign(b.score - p_auto_merge_threshold),-1,'N','Y'),
764 hz_utility_pub.created_by,hz_utility_pub.creation_date,
765 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
766 hz_utility_pub.user_id,a.dup_batch_id
767 from hz_dup_sets a, hz_dup_results b
768 where a.dup_batch_id = p_batch_id
769 and a.winner_party_id = b.ord_fid ;
770
771 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Set Parties'||SQL%ROWCOUNT);
772 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
773
774
775
776
777
778 ---------- exception block ---------------
779 EXCEPTION
780 WHEN OTHERS THEN
781 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
782 FND_MESSAGE.SET_TOKEN('PROC','REPORT_TCA_DUP_PARTIES');
783 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
784 FND_MSG_PUB.ADD;
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786
787 END;
788
789 FUNCTION check_bulk_feature return BOOLEAN IS
790
791 TYPE PartyCurTyp IS REF CURSOR;
792 pt_cur PartyCurTyp;
793
794 TYPE Nlist is table of number;
795 pidlist NList;
796
797 begin
798 open pt_cur for 'select party_id from hz_parties where rownum<3';
799 fetch pt_cur bulk collect into pidlist;
800 close pt_cur;
801 return true;
802 exception
803 when others then
804 if pt_cur%isopen THEN
805 close pt_cur;
806 end if;
807 return false;
808 end;
809
810
811 PROCEDURE tca_dup_id_worker(
812 p_dup_batch_id IN NUMBER,
813 p_match_rule_id IN NUMBER,
814 p_worker_number IN NUMBER,
815 p_number_of_workers IN NUMBER,
816 p_subset_sql IN VARCHAR2
817 )
818 IS
819 l_owner VARCHAR2(30);
820 l_pkg_name varchar2(2000);
821 anon_str varchar2(255);
822 x_inserted_duplicates number := 0;
823 x_rows_in_chunk number := 0;
824 pid_list NumberList;
828 x_trap_explosion varchar2(1) := 'Y';
825 cnt number :=1;
826 chunk_limit number :=50;
827 l_last_fetch BOOLEAN := FALSE;
829
830 TYPE PartyCurTyp IS REF CURSOR;
831 pt_cur PartyCurTyp;
832
833 start_idx NUMBER;
834 end_idx NUMBER;
835 fetch_from_party_cursor BOOLEAN;
836 bulk_feature_exists boolean;
837
838
839 BEGIN
840 -- check if bulk fetch feature exists for this version of the database
841 bulk_feature_exists := check_bulk_feature;
842
843 -- get the match rule package
844 l_pkg_name := 'HZ_IMP_MATCH_RULE_'||p_match_rule_id;
845
846
847
848 -- we first need to make sure that the subset of parties that we get for
849 -- this worker, satisfy the subset sql. The basic idea is that, we need
850 -- to make sure that the source parties that we begin with are in the subset sql,
851 -- before finding their duplicates
852
853 --Adding the condition of Status = A, to the 2 cursors below to fix bug 4669400.
854 --This will make sure that the Merged and Inactive Parties (with status as 'M' and 'I')
855 --will not be considered for duplicate idenfication.
856
857 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------');
858
859 FND_FILE.put_line(FND_FILE.log,'Start Time before insert to hz_dqm_stage_gt ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
860 IF p_subset_sql IS NULL
861 THEN
862 /* OPEN pt_cur FOR
863 'SELECT parties.PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL (parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number' */
864 execute immediate
865 'insert /*+ APPEND */ into HZ_MATCHED_PARTIES_GT(party_id)
866 SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ parties.PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP''
867 AND NVL(parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number '
868 USING p_number_of_workers, p_worker_number;
869 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted into HZ_MATCHED_PARTIES_GT by worker '||p_worker_number||' is '||SQL%ROWCOUNT ); -- BUG 5351721
870 ELSE
871 /* OPEN pt_cur FOR
872 'SELECT PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL (parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number AND '||
873 p_subset_sql */
874 execute immediate
875 'insert /*+ APPEND */ into HZ_MATCHED_PARTIES_GT(party_id)
876 SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL(parties.STATUS,''A'') = ''A''
877 AND mod(parties.PARTY_ID, :num_workers) = :worker_number AND '||
878 p_subset_sql USING p_number_of_workers, p_worker_number;
879 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted into HZ_MATCHED_PARTIES_GT by worker '||p_worker_number||' is '||SQL%ROWCOUNT ); -- BUG 5351721
880 END IF;
881 FND_FILE.put_line(FND_FILE.log,'End Time after insert to HZ_MATCHED_PARTIES_GT ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
882 COMMIT;
883
884
885 OPEN pt_cur FOR 'select party_id from HZ_MATCHED_PARTIES_GT'; -- BUG 5351721
886
887
888 -- we always fetch from the pid list, unless the chunk explodes
889 fetch_from_party_cursor :=true;
890
891 LOOP
892 FND_FILE.put_line(FND_FILE.log,'Start Time before processing chunk ' || cnt || ' ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS')); -- BUG 5351721
893 IF NOT fetch_from_party_cursor THEN
894 --dbms_output.put_line('chunk_num '||cnt);
895 IF start_idx<pid_list.COUNT THEN
896 IF (start_idx+chunk_limit-1)<(pid_list.COUNT) THEN
897 end_idx := start_idx+chunk_limit-1;
898 ELSE
899 end_idx := pid_list.COUNT;
900 END IF;
901 -- dbms_output.put_line('start '||start_idx);
902 -- dbms_output.put_line('end '||end_idx);
903 -- dbms_output.put_line('limit '||chunk_limit);
904 /* insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)
905 values (-1,-1,-1,-1,chunk_limit,cnt,sysdate);
906 */
907
908 -- truncate chunk table before inserting into it
909 l_owner := HZ_IMP_DQM_STAGE.get_owner_name('HZ_DUP_WORKER_CHUNK_GT', 'TABLE');
910 execute immediate ' truncate table ' || l_owner || '.HZ_DUP_WORKER_CHUNK_GT';
911
912 FORALL I in start_idx..end_idx
913 INSERT INTO hz_dup_worker_chunk_gt values (pid_list(I));
914
915 x_rows_in_chunk:=SQL%ROWCOUNT;
916
917 DELETE FROM hz_dup_worker_chunk_gt WHERE
918 EXISTS (Select 1 from HZ_DUP_RESULTS t
919 WHERE t.tid = party_id);
920 x_rows_in_chunk:=x_rows_in_chunk-SQL%ROWCOUNT;
921 start_idx:=start_idx+chunk_limit;
922 ELSE
923 fetch_from_party_cursor:=TRUE;
924 END IF;
925
929
926 END IF;
927
928 IF fetch_from_party_cursor THEN
930 -- fetch a chunk of party ids and note that the chunk limit
931 -- changes dynamically
932 -- not that the chunk limit would always be atleast 50.
933
934 IF bulk_feature_exists THEN
935 FETCH pt_cur BULK COLLECT INTO pid_list limit chunk_limit;
936 ELSE
937 pid_list.DELETE;
938 FOR I in 1..chunk_limit
939 LOOP
940 FETCH pt_cur INTO pid_list(I);
941 EXIT WHEN pt_cur%NOTFOUND;
942 END LOOP;
943 END IF;
944
945 -- mark it if the cursor is empty
946 IF pt_cur%NOTFOUND THEN
947 l_last_fetch:=TRUE;
948 END IF;
949
950
951 /*
952 insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)
953 values (-1,-1,-1,-1,chunk_limit,cnt,sysdate);
954 */
955 -- truncate chunk table before inserting into it
956 l_owner := HZ_IMP_DQM_STAGE.get_owner_name('HZ_DUP_WORKER_CHUNK_GT', 'TABLE');
957 execute immediate ' truncate table ' || l_owner || '.HZ_DUP_WORKER_CHUNK_GT';
958
959 -- insert all the parties in the chunk to the temp table
960 FORALL I in 1..pid_list.COUNT
961 INSERT INTO hz_dup_worker_chunk_gt values (pid_list(I));
962
963 x_rows_in_chunk:=SQL%ROWCOUNT;
964
965 -- remove any party ids from temp table if in case
966 -- dups have been found already for them
967 -- so that collision does not happen
968 -- ( for example if 1 finds 2 as a duplicate
969 -- we would like to avoid doing anything with 2, if 2 indeed happens
970 -- to be allocated to this worker )
971 DELETE FROM hz_dup_worker_chunk_gt WHERE
972 EXISTS (Select 1 from HZ_DUP_RESULTS t
973 WHERE t.tid = party_id);
974
975 x_rows_in_chunk:=x_rows_in_chunk-SQL%ROWCOUNT;
976 FND_FILE.put_line(FND_FILE.log,'Number of rows in chunk table ' || x_rows_in_chunk);
977 END IF;
978
979 -- set the trap for the explosion depending on the number of rows in the chunk
980 IF x_rows_in_chunk < 50
981 THEN
982 x_trap_explosion := 'N' ;
983 END IF;
984
985 -- EXECUTE IMMEDIATE sql_stmt USING my_sal, my_empno, OUT my_ename, OUT my_job;
986 -- build the string to execute the match rule package
987 anon_str := 'begin ' ||l_pkg_name ||'.tca_join_entities(:x_trap_explosion,:x_rows_in_chunk,:x_inserted_duplicates); end;' ;
988
989 -- call the corresponding function in this match rule package, to do the
990 -- joins on entities based on the match rule, for this chunk
991 EXECUTE IMMEDIATE anon_str USING IN x_trap_explosion, IN x_rows_in_chunk, OUT x_inserted_duplicates ;
992
993 commit;
994
995 FND_FILE.put_line(FND_FILE.log,'End Time after processing chunk ' || cnt || ' ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS')); -- BUG 5351721
996 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------');
997 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------');
998 cnt := cnt+1;
999
1000 -- if the chunk explodes, process in chunks of 25
1001 IF x_inserted_duplicates = -1
1002 THEN
1003 -- dbms_output.put_line('in chunk explosion ');
1004 IF fetch_from_party_cursor THEN
1005 start_idx :=1;
1006 fetch_from_party_cursor :=FALSE;
1007 ELSE
1008 start_idx:=start_idx-chunk_limit;
1009 END IF;
1010 chunk_limit:=25;
1011
1012 -- change chunk limit as you go
1013 -- if inserted rows is less than 50, increase the chunk size
1014 -- else halve the chunk size, making sure that the rounding of
1015 -- the arithmetic yields an integer, for the size.
1016
1017 ELSIF (x_inserted_duplicates < 50 )
1018 THEN
1019 chunk_limit:=chunk_limit*2;
1020 ELSE
1021 chunk_limit:=greatest(round(chunk_limit/2),25);
1022 END IF;
1023
1024
1025 IF l_last_fetch AND fetch_from_party_cursor
1026 THEN
1027 EXIT;
1028 END IF;
1029
1030
1031 END LOOP;
1032 CLOSE pt_cur;
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1037 FND_MESSAGE.SET_TOKEN('PROC' ,'TCA_DUP_ID_WORKER');
1038 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1039 FND_MSG_PUB.ADD;
1040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041
1042
1043 END ;
1044
1045 PROCEDURE tca_sanitize_report(
1046 p_dup_batch_id IN NUMBER,
1047 p_match_rule_id IN NUMBER,
1048 p_subset_sql IN VARCHAR2,
1052 x_threshold number;
1049 p_within_subset IN VARCHAR2
1050 )
1051 IS
1053 x_auto_merge_threshold number;
1054 ret_value number;
1055 l_pkg_name varchar2(2000);
1056 BEGIN
1057
1058 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1059 FND_FILE.put_line(FND_FILE.log,'Entering tca_sanitize_report ');
1060
1061
1062
1063 -- get the threshold and the auto merge threshold
1064 select match_score, auto_merge_score into x_threshold, x_auto_merge_threshold
1065 from hz_match_rules_vl
1066 where match_rule_id = p_match_rule_id;
1067
1068 -- sanitize data in temp tables that get populated by the match rule
1069 sanitize_tca_dup_parties ( x_threshold, x_auto_merge_threshold, p_subset_sql,
1070 p_within_subset);
1071
1072 -- report duplicate parties to hz_dup_sets aand hz_dup_set_parties
1073 report_tca_dup_parties (
1074 p_dup_batch_id, x_threshold, x_auto_merge_threshold );
1075
1076 -- exception block
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1080 FND_MESSAGE.SET_TOKEN('PROC' ,'TCA_SANITIZE_REPORT');
1081 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1082 FND_MSG_PUB.ADD;
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084
1085
1086 END ;
1087
1088
1089
1090
1091 -------------------------------------------------------------------------------------------------------------
1092 -------------------------------------------------------------------------------------------------------------
1093 -- INTERFACE TCA DUP IDENTIFICATION
1094 -------------------------------------------------------------------------------------------------------------
1095 -------------------------------------------------------------------------------------------------------------
1096
1097 /**
1098 * PROCEDURE update_hz_imp_dup_parties
1099 *
1100 * DESCRIPTION
1101 *
1102 *
1103 *
1104 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1105 *
1106 *
1107 * ARGUMENTS
1108 *
1109 *
1110 * NOTES
1111 *
1112 * MODIFICATION HISTORY
1113 *
1114 * 04-09-2003 Colathur Vijayan o Created.
1115 *
1116 */
1117
1118 --------------------------------------------------------------------------------------
1119 -- update_hz_imp_dup_parties ::: This is a generic procedure, that would do a bulk update
1120 -- of hz_dup_results, using a passed in open cursor
1121 --------------------------------------------------------------------------------------
1122
1123
1124 PROCEDURE update_hz_imp_dup_parties (
1125 p_batch_id IN number,
1126 p_cur IN EntityCur )
1127 is
1128 l_limit NUMBER := 200;
1129 l_last_fetch BOOLEAN := FALSE;
1130 H_PARTY_ID NumberList;
1131 H_DUP_PARTY_ID NumberList ;
1132 H_SCORE NumberList ;
1133
1134 BEGIN
1135
1136 -- LOOP THROUGH THE PASSED IN OPEN CURSOR
1137 LOOP
1138 FETCH p_cur BULK COLLECT INTO
1139 H_PARTY_ID
1140 , H_DUP_PARTY_ID
1141 ,H_SCORE
1142 LIMIT l_limit;
1143
1144 IF p_cur%NOTFOUND THEN
1145 l_last_fetch:=TRUE;
1146 END IF;
1147
1148 IF H_PARTY_ID.COUNT = 0 and l_last_fetch THEN
1149 EXIT;
1150 END IF;
1151
1152 BEGIN
1153
1154 FORALL I in H_PARTY_ID.FIRST..H_DUP_PARTY_ID.LAST
1155 UPDATE HZ_IMP_DUP_PARTIES A
1156 SET A.SCORE = A.SCORE + H_SCORE(I)
1157 WHERE
1158 ( A.PARTY_ID = H_PARTY_ID(I) and
1159 A.DUP_PARTY_ID = H_DUP_PARTY_ID(I) and
1160 A.BATCH_ID = p_batch_id
1161 );
1162 END;
1163
1164 IF l_last_fetch THEN
1165 EXIT;
1166
1167 END IF;
1168 END LOOP;
1169 ---------- exception block ---------------
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1173 FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_IMP_DUP_PARTIES');
1174 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1175 FND_MSG_PUB.ADD;
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END;
1178
1179
1180 PROCEDURE report_int_tca_dup_parties (
1181 p_batch_id IN NUMBER,
1182 p_match_rule_id IN NUMBER,
1183 p_request_id IN NUMBER,
1184 l_dup_batch_id OUT NOCOPY NUMBER,
1185 l_party_count OUT NOCOPY NUMBER
1186 )
1187 IS
1188 p_batch_name varchar2(255);
1189 p_count number ;
1190 BEGIN
1191
1192 -- GET INTERFACE BATCH NAME
1193 select batch_name into p_batch_name
1194 from hz_imp_batch_summary
1195 where batch_id = p_batch_id;
1196
1197 -- CALL THE HZ_DUP_BATCH TABLE HANDLER, INSERT A ROW
1198 -- FOR THIS INTERFACE BATCH AND ALSO GET THE BATCH ID
1199 -- FOR REPORTING THESE DUPLICATES TO HZ_DUP_SETS
1200 -- AND HZ_DUP_PARTIES
1201
1202 HZ_DUP_BATCH_PKG.Insert_Row(
1203 px_dup_batch_id => l_dup_batch_id
1204 ,p_dup_batch_name => p_batch_name
1205 ,p_match_rule_id => p_match_rule_id
1206 ,p_application_id => '222'
1210 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
1207 ,p_request_type => 'IMPORT'
1208 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
1209 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
1211 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
1212 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1213 );
1214
1215 -- THIS IS ADDED FOR AUTOMERGE
1216 -- WE WANT TO IDENTIFY BATCHES THAT ARE AUTO MERGE ENABLED
1217
1218 update hz_dup_batch
1219 set automerge_flag = (select automerge_flag
1220 from hz_match_rules_vl
1221 where match_rule_id = p_match_rule_id)
1222 where dup_batch_id = l_dup_batch_id ;
1223
1224 -- NOTE: WINNERS ARE TCA PARTIES
1225 -- DUPS ARE INTERFACE PARTIES
1226
1227
1228
1229 -- INSERT INTO HZ_DUP_SETS, ALL THE DISTINCT DUP_PARTY_IDs WHICH OCCUR IN HZ_IMP_DUP_PARTIES
1230 -- AS WINNERS, FOR THE GIVEN BATCH PROVIDED THE FOLLOWING CONDITION IS MET.
1231 -- 1. THE WINNER HAS ATLEAST A PARTY ID ASSOCIATED WITH IT, WHICH HAS
1232 -- ITS AUTO MERGE FLAG <> 'R' AND IS LOADED
1233
1234 select count(1) into p_count
1235 from hz_imp_dup_parties
1236 where batch_id = p_batch_id;
1237
1238 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1239 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_IMP_DUP_PARTIES before reporting '|| p_count );
1240 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1241
1242 insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
1243 status, merge_type, created_by, creation_date, last_update_login,
1244 last_update_date, last_updated_by)
1245 select win_party_id, HZ_MERGE_BATCH_S.nextval, l_dup_batch_id,
1246 'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
1247 hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
1248 hz_utility_pub.user_id
1249 from
1250 (select distinct h.dup_party_id as win_party_id
1251 from hz_imp_dup_parties h
1252 where h.batch_id = p_batch_id
1253 and exists (select a.party_id
1254 from hz_imp_dup_parties a, hz_parties b
1255 where a.batch_id = p_batch_id
1256 and a.dup_party_id = h.dup_party_id
1257 and a.auto_merge_flag <> 'R'
1258 and a.party_id = b.party_id
1259 and b.request_id = p_request_id
1260 )
1261 ) ;
1262
1263 -- This is the number of winner parties ( and equivalently the number of dup sets )
1264 p_count := SQL%ROWCOUNT ;
1265
1266 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1267 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Sets '|| p_count );
1268 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1269
1270 -- INSERT WINNER AND ALL ITS DUPLICATES TO HZ_DUP_SET_PARTIES
1271 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1272 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1273 -- this is the part for the winner
1274 insert into hz_dup_set_parties (dup_party_id, dup_set_id,merge_seq_id,
1275 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
1276 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
1277 select a.winner_party_id, a.dup_set_id, 0, 0, 100 ,'Y',
1278 hz_utility_pub.created_by,hz_utility_pub.creation_date,
1279 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
1280 hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
1281 from hz_dup_sets a
1282 where a.dup_batch_id = l_dup_batch_id
1283 union all
1284 -- this is the part for all the duplicates of the winner
1285 -- basically compare the winner from dup set to any row which has the winner
1286 -- as its dup_party_id and pick up the corresponding interface party id,
1287 -- provided the following conditions are met:
1288 -- 1. The interface party id has been loaded
1289 -- 2. The interface party id does not have an automerge flag of 'R'
1290 select b.party_id, a.dup_set_id, 0, p_batch_id, b.score , b.auto_merge_flag,
1291 hz_utility_pub.created_by,hz_utility_pub.creation_date,
1292 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
1293 hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
1294 from hz_dup_sets a, hz_imp_dup_parties b, hz_parties c
1295 where a.dup_batch_id = l_dup_batch_id
1296 and a.winner_party_id = b.dup_party_id
1297 and b.party_id = c.party_id
1298 and c.request_id = p_request_id
1299 and b.auto_merge_flag <> 'R' ;
1300
1301 -- The total number of parties inserted into hz_dup_set_parties
1302 l_party_count := SQL%ROWCOUNT ;
1303
1304 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Set Parties '|| l_party_count);
1305 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1306
1307 -- Number of parties in interface for which a merge has been requested
1308 -- = (Total number of parties inserted into dup set parties - Number of winners)
1309 l_party_count := l_party_count - p_count ;
1310
1311 -- update HZ_IMP_BATCH_SUMMARY with this count
1312 update hz_imp_batch_summary h
1316 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of merge request parties inserted into batch summary table is '
1313 set party_merge_requests = l_party_count
1314 where h.batch_id = p_batch_id ;
1315
1317 || l_party_count);
1318
1319
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1323 FND_MESSAGE.SET_TOKEN('PROC' ,'REPORT_INT_TCA_DUP_PARTIES');
1324 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1325 FND_MSG_PUB.ADD;
1326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1327
1328
1329 END ;
1330
1331 PROCEDURE report_int_tca_dup_details (
1332 p_batch_id IN NUMBER,
1333 p_match_rule_id IN NUMBER,
1334 p_request_id IN NUMBER,
1335 l_dup_batch_id IN NUMBER,
1336 l_party_count IN OUT NOCOPY NUMBER
1337 )
1338 IS
1339 p_count number ;
1340 ps_count number ;
1341 c_count number ;
1342 cp_count number ;
1343 BEGIN
1344
1345 -- INSERT INTO HZ_DUP_SETS, ALL THE DISTINCT PARTY_IDs WHICH OCCUR IN HZ_IMP_DUP_DETAILS
1346 -- FOR THE GIVEN BATCH, PROVIDED THE FOLLOWING CONDITION IS MET:
1347 -- 1. ALL THE WINNER PARTIES SHOULD HAVE ATLEAST ONE DETAIL THAT HAS BEEN
1348 -- LOADED TO TCA.
1349
1350 select count(1) into p_count
1351 from hz_imp_dup_details
1352 where batch_id = p_batch_id;
1353
1354 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1355 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of parties in HZ_IMP_DUP_DETAILS before reporting'|| p_count );
1356 FND_FILE.put_line(FND_FILE.log,'Details ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1357
1358 insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,
1359 status, merge_type, created_by, creation_date, last_update_login,
1360 last_update_date, last_updated_by)
1361 select win_party_id, HZ_MERGE_BATCH_S.nextval, l_dup_batch_id,
1362 'SYSBATCH', 'SAME_PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
1363 hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
1364 hz_utility_pub.user_id
1365 from
1366 (select distinct h.party_id as win_party_id
1367 from hz_imp_dup_details h
1368 where h.batch_id = p_batch_id
1369 and
1370 (
1371 exists (select a.dup_record_id
1372 from hz_imp_dup_details a, hz_party_sites b
1373 where a.party_id = h.party_id
1374 and a.batch_id = p_batch_id
1375 and a.record_id = b.party_site_id
1376 and b.request_id = p_request_id
1377 )
1378 or
1379 exists (select a.dup_record_id
1380 from hz_imp_dup_details a, hz_contact_points b
1381 where a.party_id = h.party_id
1382 and a.batch_id = p_batch_id
1383 and a.record_id = b.contact_point_id
1384 and b.request_id = p_request_id
1385 )
1386 or
1387 exists (select a.dup_record_id
1388 from hz_imp_dup_details a, hz_org_contacts b
1389 where a.party_id = h.party_id
1390 and a.batch_id = p_batch_id
1391 and a.record_id = b.org_contact_id
1392 and b.request_id = p_request_id
1393 )
1394 )
1395 ) ;
1396
1397 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1398 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of parties inserted to Dup Sets '||SQL%ROWCOUNT);
1399 FND_FILE.put_line(FND_FILE.log,'Details ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1400
1401 -- INSERT INTO HZ_DUP_SET_PARTIES, ALL THE WINNERS FROM HZ_DUP_SETS
1402 -- FOR THE GIVEN BATCH
1403 insert into hz_dup_set_parties (dup_party_id, dup_set_id,merge_seq_id,
1404 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
1405 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
1406 select a.winner_party_id, a.dup_set_id, 0, 0, 100 ,'Y',
1407 hz_utility_pub.created_by,hz_utility_pub.creation_date,
1408 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
1409 hz_utility_pub.user_id,a.dup_batch_id --Bug No: 4244529
1410 from hz_dup_sets a
1411 where a.dup_batch_id = l_dup_batch_id
1412 and a.merge_type = 'SAME_PARTY_MERGE';
1413
1414 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1415 FND_FILE.put_line(FND_FILE.log,'Details ::: Number of parties inserted to Dup Set Parties '||SQL%ROWCOUNT);
1416 FND_FILE.put_line(FND_FILE.log,'Details ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1417
1418 FND_FILE.put_line(FND_FILE.log,'Details ::: total merge request count is '
1419 || l_party_count);
1420
1421 -- Updating counts to Batch Summary
1422 -- Party Site Count
1423 select count(distinct a.dup_record_id) into ps_count
1424 from hz_imp_dup_details a, hz_party_sites b
1425 where a.batch_id = p_batch_id
1426 and a.record_id = b.party_site_id
1427 and b.request_id = p_request_id ;
1428 update hz_imp_batch_summary h
1432
1429 set address_merge_requests = ps_count
1430 where h.batch_id = p_batch_id ;
1431
1433 -- Contact Point Count
1434 select count(distinct a.dup_record_id) into cp_count
1435 from hz_imp_dup_details a, hz_contact_points b
1436 where a.batch_id = p_batch_id
1437 and a.record_id = b.contact_point_id
1438 and b.request_id = p_request_id ;
1439 update hz_imp_batch_summary h
1440 set contactpoint_merge_requests = cp_count
1441 where h.batch_id = p_batch_id ;
1442
1443
1444
1445 -- Contact Count
1446 select count(distinct a.dup_record_id) into c_count
1447 from hz_imp_dup_details a, hz_org_contacts b
1448 where a.batch_id = p_batch_id
1449 and a.record_id = b.org_contact_id
1450 and b.request_id = p_request_id ;
1451
1452 update hz_imp_batch_summary h
1453 set contact_merge_requests = c_count
1454 where h.batch_id = p_batch_id ;
1455
1456 -- Total Count
1457 l_party_count := l_party_count + ps_count + c_count + cp_count ;
1458
1459 update hz_imp_batch_summary h
1460 set total_merge_requests = l_party_count
1461 where h.batch_id = p_batch_id ;
1462
1463 FND_FILE.put_line(FND_FILE.log,'Details ::: Total Number of merge requests inserted into batch summary table is '
1464 || l_party_count);
1465
1466
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1470 FND_MESSAGE.SET_TOKEN('PROC' ,'REPORT_INT_TCA_DUP_DETAILS');
1471 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1472 FND_MSG_PUB.ADD;
1473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474 END ;
1475
1476
1477
1478 PROCEDURE interface_tca_dup_id(
1479 p_batch_id IN number,
1480 p_match_rule_id IN number,
1481 p_from_osr IN VARCHAR2,
1482 p_to_osr IN VARCHAR2,
1483 p_batch_mode_flag IN VARCHAR2,
1484 x_return_status OUT NOCOPY VARCHAR2,
1485 x_msg_count OUT NOCOPY NUMBER,
1486 x_msg_data OUT NOCOPY VARCHAR2
1487 )
1488 IS
1489 x_threshold number;
1490 x_auto_merge_threshold number;
1491 ret_value number;
1492 l_pkg_name varchar2(2000);
1493 anon_str varchar2(255);
1494 BEGIN
1495 -- initialize API return status to success.
1496 x_return_status := FND_API.G_RET_STS_SUCCESS;
1497
1498 -- stage data into interface tables
1499 HZ_IMP_DQM_STAGE.pop_int_tca_search_tab( p_batch_id, p_match_rule_id,p_from_osr, p_to_osr,p_batch_mode_flag,
1500 x_return_status,x_msg_count,x_msg_data);
1501
1502 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1503 THEN
1504 FND_FILE.put_line(FND_FILE.log,'Staging Unsuccessful - pop_int_tca_search_tab did not return success');
1505 return;
1506 END IF;
1507
1508 -- get the threshold and the auto merge threshold
1509 select match_score, auto_merge_score into x_threshold, x_auto_merge_threshold
1510 from hz_match_rules_vl
1511 where match_rule_id = p_match_rule_id;
1512
1513 -- get the match rule package
1514 l_pkg_name := 'HZ_IMP_MATCH_RULE_'||p_match_rule_id;
1515
1516 -- call the corresponding function in this match rule package, to join based on entities
1517 -- dictated by the match rule and report results to the hz_imp_dup_parties table
1518 -- and any other details table
1519 anon_str := 'begin ' || l_pkg_name || '.interface_tca_join_entities(:p_batch_id,' ||
1520 ':p_from_osr,:p_to_osr,:x_threshold,:x_auto_merge_threshold); end;' ;
1521
1522 EXECUTE IMMEDIATE anon_str USING p_batch_id, p_from_osr, p_to_osr, x_threshold,
1523 x_auto_merge_threshold ;
1524
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1528 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1529 FND_MESSAGE.SET_TOKEN('PROC' ,'INTERFACE_TCA_DUP_ID');
1530 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1531 FND_MSG_PUB.ADD;
1532 FND_MSG_PUB.Count_And_Get(
1533 p_encoded => FND_API.G_FALSE,
1534 p_count => x_msg_count,
1535 p_data => x_msg_data);
1536
1537 END ;
1538
1539
1540 PROCEDURE update_party_dqm_action_flag (
1541 p_batch_id IN number,
1542 p_cur IN EntityCur )
1543 is
1544 l_limit NUMBER := 200;
1545 l_last_fetch BOOLEAN := FALSE;
1546 H_POSR CharList ;
1547 H_POS CharList;
1548 H_AM_FLAG CharList ;
1549
1550 BEGIN
1551
1552 -- LOOP THROUGH THE PASSED IN OPEN CURSOR
1553 LOOP
1554 FETCH p_cur BULK COLLECT INTO
1555 H_POSR,
1556 H_POS,
1557 H_AM_FLAG
1558 LIMIT l_limit;
1559
1560 IF p_cur%NOTFOUND THEN
1564 IF H_POSR.COUNT = 0 and l_last_fetch THEN
1561 l_last_fetch:=TRUE;
1562 END IF;
1563
1565 EXIT;
1566 END IF;
1567
1568 BEGIN
1569 -- update the interface table
1570 FORALL I in H_POSR.FIRST..H_POSR.LAST
1571 UPDATE HZ_IMP_PARTIES_INT A
1572 SET A.DQM_ACTION_FLAG = DECODE(H_AM_FLAG(I), 'Y','D','P')
1573 WHERE
1574 ( A.PARTY_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
1575 A.PARTY_ORIG_SYSTEM = H_POS(I) and
1576 A.BATCH_ID = p_batch_id
1577 );
1578 END;
1579
1580 IF l_last_fetch THEN
1581 EXIT;
1582
1583 END IF;
1584 END LOOP;
1585
1586 ---------- exception block ---------------
1587 EXCEPTION
1588 WHEN OTHERS THEN
1589 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1590 FND_MESSAGE.SET_TOKEN('PROC','UPDATE_PARTY_DQM_ACTION_FLAG');
1591 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1592 FND_MSG_PUB.ADD;
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594 END;
1595
1596 PROCEDURE update_detail_dqm_action_flag (
1597 p_entity IN VARCHAR2,
1598 p_batch_id IN number,
1599 p_cur IN EntityCur )
1600 is
1601 l_limit NUMBER := 200;
1602 l_last_fetch BOOLEAN := FALSE;
1603 H_POSR CharList ;
1604 H_POS CharList;
1605
1606 BEGIN
1607
1608 -- LOOP THROUGH THE PASSED IN OPEN CURSOR
1609 LOOP
1610 FETCH p_cur BULK COLLECT INTO
1611 H_POSR,
1612 H_POS
1613 LIMIT l_limit;
1614
1615 IF p_cur%NOTFOUND THEN
1616 l_last_fetch:=TRUE;
1617 END IF;
1618
1619 IF H_POSR.COUNT = 0 and l_last_fetch THEN
1620 EXIT;
1621 END IF;
1622
1623 BEGIN
1624 -- update the corresponding detail interface table
1625 IF p_entity = 'PARTY_SITES'
1626 THEN
1627 FORALL I in H_POSR.FIRST..H_POSR.LAST
1628 UPDATE HZ_IMP_ADDRESSES_INT A
1629 SET A.DQM_ACTION_FLAG = 'P'
1630 WHERE
1631 ( A.SITE_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
1632 A.SITE_ORIG_SYSTEM = H_POS(I) and
1633 A.BATCH_ID = p_batch_id
1634 );
1635
1636 ELSIF p_entity = 'CONTACT_POINTS'
1637 THEN
1638 FORALL I in H_POSR.FIRST..H_POSR.LAST
1639 UPDATE HZ_IMP_CONTACTPTS_INT A
1640 SET A.DQM_ACTION_FLAG = 'P'
1641 WHERE
1642 ( A.CP_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
1643 A.CP_ORIG_SYSTEM = H_POS(I) and
1644 A.BATCH_ID = p_batch_id
1645 );
1646
1647 ELSIF p_entity = 'CONTACTS'
1648 THEN
1649 FORALL I in H_POSR.FIRST..H_POSR.LAST
1650 UPDATE HZ_IMP_CONTACTS_INT A
1651 SET A.DQM_ACTION_FLAG = 'P'
1652 WHERE
1653 ( A.CONTACT_ORIG_SYSTEM_REFERENCE = H_POSR(I) and
1654 A.CONTACT_ORIG_SYSTEM = H_POS(I) and
1655 A.BATCH_ID = p_batch_id
1656 );
1657
1658 END IF;
1659
1660 END;
1661
1662 IF l_last_fetch THEN
1663 EXIT;
1664
1665 END IF;
1666 END LOOP;
1667
1668
1669 ---------- exception block ---------------
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1673 FND_MESSAGE.SET_TOKEN('PROC','UPDATE_DETAIL_DQM_ACTION_FLAG');
1674 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1675 FND_MSG_PUB.ADD;
1676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1677 END;
1678
1679
1680 PROCEDURE final_process_int_tca_dup_id(p_batch_id IN number)
1681 IS
1682 BEGIN
1683 -- Potential would mean party matches that do not pass the Automerge threshold
1684 -- Duplicates would mean party matches that do pass the Automerge threshold
1685
1686 -- update dup party counts to hz_imp_batch_summary table
1687 update hz_imp_batch_summary h
1688 set dup_parties =
1689 (select count (distinct party_id)
1690 from hz_imp_dup_parties
1691 where batch_id = p_batch_id
1692 and auto_merge_flag = 'Y'
1693 )
1694 where
1695 h.batch_id = p_batch_id ;
1696
1697
1698 -- update potential dup party counts to hz_imp_batch_summary table
1699 update hz_imp_batch_summary h
1700 set potential_dup_parties =
1701 (select count (distinct party_id)
1702 from hz_imp_dup_parties
1703 where batch_id = p_batch_id
1704 and auto_merge_flag = 'N'
1705 )
1706 where
1707 h.batch_id = p_batch_id ;
1708
1712 (select count(1) from
1709 -- update potential dup party site counts to hz_imp_batch_summary table
1710 update hz_imp_batch_summary h
1711 set potential_dup_addresses =
1713 (select distinct record_id
1714 from hz_imp_dup_details
1715 where batch_id = p_batch_id
1716 and entity = 'PARTY_SITES'
1717 )
1718 )
1719 where
1720 h.batch_id = p_batch_id ;
1721
1722
1723 -- update potential dup contacts counts to hz_imp_batch_summary table
1724 update hz_imp_batch_summary h
1725 set potential_dup_contacts =
1726 (select count(1) from
1727 (select distinct record_id
1728 from hz_imp_dup_details
1729 where batch_id = p_batch_id
1730 and entity = 'CONTACTS'
1731 )
1732 )
1733 where
1734 h.batch_id = p_batch_id ;
1735
1736
1737 -- update potential contact point counts to hz_imp_batch_summary table
1738 update hz_imp_batch_summary h
1739 set potential_dup_contactpoints =
1740 (select count(1) from
1741 (select distinct record_id
1742 from hz_imp_dup_details
1743 where batch_id = p_batch_id
1744 and entity = 'CONTACT_POINTS'
1745 )
1746 )
1747 where
1748 h.batch_id = p_batch_id ;
1749
1750 EXCEPTION
1751 WHEN OTHERS THEN
1752 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1753 FND_MESSAGE.SET_TOKEN('PROC' ,'FINAL_PROCESS_INT_TCA_DUP_ID');
1754 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1755 FND_MSG_PUB.ADD;
1756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1757
1758 END;
1759
1760
1761 PROCEDURE interface_tca_sanitize_report(
1762 p_batch_id IN NUMBER,
1763 p_match_rule_id IN NUMBER,
1764 p_request_id IN NUMBER,
1765 x_dup_batch_id OUT NOCOPY NUMBER,
1766 x_return_status OUT NOCOPY VARCHAR2,
1767 x_msg_count OUT NOCOPY NUMBER,
1768 x_msg_data OUT NOCOPY VARCHAR2
1769 )
1770 IS
1771 x_party_count NUMBER := 0;
1772 BEGIN
1773 -- initialize API return status to success.
1774 x_return_status := FND_API.G_RET_STS_SUCCESS;
1775
1776 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1777 FND_FILE.put_line(FND_FILE.log,'Entering interface_tca_sanitize_report ');
1778
1779 -- report all the tca parties that find duplicates in the interface
1780 report_int_tca_dup_parties (p_batch_id, p_match_rule_id, p_request_id, x_dup_batch_id, x_party_count);
1781
1782 -- report all the tca parties that find duplicate detail information in interface
1783 report_int_tca_dup_details (p_batch_id, p_match_rule_id, p_request_id, x_dup_batch_id, x_party_count);
1784
1785
1786 EXCEPTION
1787 WHEN OTHERS THEN
1788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1789 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1790 FND_MESSAGE.SET_TOKEN('PROC' ,'INTERFACE_TCA_SANITIZE_REPORT');
1791 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1792 FND_MSG_PUB.ADD;
1793 FND_MSG_PUB.Count_And_Get(
1794 p_encoded => FND_API.G_FALSE,
1795 p_count => x_msg_count,
1796 p_data => x_msg_data);
1797
1798
1799 END;
1800
1801
1802 PROCEDURE report_int_dup_party_osrs (
1803 p_batch_id IN NUMBER
1804 )
1805 IS
1806 BEGIN
1807 -- FIRST, REPORT WINNER OSRS TO INTERFACE DEDUP RESULTS
1808 -- THESE ARE ALL PRECISELY THE ORD_F_OSRs IN HZ_INT_DUP_RESULTS_GT WHICH ARE NOT
1809 -- ORD_T_OSRs OF ANY ROW AND OCCUR AT ODD LEVELS IN THE CONNECT BY.
1810
1811 ----------------------------------------------------
1812 --- EXAMPLE :
1813 -- 1 2 LEVEL 1
1814 -- 2 3 LEVEL 2
1815 -- 3 4 LEVEL 3
1816 -- 1 7 LEVEL 1
1817 -- WOULD RESULT IN 1 AND 2 BEING CHOSEN AS WINNER PARTIES
1818 -- AFTER THE CONNECT BY. SO THE CONNECT BY NOT ONLY CHOOSES
1819 -- THE WINNER, BUT ALSO MAKES SURE THAT ODD LEVELS ARE TAKEN
1820 -- INTO ACCOUNT.
1821 -----------------------------------------------------
1822
1823 -- first insert winner party osrs, with dup osrs being themselves
1824 -- into hz_imp_int_dedup_results
1825
1826 /*
1827 insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
1828 dup_record_osr, dup_record_os, entity,
1829 score, dup_creation_date, dup_last_update_date
1830 ,created_by,creation_date,last_update_login
1831 ,last_update_date,last_updated_by)
1832
1833 select p_batch_id, win_party_osr,win_party_os, win_party_osr, win_party_os,
1834 'PARTY', 0, hz_utility_pub.creation_date, hz_utility_pub.last_update_date
1835 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date
1836 ,hz_utility_v2pub.last_update_login,hz_utility_v2pub.last_update_date
1837 ,hz_utility_v2pub.last_updated_by
1838 from
1839 (
1843 (
1840 select distinct d.ord_f_osr as win_party_osr, d.f_os as win_party_os, level as levelu
1841 from hz_int_dup_results d
1842 start with d.ord_f_osr not in
1844 select c.ord_t_osr
1845 from hz_int_dup_results c
1846 )
1847 connect by prior ord_t_osr = ord_f_osr
1848 )
1849 where mod(levelu, 2) = 1 ;
1850 */
1851
1852 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1853 FND_FILE.put_line(FND_FILE.log,'Parties ::: Reporting winners to HZ_IMP_INT_DEDUP_RESULTS ');
1854 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1855
1856
1857
1858 -- we use the temporary table hz_int_dup_results_gt, instead of
1859 -- hz_int_dup_results
1860 insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
1861 dup_record_osr, dup_record_os, entity,
1862 score, dup_creation_date, dup_last_update_date
1863 ,created_by,creation_date,last_update_login
1864 ,last_update_date,last_updated_by)
1865 select p_batch_id, win_party_osr,win_party_os, win_party_osr, win_party_os,
1866 'PARTY', 0, hz_utility_pub.creation_date, hz_utility_pub.last_update_date
1867 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date
1868 ,hz_utility_v2pub.last_update_login,hz_utility_v2pub.last_update_date
1869 ,hz_utility_v2pub.last_updated_by
1870 from
1871 (
1872 select distinct d.ord_f_osr as win_party_osr, d.f_os as win_party_os, level as levelu
1873 from hz_int_dup_results_gt d
1874 start with d.ord_f_osr not in
1875 (
1876 select c.ord_t_osr
1877 from hz_int_dup_results_gt c
1878 where c.batch_id = p_batch_id
1879 )
1880 and d.batch_id = p_batch_id
1881 connect by prior ord_t_osr = ord_f_osr and prior batch_id = batch_id
1882 )
1883 where mod(levelu, 2) = 1 ;
1884
1885 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of winner parties inserted to HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);
1886 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1887
1888 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
1889 FND_FILE.put_line(FND_FILE.log,'Parties ::: Reporting duplicate parties to HZ_IMP_INT_DEDUP_RESULTS ');
1890 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
1891
1892
1893 -- Take inserted winner party osrs from hz_imp_int_dedup_results , join with
1894 -- hz_int_dup_results_gt and insert the winner, dup pair to hz_imp_int_dedup_results
1895 insert into hz_imp_int_dedup_results ( batch_id, winner_record_osr, winner_record_os,
1896 dup_record_osr, dup_record_os, entity,
1897 score, dup_creation_date, dup_last_update_date
1898 ,created_by,creation_date,last_update_login
1899 ,last_update_date,last_updated_by)
1900 select p_batch_id, a.winner_record_osr,a.winner_record_os, b.ord_t_osr, b.t_os,
1901 'PARTY', b.score, hz_utility_pub.creation_date, hz_utility_pub.last_update_date
1902 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date
1903 ,hz_utility_v2pub.last_update_login,hz_utility_v2pub.last_update_date
1904 ,hz_utility_v2pub.last_updated_by
1905 from hz_imp_int_dedup_results a, hz_int_dup_results_gt b
1906 where a.batch_id = p_batch_id and b.batch_id = p_batch_id and a.entity = 'PARTY'
1907 and b.ord_f_osr = a.winner_record_osr ;
1908
1909 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of duplicate parties inserted to HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);
1910 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1911
1912
1913
1914 FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));
1915
1916 -- Bug Fix 3588873 :: Need to report the import interface table dates for all the duplicates
1917 -- We take of the "PARTY" entity here.
1918 update hz_imp_int_dedup_results a
1919 set (a.dup_creation_date, a.dup_last_update_date)
1920 = (select b.creation_date, b.last_update_date
1921 from hz_imp_parties_int b
1922 where b.batch_id = p_batch_id
1923 and b.party_orig_system_reference = a.dup_record_osr
1924 and b.party_orig_system = a.dup_record_os
1925 )
1926 where a.entity = 'PARTY' and a.batch_id = p_batch_id ;
1927
1928 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of duplicate parties updated in HZ_IMP_INT_DEDUP_RESULTS '||SQL%ROWCOUNT);
1929 FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of update for getting import table dates '||to_char(sysdate,'hh24:mi:ss'));
1930
1931 --bug 5393826
1932 FND_FILE.put_line(FND_FILE.log,'Parties ::: Deleting Party Duplicate sets for which no records exist in import party interface table '||to_char(sysdate,'hh24:mi:ss'));
1933
1934 delete from hz_imp_int_dedup_results a
1935 where a.entity = 'PARTY'
1936 and a.batch_id = p_batch_id
1937 and not exists
1938 ( select 1
1939 from hz_imp_parties_int b
1940 where a.batch_id = b.batch_id
1941 and a.winner_record_osr = b.party_orig_system_reference
1942 and a.winner_record_os = b.party_orig_system
1946 FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of rows deleted is ' || SQL%ROWCOUNT );
1943 );
1944
1945 FND_FILE.put_line(FND_FILE.log,'Parties ::: Delete Complete '||to_char(sysdate,'hh24:mi:ss'));
1947 --bug 5393826
1948
1949
1950 EXCEPTION
1951 WHEN OTHERS THEN
1952 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1953 FND_MESSAGE.SET_TOKEN('PROC' ,'REPORT_INT_DUP_PARTY_OSRS');
1954 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
1955 FND_MSG_PUB.ADD;
1956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1957 END ;
1958
1959
1960
1961 PROCEDURE interface_dup_id_worker(
1962 p_batch_id IN number,
1963 p_match_rule_id IN number,
1964 p_from_osr IN VARCHAR2,
1965 p_to_osr IN VARCHAR2,
1966 x_return_status OUT NOCOPY VARCHAR2,
1967 x_msg_count OUT NOCOPY NUMBER,
1968 x_msg_data OUT NOCOPY VARCHAR2
1969 )
1970 IS
1971 x_threshold number;
1972 ret_value number;
1973 anon_str varchar2(255);
1974 l_pkg_name varchar2(2000);
1975 BEGIN
1976 -- initialize API return status to success.
1977 x_return_status := FND_API.G_RET_STS_SUCCESS;
1978
1979 -- get the threshold
1980 select match_score into x_threshold
1981 from hz_match_rules_vl
1982 where match_rule_id = p_match_rule_id;
1983
1984 -- get the match rule package
1985 l_pkg_name := 'HZ_IMP_MATCH_RULE_'||p_match_rule_id;
1986
1987 -- call the corresponding function in this match rule package, to join based on entities
1988 -- dictated by the match rule and do the following
1989 -- 1. report party dup results to hz_int_dup_results
1990 -- 2. report detail dup results directly to hz_imp_int_dedup_results
1991
1992 anon_str := 'begin ' || l_pkg_name || '.interface_join_entities(:p_batch_id,' ||
1993 ':p_from_osr,:p_to_osr,:x_threshold); end;' ;
1994
1995 EXECUTE IMMEDIATE anon_str USING p_batch_id, p_from_osr, p_to_osr, x_threshold ;
1996
1997 commit;
1998
1999
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 -- dbms_output.put_line('err '||SQLERRM);
2003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2005 FND_MESSAGE.SET_TOKEN('PROC' ,'INTERFACE_DUP_ID_WORKER');
2006 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
2007 FND_MSG_PUB.ADD;
2008 FND_MSG_PUB.Count_And_Get(
2009 p_encoded => FND_API.G_FALSE,
2010 p_count => x_msg_count,
2011 p_data => x_msg_data);
2012
2013 END ;
2014
2015
2016 PROCEDURE interface_sanitize_report(
2017 p_batch_id IN NUMBER,
2018 p_match_rule_id IN NUMBER,
2019 x_return_status OUT NOCOPY VARCHAR2,
2020 x_msg_count OUT NOCOPY NUMBER,
2021 x_msg_data OUT NOCOPY VARCHAR2
2022 )
2023 IS
2024 x_threshold number;
2025 BEGIN
2026 -- initialize API return status to success.
2027 x_return_status := FND_API.G_RET_STS_SUCCESS;
2028
2029 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
2030 FND_FILE.put_line(FND_FILE.log,'Entering interface_sanitize_report ');
2031
2032
2033
2034 -- get the threshold for the match rule
2035 select match_score into x_threshold
2036 from hz_match_rules_vl
2037 where match_rule_id = p_match_rule_id;
2038
2039 -- sanitize party osrs
2040 sanitize_int_dup_party_osrs (x_threshold, p_batch_id);
2041
2042 -- report party osrs
2043 report_int_dup_party_osrs (p_batch_id);
2044
2045 -- sanitize detail osrs
2046 sanitize_int_dup_detail_osrs (p_batch_id);
2047
2048 -- do final processing
2049 final_process_int_dup_id(p_batch_id);
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2053 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2054 FND_MESSAGE.SET_TOKEN('PROC' ,'INTERFACE_SANITIZE_REPORT');
2055 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
2056 FND_MSG_PUB.ADD;
2057 FND_MSG_PUB.Count_And_Get(
2058 p_encoded => FND_API.G_FALSE,
2059 p_count => x_msg_count,
2060 p_data => x_msg_data);
2061
2062
2063 END;
2064 ----------------------------------------------------------------------------------------------------------
2065 ----------------------------------------------------------------------------------------------------------
2066 -- MATH RULE COMPILATION
2067 ----------------------------------------------------------------------------------------------------------
2068 ----------------------------------------------------------------------------------------------------------
2069
2070
2071 /**
2072 * PROCEDURE compile_match_rule
2073 *
2074 * DESCRIPTION
2075 *
2076 *
2077 *
2078 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2079 *
2080 *
2081 * ARGUMENTS
2082 *
2083 *
2084 * NOTES
2085 *
2086 * MODIFICATION HISTORY
2087 *
2091
2088 * 04-09-2003 Colathur Vijayan o Created.
2089 *
2090 */
2092 --------------------------------------------------------------------------------------
2093 -- compile_match_rule ::: This procedure generates a compiled PLSQL package
2094 -- spec and body for the given Match Rule (p_match_rule_id).
2095 -- The name of the generated match rule is
2096 -- HZ_IMP_MATCH_RULE_<p_match_rule_id>.
2097 --------------------------------------------------------------------------------------
2098
2099 PROCEDURE compile_match_rule (
2100 p_match_rule_id IN NUMBER,
2101 x_return_status OUT NOCOPY VARCHAR2,
2102 x_msg_count OUT NOCOPY NUMBER,
2103 x_msg_data OUT NOCOPY VARCHAR2
2104 ) IS
2105
2106 CURSOR check_null_set IS
2107 SELECT DISTINCT a.entity_name
2108 FROM hz_match_rule_secondary s, hz_trans_attributes_vl a
2109 WHERE a.attribute_id = s.attribute_id
2110 AND s.match_rule_id = p_match_rule_id
2111 MINUS
2112 SELECT DISTINCT a.entity_name
2113 FROM hz_match_rule_primary p, hz_trans_attributes_vl a
2114 WHERE a.attribute_id = p.attribute_id
2115 AND p.match_rule_id = p_match_rule_id;
2116
2117 CURSOR check_inactive IS
2118 SELECT 1
2119 FROM hz_match_rule_primary p, hz_primary_trans pt, hz_trans_functions_vl f
2120 WHERE p.match_rule_id = p_match_rule_id
2121 AND pt.PRIMARY_ATTRIBUTE_ID = p.PRIMARY_ATTRIBUTE_ID
2122 AND f.function_id = pt.function_id
2123 AND nvl(f.ACTIVE_FLAG,'Y') = 'N'
2124 UNION
2125 SELECT 1
2126 FROM hz_match_rule_secondary s, hz_secondary_trans pt, hz_trans_functions_vl f
2127 WHERE s.match_rule_id = p_match_rule_id
2128 AND pt.SECONDARY_ATTRIBUTE_ID = s.SECONDARY_ATTRIBUTE_ID
2129 AND f.function_id = pt.function_id
2130 AND nvl(f.ACTIVE_FLAG,'Y') = 'N';
2131
2132 -- Local variable declarations
2133 l_tmp VARCHAR2(255);
2134 l_batch_flag VARCHAR2(1);
2135 l_package_name VARCHAR2(2000);
2136
2137 BEGIN
2138
2139 --Initialize API return status to success.
2140 x_return_status := FND_API.G_RET_STS_SUCCESS;
2141
2142 -- Initialize the compiled package name
2143 l_package_name := 'HZ_IMP_MATCH_RULE_'||p_match_rule_id;
2144
2145 -- Initialize message stack
2146 FND_MSG_PUB.initialize;
2147
2148 BEGIN
2149 -- Verify that the match rule exists
2150 SELECT 1 INTO l_batch_flag
2151 FROM HZ_MATCH_RULES_VL
2152 WHERE match_rule_id = p_match_rule_id;
2153
2154 EXCEPTION
2155 WHEN NO_DATA_FOUND THEN
2156 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_NO_RULE');
2157 FND_MSG_PUB.ADD;
2158 RAISE FND_API.G_EXC_ERROR;
2159 END;
2160
2161
2162 BEGIN
2163
2164 /* Added to check that acquisition has at least one attribute for each entity defined in scoring.
2165 Added update statements since compile_match_rule is public api and commented unnecessary updates in
2166 compile_all_rules and compile_all_rules_nolog.
2167 */
2168 OPEN check_null_set;
2169 FETCH check_null_set INTO l_tmp;
2170 IF check_null_set%FOUND THEN
2171 CLOSE check_null_set;
2172 BEGIN
2173 EXECUTE IMMEDIATE 'DROP PACKAGE HZ_IMP_MATCH_RULE_'||p_match_rule_id;
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 NULL;
2177 END;
2178 fnd_message.set_name('AR','HZ_SCORING_NO_ACQUISITION');
2179 FND_MSG_PUB.ADD;
2180 RAISE FND_API.G_EXC_ERROR;
2181 END IF;
2182 CLOSE check_null_set;
2183 END;
2184
2185 /* Check if match rule has any inactive transformations */
2186 OPEN check_inactive;
2187 FETCH check_inactive INTO l_tmp;
2188 IF check_inactive%FOUND THEN
2189 CLOSE check_inactive;
2190 BEGIN
2191 EXECUTE IMMEDIATE 'DROP PACKAGE HZ_IMP_MATCH_RULE_'||p_match_rule_id;
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 NULL;
2195 END;
2196
2197 fnd_message.set_name('AR','HZ_MR_HAS_INACTIVE_TX');
2198 FND_MSG_PUB.ADD;
2199 RAISE FND_API.G_EXC_ERROR;
2200 END IF;
2201 CLOSE check_inactive;
2202
2203 -- Generate and compile match rule package spec
2204 HZ_GEN_PLSQL.new(l_package_name, 'PACKAGE');
2205 HZ_DQM_MR_PVT.gen_pkg_spec(l_package_name, p_match_rule_id);
2206 HZ_GEN_PLSQL.compile_code;
2207
2208 -- Generate and compile match rule package body
2209 HZ_GEN_PLSQL.new(l_package_name, 'PACKAGE BODY');
2210 HZ_DQM_MR_PVT.gen_pkg_body_tca_join(l_package_name, p_match_rule_id);
2211 HZ_DQM_MR_PVT.gen_pkg_body_int_tca_join(l_package_name, p_match_rule_id);
2212 HZ_DQM_MR_PVT.gen_pkg_body_int_join(l_package_name, p_match_rule_id);
2213 HZ_DQM_MR_PVT.gen_footer;
2214 HZ_GEN_PLSQL.compile_code;
2215
2216
2217 --Standard call to get message count and if count is 1, get message info.
2218 FND_MSG_PUB.Count_And_Get(
2219 p_encoded => FND_API.G_FALSE,
2220 p_count => x_msg_count,
2221 p_data => x_msg_data);
2222
2223 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'C' WHERE MATCH_RULE_ID = p_match_rule_id;
2224 COMMIT;
2225 EXCEPTION
2226 WHEN FND_API.G_EXC_ERROR THEN
2227 FND_MSG_PUB.Count_And_Get(
2228 p_encoded => FND_API.G_FALSE,
2229 p_count => x_msg_count,
2230 p_data => x_msg_data);
2231 x_return_status := FND_API.G_RET_STS_ERROR;
2232 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = p_match_rule_id;
2233 COMMIT;
2234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2235 FND_MSG_PUB.Count_And_Get(
2236 p_encoded => FND_API.G_FALSE,
2237 p_count => x_msg_count,
2241 COMMIT;
2238 p_data => x_msg_data);
2239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2240 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = p_match_rule_id;
2242 WHEN OTHERS THEN
2243
2244 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2245 FND_MESSAGE.SET_TOKEN('PROC','compile_match_rule');
2246 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2247 FND_MSG_PUB.ADD;
2248
2249 FND_MSG_PUB.Count_And_Get(
2250 p_encoded => FND_API.G_FALSE,
2251 p_count => x_msg_count,
2252 p_data => x_msg_data);
2253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2254 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = p_match_rule_id;
2255 COMMIT;
2256 END;
2257
2258
2259 /**********************************************************************************
2260 **********************************************************************************
2261 WE SHALL STUB THESE OUT NOW AND USE THESE ALGOS , IN THE FUTURE WHEN WE DEAL WITH
2262 TRANSITIVITY, FOR AUTO MERGE !!!!!!!!!!!
2263 **********************************************************************************
2264
2265 ------------------------------
2266 -- identify_dup_pairs
2267 -----------------------------
2268
2269 FUNCTION identify_dup_pairs
2270 RETURN NUMBER
2271 IS
2272 l_yn number ;
2273 BEGIN
2274 -- Update all intersecting dup pairs viz, (a,b) intersects with (c,d) if one of
2275 -- a = c, a = d, b = c, b = d is true.
2276 update hz_dup_results a
2277 set flag = 'D'
2278 where exists
2279 (select 1
2280 from hz_dup_results b
2281 where ( a.fid = b.fid and a.tid <> b.tid)
2282 or (a.tid = b.tid and a.fid <> b.fid)
2283 or (a.fid = b.tid)
2284 or (a.tid = b.fid)
2285 ) ;
2286 return 0 ;
2287 END identify_dup_pairs ;
2288
2289
2290 PROCEDURE sanitize_dup_parties_future (
2291 p_threshold IN NUMBER,
2292 p_init_msg_list IN VARCHAR2,
2293 x_return_status OUT NOCOPY VARCHAR2,
2294 x_msg_count OUT NOCOPY NUMBER,
2295 x_msg_data OUT NOCOPY VARCHAR2
2296 )
2297 IS
2298 BEGIN
2299
2300 ----------------------------------------------------------------------------------------------
2301 -- Step 1: SANITIZE THE OCCURENCE OF ALL THE REVERSED PAIRS FROM STEP 1
2302 -- AND ALSO APPLY THE THRESHOLD.
2303 ----------------------------------------------------------------------------------------------
2304
2305 -- At the end of this, we would have the following:
2306 -- The pair with fid < tid, if both the pairs have the same score
2307 -- The pair with the higher score, if in case, the pairs of different scores.
2308
2309
2310 -- delete the smallest source, if scores are equal
2311 delete from hz_dup_results a
2312 where
2313 a.score < p_threshold
2314 or
2315 (exists
2316 (Select 1 from hz_dup_results b
2317 where a.fid=b.tid and b.fid=a.tid and a.score = b.score)
2318 and a.fid < a.tid );
2319
2320 -- delete the one with the smallest score, if scores are not equal
2321 delete from hz_dup_results a
2322 where
2323 a.score < p_threshold
2324 or
2325 exists
2326 (Select 1 from hz_dup_results b
2327 where a.fid=b.tid and b.fid=a.tid and a.score < b.score) ;
2328
2329 ------------------------------------------------------------------------------------------------------
2330 -- Step 2 - IDENTIFY DUP SETS AND REPORT THEM APPROPRIATELY, TO ANOTHER TEMP TABLE
2331 -- THAT WILL BE THE BASIS FOR REPORTING TO HZ_DUP_SETS AND HZ_DUP_SET_PARTIES
2332 ------------------------------------------------------------------------------------------------------
2333
2334 -- Now call the stored program
2335 identify_dup_sets('T',x_return_status,x_msg_count,x_msg_data);
2336
2337 END;
2338
2339
2340
2341
2342 FUNCTION find_duplicates (p_fid number, p_tid number, p_dup_set_id number)
2343 RETURN NUMBER
2344 IS
2345 ret_val number;
2346 master_party_id varchar2(2000) ;
2347 rowCount number ;
2348 x_dup_set_id number;
2349
2350 CURSOR master_dup_cur
2351 IS
2352 -- Get a row that intersects with the passed in fid and tid and already has
2353 -- the stamp of the master party id
2354 select a.fid, a.tid , a.flag, a.dup_set_id
2355 from hz_dup_results a
2356 where (a.flag <> 'D') and
2357 (a.fid = p_fid or a.fid = p_tid or a.tid = p_fid or a.tid = p_tid) and
2358 rownum = 1
2359 order by a.flag ;
2360
2361 master_dup_cur_rec master_dup_cur%rowtype;
2362
2363 BEGIN
2364 rowCount := 0;
2365
2366 -- Find the Master if it exists
2367 OPEN master_dup_cur ;
2368 LOOP
2369 FETCH master_dup_cur INTO master_dup_cur_rec;
2370 -- if you cannot find anything, get the hell out of here.
2371 EXIT WHEN master_dup_cur%NOTFOUND ;
2372
2373 -- If you can get this far, store the following,
2374 -- in order to use them, in the logic, outside the loop.
2375 master_party_id := master_dup_cur_rec.flag ;
2376 x_dup_set_id := master_dup_cur_rec.dup_set_id ;
2377 rowCount := rowCount + 1;
2378 END LOOP;
2379 CLOSE master_dup_cur;
2380
2381 -- If Master does not exist, make the passed in fid to be the Master and
2382 -- create the dup set id from sequence
2383 IF rowCount = 0
2384 THEN
2385 master_party_id := to_char(p_fid);
2389
2386 -- get the sequence from HZ_DUP_SETS
2387 SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;
2388 END IF;
2390 -- Stamp all intersecting rows (including the passed in row itself), that still have a 'D' flag,
2391 -- with the stamp of the Master and also populate the dup set id column
2392 update hz_dup_results a
2393 set flag = master_party_id, dup_set_id = x_dup_set_id
2394 where (a.flag = 'D') and
2395 (a.fid = p_fid or a.fid = p_tid or a.tid = p_fid or a.tid = p_tid) ;
2396
2397 return 0 ;
2398 END find_duplicates ;
2399
2400 ------------------------------
2401 -- stamp_trivial_dup_sets
2402 -----------------------------
2403
2404 FUNCTION stamp_trivial_dup_sets
2405 RETURN NUMBER
2406 IS
2407 BEGIN
2408
2409 -- Stamp the dup set id column of all rows with flag = 'ND', with sequence obtained from
2410 -- HZ_DUP_SETS
2411 update hz_dup_results a
2412 set dup_set_id = HZ_MERGE_BATCH_S.nextval
2413 where a.flag = 'ND' ;
2414 return 0 ;
2415 END stamp_trivial_dup_sets ;
2416
2417
2418 ------------------------------
2419 -- stamp_non_trivial_dup_sets
2420 -----------------------------
2421
2422 FUNCTION stamp_non_trivial_dup_sets
2423 RETURN NUMBER
2424 IS
2425 ret_val number ;
2426 rowCount number;
2427 temp_fid number;
2428 temp_tid number;
2429 temp_dup_set_id number;
2430 temp_rowid rowid;
2431 CURSOR dup_set_cur
2432 IS
2433 select fid, tid, dup_set_id, rowid
2434 from hz_dup_results
2435 where flag = 'D'
2436 and rownum = 1
2437 order by flag ;
2438
2439 dup_set_cur_rec dup_set_cur%rowtype;
2440 BEGIN
2441
2442 rowCount := 0 ;
2443
2444
2445 -- Cursor gets at most one row or no rows.
2446 -- Also, note that the cursor would not return any row, that is stamped as 'ND' --
2447 -- the ones for which the dupset, need not have to be formed, explicitly.
2448 OPEN dup_set_cur ;
2449 LOOP
2450 FETCH dup_set_cur INTO dup_set_cur_rec;
2451 EXIT WHEN dup_set_cur%NOTFOUND;
2452
2453 -- If you can get this far, do the following
2454 -- in order to use in the logic outside the loop.
2455
2456 temp_fid := dup_set_cur_rec.fid;
2457 temp_tid := dup_set_cur_rec.tid;
2458 temp_dup_set_id := dup_set_cur_rec.dup_set_id;
2459 temp_rowid := dup_set_cur_rec.rowid;
2460 rowCount := rowCount + 1;
2461
2462 END LOOP;
2463 CLOSE dup_set_cur ;
2464
2465 -- Do Recursion, only when the rowcount is atleast 1
2466 -- No point in doing anything, if we don't find any rows with the 'D' flag.
2467 IF rowCount > 0
2468 THEN
2469 -- starting from the fetched row, identify all duplicates and form the dup set.
2470 -- note that find_duplicates, will find all intersecting rows corresponding to
2471 -- the fetched row. since dup sets may be spanned across different rows, it is
2472 -- quite possible for find_duplicates to find the actual dupset in multiple fetches.
2473 ret_val := find_duplicates(temp_fid, temp_tid, temp_dup_set_id );
2474
2475 -- recursion continues for finding the next dupset
2476 ret_val := stamp_non_trivial_dup_sets ;
2477 END IF;
2478
2479 return 0 ;
2480 END stamp_non_trivial_dup_sets ;
2481
2482
2483 ------------------------------
2484 -- report_duplicates
2485 -----------------------------
2486
2487 FUNCTION report_duplicates (p_batch_id number, p_match_rule_id number)
2488 RETURN NUMBER
2489 IS
2490 x_auto_merge_threshold number;
2491 BEGIN
2492
2493 x_auto_merge_threshold := get_auto_merge_threshold(p_match_rule_id);
2494
2495 ------------------------------------------------------
2496 -- Step1: Report trivial dup sets
2497 ------------------------------------------------------
2498
2499 -- Insert winner party into HZ_DUP_SETS
2500 insert into hz_dup_sets ( dup_set_id, dup_batch_id, winner_party_id,
2501 status, merge_type, created_by, creation_date, last_update_login,
2502 last_update_date, last_updated_by)
2503 select dup_set_id, p_batch_id, fid,
2504 'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
2505 hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
2506 hz_utility_pub.user_id
2507 from hz_dup_results
2508 where flag = 'ND' ;
2509
2510 -- Insert winner and its only duplicate, into HZ_DUP_SET_PARTIES
2511 insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
2512 merge_batch_id,score,merge_flag,created_by,creation_date,last_update_login,
2513 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
2514 select fid, dup_set_id, 0, p_batch_id, score, decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2515 hz_utility_pub.created_by,hz_utility_pub.creation_date,
2516 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2517 hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2518 from hz_dup_results
2519 where flag = 'ND';
2520
2521 insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
2522 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
2523 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
2524 select tid, dup_set_id, 0,p_batch_id, score,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2525 hz_utility_pub.created_by,hz_utility_pub.creation_date,
2526 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2527 hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2528 from hz_dup_results
2529 where flag = 'ND';
2530
2531 ------------------------------------------------------
2532 -- Step2: Report non trivial dup sets
2533 ------------------------------------------------------
2534
2535 -- Insert winner party into HZ_DUP_SETS
2536 insert into hz_dup_sets ( dup_set_id, dup_batch_id, winner_party_id,
2537 status, merge_type, created_by, creation_date, last_update_login,
2538 last_update_date, last_updated_by)
2539 select distinct dup_set_id, p_batch_id, flag ,
2540 'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
2541 hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
2542 hz_utility_pub.user_id
2543 from hz_dup_results
2544 where flag <> 'ND' ;
2545
2546 -- Insert winner party and all its duplicates into HZ_DUP_SET_PARTIES
2547
2548 insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
2549 merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,
2550 last_update_date,last_updated_by,dup_set_batch_id) --Bug No: 4244529
2551 select distinct to_number(flag), dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2552 hz_utility_pub.created_by,hz_utility_pub.creation_date,
2553 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2554 hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2555 from hz_dup_results
2556 where flag <> 'ND'
2557 union
2558 select distinct fid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2559 hz_utility_pub.created_by,hz_utility_pub.creation_date,
2560 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2561 hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2562 from hz_dup_results
2563 where flag <> 'ND'
2564 union
2565 select distinct tid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2566 hz_utility_pub.created_by,hz_utility_pub.creation_date,
2567 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2568 hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2569 from hz_dup_results
2570 where flag <> 'ND' ;
2571
2572 return 0 ;
2573 END report_duplicates ;
2574
2575
2576 /**
2577 * PROCEDURE identify_dup_sets
2578 *
2579 * DESCRIPTION
2580 *
2581 *
2582 *
2583 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2584 *
2585 *
2586 * ARGUMENTS
2587 *
2588 *
2589 * NOTES
2590 *
2591 * MODIFICATION HISTORY
2592 *
2593 * 04-09-2003 Colathur Vijayan o Created.
2594 *
2595
2596
2597 --------------------------------------------------------------------------------------
2598 -- identify_dup_sets ::: This procedure would take a table containing pairs of duplicate
2599 -- party ids, create dupsets by clusteriing all duplicates under
2600 -- a master party id, by a smart update of an existing column
2601 --------------------------------------------------------------------------------------
2602 PROCEDURE identify_dup_sets (
2603 -- input parameters
2604 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2605 -- output parameters
2606 x_return_status OUT NOCOPY VARCHAR2,
2607 x_msg_count OUT NOCOPY NUMBER,
2608 x_msg_data OUT NOCOPY VARCHAR2
2609 ) IS
2610 ret_value NUMBER;
2611
2612 BEGIN
2613
2614 -- Identify all duplicate pairs, by stamping them with flag 'D'
2615 ret_value := identify_dup_pairs ;
2616
2617
2618 -- Identify all non-trivial dupsets, by stamping them with flag = master party id of the dup set
2619 -- and the dup set id column with the sequence generated from HZ_DUP_SETS
2620 ret_value := stamp_non_trivial_dup_sets ;
2621
2622 -- Stamp the dup set id column of all trivial dup sets with the sequence
2623 -- generated from HZ_DUP_SETS
2624 ret_value := stamp_trivial_dup_sets ;
2625
2626
2627 -- standard call to get message count and if count is 1, get message info.
2628 FND_MSG_PUB.Count_And_Get(
2629 p_encoded => FND_API.G_FALSE,
2630 p_count => x_msg_count,
2631 p_data => x_msg_data);
2632
2633
2634 END identify_dup_sets ;
2635 *********************************************************************************************************************/
2636
2637
2638 END; -- Package Body HZ_DQM_DUP_ID_PKG
[Home] [Help]