1 PACKAGE BODY HZ_DQM_SEARCH_UTIL AS
2 /*$Header: ARHDQUTB.pls 120.29 2006/02/11 00:19:27 schitrap noship $ */
3 g_within_threshold NUMBER := 4;
4
5 g_string VARCHAR2(2000);
6 g_curpos NUMBER;
7 g_delim VARCHAR2(30);
8 g_numtoks NUMBER;
9
10 g_num_eval NUMBER:=0;
11
12 g_no_score BOOLEAN := FALSE;
13
14 --Start of Bug No:4244535
15
16 TYPE attr_rec_type IS RECORD(attr_id hz_trans_attributes_b.ATTRIBUTE_ID%type,
17 tmp_section hz_trans_attributes_b.temp_section%type);
18 TYPE attr_tb_type is table of attr_rec_type index by binary_integer;
19 g_attr_rec attr_rec_type;
20 g_attr_tb attr_tb_type;
21
22 --End of Bug No:4244535
23
24
25 PROCEDURE set_score IS
26 BEGIN
27 g_no_score:= FALSE;
28 END;
29 PROCEDURE set_no_score IS
30 BEGIN
31 g_no_score:= TRUE;
32 END;
33
34 PROCEDURE set_num_eval(p_num NUMBER) IS
35 BEGIN
36 g_num_eval:=p_num;
37 END;
38
39 FUNCTION get_num_eval RETURN NUMBER IS
40 BEGIN
41 RETURN g_num_eval;
42 END;
43
44 PROCEDURE new_search IS
45 BEGIN
46 HZ_TRANS_PKG.clear_globals;
47 END;
48 FUNCTION subst_reserved(
49 p_inp IN VARCHAR2) RETURN VARCHAR2 IS
50
51 retstr VARCHAR2(4000);
52
53 BEGIN
54 retstr := ' ' || p_inp || ' ';
55 retstr := replace(retstr,' ABOUT ',' {ABOUT} ');
56 retstr := replace(retstr,' ACCUM ',' {ACCUM} ');
57 retstr := replace(retstr,' AND ',' {AND} ');
58 retstr := replace(retstr,' BT ',' {BT} ');
59 retstr := replace(retstr,' BTG ',' {BTG} ');
60 retstr := replace(retstr,' BTI ',' {BTI} ');
61 retstr := replace(retstr,' BTP ',' {BTP} ');
62 retstr := replace(retstr,' MINUS ',' {MINUS} ');
63 retstr := replace(retstr,' NEAR ',' {NEAR} ');
64 retstr := replace(retstr,' NOT ',' {NOT} ');
65 retstr := replace(retstr,' NT ',' {NT} ');
66 retstr := replace(retstr,' NTG ',' {NTG} ');
67 retstr := replace(retstr,' NTI ',' {NTI} ');
68 retstr := replace(retstr,' NTP ',' {NTP} ');
69 retstr := replace(retstr,' OR ',' {OR} ');
70 retstr := replace(retstr,' PT ',' {PT} ');
71 retstr := replace(retstr,' RT ',' {RT} ');
72 retstr := replace(retstr,' SQE ',' {SQE} ');
73 retstr := replace(retstr,' SYN ',' {SYN} ');
74 retstr := replace(retstr,' TR ',' {TR} ');
75 retstr := replace(retstr,' TRSYN ',' {TRSYN} ');
76 retstr := replace(retstr,' TT ',' {TT} ');
77 retstr := replace(retstr,' WITHIN ',' {WITHIN} ');
78 return rtrim(ltrim(retstr));
79 END;
80
81 FUNCTION check_misc (p_within VARCHAR2) RETURN VARCHAR2 IS
82 l_1stchar VARCHAR2(1);
83 l_misc_within VARCHAR2(30);
84 CURSOR temp_sect(p_attr_id NUMBER) IS
85 SELECT nvl(temp_section,p_within) --Bug No: 4244535
86 FROM HZ_TRANS_ATTRIBUTES_VL
87 WHERE attribute_id = p_attr_id;
88 --AND temp_section IS NOT NULL; --Bug No: 4244535
89 l_attr_id NUMBER(15);
90 l_index NUMBER;
91 BEGIN
92 l_1stchar := substrb(p_within,1,1);
93 IF l_1stchar = 'D' THEN
94 RETURN p_within;
95 ELSIF l_1stchar = 'M' THEN
96 RETURN p_within;
97 ELSE
98 --Start of Bug No: 4244535
99 l_attr_id := to_number(substrb(p_within,2));
100 FOR i in 1..g_attr_tb.count LOOP
101 IF (g_attr_tb(i).attr_id = l_attr_id) THEN
102 RETURN g_attr_tb(i).tmp_section;
103 END IF;
104 END LOOP;
105 l_index := g_attr_tb.count+1;
106 OPEN temp_sect(l_attr_id);
107 FETCH temp_sect INTO l_misc_within;
108 IF temp_sect%FOUND THEN
109 CLOSE temp_sect;
110 g_attr_tb(l_index).attr_id := l_attr_id;
111 g_attr_tb(l_index).tmp_section := l_misc_within;
112 RETURN l_misc_within;
113 ELSE
114 CLOSE temp_sect;
115 g_attr_tb(l_index).attr_id := l_attr_id;
116 g_attr_tb(l_index).tmp_section := p_within;
117 RETURN p_within;
118 END IF;
119 --End of Bug No: 4244535
120 END IF;
121 END;
122
123 PROCEDURE add_transformation (
124 p_tx_val IN VARCHAR2,
125 p_within IN VARCHAR2,
126 x_tx_str IN OUT NOCOPY VARCHAR2) IS
127
128 l_tx_val VARCHAR2(4000);
129
130 BEGIN
131 IF p_tx_val IS NOT NULL THEN
132 l_tx_val := p_tx_val;
133 IF instrb(l_tx_val,'%')>0 OR lengthb(l_tx_val)>255 THEN ----Bug No: 3032742
134 l_tx_val := subst_reserved(l_tx_val);
135 ELSE
136 l_tx_val := '{' || l_tx_val || '}';
137 END IF;
138
139 IF x_tx_str IS NOT NULL THEN
140 x_tx_str := x_tx_str || ' OR ';
141 END IF;
142
143 IF p_within IS NULL THEN
144 x_tx_str := x_tx_str || '(' || l_tx_val || ')';
145 ELSE
146 x_tx_str := x_tx_str ||
147 '(' || l_tx_val || ') within ' || check_misc(p_within);
148 END IF;
149 END IF;
150 EXCEPTION
151 WHEN OTHERS THEN
152 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
153 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_DQM_SEARCH_UTIL.add_transformation');
154 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
155 FND_MSG_PUB.ADD;
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157 END add_transformation;
158
159 PROCEDURE add_filter (
160 p_tx_val IN VARCHAR2,
161 p_within IN VARCHAR2,
162 x_filter_str IN OUT NOCOPY VARCHAR2) IS
163 BEGIN
164 IF p_tx_val IS NOT NULL THEN
165 IF x_filter_str IS NOT NULL THEN
166 x_filter_str := x_filter_str || ' AND ';
167 END IF;
168 x_filter_str := x_filter_str ||
169 '{' || replace(p_tx_val,'_',' ') || '} within ' || check_misc(p_within);
170 END IF;
171 EXCEPTION
172 WHEN OTHERS THEN
173 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
174 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_DQM_SEARCH_UTIL.add_filter');
175 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
176 FND_MSG_PUB.ADD;
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END add_filter;
179
180 PROCEDURE add_attribute (
181 p_tx_str IN VARCHAR2,
182 p_match_str IN VARCHAR2,
183 x_contains_str IN OUT NOCOPY VARCHAR2) IS
184 BEGIN
185 IF p_tx_str IS NOT NULL THEN
186 IF x_contains_str IS NOT NULL THEN
187 x_contains_str := x_contains_str || p_match_str;
188 END IF;
189 x_contains_str := x_contains_str || '('||
190 p_tx_str || ')';
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
195 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_DQM_SEARCH_UTIL.add_attribute');
196 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
197 FND_MSG_PUB.ADD;
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END add_attribute;
200
201 PROCEDURE add_attribute_with_denorm (
202 p_tx_str IN VARCHAR2,
203 p_match_str IN VARCHAR2,
204 p_denorm_str IN VARCHAR2,
205 x_contains_str IN OUT NOCOPY VARCHAR2) IS
206 BEGIN
207 IF p_tx_str IS NOT NULL THEN
208 IF x_contains_str IS NOT NULL THEN
209 x_contains_str := x_contains_str || p_match_str;
210 END IF;
211 x_contains_str := x_contains_str || '('||
212 p_tx_str;
213 IF p_denorm_str IS NOT NULL THEN
214 x_contains_str := x_contains_str || ' AND ('||p_denorm_str ||'))';
215 ELSE
216 x_contains_str := x_contains_str || ')';
217 END IF;
218 END IF;
219 EXCEPTION
220 WHEN OTHERS THEN
221 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
222 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm');
223 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
224 FND_MSG_PUB.ADD;
225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
226 END add_attribute_with_denorm;
227
228
229 PROCEDURE add_search_record (
230 p_rec_contains_str IN VARCHAR2,
231 p_filter_str IN VARCHAR2,
232 x_contains_str IN OUT NOCOPY VARCHAR2) IS
233 BEGIN
234 IF p_rec_contains_str IS NOT NULL THEN
235 IF x_contains_str IS NOT NULL THEN
236 x_contains_str := x_contains_str || ' OR ';
237 END IF;
238 IF p_filter_str IS NOT NULL THEN
239 x_contains_str :=
240 x_contains_str || '((' || p_rec_contains_str || ') AND '||p_filter_str||')';
241 ELSE
242 x_contains_str :=
243 x_contains_str || '('|| p_rec_contains_str || ')';
244 END IF;
245 END IF;
246 EXCEPTION
247 WHEN OTHERS THEN
248 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
249 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_DQM_SEARCH_UTIL.add_search_record');
250 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
251 FND_MSG_PUB.ADD;
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END;
254
255 -- Remove records from HZ_DQM_PARTIES_GT which don't satisfy subset_defn
256 PROCEDURE remove_matches_not_in_subset (
257 p_search_ctx_id IN NUMBER,
258 p_subset_defn IN VARCHAR2
259 ) IS
260 l_sqlstr VARCHAR2(4000);
261 BEGIN
262 l_sqlstr := 'DELETE FROM HZ_DQM_PARTIES_GT m WHERE NOT EXISTS ('||
263 'SELECT p.party_id FROM HZ_PARTIES p '||
264 'WHERE p.party_id = m.party_id AND '||
265 p_subset_defn || ') AND SEARCH_CONTEXT_ID = :ctxid';
266 EXECUTE IMMEDIATE l_sqlstr USING p_search_ctx_id;
267 EXCEPTION
268 WHEN OTHERS THEN
269 FND_MESSAGE.SET_NAME('AR', 'HZ_RESTRICT_SQL_ERROR');
270 FND_MESSAGE.SET_TOKEN('PROC' ,'remove_matches_not_in_subset');
271 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
272 FND_MSG_PUB.ADD;
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 END remove_matches_not_in_subset;
275
276 FUNCTION strtok (
277 p_string VARCHAR2 DEFAULT NULL,
278 p_numtoks NUMBER DEFAULT NULL,
279 p_delim VARCHAR2 DEFAULT NULL
280 ) RETURN VARCHAR2 IS
281
282 l_spc_char NUMBER;
283 l_numtoks NUMBER;
284 ret VARCHAR2(4000); --Bug No: 3032742
285 FIRST BOOLEAN;
286 l_first_pos NUMBER;
287 BEGIN
288 IF p_string IS NOT NULL THEN
289 g_string := p_string;
290 g_curpos := 1;
291 g_delim := p_delim;
292 g_numtoks := p_numtoks;
293 END IF;
294
295 IF g_string IS NULL THEN
296 RETURN null;
297 END IF;
298
299 l_numtoks := 0;
300 FIRST := TRUE;
301 LOOP
302 l_spc_char := instr(g_string,g_delim,g_curpos);
303 EXIT WHEN l_spc_char = 0;
304
305 ret := ret || ' '|| substr(g_string,g_curpos,l_spc_char-g_curpos);
306 g_curpos := l_spc_char+1;
307 IF FIRST THEN
308 l_first_pos := g_curpos;
309 FIRST := FALSE;
310 END IF;
311 l_numtoks := l_numtoks+1;
312 EXIT WHEN l_numtoks=g_numtoks;
313 END LOOP;
314 IF l_numtoks<g_numtoks THEN
315 ret := ret || ' '|| substr(g_string,g_curpos);
316 l_numtoks := l_numtoks+1;
317 g_string := NULL;
318 END IF;
319 IF l_numtoks<g_numtoks THEN
320 RETURN NULL;
321 ELSE
322 g_curpos := l_first_pos;
323 RETURN ltrim(ret);
324 END IF;
325 END;
326
327 FUNCTION is_match(
328 p_src IN VARCHAR2,
329 p_dest IN VARCHAR2,
330 p_attr_idx IN NUMBER
331 ) RETURN BOOLEAN IS
332
333 test VARCHAR2(2000);
334
335 BEGIN
336 IF p_src IS NULL OR p_dest IS NULL THEN
337 RETURN FALSE;
338 END IF;
339
340 IF (' '||p_dest||' ' like '% '||p_src||' %') THEN
341 RETURN TRUE;
342 ELSE
343 RETURN FALSE;
344 END IF;
345 END;
346
347 FUNCTION is_similar_match(
348 p_src IN VARCHAR2,
349 p_dest IN VARCHAR2,
350 p_min_similarity IN VARCHAR2,
351 p_attr_idx IN NUMBER)
352 RETURN BOOLEAN IS
353 numspc NUMBER;
354 destspc NUMBER;
355 l_dest VARCHAR2(2000);
356 BEGIN
357 IF p_src IS NULL OR p_dest IS NULL THEN
358 RETURN FALSE;
359 ELSIF instr(p_src,'%')>0 THEN
360 RETURN is_match(p_src,p_dest,p_attr_idx);
361 ELSIF NOT is_match(p_src,p_dest,p_attr_idx) THEN
362 l_dest := p_dest;
363 numspc:=1;
364 l_dest := rtrim(p_dest);
365 while (instr(p_src,' ',1,numspc)>0) LOOP
366 numspc:=numspc+1;
367 END LOOP;
368 destspc := instr(p_dest,' ',1,numspc)-1;
369 IF (destspc>0) THEN
370 l_dest := substr(l_dest,1,destspc);
371 END IF;
372 IF is_similar(p_src,l_dest,p_min_similarity)=1 THEN
373 RETURN TRUE;
374 ELSE
375 RETURN FALSE;
376 END IF;
377 ELSE
378 RETURN TRUE;
379 END IF;
380 END;
381
382 FUNCTION is_similar(
383 p_src IN VARCHAR2,
384 p_dest IN VARCHAR2,
385 p_min_similarity IN NUMBER := 100)
386 RETURN NUMBER IS
387
388 m NUMBER;
389 n NUMBER;
390
391 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
392
393 alist NumberList;
394 blist NumberList;
395
396 cost NUMBER;
397
398 D VARCHAR2(1 CHAR );
399 C VARCHAR2(1 CHAR );
400
401 BEGIN
402
403 n := nvl(LENGTH(p_src),0);
404 m := nvl(LENGTH(p_dest),0);
405
406 IF n = 0 THEN
407 return m;
408 END IF;
409 IF m = 0 THEN
410 return n;
411 END IF;
412
413 FOR I IN 1..(M+1) LOOP
414 alist(I) := I-1;
415 END LOOP;
416
417 FOR I IN 1..N LOOP
418 IF mod(I,2) = 1 THEN
419 blist(1) := I;
420 ELSE
421 alist(1) := I;
422 END IF;
423
424 C := substr(p_src,I,1);
425
426 FOR J IN 2..(M+1) LOOP
427 D := substr(p_dest,J-1,1);
428 COST := 1;
429 IF D=C THEN
430 COST := 0;
431 END IF;
432 IF mod(I,2) = 1 THEN
433 blist(J) := least(blist(J-1)+1,alist(J)+1,alist(j-1)+COST);
434 ELSE
435 alist(J) := least(alist(J-1)+1,blist(J)+1,blist(j-1)+COST);
436 END IF;
437 END LOOP;
438 END LOOP;
439 IF mod(n,2) = 1 THEN
440 IF (1-(blist(m+1)/n))*100 > p_min_similarity THEN
441 RETURN 1;
442 ELSE
443 RETURN 0;
444 END IF;
445 ELSE
446 IF (1-(alist(m+1)/n))*100 > p_min_similarity THEN
447 RETURN 1;
448 ELSE
449 RETURN 0;
450 END IF;
451 END IF;
452 EXCEPTION
453 WHEN OTHERS THEN
454 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
455 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_DQM_SEARCH_UTIL.is_similar');
456 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
457 FND_MSG_PUB.ADD;
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 END;
460
461 -- This Function tests if the DQM indexes have been created
462 -- and if they are valid
463 FUNCTION is_dqm_available
464 RETURN VARCHAR2 IS
465
466 T NUMBER;
467 BEGIN
468
469 BEGIN
470 SELECT 1 INTO T FROM HZ_STAGED_PARTIES
471 WHERE ROWNUM=1
472 AND CONTAINS (concat_col, 'dummy_string')>0;
473 EXCEPTION
474 WHEN NO_DATA_FOUND THEN
475 NULL;
476 WHEN OTHERS THEN
477 RETURN FND_API.G_FALSE;
478 END;
479 BEGIN
480 SELECT 1 INTO T FROM HZ_STAGED_PARTY_SITES
481 WHERE ROWNUM=1
482 AND CONTAINS (CONCAT_COL, 'dummy_string')>0;
483 EXCEPTION
484 WHEN NO_DATA_FOUND THEN
485 NULL;
486 WHEN OTHERS THEN
487 RETURN FND_API.G_FALSE;
488 END;
489 BEGIN
490 SELECT 1 INTO T FROM HZ_STAGED_CONTACTS
491 WHERE ROWNUM=1
492 AND CONTAINS (CONCAT_COL, 'dummy_string')>0;
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 NULL;
496 WHEN OTHERS THEN
497 RETURN FND_API.G_FALSE;
498 END;
499 BEGIN
500 SELECT 1 INTO T FROM HZ_STAGED_CONTACT_POINTS
501 WHERE ROWNUM=1
502 AND CONTAINS (CONCAT_COL, 'dummy_string')>0;
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 NULL;
506 WHEN OTHERS THEN
507 RETURN FND_API.G_FALSE;
508 END;
509 RETURN FND_API.G_TRUE;
510 END;
511
512
513 FUNCTION is_dqm_available(
514 p_match_rule_id NUMBER
515 )
516 RETURN VARCHAR2 IS
517
518 T NUMBER;
519 X VARCHAR2(1);
520 l_comp_flag VARCHAR2(1);
521 l_count NUMBER;
522 CURSOR c1 is select compilation_flag from hz_match_rules_b where match_rule_id = p_match_rule_id;
523 CURSOR c2 is select count(*)
524 from hz_trans_functions_b
525 where function_id in (
526 select function_id
527 from hz_match_rule_primary mp, hz_primary_trans pt
528 where match_rule_id = p_match_rule_id
529 and mp.primary_attribute_id = pt.primary_attribute_id
530 union
531 select function_id
532 from hz_match_rule_secondary ms, hz_secondary_trans st
533 where match_rule_id = p_match_rule_id
534 and ms.secondary_attribute_id = st.secondary_attribute_id
535 )
536 and staged_flag <> 'Y'
537 and nvl(active_flag, 'Y') <> 'N' ;
538
539 BEGIN
540 X := hz_dqm_search_util.is_dqm_available;
541 IF (X = FND_API.G_TRUE) THEN
542 OPEN c1;
543 FETCH c1 INTO l_comp_flag;
544 IF (l_comp_flag = 'C') THEN
545 OPEN c2;
546 FETCH c2 INTO l_count;
547 IF (l_count = 0) THEN
548 X := FND_API.G_TRUE;
549 ELSE
550 X := FND_API.G_FALSE;
551 END IF;
552 ELSE
553 X := FND_API.G_FALSE;
554 END IF;
555 ELSE
556 X := FND_API.G_FALSE;
557 END IF;
558 RETURN X;
559 END is_dqm_available;
560
561
562
563 FUNCTION ESTIMATED_LENGTH(p_str VARCHAR2) RETURN NUMBER IS
564
565 l_tok VARCHAR2(4000);
566 l_str VARCHAR2(4000);
567 len NUMBER := 0;
568 eqpos NUMBER;
569 BEGIN
570 RETURN lengthb(p_str);
571 END;
572
573 PROCEDURE update_word_list (
574 p_repl_word VARCHAR2,
575 p_word_list_id NUMBER) IS
576 repl_str VARCHAR2(2000);
577 FIRST BOOLEAN;
578 begin
579
580 NULL;
581 END;
582
583 PROCEDURE get_quality_score (
584 p_srch_ctx_id IN NUMBER,
585 p_match_rule_id IN NUMBER
586 ) IS
587
588 l_score NUMBER;
589 l_quality_weight NUMBER;
590 l_quality_score NUMBER;
591 l_final_score NUMBER ;
592 l_rule_purpose VARCHAR2(1);
593 l_party_rec HZ_PARTIES%ROWTYPE;
594 l_mms NUMBER;
595 BEGIN
596 l_quality_weight := hz_dqm_quality_uh_pkg.get_quality_weighting(p_match_rule_id);
597 IF (l_quality_weight > 0) THEN
598 select rule_purpose into l_rule_purpose
599 from hz_match_rules_vl
600 where match_rule_id = p_match_rule_id;
601
602 IF ( l_rule_purpose = 'S') THEN
603 FOR TX IN (
604 select party_id, score
605 from hz_matched_parties_gt
606 where search_context_id = p_srch_ctx_id)
607 LOOP
608 select * into l_party_rec
609 from HZ_PARTIES
610 where party_id = TX.party_id;
611 l_quality_score := hz_dqm_quality_uh_pkg.get_quality_score(p_match_rule_id, l_party_rec);
612 l_score := TX.score;
613 l_final_score := (l_score * ( 100 - l_quality_weight) + l_quality_score * l_quality_weight)/ 100;
614 update hz_matched_parties_gt
615 set score = l_final_score
616 where search_context_id = p_srch_ctx_id
617 and party_id = TX.party_id;
618 END LOOP;
619 ELSIF ( l_rule_purpose = 'D') THEN
620 select sum(score) into l_mms
621 from hz_match_rule_secondary
622 where match_rule_id = p_match_rule_id;
623 FOR TX IN (select party_id, score
624 from hz_matched_parties_gt
625 where search_context_id = p_srch_ctx_id)
626 LOOP
627 select * into l_party_rec
628 from HZ_PARTIES
629 where party_id = TX.party_id;
630 l_score := TX.score;
631 l_quality_score := hz_dqm_quality_uh_pkg.get_quality_score(p_match_rule_id, l_party_rec);
632 l_final_score := (l_score - (l_score * l_quality_weight)/100 + (l_quality_score * l_quality_weight* l_mms)/(100*100));
633 update hz_matched_parties_gt
634 set score = l_final_score
635 where search_context_id = p_srch_ctx_id
636 and party_id = TX.party_id;
637 END LOOP;
638 END IF;
639 END IF;
640 EXCEPTION WHEN OTHERS THEN
641 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_QUAL_SCORE'); -- Error occured in obtaining a quality score. Please contact system adminsitrator or turn profile option off to run DQM without quality score.
642 FND_MSG_PUB.ADD;
643 RAISE;
644 END get_quality_score;
645
646
647 FUNCTION validate_trans_proc(
648 P_PROCEDURE_NAME IN VARCHAR2
649 ) return VARCHAR2 IS
650 c NUMBER;
651 l_sql VARCHAR2(255);
652 dotCheck NUMBER;
653 procCheck NUMBER;
654 l_sqlstr VARCHAR2(4000);
655 l_status VARCHAR2(255);
656 l_owner1 VARCHAR2(255);
657 l_temp VARCHAR2(255);
658 BEGIN
659 select instr(P_PROCEDURE_NAME,'.') into dotCheck from dual;
660 IF dotCheck = 0 THEN
661
662 /*select count(*) into procCheck from sys.all_objects
663 where object_name=trim(upper(P_PROCEDURE_NAME))
664 and (object_type='PROCEDURE' OR object_type='FUNCTION');*/
665
666 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
667 l_sqlstr := ' select count(*) from sys.all_procedures where object_name=trim(upper(:P_PROCEDURE_NAME)) and owner=:2';
668 EXECUTE IMMEDIATE l_sql into procCheck USING P_PROCEDURE_NAME,l_owner1;
669 END IF;
670
671 IF procCheck=0 THEN
672 RETURN 'INVALID';
673 ELSE
674 RETURN 'VALID';
675 END IF;
676 ELSE
677 c := dbms_sql.open_cursor;
678 l_sql := 'select ' || P_PROCEDURE_NAME ||
679 '(:attrval,:lang,:attr,:entity) from dual';
680 dbms_sql.parse(c,l_sql,2);
681 dbms_sql.close_cursor(c);
682 RETURN 'VALID';
683 END IF;
684 EXCEPTION WHEN OTHERS THEN
685 RETURN 'INVALID';
686 END validate_trans_proc;
687
688
689 FUNCTION validate_custom_proc(
690 P_CUST_PROCEDURE_NAME IN VARCHAR2
691 ) return VARCHAR2 IS
692 c NUMBER;
693 dotCheck NUMBER;
694 procCheck NUMBER;
695 l_sql VARCHAR2(255);
696 l_sqlstr VARCHAR2(4000);
697 l_status VARCHAR2(255);
698 l_owner1 VARCHAR2(255);
699 l_temp VARCHAR2(255);
700 BEGIN
701 select instr(P_CUST_PROCEDURE_NAME,'.') into dotCheck from dual;
702 IF dotCheck = 0 THEN
703
704 /*select count(*) into procCheck from sys.all_objects
705 where object_name=trim(upper(P_CUST_PROCEDURE_NAME))
706 and (object_type='PROCEDURE' OR object_type='FUNCTION');*/
707
708 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
709 l_sqlstr := ' select count(*) from sys.all_procedures where object_name=trim(upper(:1)) and owner=:2 ';
710 EXECUTE IMMEDIATE l_sql into procCheck USING P_CUST_PROCEDURE_NAME,l_owner1;
711 END IF;
712
713 IF procCheck=0 THEN
714 RETURN 'INVALID';
715 ELSE
716 RETURN 'VALID';
717 END IF;
718 ELSE
719 c := dbms_sql.open_cursor;
720 l_sql := 'select ' || P_CUST_PROCEDURE_NAME ||
721 '(:record_id,:entity,:attr) from dual';
722 dbms_sql.parse(c,l_sql,2);
723 dbms_sql.close_cursor(c);
724 RETURN 'VALID';
725 END IF;
726 EXCEPTION WHEN OTHERS THEN
727 RETURN 'INVALID';
728 END validate_custom_proc;
729
730 END ;
[Home] [Help]