1 PACKAGE BODY HZ_DQM_MR_PVT AS
2 /* $Header: ARHDIMRB.pls 120.18 2006/07/21 06:24:49 rarajend noship $ */
3 ----------------------------------------------------------------------------------------------------------------
4 ----------------------------------------------------------------------------------------------------------------
5 --- UTILITY FUNCTIONS FOR GETTING MATCH RULE INFO
6 ----------------------------------------------------------------------------------------------------------------
7 ----------------------------------------------------------------------------------------------------------------
8
9
10 TYPE score_rec_type IS RECORD (
11 sum_score NUMBER,
12 max_score NUMBER,
13 min_score NUMBER
14 );
15
16
17 /*
18 -- just to debug execute immediate
19 -- if in case we use dynamic sql in our match rules
20 PROCEDURE sl(str VARCHAR2) IS
21 refstr1 varchar2(32000);
22 refstr2 varchar2(32000);
23 refstr3 varchar2(32000);
24 refstr4 varchar2(32000);
25 refstr5 varchar2(32000);
26 refstr6 varchar2(32000);
27 refstr7 varchar2(32000);
28 refstr8 varchar2(32000);
29
30
31 BEGIN
32 -- SPIT OUT ONLY IF DEBUG FLAG IS ON
33 IF debug_flag = 'Y'
34 THEN
35 refstr1 := replace(str,'''''','#');
36 refstr2 := replace(refstr1,'''');
37 refstr3 := replace(refstr2,'|| #%#', '@');
38 refstr4 := replace(refstr3,'||');
39 refstr5 := replace(refstr4,'@',' || ''%''');
40 refstr6 := replace(refstr5,'#','''');
41 refstr7 := replace(refstr6,'party_join_str');
42 refstr8 := replace(refstr7,'subset_sql');
43 dbms_output.put_line(refstr8);
44 END IF;
45
46 HZ_GEN_PLSQL.add_line(str);
47 END;
48 */
49
50 -- Alias dbms.put_line, to avoid typing
51 PROCEDURE l(str VARCHAR2) IS
52 BEGIN
53 HZ_GEN_PLSQL.add_line(str);
54 END;
55
56
57 FUNCTION get_misc_scores(p_match_rule_id number)
58 return score_rec_type
59 IS
60 CURSOR c0
61 IS
62 select sum(sc) sum_score , max(sc) max_score , min(sc) min_score
63 from
64 (select entity_name ename, sum(score) sc
65 from hz_trans_attributes_vl a, hz_match_rule_secondary s
66 where s.match_rule_id = p_match_rule_id
67 and s.attribute_id = a.attribute_id
68 group by entity_name
69 order by sum(score) desc
70 );
71 srt score_rec_type ;
72 BEGIN
73 -- initialise the record type , just to make sure
74 srt.sum_score := 0 ;
75 srt.max_score := 0 ;
76 srt.min_score := 0 ;
80 srt.sum_score := score_rec.sum_score;
77
78 FOR score_rec IN c0
79 LOOP
81 srt.max_score := score_rec.max_score;
82 srt.min_score := score_rec.min_score;
83 END LOOP;
84 return srt ;
85 END;
86
87 FUNCTION get_match_threshold (p_match_rule_id number)
88 RETURN number
89 IS
90 CURSOR c0
91 IS
92 select match_score
93 from hz_match_rules_vl
94 where match_rule_id = p_match_rule_id;
95 l_yn number ;
96 result VARCHAR2(1);
97 BEGIN
98 OPEN c0;
99 FETCH c0 INTO l_yn;
100 CLOSE c0;
101 RETURN l_yn ;
102 END get_match_threshold ;
103
104
105 FUNCTION get_auto_merge_threshold (p_match_rule_id number)
106 RETURN number
107 IS
108 CURSOR c0
109 IS
110 select auto_merge_score
111 from hz_match_rules_vl
112 where match_rule_id = p_match_rule_id;
113 l_yn number ;
114 result VARCHAR2(1);
115 BEGIN
116 OPEN c0;
117 FETCH c0 INTO l_yn;
118 CLOSE c0;
119 RETURN l_yn ;
120 END get_auto_merge_threshold ;
121
122
123 FUNCTION get_match_all_flag (p_match_rule_id number)
124 RETURN varchar2
125 IS
126 CURSOR c0
127 IS
128 select match_all_flag
129 from hz_match_rules_vl
130 where match_rule_id = p_match_rule_id;
131 result VARCHAR2(1);
132 BEGIN
133 OPEN c0;
134 FETCH c0 INTO result ;
135 CLOSE c0;
136 RETURN result ;
137 END get_match_all_flag ;
138
139
140 FUNCTION has_party_filter_attributes (p_match_rule_id number)
141 RETURN varchar2
142 IS
143 result VARCHAR2(1) := 'N' ;
144 filter_count number := 0;
145 BEGIN
146
147 SELECT count(1) into filter_count
148 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
149 where p.match_rule_id=p_match_rule_id
150 and p.attribute_id=a.attribute_id
151 and a.entity_name = 'PARTY'
152 and p.filter_flag = 'Y';
153 IF filter_count > 0
154 THEN
155 result := 'Y';
156 END IF;
157
158 return result;
159 END has_party_filter_attributes ;
160
161 FUNCTION has_entity_filter_attributes (p_match_rule_id number, p_entity_name in varchar2)
162 RETURN varchar2
163 IS
164 result VARCHAR2(1) := 'N' ;
165 filter_count number := 0;
166 BEGIN
167
168 SELECT count(1) into filter_count
169 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
170 where p.match_rule_id=p_match_rule_id
171 and p.attribute_id=a.attribute_id
172 and a.entity_name = p_entity_name
173 and p.filter_flag = 'Y';
174 IF filter_count > 0
175 THEN
176 result := 'Y';
177 END IF;
178
179 return result;
180 END has_entity_filter_attributes ;
181
182 -- will return true if this match rule has scoring attributes, for the passed in entity
183 FUNCTION has_scoring_attributes ( p_match_rule_id IN NUMBER, p_entity_name IN VARCHAR2)
184 RETURN BOOLEAN
185 IS
186 temp BOOLEAN := FALSE ;
187 BEGIN
188 FOR attrs in (
189 SELECT s.attribute_id
190 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
191 where a.attribute_id=s.attribute_id
192 and s.match_rule_id=p_match_rule_id
193 and a.entity_name = p_entity_name
194 )
195 LOOP
196 temp := TRUE ;
197 END LOOP;
198 return temp ;
199 END has_scoring_attributes ;
200
201
202 -- will return true if this match rule has any scoring attributes what so ever
203 FUNCTION has_scoring_attributes ( p_match_rule_id IN NUMBER)
204 RETURN BOOLEAN
205 IS
206 temp BOOLEAN := FALSE ;
207 BEGIN
208 IF has_scoring_attributes ( p_match_rule_id, 'PARTY')
209 OR
210 has_scoring_attributes ( p_match_rule_id, 'PARTY_SITES')
211 OR
212 has_scoring_attributes ( p_match_rule_id, 'CONTACTS')
213 OR
214 has_scoring_attributes ( p_match_rule_id, 'CONTACT_POINTS')
215 THEN
216 temp := TRUE;
217 return temp ;
218 END IF;
219 return temp;
220 END has_scoring_attributes ;
221
222 -------------------------------------------------------------------------
223 -- get_insert_threshold : This will return the threshold that needs
224 -- to be exceeded by every party level dup identification insert statement
225 -- in the generated code for the match rule
226 -------------------------------------------------------------------------
227
228
229 FUNCTION get_insert_threshold ( p_match_rule_id NUMBER)
230 return number
231 IS
232 CURSOR entity_cur IS
233 select entity_name, sum(score) sc
234 from hz_trans_attributes_vl a, hz_match_rule_secondary s
235 where s.match_rule_id = p_match_rule_id
236 and s.attribute_id = a.attribute_id
237 group by entity_name
238 order by sum(score) desc ;
239 srt score_rec_type;
240 threshold number;
241 row_count number;
242 match_score number;
243 no_of_entities number;
244 match_all_flag varchar2(1);
245 insert_threshold number;
246 BEGIN
247
248
249
250 -- Get the threshold and match_all_flag
251 select match_score, match_all_flag into match_score, match_all_flag
252 from hz_match_rules_vl
253 where match_rule_id = p_match_rule_id;
254
255 -- threhold for the most part is the match-score, except that
256 -- we would change it according to the match_all_flag
257 -- to force inserts and updates in a certain way
258 threshold := match_score;
259
260 -- we want to capture the insert threshold for insert statements
261 -- insert_threshold = (match score - sum of update entity scores)
262 -- first, we initialize it by making it as big as the match score itself
263 insert_threshold := match_score ;
264
265 -- Get the different aggregates that would help in determining the template
266 -- that need to be used -- UNION/UPDATE for the corresponding entity.
267 srt := get_misc_scores(p_match_rule_id);
268
269 -- Initialize the number of entities
270 no_of_entities := 0;
271
272 -- Get the number of entities
273 FOR c0 in entity_cur
274 LOOP
275 no_of_entities := no_of_entities + 1;
276 END LOOP;
277
278
279 -- Before generating the code for the given match rule, we look at the
280 -- match_all_flag to determine, the structure of the code that needs
281 -- to go into the generated match rule package.
282 -- The flag is always assumed to be 'N' by default ie., a match on ANY of the
283 -- entities, would be given consideration.
284 -- If flag = 'Y', then we need to make sure that every query after the first
285 -- one is an update. We make this happen by manually setting the threshold.
286
287 IF match_all_flag = 'Y'
288 THEN
289 threshold := srt.sum_score;
290 END IF;
291
292 -- Open the entity cursor, determine the templates (INSERT/UPDATE) for each
293 -- entity and keep substracting the score for every update entity
294 -- from insert_threshold
295 row_count := 0;
296
297 FOR entity_cur_rec in entity_cur
298 LOOP
299 row_count := row_count + 1;
300 IF row_count = 2
301 THEN
302 IF (srt.sum_score - srt.max_score - threshold) < 0
303 THEN
304 insert_threshold := insert_threshold - entity_cur_rec.sc ;
305 END IF;
306 END IF;
307
308 IF row_count = 3
309 THEN
310 IF no_of_entities = 3
311 THEN
312 IF (entity_cur_rec.sc - threshold) < 0
313 THEN
314 insert_threshold := insert_threshold - entity_cur_rec.sc ;
315 END IF;
316
317 ELSE
318 IF ( entity_cur_rec.sc + srt.min_score - threshold) < 0
319 THEN
320 insert_threshold := insert_threshold - entity_cur_rec.sc ;
321 END IF;
322 END IF;
323 END IF;
324
325 IF row_count = 4
326 THEN
327 IF (entity_cur_rec.sc - threshold) < 0
328 THEN
329 insert_threshold := insert_threshold - entity_cur_rec.sc ;
330 END IF;
331 END IF;
332
333 END LOOP;
334
335 return insert_threshold;
336 END;
337
338 -----------------------------------------------------------------------------------------------
339 -----------------------------------------------------------------------------------------------
340 -- MATCH RULE GENERATION FOR SPEC
341 -----------------------------------------------------------------------------------------------
342 -----------------------------------------------------------------------------------------------
343
344
345 -------------------------------------------------------------------------
346 -- gen_pkg_spec: A Private procedure that will generate the package spec
347 -- of the match rule
348 -------------------------------------------------------------------------
349
350
351 PROCEDURE gen_pkg_spec (
352 p_pkg_name IN VARCHAR2,
353 p_match_rule_id IN NUMBER
354 )
355 IS
356 BEGIN
357 l('CREATE or REPLACE PACKAGE ' || p_pkg_name || ' AUTHID CURRENT_USER AS');
358 l('PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number,inserted_duplicates out number);');
359 HZ_IMP_DQM_STAGE.gen_pkg_spec(p_pkg_name, p_match_rule_id);
360 l('');
361 l('');
362 l('PROCEDURE interface_tca_join_entities(p_batch_id in number,');
363 l(' from_osr in varchar2, to_osr in varchar2, p_threshold in number, p_auto_merge_threshold in number);');
364 l('');
365 l('');
366 l('PROCEDURE interface_join_entities(p_batch_id in number,');
367 l(' from_osr in varchar2, to_osr in varchar2, p_threshold in number);');
368 l('END ;');
369
370 END;
371
372
373 -------------------------------------------------------------------------
374 -- gen_footer:
375 -------------------------------------------------------------------------
376 PROCEDURE gen_footer
377 IS
378 BEGIN
379 l('END;');
380 END;
381
382
383 -----------------------------------------------------------------------------------------------
384 -----------------------------------------------------------------------------------------------
385 -- MATCH RULE GENERATION FOR TCA JOIN
386 -----------------------------------------------------------------------------------------------
387 -----------------------------------------------------------------------------------------------
388
389
390 -------------------------------------------------------------------------
391 -- gen_header_tca :
392 -------------------------------------------------------------------------
393 PROCEDURE gen_header_tca (
394 p_pkg_name IN VARCHAR2,
395 p_match_rule_id IN NUMBER
396 )
397 IS
398 temp number;
399 BEGIN
400 l('CREATE or REPLACE PACKAGE BODY ' || p_pkg_name || ' AS');
401 HZ_IMP_DQM_STAGE.gen_pkg_body(p_pkg_name, p_match_rule_id);
402 l('');
403 l('');
404 l('');
405 l('');
406 l('---------------------------------------------------------------');
407 l('-------------------- TCA JOIN BEGINS --------------------------');
408 l('---------------------------------------------------------------');
409 l('PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number, inserted_duplicates out number)');
410 l('IS');
411 l(' x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;');
412 temp := get_insert_threshold(p_match_rule_id);
413 l(' x_insert_threshold number := ' || temp || ';');
414 l(' l_party_limit NUMBER := 50000;');
415 l(' l_detail_limit NUMBER := 100000;');
416 l('BEGIN');
417 l('FND_FILE.put_line(FND_FILE.log,''Start time of insert of Parties ''||to_char(sysdate,''hh24:mi:ss''));');
418 l('insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score)');
419 l('select f, t, least(f,t), greatest(f,t), sum(score) score from (');
420 END;
421
422
423 -------------------------------------------------------------------------
424 -- gen_footer_tca : A Private procedure that will generate the footer
425 -- for the package body of the match rule
426 -------------------------------------------------------------------------
427 PROCEDURE gen_footer_tca(p_pkg_name VARCHAR2)
428 IS
429 BEGIN
430 l('');
431 l('');
432 l('---------- exception block ---------------');
433 l('EXCEPTION');
434 l('WHEN OTHERS THEN');
435 l(' IF sqlcode=-1722');
436 l(' THEN');
437 l(' inserted_duplicates := -1;');
438 l(' ELSE');
439 l(' FND_MESSAGE.SET_NAME(''AR'', ''HZ_DQM_API_ERROR'');');
440 l(' FND_MESSAGE.SET_TOKEN(''PROC'',''' || p_pkg_name || '.tca_join_entities'');');
441 l(' FND_MESSAGE.SET_TOKEN(''ERROR'' ,SQLERRM );');
442 l(' FND_MSG_PUB.ADD;');
443 l(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;');
444 l(' END IF;');
445 l('END tca_join_entities;');
446 END;
447
448 -------------------------------------------------------------------------
449 -- gen_insert_template_tca :
450 -------------------------------------------------------------------------
451 PROCEDURE gen_insert_template_tca(
452 p_table VARCHAR2,
453 p_match_rule_id NUMBER,
454 p_entity VARCHAR2,
455 p_match_all_flag VARCHAR2
456 )
457 IS
458 FIRST BOOLEAN ;
459 FIRST1 BOOLEAN;
460 no_primary_attr_rows number := 0 ;
461 row_count number := 0;
462 outer_row_count number := 0 ;
463 inner_row_counter number := 0;
464 outer_row_counter number := 0;
465 match_all_flag varchar2(1);
466
467 BEGIN
468
469
470 -- aggregation should happen only for non party entities
471 IF p_entity <> 'PARTY'
472 THEN
473 l('select f, t, max(score) score from (');
474 END IF;
475
476 l('select /*+ ORDERED */ s1.party_id f, s2.party_id t,');
477
478 l('-------' || p_entity || ' ENTITY: SCORING SECTION ---------');
479
480 SELECT count(1) into outer_row_count
481 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
482 where a.attribute_id=s.attribute_id
483 and s.match_rule_id=p_match_rule_id
484 and a.entity_name = p_entity;
485
486 IF has_scoring_attributes(p_match_rule_id, p_entity)
487 THEN
488 -- Generate the Secondary Attribute section of the query for the passed in entity
489 FOR attrs in (
490 SELECT score,s.attribute_id , secondary_attribute_id
491 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
492 where a.attribute_id=s.attribute_id
493 and s.match_rule_id=p_match_rule_id
494 and a.entity_name = p_entity)
495 LOOP
496 outer_row_counter := outer_row_counter + 1;
497 inner_row_counter := 0;
498
499 FOR trans in (
500 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
501 FROM hz_secondary_trans st, hz_trans_functions_vl f
502 where f.function_id=st.function_id
503 and st.secondary_attribute_id = attrs.secondary_attribute_id
504 order by transformation_weight desc)
505 LOOP
506 inner_row_counter := inner_row_counter + 1;
507 l('decode(instrb(s2.'||trans.staged_attribute_column
508 || ',s1.'||trans.staged_attribute_column||
509 '),1,'|| trans.score||',');
510
511 END LOOP;
512
513 l('0');
514
515 -- Need to have as many right parentheses as inner_row_counter
516 FOR I IN 1 .. inner_row_counter
517 LOOP
518 l(')');
519 END LOOP;
520
521 IF outer_row_counter < outer_row_count
522 THEN
523 l(' + ');
524 END IF;
525 END LOOP;
526 ELSE
527 l('0 ');
528 END IF;
529
530 l(' score ');
531 l('from hz_dup_worker_chunk_gt p, '||p_table||' s1, '||p_table||' s2');
532 l('where p.party_id = s1.party_id and s1.party_id<>s2.party_id ');
533
534 --Adding the condition of 'Status = A' below, to fix bug 4669400.
535 --This will make sure that the Merged and Inactive Parties (with status as 'M' and 'I')
536 --will not be considered for duplicate idenfication.
537
538 -- Status flag should be checked only for Party entity
539 IF p_entity = 'PARTY' THEN
540 l('and nvl(s1.status,''A'') = ''A'' and nvl(s2.status,''A'') = ''A'' ');
541 END IF;
542
543 -- To make sure that the detail records (party sites, contacts and contact points) are
544 -- are considered for duplicate indentification, only if the parent party is Active.
545 IF p_entity <> 'PARTY' THEN
546 l('and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,''A'') = ''A'') ');
547 END IF;
548
549 -- SET CHUNK EXPLOSION LIMIT
550 IF p_entity = 'PARTY'
551 THEN
552 l('and 1=decode(trap_explosion,''N'',1,decode(rownum,l_party_limit,to_number(''A''),1))');
553 ELSE
554 l('and 1=decode(trap_explosion,''N'',1,decode(rownum,l_detail_limit,to_number(''A''),1))');
555 END IF;
556
557 l('and (');
558
559 -- Generate the Primary Attribute section of the query for the passed in entity
560 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
561 FIRST1 := TRUE;
562 FOR attrs in (
563 SELECT primary_attribute_id
564 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
565 where p.match_rule_id=p_match_rule_id
566 and p.attribute_id=a.attribute_id
567 and a.entity_name = p_entity
568 and nvl(p.filter_flag,'N') = 'N' )
569 LOOP
570 -- between attributes
571 IF FIRST1
572 THEN
573 FIRST1 := FALSE;
574 l('-------' || p_entity || ' ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------');
575 ELSE
576 IF p_match_all_flag = 'Y'
577 THEN
578 l('and');
579 ELSE
580 l('or');
581 END IF;
582
583 END IF;
584
585 FIRST := TRUE;
586 FOR trans in ( SELECT staged_attribute_column
587 FROM hz_primary_trans pt, hz_trans_functions_vl f
588 where f.function_id = pt.function_id
589 and pt.primary_attribute_id = attrs.primary_attribute_id
590 )
591 LOOP
592 IF FIRST
593 THEN
594 l('-- do an or between all the transformations of an attribute -- ');
595 l('(');
596 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
597 's2.'|| trans.staged_attribute_column || ' like s1.'||
598 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
599 FIRST := FALSE;
600 ELSE
601 l('or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
602 's2.'|| trans.staged_attribute_column || ' like s1.'||
603 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
604 END IF;
605
606 END LOOP;
607 l(')');
608 END LOOP;
609 l(')');
610
611 -- NOW, TAKE CARE OF ENTITY FILTER ATTRIBUTES FOR ALL ENTITIES
612 -- OTHER THAN PARTIES
613 IF p_entity <> 'PARTY' AND has_entity_filter_attributes(p_match_rule_id, p_entity)= 'Y'
614 THEN
615 FIRST1 := TRUE;
616 FOR attrs in (
617 SELECT primary_attribute_id
618 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
619 where p.match_rule_id=p_match_rule_id
620 and p.attribute_id=a.attribute_id
621 and a.entity_name = p_entity
622 and nvl(p.filter_flag,'N') = 'Y' )
623 LOOP
624 IF FIRST1
625 THEN
626 FIRST1 := FALSE;
627 l('-------' || p_entity || ' ENTITY: ACQUISITION ON FILTER ATTRIBUTES---------');
628 END IF;
629
630 -- between attributes
631 l('and');
632
633 FIRST := TRUE;
634 FOR trans in ( SELECT staged_attribute_column
635 FROM hz_primary_trans pt, hz_trans_functions_vl f
636 where f.function_id = pt.function_id
637 and pt.primary_attribute_id = attrs.primary_attribute_id
638 )
639 LOOP
640 IF FIRST
641 THEN
642 l('-- do an or between all the transformations of an attribute -- ');
643 l('(');
644 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
645 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
646 's2.'|| trans.staged_attribute_column || ' = s1.'||
647 trans.staged_attribute_column || ')');
648 FIRST := FALSE;
649 ELSE
650 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
651 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
652 's2.'|| trans.staged_attribute_column || ' = s1.'||
653 trans.staged_attribute_column || ')');
654 END IF;
655
656 END LOOP;
657 l(')');
658 END LOOP;
659 END IF;
660
661 -- complete aggregation for non party entities
662 IF p_entity <> 'PARTY'
663 THEN
664 l(' ) group by f, t ');
665 END IF;
666
667
668
669 END;
670
671
672 -------------------------------------------------------------------------
673 -- gen_insert_footer_tca : A Private procedure that will generate the footer
674 -- for the union part of the package body of the match rule
675 -------------------------------------------------------------------------
676 PROCEDURE gen_insert_footer_tca(p_match_rule_id number)
677 IS
678 FIRST1 boolean;
679 FIRST boolean;
680 BEGIN
681
682 l(' )');
683
684 -- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES
685 IF has_party_filter_attributes(p_match_rule_id)= 'Y'
686 THEN
687 l('------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------');
688 l('where EXISTS (');
689 l('SELECT 1 FROM HZ_STAGED_PARTIES p1, HZ_STAGED_PARTIES p2');
690 l('WHERE p1.party_id = f and p2.party_id = t');
691 FIRST1 := TRUE;
692 FOR attrs in (
693 SELECT primary_attribute_id
694 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
695 where p.match_rule_id=p_match_rule_id
696 and p.attribute_id=a.attribute_id
697 and a.entity_name = 'PARTY'
698 and nvl(p.filter_flag,'N') = 'Y' )
699 LOOP
700 IF FIRST1
701 THEN
705 -- between attributes
702 FIRST1 := FALSE;
703 END IF;
704
706 l('and');
707
708 FIRST := TRUE;
709 FOR trans in ( SELECT staged_attribute_column
710 FROM hz_primary_trans pt, hz_trans_functions_vl f
711 where f.function_id = pt.function_id
712 and pt.primary_attribute_id = attrs.primary_attribute_id
713 )
714 LOOP
715 IF FIRST
716 THEN
717 l('-- do an or between all the transformations of an attribute -- ');
718 l('(');
719 l('((p1.'|| trans.staged_attribute_column || ' is null and ' ||
720 'p2.'|| trans.staged_attribute_column || ' is null) or ' ||
721 'p2.'|| trans.staged_attribute_column || ' = p1.'||
722 trans.staged_attribute_column || ')');
723 FIRST := FALSE;
724 ELSE
725 l('or ((p1.'|| trans.staged_attribute_column || ' is null and ' ||
726 'p2.'|| trans.staged_attribute_column || ' is null) or ' ||
727 'p2.'|| trans.staged_attribute_column || ' = p1.'||
728 trans.staged_attribute_column || ')');
729 END IF;
730
731 END LOOP;
732 l(')');
733 END LOOP;
734 l(')');
735 END IF;
736
737 l('group by f, t ');
738
739 -- having clause should exist only if x_insert_threshold
740 -- is positive
741 IF get_insert_threshold(p_match_rule_id) > 0
742 THEN
743 l('having sum(score) >= x_insert_threshold');
744 END IF;
745
746 l(';');
747 l('inserted_duplicates := (SQL%ROWCOUNT);');
748 l('FND_FILE.put_line(FND_FILE.log,''Number of parties inserted ''||SQL%ROWCOUNT);');
749 l('FND_FILE.put_line(FND_FILE.log,''End time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
750 l('FND_CONCURRENT.AF_Commit;');
751
752 END;
753
754
755 -------------------------------------------------------------------------
756 -- gen_update_template_tca : A Private procedure that will generate the header
757 -- for the package body of the match rule
758 -------------------------------------------------------------------------
759 PROCEDURE gen_update_template_tca (
760 p_table VARCHAR2,
761 p_match_rule_id NUMBER,
762 p_entity VARCHAR2,
763 p_match_all_flag VARCHAR2
764 )
765 IS
766 FIRST BOOLEAN ;
767 FIRST1 BOOLEAN;
768 outer_row_count number := 0 ;
769 inner_row_counter number := 0;
770 outer_row_counter number := 0;
771
772 BEGIN
773
774 l('');
775 l('');
776 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
777 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning update of Parties on the basis of ' || p_entity || ''''|| ');');
778 l('FND_FILE.put_line(FND_FILE.log,''Start time of update ''||to_char(sysdate,''hh24:mi:ss''));');
779 l('open x_ent_cur for');
780
781 -- aggregation should happen only for non party entities
782 IF p_entity <> 'PARTY'
783 THEN
784 l('select f, t, max(score) score from (');
785 END IF;
786
787 l(' select /*+ ORDERED */ s1.party_id f, s2.party_id t,');
788
789
790 SELECT count(1) into outer_row_count
791 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
792 where a.attribute_id=s.attribute_id
793 and s.match_rule_id=p_match_rule_id
794 and a.entity_name = p_entity;
795
796 IF has_scoring_attributes(p_match_rule_id, p_entity)
797 THEN
798
799 -- Generate the Secondary Attribute section of the query for the passed in entity
800 FOR attrs in (
801 SELECT score,s.attribute_id , secondary_attribute_id
802 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
803 where a.attribute_id=s.attribute_id
804 and s.match_rule_id=p_match_rule_id
805 and a.entity_name = p_entity)
806 LOOP
807 outer_row_counter := outer_row_counter + 1;
808 inner_row_counter := 0;
809
810 FOR trans in (
811 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
812 FROM hz_secondary_trans st, hz_trans_functions_vl f
813 where f.function_id=st.function_id
814 and st.secondary_attribute_id = attrs.secondary_attribute_id
815 order by transformation_weight desc)
816 LOOP
817 inner_row_counter := inner_row_counter + 1;
818 l('decode(instrb(s2.'||trans.staged_attribute_column
819 || ',s1.'||trans.staged_attribute_column||
820 '),1,'|| trans.score||',');
821
822 END LOOP;
823
824 l('0');
825
826 -- Need to have as many right parentheses as inner_row_counter
827 FOR I IN 1 .. inner_row_counter
831
828 LOOP
829 l(')');
830 END LOOP;
832 IF outer_row_counter < outer_row_count
833 THEN
834 l('+');
835 END IF;
836 END LOOP;
837 ELSE
838 l('0 ');
839 END IF;
840
841 l('score');
842 l('from hz_dup_worker_chunk_gt p, hz_dup_results h1, '||p_table||' s1, '||p_table||' s2');
843 l('where p.party_id=h1.fid and s1.party_id = h1.fid and s2.party_id = h1.tid');
844 l('and ( ');
845
846 -- Generate the Primary Attribute section of the query for the passed in entity
847 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
848 FIRST1 := TRUE;
849 -- Generate the Primary Attribute section of the query for the passed in entity
850 FOR attrs in (
851 SELECT primary_attribute_id
852 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
853 where p.match_rule_id=p_match_rule_id
854 and p.attribute_id=a.attribute_id
855 and a.entity_name = p_entity
856 and nvl(p.filter_flag,'N') = 'N' )
857 LOOP
858 -- between attributes
859 IF FIRST1
860 THEN
861 FIRST1 := FALSE;
862 l('------------ NON FILTER ATTRIBUTES SECTION ------------------------');
863 ELSE
864 IF p_match_all_flag = 'Y'
865 THEN
866 l('and');
867 ELSE
868 l('or');
869 END IF;
870
871 END IF;
872
873 FIRST := TRUE;
874 FOR trans in ( SELECT staged_attribute_column
875 FROM hz_primary_trans pt, hz_trans_functions_vl f
876 where f.function_id = pt.function_id
877 and pt.primary_attribute_id = attrs.primary_attribute_id
878 )
879 LOOP
880 IF FIRST
881 THEN
882 l('-- do an or between all the transformations of an attribute -- ');
883 l('(');
884 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
885 's2.'|| trans.staged_attribute_column || ' like s1.'||
886 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
887 FIRST := FALSE;
888 ELSE
889 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
890 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
891 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
892 END IF;
893
894 END LOOP;
895 l(')');
896
897 END LOOP;
898 l(')');
899
900 -- NOW TAKE CARE OF FILTER ATTRIBUTES FIRST
901 FIRST1 := TRUE;
902 FOR attrs in (
903 SELECT primary_attribute_id
904 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
905 where p.match_rule_id=p_match_rule_id
906 and p.attribute_id=a.attribute_id
907 and a.entity_name = p_entity
908 and nvl(p.filter_flag,'N') = 'Y' )
909 LOOP
910 -- between attributes
911 IF FIRST1
912 THEN
913 FIRST1 := FALSE;
914 l('------------ FILTER ATTRIBUTES SECTION ------------------------');
915 END IF;
916
917 -- between attributes
918 l('and ');
919
920
921 FIRST := TRUE;
922 FOR trans in ( SELECT staged_attribute_column
923 FROM hz_primary_trans pt, hz_trans_functions_vl f
924 where f.function_id = pt.function_id
925 and pt.primary_attribute_id = attrs.primary_attribute_id
926 )
927 LOOP
928 IF FIRST
929 THEN
930 l('-- do an or between all the transformations of an attribute -- ');
931 l('(');
932 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
933 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
934 's2.'|| trans.staged_attribute_column || ' = s1.'||
935 trans.staged_attribute_column || ')');
936 FIRST := FALSE;
937 ELSE
938 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
939 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
940 's2.'|| trans.staged_attribute_column || ' = s1.'||
941 trans.staged_attribute_column || ')');
942 END IF;
943
944 END LOOP;
945 l(')');
946 END LOOP;
947
948
949 -- aggregation should happen only for non party entities
950 IF p_entity <> 'PARTY'
951 THEN
952 l(') group by f,t ;');
953 ELSE
954 l(';');
955 END IF;
956
957 l('HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);');
958 l('close x_ent_cur;');
962 l('FND_CONCURRENT.AF_Commit;');
959 l('FND_FILE.put_line(FND_FILE.log,''Number of parties updated ''||SQL%ROWCOUNT);');
960 l('FND_FILE.put_line(FND_FILE.log,''End time to update ''||to_char(sysdate,''hh24:mi:ss''));');
961 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending update of Parties on the basis of ' || p_entity || ''''|| ');');
963 END;
964
965
966
967
968 PROCEDURE gen_pkg_body_tca_join (
969 p_pkg_name IN VARCHAR2,
970 p_match_rule_id IN NUMBER,
971 p_att_flag IN VARCHAR2
972 )
973 IS
974
975 CURSOR entity_cur IS
976 select entity_name, entity_table_name, sc, att_flag
977 from
978 (select entity_name, decode(entity_name,
979 'PARTY','HZ_STAGED_PARTIES',
980 'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
981 'CONTACTS','HZ_STAGED_CONTACTS',
982 'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
983 sum(score) sc, 'S' att_flag
984 from hz_trans_attributes_vl a, hz_match_rule_secondary s
985 where s.match_rule_id = p_match_rule_id
986 and s.attribute_id = a.attribute_id
987 group by entity_name
988 union all
989 select entity_name, decode(entity_name,
990 'PARTY','HZ_STAGED_PARTIES',
991 'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
992 'CONTACTS','HZ_STAGED_CONTACTS',
993 'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
994 0 sc, 'P' att_flag
995 from hz_trans_attributes_vl a, hz_match_rule_primary p
996 where p.match_rule_id = p_match_rule_id
997 and p.attribute_id = a.attribute_id
998 group by entity_name
999 )
1000 where att_flag = p_att_flag
1001 order by sc desc ;
1002 srt score_rec_type;
1003 threshold number;
1004 row_count number;
1005 no_of_entities number;
1006 template varchar2(30);
1007 match_all_flag varchar2(1);
1008 insert_stmt_is_open boolean;
1009 BEGIN
1010
1011 -- dbms_output.put_line('Attribute flag is ' || p_att_flag );
1012 -- Get the threshold and match_all_flag
1013 select match_score, match_all_flag into threshold, match_all_flag
1014 from hz_match_rules_vl
1015 where match_rule_id = p_match_rule_id;
1016
1017 -- Get the different aggregates that would help in determining the template
1018 -- that need to be used -- UNION/UPDATE for the corresponding entity.
1019
1020 -- If attribute flag is 'P', make the threshold 0
1021 -- This signifies that the match rule has no scoring attributes
1022 IF p_att_flag = 'P'
1023 THEN
1024 threshold := 0;
1025 srt.sum_score := 0;
1026 srt.min_score := 0;
1027 srt.max_score := 0;
1028 ELSE
1029 srt := get_misc_scores(p_match_rule_id);
1030 END IF;
1031
1032
1033 -- Initialize the number of entities
1034 no_of_entities := 0;
1035
1036
1037 -- Get the number of entities
1038
1039 FOR c0 in entity_cur
1040 LOOP
1041 no_of_entities := no_of_entities + 1;
1042 END LOOP;
1043
1044
1045 -- Before generating the code for the given match rule, we look at the
1046 -- match_all_flag to determine, the structure of the code that needs
1047 -- to go into the generated match rule package.
1048 -- The flag is always assumed to be 'N' by default ie., a match on ANY of the
1049 -- entities, would be given consideration.
1050 -- If flag = 'Y', then we need to make sure that every query after the first
1051 -- one is an update. We make this happen by manually setting the threshold.
1052 IF match_all_flag = 'Y'
1053 THEN
1054 threshold := srt.sum_score;
1055 END IF;
1056
1057
1058 -- Generate the Header
1059 gen_header_tca(p_pkg_name, p_match_rule_id);
1060
1061 -- Open the entity cursor, determine the templates (INSERT/UPDATE) for each
1062 -- and call the appropriate function to add lines to the generated package
1063 -- for the corresponding entity
1064 row_count := 0;
1065 insert_stmt_is_open := false;
1066
1067 -- some basic observations that would help in this logic
1068 -- 1. There will always be atleast one insert statement
1069 -- 2. all insert templates would come under the insert statement
1070 -- 3. all update templates are modular and do not need any special treatment for opening and closing.
1071 -- 4. all update templates would be together
1072 -- 5. when generating an update template, we need to make sure that the insert statement is closed.
1073 -- 6. in the event that we never have an update template, we close the insert statement, outside the loop.
1074 FOR entity_cur_rec in entity_cur
1075 LOOP
1076 row_count := row_count + 1;
1077
1078 -- First row, is always an insert, unless the match rule returns nothing due
1079 -- to an erroneous combination of the threshold/match rule configuration.
1080 -- If that happnes we , get the hell out of here.
1081 IF row_count = 1
1082 THEN
1083 -- pass the first entity forcefully
1084 IF (srt.sum_score - threshold) >= 0
1085 THEN
1086 -- dbms_output.put_line('about insert for first entity');
1090 ELSE
1087 gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1088 match_all_flag);
1089 insert_stmt_is_open := true;
1091 -- need to handle this by reporting an error and getting the hell out of here.
1092 -- dbms_output.put_line('cannot even insert first entity');
1093 -- dbms_output.put_line('sum score is ' || srt.sum_score );
1094 -- dbms_output.put_line('threshold' || threshold );
1095 -- dbms_output.put_line('sum - threshold is ' || srt.sum_score - threshold );
1096 null;
1097 return ;
1098 END IF;
1099 END IF;
1100
1101 IF row_count = 2
1102 THEN
1103
1104 IF (srt.sum_score - srt.max_score - threshold) >= 0
1105 THEN
1106 l('union all');
1107 gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1108 match_all_flag);
1109 ELSE
1110 IF insert_stmt_is_open
1111 THEN
1112 gen_insert_footer_tca(p_match_rule_id);
1113 insert_stmt_is_open := false;
1114 END IF;
1115 gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1116 match_all_flag);
1117 END IF;
1118 END IF;
1119
1120 IF row_count = 3
1121 THEN
1122 IF no_of_entities = 3
1123 THEN
1124 IF (entity_cur_rec.sc - threshold) >= 0
1125 THEN
1126 l('union all');
1127 gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1128 match_all_flag);
1129 ELSE
1130 IF insert_stmt_is_open
1131 THEN
1132 gen_insert_footer_tca(p_match_rule_id);
1133 insert_stmt_is_open := false;
1134 END IF;
1135 gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1136 match_all_flag);
1137 END IF;
1138
1139 ELSE
1140 IF ( entity_cur_rec.sc + srt.min_score - threshold) >= 0
1141 THEN
1142 l('union all');
1143 gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1144 match_all_flag);
1145 ELSE
1146 IF insert_stmt_is_open
1147 THEN
1148 gen_insert_footer_tca(p_match_rule_id);
1149 insert_stmt_is_open := false;
1150 END IF;
1151 gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1152 match_all_flag);
1153 END IF;
1154 END IF;
1155 END IF;
1156
1157 IF row_count = 4
1158 THEN
1159 IF (entity_cur_rec.sc - threshold) >= 0
1160 THEN
1161 l('union all');
1162 gen_insert_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1163 match_all_flag);
1164 ELSE
1165 IF insert_stmt_is_open
1166 THEN
1167 gen_insert_footer_tca(p_match_rule_id);
1168 insert_stmt_is_open := false;
1169 END IF;
1170 gen_update_template_tca(entity_cur_rec.entity_table_name, p_match_rule_id, entity_cur_rec.entity_name,
1171 match_all_flag);
1172 END IF;
1173 END IF;
1174
1175 END LOOP;
1176
1177 -- Just to make sure that the insert statement is not open, after all the entity queries
1178 -- have been generated
1179 IF insert_stmt_is_open
1180 THEN
1181 gen_insert_footer_tca(p_match_rule_id);
1182 insert_stmt_is_open := false;
1183 END IF;
1184
1185 -- generate the footer for the package
1186 gen_footer_tca(p_pkg_name) ;
1187
1188 END;
1189
1190 PROCEDURE gen_pkg_body_tca_join (
1191 p_pkg_name IN VARCHAR2,
1192 p_match_rule_id IN NUMBER
1193 )
1194 IS
1195 BEGIN
1196 IF has_scoring_attributes(p_match_rule_id)
1197 THEN
1198 gen_pkg_body_tca_join(p_pkg_name, p_match_rule_id, 'S');
1199 ELSE
1200 gen_pkg_body_tca_join(p_pkg_name, p_match_rule_id, 'P');
1201 END IF;
1202 END ;
1203
1204 -----------------------------------------------------------------------------------------------------------
1205 -- MATCH RULE GENERATION FOR THE INTERFACE TCA JOIN
1206 -----------------------------------------------------------------------------------------------------------
1207
1208
1209 -------------------------------------------------------------------------
1210 -- gen_header_int_tca :
1211 -------------------------------------------------------------------------
1212 PROCEDURE gen_header_int_tca (
1213 p_pkg_name IN VARCHAR2,
1214 p_match_rule_id IN NUMBER
1215 )
1216 IS
1217 temp number;
1218 BEGIN
1219 l('');
1220 l('');
1221 l('');
1222 l('');
1223 l('---------------------------------------------------------------');
1224 l('-------------------- INTERFACE TCA JOIN BEGINS --------------------------');
1225 l('---------------------------------------------------------------');
1226 l('PROCEDURE interface_tca_join_entities( p_batch_id in number, from_osr in varchar2, to_osr in varchar2,');
1227 l(' p_threshold in number, p_auto_merge_threshold in number)');
1228 l('IS');
1229 l('x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;');
1230 temp := get_insert_threshold(p_match_rule_id);
1231 l('x_insert_threshold number := ' || temp || ';');
1232 l('BEGIN');
1233 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
1234 l('FND_FILE.put_line(FND_FILE.log,''WU: ''||from_osr||'' to ''||to_osr);');
1235 l('FND_FILE.put_line(FND_FILE.log,''Start time of insert of Parties ''||to_char(sysdate,''hh24:mi:ss''));');
1236 l('insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag');
1237 l(',created_by,creation_date,last_update_login,last_update_date,last_updated_by)');
1238 l('select f, t, sum(score) sc, party_osr, party_os, p_batch_id, ''N'' ');
1239 l(',hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login');
1240 l(',hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by');
1241 l('from (');
1242 END;
1243
1244
1245 -------------------------------------------------------------------------
1246 -- gen_footer_int_tca:
1247 -------------------------------------------------------------------------
1248 PROCEDURE gen_footer_int_tca(p_pkg_name VARCHAR2)
1249 IS
1250 BEGIN
1251 l('');
1252 l('---------- exception block ---------------');
1253 l('EXCEPTION');
1254 l('WHEN OTHERS THEN');
1255 l(' FND_MESSAGE.SET_NAME(''AR'', ''HZ_DQM_API_ERROR'');');
1256 l(' FND_MESSAGE.SET_TOKEN(''PROC'',''' || p_pkg_name || '.interface_tca_join_entities'');');
1257 l(' FND_MESSAGE.SET_TOKEN(''ERROR'' ,SQLERRM );');
1258 l(' FND_MSG_PUB.ADD;');
1259 l(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;');
1260 l('END interface_tca_join_entities;');
1261 END;
1262
1263
1264
1265
1266
1267 -------------------------------------------------------------------------
1268 -- gen_insert_template_int_tca :
1269 -------------------------------------------------------------------------
1270 PROCEDURE gen_insert_template_int_tca(
1271 s_table VARCHAR2,
1272 p_table VARCHAR2,
1273 p_match_rule_id NUMBER,
1274 p_entity VARCHAR2,
1275 p_match_all_flag VARCHAR2
1276 )
1277 IS
1278 FIRST BOOLEAN ;
1279 FIRST1 BOOLEAN;
1280 outer_row_count number := 0 ;
1281 inner_row_counter number := 0;
1282 outer_row_counter number := 0;
1283
1284 BEGIN
1285 -- finding the max, applies only to detail information viz., to non-party entities.
1286 IF p_entity <> 'PARTY'
1287 THEN
1288 l('select f, t, max(score) score, party_osr, party_os from (');
1289 END IF;
1290
1291 l('select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,');
1292
1293 l('-------' || p_entity || ' ENTITY: SCORING SECTION ---------');
1294
1295 SELECT count(1) into outer_row_count
1296 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
1297 where a.attribute_id=s.attribute_id
1298 and s.match_rule_id=p_match_rule_id
1299 and a.entity_name = p_entity;
1300
1301 IF has_scoring_attributes(p_match_rule_id, p_entity)
1302 THEN
1303 -- Generate the Secondary Attribute section of the query for the passed in entity
1304 FOR attrs in (
1305 SELECT score,s.attribute_id , secondary_attribute_id
1306 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
1307 where a.attribute_id=s.attribute_id
1308 and s.match_rule_id=p_match_rule_id
1309 and a.entity_name = p_entity)
1310 LOOP
1311 outer_row_counter := outer_row_counter + 1;
1312 inner_row_counter := 0;
1313
1314 FOR trans in (
1315 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
1316 FROM hz_secondary_trans st, hz_trans_functions_vl f
1317 where f.function_id=st.function_id
1318 and st.secondary_attribute_id = attrs.secondary_attribute_id
1319 order by transformation_weight desc)
1320 LOOP
1321 inner_row_counter := inner_row_counter + 1;
1322 l('decode(instrb(s2.'||trans.staged_attribute_column
1323 || ',s1.'||trans.staged_attribute_column||
1324 '),1,'|| trans.score||',');
1325
1326 END LOOP;
1327
1328 l('0');
1329
1330 -- Need to have as many right parentheses as inner_row_counter
1331 FOR I IN 1 .. inner_row_counter
1332 LOOP
1333 l(')');
1334 END LOOP;
1335
1336 IF outer_row_counter < outer_row_count
1337 THEN
1338 l('+');
1339 END IF;
1340 END LOOP;
1341 ELSE
1342 l('0 ');
1343 END IF;
1344
1345
1346 l('score , s1.party_osr party_osr, s1.party_os party_os');
1347
1348
1349 -- if the passed in entity is a detail level entity, then we need to make sure
1350 -- that the party level filters ( if any), participate in the join
1351 -- for the detail
1352 IF p_entity <> 'PARTY' AND has_party_filter_attributes(p_match_rule_id)= 'Y'
1353 THEN
1354 l('from '||s_table||' s1, '||p_table||' s2');
1355 ELSE
1356 l('from '||s_table||' s1, '||p_table||' s2 ');
1357 END IF;
1358
1359 -- for the detail
1360
1361 IF p_entity <> 'PARTY'
1362 THEN
1363 l('where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = ''I''');
1364 ELSE
1365 l('where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr');
1366 END IF;
1367
1368 --Adding the condition of 'Status = A' below, to fix bug 4669400.
1369 --This will make sure that the Merged and Inactive Parties (with status as 'M' and 'I')
1370 --will not be considered for duplicate idenfication.
1371
1372 -- Status flag should be checked only for Party entity
1373 IF p_entity = 'PARTY' THEN
1374 l('and nvl(s2.status,''A'') = ''A'' ');
1375 END IF;
1376
1377 -- To make sure that the detail records (party sites, contacts and contact points) are
1378 -- are considered for duplicate indentification, only if the parent party is Active.
1379 IF p_entity <> 'PARTY' THEN
1380 l('and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,''A'') = ''A'') ');
1381 END IF;
1382
1383
1384 l('and ( ');
1385
1386
1387 -- Generate the Primary Attribute section of the query for the passed in entity
1388 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
1389 FIRST1 := TRUE;
1390 -- Generate the Primary Attribute section of the query for the passed in entity
1391 FOR attrs in (
1392 SELECT primary_attribute_id
1393 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1394 where p.match_rule_id=p_match_rule_id
1395 and p.attribute_id=a.attribute_id
1396 and a.entity_name = p_entity
1397 and nvl(p.filter_flag,'N') = 'N' )
1398 LOOP
1399 -- between attributes
1400 IF FIRST1
1401 THEN
1402 FIRST1 := FALSE;
1403 l('-------' || p_entity || ' ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------');
1404 ELSE
1405 IF p_match_all_flag = 'Y'
1406 THEN
1407 l('and');
1408 ELSE
1409 l('or');
1410 END IF;
1411
1412 END IF;
1413
1414 FIRST := TRUE;
1415 FOR trans in ( SELECT staged_attribute_column
1416 FROM hz_primary_trans pt, hz_trans_functions_vl f
1417 where f.function_id = pt.function_id
1418 and pt.primary_attribute_id = attrs.primary_attribute_id
1419 )
1420 LOOP
1421 IF FIRST
1422 THEN
1423 l('-- do an or between all the transformations of an attribute -- ');
1424 l('(');
1425 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
1426 's2.'|| trans.staged_attribute_column || ' like s1.'||
1427 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
1428 FIRST := FALSE;
1429 ELSE
1430 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
1431 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
1432 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
1433 END IF;
1434
1435 END LOOP;
1436 l(')');
1437
1438 END LOOP;
1439 l(')');
1440
1441 -- NOW, TAKE CARE OF ENTITY FILTER ATTRIBUTES FOR ALL ENTITIES
1442 -- OTHER THAN PARTIES
1443 IF p_entity <> 'PARTY' AND has_entity_filter_attributes(p_match_rule_id,p_entity)= 'Y'
1444 THEN
1445 -- NOW TAKE CARE OF FILTER ATTRIBUTES
1446 FIRST1 := TRUE;
1447 FOR attrs in (
1448 SELECT primary_attribute_id
1449 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1450 where p.match_rule_id=p_match_rule_id
1451 and p.attribute_id=a.attribute_id
1452 and a.entity_name = p_entity
1453 and nvl(p.filter_flag,'N') = 'Y' )
1454 LOOP
1455 -- between attributes
1456 IF FIRST1
1457 THEN
1458 FIRST1 := FALSE;
1459 l('-------' || p_entity || ' ENTITY: ACQUISITION ON FILTER ATTRIBUTES ---------');
1460 END IF;
1461
1462 -- between attributes
1463 l('and ');
1464
1465 FIRST := TRUE;
1466 FOR trans in ( SELECT staged_attribute_column
1467 FROM hz_primary_trans pt, hz_trans_functions_vl f
1468 where f.function_id = pt.function_id
1469 and pt.primary_attribute_id = attrs.primary_attribute_id
1470 )
1471 LOOP
1472 IF FIRST
1473 THEN
1474 l('-- do an or between all the transformations of an attribute -- ');
1475 l('(');
1476 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
1477 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
1478 's2.'|| trans.staged_attribute_column || ' = s1.'||
1479 trans.staged_attribute_column || ' || '' '' )' );
1480 FIRST := FALSE;
1481 ELSE
1482 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
1483 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
1484 's2.'|| trans.staged_attribute_column || ' = s1.'||
1485 trans.staged_attribute_column || ' || '' '' )' );
1486 END IF;
1487
1488 END LOOP;
1489 l(')');
1490 END LOOP;
1491 END IF;
1492
1493 -- complete the insert statement for non-party entities
1494 IF p_entity <> 'PARTY'
1495 THEN
1496 l(')');
1497 l('group by f, t, party_osr, party_os');
1498 END IF;
1499
1500 END;
1501
1502 -------------------------------------------------------------------------
1503 -- gen_dl_insert_template_int_tca :
1504 -------------------------------------------------------------------------
1505 PROCEDURE gen_dl_insert_template_int_tca(
1506 s_table VARCHAR2,
1507 p_table VARCHAR2,
1508 p_match_rule_id NUMBER,
1509 p_entity VARCHAR2,
1510 p_entity_id_name VARCHAR2,
1511 p_entity_osr_name VARCHAR2,
1512 p_entity_os_name VARCHAR2,
1513 p_match_all_flag VARCHAR2
1514 )
1515 IS
1516 FIRST BOOLEAN ;
1517 FIRST1 BOOLEAN ;
1518 outer_row_count number := 0 ;
1519 inner_row_counter number := 0;
1520 outer_row_counter number := 0;
1521
1522 BEGIN
1523 l('');
1524 l('-------------' || p_entity || ' LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------');
1525 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
1526 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning insert of ' || p_entity || ''''|| ');');
1527 l('FND_FILE.put_line(FND_FILE.log,''Start time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
1528
1529 -- BUG FIX FOR 4750317, CHANGED ORDER OF INSERTS
1530 l('insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id');
1531 l(',created_by,creation_date,last_update_login,last_update_date,last_updated_by)');
1532 l('select /*+ USE_CONCAT */ s1.party_id f,');
1533
1534 SELECT count(1) into outer_row_count
1535 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
1536 where a.attribute_id=s.attribute_id
1537 and s.match_rule_id=p_match_rule_id
1538 and a.entity_name = p_entity;
1539
1540 IF has_scoring_attributes(p_match_rule_id, p_entity)
1541 THEN
1542 -- Generate the Secondary Attribute section of the query for the passed in entity
1543 FOR attrs in (
1544 SELECT score,s.attribute_id , secondary_attribute_id
1545 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
1546 where a.attribute_id=s.attribute_id
1547 and s.match_rule_id=p_match_rule_id
1548 and a.entity_name = p_entity)
1549 LOOP
1550 outer_row_counter := outer_row_counter + 1;
1551 inner_row_counter := 0;
1552
1553 FOR trans in (
1554 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
1555 FROM hz_secondary_trans st, hz_trans_functions_vl f
1556 where f.function_id=st.function_id
1557 and st.secondary_attribute_id = attrs.secondary_attribute_id
1558 order by transformation_weight desc)
1559 LOOP
1563 '),1,'|| trans.score||',');
1560 inner_row_counter := inner_row_counter + 1;
1561 l('decode(instrb(s2.'||trans.staged_attribute_column
1562 || ',s1.'||trans.staged_attribute_column||
1564
1565 END LOOP;
1566
1567 l('0');
1568
1569 -- Need to have as many right parentheses as inner_row_counter
1570 FOR I IN 1 .. inner_row_counter
1571 LOOP
1572 l(')');
1573 END LOOP;
1574
1575 IF outer_row_counter < outer_row_count
1576 THEN
1577 l('+');
1578 END IF;
1579 END LOOP;
1580 ELSE
1581 l('0 ');
1582 END IF;
1583
1584 l('score , s1.party_osr, s1.party_os, p_batch_id,' || ''''|| p_entity ||'''' ||', s1.' || p_entity_id_name || ', s1.' || p_entity_osr_name || ', s1.' || p_entity_os_name || ',');
1585 l(' s2.' || p_entity_id_name );
1586 l(',hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login');
1587 l(',hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by');
1588 l('from '||s_table||' s1, '||p_table||' s2 ');
1589 l('where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = ''U''');
1590 l('and s1.party_id = s2.party_id');
1591 l('and ( ');
1592
1593 -- Generate the Primary Attribute section of the query for the passed in entity
1594 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
1595 FIRST1 := TRUE;
1596 -- Generate the Primary Attribute section of the query for the passed in entity
1597 FOR attrs in (
1598 SELECT primary_attribute_id
1599 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1600 where p.match_rule_id=p_match_rule_id
1601 and p.attribute_id=a.attribute_id
1602 and a.entity_name = p_entity
1603 and nvl(p.filter_flag,'N') = 'N' )
1604 LOOP
1605 -- between attributes
1606 IF FIRST1
1607 THEN
1608 FIRST1 := FALSE;
1609 l('------------ NON FILTER ATTRIBUTES SECTION ------------------------');
1610 ELSE
1611 IF p_match_all_flag = 'Y'
1612 THEN
1613 l('and');
1614 ELSE
1615 l('or');
1616 END IF;
1617
1618 END IF;
1619
1620 FIRST := TRUE;
1621 FOR trans in ( SELECT staged_attribute_column
1622 FROM hz_primary_trans pt, hz_trans_functions_vl f
1623 where f.function_id = pt.function_id
1624 and pt.primary_attribute_id = attrs.primary_attribute_id
1625 )
1626 LOOP
1627 IF FIRST
1628 THEN
1629 l('-- do an or between all the transformations of an attribute -- ');
1630 l('(');
1631 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
1632 's2.'|| trans.staged_attribute_column || ' like s1.'||
1633 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
1634 FIRST := FALSE;
1635 ELSE
1636 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
1637 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
1638 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
1639 END IF;
1640
1641 END LOOP;
1642 l(')');
1643
1644 END LOOP;
1645 l(')');
1646
1647 -- NOW TAKE CARE OF FILTER ATTRIBUTES
1648 FIRST1 := TRUE;
1649 FOR attrs in (
1650 SELECT primary_attribute_id
1651 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1652 where p.match_rule_id=p_match_rule_id
1653 and p.attribute_id=a.attribute_id
1654 and a.entity_name = p_entity
1655 and nvl(p.filter_flag,'N') = 'Y'
1656 and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
1657 )
1658 LOOP
1659 -- between attributes
1660 IF FIRST1
1661 THEN
1662 FIRST1 := FALSE;
1663 l('------------ FILTER ATTRIBUTES SECTION ------------------------');
1664 END IF;
1665
1666 l('and ');
1667
1668 FIRST := TRUE;
1669 FOR trans in ( SELECT staged_attribute_column
1670 FROM hz_primary_trans pt, hz_trans_functions_vl f
1671 where f.function_id = pt.function_id
1672 and pt.primary_attribute_id = attrs.primary_attribute_id
1673 )
1674 LOOP
1675 IF FIRST
1676 THEN
1677 l('-- do an or between all the transformations of an attribute -- ');
1678 l('(');
1679 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
1680 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
1681 's2.'|| trans.staged_attribute_column || ' = s1.'||
1682 trans.staged_attribute_column || ' || '' '' )' );
1683 FIRST := FALSE;
1684 ELSE
1685 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
1686 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
1687 's2.'|| trans.staged_attribute_column || ' = s1.'||
1688 trans.staged_attribute_column || ' || '' '' )' );
1689 END IF;
1690
1691 END LOOP;
1692 l(')');
1693 END LOOP;
1694
1695 -- IF THE ENTITY IS NOT A PARTY THEN WE NEED TO MAKE SURE THAT ALL PARTY LEVEL
1696 -- ACQUISTION ATTRIBUTES (IF ANY), THAT SERVE AS FILTERS ARE MATCHED.
1697
1698
1699 l(';');
1700 l('');
1701 l('');
1702 l('--------UPDATE DQM ACTION FLAG IN ' || p_entity ||' INTERFACE/STAGING TABLES --------------');
1703 l('open x_ent_cur for');
1704 l('select distinct a.record_osr, a.record_os');
1705 l('from hz_imp_dup_details a');
1706 l('where a.batch_id = p_batch_id');
1707 l('and a.party_osr between from_osr and to_osr and a.entity =''' || p_entity || ''';') ;
1708 l('HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag(''' ||p_entity ||''',p_batch_id, x_ent_cur);');
1709 l('-------------' || p_entity || ' LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------');
1710 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending insert of ' || p_entity || ''''|| ');');
1711 l('FND_FILE.put_line(FND_FILE.log,''Number of records inserted ''||SQL%ROWCOUNT);');
1712 l('FND_FILE.put_line(FND_FILE.log,''End time to insert ''||to_char(sysdate,''hh24:mi:ss''));');
1713 l('');
1714 l('');
1715
1716 END;
1717
1718 -------------------------------------------------------------------------
1719 -- gen_insert_footer_int_tca :
1720 -------------------------------------------------------------------------
1721 PROCEDURE gen_insert_footer_int_tca(p_match_rule_id number)
1722 IS
1723 FIRST1 boolean;
1724 FIRST boolean;
1725 BEGIN
1726 l(')');
1727
1728 -- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES
1729 IF has_party_filter_attributes(p_match_rule_id)= 'Y'
1730 THEN
1731 l('------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------');
1732 l('where EXISTS (');
1733 l('SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_STAGED_PARTIES p2');
1734 l('WHERE p1.batch_id = p_batch_id and p1.party_osr = party_osr and p1.party_os = party_os');
1735 l('and p2.party_id = t');
1736 FIRST1 := TRUE;
1737 FOR attrs in (
1738 SELECT primary_attribute_id
1739 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1740 where p.match_rule_id=p_match_rule_id
1741 and p.attribute_id=a.attribute_id
1742 and a.entity_name = 'PARTY'
1743 and nvl(p.filter_flag,'N') = 'Y'
1744 and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
1745 )
1746 LOOP
1747 IF FIRST1
1748 THEN
1749 FIRST1 := FALSE;
1750 END IF;
1751
1752 -- between attributes
1753 l('and');
1754
1755 FIRST := TRUE;
1756 FOR trans in ( SELECT staged_attribute_column
1757 FROM hz_primary_trans pt, hz_trans_functions_vl f
1758 where f.function_id = pt.function_id
1759 and pt.primary_attribute_id = attrs.primary_attribute_id
1760 )
1761 LOOP
1762 IF FIRST
1763 THEN
1764 l('-- do an or between all the transformations of an attribute -- ');
1765 l('(');
1766 l('((p1.'|| trans.staged_attribute_column || ' is null and ' ||
1767 'p2.'|| trans.staged_attribute_column || ' is null) or ' ||
1768 'p2.'|| trans.staged_attribute_column || ' = p1.'||
1769 trans.staged_attribute_column || ' || '' '' )');
1770 FIRST := FALSE;
1771 ELSE
1772 l('or ((p1.'|| trans.staged_attribute_column || ' is null and ' ||
1773 'p2.'|| trans.staged_attribute_column || ' is null) or ' ||
1774 'p2.'|| trans.staged_attribute_column || ' = p1.'||
1775 trans.staged_attribute_column || ' || '' '' )');
1776 END IF;
1777
1778 END LOOP;
1779 l(')');
1780 END LOOP;
1781 l(')');
1782 END IF;
1783
1784 l('group by f, t, party_osr, party_os');
1785
1786 -- having clause should exist only if x_insert_threshold
1787 -- is positive
1788 IF get_insert_threshold(p_match_rule_id) > 0
1789 THEN
1790 l('having sum(score) >= x_insert_threshold');
1791 END IF;
1792
1793 l(';');
1794 l('FND_FILE.put_line(FND_FILE.log,''Number of parties inserted ''||SQL%ROWCOUNT);');
1795 l('FND_FILE.put_line(FND_FILE.log,''End time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
1796 END;
1797
1798
1799 -------------------------------------------------------------------------
1800 -- gen_update_template_int_tca :
1801 -------------------------------------------------------------------------
1802 PROCEDURE gen_update_template_int_tca (
1803 s_table VARCHAR2,
1804 p_table VARCHAR2,
1805 p_match_rule_id NUMBER,
1806 p_entity VARCHAR2,
1807 p_match_all_flag VARCHAR2
1808 )
1809 IS
1810 FIRST BOOLEAN ;
1811 FIRST1 BOOLEAN;
1812 outer_row_count number := 0 ;
1813 inner_row_counter number := 0;
1814 outer_row_counter number := 0;
1815
1816 BEGIN
1817
1818 l('');
1819 l('');
1820 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
1821 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning update of Parties on the basis of ' || p_entity || ''''|| ');');
1822 l('FND_FILE.put_line(FND_FILE.log,''Start time of update ''||to_char(sysdate,''hh24:mi:ss''));');
1823
1824 l('open x_ent_cur for');
1825 l('select f,t,max(score) from (');
1826 l(' select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,');
1827
1828 -- Generate the Secondary Attribute section of the query for the passed in entity
1829 SELECT count(1) into outer_row_count
1830 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
1831 where a.attribute_id=s.attribute_id
1832 and s.match_rule_id=p_match_rule_id
1833 and a.entity_name = p_entity;
1834
1835
1836 IF has_scoring_attributes(p_match_rule_id, p_entity)
1837 THEN
1838
1839 FOR attrs in (
1840 SELECT score,s.attribute_id , secondary_attribute_id
1841 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
1842 where a.attribute_id=s.attribute_id
1843 and s.match_rule_id=p_match_rule_id
1844 and a.entity_name = p_entity)
1845 LOOP
1846 outer_row_counter := outer_row_counter + 1;
1847 inner_row_counter := 0;
1848
1849 FOR trans in (
1850 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
1851 FROM hz_secondary_trans st, hz_trans_functions_vl f
1852 where f.function_id=st.function_id
1853 and st.secondary_attribute_id = attrs.secondary_attribute_id
1854 order by transformation_weight desc)
1855 LOOP
1856 inner_row_counter := inner_row_counter + 1;
1857 l('decode(instrb(s2.'||trans.staged_attribute_column
1858 || ',s1.'||trans.staged_attribute_column||
1859 '),1,'|| trans.score||',');
1860
1861 END LOOP;
1862
1863 l('0');
1864
1865 -- Need to have as many right parentheses as inner_row_counter
1866 FOR I IN 1 .. inner_row_counter
1867 LOOP
1868 l(')');
1869 END LOOP;
1870
1871 IF outer_row_counter < outer_row_count
1872 THEN
1873 l('+');
1874 END IF;
1875 END LOOP;
1876 ELSE
1877 l('0 ');
1878 END IF;
1879
1880 l('score');
1881 l('from hz_imp_dup_parties h1, '||s_table||' s1, '||p_table||' s2');
1885 -- Generate the Primary Attribute section of the query for the passed in entity
1882 l('where h1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr');
1883 l('and s1.batch_id = h1.batch_id and s1.party_osr = h1.party_osr and s1.party_os = h1.party_os and s2.party_id = h1.dup_party_id');
1884 l('and ( ');
1886 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
1887 FIRST1 := TRUE;
1888 -- Generate the Primary Attribute section of the query for the passed in entity
1889 FOR attrs in (
1890 SELECT primary_attribute_id
1891 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1892 where p.match_rule_id=p_match_rule_id
1893 and p.attribute_id=a.attribute_id
1894 and a.entity_name = p_entity
1895 and nvl(p.filter_flag,'N') = 'N' )
1896 LOOP
1897 -- between attributes
1898 IF FIRST1
1899 THEN
1900 FIRST1 := FALSE;
1901 l('------------ NON FILTER ATTRIBUTES SECTION ------------------------');
1902 ELSE
1903 IF p_match_all_flag = 'Y'
1904 THEN
1905 l('and');
1906 ELSE
1907 l('or');
1908 END IF;
1909
1910 END IF;
1911
1912 FIRST := TRUE;
1913 FOR trans in ( SELECT staged_attribute_column
1914 FROM hz_primary_trans pt, hz_trans_functions_vl f
1915 where f.function_id = pt.function_id
1916 and pt.primary_attribute_id = attrs.primary_attribute_id
1917 )
1918 LOOP
1919 IF FIRST
1920 THEN
1921 l('-- do an or between all the transformations of an attribute -- ');
1922 l('(');
1923 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
1924 's2.'|| trans.staged_attribute_column || ' like s1.'||
1925 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
1926 FIRST := FALSE;
1927 ELSE
1928 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
1929 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
1930 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
1931 END IF;
1932
1933 END LOOP;
1934 l(')');
1935
1936 END LOOP;
1937 l(')');
1938
1939 -- NOW TAKE CARE OF FILTER ATTRIBUTES
1940 FIRST1 := TRUE;
1941 FOR attrs in (
1942 SELECT primary_attribute_id
1943 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
1944 where p.match_rule_id=p_match_rule_id
1945 and p.attribute_id=a.attribute_id
1946 and a.entity_name = p_entity
1947 and nvl(p.filter_flag,'N') = 'Y'
1948 and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
1949 )
1950 LOOP
1951 -- between attributes
1952 IF FIRST1
1953 THEN
1954 FIRST1 := FALSE;
1955 l('------------ FILTER ATTRIBUTES SECTION ------------------------');
1956 END IF;
1957
1958 l('and ');
1959
1960 FIRST := TRUE;
1961 FOR trans in ( SELECT staged_attribute_column
1962 FROM hz_primary_trans pt, hz_trans_functions_vl f
1963 where f.function_id = pt.function_id
1964 and pt.primary_attribute_id = attrs.primary_attribute_id
1965 )
1966 LOOP
1967 IF FIRST
1968 THEN
1969 l('-- do an or between all the transformations of an attribute -- ');
1970 l('(');
1971 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
1972 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
1973 's2.'|| trans.staged_attribute_column || ' = s1.'||
1974 trans.staged_attribute_column || ' || '' '' )' );
1975 FIRST := FALSE;
1976 ELSE
1977 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
1978 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
1979 's2.'|| trans.staged_attribute_column || ' = s1.'||
1980 trans.staged_attribute_column || ' || '' '' )' );
1981 END IF;
1982
1983 END LOOP;
1984 l(')');
1985 END LOOP;
1986 l(') group by f,t ;');
1987 l('HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);');
1988 l('close x_ent_cur;');
1989 l('FND_FILE.put_line(FND_FILE.log,''Number of parties updated ''||SQL%ROWCOUNT);');
1990 l('FND_FILE.put_line(FND_FILE.log,''End time to update ''||to_char(sysdate,''hh24:mi:ss''));');
1991 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending update of Parties on the basis of ' || p_entity || ''''|| ');');
1992 END;
1993
1997 -- apply threshold to hz_imp_dup_parties
1994 PROCEDURE gen_thr_check_int_tca
1995 IS
1996 BEGIN
1998 l('');
1999 l('--------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------');
2000 l('');
2001 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
2002 l('FND_FILE.put_line(FND_FILE.log,''DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY '');');
2003 l('FND_FILE.put_line(FND_FILE.log,''Begin time to delete ''||to_char(sysdate,''hh24:mi:ss''));');
2004 l('');
2005 l('delete from hz_imp_dup_parties a');
2006 l('where (a.party_osr >= from_osr and a.party_osr <= to_osr');
2007 l('and a.batch_id = p_batch_id)');
2008 l('and (');
2009 l('a.score < p_threshold');
2010 l('or');
2011 l('-- delete the party id whose duplicate is a bigger number, when scores are same');
2012 l('exists');
2013 l(' (Select 1 from hz_imp_dup_parties b');
2014 l(' where b.batch_id=p_batch_id and a.party_id=b.party_id and a.dup_party_id > b.dup_party_id and a.score = b.score)');
2015 l('or');
2016 l('-- delete the party id with least score, if scores are different');
2017 l('exists');
2018 l(' (Select 1 from hz_imp_dup_parties b');
2019 l(' where b.batch_id=p_batch_id and a.party_id=b.party_id and a.score < b.score)');
2020 l(');');
2021 l('');
2022 l('FND_FILE.put_line(FND_FILE.log,''Number of records deleted from hz_imp_dup_parties ''||SQL%ROWCOUNT);');
2023 l('FND_FILE.put_line(FND_FILE.log,''End time to delete ''||to_char(sysdate,''hh24:mi:ss''));');
2024
2025 l('--------UPDATE AUTO MERGE FLAG --------------');
2026 l('update hz_imp_dup_parties a');
2027 l('set a.auto_merge_flag = ''Y''');
2028 l('where a.score >= p_auto_merge_threshold');
2029 l('and a.party_osr >= from_osr and a.party_osr <= to_osr');
2030 l('and a.batch_id = p_batch_id ;');
2031 l('--------UPDATE DQM ACTION FLAG IN INTERFACE/STAGING TABLES --------------');
2032 l('');
2033 l('open x_ent_cur for');
2034 l('select a.party_osr, a.party_os, a.auto_merge_flag');
2035 l('from hz_imp_dup_parties a');
2036 l('where a.batch_id = p_batch_id');
2037 l('and a.party_osr between from_osr and to_osr ;');
2038 l('HZ_DQM_DUP_ID_PKG.update_party_dqm_action_flag(p_batch_id, x_ent_cur);');
2039 l('----------------------PARTY LEVEL DUPLICATE IDENTIFICATION ENDS --------------------');
2040 l('');
2041 END ;
2042 -------------------------------------------------------------------------
2043 -- gen_pkg_body_int_tca_join : A Private procedure that will generate the package body
2044 -- of the match rule
2045 -------------------------------------------------------------------------
2046
2047
2048 PROCEDURE gen_pkg_body_int_tca_join (
2049 p_pkg_name IN VARCHAR2,
2050 p_match_rule_id IN NUMBER,
2051 p_att_flag IN VARCHAR2
2052 )
2053 IS
2054
2055 CURSOR entity_cur IS
2056
2057 select entity_name, search_table_name, entity_table_name, entity_id_name, entity_osr_name,
2058 entity_os_name, sc, att_flag
2059 from
2060 ( select entity_name, decode(entity_name,
2061 'PARTY','HZ_SRCH_PARTIES',
2062 'PARTY_SITES', 'HZ_SRCH_PSITES',
2063 'CONTACTS','HZ_SRCH_CONTACTS',
2064 'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
2065 decode(entity_name,
2066 'PARTY','HZ_STAGED_PARTIES',
2067 'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
2068 'CONTACTS','HZ_STAGED_CONTACTS',
2069 'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
2070 decode(entity_name,
2071 'PARTY','PARTY_ID',
2072 'PARTY_SITES', 'PARTY_SITE_ID',
2073 'CONTACTS','ORG_CONTACT_ID',
2074 'CONTACT_POINTS', 'CONTACT_POINT_ID') entity_id_name,
2075 decode(entity_name,
2076 'PARTY','PARTY_OSR',
2077 'PARTY_SITES', 'PARTY_SITE_OSR',
2078 'CONTACTS','CONTACT_OSR',
2079 'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
2080 decode(entity_name,
2081 'PARTY','PARTY_OS',
2082 'PARTY_SITES', 'PARTY_SITE_OS',
2083 'CONTACTS','CONTACT_OS',
2084 'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
2085 sum(score) sc, 'S' att_flag
2086 from hz_trans_attributes_vl a, hz_match_rule_secondary s
2087 where s.match_rule_id = p_match_rule_id
2088 and s.attribute_id = a.attribute_id
2089 group by entity_name
2090 union all
2091 select entity_name, decode(entity_name,
2092 'PARTY','HZ_SRCH_PARTIES',
2093 'PARTY_SITES', 'HZ_SRCH_PSITES',
2094 'CONTACTS','HZ_SRCH_CONTACTS',
2095 'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
2096 decode(entity_name,
2097 'PARTY','HZ_STAGED_PARTIES',
2098 'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
2099 'CONTACTS','HZ_STAGED_CONTACTS',
2100 'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
2101 decode(entity_name,
2102 'PARTY','PARTY_ID',
2103 'PARTY_SITES', 'PARTY_SITE_ID',
2104 'CONTACTS','ORG_CONTACT_ID',
2105 'CONTACT_POINTS', 'CONTACT_POINT_ID') entity_id_name,
2106 decode(entity_name,
2107 'PARTY','PARTY_OSR',
2108 'PARTY_SITES', 'PARTY_SITE_OSR',
2109 'CONTACTS','CONTACT_OSR',
2110 'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
2111 decode(entity_name,
2112 'PARTY','PARTY_OS',
2113 'PARTY_SITES', 'PARTY_SITE_OS',
2114 'CONTACTS','CONTACT_OS',
2115 'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
2116 0 sc, 'P' att_flag
2117 from hz_trans_attributes_vl a, hz_match_rule_primary p
2118 where p.match_rule_id = p_match_rule_id
2119 and p.attribute_id = a.attribute_id
2120 group by entity_name
2121 ) where att_flag = p_att_flag
2122 order by sc desc ;
2123 srt score_rec_type;
2124 threshold number;
2125 match_all_flag varchar2(1);
2126 row_count number;
2127 no_of_entities number;
2128 template varchar2(30);
2129 insert_stmt_is_open boolean;
2130 BEGIN
2131
2132 -- Get the threshold and match_all_flag
2133 select match_score, match_all_flag into threshold, match_all_flag
2134 from hz_match_rules_vl
2135 where match_rule_id = p_match_rule_id;
2136
2137 -- Get the different aggregates that would help in determining the template
2138 -- that need to be used -- UNION/UPDATE for the corresponding entity.
2139
2140 -- If attribute flag is 'P', make the threshold 0
2141 -- This signifies that the match rule has no scoring attributes
2142 IF p_att_flag = 'P'
2143 THEN
2144 threshold := 0;
2145 srt.sum_score := 0;
2146 srt.min_score := 0;
2147 srt.max_score := 0;
2148 ELSE
2149 srt := get_misc_scores(p_match_rule_id);
2150 END IF;
2151
2152
2153 -- Initialize the number of entities
2154 no_of_entities := 0;
2155
2156 -- Get the number of entities
2157 FOR c0 in entity_cur
2158 LOOP
2159 no_of_entities := no_of_entities + 1;
2160 END LOOP;
2161
2162
2163 -- Before generating the code for the given match rule, we look at the
2164 -- match_all_flag to determine, the structure of the code that needs
2165 -- to go into the generated match rule package.
2166 -- The flag is always assumed to be 'N' by default ie., a match on ANY of the
2167 -- entities, would be given consideration.
2168 -- If flag = 'Y', then we need to make sure that every query after the first
2169 -- one is an update. We make this happen by manually setting the threshold.
2170 IF match_all_flag = 'Y'
2171 THEN
2172 threshold := srt.sum_score;
2173 END IF;
2174
2175 -- Generate the Header
2176 gen_header_int_tca (p_pkg_name, p_match_rule_id);
2177
2178 l('------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------');
2179
2180 -- Open the entity cursor, determine the templates (INSERT/UPDATE) for each
2181 -- and call the appropriate function to add lines to the generated package
2182 -- for the corresponding entity
2183 row_count := 0;
2184 insert_stmt_is_open := false;
2185
2186 -- some basic observations that would help in this logic
2187 -- 1. There will always be atleast one insert statement
2188 -- 2. all insert templates would come under the insert statement
2189 -- 3. all update templates are modular and do not need any special treatment for opening and closing.
2190 -- 4. all update templates would be together
2191 -- 5. when gnerating an update template, we need to make sure that the insert statement is closed.
2192 -- 6. in the event that we never have an update template, we close the insert statement, outside the loop.
2193 FOR entity_cur_rec in entity_cur
2194 LOOP
2195 row_count := row_count + 1;
2196
2197
2198
2199 -- First row, is always an insert, unless the match rule returns nothing due
2200 -- to an erroneous combination of the threshold/match rule configuration.
2201 -- If that happnes we , get the hell out of here.
2202 IF row_count = 1
2203 THEN
2204 -- pass the first entity forcefully
2205 IF (srt.sum_score - threshold) >= 0
2206 THEN
2207 gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
2208 entity_cur_rec.entity_name, match_all_flag);
2209 insert_stmt_is_open := true;
2210 ELSE
2211 -- need to handle this by reporting an error and getting the hell out of here.
2212 null;
2213 return ;
2214 END IF;
2215 END IF;
2216
2217 IF row_count = 2
2218 THEN
2219
2220 IF (srt.sum_score - srt.max_score - threshold) >= 0
2221 THEN
2222 l('union all');
2223 gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
2224 entity_cur_rec.entity_name, match_all_flag);
2225 ELSE
2226 IF insert_stmt_is_open
2227 THEN
2228 gen_insert_footer_int_tca(p_match_rule_id) ;
2229 insert_stmt_is_open := false;
2230 END IF;
2231 gen_update_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
2232 entity_cur_rec.entity_name, match_all_flag);
2233 END IF;
2234 END IF;
2235
2236 IF row_count = 3
2237 THEN
2238 IF no_of_entities = 3
2239 THEN
2240 IF (entity_cur_rec.sc - threshold) >= 0
2241 THEN
2242 l('union all');
2243 gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
2244 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
2245 ELSE
2246 IF insert_stmt_is_open
2247 THEN
2248 gen_insert_footer_int_tca(p_match_rule_id) ;
2249 insert_stmt_is_open := false;
2250 END IF;
2251 gen_update_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
2252 entity_cur_rec.entity_name, match_all_flag);
2253 END IF;
2254
2255 ELSE
2256 IF ( entity_cur_rec.sc + srt.min_score - threshold) >= 0
2257 THEN
2258 l('union all');
2259 gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
2260 entity_cur_rec.entity_name, match_all_flag);
2261 ELSE
2262 IF insert_stmt_is_open
2263 THEN
2264 gen_insert_footer_int_tca(p_match_rule_id) ;
2265 insert_stmt_is_open := false;
2266 END IF;
2267 gen_update_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
2268 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
2269 END IF;
2270 END IF;
2271 END IF;
2272
2273 IF row_count = 4
2274 THEN
2275 IF (entity_cur_rec.sc - threshold) >= 0
2276 THEN
2277 l('union all');
2278 gen_insert_template_int_tca(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
2279 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
2280 ELSE
2281 IF insert_stmt_is_open
2282 THEN
2283 gen_insert_footer_int_tca(p_match_rule_id);
2284 insert_stmt_is_open := false;
2285 END IF;
2286 gen_update_template_int_tca(entity_cur_rec.search_table_name, entity_cur_rec.entity_table_name,
2287 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
2288 END IF;
2289 END IF;
2290
2291 END LOOP;
2292
2293 -- Just to make sure that the insert statement is not open, after all the entity queries
2294 -- have been generated
2295 IF insert_stmt_is_open
2296 THEN
2297 gen_insert_footer_int_tca(p_match_rule_id) ;
2298 insert_stmt_is_open := false;
2299 END IF;
2300
2301 -- generate threshold check
2302 gen_thr_check_int_tca ;
2303
2304 -- generate code for detail level duplicate identification
2305 FOR entity_cur_rec in entity_cur
2306 LOOP
2307 IF entity_cur_rec.entity_name <> 'PARTY'
2308 THEN
2309 gen_dl_insert_template_int_tca(entity_cur_rec.search_table_name,
2310 entity_cur_rec.entity_table_name,
2311 p_match_rule_id,
2312 entity_cur_rec.entity_name,
2313 entity_cur_rec.entity_id_name,
2314 entity_cur_rec.entity_osr_name,
2315 entity_cur_rec.entity_os_name,
2316 match_all_flag );
2317 END IF;
2318
2319 END LOOP;
2320 -- generate the footer for the package
2321 gen_footer_int_tca(p_pkg_name) ;
2322
2323 END;
2324
2325 PROCEDURE gen_pkg_body_int_tca_join (
2326 p_pkg_name IN VARCHAR2,
2327 p_match_rule_id IN NUMBER
2328 )
2329 IS
2330 BEGIN
2331 IF has_scoring_attributes(p_match_rule_id)
2332 THEN
2333 gen_pkg_body_int_tca_join(p_pkg_name, p_match_rule_id, 'S');
2334 ELSE
2335 gen_pkg_body_int_tca_join(p_pkg_name, p_match_rule_id, 'P');
2336 END IF;
2337 END ;
2338
2339
2340 ------------------------------------------------------------------------
2341 -- MATCH RULE GENERATION FOR INTERFACE JOIN
2342 ------------------------------------------------------------------------
2343
2344
2345
2346 -------------------------------------------------------------------------
2347 -- gen_header_int :
2348 -------------------------------------------------------------------------
2349 PROCEDURE gen_header_int (
2350 p_pkg_name IN VARCHAR2,
2351 p_match_rule_id IN NUMBER
2352 )
2353 IS
2354 temp number;
2355 BEGIN
2356 l('');
2357 l('');
2358 l('');
2359 l('');
2360 l('---------------------------------------------------------------');
2361 l('-------------------- INTERFACE JOIN BEGINS --------------------------');
2362 l('---------------------------------------------------------------');
2363 l('PROCEDURE interface_join_entities(p_batch_id in number,');
2364 l(' from_osr in varchar2, to_osr in varchar2, p_threshold in number)');
2365 l('IS');
2366 l('x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;');
2367 temp := get_insert_threshold(p_match_rule_id);
2368 l(' x_insert_threshold number := ' || temp || ';');
2369 l('BEGIN');
2370 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
2371 l('FND_FILE.put_line(FND_FILE.log,''WU: ''||from_osr||'' to ''||to_osr);');
2372 l('FND_FILE.put_line(FND_FILE.log,''Start time of insert of Parties ''||to_char(sysdate,''hh24:mi:ss''));');
2373 l('insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)');
2374 l('select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (');
2375 END;
2376
2377 -------------------------------------------------------------------------
2378 -- gen_insert_template_int :
2379 -------------------------------------------------------------------------
2380 PROCEDURE gen_insert_template_int(
2381 s_table VARCHAR2,
2382 p_table VARCHAR2,
2383 p_match_rule_id NUMBER,
2384 p_entity VARCHAR2,
2385 p_match_all_flag VARCHAR2
2386 )
2387 IS
2388 FIRST BOOLEAN ;
2389 FIRST1 BOOLEAN;
2390 outer_row_count number := 0 ;
2391 inner_row_counter number := 0;
2392 outer_row_counter number := 0;
2393
2394 BEGIN
2395 -- finding the max, applies only to detail information viz., to non-party entities.
2396 IF p_entity <> 'PARTY'
2397 THEN
2398 l('select f, t, max(score) score, fos, tos from (');
2399 END IF;
2400
2401 l('select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,');
2402
2403
2404 l('-------' || p_entity || ' ENTITY: SCORING SECTION ---------');
2405
2406 SELECT count(1) into outer_row_count
2407 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
2408 where a.attribute_id=s.attribute_id
2409 and s.match_rule_id=p_match_rule_id
2410 and a.entity_name = p_entity;
2411
2412 IF has_scoring_attributes(p_match_rule_id, p_entity)
2413 THEN
2414 -- Generate the Secondary Attribute section of the query for the passed in entity
2415 FOR attrs in (
2416 SELECT score,s.attribute_id , secondary_attribute_id
2417 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
2418 where a.attribute_id=s.attribute_id
2419 and s.match_rule_id=p_match_rule_id
2420 and a.entity_name = p_entity)
2421 LOOP
2422 outer_row_counter := outer_row_counter + 1;
2423 inner_row_counter := 0;
2424
2425 FOR trans in (
2426 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
2427 FROM hz_secondary_trans st, hz_trans_functions_vl f
2428 where f.function_id=st.function_id
2429 and st.secondary_attribute_id = attrs.secondary_attribute_id
2430 order by transformation_weight desc)
2431 LOOP
2432 inner_row_counter := inner_row_counter + 1;
2433 l('decode(instrb(s2.'||trans.staged_attribute_column
2434 || ',s1.'||trans.staged_attribute_column||
2435 '),1,'|| trans.score||',');
2436 END LOOP;
2437
2438 l('0');
2439
2440 -- Need to have as many right parentheses as inner_row_counter
2441 FOR I IN 1 .. inner_row_counter
2442 LOOP
2443 l(')');
2444 END LOOP;
2445
2446 IF outer_row_counter < outer_row_count
2447 THEN
2448 l('+');
2449 END IF;
2450 END LOOP;
2451 ELSE
2452 l('0 ');
2453 END IF;
2454
2455 l('score, s1.party_os fos, s2.party_os tos');
2456
2457
2458 -- if the passed in entity is a detail level entity, then we need to make sure
2459 -- that the party level filters ( if any), participate in the join
2460 -- for the detail
2461 IF p_entity <> 'PARTY' AND has_party_filter_attributes(p_match_rule_id)= 'Y'
2462 THEN
2463 l('from '||s_table||' s1, '||s_table||' s2');
2464 ELSE
2465 l('from '||s_table||' s1, '||s_table||' s2 ');
2466 END IF;
2467
2468 l('where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr');
2469 l('and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)');
2470 -- only for contact point types
2471 IF p_entity = 'CONTACT_POINTS'
2472 THEN
2473 l('and s1.contact_point_type = s2.contact_point_type');
2474 END IF;
2475
2476 l('and (');
2477
2478 -- Generate the Primary Attribute section of the query for the passed in entity
2479 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
2480 FIRST1 := TRUE;
2481 -- Generate the Primary Attribute section of the query for the passed in entity
2482 FOR attrs in (
2483 SELECT primary_attribute_id
2484 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
2485 where p.match_rule_id=p_match_rule_id
2486 and p.attribute_id=a.attribute_id
2487 and a.entity_name = p_entity
2488 and nvl(p.filter_flag,'N') = 'N' )
2489 LOOP
2490 -- between attributes
2491 IF FIRST1
2492 THEN
2493 FIRST1 := FALSE;
2494 l('-------' || p_entity || ' ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------');
2495 ELSE
2496 IF p_match_all_flag = 'Y'
2497 THEN
2498 l('and');
2499 ELSE
2500 l('or');
2501 END IF;
2502
2503 END IF;
2504
2505 FIRST := TRUE;
2506 FOR trans in ( SELECT staged_attribute_column
2507 FROM hz_primary_trans pt, hz_trans_functions_vl f
2508 where f.function_id = pt.function_id
2509 and pt.primary_attribute_id = attrs.primary_attribute_id
2510 )
2511 LOOP
2512 IF FIRST
2513 THEN
2514 l('-- do an or between all the transformations of an attribute -- ');
2515 l('(');
2516 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
2517 's2.'|| trans.staged_attribute_column || ' like s1.'||
2518 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
2519 FIRST := FALSE;
2520 ELSE
2521 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
2522 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
2523 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
2524 END IF;
2525
2526 END LOOP;
2527 l(')');
2528
2529 END LOOP;
2530 l(')');
2531
2532 -- NOW, TAKE CARE OF ENTITY FILTER ATTRIBUTES FOR ALL ENTITIES
2533 -- OTHER THAN PARTIES
2534 IF p_entity <> 'PARTY' AND has_entity_filter_attributes(p_match_rule_id, p_entity)= 'Y'
2535 THEN
2536
2537 FIRST1 := TRUE;
2538 FOR attrs in (
2539 SELECT primary_attribute_id
2540 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
2541 where p.match_rule_id=p_match_rule_id
2542 and p.attribute_id=a.attribute_id
2543 and a.entity_name = p_entity
2544 and nvl(p.filter_flag,'N') = 'Y' )
2545 LOOP
2546 -- between attributes
2547 IF FIRST1
2548 THEN
2549 FIRST1 := FALSE;
2550 l('-------' || p_entity || ' ENTITY: ACQUISITION ON FILTER ATTRIBUTES ---------');
2551 END IF;
2552
2553 -- between attributes
2554 l('and ');
2555
2556 FIRST := TRUE;
2557 FOR trans in ( SELECT staged_attribute_column
2558 FROM hz_primary_trans pt, hz_trans_functions_vl f
2559 where f.function_id = pt.function_id
2560 and pt.primary_attribute_id = attrs.primary_attribute_id
2561 )
2562 LOOP
2563 IF FIRST
2564 THEN
2565 l('-- do an or between all the transformations of an attribute -- ');
2566 l('(');
2567 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
2568 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
2569 's2.'|| trans.staged_attribute_column || ' = s1.'||
2570 trans.staged_attribute_column || ')');
2571 FIRST := FALSE;
2572 ELSE
2573 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
2574 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
2575 's2.'|| trans.staged_attribute_column || ' = s1.'||
2576 trans.staged_attribute_column || ')');
2577 END IF;
2578
2579 END LOOP;
2580 l(')');
2581 END LOOP;
2582
2583 END IF;
2584
2585 -- complete the insert statement for non-party entities
2586 IF p_entity <> 'PARTY'
2587 THEN
2588 l(')');
2589 l('group by f, t, fos, tos');
2590 END IF;
2591
2592 END;
2593
2594 -------------------------------------------------------------------------
2595 -- gen_dl_insert_template_int :
2596 -------------------------------------------------------------------------
2597 PROCEDURE gen_dl_insert_template_int(
2598 s_table VARCHAR2,
2599 p_table VARCHAR2,
2600 p_match_rule_id NUMBER,
2601 p_entity VARCHAR2,
2602 p_entity_osr_name VARCHAR2,
2603 p_entity_os_name VARCHAR2,
2604 p_match_all_flag VARCHAR2
2605 )
2606 IS
2607 FIRST BOOLEAN ;
2608 FIRST1 BOOLEAN ;
2609 outer_row_count number := 0 ;
2610 inner_row_counter number := 0;
2611 outer_row_counter number := 0;
2612
2613 BEGIN
2614
2615 l('-------------' || p_entity || ' LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------');
2616 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
2617 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning insert of ' || p_entity || ''''|| ');');
2618 l('FND_FILE.put_line(FND_FILE.log,''Start time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
2619 l('insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,');
2620 l('dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,');
2621 l('dup_creation_date,dup_last_update_date');
2622 l(',created_by,creation_date,last_update_login,last_update_date,last_updated_by)');
2623 l('select /*+ USE_CONCAT */ p_batch_id, s1.' || p_entity_osr_name || ', s1.' || p_entity_os_name || ',');
2624 l('s2.' || p_entity_osr_name || ', s2.' || p_entity_os_name || ',');
2625 l('s1.party_osr, s2.party_os,' || '''' || p_entity || ''',');
2626 SELECT count(1) into outer_row_count
2627 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
2628 where a.attribute_id=s.attribute_id
2629 and s.match_rule_id=p_match_rule_id
2630 and a.entity_name = p_entity;
2631
2632 IF has_scoring_attributes(p_match_rule_id, p_entity)
2633 THEN
2634 -- Generate the Secondary Attribute section of the query for the passed in entity
2635 FOR attrs in (
2636 SELECT score,s.attribute_id , secondary_attribute_id
2637 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
2638 where a.attribute_id=s.attribute_id
2639 and s.match_rule_id=p_match_rule_id
2640 and a.entity_name = p_entity)
2641 LOOP
2642 outer_row_counter := outer_row_counter + 1;
2643 inner_row_counter := 0;
2644
2645 FOR trans in (
2646 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
2647 FROM hz_secondary_trans st, hz_trans_functions_vl f
2648 where f.function_id=st.function_id
2649 and st.secondary_attribute_id = attrs.secondary_attribute_id
2650 order by transformation_weight desc)
2651 LOOP
2652 inner_row_counter := inner_row_counter + 1;
2653 l('decode(nvl(s1.'||trans.staged_attribute_column||
2654 ',''N1''),nvl(substrb(s2.'||trans.staged_attribute_column||
2655 ',1,length(s1.'||trans.staged_attribute_column||')),''N2''),'||trans.score||', ');
2656
2657 END LOOP;
2658
2659 l('0');
2660
2661 -- Need to have as many right parentheses as inner_row_counter
2662 FOR I IN 1 .. inner_row_counter
2663 LOOP
2664 l(')');
2665 END LOOP;
2666
2667 IF outer_row_counter < outer_row_count
2668 THEN
2669 l('+');
2670 END IF;
2671 END LOOP;
2672 ELSE
2673 l('0 ');
2674 END IF;
2675
2676 l('score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date');
2677 l(',hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login');
2678 l(',hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by');
2679 l('from '||s_table||' s1, '||s_table||' s2 ');
2680 l('where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr ');
2681 l(' and ( ( (s1.party_osr = s2.party_osr) and ( nvl(s1.party_id, 1) = nvl(s2.party_id,1) ) ) OR ( s1.party_id = s2.party_id) ) '); -- bug 5393826
2682 l('and s2.batch_id = p_batch_id and s1.' || p_entity_osr_name || ' < ' || 's2.' || p_entity_osr_name );
2683
2684 -- only for contact point types
2685 IF p_entity = 'CONTACT_POINTS'
2686 THEN
2687 l('and s1.contact_point_type = s2.contact_point_type');
2688 END IF;
2689
2690 l('and ( ');
2691
2692 -- Generate the Primary Attribute section of the query for the passed in entity
2693 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
2694 FIRST1 := TRUE;
2695 -- Generate the Primary Attribute section of the query for the passed in entity
2696 FOR attrs in (
2697 SELECT primary_attribute_id
2698 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
2699 where p.match_rule_id=p_match_rule_id
2700 and p.attribute_id=a.attribute_id
2701 and a.entity_name = p_entity
2702 and nvl(p.filter_flag,'N') = 'N' )
2703 LOOP
2704 -- between attributes
2705 IF FIRST1
2706 THEN
2707 FIRST1 := FALSE;
2708 l('------------ NON FILTER ATTRIBUTES SECTION ------------------------');
2709 ELSE
2710 IF p_match_all_flag = 'Y'
2711 THEN
2712 l('and');
2713 ELSE
2714 l('or');
2715 END IF;
2716
2717 END IF;
2718
2719 FIRST := TRUE;
2720 FOR trans in ( SELECT staged_attribute_column
2721 FROM hz_primary_trans pt, hz_trans_functions_vl f
2722 where f.function_id = pt.function_id
2723 and pt.primary_attribute_id = attrs.primary_attribute_id
2724 )
2725 LOOP
2726 IF FIRST
2727 THEN
2728 l('-- do an or between all the transformations of an attribute -- ');
2729 l('(');
2730 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
2731 's2.'|| trans.staged_attribute_column || ' like s1.'||
2732 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
2733 FIRST := FALSE;
2734 ELSE
2735 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
2736 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
2737 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
2738 END IF;
2739
2740 END LOOP;
2741 l(')');
2742
2743 END LOOP;
2744 l(')');
2745
2746 -- NOW TAKE CARE OF FILTER ATTRIBUTES
2747 FIRST1 := TRUE;
2748 FOR attrs in (
2749 SELECT primary_attribute_id
2750 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
2751 where p.match_rule_id=p_match_rule_id
2752 and p.attribute_id=a.attribute_id
2753 and a.entity_name = p_entity
2754 and nvl(p.filter_flag,'N') = 'Y'
2755 and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
2756 )
2757 LOOP
2758 -- between attributes
2759 IF FIRST1
2760 THEN
2761 FIRST1 := FALSE;
2762 l('------------ FILTER ATTRIBUTES SECTION ------------------------');
2763 END IF;
2764
2765 l('and ');
2766
2767 FIRST := TRUE;
2768 FOR trans in ( SELECT staged_attribute_column
2769 FROM hz_primary_trans pt, hz_trans_functions_vl f
2770 where f.function_id = pt.function_id
2771 and pt.primary_attribute_id = attrs.primary_attribute_id
2772 )
2773 LOOP
2774 IF FIRST
2775 THEN
2776 l('-- do an or between all the transformations of an attribute -- ');
2777 l('(');
2778 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
2779 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
2780 's2.'|| trans.staged_attribute_column || ' = s1.'||
2781 trans.staged_attribute_column || ')');
2782 FIRST := FALSE;
2783 ELSE
2784 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
2785 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
2786 's2.'|| trans.staged_attribute_column || ' = s1.'||
2787 trans.staged_attribute_column || ')');
2788 END IF;
2789
2790 END LOOP;
2791 l(')');
2792 END LOOP;
2793
2794 -- IF THE ENTITY IS NOT A PARTY THEN WE NEED TO MAKE SURE THAT ALL PARTY LEVEL
2795 -- ACQUISTION ATTRIBUTES (IF ANY), THAT SERVE AS FILTERS ARE MATCHED.
2796
2797
2798 l(';');
2799 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending insert of ' || p_entity || ''''|| ');');
2800 l('FND_FILE.put_line(FND_FILE.log,''Number of records inserted ''||SQL%ROWCOUNT);');
2801 l('FND_FILE.put_line(FND_FILE.log,''End time to insert ''||to_char(sysdate,''hh24:mi:ss''));');
2802 l('FND_CONCURRENT.AF_Commit;');
2803 END;
2804
2805 -------------------------------------------------------------------------
2806 -- gen_insert_footer_int :
2807 -------------------------------------------------------------------------
2808 PROCEDURE gen_insert_footer_int(p_match_rule_id number)
2809 IS
2810 FIRST1 boolean;
2811 FIRST boolean;
2812 BEGIN
2813 l(')');
2814
2815 -- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES
2816 IF has_party_filter_attributes(p_match_rule_id)= 'Y'
2817 THEN
2818 l('------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------');
2819 l('where EXISTS (');
2820 l('SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_SRCH_PARTIES p2');
2821 l('WHERE p1.batch_id = p_batch_id and p1.party_osr = f and p1.party_os = fos');
2822 l('and p2.batch_id = p_batch_id and p2.party_osr = t and p2.party_os = tos');
2823 FIRST1 := TRUE;
2824 FOR attrs in (
2825 SELECT primary_attribute_id
2826 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
2827 where p.match_rule_id=p_match_rule_id
2828 and p.attribute_id=a.attribute_id
2829 and a.entity_name = 'PARTY'
2830 and nvl(p.filter_flag,'N') = 'Y'
2831 and HZ_IMP_DQM_STAGE.EXIST_COL(a.attribute_name, a.entity_name ) = 'Y'
2832 )
2833 LOOP
2834 IF FIRST1
2835 THEN
2836 FIRST1 := FALSE;
2837 END IF;
2838
2839 -- between attributes
2840 l('and');
2841
2842 FIRST := TRUE;
2843 FOR trans in ( SELECT staged_attribute_column
2844 FROM hz_primary_trans pt, hz_trans_functions_vl f
2845 where f.function_id = pt.function_id
2846 and pt.primary_attribute_id = attrs.primary_attribute_id
2847 )
2848 LOOP
2849 IF FIRST
2850 THEN
2851 l('-- do an or between all the transformations of an attribute -- ');
2852 l('(');
2853 l('((p1.'|| trans.staged_attribute_column || ' is null and ' ||
2854 'p2.'|| trans.staged_attribute_column || ' is null) or ' ||
2855 'p2.'|| trans.staged_attribute_column || ' = p1.'||
2856 trans.staged_attribute_column || ')');
2857 FIRST := FALSE;
2858 ELSE
2859 l('or ((p1.'|| trans.staged_attribute_column || ' is null and ' ||
2860 'p2.'|| trans.staged_attribute_column || ' is null) or ' ||
2861 'p2.'|| trans.staged_attribute_column || ' = p1.'||
2862 trans.staged_attribute_column || ')');
2863 END IF;
2864
2865 END LOOP;
2866 l(')');
2867 END LOOP;
2868 l(')');
2869 END IF;
2870
2871 l('group by f, t, fos, tos');
2872
2873 -- having clause should exist only if x_insert_threshold
2874 -- is positive
2875 IF get_insert_threshold(p_match_rule_id) > 0
2876 THEN
2877 l('having sum(score) >= x_insert_threshold');
2878 END IF;
2879
2880 l(';');
2881 l('FND_FILE.put_line(FND_FILE.log,''Number of parties inserted ''||SQL%ROWCOUNT);');
2882 l('FND_FILE.put_line(FND_FILE.log,''End time of insert ''||to_char(sysdate,''hh24:mi:ss''));');
2883 l('FND_CONCURRENT.AF_Commit;');
2884 END;
2885
2886
2887 -------------------------------------------------------------------------
2888 -- gen_update_template_int :
2889 -------------------------------------------------------------------------
2890 PROCEDURE gen_update_template_int (
2891 s_table VARCHAR2,
2892 p_table VARCHAR2,
2893 p_match_rule_id NUMBER,
2894 p_entity VARCHAR2,
2895 p_match_all_flag VARCHAR2
2896 )
2897 IS
2898 FIRST BOOLEAN ;
2899 FIRST1 BOOLEAN;
2900 outer_row_count number := 0 ;
2901 inner_row_counter number := 0;
2902 outer_row_counter number := 0;
2903
2904 BEGIN
2905
2906 l('');
2907 l('');
2908 l('FND_FILE.put_line(FND_FILE.log,''------------------------------------------------'');');
2909 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Beginning update of Parties on the basis of ' || p_entity || ''''|| ');');
2910 l('FND_FILE.put_line(FND_FILE.log,''Start time of update ''||to_char(sysdate,''hh24:mi:ss''));');
2911 l('open x_ent_cur for');
2912 l('select f,t,max(score) from (');
2913 l(' select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,');
2914
2915 -- Generate the Secondary Attribute section of the query for the passed in entity
2916 SELECT count(1) into outer_row_count
2917 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
2918 where a.attribute_id=s.attribute_id
2919 and s.match_rule_id=p_match_rule_id
2920 and a.entity_name = p_entity;
2921
2922 IF has_scoring_attributes(p_match_rule_id, p_entity)
2923 THEN
2924
2925 FOR attrs in (
2926 SELECT score,s.attribute_id , secondary_attribute_id
2927 FROM HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
2928 where a.attribute_id=s.attribute_id
2929 and s.match_rule_id=p_match_rule_id
2930 and a.entity_name = p_entity)
2931 LOOP
2932 outer_row_counter := outer_row_counter + 1;
2933 inner_row_counter := 0;
2934
2935 FOR trans in (
2936 SELECT round(transformation_weight/100*attrs.score) score, staged_attribute_column
2937 FROM hz_secondary_trans st, hz_trans_functions_vl f
2938 where f.function_id=st.function_id
2939 and st.secondary_attribute_id = attrs.secondary_attribute_id
2940 order by transformation_weight desc)
2941 LOOP
2942 inner_row_counter := inner_row_counter + 1;
2943 l('decode(instrb(s2.'||trans.staged_attribute_column
2944 || ',s1.'||trans.staged_attribute_column||
2945 '),1,'|| trans.score||',');
2946
2947 END LOOP;
2948
2949 l('0');
2950
2951 -- Need to have as many right parentheses as inner_row_counter
2952 FOR I IN 1 .. inner_row_counter
2953 LOOP
2954 l(')');
2955 END LOOP;
2956
2957 IF outer_row_counter < outer_row_count
2958 THEN
2959 l('+');
2960 END IF;
2961 END LOOP;
2962 ELSE
2963 l('0 ');
2964 END IF;
2965
2966 l('score');
2967 l('from hz_int_dup_results h1, '||s_table||' s1, '||s_table||' s2');
2968 l('where');
2969 l('s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id');
2970 l('and s1.party_osr between from_osr and to_osr');
2971
2972 -- only for contact point types
2973 IF p_entity = 'CONTACT_POINTS'
2974 THEN
2975 l('and s1.contact_point_type = s2.contact_point_type');
2976 END IF;
2977
2978
2979 l('and ( ');
2980
2981 -- Generate the Primary Attribute section of the query for the passed in entity
2982 -- TAKE CARE OF NON-FILTER ATTRIBUTES FIRST
2983 FIRST1 := TRUE;
2984 -- Generate the Primary Attribute section of the query for the passed in entity
2985 FOR attrs in (
2986 SELECT primary_attribute_id
2987 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
2988 where p.match_rule_id=p_match_rule_id
2989 and p.attribute_id=a.attribute_id
2990 and a.entity_name = p_entity
2991 and nvl(p.filter_flag,'N') = 'N' )
2992 LOOP
2993 -- between attributes
2994 IF FIRST1
2995 THEN
2996 FIRST1 := FALSE;
2997 l('------------ NON FILTER ATTRIBUTES SECTION ------------------------');
2998 ELSE
2999 IF p_match_all_flag = 'Y'
3000 THEN
3001 l('and');
3002 ELSE
3003 l('or');
3004 END IF;
3005
3006 END IF;
3007
3008 FIRST := TRUE;
3009 FOR trans in ( SELECT staged_attribute_column
3010 FROM hz_primary_trans pt, hz_trans_functions_vl f
3011 where f.function_id = pt.function_id
3012 and pt.primary_attribute_id = attrs.primary_attribute_id
3013 )
3014 LOOP
3015 IF FIRST
3016 THEN
3017 l('-- do an or between all the transformations of an attribute -- ');
3018 l('(');
3019 l('(s1.'|| trans.staged_attribute_column || ' is not null and ' ||
3020 's2.'|| trans.staged_attribute_column || ' like s1.'||
3021 trans.staged_attribute_column || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
3022 FIRST := FALSE;
3023 ELSE
3024 l(' or (s1.'|| trans.staged_attribute_column || ' is not null and ' ||
3025 's2.'|| trans.staged_attribute_column || ' '|| ' like s1.'||
3026 trans.staged_attribute_column || ' ' || ' || decode(sign(lengthb(s1.' || trans.staged_attribute_column || ')-'|| HZ_DQM_DUP_ID_PKG.l_like_comparison_min_length || '),1,''%'',''''))');
3027 END IF;
3028
3029 END LOOP;
3030 l(')');
3031
3032 END LOOP;
3033 l(')');
3034
3035 -- NOW TAKE CARE OF FILTER ATTRIBUTES
3036 FIRST1 := TRUE;
3037 FOR attrs in (
3038 SELECT primary_attribute_id
3039 FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
3040 where p.match_rule_id=p_match_rule_id
3041 and p.attribute_id=a.attribute_id
3042 and a.entity_name = p_entity
3043 and nvl(p.filter_flag,'N') = 'Y' )
3044 LOOP
3045 -- between attributes
3046 IF FIRST1
3047 THEN
3048 FIRST1 := FALSE;
3049 l('------------ FILTER ATTRIBUTES SECTION ------------------------');
3050 END IF;
3051
3052 l('and ');
3053
3054 FIRST := TRUE;
3055 FOR trans in ( SELECT staged_attribute_column
3056 FROM hz_primary_trans pt, hz_trans_functions_vl f
3057 where f.function_id = pt.function_id
3058 and pt.primary_attribute_id = attrs.primary_attribute_id
3059 )
3060 LOOP
3061 IF FIRST
3062 THEN
3063 l('-- do an or between all the transformations of an attribute -- ');
3064 l('(');
3065 l('((s1.'|| trans.staged_attribute_column || ' is null and ' ||
3066 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
3067 's2.'|| trans.staged_attribute_column || ' = s1.'||
3068 trans.staged_attribute_column || ')');
3069 FIRST := FALSE;
3070 ELSE
3071 l('or ((s1.'|| trans.staged_attribute_column || ' is null and ' ||
3072 's2.'|| trans.staged_attribute_column || ' is null) or ' ||
3073 's2.'|| trans.staged_attribute_column || ' = s1.'||
3074 trans.staged_attribute_column || ')');
3075 END IF;
3076
3077 END LOOP;
3078 l(')');
3079 END LOOP;
3080 l(') group by f,t ;');
3081 l('HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);');
3082 l('close x_ent_cur;');
3083 l('FND_FILE.put_line(FND_FILE.log,''Number of parties updated ''||SQL%ROWCOUNT);');
3084 l('FND_FILE.put_line(FND_FILE.log,''End time to update ''||to_char(sysdate,''hh24:mi:ss''));');
3085 l('FND_FILE.put_line(FND_FILE.log,'||''''|| 'Ending update of Parties on the basis of ' || p_entity || ''''|| ');');
3086 l('FND_CONCURRENT.AF_Commit;');
3087
3088 END;
3089
3090 -------------------------------------------------------------------------
3091 -- gen_footer_int :
3092 -------------------------------------------------------------------------
3093 PROCEDURE gen_footer_int(p_pkg_name VARCHAR2)
3094 IS
3095 BEGIN
3096 l('');
3097 l('---------- exception block ---------------');
3098 l('EXCEPTION');
3099 l('WHEN OTHERS THEN');
3100 l(' FND_MESSAGE.SET_NAME(''AR'', ''HZ_DQM_API_ERROR'');');
3101 l(' FND_MESSAGE.SET_TOKEN(''PROC'',''' || p_pkg_name || '.interface_join_entities'');');
3102 l(' FND_MESSAGE.SET_TOKEN(''ERROR'' ,SQLERRM );');
3103 l(' FND_MSG_PUB.ADD;');
3104 l(' RAISE FND_API.G_EXC_UNEXPECTED_ERROR;');
3105 l('END interface_join_entities;');
3106 END;
3107
3108
3109
3110 -------------------------------------------------------------------------
3111 -- gen_pkg_body_int_join : A Private procedure that will generate the package body
3112 -- of the match rule
3113 -------------------------------------------------------------------------
3114
3115 PROCEDURE gen_pkg_body_int_join(
3116 p_pkg_name IN VARCHAR2,
3117 p_match_rule_id IN NUMBER,
3118 p_att_flag IN VARCHAR2
3119 )
3120 IS
3121 CURSOR entity_cur IS
3122 select entity_name, search_table_name, entity_table_name, entity_osr_name, entity_os_name, sc, att_flag
3126 'PARTY_SITES', 'HZ_SRCH_PSITES',
3123 from
3124 (select entity_name, decode(entity_name,
3125 'PARTY','HZ_SRCH_PARTIES',
3127 'CONTACTS','HZ_SRCH_CONTACTS',
3128 'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
3129 decode(entity_name,
3130 'PARTY','HZ_STAGED_PARTIES',
3131 'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
3132 'CONTACTS','HZ_STAGED_CONTACTS',
3133 'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
3134 decode(entity_name,
3135 'PARTY','PARTY_OSR',
3136 'PARTY_SITES', 'PARTY_SITE_OSR',
3137 'CONTACTS','CONTACT_OSR',
3138 'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
3139 decode(entity_name,
3140 'PARTY','PARTY_OS',
3141 'PARTY_SITES', 'PARTY_SITE_OS',
3142 'CONTACTS','CONTACT_OS',
3143 'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
3144 sum(score) sc, 'S' att_flag
3145 from hz_trans_attributes_vl a, hz_match_rule_secondary s
3146 where s.match_rule_id = p_match_rule_id
3147 and s.attribute_id = a.attribute_id
3148 group by entity_name
3149 union all
3150 select entity_name, decode(entity_name,
3151 'PARTY','HZ_SRCH_PARTIES',
3152 'PARTY_SITES', 'HZ_SRCH_PSITES',
3153 'CONTACTS','HZ_SRCH_CONTACTS',
3154 'CONTACT_POINTS', 'HZ_SRCH_CPTS') search_table_name,
3155 decode(entity_name,
3156 'PARTY','HZ_STAGED_PARTIES',
3157 'PARTY_SITES', 'HZ_STAGED_PARTY_SITES',
3158 'CONTACTS','HZ_STAGED_CONTACTS',
3159 'CONTACT_POINTS', 'HZ_STAGED_CONTACT_POINTS') entity_table_name,
3160 decode(entity_name,
3161 'PARTY','PARTY_OSR',
3162 'PARTY_SITES', 'PARTY_SITE_OSR',
3163 'CONTACTS','CONTACT_OSR',
3164 'CONTACT_POINTS', 'CONTACT_PT_OSR') entity_osr_name,
3165 decode(entity_name,
3166 'PARTY','PARTY_OS',
3167 'PARTY_SITES', 'PARTY_SITE_OS',
3168 'CONTACTS','CONTACT_OS',
3169 'CONTACT_POINTS', 'CONTACT_PT_OS') entity_os_name,
3170 0 sc, 'P' att_flag
3171 from hz_trans_attributes_vl a, hz_match_rule_primary p
3172 where p.match_rule_id = p_match_rule_id
3173 and p.attribute_id = a.attribute_id
3174 group by entity_name
3175 ) where att_flag = p_att_flag
3176 order by sc desc ;
3177 srt score_rec_type;
3178 threshold number;
3179 match_all_flag varchar2(1);
3180 row_count number;
3181 no_of_entities number;
3182 template varchar2(30);
3183 insert_stmt_is_open boolean;
3184 BEGIN
3185
3186 -- Get the threshold and match_all_flag
3187 select match_score, match_all_flag into threshold, match_all_flag
3188 from hz_match_rules_vl
3189 where match_rule_id = p_match_rule_id;
3190
3191 -- Get the different aggregates that would help in determining the template
3192 -- that need to be used -- UNION/UPDATE for the corresponding entity.
3193
3194 -- If attribute flag is 'P', make the threshold 0
3195 -- This signifies that the match rule has no scoring attributes
3196 IF p_att_flag = 'P'
3197 THEN
3198 threshold := 0;
3199 srt.sum_score := 0;
3200 srt.min_score := 0;
3201 srt.max_score := 0;
3202 ELSE
3203 srt := get_misc_scores(p_match_rule_id);
3204 END IF;
3205
3206
3207 -- Initialize the number of entities
3208 no_of_entities := 0;
3209
3210 -- Get the number of entities
3211 FOR c0 in entity_cur
3212 LOOP
3213 no_of_entities := no_of_entities + 1;
3214 END LOOP;
3215
3216
3217 -- Before generating the code for the given match rule, we look at the
3218 -- match_all_flag to determine, the structure of the code that needs
3219 -- to go into the generated match rule package.
3220 -- The flag is always assumed to be 'N' by default ie., a match on ANY of the
3221 -- entities, would be given consideration.
3222 -- If flag = 'Y', then we need to make sure that every query after the first
3223 -- one is an update. We make this happen by manually setting the threshold.
3224 IF match_all_flag = 'Y'
3225 THEN
3226 threshold := srt.sum_score;
3227 END IF;
3228
3229 -- Generate the Header
3230 gen_header_int(p_pkg_name, p_match_rule_id);
3231
3232 l('------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------');
3233
3234 -- Open the entity cursor, determine the templates (INSERT/UPDATE) for each
3235 -- and call the appropriate function to add lines to the generated package
3236 -- for the corresponding entity
3237 row_count := 0;
3238 insert_stmt_is_open := false;
3239
3243 -- 3. all update templates are modular and do not need any special treatment for opening and closing.
3240 -- some basic observations that would help in this logic
3241 -- 1. There will always be atleast one insert statement
3242 -- 2. all insert templates would come under the insert statement
3244 -- 4. all update templates would be together
3245 -- 5. when gnerating an update template, we need to make sure that the insert statement is closed.
3246 -- 6. in the event that we never have an update template, we close the insert statement, outside the loop.
3247 FOR entity_cur_rec in entity_cur
3248 LOOP
3249 row_count := row_count + 1;
3250
3251
3252
3253 -- First row, is always an insert, unless the match rule returns nothing due
3254 -- to an erroneous combination of the threshold/match rule configuration.
3255 -- If that happnes we , get the hell out of here.
3256 IF row_count = 1
3257 THEN
3258 -- pass the first entity forcefully
3259 IF (srt.sum_score - threshold) >= 0
3260 THEN
3261 gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
3262 entity_cur_rec.entity_name, match_all_flag);
3263 insert_stmt_is_open := true;
3264 ELSE
3265 -- need to handle this by reporting an error and getting the hell out of here.
3266 null;
3267 return ;
3268 END IF;
3269 END IF;
3270
3271 IF row_count = 2
3272 THEN
3273
3274 IF (srt.sum_score - srt.max_score - threshold) >= 0
3275 THEN
3276 l('union all');
3277 gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
3278 entity_cur_rec.entity_name, match_all_flag);
3279 ELSE
3280 IF insert_stmt_is_open
3281 THEN
3282 gen_insert_footer_int(p_match_rule_id) ;
3283 insert_stmt_is_open := false;
3284 END IF;
3285 gen_update_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
3286 entity_cur_rec.entity_name, match_all_flag);
3287 END IF;
3288 END IF;
3289
3290 IF row_count = 3
3291 THEN
3292 IF no_of_entities = 3
3293 THEN
3294 IF (entity_cur_rec.sc - threshold) >= 0
3295 THEN
3296 l('union all');
3297 gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
3298 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
3299 ELSE
3300 IF insert_stmt_is_open
3301 THEN
3302 gen_insert_footer_int(p_match_rule_id) ;
3303 insert_stmt_is_open := false;
3304 END IF;
3305 gen_update_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
3306 entity_cur_rec.entity_name, match_all_flag);
3307 END IF;
3308
3309 ELSE
3310 IF ( entity_cur_rec.sc + srt.min_score - threshold) >= 0
3311 THEN
3312 l('union all');
3313 gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name, p_match_rule_id,
3314 entity_cur_rec.entity_name, match_all_flag);
3315 ELSE
3316 IF insert_stmt_is_open
3317 THEN
3318 gen_insert_footer_int(p_match_rule_id) ;
3319 insert_stmt_is_open := false;
3320 END IF;
3321 gen_update_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
3322 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
3323 END IF;
3324 END IF;
3325 END IF;
3326
3327 IF row_count = 4
3328 THEN
3329 IF (entity_cur_rec.sc - threshold) >= 0
3330 THEN
3331 l('union all');
3332 gen_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
3333 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
3334 ELSE
3335 IF insert_stmt_is_open
3336 THEN
3337 gen_insert_footer_int(p_match_rule_id);
3338 insert_stmt_is_open := false;
3339 END IF;
3340 gen_update_template_int(entity_cur_rec.search_table_name, entity_cur_rec.entity_table_name,
3341 p_match_rule_id, entity_cur_rec.entity_name, match_all_flag);
3342 END IF;
3343 END IF;
3344
3345 END LOOP;
3346
3347 -- Just to make sure that the insert statement is not open, after all the entity queries
3348 -- have been generated
3349 IF insert_stmt_is_open
3350 THEN
3351 gen_insert_footer_int(p_match_rule_id) ;
3352 insert_stmt_is_open := false;
3353 END IF;
3354
3355 -- generate code for detail level duplicate identification
3356 FOR entity_cur_rec in entity_cur
3357 LOOP
3358 IF entity_cur_rec.entity_name <> 'PARTY'
3359 THEN
3360 gen_dl_insert_template_int(entity_cur_rec.search_table_name,entity_cur_rec.entity_table_name,
3361 p_match_rule_id, entity_cur_rec.entity_name, entity_cur_rec.entity_osr_name,
3362 entity_cur_rec.entity_os_name, match_all_flag );
3363 END IF;
3364
3365 END LOOP;
3366 -- generate the footer for the package
3367 gen_footer_int(p_pkg_name) ;
3368
3369 END;
3370
3371
3372 PROCEDURE gen_pkg_body_int_join (
3373 p_pkg_name IN VARCHAR2,
3374 p_match_rule_id IN NUMBER
3375 )
3376 IS
3377 BEGIN
3378 IF has_scoring_attributes(p_match_rule_id)
3379 THEN
3380 gen_pkg_body_int_join(p_pkg_name, p_match_rule_id, 'S');
3381 ELSE
3382 gen_pkg_body_int_join(p_pkg_name, p_match_rule_id, 'P');
3383 END IF;
3384 END ;
3385
3386
3387
3388 END;
[Home] [Help]