6 | NAME
1 PACKAGE BODY HZ_MATCH_RULE_51 AS
2 /*=======================================================================+
3 | Copyright (c) 1999 Oracle Corporation Redwood Shores, California, USA|
4 | All rights reserved. |
5 +=======================================================================+
7 | HZ_MATCH_RULE_51
8 |
9 | DESCRIPTION
10 |
11 | Compiled by the HZ Match Rule Compiler
12 | -- Do Not Modify --
13 |
14 | PUBLIC PROCEDURES
15 | find_parties
16 | get_matching_party_sites
17 | get_matching_contacts
18 | get_matching_contact_points
19 | get_score_details
20 |
21 | HISTORY
22 | 28-NOV-2012 Generated by HZ Match Rule Compiler
23 |
24 *=======================================================================*/
25
26 -- ==========================================================================================
27 -- ============MATCH RULE COMPILER GENERATED CODE FOR BULK MATCH RULES ======================
28 -- ==========================================================================================
29
30 TYPE vlisttype IS TABLE of VARCHAR2(255) INDEX BY BINARY_INTEGER ;
31 call_order vlisttype;
32 call_max_score HZ_PARTY_SEARCH.IDList;
33 call_type vlisttype;
34 g_party_stage_rec HZ_PARTY_STAGE.party_stage_rec_type;
35 g_party_site_stage_list HZ_PARTY_STAGE.party_site_stage_list;
36 g_contact_stage_list HZ_PARTY_STAGE.contact_stage_list;
37 g_contact_pt_stage_list HZ_PARTY_STAGE.contact_pt_stage_list;
38 g_mappings HZ_PARTY_SEARCH.IDList;
39 g_max_id NUMBER:=2000000000;
40
41 g_debug_count NUMBER := 0;
42 g_score_until_thresh BOOLEAN:=false;
43
44 g_thres_score NUMBER:=1000;
45
46 /************************************************
47 This procedure checks if the input search criteria
48 is valid. It checks if :
49 1. At least one primary condition is passed
50 ************************************************/
51
52 FUNCTION check_parties_bulk(
53 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type)
54 RETURN VARCHAR2 IS
55 BEGIN
56 IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
57 RETURN 'Y' ;
58 END IF;
59 IF p_party_search_rec.JGZZ_FISCAL_CODE IS NOT NULL THEN
60 RETURN 'Y' ;
61 END IF;
62 RETURN null;
63 EXCEPTION
64 WHEN OTHERS THEN
65 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
66 FND_MESSAGE.SET_TOKEN('PROC','check_parties_bulk');
67 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
68 FND_MSG_PUB.ADD;
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END check_parties_bulk ;
71
72 /************************************************
73 This procedure checks if the input search condition
74 has party site criteria.
75 ************************************************/
76
77 FUNCTION check_party_sites_bulk(
78 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list
79 )
80 RETURN VARCHAR2 IS
81 x_primary boolean := FALSE;
82 BEGIN
83 FOR I IN 1..p_party_site_list.COUNT LOOP
84 IF p_party_site_list(I).ADDRESS IS NOT NULL
85 OR p_party_site_list(I).POSTAL_CODE IS NOT NULL
86 THEN
87 x_primary := TRUE;
88 END IF;
89 EXIT WHEN x_primary;
90 END LOOP;
91
92 IF x_primary = TRUE THEN RETURN 'Y'; ELSE RETURN null; END IF;
93 EXCEPTION
94 WHEN OTHERS THEN
95 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
96 FND_MESSAGE.SET_TOKEN('PROC','check_party_sites_bulk');
97 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
98 FND_MSG_PUB.ADD;
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END check_party_sites_bulk ;
101
102 /************************************************
103 This procedure checks if the input search condition
104 has contact criteria.
105 ************************************************/
106
107 FUNCTION check_contacts_bulk (
108 p_contact_list IN HZ_PARTY_SEARCH.contact_list
109 )
110 RETURN VARCHAR2 IS
111 x_primary boolean := FALSE;
112 BEGIN
113 FOR I IN 1..p_contact_list.COUNT LOOP
114 NULL;
115 END LOOP;
116
117 IF x_primary = TRUE THEN RETURN 'Y'; ELSE RETURN null; END IF;
118 EXCEPTION
119 WHEN OTHERS THEN
120 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
121 FND_MESSAGE.SET_TOKEN('PROC','check_contacts_bulk');
122 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
123 FND_MSG_PUB.ADD;
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END check_contacts_bulk ;
126
127 /************************************************
128 This procedure checks if the input search condition
129 has contact criteria.
130 ************************************************/
131
132 FUNCTION check_cpts_bulk (
133 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list
134 )
135 RETURN VARCHAR2 IS
136 x_primary boolean := FALSE;
137 BEGIN
138 FOR I IN 1..p_contact_point_list.COUNT LOOP
139 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
140 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
141 THEN
142 x_primary := TRUE;
143 END IF;
144 EXIT WHEN x_primary;
145 END LOOP;
146
147 IF x_primary = TRUE THEN RETURN 'Y'; ELSE RETURN null; END IF;
148 EXCEPTION
149 WHEN OTHERS THEN
150 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
151 FND_MESSAGE.SET_TOKEN('PROC','check_cpts_bulk');
152 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
153 FND_MSG_PUB.ADD;
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END check_cpts_bulk ;
156
160 IF in_id<g_max_id THEN
157 FUNCTION map_id (in_id NUMBER) RETURN NUMBER IS
158 l_newidx NUMBER;
159 BEGIN
161 RETURN in_id;
162 ELSE
163 FOR I in 1..g_mappings.COUNT LOOP
164 IF in_id = g_mappings(I) THEN
165 RETURN (g_max_id+I);
166 END IF;
167 END LOOP;
168 l_newidx := g_mappings.COUNT+1;
169 g_mappings(l_newidx) := in_id;
170 RETURN (g_max_id+l_newidx);
171 END IF;
172 END;
173 FUNCTION GET_PARTY_SCORE
174 (
175 p_table_TX2 VARCHAR2
176 ,p_table_TX59 VARCHAR2
177 ,p_table_TX45 VARCHAR2
178 ) RETURN NUMBER IS
179 total NUMBER := 0;
180 BEGIN
181 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
185 RETURN total;
182 hz_utility_v2pub.debug(p_message=>'GET_PARTY_SCORE ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
183 END IF;
184 IF g_score_until_thresh AND (total)>=g_thres_score THEN
186 END IF;
187 IF HZ_DQM_SEARCH_UTIL.is_match(g_party_stage_rec.TX2, p_table_TX2,6) THEN
188 total := total+80;
189 IF g_score_until_thresh AND (total)>=g_thres_score THEN
190 RETURN total;
191 END IF;
192 ELSIF HZ_DQM_SEARCH_UTIL.is_match(g_party_stage_rec.TX59, p_table_TX59,138) THEN
193 total := total+72;
194 IF g_score_until_thresh AND (total)>=g_thres_score THEN
195 RETURN total;
196 END IF;
197 END IF;
198 IF HZ_DQM_SEARCH_UTIL.is_match(g_party_stage_rec.TX45, p_table_TX45,50) THEN
199 total := total+200;
200 IF g_score_until_thresh AND (total)>=g_thres_score THEN
201 RETURN total;
202 END IF;
203 END IF;
204 RETURN total;
205 END;
206 FUNCTION GET_PARTY_SITES_SCORE (
207 x_matchidx OUT NUMBER
208 ,p_table_TX26 VARCHAR2
209 ,p_table_TX9 VARCHAR2
210 ,p_table_TX14 VARCHAR2
211 ,p_table_TX22 VARCHAR2
212 ) RETURN NUMBER IS
213 maxscore NUMBER := 0;
214 l_current_score NUMBER := 0;
215 BEGIN
216 x_matchidx := 0;
217 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
218 RETURN l_current_score;
219 END IF;
220 FOR J IN 1..g_party_site_stage_list.COUNT LOOP
221 l_current_score := 0;
222 IF HZ_DQM_SEARCH_UTIL.is_match(g_party_site_stage_list(J).TX26, p_table_TX26,(50000*(J-1)+140)) THEN
223 l_current_score:=l_current_score+ 100;
224 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
225 x_matchidx:=J;
226 RETURN l_current_score;
227 END IF;
228 END IF;
229 IF HZ_DQM_SEARCH_UTIL.is_match(g_party_site_stage_list(J).TX9, p_table_TX9,(50000*(J-1)+72)) THEN
230 l_current_score:=l_current_score+ 5;
231 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
232 x_matchidx:=J;
233 RETURN l_current_score;
234 END IF;
235 END IF;
236 IF HZ_DQM_SEARCH_UTIL.is_match(g_party_site_stage_list(J).TX14, p_table_TX14,(50000*(J-1)+77)) THEN
237 l_current_score:=l_current_score+ 5;
238 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
239 x_matchidx:=J;
240 RETURN l_current_score;
241 END IF;
242 END IF;
243 IF HZ_DQM_SEARCH_UTIL.is_match(g_party_site_stage_list(J).TX22, p_table_TX22,(50000*(J-1)+85)) THEN
244 l_current_score:=l_current_score+ 5;
245 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
246 x_matchidx:=J;
247 RETURN l_current_score;
248 END IF;
249 END IF;
250 IF maxscore<l_current_score THEN
254 END LOOP;
251 maxscore:=l_current_score;
252 x_matchidx:=J;
253 END IF;
255 RETURN maxscore;
256 END;
257 FUNCTION GET_CONTACTS_SCORE (
258 x_matchidx OUT NUMBER
259 ) RETURN NUMBER IS
260 maxscore NUMBER := 0;
261 l_current_score NUMBER := 0;
262 BEGIN
263 x_matchidx := 0;
264 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
265 RETURN l_current_score;
266 END IF;
267 FOR J IN 1..g_contact_stage_list.COUNT LOOP
268 l_current_score := 0;
269 IF maxscore<l_current_score THEN
270 maxscore:=l_current_score;
271 x_matchidx:=J;
272 END IF;
273 END LOOP;
274 RETURN maxscore;
275 END;
276 FUNCTION GET_CONTACT_POINTS_SCORE (
277 x_matchidx OUT NUMBER
278 ,p_table_TX5 VARCHAR2
279 ,p_table_TX158 VARCHAR2
280 ,p_table_TX10 VARCHAR2
281 ) RETURN NUMBER IS
282 maxscore NUMBER := 0;
283 l_current_score NUMBER := 0;
284 BEGIN
285 x_matchidx := 0;
286 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
287 RETURN l_current_score;
288 END IF;
289 FOR J IN 1..g_contact_pt_stage_list.COUNT LOOP
290 l_current_score := 0;
291 IF HZ_DQM_SEARCH_UTIL.is_match(g_contact_pt_stage_list(J).TX5, p_table_TX5,(50000*(J-1)+114)) THEN
292 l_current_score:=l_current_score+ 60;
293 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
294 x_matchidx:=J;
295 RETURN l_current_score;
296 END IF;
297 END IF;
298 IF HZ_DQM_SEARCH_UTIL.is_match(g_contact_pt_stage_list(J).TX158, p_table_TX158,(50000*(J-1)+128)) THEN
299 l_current_score:=l_current_score+ 70;
300 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
301 x_matchidx:=J;
302 RETURN l_current_score;
303 END IF;
304 ELSIF -- l_current_score<70 AND
305 HZ_DQM_SEARCH_UTIL.is_match(g_contact_pt_stage_list(J).TX10, p_table_TX10,(50000*(J-1)+120)) THEN
306 l_current_score:=l_current_score+ 70;
307 IF g_score_until_thresh AND (l_current_score)>=g_thres_score THEN
308 x_matchidx:=J;
309 RETURN l_current_score;
310 END IF;
311 END IF;
312 IF maxscore<l_current_score THEN
313 maxscore:=l_current_score;
314 x_matchidx:=J;
315 END IF;
316 END LOOP;
317 RETURN maxscore;
318 END;
319 FUNCTION get_attrib_val(
320 p_record_id NUMBER
321 ,p_entity VARCHAR2
322 ,p_attribute VARCHAR2
323 ) RETURN VARCHAR2 IS
324 l_matched_value VARCHAR2(2000);
325 l_party_type VARCHAR2(255);
326 BEGIN
327 IF p_entity = 'PARTY' THEN
328 IF p_attribute = 'PARTY_NAME' THEN
329
330 SELECT party_type INTO l_party_type
331 FROM HZ_PARTIES
332 WHERE party_id = p_record_id;
333 IF l_party_type = 'ORGANIZATION' THEN
334 SELECT PARTY_NAME INTO l_matched_value
335 FROM HZ_PARTIES
336 WHERE party_id = p_record_id
337 and rownum = 1;
338 ELSIF l_party_type = 'PERSON' THEN
339 SELECT PARTY_NAME INTO l_matched_value
340 FROM HZ_PARTIES
341 WHERE party_id = p_record_id
342 and rownum = 1;
343 END IF;
344 RETURN l_matched_value;
345 END IF;
346 IF p_attribute = 'JGZZ_FISCAL_CODE' THEN
347
348 SELECT party_type INTO l_party_type
349 FROM HZ_PARTIES
350 WHERE party_id = p_record_id;
351 IF l_party_type = 'ORGANIZATION' THEN
352 SELECT JGZZ_FISCAL_CODE INTO l_matched_value
356 and rownum = 1;
353 FROM HZ_ORGANIZATION_PROFILES
354 WHERE party_id = p_record_id
355 and effective_end_date is null
357 ELSIF l_party_type = 'PERSON' THEN
358 SELECT JGZZ_FISCAL_CODE INTO l_matched_value
359 FROM HZ_PERSON_PROFILES
360 WHERE party_id = p_record_id
361 and effective_end_date is null
362 and rownum = 1;
363 END IF;
364 RETURN l_matched_value;
365 END IF;
366 NULL;
367 END IF;
368 IF p_entity = 'PARTY_SITES' THEN
369 IF p_attribute = 'ADDRESS' THEN
370 l_matched_value := HZ_PARTY_ACQUIRE.get_address (p_record_id, p_entity,'ADDRESS','Y');
371 RETURN l_matched_value;
372 END IF;
373 IF p_attribute = 'CITY' THEN
374 BEGIN
375 SELECT HZ_LOCATIONS.CITY
376 INTO l_matched_value
380 EXCEPTION
377 FROM HZ_PARTY_SITES, HZ_LOCATIONS
378 WHERE HZ_PARTY_SITES.party_site_id = p_record_id
379 AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
381 WHEN NO_DATA_FOUND THEN
382 l_matched_value := 'Err';
383 END;
384 RETURN l_matched_value;
385 END IF;
386 IF p_attribute = 'STATE' THEN
387 BEGIN
388 SELECT HZ_LOCATIONS.STATE
389 INTO l_matched_value
390 FROM HZ_PARTY_SITES, HZ_LOCATIONS
391 WHERE HZ_PARTY_SITES.party_site_id = p_record_id
392 AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
393 EXCEPTION
394 WHEN NO_DATA_FOUND THEN
395 l_matched_value := 'Err';
396 END;
397 RETURN l_matched_value;
398 END IF;
399 IF p_attribute = 'COUNTRY' THEN
400 BEGIN
401 SELECT HZ_LOCATIONS.COUNTRY
402 INTO l_matched_value
403 FROM HZ_PARTY_SITES, HZ_LOCATIONS
404 WHERE HZ_PARTY_SITES.party_site_id = p_record_id
405 AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 l_matched_value := 'Err';
409 END;
410 RETURN l_matched_value;
411 END IF;
412 NULL;
413 END IF;
414 IF p_entity = 'CONTACTS' THEN
415 NULL;
416 END IF;
417 IF p_entity = 'CONTACT_POINTS' THEN
418 IF p_attribute = 'EMAIL_ADDRESS' THEN
419 BEGIN
420 SELECT EMAIL_ADDRESS INTO l_matched_value
421 FROM HZ_CONTACT_POINTS
422 WHERE contact_point_id = p_record_id and rownum=1;
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 l_matched_value := 'Err';
426 END;
427 RETURN l_matched_value;
428 END IF;
429 IF p_attribute = 'RAW_PHONE_NUMBER' THEN
430 l_matched_value := HZ_PARTY_ACQUIRE.get_phone_number (p_record_id, p_entity,'RAW_PHONE_NUMBER','Y');
431 RETURN l_matched_value;
432 END IF;
433 NULL;
434 END IF;
435 END;
436 PROCEDURE INSERT_PARTY_SCORE (
437 p_party_id IN NUMBER
438 ,p_record_id IN NUMBER
439 ,p_search_ctx_id IN NUMBER
440 ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
441 ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
442 ,p_table_TX2 VARCHAR2
443 ,p_table_TX59 VARCHAR2
444 ,p_table_TX45 VARCHAR2
445 ,p_idx IN NUMBER) IS
446 l_current_score NUMBER:=0;
447 l_score NUMBER;
448 l_attrib_value VARCHAR2(2000);
449 BEGIN
450 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
451 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_PARTY_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
452 END IF;
453 l_score :=0;
454 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX2, p_table_TX2,(50000*(p_idx-1)+6)) THEN
455 l_score :=80;
456 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
457 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
458 END IF;
459 ELSIF l_current_score<72 AND
460 HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX59, p_table_TX59,(50000*(p_idx-1)+138)) THEN
461 l_score :=72;
462 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
463 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
464 END IF;
465 END IF;
466 IF l_score>0 THEN
467 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
468 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
469 END IF;
470 l_attrib_value := get_attrib_val(p_record_id,'PARTY','PARTY_NAME');
471 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
472 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
473 END IF;
474 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
475 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
476 VALUES (
477 p_party_id,p_record_id,p_search_ctx_id,'PARTY_NAME',
478 'PARTY', p_search_rec.PARTY_NAME,
479 l_attrib_value,l_score);
480 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
481 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
482 END IF;
483 END IF;
484 l_score :=0;
485 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX45, p_table_TX45,(50000*(p_idx-1)+50)) THEN
486 l_score :=200;
487 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
488 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
489 END IF;
490 END IF;
491 IF l_score>0 THEN
492 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
493 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
494 END IF;
495 l_attrib_value := get_attrib_val(p_record_id,'PARTY','JGZZ_FISCAL_CODE');
496 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
500 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
497 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
498 END IF;
499 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
501 VALUES (
502 p_party_id,p_record_id,p_search_ctx_id,'JGZZ_FISCAL_CODE',
503 'PARTY', p_search_rec.JGZZ_FISCAL_CODE,
504 l_attrib_value,l_score);
505 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
506 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
507 END IF;
508 END IF;
509 NULL;
510 END;
511 PROCEDURE INSERT_PARTY_SITES_SCORE (
512 p_party_id IN NUMBER
513 ,p_record_id IN NUMBER
514 ,p_search_ctx_id IN NUMBER
515 ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
516 ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
517 ,p_table_TX26 VARCHAR2
518 ,p_table_TX9 VARCHAR2
519 ,p_table_TX14 VARCHAR2
520 ,p_table_TX22 VARCHAR2
521 ,p_idx IN NUMBER) IS
522 l_current_score NUMBER:=0;
523 l_score NUMBER;
524 l_attrib_value VARCHAR2(2000);
525 BEGIN
526 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
527 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_PARTY_SITES_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
528 END IF;
529 l_score :=0;
530 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX26, p_table_TX26,(50000*(p_idx-1)+140)) THEN
531 l_score :=100;
532 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
533 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
534 END IF;
535 END IF;
536 IF l_score>0 THEN
537 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
538 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
539 END IF;
540 l_attrib_value := get_attrib_val(p_record_id,'PARTY_SITES','ADDRESS');
541 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
542 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
543 END IF;
544 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
545 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
546 VALUES (
547 p_party_id,p_record_id,p_search_ctx_id,'ADDRESS',
548 'PARTY_SITES', p_search_rec.ADDRESS,
549 l_attrib_value,l_score);
550 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
551 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
552 END IF;
553 END IF;
554 l_score :=0;
555 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX9, p_table_TX9,(50000*(p_idx-1)+72)) THEN
556 l_score :=5;
557 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
558 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
559 END IF;
560 END IF;
561 IF l_score>0 THEN
562 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
563 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
564 END IF;
565 l_attrib_value := get_attrib_val(p_record_id,'PARTY_SITES','CITY');
566 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
567 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
568 END IF;
569 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
570 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
571 VALUES (
572 p_party_id,p_record_id,p_search_ctx_id,'CITY',
573 'PARTY_SITES', p_search_rec.CITY,
574 l_attrib_value,l_score);
575 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
576 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
577 END IF;
578 END IF;
579 l_score :=0;
583 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
580 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX14, p_table_TX14,(50000*(p_idx-1)+77)) THEN
581 l_score :=5;
582 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
584 END IF;
585 END IF;
586 IF l_score>0 THEN
587 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
588 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
589 END IF;
590 l_attrib_value := get_attrib_val(p_record_id,'PARTY_SITES','STATE');
591 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
592 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
593 END IF;
594 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
595 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
596 VALUES (
597 p_party_id,p_record_id,p_search_ctx_id,'STATE',
598 'PARTY_SITES', p_search_rec.STATE,
599 l_attrib_value,l_score);
600 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
601 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
602 END IF;
603 END IF;
604 l_score :=0;
605 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX22, p_table_TX22,(50000*(p_idx-1)+85)) THEN
606 l_score :=5;
607 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
611 IF l_score>0 THEN
608 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
609 END IF;
610 END IF;
612 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
613 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
614 END IF;
615 l_attrib_value := get_attrib_val(p_record_id,'PARTY_SITES','COUNTRY');
616 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
617 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
618 END IF;
619 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
620 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
621 VALUES (
622 p_party_id,p_record_id,p_search_ctx_id,'COUNTRY',
623 'PARTY_SITES', p_search_rec.COUNTRY,
624 l_attrib_value,l_score);
625 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
626 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
627 END IF;
628 END IF;
629 NULL;
630 END;
631 PROCEDURE INSERT_CONTACTS_SCORE (
632 p_party_id IN NUMBER
633 ,p_record_id IN NUMBER
634 ,p_search_ctx_id IN NUMBER
635 ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
636 ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
637 ,p_idx IN NUMBER) IS
638 l_current_score NUMBER:=0;
639 l_score NUMBER;
640 l_attrib_value VARCHAR2(2000);
641 BEGIN
642 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
643 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_CONTACTS_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
644 END IF;
645 NULL;
646 END;
647 PROCEDURE INSERT_CONTACT_POINTS_SCORE (
648 p_party_id IN NUMBER
649 ,p_record_id IN NUMBER
650 ,p_search_ctx_id IN NUMBER
651 ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
652 ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
653 ,p_table_TX5 VARCHAR2
654 ,p_table_TX158 VARCHAR2
655 ,p_table_TX10 VARCHAR2
656 ,p_idx IN NUMBER) IS
657 l_current_score NUMBER:=0;
658 l_score NUMBER;
659 l_attrib_value VARCHAR2(2000);
660 BEGIN
661 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
665 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX5, p_table_TX5,(50000*(p_idx-1)+114)) THEN
662 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_CONTACT_POINTS_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
663 END IF;
664 l_score :=0;
666 l_score :=60;
667 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
668 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
669 END IF;
670 END IF;
671 IF l_score>0 THEN
672 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
673 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
674 END IF;
675 l_attrib_value := get_attrib_val(p_record_id,'CONTACT_POINTS','EMAIL_ADDRESS');
676 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
677 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
678 END IF;
679 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
680 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
681 VALUES (
682 p_party_id,p_record_id,p_search_ctx_id,'EMAIL_ADDRESS',
683 'CONTACT_POINTS', p_search_rec.EMAIL_ADDRESS,
684 l_attrib_value,l_score);
685 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
686 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
687 END IF;
688 END IF;
689 l_score :=0;
690 IF HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX158, p_table_TX158,(50000*(p_idx-1)+128)) THEN
691 l_score :=70;
692 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
693 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
694 END IF;
695 ELSIF l_current_score<70 AND
696 HZ_DQM_SEARCH_UTIL.is_match(p_stage_rec.TX10, p_table_TX10,(50000*(p_idx-1)+120)) THEN
697 l_score :=70;
698 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
699 hz_utility_v2pub.debug(p_message=>'l_score is - '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
700 END IF;
701 END IF;
702 IF l_score>0 THEN
703 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
704 hz_utility_v2pub.debug(p_message=>'l_score > 0',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
705 END IF;
706 l_attrib_value := get_attrib_val(p_record_id,'CONTACT_POINTS','RAW_PHONE_NUMBER');
707 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
708 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
709 END IF;
710 INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
711 ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
712 VALUES (
713 p_party_id,p_record_id,p_search_ctx_id,'RAW_PHONE_NUMBER',
714 'CONTACT_POINTS', p_search_rec.RAW_PHONE_NUMBER,
715 l_attrib_value,l_score);
716 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
717 hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
718 END IF;
719 END IF;
720 NULL;
721 END;
722
723 /************************************************
727
724 This procedure populates global cond record
725 for the PARTY Entity
726 ************************************************/
728 PROCEDURE POP_PARTY_COND_REC(
729 p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
730 ) IS
731 BEGIN
732 NULL ;
733 EXCEPTION
734 WHEN OTHERS THEN
735 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
736 FND_MESSAGE.SET_TOKEN('PROC' , 'POP_PARTY_COND_REC');
737 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
738 FND_MSG_PUB.ADD;
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END;
741
745 for the PARTY_SITES Entity
742
743 /************************************************
744 This procedure populates global cond record
746 ************************************************/
747
748 PROCEDURE POP_PARTY_SITES_COND_REC(
749 p_search_list IN HZ_PARTY_SEARCH.party_site_list
750 ) IS
751 BEGIN
752 ---------POPULATE THE GLOBAL WORD CONDITION REC FOR PARTY_SITES-------------
753 ------ Populate global condition record only if search list is not empty -----------
754 IF p_search_list.COUNT > 0
755 THEN
756 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,p_search_list(1).COUNTRY);
757 END IF ;
758 EXCEPTION
759 WHEN OTHERS THEN
760 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
761 FND_MESSAGE.SET_TOKEN('PROC' , 'POP_PARTY_SITES_COND_REC');
762 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
763 FND_MSG_PUB.ADD;
764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
765 END;
766
767
768 /************************************************
769 This procedure populates global cond record
770 for the CONTACTS Entity
771 ************************************************/
772
773 PROCEDURE POP_CONTACTS_COND_REC(
774 p_search_list IN HZ_PARTY_SEARCH.contact_list
775 ) IS
776 BEGIN
777 NULL ;
778 EXCEPTION
779 WHEN OTHERS THEN
780 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
781 FND_MESSAGE.SET_TOKEN('PROC' , 'POP_CONTACTS_COND_REC');
782 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
783 FND_MSG_PUB.ADD;
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 END;
786
787
788 /************************************************
789 This procedure populates global cond record
790 for the CONTACT_POINTS Entity
791 ************************************************/
792
793 PROCEDURE POP_CONTACT_POINTS_COND_REC(
794 p_search_list IN HZ_PARTY_SEARCH.contact_point_list
795 ) IS
796 BEGIN
797 NULL ;
798 EXCEPTION
799 WHEN OTHERS THEN
800 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
801 FND_MESSAGE.SET_TOKEN('PROC' , 'POP_CONTACT_POINTS_COND_REC');
802 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
803 FND_MSG_PUB.ADD;
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805 END;
806
807 PROCEDURE init_score_context (
808 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
809 HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
810 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
811 HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
812 p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
813 HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
814 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
815 HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
816 ) IS
817 l_dummy NUMBER;
818 BEGIN
819 -- Transform search criteria
820 HZ_TRANS_PKG.clear_globals;
821 MAP_PARTY_REC(FALSE,p_party_search_rec, l_dummy, g_party_stage_rec);
822 MAP_PARTY_SITE_REC(FALSE,p_party_site_list, l_dummy, g_party_site_stage_list);
823 MAP_CONTACT_REC(FALSE,p_contact_list, l_dummy, g_contact_stage_list);
824 MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
825
826 END;
827 FUNCTION init_search(
828 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
829 HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
830 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
831 HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
832 p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
833 HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
834 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
835 HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
836 p_match_type IN VARCHAR2,
837 x_party_max_score OUT NUMBER,
838 x_ps_max_score OUT NUMBER,
839 x_contact_max_score OUT NUMBER,
840 x_cpt_max_score OUT NUMBER
841 ) RETURN NUMBER IS
842 l_entered_max_score NUMBER:=0;
843 l_ps_entered_max_score NUMBER:=0;
844 l_ct_entered_max_score NUMBER:=0;
845 l_cpt_entered_max_score NUMBER:=0;
846 vlist vlisttype;
847 maxscore HZ_PARTY_SEARCH.IDList;
848 l_name VARCHAR2(200);
849 l_idx NUMBER;
850 l_num NUMBER;
851 total NUMBER;
852 threshold NUMBER;
853 BEGIN
854 IF NOT check_prim_cond (p_party_search_rec,
855 p_party_site_list,
856 p_contact_list,
857 p_contact_point_list) THEN
858 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_PRIMARY_COND');
859 FND_MSG_PUB.ADD;
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862 HZ_DQM_SEARCH_UTIL.set_score;
863 g_mappings.DELETE;
864 g_party_site_stage_list.DELETE;
865 g_contact_stage_list.DELETE;
866 g_contact_pt_stage_list.DELETE;
867 call_order.DELETE;
868 call_max_score.DELETE;
869 HZ_DQM_SEARCH_UTIL.new_search;
870 HZ_TRANS_PKG.set_party_type(p_party_search_rec.PARTY_TYPE);
871 HZ_DQM_SEARCH_UTIL.set_num_eval(0);
872
873 -- Transform search criteria
874 MAP_PARTY_REC(TRUE,p_party_search_rec, l_entered_max_score, g_party_stage_rec);
875 MAP_PARTY_SITE_REC(TRUE,p_party_site_list, l_ps_entered_max_score, g_party_site_stage_list);
876 MAP_CONTACT_REC(TRUE,p_contact_list, l_ct_entered_max_score, g_contact_stage_list);
877 MAP_CONTACT_POINT_REC(TRUE,p_contact_point_list, l_cpt_entered_max_score, g_contact_pt_stage_list);
878
879
880 l_idx := l_entered_max_score+1;
881 vlist (l_idx) := 'PARTY';
882 maxscore (l_idx) := l_entered_max_score;
883 l_idx := l_ps_entered_max_score+1;
884 WHILE vlist.EXISTS(l_idx) LOOP
885 l_idx := l_idx+1;
886 END LOOP;
887 vlist (l_idx) := 'PARTY_SITE';
888 maxscore (l_idx) := l_ps_entered_max_score;
889
890 l_idx := l_ct_entered_max_score+1;
891 WHILE vlist.EXISTS(l_idx) LOOP
892 l_idx := l_idx+1;
893 END LOOP;
894 vlist (l_idx) := 'CONTACT';
895 maxscore (l_idx) := l_ct_entered_max_score;
896
897 l_idx := l_cpt_entered_max_score+1;
898 WHILE vlist.EXISTS(l_idx) LOOP
899 l_idx := l_idx+1;
900 END LOOP;
901 vlist (l_idx) := 'CONTACT_POINT';
902 maxscore (l_idx) := l_cpt_entered_max_score;
903
904 l_num := 1;
905 l_idx := vlist.LAST;
906 WHILE l_idx IS NOT NULL LOOP
907 call_order(l_num) := vlist(l_idx);
908 call_max_score(l_num) := maxscore(l_idx);
909 l_idx := vlist.PRIOR(l_idx);
910 l_num := l_num+1;
911 END LOOP;
912 call_order(5):='NONE';
913 IF p_match_type = ' OR ' THEN
914 threshold := 175;
915 l_idx := vlist.FIRST;
916 total := 0;
917 l_num := 4;
918 WHILE l_idx IS NOT NULL LOOP
919 total := total+maxscore(l_idx);
920 IF total<threshold THEN
921 call_type(l_num) := 'AND';
922 ELSE
923 call_type(l_num) := 'OR';
924 END IF;
925 l_idx := vlist.NEXT(l_idx);
926 l_num := l_num-1;
927 END LOOP;
928 ELSE
929 call_type(1) := 'OR';
930 call_type(2) := 'AND';
931 call_type(3) := 'AND';
932 call_type(4) := 'AND';
933 END IF;
934 x_party_max_score := l_entered_max_score;
935 x_ps_max_score := l_ps_entered_max_score;
936 x_contact_max_score := l_ct_entered_max_score;
937 x_cpt_max_score := l_cpt_entered_max_score;
938 RETURN (l_entered_max_score+l_ps_entered_max_score+l_ct_entered_max_score+l_cpt_entered_max_score);
939 END;
940
941
942 PROCEDURE open_party_cursor(
943 p_dup_party_id NUMBER,
944 p_restrict_sql VARCHAR2,
945 p_contains_str VARCHAR2,
946 p_search_ctx_id NUMBER,
947 p_match_str VARCHAR2,
948 p_search_merged VARCHAR2,
949 x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
950 l_sqlstr VARCHAR2(4000);
951 BEGIN
952 IF p_contains_str IS NULL THEN
953 OPEN x_cursor FOR
954 SELECT PARTY_ID , TX2, TX59, TX45
955 FROM HZ_STAGED_PARTIES stage
956 WHERE PARTY_ID = p_dup_party_id;
957 ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
958 IF p_search_ctx_id IS NULL THEN
959 OPEN x_cursor FOR
960 SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45
961 FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage
962 WHERE
963 srch.batch_id = -1
964 AND
965 (
966 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
967 -- do an or between all the transformations of an attribute --
968 (
969 (srch.TX59 is not null and stage.TX59 like srch.TX59 || '%')
970 )
971 OR
972 -- do an or between all the transformations of an attribute --
973 (
974 (srch.TX45 is not null and stage.TX45 like srch.TX45 || '%')
975 )
976 )
977 AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
978 AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'))
979 AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
980 ELSE
981 OPEN x_cursor FOR
982 SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45
983 FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage, HZ_SRCH_PARTIES srch
984 WHERE
985 srch.batch_id = -1
986 AND
987 (
988 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
989 -- do an or between all the transformations of an attribute --
990 (
991 (srch.TX59 is not null and stage.TX59 like srch.TX59 || '%')
992 )
993 OR
994 -- do an or between all the transformations of an attribute --
995 (
996 (srch.TX45 is not null and stage.TX45 like srch.TX45 || '%')
997 )
998 )
999 AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1000 AND d.party_id = stage.party_id
1001 AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1002 AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1003 AND (nvl(p_search_merged,'N')='Y' OR nvl(stage.status,'A') in ('A'));
1004 END IF;
1005 ELSE
1006 IF p_search_ctx_id IS NULL THEN
1007 l_sqlstr := 'SELECT /*+ ORDERED */ stage.PARTY_ID , stage.TX2, stage.TX59, stage.TX45'||
1008 ' FROM HZ_SRCH_PARTIES srch, HZ_STAGED_PARTIES stage'||
1009 ' WHERE'||
1010 'srch.batch_id = -1'||
1011 'AND'||
1012 '('||
1013 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1014 -- do an or between all the transformations of an attribute --
1015 '('||
1016 '(srch.TX59 is not null and stage.TX59 like srch.TX59 || ''%'')'||
1017 ')'||
1018 ' OR '||
1019 -- do an or between all the transformations of an attribute --
1020 '('||
1021 '(srch.TX45 is not null and stage.TX45 like srch.TX45 || ''%'')'||
1022 ')'||
1023 ')'||
1024 ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1025 ' AND ('||p_restrict_sql||')' ||
1026 ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1027 IF p_search_merged IS NULL OR p_search_merged <> 'Y' THEN
1028 l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1029 END IF;
1030 OPEN x_cursor FOR l_sqlstr USING p_contains_str
1031 ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1032 ,p_dup_party_id, p_dup_party_id;
1033 END IF;
1034 END IF;
1035 exception
1036 when others then
1037 if (instrb(SQLERRM,'DRG-51030')>0) then
1038 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_WILDCARD_ERR');
1039 FND_MSG_PUB.ADD;
1040 RAISE FND_API.G_EXC_ERROR;
1041 else
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043 end if;
1044 END;
1045
1046 PROCEDURE open_party_site_cursor(
1047 p_dup_party_id NUMBER,
1048 p_party_id NUMBER,
1049 p_restrict_sql VARCHAR2,
1050 p_contains_str VARCHAR2,
1051 p_search_ctx_id NUMBER,
1052 x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1053 l_sqlstr VARCHAR2(4000);
1054 BEGIN
1055 IF p_party_id IS NOT NULL THEN
1056 OPEN x_cursor FOR
1057 SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
1058 FROM HZ_STAGED_PARTY_SITES stage,HZ_SRCH_PSITES srch
1059 WHERE
1060 srch.batch_id = -1
1061 AND
1062 (
1063 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1064 -- do an or between all the transformations of an attribute --
1065 (
1066 (srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
1067 )
1068 )
1069 AND EXISTS (
1070 SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1071 WHERE p.PARTY_ID = stage.PARTY_ID
1072 AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
1073 AND stage.party_id = p_party_id;
1077 SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
1074 ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1075 IF p_search_ctx_id IS NULL THEN
1076 OPEN x_cursor FOR
1078 FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p
1079 WHERE
1080 srch.batch_id = -1
1081 AND
1082 (
1083 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1084 -- do an or between all the transformations of an attribute --
1085 (
1086 (srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
1087 )
1088 )
1089 AND p.PARTY_ID = stage.PARTY_ID
1090 AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36))
1091 AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1092 ELSE
1093 OPEN x_cursor FOR
1094 SELECT stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, stage.TX26, stage.TX9, stage.TX14, stage.TX22
1095 FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage, HZ_SRCH_PSITES srch
1096 WHERE
1097 srch.batch_id = -1
1098 AND
1099 (
1100 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1101 -- do an or between all the transformations of an attribute --
1102 (
1103 (srch.TX26 is not null and stage.TX26 like srch.TX26 || '%')
1104 )
1105 )
1106 AND d.search_context_id = p_search_ctx_id
1107 AND d.party_id = stage.party_id
1108 AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1109 END IF;
1110 ELSE
1111 l_sqlstr := 'SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID , stage.TX26, stage.TX9, stage.TX14, stage.TX22'||
1112 ' FROM HZ_SRCH_PSITES srch, HZ_STAGED_PARTY_SITES stage, HZ_STAGED_PARTIES p'||
1113 ' WHERE' ||
1114 'srch.batch_id = -1'||
1115 'AND'||
1116 '('||
1117 -------PARTY_SITES LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1118 -- do an or between all the transformations of an attribute --
1119 '('||
1120 '(srch.TX26 is not null and stage.TX26 like srch.TX26 || ''%'')'||
1121 ')'||
1122 ')'||
1123 ' AND p.party_id = stage.party_id ' ||
1124 ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36))) ' ||
1125 ' AND ('||p_restrict_sql||')' ||
1126 ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1127 OPEN x_cursor FOR l_sqlstr USING p_contains_str
1128 ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1129 ,p_dup_party_id, p_dup_party_id;
1130 END IF;
1131 exception
1132 when others then
1133 if (instrb(SQLERRM,'DRG-51030')>0) then
1134 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_WILDCARD_ERR');
1135 FND_MSG_PUB.ADD;
1136 RAISE FND_API.G_EXC_ERROR;
1137 else
1138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139 end if;
1140 END;
1141
1142 PROCEDURE open_contact_cursor(
1143 p_dup_party_id NUMBER,
1144 p_party_id NUMBER,
1145 p_restrict_sql VARCHAR2,
1146 p_contains_str VARCHAR2,
1147 p_search_ctx_id NUMBER,
1148 x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1149 l_sqlstr VARCHAR2(4000);
1150 BEGIN
1151 NULL ;
1152 END;
1153
1154 PROCEDURE open_contact_pt_cursor(
1155 p_dup_party_id NUMBER,
1156 p_party_id NUMBER,
1157 p_restrict_sql VARCHAR2,
1158 p_contains_str VARCHAR2,
1159 p_search_ctx_id NUMBER,
1160 x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1161 l_sqlstr VARCHAR2(4000);
1162 BEGIN
1163 IF p_party_id IS NOT NULL THEN
1164 OPEN x_cursor FOR
1165 SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID,stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX158, stage.TX10
1166 FROM HZ_STAGED_CONTACT_POINTS stage, HZ_SRCH_CPTS srch
1167 WHERE
1168 srch.batch_id = -1
1169 AND
1170 (
1171 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1172 -- do an or between all the transformations of an attribute --
1173 (
1174 (srch.TX5 is not null and stage.TX5 like srch.TX5 || '%')
1175 )
1176 OR
1177 -- do an or between all the transformations of an attribute --
1178 (
1179 (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1180 )
1181 )
1182 AND EXISTS (
1183 SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1184 WHERE p.PARTY_ID = stage.PARTY_ID
1185 AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
1186 AND stage.party_id = p_party_id;
1187 ELSIF p_restrict_sql IS NULL OR p_search_ctx_id IS NOT NULL THEN
1188 IF p_search_ctx_id IS NULL THEN
1189 OPEN x_cursor FOR
1190 SELECT /*+ USE_NL(srch stage) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX158, stage.TX10
1191 FROM HZ_SRCH_CPTS srch, HZ_STAGED_CONTACT_POINTS stage
1192 WHERE
1193 srch.batch_id = -1
1194 AND
1195 (
1196 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1197 -- do an or between all the transformations of an attribute --
1198 (
1199 (srch.TX5 is not null and stage.TX5 like srch.TX5 || '%')
1200 )
1201 OR
1202 -- do an or between all the transformations of an attribute --
1203 (
1204 (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1205 )
1206 )
1207 AND EXISTS (
1208 SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p
1209 WHERE p.PARTY_ID = stage.PARTY_ID
1210 AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
1211 AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1212 ELSE
1213 OPEN x_cursor FOR
1214 SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX158, stage.TX10
1215 FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage, HZ_SRCH_CPTS srch
1216 WHERE
1217 srch.batch_id = -1
1218 AND
1219 (
1220 -------PARTY LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1221 -- do an or between all the transformations of an attribute --
1222 (
1223 (srch.TX5 is not null and stage.TX5 like srch.TX5 || '%')
1224 )
1225 OR
1226 -- do an or between all the transformations of an attribute --
1227 (
1228 (srch.TX158 is not null and stage.TX158 like srch.TX158 || '%')
1229 )
1230 )
1231 AND d.search_context_id = p_search_ctx_id
1232 AND d.party_id = stage.party_id
1233 AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
1234 END IF;
1235 ELSE
1236 l_sqlstr := 'SELECT /*+ USE_NL(srch stage) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID , stage.TX5, stage.TX158, stage.TX10'||
1237 ' FROM HZ_SRCH_CPTS srch, HZ_STAGED_CONTACT_POINTS stage'||
1238 ' WHERE'||
1239 'srch.batch_id = -1'||
1240 'AND'||
1241 '('||
1242 -------CONTACT_POINTS LEVEL ACQUISITION ON NON-FILTER ATTRIBUTES USING B-TREE INDEXES ---------
1243 -- do an or between all the transformations of an attribute --
1244 '('||
1245 '(srch.TX5 is not null and stage.TX5 like srch.TX5 || ''%'')'||
1246 ')'||
1247 ' OR '||
1248 -- do an or between all the transformations of an attribute --
1249 '('||
1250 '(srch.TX158 is not null and stage.TX158 like srch.TX158 || ''%'')'||
1251 ')'||
1252 ')'||
1253 ' AND EXISTS ('||
1254 ' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p ' ||
1255 ' WHERE p.party_id = stage.party_id ' ||
1256 ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36))) ' ||
1257 ' AND ('||p_restrict_sql||')' ||
1258 ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1259 OPEN x_cursor FOR l_sqlstr USING p_contains_str
1260 ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1261 ,p_dup_party_id, p_dup_party_id;
1262 END IF;
1263 exception
1264 when others then
1265 if (instrb(SQLERRM,'DRG-51030')>0) then
1266 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_WILDCARD_ERR');
1267 FND_MSG_PUB.ADD;
1268 RAISE FND_API.G_EXC_ERROR;
1269 else
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271 end if;
1272 END;
1273
1274 FUNCTION get_new_score_rec (
1275 p_init_total_score NUMBER,
1276 p_init_party_score NUMBER,
1277 p_init_party_site_score NUMBER,
1278 p_init_contact_score NUMBER,
1279 p_init_contact_point_score NUMBER,
1280 p_party_id NUMBER,
1281 p_party_site_id NUMBER,
1282 p_org_contact_id NUMBER,
1283 p_contact_point_id NUMBER)
1284 RETURN HZ_PARTY_SEARCH.score_rec IS
1285 l_score_rec HZ_PARTY_SEARCH.score_rec;
1286 BEGIN
1287 l_score_rec.TOTAL_SCORE := p_init_total_score;
1288 l_score_rec.PARTY_SCORE := p_init_party_score;
1289 l_score_rec.PARTY_SITE_SCORE := p_init_party_site_score;
1290 l_score_rec.CONTACT_SCORE := p_init_contact_score;
1291 l_score_rec.CONTACT_POINT_SCORE := p_init_contact_point_score;
1292 l_score_rec.PARTY_ID := p_party_id;
1293 l_score_rec.PARTY_SITE_ID := p_party_site_id;
1294 l_score_rec.ORG_CONTACT_ID := p_org_contact_id;
1295 l_score_rec.CONTACT_POINT_ID := p_contact_point_id;
1296 RETURN l_score_rec;
1297 END;
1298
1299 /**********************************************************
1300 This procedure finds the set of parties that match the search
1301 criteria and returns a scored set of parties
1302
1303 The steps in executing the search are as follows
1307 4. Execution of Secondary queries to score results
1304 1. Initialization and error checks
1305 2. Setup of intermedia query strings for Acquisition query
1306 3. Execution of Acquisition query
1308 5. Setup of data temporary table to return search results
1309 **********************************************************/
1310
1311 -------------------------------------------------------------------------------------
1312 -------------------- BULK MATCH RULE ::: find_parties ------------------------------
1313 -------------------------------------------------------------------------------------
1314 PROCEDURE find_parties (
1315 p_rule_id IN NUMBER,
1316 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
1317 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
1318 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
1319 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
1320 p_restrict_sql IN VARCHAR2,
1321 p_match_type IN VARCHAR2,
1322 p_search_merged IN VARCHAR2,
1323 p_dup_party_id IN NUMBER,
1324 p_dup_set_id IN NUMBER,
1325 p_dup_batch_id IN NUMBER,
1326 p_ins_details IN VARCHAR2,
1327 x_search_ctx_id OUT NUMBER,
1328 x_num_matches OUT NUMBER
1329 ) IS
1330
1331 -- Strings to hold the generated Intermedia query strings
1332 l_party_contains_str VARCHAR2(32000);
1333 l_party_site_contains_str VARCHAR2(32000);
1334 l_contact_contains_str VARCHAR2(32000);
1335 l_contact_pt_contains_str VARCHAR2(32000);
1336 l_denorm_str VARCHAR2(32000);
1337 l_ps_denorm_str VARCHAR2(32000);
1338 l_ct_denorm_str VARCHAR2(32000);
1339 l_cpt_denorm_str VARCHAR2(32000);
1340
1341 -- Other local variables
1342 l_match_str VARCHAR2(30); -- Match type (AND or OR)
1343 l_sqlstr VARCHAR2(32000); -- Dynamic SQL String
1344 -- For Score calculation
1345 l_max_score NUMBER;
1346 l_match_idx NUMBER;
1347 l_entered_max_score NUMBER;
1348 FIRST BOOLEAN;
1349 l_search_ctx_id NUMBER; -- Generated Search Context ID
1350
1351 l_TX2 VARCHAR2(2000);
1352 l_TX26 VARCHAR2(2000);
1353 l_TX9 VARCHAR2(2000);
1354 l_TX22 VARCHAR2(2000);
1355 l_TX158 VARCHAR2(2000);
1356 l_TX5 VARCHAR2(2000);
1357 l_TX10 VARCHAR2(2000);
1358 l_TX59 VARCHAR2(2000);
1359 l_TX45 VARCHAR2(2000);
1360 l_TX14 VARCHAR2(2000);
1361 H_SCORES HZ_PARTY_SEARCH.score_list;
1362 H_PARTY_ID HZ_PARTY_SEARCH.IDList;
1363 H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
1364
1365 l_score NUMBER;
1366 l_idx NUMBER;
1367 l_party_cur HZ_PARTY_STAGE.StageCurTyp;
1368 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
1369 l_contact_cur HZ_PARTY_STAGE.StageCurTyp;
1370 l_contact_pt_cur HZ_PARTY_STAGE.StageCurTyp;
1371 l_party_id NUMBER;
1372 l_ps_party_id NUMBER;
1373 l_ct_party_id NUMBER;
1374 l_cpt_party_id NUMBER;
1375 l_cpt_ps_id NUMBER;
1376 l_cpt_contact_id NUMBER;
1377 l_party_site_id NUMBER;
1378 l_org_contact_id NUMBER;
1379 l_contact_pt_id NUMBER;
1380 l_ps_contact_id NUMBER;
1381 l_party_max_score NUMBER;
1382 l_ps_max_score NUMBER;
1383 l_contact_max_score NUMBER;
1384 l_cpt_max_score NUMBER;
1385 l_denorm_max_score NUMBER;
1386 l_non_denorm_max_score NUMBER;
1387
1388 defpt NUMBER :=0;
1389 defps NUMBER :=0;
1390 defct NUMBER :=0;
1391 defcpt NUMBER :=0;
1392 l_index NUMBER;
1393 l_max_thresh NUMBER;
1394 l_tmp NUMBER;
1395 l_merge_flag VARCHAR2(1);
1396 l_num_eval NUMBER:=0;
1397
1398 --Fix for bug 4417124
1399 l_use_contact_addr_info BOOLEAN := TRUE;
1400 l_use_contact_cpt_info BOOLEAN := TRUE;
1401 l_use_contact_addr_flag VARCHAR2(1) := 'Y';
1402 l_use_contact_cpt_flag VARCHAR2(1) := 'Y';
1403
1404 h_ps_id HZ_PARTY_SEARCH.IDList;
1405 h_ps_party_id HZ_PARTY_SEARCH.IDList;
1406 h_ps_score HZ_PARTY_SEARCH.IDList;
1407 h_ct_id HZ_PARTY_SEARCH.IDList;
1408 h_ct_party_id HZ_PARTY_SEARCH.IDList;
1409 h_ct_score HZ_PARTY_SEARCH.IDList;
1410 h_cpt_id HZ_PARTY_SEARCH.IDList;
1411 h_cpt_party_id HZ_PARTY_SEARCH.IDList;
1412 h_cpt_score HZ_PARTY_SEARCH.IDList;
1413 detcnt NUMBER := 1;
1414
1415
1416 /********************* Find Parties private procedures *******/
1417 PROCEDURE push_eval IS
1418 BEGIN
1419 H_PARTY_ID.DELETE;
1420 H_PARTY_ID_LIST.DELETE;
1421 H_SCORES.DELETE;
1422 g_mappings.DELETE;
1423 HZ_DQM_SEARCH_UTIL.set_num_eval(0);
1424 call_order(5) := call_order(1);
1425 call_type(5) := 'AND';
1426 call_max_score(5) := call_max_score(1);
1427 call_type(2) := 'OR';
1428 END;
1429
1430 /** Private procedure to acquire and score at party level ***/
1431 PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
1432 l_party_id_idx NUMBER:=1;
1433 l_ctx_id NUMBER;
1434 BEGIN
1435 SAVEPOINT eval_start;
1436 IF l_match_str = ' AND ' AND p_call_type = 'AND' THEN
1437 l_ctx_id := l_search_ctx_id;
1438 FORALL I in 1..H_PARTY_ID.COUNT
1439 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1440 l_search_ctx_id,H_PARTY_ID(I));
1441 H_PARTY_ID.DELETE;
1442 H_PARTY_ID_LIST.DELETE;
1443 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
1444 l_ctx_id := l_search_ctx_id;
1445 FORALL I in 1..H_PARTY_ID.COUNT
1446 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1447 l_search_ctx_id,H_PARTY_ID(I));
1448 l_party_id_idx := H_PARTY_ID.COUNT+1;
1449 ELSE
1450 l_ctx_id := NULL;
1451 l_party_id_idx := H_PARTY_ID.COUNT+1;
1452 END IF;
1453 open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
1454 LOOP
1455 FETCH l_party_cur INTO
1456 l_party_id , l_TX2, l_TX59, l_TX45;
1457 EXIT WHEN l_party_cur%NOTFOUND;
1458 l_index := map_id(l_party_id);
1459 l_score := GET_PARTY_SCORE(l_TX2,l_TX59,l_TX45);
1460 IF NOT H_SCORES.EXISTS(l_index) THEN
1461 H_SCORES(l_index) := get_new_score_rec(l_score,l_score,defps,defct,defcpt, l_party_id, null, null,null);
1462 ELSE
1463 H_SCORES(l_index).TOTAL_SCORE :=
1464 H_SCORES(l_index).TOTAL_SCORE+l_score;
1465 H_SCORES(l_index).PARTY_SCORE := l_score;
1466 END IF;
1467 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) THEN
1468 H_PARTY_ID_LIST(l_index) := 1;
1469 H_PARTY_ID(l_party_id_idx) := l_party_id;
1470 l_party_id_idx:= l_party_id_idx+1;
1471 END IF;
1472 IF l_party_id_idx>l_max_thresh THEN
1473 CLOSE l_party_cur;
1474 IF p_index>1 THEN
1475 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1476 hz_utility_v2pub.debug(p_message=>'In eval party level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1477 END IF;
1478 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
1479 FND_MSG_PUB.ADD;
1480 RAISE FND_API.G_EXC_ERROR;
1481 ELSE
1482 push_eval;
1483 RETURN;
1484 END IF;
1485 END IF;
1486 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1487 hz_utility_v2pub.debug(p_message=>'Party Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1488 hz_utility_v2pub.debug(p_message=>'l_party_id '||l_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1489 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1490 END IF;
1491 END LOOP;
1492 CLOSE l_party_cur;
1493 ROLLBACK to eval_start;
1494 END;
1495
1496 /** Private procedure to acquire and score at party site level ***/
1497 PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
1498 l_party_id_idx NUMBER:=1;
1499 l_ctx_id NUMBER;
1500 BEGIN
1501 SAVEPOINT eval_start;
1502 IF l_match_str = ' AND ' AND p_call_type = 'AND' THEN
1503 l_ctx_id := l_search_ctx_id;
1504 FORALL I in 1..H_PARTY_ID.COUNT
1505 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1506 l_search_ctx_id,H_PARTY_ID(I));
1507 H_PARTY_ID.DELETE;
1508 H_PARTY_ID_LIST.DELETE;
1509 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
1510 l_ctx_id := l_search_ctx_id;
1511 FORALL I in 1..H_PARTY_ID.COUNT
1512 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1513 l_search_ctx_id,H_PARTY_ID(I));
1514 l_party_id_idx := H_PARTY_ID.COUNT+1;
1515 ELSE
1516 l_party_id_idx := H_PARTY_ID.COUNT+1;
1517 l_ctx_id := NULL;
1518 END IF;
1519 open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, l_party_site_cur);
1520 LOOP
1521 FETCH l_party_site_cur INTO
1522 l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX26, l_TX9, l_TX14, l_TX22;
1523 EXIT WHEN l_party_site_cur%NOTFOUND;
1524 IF l_use_contact_addr_info OR l_ps_contact_id IS NOT NULL THEN
1525 l_index := map_id(l_ps_party_id);
1526 l_score := GET_PARTY_SITES_SCORE(l_match_idx,l_TX26,l_TX9,l_TX14,l_TX22);
1527 IF NOT H_SCORES.EXISTS(l_index) THEN
1528 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,l_score,defct,defcpt, l_ps_party_id, l_party_site_id, null,null);
1529 ELSE
1530 IF l_score > H_SCORES(l_index).PARTY_SITE_SCORE THEN
1531 H_SCORES(l_index).TOTAL_SCORE :=
1532 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).PARTY_SITE_SCORE+l_score;
1533 H_SCORES(l_index).PARTY_SITE_SCORE := l_score;
1534 END IF;
1535 END IF;
1536 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) THEN
1537 H_PARTY_ID_LIST(l_index) := 1;
1538 H_PARTY_ID(l_party_id_idx) := l_ps_party_id;
1539 l_party_id_idx:= l_party_id_idx+1;
1540 END IF;
1541 IF l_party_id_idx>l_max_thresh THEN
1542 CLOSE l_party_site_cur;
1543 IF p_index>1 THEN
1544 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1545 hz_utility_v2pub.debug(p_message=>'In eval party site level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1546 END IF;
1547 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
1548 FND_MSG_PUB.ADD;
1549 RAISE FND_API.G_EXC_ERROR;
1550 ELSE
1551 push_eval;
1552 RETURN;
1553 END IF;
1554 END IF;
1555 IF p_ins_details = 'Y' THEN
1556 h_ps_id(detcnt) := l_party_site_id;
1557 h_ps_party_id(detcnt) := l_ps_party_id;
1558 h_ps_score(detcnt) := round((l_score/p_emax_score)*100);
1559 detcnt := detcnt +1;
1560 END IF;
1561 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1562 hz_utility_v2pub.debug(p_message=>'Party Site Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1563 hz_utility_v2pub.debug(p_message=>'l_party_site_id '||l_party_site_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1564 hz_utility_v2pub.debug(p_message=>'l_ps_party_id '||l_ps_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1565 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1566 END IF;
1567 END IF;
1568 END LOOP;
1569 CLOSE l_party_site_cur;
1570 ROLLBACK to eval_start;
1571 END;
1572
1573 /** Private procedure to acquire and score at party site level ***/
1574 PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
1575 l_party_id_idx NUMBER:=1;
1576 l_ctx_id NUMBER;
1577 BEGIN
1578 SAVEPOINT eval_start;
1579 IF l_match_str = ' AND ' AND p_call_type='AND' THEN
1580 l_ctx_id := l_search_ctx_id;
1581 FORALL I in 1..H_PARTY_ID.COUNT
1582 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1583 l_search_ctx_id,H_PARTY_ID(I));
1584 H_PARTY_ID.DELETE;
1585 H_PARTY_ID_LIST.DELETE;
1586 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
1587 l_ctx_id := l_search_ctx_id;
1588 FORALL I in 1..H_PARTY_ID.COUNT
1589 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1590 l_search_ctx_id,H_PARTY_ID(I));
1591 l_party_id_idx := H_PARTY_ID.COUNT+1;
1592 ELSE
1593 l_ctx_id := NULL;
1594 l_party_id_idx := H_PARTY_ID.COUNT+1;
1595 END IF;
1596 open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, l_contact_cur);
1597 LOOP
1598 FETCH l_contact_cur INTO
1599 l_org_contact_id, l_ct_party_id ;
1600 EXIT WHEN l_contact_cur%NOTFOUND;
1601 l_index := map_id(l_ct_party_id);
1602 l_score := GET_CONTACTS_SCORE(l_match_idx);
1603 IF NOT H_SCORES.EXISTS(l_index) THEN
1604 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,l_score,defcpt, l_ct_party_id, null, l_org_contact_id,null);
1605 ELSE
1606 IF l_score > H_SCORES(l_index).CONTACT_SCORE THEN
1607 H_SCORES(l_index).TOTAL_SCORE :=
1608 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_SCORE+l_score;
1609 H_SCORES(l_index).CONTACT_SCORE := l_score;
1610 END IF;
1611 END IF;
1612 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) THEN
1613 H_PARTY_ID_LIST(l_index) := 1;
1614 H_PARTY_ID(l_party_id_idx) := l_ct_party_id;
1615 l_party_id_idx:= l_party_id_idx+1;
1616 END IF;
1617 IF l_party_id_idx>l_max_thresh THEN
1618 CLOSE l_contact_cur;
1619 IF p_index>1 THEN
1620 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1621 hz_utility_v2pub.debug(p_message=>'In eval contact level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1622 END IF;
1623 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
1624 FND_MSG_PUB.ADD;
1625 RAISE FND_API.G_EXC_ERROR;
1626 ELSE
1627 push_eval;
1628 RETURN;
1629 END IF;
1630 END IF;
1631 IF p_ins_details = 'Y' THEN
1632 h_ct_id(detcnt) := l_org_contact_id;
1633 h_ct_party_id(detcnt) := l_ct_party_id;
1634 h_ct_score(detcnt) := round((l_score/p_emax_score)*100);
1635 detcnt := detcnt +1;
1636 END IF;
1637 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1638 hz_utility_v2pub.debug(p_message=>'Contact Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1639 hz_utility_v2pub.debug(p_message=>'l_org_contact_id '||l_org_contact_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1640 hz_utility_v2pub.debug(p_message=>'l_ct_party_id '||l_ct_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1641 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1642 END IF;
1643 END LOOP;
1644 CLOSE l_contact_cur;
1645 ROLLBACK to eval_start;
1646 END;
1647
1648 /** Private procedure to acquire and score at contact point level ***/
1649 PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
1650 l_party_id_idx NUMBER:=1;
1651 l_ctx_id NUMBER;
1652 BEGIN
1653 SAVEPOINT eval_start;
1654 IF l_match_str = ' AND ' AND p_call_type = 'AND' THEN
1655 l_ctx_id := l_search_ctx_id;
1656 FORALL I in 1..H_PARTY_ID.COUNT
1657 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1658 l_search_ctx_id,H_PARTY_ID(I));
1659 H_PARTY_ID.DELETE;
1660 H_PARTY_ID_LIST.DELETE;
1661 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
1662 l_ctx_id := l_search_ctx_id;
1663 FORALL I in 1..H_PARTY_ID.COUNT
1664 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
1665 l_search_ctx_id,H_PARTY_ID(I));
1666 l_party_id_idx := H_PARTY_ID.COUNT+1;
1667 ELSE
1668 l_ctx_id := NULL;
1669 l_party_id_idx := H_PARTY_ID.COUNT+1;
1670 END IF;
1671 open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, l_contact_pt_cur);
1672 LOOP
1673 FETCH l_contact_pt_cur INTO
1674 l_contact_pt_id, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX5, l_TX158, l_TX10;
1675 EXIT WHEN l_contact_pt_cur%NOTFOUND;
1676 IF l_use_contact_cpt_info OR l_ps_contact_id IS NOT NULL THEN
1677 l_index := map_id(l_cpt_party_id);
1678 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
1679 IF NOT H_SCORES.EXISTS(l_index) THEN
1680 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,defct,l_score, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id,l_contact_pt_id);
1681 ELSE
1682 IF l_score > H_SCORES(l_index).CONTACT_POINT_SCORE THEN
1683 H_SCORES(l_index).TOTAL_SCORE :=
1684 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_POINT_SCORE+l_score;
1685 H_SCORES(l_index).CONTACT_POINT_SCORE := l_score;
1686 END IF;
1687 END IF;
1688 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) THEN
1689 H_PARTY_ID_LIST(l_index) := 1;
1690 H_PARTY_ID(l_party_id_idx) := l_cpt_party_id;
1691 l_party_id_idx:= l_party_id_idx+1;
1692 END IF;
1693 IF l_party_id_idx>l_max_thresh THEN
1694 CLOSE l_contact_pt_cur;
1695 IF p_index>1 THEN
1696 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1697 hz_utility_v2pub.debug(p_message=>'In eval contact point level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1698 END IF;
1699 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
1700 FND_MSG_PUB.ADD;
1701 RAISE FND_API.G_EXC_ERROR;
1702 ELSE
1703 push_eval;
1704 RETURN;
1705 END IF;
1706 END IF;
1707 IF p_ins_details = 'Y' THEN
1708 h_cpt_id(detcnt) := l_contact_pt_id;
1709 h_cpt_party_id(detcnt) := l_cpt_party_id;
1710 h_cpt_score(detcnt) := round((l_score/p_emax_score)*100);
1711 detcnt := detcnt +1;
1712 END IF;
1713 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1714 hz_utility_v2pub.debug(p_message=>'Contact pt Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1715 hz_utility_v2pub.debug(p_message=>'l_contact_pt_id '||l_contact_pt_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1716 hz_utility_v2pub.debug(p_message=>'l_cpt_party_id '||l_cpt_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1717 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1718 END IF;
1719 END IF;
1720 END LOOP;
1721 CLOSE l_contact_pt_cur;
1722 ROLLBACK to eval_start;
1723 END;
1724
1725 /** Private procedure to call the eval procedure at each entity in the correct order ***/
1726 PROCEDURE do_eval (p_index NUMBER) IS
1727 l_ctx_id NUMBER;
1728 l_threshold NUMBER;
1729 other_criteria_exists BOOLEAN;
1730 BEGIN
1731 IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
1732 l_threshold := 175;
1733 other_criteria_exists := TRUE ;
1734 IF (call_max_score(2) = 0 and call_max_score(3) = 0 and call_max_score(4) = 0 ) THEN
1735 other_criteria_exists := FALSE;
1736 END IF ;
1737 IF( (l_match_str = ' AND ' AND other_criteria_exists) OR ( call_max_score(p_index) < l_threshold) )THEN
1738 RETURN;
1739 ELSE
1740 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1741 hz_utility_v2pub.debug(p_message=>'In do eval number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1742 END IF;
1743 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
1744 FND_MSG_PUB.ADD;
1745 RAISE FND_API.G_EXC_ERROR;
1746 END IF;
1747 END IF;
1748 IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
1749 eval_party_level(l_party_contains_str,call_type(p_index), p_index);
1750 ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
1751 eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
1752 ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
1753 eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
1754 ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
1755 eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
1756 END IF;
1757 END;
1758 /************ End of find_parties private procedures **********/
1759
1760 BEGIN
1761
1762 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1763 hz_utility_v2pub.debug(p_message=>'find_parties(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1764 END IF;
1765 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1766 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1767 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1771 hz_utility_v2pub.debug(p_message=>'p_dup_party_id '||p_dup_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1768 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1769 hz_utility_v2pub.debug(p_message=>'p_dup_set_id '||p_dup_set_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1770 hz_utility_v2pub.debug(p_message=>'p_search_merged '||p_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1772 END IF;
1773 -- ************************************
1774 -- STEP 1. Initialization and error checks
1775
1776 l_match_str := ' OR ';
1777 IF p_match_type = 'AND' THEN
1778 l_match_str := ' AND ';
1779 ELSIF p_match_type = 'OR' THEN
1780 l_match_str := ' OR ';
1781 END IF;
1782 SAVEPOINT find_parties;
1783 l_entered_max_score:= init_search(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list, l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
1784 IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
1785
1786
1787 --Fix for bug 4417124
1788
1789 SELECT use_contact_addr_flag, use_contact_cpt_flag
1790 INTO l_use_contact_addr_flag, l_use_contact_cpt_flag
1791 FROM hz_match_rules_b
1792 WHERE match_rule_id = 51;
1793
1794 IF p_dup_batch_id IS NOT NULL AND NVL(l_use_contact_addr_flag, 'Y') = 'N' THEN
1795 l_use_contact_addr_info := FALSE;
1796 END IF;
1797
1798 IF p_dup_batch_id IS NOT NULL AND NVL(l_use_contact_cpt_flag, 'Y') = 'N' THEN
1799 l_use_contact_cpt_info := FALSE;
1800 END IF;
1801
1802 --End fix for bug 4417124
1803
1804 l_max_thresh:=nvl(FND_PROFILE.VALUE('HZ_DQM_MAX_EVAL_THRESH'),200);
1805 IF nvl(FND_PROFILE.VALUE('HZ_DQM_SCORE_UNTIL_THRESH'),'N')='Y' THEN
1806 g_score_until_thresh := true;
1807 ELSE
1808 g_score_until_thresh := false;
1809 END IF;
1810 l_party_site_contains_str := check_party_sites_bulk (p_party_site_list);
1811 l_contact_contains_str := check_contacts_bulk (p_contact_list);
1812 l_contact_pt_contains_str := check_cpts_bulk (p_contact_point_list);
1813 l_party_contains_str := check_parties_bulk (p_party_search_rec) ;
1814 init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
1815
1816 -- Setup Search Context ID
1817 SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
1818 x_search_ctx_id := l_search_ctx_id;
1819
1820 IF l_party_contains_str IS NULL THEN
1821 defpt := 1;
1822 END IF;
1823 IF l_party_site_contains_str IS NULL THEN
1824 defps := 1;
1825 END IF;
1826 IF l_contact_contains_str IS NULL THEN
1827 defct := 1;
1828 END IF;
1829 IF l_contact_pt_contains_str IS NULL THEN
1830 defcpt := 1;
1831 END IF;
1832
1833 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1834 hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1835 hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1836 hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1837 hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1838 hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1839 hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1840 END IF;
1841 FOR I in 1..5 LOOP
1842 do_eval(I);
1843 END LOOP;
1844 ROLLBACK to find_parties;
1845 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1846 hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 175 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1847 END IF;
1848 x_num_matches := 0;
1849 l_num_eval := 0;
1850 IF l_match_str = ' OR ' THEN
1851 l_party_id := H_SCORES.FIRST;
1852 ELSE
1853 l_party_id := H_PARTY_ID_LIST.FIRST;
1854 END IF;
1855 WHILE l_party_id IS NOT NULL LOOP
1856 l_num_eval:= l_num_eval+1;
1857 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1858 hz_utility_v2pub.debug(p_message=>'Match Party ID '||H_SCORES(l_party_id).PARTY_ID,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1859 hz_utility_v2pub.debug(p_message=>'Score '||H_SCORES(l_party_id).TOTAL_SCORE,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1860 END IF;
1861 IF H_SCORES(l_party_id).TOTAL_SCORE>=175 THEN
1862 IF p_dup_set_id IS NULL THEN
1863 INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
1864 VALUES (l_search_ctx_id,H_SCORES(l_party_id).PARTY_ID,H_SCORES(l_party_id).TOTAL_SCORE);
1865 x_num_matches := x_num_matches+1;
1866 ELSE
1867 BEGIN
1868 SELECT 1 INTO l_tmp FROM HZ_DUP_SET_PARTIES
1869 WHERE DUP_PARTY_ID = H_SCORES(l_party_id).PARTY_ID
1870 AND DUP_SET_BATCH_ID = p_dup_batch_id
1871 AND ROWNUM=1;
1875 l_merge_flag := 'Y';
1872 EXCEPTION
1873 WHEN NO_DATA_FOUND THEN
1874 IF H_SCORES(l_party_id).TOTAL_SCORE>=250 THEN
1876 ELSE
1877 l_merge_flag := 'N';
1878 END IF;
1879 INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,
1880 MERGE_BATCH_ID,SCORE,MERGE_FLAG, CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,
1881 LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID)
1882 VALUES (H_SCORES(l_party_id).PARTY_ID,p_dup_set_id,0,0,
1883 H_SCORES(l_party_id).TOTAL_SCORE, l_merge_flag,
1884 hz_utility_pub.created_by,hz_utility_pub.creation_date,
1885 hz_utility_pub.last_update_login,
1886 hz_utility_pub.last_update_date,
1887 hz_utility_pub.user_id,p_dup_batch_id);
1888 x_num_matches := x_num_matches+1;
1889 END;
1890 END IF;
1891 END IF;
1892 IF l_match_str = ' OR ' THEN
1893 l_party_id:=H_SCORES.NEXT(l_party_id);
1894 ELSE
1895 l_party_id:=H_PARTY_ID_LIST.NEXT(l_party_id);
1896 END IF;
1897 END LOOP;
1898
1899 ----------INSERT INTO HZ_MATCHED_PARTY_SITES -----
1900 IF p_ins_details = 'Y' THEN
1901 FORALL I in 1..h_ps_id.COUNT
1902 INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (
1903 l_search_ctx_id, h_ps_id(I), h_ps_party_id(I), h_ps_score(I));
1904 END IF;
1905 ----------INSERT INTO HZ_MATCHED_CONTACTS-----
1906 IF p_ins_details = 'Y' THEN
1907 FORALL I in 1..h_ct_id.COUNT
1908 INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (
1909 l_search_ctx_id, h_ct_id(I), h_ct_party_id(I), h_ct_score(I));
1910 END IF;
1911 ----------INSERT INTO HZ_MATCHED_CPTS-----
1912 IF p_ins_details = 'Y' THEN
1913 FORALL I in 1..h_cpt_id.COUNT
1914 INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
1915 l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));
1916 END IF;
1917
1918 HZ_DQM_SEARCH_UTIL.set_num_eval(l_num_eval);
1919 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1920 hz_utility_v2pub.debug(p_message=>'find_parties(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1921 END IF;
1922 EXCEPTION
1923 WHEN FND_API.G_EXC_ERROR THEN
1924 ROLLBACK to find_parties;
1925 RAISE FND_API.G_EXC_ERROR;
1926 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1927 ROLLBACK to find_parties;
1928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1929 WHEN OTHERS THEN
1930 ROLLBACK to find_parties;
1931 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1932 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_parties');
1933 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1934 FND_MSG_PUB.ADD;
1935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1936 END find_parties;
1937
1938 -------------------------------------------------------------------------------------
1939 -------------------- BULK MATCH RULE ::: find_persons ------------------------------
1940 -------------------------------------------------------------------------------------
1941 PROCEDURE find_persons (
1942 p_rule_id IN NUMBER,
1943 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
1944 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
1945 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
1946 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
1947 p_restrict_sql IN VARCHAR2,
1948 p_match_type IN VARCHAR2,
1949 p_search_merged IN VARCHAR2,
1950 p_ins_details IN VARCHAR2,
1951 x_search_ctx_id OUT NUMBER,
1952 x_num_matches OUT NUMBER
1953 ) IS
1954
1955 -- Strings to hold the generated Intermedia query strings
1956 l_party_contains_str VARCHAR2(32000);
1957 l_party_site_contains_str VARCHAR2(32000);
1958 l_contact_contains_str VARCHAR2(32000);
1959 l_contact_pt_contains_str VARCHAR2(32000);
1960 l_denorm_str VARCHAR2(32000);
1961 l_ps_denorm_str VARCHAR2(32000);
1962 l_ct_denorm_str VARCHAR2(32000);
1963 l_cpt_denorm_str VARCHAR2(32000);
1964
1965 -- Other local variables
1966 l_match_str VARCHAR2(30); -- Match type (AND or OR)
1967 l_sqlstr VARCHAR2(32000); -- Dynamic SQL String
1968 -- For Score calculation
1969 l_max_score NUMBER;
1970 l_match_idx NUMBER;
1971 l_entered_max_score NUMBER;
1972 FIRST BOOLEAN;
1973 l_search_ctx_id NUMBER; -- Generated Search Context ID
1974
1975 l_TX2 VARCHAR2(2000);
1976 l_TX26 VARCHAR2(2000);
1977 l_TX9 VARCHAR2(2000);
1978 l_TX22 VARCHAR2(2000);
1979 l_TX158 VARCHAR2(2000);
1980 l_TX5 VARCHAR2(2000);
1981 l_TX10 VARCHAR2(2000);
1982 l_TX59 VARCHAR2(2000);
1983 l_TX45 VARCHAR2(2000);
1984 l_TX14 VARCHAR2(2000);
1985 H_SCORES HZ_PARTY_SEARCH.score_list;
1986 H_PARTY_ID HZ_PARTY_SEARCH.IDList;
1987 H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
1988
1989 l_score NUMBER;
1990 l_idx NUMBER;
1991 l_party_cur HZ_PARTY_STAGE.StageCurTyp;
1992 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
1993 l_contact_cur HZ_PARTY_STAGE.StageCurTyp;
1994 l_contact_pt_cur HZ_PARTY_STAGE.StageCurTyp;
1995 l_party_id NUMBER;
1996 l_ps_party_id NUMBER;
1997 l_ct_party_id NUMBER;
1998 l_cpt_party_id NUMBER;
1999 l_cpt_ps_id NUMBER;
2000 l_cpt_contact_id NUMBER;
2001 l_party_site_id NUMBER;
2002 l_org_contact_id NUMBER;
2003 l_contact_pt_id NUMBER;
2004 l_ps_contact_id NUMBER;
2008 l_cpt_max_score NUMBER;
2005 l_party_max_score NUMBER;
2006 l_ps_max_score NUMBER;
2007 l_contact_max_score NUMBER;
2009 l_denorm_max_score NUMBER;
2010 l_non_denorm_max_score NUMBER;
2011
2012 defpt NUMBER :=0;
2013 defps NUMBER :=0;
2014 defct NUMBER :=0;
2015 defcpt NUMBER :=0;
2016 l_index NUMBER;
2017 l_max_thresh NUMBER;
2018 l_tmp NUMBER;
2019 l_merge_flag VARCHAR2(1);
2020 l_num_eval NUMBER:=0;
2021
2022 h_ps_id HZ_PARTY_SEARCH.IDList;
2023 h_ps_party_id HZ_PARTY_SEARCH.IDList;
2024 h_ps_score HZ_PARTY_SEARCH.IDList;
2025 h_ct_id HZ_PARTY_SEARCH.IDList;
2026 h_ct_party_id HZ_PARTY_SEARCH.IDList;
2027 h_ct_score HZ_PARTY_SEARCH.IDList;
2028 h_cpt_id HZ_PARTY_SEARCH.IDList;
2029 h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2030 h_cpt_score HZ_PARTY_SEARCH.IDList;
2031 detcnt NUMBER := 1;
2032 l_person_id NUMBER;
2033
2034 /********************* Find Parties private procedures *******/
2035 PROCEDURE set_person_party_type IS
2036 BEGIN
2037 g_party_stage_rec.TX36:=
2038 HZ_TRANS_PKG.EXACT(
2039 'PERSON'
2040 ,null,'PARTY_TYPE'
2041 ,'PARTY');
2042 END;
2043
2044 PROCEDURE unset_person_party_type IS
2045 BEGIN
2046 g_party_stage_rec.TX36 := '';
2047 END;
2048
2049 FUNCTION get_person_id(p_party_id NUMBER, p_contact_id NUMBER)
2050 RETURN NUMBER IS
2051 l_party_type VARCHAR2(255);
2052 l_person_id NUMBER(15);
2053 BEGIN
2054 SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;
2055 IF l_party_type = 'PERSON' THEN
2056 RETURN p_party_id;
2057 ELSIF p_contact_id IS NULL THEN
2058 RETURN NULL;
2059 ELSE
2060 BEGIN
2061 SELECT subject_id INTO l_person_id FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, hz_parties p
2062 WHERE oc.org_contact_id = p_contact_id
2063 AND r.relationship_id = oc.party_relationship_id
2064 AND r.object_id = p_party_id
2065 AND p.party_id = r.subject_id
2066 AND p.party_type = 'PERSON'
2067 AND ROWNUM=1;
2068
2069 RETURN l_person_id;
2070 EXCEPTION
2071 WHEN NO_DATA_FOUND THEN
2072 RETURN NULL;
2073 END;
2074 END IF;
2075 END;
2076
2077 PROCEDURE push_eval IS
2078 BEGIN
2079 H_PARTY_ID.DELETE;
2080 H_PARTY_ID_LIST.DELETE;
2081 H_SCORES.DELETE;
2082 g_mappings.DELETE;
2083 HZ_DQM_SEARCH_UTIL.set_num_eval(0);
2084 call_order(5) := call_order(1);
2085 call_type(5) := 'AND';
2086 call_max_score(5) := call_max_score(1);
2087 call_type(2) := 'OR';
2088 END;
2089
2090 /** Private procedure to acquire and score at party level ***/
2091 PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2092 l_party_id_idx NUMBER:=1;
2093 l_ctx_id NUMBER;
2094 BEGIN
2095 SAVEPOINT eval_start;
2096 set_person_party_type;
2097 IF l_match_str = ' AND ' AND p_call_type = 'AND' THEN
2098 l_ctx_id := l_search_ctx_id;
2099 FORALL I in 1..H_PARTY_ID.COUNT
2100 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2101 l_search_ctx_id,H_PARTY_ID(I));
2102 H_PARTY_ID.DELETE;
2103 H_PARTY_ID_LIST.DELETE;
2104 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
2105 l_ctx_id := l_search_ctx_id;
2106 FORALL I in 1..H_PARTY_ID.COUNT
2107 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2108 l_search_ctx_id,H_PARTY_ID(I));
2109 l_party_id_idx := H_PARTY_ID.COUNT+1;
2110 ELSE
2111 l_ctx_id := NULL;
2112 l_party_id_idx := H_PARTY_ID.COUNT+1;
2113 END IF;
2114 open_party_cursor(null, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,null,l_party_cur);
2115 LOOP
2116 FETCH l_party_cur INTO
2117 l_party_id , l_TX2, l_TX59, l_TX45;
2118 EXIT WHEN l_party_cur%NOTFOUND;
2119 l_index := map_id(l_party_id);
2120 l_score := GET_PARTY_SCORE(l_TX2,l_TX59,l_TX45);
2121 IF NOT H_SCORES.EXISTS(l_index) THEN
2122 H_SCORES(l_index) := get_new_score_rec(l_score,l_score,defps,defct,defcpt, l_party_id, null, null,null);
2123 ELSE
2124 H_SCORES(l_index).TOTAL_SCORE :=
2125 H_SCORES(l_index).TOTAL_SCORE+l_score;
2126 H_SCORES(l_index).PARTY_SCORE := l_score;
2127 END IF;
2128 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) AND H_SCORES.EXISTS(l_index) THEN
2129 H_PARTY_ID_LIST(l_index) := 1;
2130 H_PARTY_ID(l_party_id_idx) := l_party_id;
2131 l_party_id_idx:= l_party_id_idx+1;
2132 END IF;
2133 IF l_party_id_idx>l_max_thresh THEN
2134 CLOSE l_party_cur;
2135 IF p_index>1 THEN
2136 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2137 hz_utility_v2pub.debug(p_message=>'In eval party level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2138 END IF;
2139 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
2140 FND_MSG_PUB.ADD;
2141 RAISE FND_API.G_EXC_ERROR;
2142 ELSE
2143 push_eval;
2144 RETURN;
2145 END IF;
2146 END IF;
2147 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2148 hz_utility_v2pub.debug(p_message=>'Party Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2152 END LOOP;
2149 hz_utility_v2pub.debug(p_message=>'l_party_id '||l_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2150 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2151 END IF;
2153 CLOSE l_party_cur;
2154 ROLLBACK to eval_start;
2155 END;
2156 PROCEDURE open_person_contact_cursor(
2157 p_contains_str VARCHAR2,
2158 p_search_ctx_id NUMBER,
2159 x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
2160 BEGIN
2161 OPEN x_cursor FOR
2162 SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
2163 FROM HZ_STAGED_CONTACTS stage
2164 WHERE contains( concat_col, p_contains_str)>0
2165 AND ORG_CONTACT_ID in (
2166 SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
2167 org_contact_id
2168 from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
2169 where d.party_id = r.subject_id
2170 and oc.party_relationship_id = r.relationship_id
2171 and d.search_context_id = p_search_ctx_id);
2172 exception
2173 when others then
2174 if (instrb(SQLERRM,'DRG-51030')>0) then
2175 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_WILDCARD_ERR');
2176 FND_MSG_PUB.ADD;
2177 RAISE FND_API.G_EXC_ERROR;
2178 elsif (instrb(SQLERRM,'DRG-50943')>0) then
2179 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_SEARCH_ERROR');
2180 FND_MSG_PUB.ADD;
2181 RAISE FND_API.G_EXC_ERROR;
2182 else
2183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2184 end if;
2185 END;
2186
2187 /** Private procedure to acquire and score at party site level ***/
2188 PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2189 l_party_id_idx NUMBER:=1;
2190 l_ctx_id NUMBER;
2191 l_person_id NUMBER;
2192 BEGIN
2193 SAVEPOINT eval_start;
2194 unset_person_party_type;
2195 IF l_match_str = ' AND ' AND p_call_type = 'AND' THEN
2196 l_ctx_id := l_search_ctx_id;
2197 FORALL I in 1..H_PARTY_ID.COUNT
2198 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2199 l_search_ctx_id,H_PARTY_ID(I));
2200 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
2201 SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc,
2202 HZ_RELATIONSHIPS r
2203 WHERE oc.party_relationship_id = r.relationship_id
2204 AND r.object_id = d.party_id
2205 AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;
2206 H_PARTY_ID.DELETE;
2207 H_PARTY_ID_LIST.DELETE;
2208 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
2209 l_ctx_id := l_search_ctx_id;
2210 FORALL I in 1..H_PARTY_ID.COUNT
2211 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2212 l_search_ctx_id,H_PARTY_ID(I));
2213 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
2214 SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc,
2215 HZ_RELATIONSHIPS r
2216 WHERE oc.party_relationship_id = r.relationship_id
2217 AND r.object_id = d.party_id
2218 AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;
2219 l_party_id_idx := H_PARTY_ID.COUNT+1;
2220 ELSE
2221 l_party_id_idx := H_PARTY_ID.COUNT+1;
2222 l_ctx_id := NULL;
2223 END IF;
2224 open_party_site_cursor(null,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, l_party_site_cur);
2225 LOOP
2226 FETCH l_party_site_cur INTO
2227 l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX26, l_TX9, l_TX14, l_TX22;
2228 EXIT WHEN l_party_site_cur%NOTFOUND;
2229 l_person_id := get_person_id(l_ps_party_id, l_ps_contact_id);
2230 IF l_person_id IS NOT NULL THEN
2231 l_index := map_id(l_person_id);
2232 l_score := GET_PARTY_SITES_SCORE(l_match_idx,l_TX26,l_TX9,l_TX14,l_TX22);
2233 IF NOT H_SCORES.EXISTS(l_index) THEN
2234 IF l_ctx_id IS NULL THEN
2235 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,l_score,defct,defcpt, l_person_id, l_party_site_id, null,null);
2236 END IF;
2237 ELSE
2238 IF l_score > H_SCORES(l_index).PARTY_SITE_SCORE THEN
2239 H_SCORES(l_index).TOTAL_SCORE :=
2240 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).PARTY_SITE_SCORE+l_score;
2241 H_SCORES(l_index).PARTY_SITE_SCORE := l_score;
2242 END IF;
2243 END IF;
2244 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) AND H_SCORES.EXISTS(l_index) THEN
2245 H_PARTY_ID_LIST(l_index) := 1;
2246 H_PARTY_ID(l_party_id_idx) := l_ps_party_id;
2247 l_party_id_idx:= l_party_id_idx+1;
2248 END IF;
2249 IF l_party_id_idx>l_max_thresh THEN
2250 CLOSE l_party_site_cur;
2251 IF p_index>1 THEN
2252 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2253 hz_utility_v2pub.debug(p_message=>'In eval party site level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2254 END IF;
2255 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
2256 FND_MSG_PUB.ADD;
2257 RAISE FND_API.G_EXC_ERROR;
2258 ELSE
2259 push_eval;
2260 RETURN;
2261 END IF;
2262 END IF;
2263 IF p_ins_details = 'Y' THEN
2264 h_ps_id(detcnt) := l_party_site_id;
2265 h_ps_party_id(detcnt) := l_person_id;
2266 h_ps_score(detcnt) := round((l_score/p_emax_score)*100);
2270 hz_utility_v2pub.debug(p_message=>'Party Site Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2267 detcnt := detcnt +1;
2268 END IF;
2269 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2271 hz_utility_v2pub.debug(p_message=>'l_party_site_id '||l_party_site_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2272 hz_utility_v2pub.debug(p_message=>'l_ps_party_id '||l_person_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2273 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2274 END IF;
2275 END IF;
2276 END LOOP;
2277 CLOSE l_party_site_cur;
2278 ROLLBACK to eval_start;
2279 END;
2280
2281 /** Private procedure to acquire and score at party site level ***/
2282 PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2283 l_party_id_idx NUMBER:=1;
2284 l_ctx_id NUMBER;
2285 BEGIN
2286 SAVEPOINT eval_start;
2287 l_ctx_id := l_search_ctx_id;
2288 unset_person_party_type;
2289 FORALL I in 1..H_PARTY_ID.COUNT
2290 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2291 l_search_ctx_id,H_PARTY_ID(I));
2292 l_party_id_idx := H_PARTY_ID.COUNT+1;
2293 open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
2294 LOOP
2295 FETCH l_contact_cur INTO
2296 l_org_contact_id, l_ct_party_id ;
2297 EXIT WHEN l_contact_cur%NOTFOUND;
2298 l_person_id := get_person_id(l_ct_party_id, l_org_contact_id);
2299 l_index := map_id(l_person_id);
2300 IF l_person_id IS NOT NULL AND H_SCORES.EXISTS(l_index) THEN
2301 l_score := GET_CONTACTS_SCORE(l_match_idx);
2302 IF l_score > H_SCORES(l_index).CONTACT_SCORE THEN
2303 H_SCORES(l_index).TOTAL_SCORE :=
2304 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_SCORE+l_score;
2305 H_SCORES(l_index).CONTACT_SCORE := l_score;
2306 END IF;
2307 END IF;
2308 IF p_ins_details = 'Y' THEN
2309 h_ct_id(detcnt) := l_org_contact_id;
2310 h_ct_party_id(detcnt) := l_person_id;
2311 h_ct_score(detcnt) := round((l_score/p_emax_score)*100);
2312 detcnt := detcnt +1;
2313 END IF;
2314 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2315 hz_utility_v2pub.debug(p_message=>'Contact Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2316 hz_utility_v2pub.debug(p_message=>'l_org_contact_id '||l_org_contact_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2317 hz_utility_v2pub.debug(p_message=>'l_ct_party_id '||l_person_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2318 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2319 END IF;
2320 END LOOP;
2321 CLOSE l_contact_cur;
2322 ROLLBACK to eval_start;
2323 END;
2324
2325 /** Private procedure to acquire and score at contact point level ***/
2326 PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2327 l_party_id_idx NUMBER:=1;
2328 l_ctx_id NUMBER;
2329 BEGIN
2330 SAVEPOINT eval_start;
2331 unset_person_party_type;
2332 IF l_match_str = ' AND ' AND p_call_type = 'AND' THEN
2333 l_ctx_id := l_search_ctx_id;
2334 FORALL I in 1..H_PARTY_ID.COUNT
2335 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2336 l_search_ctx_id,H_PARTY_ID(I));
2337 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
2338 SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc,
2339 HZ_RELATIONSHIPS r
2340 WHERE oc.party_relationship_id = r.relationship_id
2341 AND r.object_id = d.party_id
2342 AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;
2343 H_PARTY_ID.DELETE;
2344 H_PARTY_ID_LIST.DELETE;
2345 ELSIF l_match_str = ' OR ' AND p_call_type = 'AND' THEN
2346 l_ctx_id := l_search_ctx_id;
2347 FORALL I in 1..H_PARTY_ID.COUNT
2348 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
2349 l_search_ctx_id,H_PARTY_ID(I));
2350 INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)
2351 SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc,
2352 HZ_RELATIONSHIPS r
2353 WHERE oc.party_relationship_id = r.relationship_id
2354 AND r.object_id = d.party_id
2355 AND d.SEARCH_CONTEXT_ID=l_search_ctx_id;
2356 l_party_id_idx := H_PARTY_ID.COUNT+1;
2357 ELSE
2358 l_ctx_id := NULL;
2359 l_party_id_idx := H_PARTY_ID.COUNT+1;
2360 END IF;
2361 open_contact_pt_cursor(null,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, l_contact_pt_cur);
2362 LOOP
2363 FETCH l_contact_pt_cur INTO
2364 l_contact_pt_id, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX5, l_TX158, l_TX10;
2365 EXIT WHEN l_contact_pt_cur%NOTFOUND;
2366 l_person_id := get_person_id(l_cpt_party_id, l_cpt_contact_id);
2367 IF l_person_id IS NOT NULL THEN
2368 l_index := map_id(l_person_id);
2369 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
2370 IF NOT H_SCORES.EXISTS(l_index) THEN
2371 IF l_ctx_id IS NULL THEN
2375 IF l_score > H_SCORES(l_index).CONTACT_POINT_SCORE THEN
2372 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,defct,l_score, l_person_id, l_cpt_ps_id, l_cpt_contact_id,l_contact_pt_id);
2373 END IF;
2374 ELSE
2376 H_SCORES(l_index).TOTAL_SCORE :=
2377 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_POINT_SCORE+l_score;
2378 H_SCORES(l_index).CONTACT_POINT_SCORE := l_score;
2379 END IF;
2380 END IF;
2381 IF NOT H_PARTY_ID_LIST.EXISTS(l_index) AND H_SCORES.EXISTS(l_index) THEN
2382 H_PARTY_ID_LIST(l_index) := 1;
2383 H_PARTY_ID(l_party_id_idx) := l_person_id;
2384 l_party_id_idx:= l_party_id_idx+1;
2385 END IF;
2386 IF l_party_id_idx>l_max_thresh THEN
2387 CLOSE l_contact_pt_cur;
2388 IF p_index>1 THEN
2389 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2390 hz_utility_v2pub.debug(p_message=>'In eval contact point level number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2391 END IF;
2392 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
2393 FND_MSG_PUB.ADD;
2394 RAISE FND_API.G_EXC_ERROR;
2395 ELSE
2396 push_eval;
2397 RETURN;
2398 END IF;
2399 END IF;
2400 IF p_ins_details = 'Y' THEN
2401 h_cpt_id(detcnt) := l_contact_pt_id;
2402 h_cpt_party_id(detcnt) := l_person_id;
2403 h_cpt_score(detcnt) := round((l_score/p_emax_score)*100);
2404 detcnt := detcnt +1;
2405 END IF;
2406 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2407 hz_utility_v2pub.debug(p_message=>'Contact pt Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2408 hz_utility_v2pub.debug(p_message=>'l_contact_pt_id '||l_contact_pt_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2409 hz_utility_v2pub.debug(p_message=>'l_cpt_party_id '||l_person_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2410 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2411 END IF;
2412 END IF;
2413 END LOOP;
2414 CLOSE l_contact_pt_cur;
2415 ROLLBACK to eval_start;
2416 IF p_ins_details = 'Y' THEN
2417 FORALL I in 1..h_cpt_id.COUNT
2418 INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
2419 l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));
2420 END IF;
2421 END;
2422
2423 /** Private procedure to call the eval procedure at each entity in the correct order ***/
2424 PROCEDURE do_eval (p_index NUMBER) IS
2425 l_ctx_id NUMBER;
2426 l_threshold NUMBER;
2427 other_criteria_exists BOOLEAN;
2428 BEGIN
2429 IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2430 l_threshold := 175;
2431 other_criteria_exists := TRUE ;
2432 IF (call_max_score(2) = 0 and call_max_score(3) = 0 and call_max_score(4) = 0 ) THEN
2433 other_criteria_exists := FALSE;
2434 END IF ;
2435 IF( (l_match_str = ' AND ' AND other_criteria_exists) OR ( call_max_score(p_index) < l_threshold) )THEN
2436 RETURN;
2437 ELSE
2438 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2439 hz_utility_v2pub.debug(p_message=>'In do eval number of matches found exceeded threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2440 END IF;
2441 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_THRESH_EXCEEDED');
2442 FND_MSG_PUB.ADD;
2443 RAISE FND_API.G_EXC_ERROR;
2444 END IF;
2445 END IF;
2446 IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2447 eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2448 ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2449 eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2450 ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2451 eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2452 END IF;
2453 END;
2454 /************ End of find_persons private procedures **********/
2455
2456 BEGIN
2457
2458 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2459 hz_utility_v2pub.debug(p_message=>'find_persons(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2460 END IF;
2461 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2462 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2463 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2464 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2465 END IF;
2466 -- ************************************
2467 -- STEP 1. Initialization and error checks
2468
2469 l_match_str := ' OR ';
2470 IF p_match_type = 'AND' THEN
2471 l_match_str := ' AND ';
2472 ELSIF p_match_type = 'OR' THEN
2473 l_match_str := ' OR ';
2474 END IF;
2475 SAVEPOINT find_persons;
2479 l_max_thresh:=nvl(FND_PROFILE.VALUE('HZ_DQM_MAX_EVAL_THRESH'),200);
2476 l_entered_max_score:= init_search(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list, l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
2477 IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
2478
2480 IF nvl(FND_PROFILE.VALUE('HZ_DQM_SCORE_UNTIL_THRESH'),'N')='Y' THEN
2481 g_score_until_thresh := true;
2482 ELSE
2483 g_score_until_thresh := false;
2484 END IF;
2485 l_party_site_contains_str := check_party_sites_bulk (p_party_site_list);
2486 l_contact_contains_str := check_contacts_bulk (p_contact_list);
2487 l_contact_pt_contains_str := check_cpts_bulk (p_contact_point_list);
2488 l_party_contains_str := check_parties_bulk (p_party_search_rec) ;
2489 init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2490
2491 -- Setup Search Context ID
2492 SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
2493 x_search_ctx_id := l_search_ctx_id;
2494
2495 IF l_party_contains_str IS NULL THEN
2496 defpt := 1;
2497 END IF;
2498 IF l_party_site_contains_str IS NULL THEN
2499 defps := 1;
2500 END IF;
2501 IF l_contact_contains_str IS NULL THEN
2502 defct := 1;
2503 END IF;
2504 IF l_contact_pt_contains_str IS NULL THEN
2505 defcpt := 1;
2506 END IF;
2507
2508 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2509 hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2510 hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2511 hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2512 hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2513 hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2514 hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2515 END IF;
2516 FOR I in 1..5 LOOP
2517 do_eval(I);
2518 END LOOP;
2519 ROLLBACK to find_persons;
2520 IF l_contact_contains_str IS NOT NULL THEN
2521 eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
2522 END IF;
2523 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2524 hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 33 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2525 END IF;
2526 x_num_matches := 0;
2527 l_num_eval := 0;
2528 IF l_match_str = ' OR ' THEN
2529 l_party_id := H_SCORES.FIRST;
2530 ELSE
2531 l_party_id := H_PARTY_ID_LIST.FIRST;
2532 END IF;
2533 WHILE l_party_id IS NOT NULL LOOP
2534 l_num_eval:= l_num_eval+1;
2535 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2536 hz_utility_v2pub.debug(p_message=>'Match Party ID '||H_SCORES(l_party_id).PARTY_ID,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2537 hz_utility_v2pub.debug(p_message=>'Score '||round((H_SCORES(l_party_id).TOTAL_SCORE/l_entered_max_score)*100),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2538 END IF;
2539 IF (H_SCORES(l_party_id).TOTAL_SCORE/l_entered_max_score)>=(175/525) THEN
2540 INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
2541 VALUES (l_search_ctx_id,H_SCORES(l_party_id).PARTY_ID,round((H_SCORES(l_party_id).TOTAL_SCORE/l_entered_max_score)*100));
2542 x_num_matches := x_num_matches+1;
2543 END IF;
2544 IF l_match_str = ' OR ' THEN
2545 l_party_id:=H_SCORES.NEXT(l_party_id);
2546 ELSE
2547 l_party_id:=H_PARTY_ID_LIST.NEXT(l_party_id);
2548 END IF;
2549 END LOOP;
2550
2551 ----------INSERT INTO HZ_MATCHED_PARTY_SITES -----
2552 IF p_ins_details = 'Y' THEN
2553 FORALL I in 1..h_ps_id.COUNT
2554 INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (
2555 l_search_ctx_id, h_ps_id(I), h_ps_party_id(I), h_ps_score(I));
2556 END IF;
2557 ----------INSERT INTO HZ_MATCHED_CONTACTS-----
2558 IF p_ins_details = 'Y' THEN
2559 FORALL I in 1..h_ct_id.COUNT
2560 INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (
2561 l_search_ctx_id, h_ct_id(I), h_ct_party_id(I), h_ct_score(I));
2562 END IF;
2563 ----------INSERT INTO HZ_MATCHED_CPTS-----
2564 IF p_ins_details = 'Y' THEN
2565 FORALL I in 1..h_cpt_id.COUNT
2566 INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
2567 l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));
2568 END IF;
2569
2570 HZ_DQM_SEARCH_UTIL.set_num_eval(l_num_eval);
2571 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2572 hz_utility_v2pub.debug(p_message=>'find_persons(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2573 END IF;
2574 EXCEPTION
2575 WHEN FND_API.G_EXC_ERROR THEN
2576 ROLLBACK to find_persons;
2577 RAISE FND_API.G_EXC_ERROR;
2578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2579 ROLLBACK to find_persons;
2583 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2581 WHEN OTHERS THEN
2582 ROLLBACK to find_persons;
2584 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_persons');
2585 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
2586 FND_MSG_PUB.ADD;
2587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2588 END find_persons;
2589 PROCEDURE find_persons (
2590 p_rule_id IN NUMBER,
2591 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2592 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2593 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2594 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2595 p_restrict_sql IN VARCHAR2,
2596 p_match_type IN VARCHAR2,
2597 p_ins_details IN VARCHAR2,
2598 x_search_ctx_id OUT NUMBER,
2599 x_num_matches OUT NUMBER
2600 ) IS
2601 BEGIN
2602 find_persons(p_rule_id,p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list,
2603 p_restrict_sql,p_match_type,NULL,p_ins_details,x_search_ctx_id,x_num_matches);
2604 END find_persons;
2605
2606 -------------------------------------------------------------------------------------
2607 -------------------- BULK MATCH RULE ::: find_party_details ------------------------
2608 -------------------------------------------------------------------------------------
2609 PROCEDURE find_party_details (
2610 p_rule_id IN NUMBER,
2611 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2612 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2613 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2614 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2615 p_restrict_sql IN VARCHAR2,
2616 p_match_type IN VARCHAR2,
2617 p_search_merged IN VARCHAR2,
2618 x_search_ctx_id OUT NUMBER,
2619 x_num_matches OUT NUMBER
2620 ) IS
2621
2622 BEGIN
2623 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2624 hz_utility_v2pub.debug(p_message=>'find_party_details(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2625 END IF;
2626 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2627 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2628 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2629 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2630 hz_utility_v2pub.debug(p_message=>'p_search_merged '||p_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2631 END IF;
2632 find_parties(p_rule_id,p_party_search_rec,p_party_site_list, p_contact_list, p_contact_point_list,
2633 p_restrict_sql,p_match_type,p_search_merged,null,null, null,'Y',
2634 x_search_ctx_id,x_num_matches);
2635 DELETE FROM HZ_MATCHED_PARTY_SITES_GT ps WHERE SEARCH_CONTEXT_ID = x_search_ctx_id
2636 AND NOT EXISTS
2637 (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ps.PARTY_ID);
2638 DELETE FROM HZ_MATCHED_CONTACTS_GT ct WHERE SEARCH_CONTEXT_ID = x_search_ctx_id
2639 AND NOT EXISTS
2640 (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ct.PARTY_ID);
2641 DELETE FROM HZ_MATCHED_CPTS_GT cpt WHERE SEARCH_CONTEXT_ID = x_search_ctx_id
2642 AND NOT EXISTS
2643 (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = cpt.PARTY_ID);
2644 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2645 hz_utility_v2pub.debug(p_message=>'find_party_details(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2646 END IF;
2647 EXCEPTION
2648 WHEN FND_API.G_EXC_ERROR THEN
2649 RAISE FND_API.G_EXC_ERROR;
2650 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2652 WHEN OTHERS THEN
2653 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2654 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_party_details');
2655 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
2656 FND_MSG_PUB.ADD;
2657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2658 END find_party_details;
2659
2660 -------------------------------------------------------------------------------------
2661 -------------------- BULK MATCH RULE ::: find_duplicate_parties -------------------
2662 -------------------------------------------------------------------------------------
2663 PROCEDURE find_duplicate_parties (
2664 p_rule_id IN NUMBER,
2665 p_party_id IN NUMBER,
2666 p_restrict_sql IN VARCHAR2,
2667 p_match_type IN VARCHAR2,
2668 p_dup_batch_id IN NUMBER,
2669 p_search_merged IN VARCHAR2,
2670 x_dup_set_id OUT NUMBER,
2671 x_search_ctx_id OUT NUMBER,
2672 x_num_matches OUT NUMBER
2673 ) IS
2674 l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
2675 l_party_site_list HZ_PARTY_SEARCH.party_site_list;
2676 l_contact_list HZ_PARTY_SEARCH.contact_list;
2677 l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
2678 l_match_idx NUMBER;
2679
2680 BEGIN
2681 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2685 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2682 hz_utility_v2pub.debug(p_message=>'find_duplicate_parties(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2683 END IF;
2684 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2686 hz_utility_v2pub.debug(p_message=>'p_party_id '||p_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2687 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2688 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2689 hz_utility_v2pub.debug(p_message=>'p_dup_batch_id '||p_dup_batch_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2690 hz_utility_v2pub.debug(p_message=>'p_search_merged '||p_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2691 END IF;
2692
2693 get_party_for_search(
2694 p_party_id, l_party_rec,l_party_site_list, l_contact_list, l_cpt_list);
2695
2696 IF NOT check_prim_cond (l_party_rec,
2697 l_party_site_list,
2698 l_contact_list,
2699 l_cpt_list) THEN
2700 x_dup_set_id:=NULL;
2701 x_search_ctx_id:=NULL;
2702 x_num_matches:=0;
2703 RETURN;
2704 END IF;
2705 x_dup_set_id := NULL;
2706 IF p_dup_batch_id IS NOT NULL THEN
2707 SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;
2708 END IF;
2709
2710 find_parties(p_rule_id,l_party_rec,l_party_site_list, l_contact_list, l_cpt_list,
2711 p_restrict_sql,p_match_type,p_search_merged,p_party_id,x_dup_set_id,p_dup_batch_id,'N',
2712 x_search_ctx_id,x_num_matches);
2713 IF x_num_matches > 0 AND p_dup_batch_id IS NOT NULL THEN
2714 INSERT INTO HZ_DUP_SETS ( DUP_SET_ID, DUP_BATCH_ID, WINNER_PARTY_ID,
2715 STATUS, MERGE_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
2716 LAST_UPDATE_DATE, LAST_UPDATED_BY)
2717 VALUES (x_dup_set_id, p_dup_batch_id, p_party_id, 'SYSBATCH',
2718 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
2719 hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
2720 hz_utility_pub.user_id);
2721
2722 INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,
2723 MERGE_BATCH_ID,merge_flag,SCORE,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,
2724 LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID)
2725 VALUES (p_party_id,x_dup_set_id,0,0,
2726 'Y',100,hz_utility_pub.created_by,hz_utility_pub.creation_date,
2727 hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2728 hz_utility_pub.user_id,p_dup_batch_id);
2729 ELSE
2730 x_dup_set_id := NULL;
2731 END IF;
2732 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2733 hz_utility_v2pub.debug(p_message=>'find_duplicate_parties(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2734 END IF;
2735 EXCEPTION
2736 WHEN FND_API.G_EXC_ERROR THEN
2737 RAISE FND_API.G_EXC_ERROR;
2738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2740 WHEN OTHERS THEN
2741 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2742 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_duplicate_parties');
2743 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
2744 FND_MSG_PUB.ADD;
2745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2746 END find_duplicate_parties;
2747
2748 -------------------------------------------------------------------------------------
2749 -------------------- BULK MATCH RULE ::: find_duplicate_party_sites-----------------
2750 -------------------------------------------------------------------------------------
2751 PROCEDURE find_duplicate_party_sites (
2752 p_rule_id IN NUMBER,
2753 p_party_site_id IN NUMBER,
2754 p_party_id IN NUMBER,
2755 p_restrict_sql IN VARCHAR2,
2756 p_match_type IN VARCHAR2,
2757 x_search_ctx_id OUT NUMBER,
2758 x_num_matches OUT NUMBER
2759 ) IS
2760
2761 l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
2762 l_party_site_list HZ_PARTY_SEARCH.party_site_list;
2763 l_contact_list HZ_PARTY_SEARCH.contact_list;
2764 l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
2765 contact_point_ids HZ_PARTY_SEARCH.IDList;
2766 p_party_site_list HZ_PARTY_SEARCH.IDList;
2767 p_contact_ids HZ_PARTY_SEARCH.IDList;
2768 l_match_idx NUMBER;
2769 cursor get_cpts_for_party_sites is select contact_point_id
2770 from hz_contact_points
2771 where owner_table_name = 'HZ_PARTY_SITES'
2772 and primary_flag='Y'
2773 and owner_table_id = p_party_site_id;
2774 BEGIN
2775 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2776 hz_utility_v2pub.debug(p_message=>'find_duplicate_party_sites(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2777 END IF;
2778 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2779 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2780 hz_utility_v2pub.debug(p_message=>'p_party_site_id '||p_party_site_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2784 END IF;
2781 hz_utility_v2pub.debug(p_message=>'p_party_id '||p_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2782 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2783 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2785 p_party_site_list(1) := p_party_site_id;
2786 OPEN get_cpts_for_party_sites;
2787 LOOP
2788 FETCH get_cpts_for_party_sites BULK COLLECT INTO contact_point_ids;
2789 EXIT WHEN get_cpts_for_party_sites%NOTFOUND;
2790 END LOOP;
2791 CLOSE get_cpts_for_party_sites;
2792
2793 get_search_criteria (
2794 null,
2795 p_party_site_list,
2796 HZ_PARTY_SEARCH.G_MISS_ID_LIST,
2797 contact_point_ids,
2798 l_party_search_rec,
2799 l_party_site_list,
2800 l_contact_list,
2801 l_contact_point_list) ;
2802 IF NOT check_prim_cond (l_party_search_rec,
2803 l_party_site_list,
2804 l_contact_list,
2805 l_contact_point_list) THEN
2806 x_search_ctx_id:=NULL;
2807 x_num_matches:=0;
2808 RETURN;
2809 END IF;
2810
2811 get_matching_party_sites (p_rule_id,
2812 p_party_id,
2813 l_party_site_list,
2814 l_contact_point_list,
2815 p_restrict_sql,
2816 p_match_type,
2817 p_party_site_id,
2818 x_search_ctx_id,
2819 x_num_matches);
2820 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2821 hz_utility_v2pub.debug(p_message=>'find_duplicate_party_sites(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2822 END IF;
2823 EXCEPTION
2824 WHEN FND_API.G_EXC_ERROR THEN
2825 RAISE FND_API.G_EXC_ERROR;
2826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2828 WHEN OTHERS THEN
2829 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2830 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_duplicate_party_sites');
2831 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
2832 FND_MSG_PUB.ADD;
2833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2834 END find_duplicate_party_sites;
2835
2836 -------------------------------------------------------------------------------------
2837 -------------------- BULK MATCH RULE ::: find_duplicate_contacts--------------------
2838 -------------------------------------------------------------------------------------
2839 PROCEDURE find_duplicate_contacts (
2840 p_rule_id IN NUMBER,
2841 p_org_contact_id IN NUMBER,
2842 p_party_id IN NUMBER,
2843 p_restrict_sql IN VARCHAR2,
2844 p_match_type IN VARCHAR2,
2845 x_search_ctx_id OUT NUMBER,
2846 x_num_matches OUT NUMBER
2847 ) IS
2848
2849 l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
2850 l_party_site_list HZ_PARTY_SEARCH.party_site_list;
2851 l_contact_list HZ_PARTY_SEARCH.contact_list;
2852 l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
2853 contact_point_ids HZ_PARTY_SEARCH.IDList;
2854 p_party_site_list HZ_PARTY_SEARCH.IDList;
2855 p_contact_ids HZ_PARTY_SEARCH.IDList;
2856 l_match_idx NUMBER;
2857 cursor get_cpt_for_contact_id is select contact_point_id
2858 from hz_org_contacts a, hz_relationships b, hz_contact_points c
2859 where a.party_relationship_id = b.relationship_id
2860 and c.owner_table_name = 'HZ_PARTIES'
2861 and c.primary_flag='Y'
2862 and c.owner_table_id = b.party_id
2863 and b.directional_flag = 'F'
2864 and a.org_contact_id = p_org_contact_id;
2865 BEGIN
2866 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2867 hz_utility_v2pub.debug(p_message=>'find_duplicate_contacts(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2868 END IF;
2869 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2870 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2871 hz_utility_v2pub.debug(p_message=>'p_org_contact_id '||p_org_contact_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2872 hz_utility_v2pub.debug(p_message=>'p_party_id '||p_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2873 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2874 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2875 END IF;
2876 p_contact_ids(1) := p_org_contact_id;
2877 OPEN get_cpt_for_contact_id;
2878 LOOP
2879 FETCH get_cpt_for_contact_id BULK COLLECT INTO contact_point_ids;
2880 EXIT WHEN get_cpt_for_contact_id%NOTFOUND;
2881 END LOOP;
2882 CLOSE get_cpt_for_contact_id;
2883
2884 get_search_criteria (
2885 null,
2886 HZ_PARTY_SEARCH.G_MISS_ID_LIST,
2887 p_contact_ids,
2888 contact_point_ids,
2889 l_party_search_rec,
2890 l_party_site_list,
2891 l_contact_list,
2892 l_contact_point_list) ;
2893 IF NOT check_prim_cond (l_party_search_rec,
2894 l_party_site_list,
2898 x_num_matches:=0;
2895 l_contact_list,
2896 l_contact_point_list) THEN
2897 x_search_ctx_id:=NULL;
2899 RETURN;
2900 END IF;
2901
2902 get_matching_contacts (p_rule_id,
2903 p_party_id,
2904 l_contact_list,
2905 l_contact_point_list,
2906 p_restrict_sql,
2907 p_match_type,
2908 p_org_contact_id,
2909 x_search_ctx_id,
2910 x_num_matches);
2911
2912 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2913 hz_utility_v2pub.debug(p_message=>'find_duplicate_contacts(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2914 END IF;
2915 EXCEPTION
2916 WHEN FND_API.G_EXC_ERROR THEN
2917 RAISE FND_API.G_EXC_ERROR;
2918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2920 WHEN OTHERS THEN
2921 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2922 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_duplicate_contacts');
2923 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
2924 FND_MSG_PUB.ADD;
2925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2926 END find_duplicate_contacts;
2927
2928 -------------------------------------------------------------------------------------
2929 -------------------- BULK MATCH RULE ::: find_duplicate_contact_points -------------
2930 -------------------------------------------------------------------------------------
2931 PROCEDURE find_duplicate_contact_points (
2932 p_rule_id IN NUMBER,
2933 p_contact_point_id IN NUMBER,
2934 p_party_id IN NUMBER,
2935 p_restrict_sql IN VARCHAR2,
2936 p_match_type IN VARCHAR2,
2937 x_search_ctx_id OUT NUMBER,
2938 x_num_matches OUT NUMBER
2939 ) IS
2940 l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
2941 l_party_site_list HZ_PARTY_SEARCH.party_site_list;
2942 l_contact_list HZ_PARTY_SEARCH.contact_list;
2943 l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
2944 contact_point_ids HZ_PARTY_SEARCH.IDList;
2945 p_party_site_list HZ_PARTY_SEARCH.IDList;
2946 p_contact_ids HZ_PARTY_SEARCH.IDList;
2947 l_match_idx NUMBER;
2948
2949 BEGIN
2950 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2951 hz_utility_v2pub.debug(p_message=>'find_duplicate_contact_points(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2952 END IF;
2953 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2954 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2955 hz_utility_v2pub.debug(p_message=>'p_contact_point_id '||p_contact_point_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2956 hz_utility_v2pub.debug(p_message=>'p_party_id '||p_party_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2957 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2958 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2959 END IF;
2960 contact_point_ids(1) := p_contact_point_id;
2961 get_search_criteria (
2962 null,
2963 HZ_PARTY_SEARCH.G_MISS_ID_LIST,
2964 HZ_PARTY_SEARCH.G_MISS_ID_LIST,
2965 contact_point_ids,
2966 l_party_search_rec,
2967 l_party_site_list,
2968 l_contact_list,
2969 l_contact_point_list );
2970
2971 IF NOT check_prim_cond (l_party_search_rec,
2972 l_party_site_list,
2973 l_contact_list,
2974 l_contact_point_list) THEN
2975 x_search_ctx_id:=NULL;
2976 x_num_matches:=0;
2977 RETURN;
2978 END IF;
2979 get_matching_contact_points (
2980 p_rule_id,
2981 p_party_id,
2982 l_contact_point_list,
2983 p_restrict_sql,
2984 p_match_type,
2985 p_contact_point_id,
2986 x_search_ctx_id,
2987 x_num_matches );
2988 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2989 hz_utility_v2pub.debug(p_message=>'find_duplicate_contact_points(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
2990 END IF;
2991 EXCEPTION
2992 WHEN FND_API.G_EXC_ERROR THEN
2993 RAISE FND_API.G_EXC_ERROR;
2994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2996 WHEN OTHERS THEN
2997 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
2998 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.find_duplicate_contact_points');
2999 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
3000 FND_MSG_PUB.ADD;
3001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3002 END find_duplicate_contact_points;
3003
3004 -------------------------------------------------------------------------------------
3005 -------------------- BULK MATCH RULE ::: find_parties_dynamic-----------------------
3006 -------------------------------------------------------------------------------------
3007 PROCEDURE find_parties_dynamic (
3008 p_rule_id IN NUMBER,
3009 p_attrib_id1 IN NUMBER,
3010 p_attrib_id2 IN NUMBER,
3011 p_attrib_id3 IN NUMBER,
3012 p_attrib_id4 IN NUMBER,
3013 p_attrib_id5 IN NUMBER,
3017 p_attrib_id9 IN NUMBER,
3014 p_attrib_id6 IN NUMBER,
3015 p_attrib_id7 IN NUMBER,
3016 p_attrib_id8 IN NUMBER,
3018 p_attrib_id10 IN NUMBER,
3019 p_attrib_id11 IN NUMBER,
3020 p_attrib_id12 IN NUMBER,
3021 p_attrib_id13 IN NUMBER,
3022 p_attrib_id14 IN NUMBER,
3023 p_attrib_id15 IN NUMBER,
3024 p_attrib_id16 IN NUMBER,
3025 p_attrib_id17 IN NUMBER,
3026 p_attrib_id18 IN NUMBER,
3027 p_attrib_id19 IN NUMBER,
3028 p_attrib_id20 IN NUMBER,
3029 p_attrib_val1 IN VARCHAR2,
3030 p_attrib_val2 IN VARCHAR2,
3031 p_attrib_val3 IN VARCHAR2,
3032 p_attrib_val4 IN VARCHAR2,
3033 p_attrib_val5 IN VARCHAR2,
3034 p_attrib_val6 IN VARCHAR2,
3035 p_attrib_val7 IN VARCHAR2,
3036 p_attrib_val8 IN VARCHAR2,
3037 p_attrib_val9 IN VARCHAR2,
3038 p_attrib_val10 IN VARCHAR2,
3039 p_attrib_val11 IN VARCHAR2,
3040 p_attrib_val12 IN VARCHAR2,
3041 p_attrib_val13 IN VARCHAR2,
3042 p_attrib_val14 IN VARCHAR2,
3043 p_attrib_val15 IN VARCHAR2,
3044 p_attrib_val16 IN VARCHAR2,
3045 p_attrib_val17 IN VARCHAR2,
3046 p_attrib_val18 IN VARCHAR2,
3047 p_attrib_val19 IN VARCHAR2,
3048 p_attrib_val20 IN VARCHAR2,
3049 p_restrict_sql IN VARCHAR2,
3050 p_match_type IN VARCHAR2,
3051 p_search_merged IN VARCHAR2,
3052 x_search_ctx_id OUT NUMBER,
3053 x_num_matches OUT NUMBER
3054 ) IS
3055 BEGIN
3056 call_api_dynamic(p_rule_id,p_attrib_id1, p_attrib_id2,p_attrib_id3,p_attrib_id4,p_attrib_id5,
3057 p_attrib_id6,p_attrib_id7,p_attrib_id8,p_attrib_id9,p_attrib_id10,
3058 p_attrib_id11,p_attrib_id12,p_attrib_id13,p_attrib_id14,p_attrib_id15,
3059 p_attrib_id16,p_attrib_id17,p_attrib_id18,p_attrib_id19,p_attrib_id20,
3060 p_attrib_val1,p_attrib_val2,p_attrib_val3,p_attrib_val4,p_attrib_val5,
3061 p_attrib_val6,p_attrib_val7,p_attrib_val8,p_attrib_val9,p_attrib_val10,
3062 p_attrib_val11,p_attrib_val12,p_attrib_val13,p_attrib_val14,p_attrib_val15,
3063 p_attrib_val16,p_attrib_val17,p_attrib_val18,p_attrib_val19,p_attrib_val20,
3064 p_restrict_sql,'FIND_PARTIES',p_match_type,null,p_search_merged,x_search_ctx_id,x_num_matches);
3065 END;
3066
3067 -------------------------------------------------------------------------------------
3068 -------------------- BULK MATCH RULE ::: call_api_dynamic---------------------------
3069 -------------------------------------------------------------------------------------
3070 PROCEDURE call_api_dynamic (
3071 p_rule_id IN NUMBER,
3072 p_attrib_id1 IN NUMBER,
3073 p_attrib_id2 IN NUMBER,
3074 p_attrib_id3 IN NUMBER,
3075 p_attrib_id4 IN NUMBER,
3076 p_attrib_id5 IN NUMBER,
3077 p_attrib_id6 IN NUMBER,
3078 p_attrib_id7 IN NUMBER,
3079 p_attrib_id8 IN NUMBER,
3080 p_attrib_id9 IN NUMBER,
3081 p_attrib_id10 IN NUMBER,
3082 p_attrib_id11 IN NUMBER,
3083 p_attrib_id12 IN NUMBER,
3084 p_attrib_id13 IN NUMBER,
3085 p_attrib_id14 IN NUMBER,
3086 p_attrib_id15 IN NUMBER,
3087 p_attrib_id16 IN NUMBER,
3088 p_attrib_id17 IN NUMBER,
3089 p_attrib_id18 IN NUMBER,
3090 p_attrib_id19 IN NUMBER,
3091 p_attrib_id20 IN NUMBER,
3092 p_attrib_val1 IN VARCHAR2,
3093 p_attrib_val2 IN VARCHAR2,
3094 p_attrib_val3 IN VARCHAR2,
3095 p_attrib_val4 IN VARCHAR2,
3096 p_attrib_val5 IN VARCHAR2,
3097 p_attrib_val6 IN VARCHAR2,
3098 p_attrib_val7 IN VARCHAR2,
3099 p_attrib_val8 IN VARCHAR2,
3100 p_attrib_val9 IN VARCHAR2,
3101 p_attrib_val10 IN VARCHAR2,
3102 p_attrib_val11 IN VARCHAR2,
3103 p_attrib_val12 IN VARCHAR2,
3104 p_attrib_val13 IN VARCHAR2,
3105 p_attrib_val14 IN VARCHAR2,
3106 p_attrib_val15 IN VARCHAR2,
3107 p_attrib_val16 IN VARCHAR2,
3108 p_attrib_val17 IN VARCHAR2,
3109 p_attrib_val18 IN VARCHAR2,
3110 p_attrib_val19 IN VARCHAR2,
3111 p_attrib_val20 IN VARCHAR2,
3112 p_restrict_sql IN VARCHAR2,
3113 p_api_name IN VARCHAR2,
3114 p_match_type IN VARCHAR2,
3115 p_party_id IN NUMBER,
3116 p_search_merged IN VARCHAR2,
3117 x_search_ctx_id OUT NUMBER,
3118 x_num_matches OUT NUMBER
3119 ) IS
3120 TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
3121 l_match_idx NUMBER;
3122 AttrVals AttrList;
3123 l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3124 l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3125 l_contact_list HZ_PARTY_SEARCH.contact_list;
3126 l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3127 l_dup_set_id NUMBER;
3128 l_idx NUMBER;
3129 l_cpt_type VARCHAR2(255);
3130 FIRST BOOLEAN := TRUE;
3131
3132 BEGIN
3133 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3134 hz_utility_v2pub.debug(p_message=>'call_api_dynamic(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3135 END IF;
3136
3137 IF p_attrib_id1 IS NOT NULL THEN
3138 AttrVals(p_attrib_id1):=p_attrib_val1;
3139 END IF;
3140 IF p_attrib_id2 IS NOT NULL THEN
3141 AttrVals(p_attrib_id2):=p_attrib_val2;
3142 END IF;
3143 IF p_attrib_id3 IS NOT NULL THEN
3144 AttrVals(p_attrib_id3):=p_attrib_val3;
3145 END IF;
3146 IF p_attrib_id4 IS NOT NULL THEN
3147 AttrVals(p_attrib_id4):=p_attrib_val4;
3148 END IF;
3149 IF p_attrib_id5 IS NOT NULL THEN
3150 AttrVals(p_attrib_id5):=p_attrib_val5;
3151 END IF;
3152 IF p_attrib_id6 IS NOT NULL THEN
3153 AttrVals(p_attrib_id6):=p_attrib_val6;
3154 END IF;
3155 IF p_attrib_id7 IS NOT NULL THEN
3156 AttrVals(p_attrib_id7):=p_attrib_val7;
3157 END IF;
3158 IF p_attrib_id8 IS NOT NULL THEN
3159 AttrVals(p_attrib_id8):=p_attrib_val8;
3160 END IF;
3161 IF p_attrib_id9 IS NOT NULL THEN
3162 AttrVals(p_attrib_id9):=p_attrib_val9;
3163 END IF;
3164 IF p_attrib_id10 IS NOT NULL THEN
3165 AttrVals(p_attrib_id10):=p_attrib_val10;
3166 END IF;
3167 IF p_attrib_id11 IS NOT NULL THEN
3168 AttrVals(p_attrib_id11):=p_attrib_val11;
3169 END IF;
3170 IF p_attrib_id12 IS NOT NULL THEN
3171 AttrVals(p_attrib_id12):=p_attrib_val12;
3172 END IF;
3173 IF p_attrib_id13 IS NOT NULL THEN
3174 AttrVals(p_attrib_id13):=p_attrib_val13;
3175 END IF;
3176 IF p_attrib_id14 IS NOT NULL THEN
3177 AttrVals(p_attrib_id14):=p_attrib_val14;
3178 END IF;
3179 IF p_attrib_id15 IS NOT NULL THEN
3180 AttrVals(p_attrib_id15):=p_attrib_val15;
3181 END IF;
3182 IF p_attrib_id16 IS NOT NULL THEN
3183 AttrVals(p_attrib_id16):=p_attrib_val16;
3184 END IF;
3185 IF p_attrib_id17 IS NOT NULL THEN
3186 AttrVals(p_attrib_id17):=p_attrib_val17;
3187 END IF;
3188 IF p_attrib_id18 IS NOT NULL THEN
3189 AttrVals(p_attrib_id18):=p_attrib_val18;
3190 END IF;
3191 IF p_attrib_id19 IS NOT NULL THEN
3192 AttrVals(p_attrib_id19):=p_attrib_val19;
3193 END IF;
3194 IF p_attrib_id20 IS NOT NULL THEN
3195 AttrVals(p_attrib_id20):=p_attrib_val20;
3196 END IF;
3197 IF AttrVals.EXISTS(8) THEN
3198 l_party_rec.PARTY_NAME:= AttrVals(8);
3199 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3200 hz_utility_v2pub.debug(p_message=>'l_party_rec.PARTY_NAME '||AttrVals(8),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3201 END IF;
3202 END IF;
3203
3204 IF AttrVals.EXISTS(14) THEN
3205 l_party_rec.PARTY_TYPE:= AttrVals(14);
3206 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3207 hz_utility_v2pub.debug(p_message=>'l_party_rec.PARTY_TYPE '||AttrVals(14),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3208 END IF;
3209 END IF;
3210
3211 IF AttrVals.EXISTS(19) THEN
3212 l_party_rec.JGZZ_FISCAL_CODE:= AttrVals(19);
3213 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3214 hz_utility_v2pub.debug(p_message=>'l_party_rec.JGZZ_FISCAL_CODE '||AttrVals(19),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3215 END IF;
3216 END IF;
3217
3218 IF AttrVals.EXISTS(27) THEN
3219 l_party_site_list(1).ADDRESS:= AttrVals(27);
3220 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3221 hz_utility_v2pub.debug(p_message=>'l_party_site_list(1).ADDRESS '||AttrVals(27),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3222 END IF;
3223 END IF;
3224
3225 IF AttrVals.EXISTS(29) THEN
3226 l_party_site_list(1).CITY:= AttrVals(29);
3227 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3228 hz_utility_v2pub.debug(p_message=>'l_party_site_list(1).CITY '||AttrVals(29),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3229 END IF;
3230 END IF;
3231
3232 IF AttrVals.EXISTS(30) THEN
3233 l_party_site_list(1).POSTAL_CODE:= AttrVals(30);
3234 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3235 hz_utility_v2pub.debug(p_message=>'l_party_site_list(1).POSTAL_CODE '||AttrVals(30),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3236 END IF;
3237 END IF;
3238
3239 IF AttrVals.EXISTS(32) THEN
3240 l_party_site_list(1).STATE:= AttrVals(32);
3241 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3242 hz_utility_v2pub.debug(p_message=>'l_party_site_list(1).STATE '||AttrVals(32),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3243 END IF;
3244 END IF;
3245
3246 IF AttrVals.EXISTS(36) THEN
3247 l_party_site_list(1).COUNTRY:= AttrVals(36);
3248 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3249 hz_utility_v2pub.debug(p_message=>'l_party_site_list(1).COUNTRY '||AttrVals(36),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3250 END IF;
3251 END IF;
3252
3253 IF AttrVals.EXISTS(48) THEN
3254 l_cpt_type := 'EMAIL';
3255 FIRST := FALSE;
3256 FOR I in 1..l_cpt_list.COUNT LOOP
3257 IF l_cpt_list(I).CONTACT_POINT_TYPE=l_cpt_type THEN
3258 l_cpt_list(I).EMAIL_ADDRESS:= AttrVals(48);
3259 FIRST := TRUE;
3260 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3261 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||I||').CONTACT_POINT_TYPE '||l_cpt_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3262 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||I||').EMAIL_ADDRESS '||AttrVals(48),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3263 END IF;
3264 END IF;
3265 END LOOP;
3266 IF not FIRST THEN
3267 l_idx := l_cpt_list.COUNT+1;
3268 l_cpt_list(l_idx).CONTACT_POINT_TYPE:=l_cpt_type;
3269 l_cpt_list(l_idx).EMAIL_ADDRESS:= AttrVals(48);
3270 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3271 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||l_idx||').CONTACT_POINT_TYPE '||l_cpt_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3272 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||l_idx||').EMAIL_ADDRESS '||AttrVals(48),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3273 END IF;
3274 END IF;
3275 END IF;
3276
3277 IF AttrVals.EXISTS(52) THEN
3278 l_cpt_type := 'PHONE';
3279 FIRST := FALSE;
3280 FOR I in 1..l_cpt_list.COUNT LOOP
3281 IF l_cpt_list(I).CONTACT_POINT_TYPE=l_cpt_type THEN
3282 l_cpt_list(I).RAW_PHONE_NUMBER:= AttrVals(52);
3283 FIRST := TRUE;
3284 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3285 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||I||').CONTACT_POINT_TYPE '||l_cpt_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3286 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||I||').RAW_PHONE_NUMBER '||AttrVals(52),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3287 END IF;
3288 END IF;
3289 END LOOP;
3290 IF not FIRST THEN
3291 l_idx := l_cpt_list.COUNT+1;
3292 l_cpt_list(l_idx).CONTACT_POINT_TYPE:=l_cpt_type;
3293 l_cpt_list(l_idx).RAW_PHONE_NUMBER:= AttrVals(52);
3294 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3295 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||l_idx||').CONTACT_POINT_TYPE '||l_cpt_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3296 hz_utility_v2pub.debug(p_message=>'l_cpt_list('||l_idx||').RAW_PHONE_NUMBER '||AttrVals(52),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3297 END IF;
3298 END IF;
3299 END IF;
3300
3301
3302 IF upper(p_api_name) = 'FIND_PARTIES' THEN
3303 find_parties(p_rule_id,l_party_rec,l_party_site_list, l_contact_list, l_cpt_list,
3304 p_restrict_sql,p_match_type,p_search_merged,NULL,NULL,NULL,'N',
3305 x_search_ctx_id,x_num_matches);
3306 ELSIF upper(p_api_name) = 'FIND_PARTY_DETAILS' THEN
3307 find_party_details(p_rule_id,l_party_rec,l_party_site_list, l_contact_list, l_cpt_list,
3308 p_restrict_sql,p_match_type,p_search_merged,
3309 x_search_ctx_id,x_num_matches);
3310 ELSIF upper(p_api_name) = 'FIND_PERSONS' THEN
3311 find_persons(p_rule_id,l_party_rec,l_party_site_list, l_contact_list, l_cpt_list,
3312 p_restrict_sql,p_match_type,'N',
3313 x_search_ctx_id,x_num_matches);
3314 ELSIF upper(p_api_name) = 'GET_MATCHING_PARTY_SITES' THEN
3315 get_matching_party_sites(p_rule_id,p_party_id,l_party_site_list, l_cpt_list,
3316 p_restrict_sql,p_match_type,NULL,
3317 x_search_ctx_id,x_num_matches);
3318 ELSIF upper(p_api_name) = 'GET_MATCHING_CONTACTS' THEN
3322 ELSIF upper(p_api_name) = 'GET_MATCHING_CONTACT_POINTS' THEN
3319 get_matching_contacts(p_rule_id,p_party_id,l_contact_list, l_cpt_list,
3320 p_restrict_sql,p_match_type,NULL,
3321 x_search_ctx_id,x_num_matches);
3323 get_matching_contact_points(p_rule_id,p_party_id, l_cpt_list,
3324 p_restrict_sql,p_match_type,NULL,
3325 x_search_ctx_id,x_num_matches);
3326 END IF;
3327 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3328 hz_utility_v2pub.debug(p_message=>'call_api_dynamic(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3329 END IF;
3330 EXCEPTION
3331 WHEN FND_API.G_EXC_ERROR THEN
3332 RAISE FND_API.G_EXC_ERROR;
3333 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3335 WHEN OTHERS THEN
3336 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
3337 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.call_api_dynamic');
3338 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
3339 FND_MSG_PUB.ADD;
3340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3341 END call_api_dynamic;
3342
3343 -------------------------------------------------------------------------------------
3344 -------------------- BULK MATCH RULE ::: get_matching_party_sites ------------------
3345 -------------------------------------------------------------------------------------
3346
3347 PROCEDURE get_matching_party_sites (
3348 p_rule_id IN NUMBER,
3349 p_party_id IN NUMBER,
3350 p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
3351 p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
3352 p_restrict_sql IN VARCHAR2,
3353 p_match_type IN VARCHAR2,
3354 p_dup_party_site_id IN NUMBER,
3355 x_search_ctx_id OUT NUMBER,
3356 x_num_matches OUT NUMBER
3357 ) IS
3358
3359 -- Strings to hold the generated Intermedia query strings
3360 l_party_contains_str VARCHAR2(32000);
3361 l_match_idx NUMBER;
3362 l_party_site_contains_str VARCHAR2(32000);
3363 l_contact_contains_str VARCHAR2(32000);
3364 l_contact_pt_contains_str VARCHAR2(32000);
3365 l_tmp VARCHAR2(32000);
3366
3367 -- Other local variables
3368 l_match_str VARCHAR2(30); -- Match type (AND or OR)
3369 l_sqlstr VARCHAR2(32000); -- Dynamic SQL String
3370 -- For Score calculation
3371 l_max_score NUMBER;
3372 l_entered_max_score NUMBER;
3373 FIRST BOOLEAN;
3374 l_search_ctx_id NUMBER; -- Generated Search Context ID
3375
3376 l_TX26 VARCHAR2(2000);
3377 l_TX9 VARCHAR2(2000);
3378 l_TX22 VARCHAR2(2000);
3379 l_TX158 VARCHAR2(2000);
3380 l_TX5 VARCHAR2(2000);
3381 l_TX10 VARCHAR2(2000);
3382 l_TX14 VARCHAR2(2000);
3383 H_SCORES HZ_PARTY_SEARCH.score_list;
3384
3385 l_score NUMBER;
3386 l_idx NUMBER;
3387 l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3388 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
3389 l_contact_cur HZ_PARTY_STAGE.StageCurTyp;
3390 l_contact_pt_cur HZ_PARTY_STAGE.StageCurTyp;
3391 l_party_id NUMBER;
3392 l_ps_party_id NUMBER;
3393 l_ct_party_id NUMBER;
3394 l_cpt_party_id NUMBER;
3395 l_cpt_ps_id NUMBER;
3396 l_cpt_contact_id NUMBER;
3397 l_party_site_id NUMBER;
3398 l_org_contact_id NUMBER;
3399 l_contact_pt_id NUMBER;
3400 l_ps_contact_id NUMBER;
3401 l_party_max_score NUMBER;
3402 l_ps_max_score NUMBER;
3403 l_contact_max_score NUMBER;
3404 l_cpt_max_score NUMBER;
3405
3406 defpt NUMBER :=0;
3407 defps NUMBER :=0;
3408 defct NUMBER :=0;
3409 defcpt NUMBER :=0;
3410 l_index NUMBER;
3411
3412
3413 BEGIN
3414 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3415 hz_utility_v2pub.debug(p_message=>'get_matching_party_sites(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3416 END IF;
3417 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3418 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3419 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3420 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3421 hz_utility_v2pub.debug(p_message=>'p_dup_party_site_id '||p_dup_party_site_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3422 END IF;
3423
3424 -- ************************************
3425 -- STEP 1. Initialization and error checks
3426
3427 l_match_str := ' OR ';
3428 IF p_match_type = 'AND' THEN
3429 l_match_str := ' AND ';
3430 ELSIF p_match_type = 'OR' THEN
3431 l_match_str := ' OR ';
3432 END IF;
3433 SAVEPOINT get_matching_party_sites ;
3434 l_entered_max_score:= init_search( HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC, p_party_site_list, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST, p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
3435 g_score_until_thresh := false;
3436 IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
3437
3438 l_party_site_contains_str := check_party_sites_bulk (p_party_site_list);
3439 l_contact_pt_contains_str := check_cpts_bulk (p_contact_point_list);
3440 init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
3441
3442 -- Setup Search Context ID
3446 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3443 SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3444 x_search_ctx_id := l_search_ctx_id;
3445
3447 hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3448 hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3449 hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3450 hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3451 END IF;
3452 IF l_party_site_contains_str IS NULL THEN
3453 defps := 1;
3454 END IF;
3455 IF l_contact_pt_contains_str IS NULL THEN
3456 defcpt := 1;
3457 END IF;
3458
3459 IF l_party_site_contains_str IS NOT NULL THEN
3460 open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL,l_party_site_cur);
3461 LOOP
3462 FETCH l_party_site_cur INTO
3463 l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX26, l_TX9, l_TX14, l_TX22;
3464 EXIT WHEN l_party_site_cur%NOTFOUND;
3465 IF (p_dup_party_site_id IS NULL OR (
3466 p_dup_party_site_id IS NOT NULL AND l_ps_contact_id IS NULL AND
3467 l_party_site_id <> p_dup_party_site_id)) THEN
3468 l_index := map_id(l_party_site_id);
3469 l_score := GET_PARTY_SITES_SCORE(l_match_idx,l_TX26,l_TX9,l_TX14,l_TX22);
3470 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,l_score,defct,defcpt, l_ps_party_id, l_party_site_id, null,null);
3471 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3472 hz_utility_v2pub.debug(p_message=>'Party Site Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3473 hz_utility_v2pub.debug(p_message=>'l_party_site_id '||l_party_site_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3474 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3475 END IF;
3476 END IF;
3477 END LOOP;
3478 CLOSE l_party_site_cur;
3479 END IF;
3480
3481 IF l_contact_pt_contains_str IS NOT NULL THEN
3482 open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL,l_contact_pt_cur);
3483 LOOP
3484 FETCH l_contact_pt_cur INTO
3485 l_contact_pt_id, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX5, l_TX158, l_TX10;
3486 EXIT WHEN l_contact_pt_cur%NOTFOUND;
3487 IF (l_cpt_ps_id IS NOT NULL AND (p_dup_party_site_id IS NULL OR (
3488 p_dup_party_site_id IS NOT NULL AND l_cpt_contact_id IS NULL AND p_dup_party_site_id <> l_cpt_ps_id))) THEN
3489 l_index := map_id(l_cpt_ps_id);
3490 IF l_match_str = ' OR ' THEN
3491 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3492 IF NOT H_SCORES.EXISTS(l_index) THEN
3493 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,defct,l_score,l_cpt_party_id,l_cpt_ps_id,l_cpt_contact_id,l_contact_pt_id);
3494 ELSE
3495 IF l_score > H_SCORES(l_index).CONTACT_POINT_SCORE THEN
3496 H_SCORES(l_index).TOTAL_SCORE :=
3497 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_POINT_SCORE+l_score;
3498 H_SCORES(l_index).CONTACT_POINT_SCORE := l_score;
3499 END IF;
3500 END IF;
3501 ELSE
3502 IF H_SCORES.EXISTS(l_index) THEN
3503 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3504 IF l_score > H_SCORES(l_index).CONTACT_POINT_SCORE THEN
3505 H_SCORES(l_index).TOTAL_SCORE :=
3506 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_POINT_SCORE+l_score;
3507 H_SCORES(l_index).CONTACT_POINT_SCORE := l_score;
3508 END IF;
3509 ELSIF defps=1 THEN
3510 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3511 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,defct,l_score,l_cpt_party_id,l_cpt_ps_id,l_cpt_contact_id,l_contact_pt_id);
3512 END IF;
3513 END IF;
3514 END IF;
3515 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3516 hz_utility_v2pub.debug(p_message=>'Contact_point Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3517 hz_utility_v2pub.debug(p_message=>'l_party_site_id '||l_cpt_ps_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3518 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3519 END IF;
3520 END LOOP;
3521 CLOSE l_contact_pt_cur;
3522 END IF;
3523 ROLLBACK to get_matching_party_sites ;
3524 x_num_matches := 0;
3525 l_party_site_id := H_SCORES.FIRST;
3526 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3527 hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 33 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3528 END IF;
3529 WHILE l_party_site_id IS NOT NULL LOOP
3530 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3534 IF l_match_str = ' OR ' THEN
3531 hz_utility_v2pub.debug(p_message=>'Match Party Site ID '||H_SCORES(l_party_site_id).PARTY_SITE_ID,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3532 hz_utility_v2pub.debug(p_message=>'Score '||round((H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3533 END IF;
3535 IF (H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)>=(175/525) THEN
3536 INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE)
3537 VALUES (l_search_ctx_id,H_SCORES(l_party_site_id).PARTY_ID, H_SCORES(l_party_site_id).PARTY_SITE_ID, (H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100);
3538 x_num_matches := x_num_matches+1;
3539 END IF;
3540 ELSE
3541 IF H_SCORES(l_party_site_id).PARTY_SITE_SCORE>0 AND
3542 H_SCORES(l_party_site_id).CONTACT_POINT_SCORE>0 AND
3543 (H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)>=(175/525) THEN
3544 INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE)
3545 VALUES (l_search_ctx_id,H_SCORES(l_party_site_id).PARTY_ID, H_SCORES(l_party_site_id).PARTY_SITE_ID, round((H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100));
3546 x_num_matches := x_num_matches+1;
3547 END IF;
3548 END IF;
3549 l_party_site_id:=H_SCORES.NEXT(l_party_site_id);
3550 END LOOP;
3551 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3552 hz_utility_v2pub.debug(p_message=>'get_matching_party_sites(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3553 END IF;
3554 EXCEPTION
3555 WHEN FND_API.G_EXC_ERROR THEN
3556 ROLLBACK to get_matching_party_sites ;
3557 RAISE FND_API.G_EXC_ERROR;
3558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3559 ROLLBACK to get_matching_party_sites ;
3560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3561 WHEN OTHERS THEN
3562 ROLLBACK to get_matching_party_sites ;
3563 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
3564 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.get_matching_party_sites');
3565 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
3566 FND_MSG_PUB.ADD;
3567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3568 END get_matching_party_sites;
3569
3570 -------------------------------------------------------------------------------------
3571 -------------------- BULK MATCH RULE ::: get_matching_contacts --------------------
3572 -------------------------------------------------------------------------------------
3573
3574 PROCEDURE get_matching_contacts (
3575 p_rule_id IN NUMBER,
3576 p_party_id IN NUMBER,
3577 p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
3578 p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
3579 p_restrict_sql IN VARCHAR2,
3580 p_match_type IN VARCHAR2,
3581 p_dup_contact_id IN NUMBER,
3582 x_search_ctx_id OUT NUMBER,
3583 x_num_matches OUT NUMBER
3584 ) IS
3585
3586 -- Strings to hold the generated Intermedia query strings
3587 l_party_contains_str VARCHAR2(32000);
3588 l_party_site_contains_str VARCHAR2(32000);
3589 l_contact_contains_str VARCHAR2(32000);
3590 l_contact_pt_contains_str VARCHAR2(32000);
3591 l_tmp VARCHAR2(32000);
3592
3593 -- Other local variables
3594 l_match_str VARCHAR2(30); -- Match type (AND or OR)
3595 l_match_idx NUMBER;
3596 l_sqlstr VARCHAR2(32000); -- Dynamic SQL String
3597 -- For Score calculation
3598 l_max_score NUMBER;
3599 l_entered_max_score NUMBER;
3600 FIRST BOOLEAN;
3601 l_search_ctx_id NUMBER; -- Generated Search Context ID
3602
3603 l_TX158 VARCHAR2(2000);
3604 l_TX5 VARCHAR2(2000);
3605 l_TX10 VARCHAR2(2000);
3606 H_SCORES HZ_PARTY_SEARCH.score_list;
3607
3608 l_score NUMBER;
3609 l_idx NUMBER;
3610 l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3611 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
3612 l_contact_cur HZ_PARTY_STAGE.StageCurTyp;
3613 l_contact_pt_cur HZ_PARTY_STAGE.StageCurTyp;
3614 l_party_id NUMBER;
3615 l_ps_party_id NUMBER;
3616 l_ct_party_id NUMBER;
3617 l_cpt_party_id NUMBER;
3618 l_cpt_ps_id NUMBER;
3619 l_cpt_contact_id NUMBER;
3620 l_party_site_id NUMBER;
3621 l_org_contact_id NUMBER;
3622 l_contact_pt_id NUMBER;
3623
3624 defpt NUMBER :=0;
3625 defps NUMBER :=0;
3626 defct NUMBER :=0;
3627 defcpt NUMBER :=0;
3628 l_index NUMBER;
3629 l_party_max_score NUMBER;
3630 l_ps_max_score NUMBER;
3631 l_contact_max_score NUMBER;
3632 l_cpt_max_score NUMBER;
3633
3634
3635 BEGIN
3636 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3637 hz_utility_v2pub.debug(p_message=>'get_matching_contacts(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3638 END IF;
3639 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3640 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3641 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3642 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3643 hz_utility_v2pub.debug(p_message=>'p_dup_contact_id '||p_dup_contact_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3647 -- STEP 1. Initialization and error checks
3644 END IF;
3645
3646 -- ************************************
3648
3649 l_match_str := ' OR ';
3650 IF p_match_type = 'AND' THEN
3651 l_match_str := ' AND ';
3652 ELSIF p_match_type = 'OR' THEN
3653 l_match_str := ' OR ';
3654 END IF;
3655 SAVEPOINT get_matching_contacts ;
3656 l_entered_max_score:= init_search( HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC, HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, p_contact_list, p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
3657 g_score_until_thresh := false;
3658 IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
3659
3660 l_contact_contains_str := check_contacts_bulk (p_contact_list);
3661 l_contact_pt_contains_str := check_cpts_bulk (p_contact_point_list);
3662 init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
3663
3664 -- Setup Search Context ID
3665 SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3666 x_search_ctx_id := l_search_ctx_id;
3667
3668 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3669 hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3670 hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3671 hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3672 hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3673 END IF;
3674 IF l_contact_contains_str IS NULL THEN
3675 defct := 1;
3676 END IF;
3677 IF l_contact_pt_contains_str IS NULL THEN
3678 defcpt := 1;
3679 END IF;
3680
3681 IF l_contact_contains_str IS NOT NULL THEN
3682 open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL,l_contact_cur);
3683 LOOP
3684 FETCH l_contact_cur INTO
3685 l_org_contact_id, l_ct_party_id ;
3686 EXIT WHEN l_contact_cur%NOTFOUND;
3687 IF (p_dup_contact_id IS NULL OR l_org_contact_id <> p_dup_contact_id) THEN
3688 l_index := map_id(l_org_contact_id);
3689 l_score := GET_CONTACTS_SCORE(l_match_idx);
3690 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,l_score,defcpt, l_ct_party_id, null, l_org_contact_id, null);
3691 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3692 hz_utility_v2pub.debug(p_message=>'Contact Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3693 hz_utility_v2pub.debug(p_message=>'l_org_contact_id '||l_org_contact_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3694 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3695 END IF;
3696 END IF;
3697 END LOOP;
3698 CLOSE l_contact_cur;
3699 END IF;
3700
3701 IF l_contact_pt_contains_str IS NOT NULL THEN
3702 open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL,l_contact_pt_cur);
3703 LOOP
3704 FETCH l_contact_pt_cur INTO
3705 l_contact_pt_id, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX5, l_TX158, l_TX10;
3706 EXIT WHEN l_contact_pt_cur%NOTFOUND;
3707 IF (l_cpt_contact_id IS NOT NULL AND (p_dup_contact_id IS NULL OR l_cpt_contact_id <> p_dup_contact_id)) THEN
3708 l_index := map_id(l_cpt_contact_id);
3709 IF l_match_str = ' OR ' THEN
3710 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3711 IF NOT H_SCORES.EXISTS(l_index) THEN
3712 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,defct,l_score,l_cpt_party_id,l_cpt_ps_id,l_cpt_contact_id,l_contact_pt_id);
3713 ELSE
3714 IF l_score > H_SCORES(l_index).CONTACT_POINT_SCORE THEN
3715 H_SCORES(l_index).TOTAL_SCORE :=
3716 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_POINT_SCORE+l_score;
3717 H_SCORES(l_index).CONTACT_POINT_SCORE := l_score;
3718 END IF;
3719 END IF;
3720 ELSE
3721 IF H_SCORES.EXISTS(l_index) THEN
3722 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3723 IF l_score > H_SCORES(l_index).CONTACT_POINT_SCORE THEN
3724 H_SCORES(l_index).TOTAL_SCORE :=
3725 H_SCORES(l_index).TOTAL_SCORE-H_SCORES(l_index).CONTACT_POINT_SCORE+l_score;
3726 H_SCORES(l_index).CONTACT_POINT_SCORE := l_score;
3727 END IF;
3728 ELSIF defps=1 THEN
3729 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3730 H_SCORES(l_index) := get_new_score_rec(l_score,defpt,defps,defct,l_score,l_cpt_party_id,l_cpt_ps_id,l_cpt_contact_id,l_contact_pt_id);
3731 END IF;
3732 END IF;
3733 END IF;
3734 END LOOP;
3735 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3736 hz_utility_v2pub.debug(p_message=>'Contact_point Level Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3740 CLOSE l_contact_pt_cur;
3737 hz_utility_v2pub.debug(p_message=>'l_org_contact_id '||l_cpt_contact_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3738 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3739 END IF;
3741 END IF;
3742 ROLLBACK to get_matching_contacts ;
3743 x_num_matches := 0;
3744 l_org_contact_id := H_SCORES.FIRST;
3745 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3746 hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 33 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3747 END IF;
3748 WHILE l_org_contact_id IS NOT NULL LOOP
3749 IF l_match_str = ' OR ' THEN
3750 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3751 hz_utility_v2pub.debug(p_message=>'Match Contact ID '||H_SCORES(l_org_contact_id).ORG_CONTACT_ID,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3752 hz_utility_v2pub.debug(p_message=>'Score '||round((H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3753 END IF;
3754 IF (H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)>=(175/525) THEN
3755 INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE)
3756 VALUES (l_search_ctx_id,H_SCORES(l_org_contact_id).PARTY_ID, H_SCORES(l_org_contact_id).ORG_CONTACT_ID, (H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100);
3757 x_num_matches := x_num_matches+1;
3758 END IF;
3759 ELSE
3760 IF H_SCORES(l_org_contact_id).CONTACT_SCORE>0 AND
3761 H_SCORES(l_org_contact_id).CONTACT_POINT_SCORE>0 AND
3762 (H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)>=(175/525) THEN
3763 INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE)
3764 VALUES (l_search_ctx_id,H_SCORES(l_org_contact_id).PARTY_ID, H_SCORES(l_org_contact_id).ORG_CONTACT_ID, round((H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100));
3765 x_num_matches := x_num_matches+1;
3766 END IF;
3767 END IF;
3768 l_org_contact_id:=H_SCORES.NEXT(l_org_contact_id);
3769 END LOOP;
3770 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3771 hz_utility_v2pub.debug(p_message=>'get_matching_contacts(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3772 END IF;
3773 EXCEPTION
3774 WHEN FND_API.G_EXC_ERROR THEN
3775 ROLLBACK to get_matching_contacts ;
3776 RAISE FND_API.G_EXC_ERROR;
3777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3778 ROLLBACK to get_matching_contacts ;
3779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3780 WHEN OTHERS THEN
3781 ROLLBACK to get_matching_contacts ;
3782 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
3783 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.get_matching_contacts');
3784 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
3785 FND_MSG_PUB.ADD;
3786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3787 END get_matching_contacts;
3788
3789 -------------------------------------------------------------------------------------
3790 -------------------- BULK MATCH RULE ::: get_matching_contact_points ---------------
3791 -------------------------------------------------------------------------------------
3792
3793 PROCEDURE get_matching_contact_points (
3794 p_rule_id IN NUMBER,
3795 p_party_id IN NUMBER,
3796 p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
3797 p_restrict_sql IN VARCHAR2,
3798 p_match_type IN VARCHAR2,
3799 p_dup_contact_point_id IN NUMBER,
3800 x_search_ctx_id OUT NUMBER,
3801 x_num_matches OUT NUMBER
3802 ) IS
3803
3804
3805 -- Strings to hold the generated Intermedia query strings
3806 l_contact_pt_contains_str VARCHAR2(32000);
3807 l_tmp VARCHAR2(32000);
3808
3809 -- Other local variables
3810 l_match_str VARCHAR2(30); -- Match type (AND or OR)
3811 l_match_idx NUMBER;
3812 -- For Score calculation
3813 l_entered_max_score NUMBER;
3814 l_search_ctx_id NUMBER; -- Generated Search Context ID
3815
3816 l_TX158 VARCHAR2(2000);
3817 l_TX5 VARCHAR2(2000);
3818 l_TX10 VARCHAR2(2000);
3819
3820 l_score NUMBER;
3821 l_idx NUMBER;
3822 l_contact_pt_cur HZ_PARTY_STAGE.StageCurTyp;
3823 l_cpt_party_id NUMBER;
3824 l_cpt_ps_id NUMBER;
3825 l_cpt_contact_id NUMBER;
3826 l_contact_pt_id NUMBER;
3827 H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3828 H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
3829 H_SCORE HZ_PARTY_SEARCH.IDList;
3830
3831 cnt NUMBER :=0;
3832 l_party_max_score NUMBER;
3833 l_ps_max_score NUMBER;
3834 l_contact_max_score NUMBER;
3835 l_cpt_max_score NUMBER;
3836
3837
3838 BEGIN
3839 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3840 hz_utility_v2pub.debug(p_message=>'get_matching_contact_points(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3841 END IF;
3842 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3843 hz_utility_v2pub.debug(p_message=>'Input Parameters:',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3847 END IF;
3844 hz_utility_v2pub.debug(p_message=>'p_match_type '||p_match_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3845 hz_utility_v2pub.debug(p_message=>'p_restrict_sql '||p_restrict_sql,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3846 hz_utility_v2pub.debug(p_message=>'p_dup_contact_point_id '||p_dup_contact_point_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3848
3849 -- ************************************
3850 -- STEP 1. Initialization and error checks
3851
3852 l_match_str := ' OR ';
3853 IF p_match_type = 'AND' THEN
3854 l_match_str := ' AND ';
3855 ELSIF p_match_type = 'OR' THEN
3856 l_match_str := ' OR ';
3857 END IF;
3858 SAVEPOINT get_matching_contact_points ;
3859 l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
3860 HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
3861 p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
3862 g_score_until_thresh := false;
3863 IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
3864
3865 l_contact_pt_contains_str := check_cpts_bulk (p_contact_point_list);
3866 init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
3867
3868 -- Setup Search Context ID
3869 SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3870 x_search_ctx_id := l_search_ctx_id;
3871 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3872 hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3873 hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3874 hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3875 END IF;
3876
3877 IF l_contact_pt_contains_str IS NOT NULL THEN
3878 open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL,l_contact_pt_cur);
3879 cnt := 1;
3880 LOOP
3881 FETCH l_contact_pt_cur INTO
3882 l_contact_pt_id, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX5, l_TX158, l_TX10;
3883 EXIT WHEN l_contact_pt_cur%NOTFOUND;
3884 IF (p_dup_contact_point_id IS NULL OR (
3885 p_dup_contact_point_id IS NOT NULL AND
3886 l_cpt_ps_id IS NULL AND l_cpt_contact_id IS NULL AND
3887 p_dup_contact_point_id <> l_contact_pt_id)) THEN
3888 H_CONTACT_POINT_ID(cnt) := l_contact_pt_id;
3889 H_PARTY_ID(cnt) := l_cpt_party_id;
3890 H_SCORE(cnt) := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
3891 cnt := cnt+1;
3892 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3893 hz_utility_v2pub.debug(p_message=>'Contact Point Matches',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3894 hz_utility_v2pub.debug(p_message=>'l_contact_pt_id '||l_contact_pt_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3895 hz_utility_v2pub.debug(p_message=>'l_score '||l_score,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3896 END IF;
3897 END IF;
3898 END LOOP;
3899 CLOSE l_contact_pt_cur;
3900 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3901 hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 33 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3902 END IF;
3903 ROLLBACK to get_matching_contact_points ;
3904 x_num_matches := 0;
3905 FOR I in 1..H_CONTACT_POINT_ID.COUNT LOOP
3906 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3907 hz_utility_v2pub.debug(p_message=>'Match Contact Point ID '||H_CONTACT_POINT_ID(I),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3908 hz_utility_v2pub.debug(p_message=>'Score '||round((H_SCORE(I)/l_entered_max_score)*100),p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3909 END IF;
3910 IF (H_SCORE(I)/l_entered_max_score)>=(175/525) THEN
3911 INSERT INTO HZ_MATCHED_CPTS_GT(SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
3912 l_search_ctx_id,H_CONTACT_POINT_ID(I),H_PARTY_ID(I),round(H_SCORE(I)/l_entered_max_score)*100);
3913 x_num_matches := x_num_matches + 1;
3914 END IF;
3915 END LOOP;
3916 END IF;
3917 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3918 hz_utility_v2pub.debug(p_message=>'get_matching_contact_points(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
3919 END IF;
3920 EXCEPTION
3921 WHEN FND_API.G_EXC_ERROR THEN
3922 ROLLBACK to get_matching_contact_points ;
3923 RAISE FND_API.G_EXC_ERROR;
3924 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3925 ROLLBACK to get_matching_contact_points ;
3926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3927 WHEN OTHERS THEN
3928 ROLLBACK to get_matching_contact_points ;
3929 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
3930 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.get_matching_contact_points');
3934 END get_matching_contact_points;
3931 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
3932 FND_MSG_PUB.ADD;
3933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3935
3936 /**********************************************************
3937 This procedure finds the score details for a specific party that
3938 matched
3939
3940 **********************************************************/
3941
3942 PROCEDURE get_score_details (
3943 p_rule_id IN NUMBER,
3944 p_party_id IN NUMBER,
3945 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3946 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3947 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3948 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3949 x_search_ctx_id IN OUT NUMBER
3950 ) IS
3951
3952 -- Strings to hold the generated Intermedia query strings
3953 l_party_contains_str VARCHAR2(32000);
3954 l_party_site_contains_str VARCHAR2(32000);
3955 l_contact_contains_str VARCHAR2(32000);
3956 l_contact_pt_contains_str VARCHAR2(32000);
3957 l_tmp VARCHAR2(32000);
3958
3959 -- Other local variables
3960 l_match_str VARCHAR2(30); -- Match type (AND or OR)
3961 -- For Score calculation
3962 l_max_score NUMBER;
3963 l_entered_max_score NUMBER;
3964 FIRST BOOLEAN;
3965 l_search_ctx_id NUMBER; -- Generated Search Context ID
3966
3967 l_TX2 VARCHAR2(2000);
3968 l_max_TX2 VARCHAR2(2000);
3969 l_TX26 VARCHAR2(2000);
3970 l_max_TX26 VARCHAR2(2000);
3971 l_TX9 VARCHAR2(2000);
3972 l_max_TX9 VARCHAR2(2000);
3973 l_TX22 VARCHAR2(2000);
3974 l_max_TX22 VARCHAR2(2000);
3975 l_TX158 VARCHAR2(2000);
3976 l_max_TX158 VARCHAR2(2000);
3977 l_TX5 VARCHAR2(2000);
3978 l_max_TX5 VARCHAR2(2000);
3979 l_TX10 VARCHAR2(2000);
3980 l_max_TX10 VARCHAR2(2000);
3981 l_TX59 VARCHAR2(2000);
3982 l_max_TX59 VARCHAR2(2000);
3983 l_TX45 VARCHAR2(2000);
3984 l_max_TX45 VARCHAR2(2000);
3985 l_TX14 VARCHAR2(2000);
3986 l_max_TX14 VARCHAR2(2000);
3987 H_SCORES HZ_PARTY_SEARCH.score_list;
3988
3989 l_score NUMBER;
3990 l_match_idx NUMBER;
3991 l_idx NUMBER;
3992 l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3993 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
3994 l_contact_cur HZ_PARTY_STAGE.StageCurTyp;
3995 l_contact_pt_cur HZ_PARTY_STAGE.StageCurTyp;
3996 l_party_id NUMBER;
3997 l_ps_party_id NUMBER;
3998 l_ct_party_id NUMBER;
3999 l_cpt_party_id NUMBER;
4000 l_cpt_ps_id NUMBER;
4001 l_cpt_contact_id NUMBER;
4002 l_party_site_id NUMBER;
4003 l_org_contact_id NUMBER;
4004 l_contact_pt_id NUMBER;
4005 l_ps_contact_id NUMBER;
4006 l_max_id NUMBER;
4007 l_max_idx NUMBER;
4008
4009 l_index NUMBER;
4010 l_party_max_score NUMBER;
4011 l_ps_max_score NUMBER;
4012 l_contact_max_score NUMBER;
4013 l_cpt_max_score NUMBER;
4014
4015
4016 BEGIN
4017
4018 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4019 hz_utility_v2pub.debug(p_message=>'get_score_details(+) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4020 END IF;
4021 -- ************************************
4022 -- STEP 1. Initialization and error checks
4023
4024 l_entered_max_score:= init_search(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list,' OR ', l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4025 g_score_until_thresh := false;
4026 IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4027 l_party_site_contains_str := check_party_sites_bulk (p_party_site_list);
4028 l_contact_contains_str := check_contacts_bulk (p_contact_list);
4029 l_contact_pt_contains_str := check_cpts_bulk (p_contact_point_list);
4030 init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4031
4032 -- Setup Search Context ID
4033 IF x_search_ctx_id IS NULL THEN
4034 SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4035 x_search_ctx_id := l_search_ctx_id;
4036 ELSE
4037 l_search_ctx_id := x_search_ctx_id;
4038 END IF;
4039
4040 open_party_cursor(p_party_id, null, null,null,null,null,l_party_cur);
4041 LOOP
4042 FETCH l_party_cur INTO
4043 l_party_id , l_TX2, l_TX59, l_TX45;
4044 EXIT WHEN l_party_cur%NOTFOUND;
4045 INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX59,l_TX45,1);
4046 END LOOP;
4047 CLOSE l_party_cur;
4048
4049 IF l_party_site_contains_str IS NOT NULL THEN
4050 l_max_score := 0;
4051 l_max_id := 0;
4052 l_max_idx := 0;
4053 open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,l_party_site_cur);
4054 LOOP
4055 FETCH l_party_site_cur INTO
4056 l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX26, l_TX9, l_TX14, l_TX22;
4057 EXIT WHEN l_party_site_cur%NOTFOUND;
4058 l_score := GET_PARTY_SITES_SCORE(l_match_idx,l_TX26,l_TX9,l_TX14,l_TX22);
4059 IF l_score > l_max_score THEN
4060 l_max_score := l_score;
4061 l_max_id := l_party_site_id;
4062 l_max_idx := l_match_idx;
4063 l_max_TX26 := l_TX26;
4064 l_max_TX9 := l_TX9;
4065 l_max_TX22 := l_TX22;
4066 l_max_TX14 := l_TX14;
4067 END IF;
4068 END LOOP;
4069 CLOSE l_party_site_cur;
4070 IF l_max_score>0 THEN
4074
4071 INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX26,l_max_TX9,l_max_TX14,l_max_TX22,l_max_idx);
4072 END IF;
4073 END IF;
4075 IF l_contact_contains_str IS NOT NULL THEN
4076 l_max_score := 0;
4077 l_max_id := 0;
4078 l_max_idx := 0;
4079 open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL,l_contact_cur);
4080 LOOP
4081 FETCH l_contact_cur INTO
4082 l_org_contact_id, l_ct_party_id ;
4083 EXIT WHEN l_contact_cur%NOTFOUND;
4084 l_score := GET_CONTACTS_SCORE(l_match_idx);
4085 IF l_score > l_max_score THEN
4086 l_max_score := l_score;
4087 l_max_id := l_org_contact_id;
4088 l_max_idx := l_match_idx;
4089 END IF;
4090 END LOOP;
4091 CLOSE l_contact_cur;
4092 IF l_max_score>0 THEN
4093 INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
4094 END IF;
4095 END IF;
4096
4097 IF l_contact_pt_contains_str IS NOT NULL THEN
4098 l_max_score := 0;
4099 l_max_id := 0;
4100 l_max_idx := 0;
4101 open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,l_contact_pt_cur);
4102 LOOP
4103 FETCH l_contact_pt_cur INTO
4104 l_contact_pt_id, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX5, l_TX158, l_TX10;
4105 EXIT WHEN l_contact_pt_cur%NOTFOUND;
4106 l_score := GET_CONTACT_POINTS_SCORE(l_match_idx,l_TX5,l_TX158,l_TX10);
4107 IF l_score > l_max_score THEN
4108 l_max_score := l_score;
4109 l_max_id := l_contact_pt_id;
4110 l_max_idx := l_match_idx;
4111 l_max_TX158 := l_TX158;
4112 l_max_TX5 := l_TX5;
4113 l_max_TX10 := l_TX10;
4114 END IF;
4115 END LOOP;
4116 IF l_max_score>0 THEN
4117 INSERT_CONTACT_POINTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_point_list(l_max_idx), g_contact_pt_stage_list(l_max_idx) ,l_max_TX5,l_max_TX158,l_max_TX10,l_max_idx);
4118 END IF;
4119 CLOSE l_contact_pt_cur;
4120 END IF;
4121 --------------- DELETE FROM ALL SRCH TABLES ---------------------
4122 DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4123 DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4124 DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4125 DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4126 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4127 hz_utility_v2pub.debug(p_message=>'get_score_details(-) ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
4128 END IF;
4129 EXCEPTION
4130 WHEN FND_API.G_EXC_ERROR THEN
4131 --------------- DELETE FROM ALL SRCH TABLES ---------------------
4132 DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4133 DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4134 DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4135 DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4136 RAISE FND_API.G_EXC_ERROR;
4137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4138 --------------- DELETE FROM ALL SRCH TABLES ---------------------
4139 DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4140 DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4141 DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4142 DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4144 WHEN OTHERS THEN
4145 --------------- DELETE FROM ALL SRCH TABLES ---------------------
4146 DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;
4147 DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;
4148 DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;
4149 DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;
4150 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
4151 FND_MESSAGE.SET_TOKEN('PROC','HZ_MATCH_RULE_51.get_score_details');
4152 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4153 FND_MSG_PUB.ADD;
4154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4155 END get_score_details;
4156
4157
4158 /************************************************
4159 This procedure retrieves the match rule attributes into
4160 the search record structures
4161 ************************************************/
4162
4163 PROCEDURE get_party_for_search (
4164 p_party_id IN NUMBER,
4165 x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
4166 x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
4167 x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
4168 x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
4169 ) IS
4170 l_party_id NUMBER;
4171 l_party_site_ids HZ_PARTY_SEARCH.IDList;
4172 l_contact_ids HZ_PARTY_SEARCH.IDList;
4173 l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
4174 ps NUMBER :=1;
4175 cpt NUMBER :=1;
4176 ct NUMBER :=1;
4177 l_use_contact_info varchar2(1);
4178 BEGIN
4179
4180 l_use_contact_info := 'Y';
4181 IF nvl(FND_PROFILE.VALUE('HZ_DQM_REL_PARTY_MATCH'),'N')='Y' THEN
4182 l_use_contact_info := 'N';
4183 END IF;
4184 l_party_id := p_party_id;
4185 FOR PARTY_SITES IN (
4186 SELECT party_site_id FROM (
4187 SELECT party_site_id,identifying_address_flag
4188 FROM HZ_PARTY_SITES
4189 WHERE party_id = p_party_id
4190 AND (status is null OR status = 'A')
4191 AND identifying_address_flag='Y'
4192 UNION
4193
4194 SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
4195 FROM HZ_PARTY_SITES
4196 WHERE party_id = p_party_id
4200
4197 AND (status is null OR status = 'A')
4198 AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
4199 UNION
4201 SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
4202 FROM HZ_PARTY_SITES
4203 WHERE (status is null OR status = 'A')
4204 AND party_id in (
4205 SELECT party_id
4206 FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
4207 WHERE HZ_RELATIONSHIPS.object_id = p_party_id
4208 AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4209 AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4210 AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
4211 and l_use_contact_info = 'Y'
4212 )
4213 ) order by identifying_address_flag desc
4214 ) LOOP
4215 l_party_site_ids(ps) := PARTY_SITES.party_site_id;
4216 ps:=ps+1;
4217 END LOOP;
4218 FOR CONTACT_POINTS IN (
4219 SELECT CONTACT_POINT_ID
4220 FROM HZ_CONTACT_POINTS
4221 WHERE PRIMARY_FLAG = 'Y'
4222 AND owner_table_name = 'HZ_PARTIES'
4223 AND owner_table_id = p_party_id
4224
4225 UNION
4226
4227 SELECT CONTACT_POINT_ID
4228 FROM HZ_CONTACT_POINTS,HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
4229 WHERE PRIMARY_FLAG = 'Y'
4230 AND owner_table_name = 'HZ_PARTIES'
4231 AND OWNER_TABLE_ID = HZ_RELATIONSHIPS.party_id
4232 AND HZ_RELATIONSHIPS.object_id = p_party_id
4233 AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4234 AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4235 AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
4236 and l_use_contact_info = 'Y'
4237 UNION
4238
4239 SELECT CONTACT_POINT_ID
4240 FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
4241 WHERE PRIMARY_FLAG = 'Y'
4242 AND owner_table_name = 'HZ_PARTY_SITES'
4243 AND owner_table_id = party_site_id
4244 AND PARTY_ID = p_party_id
4245 AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
4246 l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;
4247 cpt := cpt+1;
4248 END LOOP;
4249 get_search_criteria(l_party_id,l_party_site_ids,l_contact_ids,l_contact_pt_ids,
4250 x_party_search_rec,x_party_site_list,x_contact_list,x_contact_point_list);
4251 EXCEPTION
4252 WHEN FND_API.G_EXC_ERROR THEN
4253 RAISE FND_API.G_EXC_ERROR;
4254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4256 WHEN OTHERS THEN
4257 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
4258 FND_MESSAGE.SET_TOKEN('PROC','get_party_for_search');
4259 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4260 FND_MSG_PUB.ADD;
4261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4262 END get_party_for_search;
4263
4264 PROCEDURE get_search_criteria (
4265 p_party_id IN NUMBER,
4266 p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
4267 p_contact_ids IN HZ_PARTY_SEARCH.IDList,
4268 p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
4269 x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
4270 x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
4271 x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
4272 x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
4273 ) IS
4274 BEGIN
4275
4276 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4277 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - get_search_criteria',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4278 END IF;
4279 IF p_party_id IS NOT NULL THEN
4280 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4281 hz_utility_v2pub.debug(p_message=>'Before Calling Procedure - get_party_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4282 END IF;
4283 get_party_rec(p_party_id, x_party_search_rec);
4284 END IF;
4285 IF p_party_site_ids IS NOT NULL AND p_party_site_ids.COUNT>0 THEN
4286 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4287 hz_utility_v2pub.debug(p_message=>'Before Calling Procedure - get_party_site_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4288 END IF;
4289 get_party_site_rec(p_party_site_ids, x_party_site_list);
4290 END IF;
4291 IF p_contact_pt_ids IS NOT NULL AND p_contact_pt_ids.COUNT>0 THEN
4292 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4293 hz_utility_v2pub.debug(p_message=>'Before Calling Procedure - get_contact_point_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4294 END IF;
4295 get_contact_point_rec(p_contact_pt_ids, x_contact_point_list);
4296 END IF;
4297 EXCEPTION
4298 WHEN FND_API.G_EXC_ERROR THEN
4299 RAISE FND_API.G_EXC_ERROR;
4300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4302 WHEN OTHERS THEN
4303 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
4304 FND_MESSAGE.SET_TOKEN('PROC','get_search_criteria');
4305 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4306 FND_MSG_PUB.ADD;
4307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4308 END get_search_criteria;
4309
4310
4311 /************************************************
4312 This procedure retrieves the match rule party attributes into
4313 the party search record structure
4314 ************************************************/
4315
4316 PROCEDURE get_party_rec (
4320 l_party_type VARCHAR2(255);
4317 p_party_id IN NUMBER,
4318 x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
4319 ) IS
4321 BEGIN
4322
4323 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4324 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - get_party_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4325 END IF;
4326 SELECT PARTY_TYPE INTO l_party_type
4327 FROM HZ_PARTIES
4328 WHERE PARTY_ID = p_party_id;
4329
4330 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4331 hz_utility_v2pub.debug(p_message=>'l_party_type is - '||l_party_type,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4332 END IF;
4333 IF l_party_type = 'ORGANIZATION' THEN
4334 SELECT
4335 translate(HZ_ORGANIZATION_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
4336 ,translate(HZ_PARTIES.PARTY_NAME, '%',' ')
4337 ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
4338 INTO
4339 x_party_search_rec.JGZZ_FISCAL_CODE
4340 ,x_party_search_rec.PARTY_NAME
4341 ,x_party_search_rec.PARTY_TYPE
4342 FROM HZ_PARTIES, HZ_ORGANIZATION_PROFILES
4343 WHERE HZ_PARTIES.party_id = HZ_ORGANIZATION_PROFILES.party_id
4344 AND HZ_ORGANIZATION_PROFILES.effective_end_date is NULL
4345 AND HZ_PARTIES.party_id = p_party_id;
4346 ELSIF l_party_type = 'PERSON' THEN
4347 SELECT
4348 translate(HZ_PERSON_PROFILES.JGZZ_FISCAL_CODE, '%',' ')
4349 ,translate(HZ_PARTIES.PARTY_NAME, '%',' ')
4350 ,translate(HZ_PARTIES.PARTY_TYPE, '%',' ')
4351 INTO
4352 x_party_search_rec.JGZZ_FISCAL_CODE
4353 ,x_party_search_rec.PARTY_NAME
4354 ,x_party_search_rec.PARTY_TYPE
4355 FROM HZ_PARTIES, HZ_PERSON_PROFILES
4356 WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
4357 AND HZ_PERSON_PROFILES.effective_end_date is NULL
4358 AND HZ_PARTIES.party_id = p_party_id;
4359 END IF;
4360 x_party_search_rec.PARTY_TYPE := l_party_type;
4361 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4362 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - generate_custom_code',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4363 END IF;
4364
4365 EXCEPTION
4366 WHEN OTHERS THEN
4367 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_QUERY_ERROR');
4368 FND_MESSAGE.SET_TOKEN('PROC','get_party_rec');
4369 FND_MESSAGE.SET_TOKEN('PARTY_ID',p_party_id);
4370 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4371 FND_MSG_PUB.ADD;
4372 RAISE FND_API.G_EXC_ERROR;
4373 END get_party_rec;
4374
4375 /************************************************
4376 This procedure retrieves the match rule party site attributes into
4377 the party site search record structure
4378 ************************************************/
4379
4380 PROCEDURE get_party_site_rec (
4381 p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
4382 x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
4383 ) IS
4384 CURSOR c_party_sites(cp_party_site_id NUMBER) IS
4385 SELECT party_site_id
4386 ,translate(HZ_LOCATIONS.CITY, '%',' ')
4387 ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
4388 ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
4389 ,translate(HZ_LOCATIONS.STATE, '%',' ')
4390 FROM HZ_PARTY_SITES, HZ_LOCATIONS
4391 WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
4392 AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
4393
4394 I NUMBER;
4395 J NUMBER:=1;
4396 l_party_site_id NUMBER;
4397
4398 BEGIN
4399
4400 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4401 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - get_party_site_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4402 END IF;
4403 FOR I IN 1..p_party_site_ids.COUNT LOOP
4404 l_party_site_id := p_party_site_ids(I);
4405 OPEN c_party_sites(p_party_site_ids(I));
4406 LOOP
4407 FETCH c_party_sites INTO
4408 l_party_site_id
4409 ,x_party_site_list(J).CITY
4410 ,x_party_site_list(J).COUNTRY
4411 ,x_party_site_list(J).POSTAL_CODE
4412 ,x_party_site_list(J).STATE
4413 ;
4414 EXIT WHEN c_party_sites%NOTFOUND;
4415
4416 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4417 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - generate_custom_code',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4418 END IF;
4419 x_party_site_list(J).ADDRESS:=
4420 HZ_PARTY_ACQUIRE.get_address(l_party_site_id , 'PARTY_SITES','ADDRESS');
4421 J:=J+1;
4422
4423 END LOOP;
4424 CLOSE c_party_sites;
4425 END LOOP;
4426
4427 EXCEPTION
4428 WHEN OTHERS THEN
4429 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_QUERY_ERROR');
4430 FND_MESSAGE.SET_TOKEN('PROC','get_party_site_rec');
4431 FND_MESSAGE.SET_TOKEN('PARTY_ID',l_party_site_id);
4432 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4433 FND_MSG_PUB.ADD;
4434 RAISE FND_API.G_EXC_ERROR;
4435 END get_party_site_rec;
4436
4437 /************************************************
4438 This procedure retrieves the match rule contact attributes into
4439 the contact search record structure
4440 ************************************************/
4441
4442 PROCEDURE get_contact_rec (
4446 CURSOR c_contacts(cp_org_contact_id NUMBER) IS
4443 p_contact_ids IN HZ_PARTY_SEARCH.IDList,
4444 x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
4445 ) IS
4447 SELECT org_contact_id
4448 FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
4449 WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
4450 AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4451 AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4452 AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
4453 AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
4454 AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
4455 AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
4456
4457 I NUMBER;
4458 l_org_contact_id NUMBER;
4459 J NUMBER:=1;
4460 BEGIN
4461
4462 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4463 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - get_contact_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4464 END IF;
4465 FOR I IN 1..p_contact_ids.COUNT LOOP
4466 l_org_contact_id := p_contact_ids(I);
4467 OPEN c_contacts(p_contact_ids(I));
4468 LOOP
4469 FETCH c_contacts INTO
4470 l_org_contact_id
4471 ;
4472 EXIT WHEN c_contacts%NOTFOUND;
4473
4474 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4475 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - generate_custom_code',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4476 END IF;
4477
4478 J:=J+1;
4479 END LOOP;
4480 CLOSE c_contacts;
4481 END LOOP;
4482
4483 EXCEPTION
4484 WHEN OTHERS THEN
4485 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_QUERY_ERROR');
4486 FND_MESSAGE.SET_TOKEN('PROC','get_contact_rec');
4487 FND_MESSAGE.SET_TOKEN('PARTY_ID',l_org_contact_id);
4488 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4489 FND_MSG_PUB.ADD;
4490 RAISE FND_API.G_EXC_ERROR;
4491 END get_contact_rec;
4492
4493 /************************************************
4494 This procedure retrieves the match rule contact point attributes into
4495 the contact point search record structure
4496 ************************************************/
4497
4498 PROCEDURE get_contact_point_rec (
4499 p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
4500 x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
4501 ) IS
4502
4503 -- Cursor to fetch primary contact points for party
4504 CURSOR c_cpts(cp_contact_point_id NUMBER) IS
4505 SELECT contact_point_id, contact_point_type
4506 ,translate(HZ_CONTACT_POINTS.EMAIL_ADDRESS, '%',' ')
4507 FROM HZ_CONTACT_POINTS
4508 WHERE contact_point_id = cp_contact_point_id;
4509
4510 I NUMBER;
4511 l_contact_point_id NUMBER;
4512 J NUMBER:=1;
4513 BEGIN
4514
4515 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4516 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - get_contact_point_rec',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4517 END IF;
4518 FOR I in 1..p_contact_point_ids.COUNT LOOP
4519 l_contact_point_id := p_contact_point_ids(I);
4520 OPEN c_cpts(p_contact_point_ids(I));
4521 LOOP
4522 FETCH c_cpts INTO
4523 l_contact_point_id, x_contact_point_list(J).contact_point_type
4524 ,x_contact_point_list(J).EMAIL_ADDRESS
4525 ;
4526 EXIT WHEN c_cpts%NOTFOUND;
4527
4528 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4529 hz_utility_v2pub.debug(p_message=>'Inside calling procedure - generate_custom_code',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4530 END IF;
4531 x_contact_point_list(J).RAW_PHONE_NUMBER:=
4532 HZ_PARTY_ACQUIRE.get_phone_number(l_contact_point_id , 'CONTACT_POINTS','RAW_PHONE_NUMBER');
4533 J:=J+1;
4534
4535 END LOOP;
4536 CLOSE c_cpts;
4537 END LOOP;
4538
4539 EXCEPTION
4540 WHEN OTHERS THEN
4541 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_QUERY_ERROR');
4542 FND_MESSAGE.SET_TOKEN('PROC','get_contact_point_rec');
4543 FND_MESSAGE.SET_TOKEN('PARTY_ID',l_contact_point_id);
4544 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
4545 FND_MSG_PUB.ADD;
4546 RAISE FND_API.G_EXC_ERROR;
4547 END get_contact_point_rec;
4548
4549 /************************************************
4550 This procedure maps a search record from the logical
4551 record structure to the stage schema structure
4552 for the PARTY Entity after applying
4553 the defined transformations
4554 ************************************************/
4555
4556 PROCEDURE MAP_PARTY_REC(
4557 p_search_ctx IN BOOLEAN,
4558 p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4559 x_entered_max_score OUT NUMBER,
4560 x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
4561 ) IS
4562 tmp VARCHAR2(4000);
4563 BEGIN
4564 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4565 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - MAP_PARTY_REC',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4566 END IF;
4567 IF p_search_ctx THEN
4568 x_entered_max_score:=0;
4569 IF p_search_rec.PARTY_NAME IS NOT NULL THEN
4570 x_entered_max_score := x_entered_max_score+80;
4571 END IF;
4575 END IF;
4572 IF p_search_rec.JGZZ_FISCAL_CODE IS NOT NULL THEN
4573 x_entered_max_score := x_entered_max_score+200;
4574 END IF;
4576 IF p_search_ctx THEN
4577 IF p_search_rec.JGZZ_FISCAL_CODE IS NOT NULL THEN
4578 x_stage_rec.TX45 :=
4579 HZ_TRANS_PKG.RM_SPLCHAR(
4580 p_search_rec.JGZZ_FISCAL_CODE
4581 ,null,'JGZZ_FISCAL_CODE'
4582 ,'PARTY','SEARCH');
4583 ELSE
4584 x_stage_rec.TX45 := '';
4585 END IF;
4586 ELSE
4587 IF p_search_rec.JGZZ_FISCAL_CODE IS NOT NULL THEN
4588 tmp :=HZ_TRANS_PKG.RM_SPLCHAR(
4589 x_stage_rec.TX45
4590 ,null,'JGZZ_FISCAL_CODE'
4591 ,'PARTY','SCORE');
4592 IF tmp IS NOT NULL THEN
4593 x_stage_rec.TX45 := tmp;
4594 END IF;
4595 END IF;
4596 END IF;
4597 IF p_search_ctx THEN
4598 IF p_search_rec.PARTY_NAME IS NOT NULL THEN
4599 x_stage_rec.TX59 :=
4600 HZ_TRANS_PKG.BASIC_WRNAMES(
4601 p_search_rec.PARTY_NAME
4602 ,null,'PARTY_NAME'
4603 ,'PARTY','SEARCH');
4604 ELSE
4605 x_stage_rec.TX59 := '';
4606 END IF;
4607 ELSE
4608 IF p_search_rec.PARTY_NAME IS NOT NULL THEN
4609 tmp :=HZ_TRANS_PKG.BASIC_WRNAMES(
4610 x_stage_rec.TX59
4611 ,null,'PARTY_NAME'
4612 ,'PARTY','SCORE');
4613 IF tmp IS NOT NULL THEN
4614 x_stage_rec.TX59 := tmp;
4615 END IF;
4616 END IF;
4617 END IF;
4618 IF p_search_ctx THEN
4619 IF p_search_rec.PARTY_NAME IS NOT NULL THEN
4620 x_stage_rec.TX2 :=
4621 HZ_TRANS_PKG.EXACT_PADDED(
4622 p_search_rec.PARTY_NAME
4623 ,null,'PARTY_NAME'
4624 ,'PARTY');
4625 ELSE
4626 x_stage_rec.TX2 := '';
4627 END IF;
4628 END IF;
4629 IF p_search_ctx THEN
4630 IF p_search_rec.PARTY_TYPE IS NOT NULL THEN
4631 x_stage_rec.TX36 :=
4632 HZ_TRANS_PKG.EXACT(
4633 p_search_rec.PARTY_TYPE
4634 ,null,'PARTY_TYPE'
4635 ,'PARTY');
4636 ELSE
4637 x_stage_rec.TX36 := '';
4638 END IF;
4639 END IF;
4640 IF p_search_ctx THEN
4641 insert into HZ_SRCH_PARTIES(batch_id,party_id, party_osr,party_os,TX45,TX59,TX2,TX36) values(-1,-1,-1,-1,x_stage_rec.TX45,x_stage_rec.TX59,x_stage_rec.TX2,x_stage_rec.TX36);
4642 END IF;
4643 EXCEPTION
4644 WHEN OTHERS THEN
4645 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
4646 FND_MESSAGE.SET_TOKEN('PROC' , 'MAP_PARTY_REC');
4647 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4648 FND_MSG_PUB.ADD;
4649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4650 END;
4651
4652
4653 /************************************************
4654 This procedure maps a search record from the logical
4655 record structure to the stage schema structure
4656 for the PARTY_SITES Entity after applying
4657 the defined transformations
4658 ************************************************/
4659
4660 PROCEDURE MAP_PARTY_SITE_REC(
4661 p_search_ctx IN BOOLEAN,
4662 p_search_list IN HZ_PARTY_SEARCH.party_site_list,
4663 x_entered_max_score OUT NUMBER,
4664 x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
4665 ) IS
4666 l_current_max_score NUMBER;
4667 tmp VARCHAR2(4000);
4668 BEGIN
4669 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4670 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - MAP_PARTY_SITE_REC',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4671 END IF;
4672 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4673 hz_utility_v2pub.debug(p_message=>'p_entity - PARTY_SITES',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4674 END IF;
4675 IF p_search_ctx THEN
4676 x_entered_max_score:=0;
4677 FOR I IN 1..p_search_list.COUNT LOOP
4678 l_current_max_score:=0;
4679 IF p_search_list(I).ADDRESS IS NOT NULL THEN
4680 l_current_max_score := l_current_max_score+100;
4681 END IF;
4682 IF p_search_list(I).CITY IS NOT NULL THEN
4683 l_current_max_score := l_current_max_score+5;
4684 END IF;
4685 IF p_search_list(I).STATE IS NOT NULL THEN
4686 l_current_max_score := l_current_max_score+5;
4687 END IF;
4688 IF p_search_list(I).COUNTRY IS NOT NULL THEN
4689 l_current_max_score := l_current_max_score+5;
4690 END IF;
4691 IF l_current_max_score>x_entered_max_score THEN
4692 x_entered_max_score:=l_current_max_score;
4693 END IF;
4694 END LOOP;
4695 END IF;
4696 FOR I IN 1..p_search_list.COUNT LOOP
4697 IF p_search_ctx THEN
4698 IF p_search_list(I).ADDRESS IS NOT NULL THEN
4699 x_stage_list(I).TX26 :=
4700 HZ_TRANS_PKG.BASIC_WRADDR(
4701 p_search_list(I).ADDRESS
4702 ,null,'ADDRESS'
4703 ,'PARTY_SITES','SEARCH');
4704 ELSE
4705 x_stage_list(I).TX26 := '';
4706 END IF;
4707 ELSE
4708 IF p_search_list(I).ADDRESS IS NOT NULL THEN
4709 tmp := HZ_TRANS_PKG.BASIC_WRADDR(
4710 x_stage_list(I).TX26
4711 ,null,'ADDRESS'
4712 ,'PARTY_SITES','SCORE');
4713 IF tmp IS NOT NULL THEN
4714 x_stage_list(I).TX26 := tmp;
4715 END IF;
4716 END IF;
4720 IF p_search_ctx THEN
4717 END IF;
4718 END LOOP;
4719 FOR I IN 1..p_search_list.COUNT LOOP
4721 IF p_search_list(I).CITY IS NOT NULL THEN
4722 x_stage_list(I).TX9 :=
4723 HZ_TRANS_PKG.EXACT(
4724 p_search_list(I).CITY
4725 ,null,'CITY'
4726 ,'PARTY_SITES');
4727 ELSE
4728 x_stage_list(I).TX9 := '';
4729 END IF;
4730 END IF;
4731 END LOOP;
4732 FOR I IN 1..p_search_list.COUNT LOOP
4733 IF p_search_ctx THEN
4734 IF p_search_list(I).COUNTRY IS NOT NULL THEN
4735 x_stage_list(I).TX22 :=
4736 HZ_TRANS_PKG.EXACT(
4737 p_search_list(I).COUNTRY
4738 ,null,'COUNTRY'
4739 ,'PARTY_SITES');
4740 ELSE
4741 x_stage_list(I).TX22 := '';
4742 END IF;
4743 END IF;
4744 END LOOP;
4745 FOR I IN 1..p_search_list.COUNT LOOP
4746 IF p_search_ctx THEN
4747 IF p_search_list(I).POSTAL_CODE IS NOT NULL THEN
4748 x_stage_list(I).TX11 :=
4749 HZ_TRANS_PKG.RM_SPLCHAR(
4750 p_search_list(I).POSTAL_CODE
4751 ,null,'POSTAL_CODE'
4752 ,'PARTY_SITES','SEARCH');
4753 ELSE
4754 x_stage_list(I).TX11 := '';
4755 END IF;
4756 ELSE
4757 IF p_search_list(I).POSTAL_CODE IS NOT NULL THEN
4758 tmp := HZ_TRANS_PKG.RM_SPLCHAR(
4759 x_stage_list(I).TX11
4760 ,null,'POSTAL_CODE'
4761 ,'PARTY_SITES','SCORE');
4762 IF tmp IS NOT NULL THEN
4763 x_stage_list(I).TX11 := tmp;
4764 END IF;
4765 END IF;
4766 END IF;
4767 END LOOP;
4768 FOR I IN 1..p_search_list.COUNT LOOP
4769 IF p_search_ctx THEN
4770 IF p_search_list(I).STATE IS NOT NULL THEN
4771 x_stage_list(I).TX14 :=
4772 HZ_TRANS_PKG.WRSTATE_EXACT(
4773 p_search_list(I).STATE
4774 ,null,'STATE'
4775 ,'PARTY_SITES','SEARCH');
4776 ELSE
4777 x_stage_list(I).TX14 := '';
4778 END IF;
4779 ELSE
4780 IF p_search_list(I).STATE IS NOT NULL THEN
4781 tmp := HZ_TRANS_PKG.WRSTATE_EXACT(
4782 x_stage_list(I).TX14
4783 ,null,'STATE'
4784 ,'PARTY_SITES','SCORE');
4785 IF tmp IS NOT NULL THEN
4786 x_stage_list(I).TX14 := tmp;
4787 END IF;
4788 END IF;
4789 END IF;
4790 END LOOP;
4791 IF p_search_ctx THEN
4792 FOR I IN 1..p_search_list.COUNT LOOP
4793 insert into HZ_SRCH_PSITES(batch_id,party_id, party_osr,party_os, party_site_id,party_site_osr, party_site_os,new_party_flag ,TX26,TX9,TX22,TX11,TX14) values(-1,-1,-1,-1,-1,-1,-1,'Y',x_stage_list(I).TX26,x_stage_list(I).TX9,x_stage_list(I).TX22,x_stage_list(I).TX11,x_stage_list(I).TX14);
4794 END LOOP;
4795 END IF;
4796 EXCEPTION
4797 WHEN OTHERS THEN
4798 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
4799 FND_MESSAGE.SET_TOKEN('PROC' , 'MAP_PARTY_SITE_REC');
4800 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4801 FND_MSG_PUB.ADD;
4802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4803 END;
4804
4805
4806 /************************************************
4807 This procedure maps a search record from the logical
4808 record structure to the stage schema structure
4809 for the CONTACTS Entity after applying
4810 the defined transformations
4811 ************************************************/
4812
4813 PROCEDURE MAP_CONTACT_REC(
4814 p_search_ctx IN BOOLEAN,
4815 p_search_list IN HZ_PARTY_SEARCH.contact_list,
4816 x_entered_max_score OUT NUMBER,
4817 x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
4818 ) IS
4819 l_current_max_score NUMBER;
4820 tmp VARCHAR2(4000);
4821 BEGIN
4822 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4823 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - MAP_CONTACT_REC',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4824 END IF;
4825 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4826 hz_utility_v2pub.debug(p_message=>'p_entity - CONTACTS',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4827 END IF;
4828 IF p_search_ctx THEN
4829 x_entered_max_score:=0;
4830 FOR I IN 1..p_search_list.COUNT LOOP
4831 l_current_max_score:=0;
4832 IF l_current_max_score>x_entered_max_score THEN
4833 x_entered_max_score:=l_current_max_score;
4834 END IF;
4835 END LOOP;
4836 END IF;
4837 NULL;
4838 EXCEPTION
4839 WHEN OTHERS THEN
4840 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
4841 FND_MESSAGE.SET_TOKEN('PROC' , 'MAP_CONTACT_REC');
4842 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4843 FND_MSG_PUB.ADD;
4844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4845 END;
4846
4847
4848 /************************************************
4849 This procedure maps a search record from the logical
4850 record structure to the stage schema structure
4851 for the CONTACT_POINTS Entity after applying
4852 the defined transformations
4853 ************************************************/
4854
4855 PROCEDURE MAP_CONTACT_POINT_REC(
4856 p_search_ctx IN BOOLEAN,
4857 p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
4858 x_entered_max_score OUT NUMBER,
4859 x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
4860 ) IS
4861 l_current_max_score NUMBER;
4862 tmp VARCHAR2(4000);
4863 BEGIN
4864 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4868 hz_utility_v2pub.debug(p_message=>'p_entity - CONTACT_POINTS',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4865 hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - MAP_CONTACT_POINT_REC',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4866 END IF;
4867 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4869 END IF;
4870 IF p_search_ctx THEN
4871 x_entered_max_score:=0;
4872 FOR I IN 1..p_search_list.COUNT LOOP
4873 l_current_max_score:=0;
4874 x_stage_list(I).CONTACT_POINT_TYPE := p_search_list(I).CONTACT_POINT_TYPE;
4875 IF p_search_list(I).EMAIL_ADDRESS IS NOT NULL THEN
4876 l_current_max_score := l_current_max_score+60;
4877 END IF;
4878 IF p_search_list(I).RAW_PHONE_NUMBER IS NOT NULL THEN
4879 l_current_max_score := l_current_max_score+70;
4880 END IF;
4881 IF l_current_max_score>x_entered_max_score THEN
4882 x_entered_max_score:=l_current_max_score;
4883 END IF;
4884 END LOOP;
4885 END IF;
4886 FOR I IN 1..p_search_list.COUNT LOOP
4887 IF p_search_ctx THEN
4888 IF p_search_list(I).EMAIL_ADDRESS IS NOT NULL THEN
4889 x_stage_list(I).TX5 :=
4890 HZ_TRANS_PKG.EXACT_EMAIL(
4891 p_search_list(I).EMAIL_ADDRESS
4892 ,null,'EMAIL_ADDRESS'
4893 ,'CONTACT_POINTS');
4894 ELSE
4895 x_stage_list(I).TX5 := '';
4896 END IF;
4897 END IF;
4898 END LOOP;
4899 FOR I IN 1..p_search_list.COUNT LOOP
4900 IF p_search_ctx THEN
4901 IF p_search_list(I).RAW_PHONE_NUMBER IS NOT NULL THEN
4902 x_stage_list(I).TX158 :=
4903 HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(
4904 p_search_list(I).RAW_PHONE_NUMBER
4905 ,null,'RAW_PHONE_NUMBER'
4906 ,'CONTACT_POINTS');
4907 ELSE
4908 x_stage_list(I).TX158 := '';
4909 END IF;
4910 END IF;
4911 END LOOP;
4912 FOR I IN 1..p_search_list.COUNT LOOP
4913 IF p_search_ctx THEN
4914 IF p_search_list(I).RAW_PHONE_NUMBER IS NOT NULL THEN
4915 x_stage_list(I).TX10 :=
4916 HZ_TRANS_PKG.RM_SPLCHAR(
4917 p_search_list(I).RAW_PHONE_NUMBER
4918 ,null,'RAW_PHONE_NUMBER'
4919 ,'CONTACT_POINTS','SEARCH');
4920 ELSE
4921 x_stage_list(I).TX10 := '';
4922 END IF;
4923 ELSE
4924 IF p_search_list(I).RAW_PHONE_NUMBER IS NOT NULL THEN
4925 tmp := HZ_TRANS_PKG.RM_SPLCHAR(
4926 x_stage_list(I).TX10
4927 ,null,'RAW_PHONE_NUMBER'
4928 ,'CONTACT_POINTS','SCORE');
4929 IF tmp IS NOT NULL THEN
4930 x_stage_list(I).TX10 := tmp;
4931 END IF;
4932 END IF;
4933 END IF;
4934 END LOOP;
4935 IF p_search_ctx THEN
4936 FOR I IN 1..p_search_list.COUNT LOOP
4937 insert into HZ_SRCH_CPTS(batch_id,party_id, party_osr,party_os, contact_point_id,contact_pt_osr, contact_pt_os,contact_point_type,new_party_flag ,TX5,TX158,TX10) values(-1,-1,-1,-1,-1,-1,-1,-1,'Y',x_stage_list(I).TX5,x_stage_list(I).TX158,x_stage_list(I).TX10);
4938 END LOOP;
4939 END IF;
4940 EXCEPTION
4941 WHEN OTHERS THEN
4942 FND_MESSAGE.SET_NAME('AR', 'HZ_TRANSFORM_PROC_ERROR');
4943 FND_MESSAGE.SET_TOKEN('PROC' , 'MAP_CONTACT_POINT_REC');
4944 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4945 FND_MSG_PUB.ADD;
4946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4947 END;
4948
4949
4950 /************************************************
4951 This procedure checks if the input search criteria
4952 is valid. It checks if :
4953 1. At least one primary condition is passed
4954 2. Contact Point Type is not null for each condition
4955 ************************************************/
4956
4957 FUNCTION check_prim_cond(
4958 p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4959 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4960 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4961 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
4962 RETURN BOOLEAN IS
4963 BEGIN
4964 FOR I IN 1..p_contact_point_list.COUNT LOOP
4965 IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
4966 p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
4967 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
4968 ) THEN
4969 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_CONTACT_POINT_TYPE');
4970 FND_MSG_PUB.ADD;
4971 RAISE FND_API.G_EXC_ERROR;
4972 END IF;
4973 END LOOP;
4974
4975 IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
4976 RETURN TRUE;
4977 END IF;
4978 IF p_party_search_rec.JGZZ_FISCAL_CODE IS NOT NULL THEN
4979 RETURN TRUE;
4980 END IF;
4981 FOR I IN 1..p_party_site_list.COUNT LOOP
4982 IF p_party_site_list(I).ADDRESS IS NOT NULL THEN
4983 RETURN TRUE;
4984 END IF;
4985 END LOOP;
4986 FOR I IN 1..p_contact_point_list.COUNT LOOP
4987 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL THEN
4988 RETURN TRUE;
4989 END IF;
4990 END LOOP;
4991 FOR I IN 1..p_contact_point_list.COUNT LOOP
4992 IF p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL THEN
4993 RETURN TRUE;
4994 END IF;
4995 END LOOP;
4996 RETURN FALSE;
4997 EXCEPTION
4998 WHEN FND_API.G_EXC_ERROR THEN
4999 RAISE FND_API.G_EXC_ERROR;
5000 WHEN OTHERS THEN
5001 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
5002 FND_MESSAGE.SET_TOKEN('PROC','check_prim_cond');
5003 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
5007
5004 FND_MSG_PUB.ADD;
5005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5006 END check_prim_cond;
5008 /************************************************
5009 This procedure checks if the input search condition
5010 has party site criteria.
5011 ************************************************/
5012
5013 PROCEDURE check_party_site_cond(
5014 p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5015 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5016 x_secondary OUT BOOLEAN,
5017 x_primary OUT BOOLEAN
5018 ) IS
5019 BEGIN
5020 x_primary:= FALSE;
5021 x_secondary:= FALSE;
5022 FOR I IN 1..p_party_site_list.COUNT LOOP
5023 IF p_party_site_list(I).ADDRESS IS NOT NULL
5024 OR p_party_site_list(I).POSTAL_CODE IS NOT NULL
5025 THEN
5026 x_primary := TRUE;
5027 END IF;
5028 EXIT WHEN x_primary;
5029 END LOOP;
5030 FOR I IN 1..p_contact_point_list.COUNT LOOP
5031 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
5032 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
5033 THEN
5034 x_primary := TRUE;
5035 END IF;
5036 EXIT WHEN x_primary;
5037 END LOOP;
5038
5039 FOR I IN 1..p_party_site_list.COUNT LOOP
5040 IF p_party_site_list(I).ADDRESS IS NOT NULL
5041 OR p_party_site_list(I).CITY IS NOT NULL
5042 OR p_party_site_list(I).STATE IS NOT NULL
5043 OR p_party_site_list(I).COUNTRY IS NOT NULL
5044 THEN
5045 x_secondary := TRUE;
5046 END IF;
5047 EXIT WHEN x_secondary;
5048 END LOOP;
5049 FOR I IN 1..p_contact_point_list.COUNT LOOP
5050 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
5051 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
5052 THEN
5053 x_secondary := TRUE;
5054 END IF;
5055 EXIT WHEN x_secondary;
5056 END LOOP;
5057 EXCEPTION
5058 WHEN OTHERS THEN
5059 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
5060 FND_MESSAGE.SET_TOKEN('PROC','check_party_site_cond');
5061 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
5062 FND_MSG_PUB.ADD;
5063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5064 END check_party_site_cond;
5065
5066 /************************************************
5067 This procedure checks if the input search condition
5068 has contact criteria.
5069 ************************************************/
5070
5071 PROCEDURE check_contact_cond(
5072 p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5073 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5074 x_secondary OUT BOOLEAN,
5075 x_primary OUT BOOLEAN
5076 ) IS
5077 BEGIN
5078 x_primary:= FALSE;
5079 x_secondary:= FALSE;
5080 FOR I IN 1..p_contact_list.COUNT LOOP
5081 NULL;
5082 END LOOP;
5083
5084 FOR I IN 1..p_contact_point_list.COUNT LOOP
5085 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
5086 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
5087 THEN
5088 x_primary := TRUE;
5089 END IF;
5090 EXIT WHEN x_primary;
5091 END LOOP;
5092
5093 FOR I IN 1..p_contact_list.COUNT LOOP
5094 NULL;
5095 END LOOP;
5096
5097 FOR I IN 1..p_contact_point_list.COUNT LOOP
5098 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
5099 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
5100 THEN
5101 x_secondary := TRUE;
5102 END IF;
5103 EXIT WHEN x_secondary;
5104 END LOOP;
5105 EXCEPTION
5106 WHEN OTHERS THEN
5107 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
5108 FND_MESSAGE.SET_TOKEN('PROC','check_contact_cond');
5109 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
5110 FND_MSG_PUB.ADD;
5111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5112 END check_contact_cond;
5113
5114 /************************************************
5115 This procedure checks if the input search condition
5116 has valid contact point criteria.
5117 ************************************************/
5118
5119 PROCEDURE check_contact_point_cond(
5120 p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5121 x_secondary OUT BOOLEAN,
5122 x_primary OUT BOOLEAN
5123 ) IS
5124 BEGIN
5125 x_primary:= FALSE;
5126 x_secondary:= FALSE;
5127
5128 FOR I IN 1..p_contact_point_list.COUNT LOOP
5129 IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL THEN
5130 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_CONTACT_POINT_TYPE');
5131 FND_MSG_PUB.ADD;
5132 RAISE FND_API.G_EXC_ERROR;
5133 END IF;
5134 END LOOP;
5135
5136 FOR I IN 1..p_contact_point_list.COUNT LOOP
5137 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
5138 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
5139 THEN
5140 x_primary := TRUE;
5141 END IF;
5142 EXIT WHEN x_primary;
5143 END LOOP;
5144 FOR I IN 1..p_contact_point_list.COUNT LOOP
5145 IF p_contact_point_list(I).EMAIL_ADDRESS IS NOT NULL
5146 OR p_contact_point_list(I).RAW_PHONE_NUMBER IS NOT NULL
5147 THEN
5148 x_secondary := TRUE;
5149 END IF;
5150 EXIT WHEN x_secondary;
5151 END LOOP;
5152 EXCEPTION
5153 WHEN OTHERS THEN
5154 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
5155 FND_MESSAGE.SET_TOKEN('PROC','check_contact_point_cond');
5156 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
5157 FND_MSG_PUB.ADD;
5161 FUNCTION check_staged RETURN BOOLEAN IS
5158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5159 END check_contact_point_cond;
5160
5162
5163 CURSOR c_check_staged IS
5164 SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL
5165 WHERE nvl(STAGED_FLAG,'N') = 'N'
5166 AND FUNCTION_ID in (
5167 6
5168 ,40
5169 ,50
5170 ,72
5171 ,74
5172 ,77
5173 ,85
5174 ,114
5175 ,120
5176 ,128
5177 ,138
5178 ,140
5179 );
5180 l_tmp NUMBER;
5181 BEGIN
5182 IF g_staged = 1 THEN
5183 RETURN TRUE;
5184 ELSIF g_staged = 0 THEN
5185 RETURN FALSE;
5186 END IF;
5187
5188 OPEN c_check_staged;
5189 FETCH c_check_staged INTO l_tmp;
5190 IF c_check_staged%FOUND THEN
5191 CLOSE c_check_staged;
5192 g_staged := 0;
5193 RETURN FALSE;
5194 ELSE
5195 CLOSE c_check_staged;
5196 g_staged := 1;
5197 RETURN TRUE;
5198 END IF;
5199 END check_staged;
5200
5201 -- Fix for Bug 4736139
5202 FUNCTION check_staged_var RETURN VARCHAR2 IS
5203 l_staged VARCHAR2(1);
5204 l_staged_bool BOOLEAN;
5205 BEGIN
5206 l_staged_bool := check_staged;
5207 IF l_staged_bool THEN
5208 l_staged := 'Y';
5209 ELSE
5210 l_staged := 'N';
5211 END IF;
5212 RETURN l_staged;
5213 END check_staged_var;
5214 -- End fix for Bug 4736139
5215
5216 END;
5217
[Home] [Help]