[Home] [Help]
PACKAGE BODY: APPS.HZ_BATCH_DUPLICATE
Source
1 package BODY HZ_BATCH_DUPLICATE AS
2 /*$Header: ARHBDUPB.pls 120.27.12000000.2 2007/05/25 10:04:22 vsegu ship $*/
3
4 -- Private procedure forward declaration
5 FUNCTION check_party_in_dupset (
6 p_batch_id IN NUMBER,
7 p_party_id IN NUMBER,
8 p_dup_set_id IN NUMBER := -1
9 ) RETURN BOOLEAN;
10
11 PROCEDURE remove_non_duplicates (
12 p_cur_party_id IN NUMBER,
13 p_dup_set_id IN NUMBER
14 );
15
16 PROCEDURE insert_match_details (
17 p_search_ctx_id IN NUMBER,
18 p_dup_set_id IN NUMBER,
19 p_dup_party_id IN NUMBER
20 );
21
22 PROCEDURE out(
23 message IN VARCHAR2,
24 newline IN BOOLEAN DEFAULT TRUE);
25
26 PROCEDURE log(
27 message IN VARCHAR2,
28 newline IN BOOLEAN DEFAULT TRUE);
29
30 PROCEDURE outandlog(
31 message IN VARCHAR2,
32 newline IN BOOLEAN DEFAULT TRUE);
33
34 FUNCTION logerror RETURN VARCHAR2;
35
36 g_print_time_taken NUMBER:=5;
37 g_print_num_eval NUMBER:=50;
38
39
40 -- VJN INTRODUCED GLOBAL VARIABLES FOR QUICK DUPLICATE IDENTIFICATION
41 -- These variables which were earlier in find_dup_parties
42 -- are moved here, in order to make sure that the values of the
43 -- fetched concurrent request ids are in scope, when find_dup_parties
44 -- calls itself after the workers are all completed.
45
46 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
47 l_sub_requests nTable;
48
49 PROCEDURE find_dup_parties (
50 errbuf OUT NOCOPY VARCHAR2,
51 retcode OUT NOCOPY VARCHAR2,
52 p_rule_id IN VARCHAR2,
53 p_num_workers IN VARCHAR2,
54 p_batch_name IN VARCHAR2,
55 p_subset_defn IN VARCHAR2,
56 p_match_within_subset IN VARCHAR2,
57 p_search_merged IN VARCHAR2
58 ) IS
59
60 -- start bug 4773387
61
62 cursor am_candidates_exist_csr(cp_dup_batch_id NUMBER) is
63 select count(*)
64 from hz_dup_set_parties dsp, hz_dup_sets ds
65 where dsp.dup_set_id = ds.dup_set_id
66 and ds.dup_batch_id = cp_dup_batch_id
67 and ds.winner_party_id <> dsp.dup_party_id
68 and merge_flag = 'Y'
69 and rownum =1;
70
71 l_am_count NUMBER;
72 -- end bug 4773387
73
74 l_rule_id NUMBER;
75 l_batch_id NUMBER;
76
77 -- VJN INTRODUCED VARIABLES FOR QUICK DUPLICATE IDENTIFICATION
78 match_rule_purpose varchar2(1) ;
79 req_data VARCHAR2(30);
80 l_conc_phase VARCHAR2(80);
81 l_conc_status VARCHAR2(80);
82 l_conc_dev_phase VARCHAR2(30);
83 l_conc_dev_status VARCHAR2(30);
84 l_message VARCHAR2(240);
85 call_status boolean;
86 dup_workers_completed boolean;
87 l_sub FND_CONCURRENT.REQUESTS_TAB_TYPE;
88
89 l_owner VARCHAR2(30);
90 l_automerge_flag VARCHAR2(1);
91 l_req_id NUMBER;
92 temp NUMBER ;
93 batch_count NUMBER;
94 l_batch_name VARCHAR2(360);
95 l_new_batch_id NUMBER;
96 l_request_id NUMBER;
97 l_staged_var VARCHAR2(1);
98
99 CURSOR dup_dup_parties(cp_dup_batch_id NUMBER) IS
100 select dup_party_id
101 from hz_dup_set_parties
102 where dup_set_id in (select dup_set_id from hz_dup_sets where dup_batch_id=cp_dup_batch_id)
103 group by dup_party_id
104 having count(*)>1;
105
106 CURSOR dup_party_sets(cp_dup_batch_id NUMBER, cp_dup_party_id NUMBER) IS
107 select ds.dup_set_id
108 FROM hz_dup_set_parties dsp, hz_dup_sets ds
109 where dsp.dup_set_id=ds.dup_set_id
110 and ds.dup_batch_id=cp_dup_batch_id
111 and dsp.dup_party_id=cp_dup_party_id
112 order by ds.dup_set_id;
113
114
115 l_dup_dup_set NUMBER;
116 l_dup_dup_id NUMBER;
117 l_num_left NUMBER;
118 l_winner_id NUMBER;
119 FIRST BOOLEAN;
120
121 BEGIN
122
123 -- Fix for bug 4736139, to display an error message and error out
124 -- if staging not being run/complete, before calling SDIB.
125
126 EXECUTE IMMEDIATE 'SELECT HZ_MATCH_RULE_'||p_rule_id||'.check_staged_var from dual' INTO l_staged_var;
127
128 IF l_staged_var='N' THEN
129 FND_MESSAGE.SET_NAME('AR', 'HZ_MATCH_RULE_TX_NOT_STAGED');
130 FND_MSG_PUB.ADD;
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133
134 -- End bug 4736139
135
136 -- req_data will be null the first time, by default
137 req_data := fnd_conc_global.request_data;
138
139 -- get the purpose of the match rule first
140 select rule_purpose into match_rule_purpose
141 from hz_match_rules_vl
142 where match_rule_id = p_rule_id ;
143
144 l_batch_id := to_number(p_batch_name);
145
146 -- First Phase
147 IF (req_data IS NULL)
148 THEN
149 l_rule_id := to_number(p_rule_id);
150 retcode := 0;
151
152 /* Changes for scheduling of SDIB concurrent program. Bug: 4631257*/
153 l_batch_id := to_number(p_batch_name);
154 select count(*) into batch_count from hz_dup_batch where dup_batch_id=l_batch_id
155 and request_id is not null;
156
157
158 if(batch_count>0) then
159
160
161 outandlog('batch count= '||batch_count);
162 outandlog('l_batch_name= '||l_batch_name);
163
164 select nvl(automerge_flag,'N') into l_automerge_flag
165 from hz_dup_batch
166 where dup_batch_id = l_batch_id;
167
168 select HZ_DUP_BATCH_S.NEXTVAL into l_new_batch_id from dual;
169
170 insert into hz_dl_selected_criteria
171 (select hz_dl_selected_criteria_s.NEXTVAL, 'SDIB', l_new_batch_id, attribute_name,operation,value,fnd_global.user_id,
172 sysdate,fnd_global.login_id,fnd_global.user_id,sysdate from hz_dl_selected_criteria
173 where batch_id=l_batch_id and batch_type='SDIB');
174
175 select dup_batch_name into l_batch_name from hz_dup_batch where dup_batch_id = l_batch_id;
176
177 l_batch_name := substr(l_batch_name,0,(instr(l_batch_name,'-')-1));
178 l_batch_id := l_new_batch_id;
179
180 HZ_DUP_BATCH_PKG.Insert_Row(l_batch_id,l_batch_name,p_rule_id,fnd_global.resp_appl_id,
181 'SYSTEM_GENERATED',fnd_global.user_id,sysdate,fnd_global.login_id,
182 sysdate,fnd_global.user_id);
183
184
185 UPDATE HZ_DUP_BATCH set automerge_flag = l_automerge_flag, last_update_date = SYSDATE,
186 last_update_login = FND_GLOBAL.LOGIN_ID,last_updated_by = FND_GLOBAL.USER_ID
187 Where dup_batch_id = l_batch_id;
188 outandlog('new batch id = '||l_batch_id);
189 end if;
190
191 UPDATE HZ_DUP_BATCH
192 SET REQUEST_ID = hz_utility_v2pub.request_id
193 WHERE dup_batch_id = l_batch_id;
194
195
196 outandlog('Starting Concurrent Program ''Batch Duplicate Identification''');
197 outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
198 outandlog('NEWLINE');
199
200
201 log('match_rule_purpose is '||match_rule_purpose);
202
203 -- This step is necessary only for Bulk Match Rules :: Refer to Bug 4261835
204 IF match_rule_purpose = 'Q'
205 THEN
206 BEGIN
207 log('Beginning to truncate HZ_DUP_RESULTS');
208 -- make sure we start on a clean slate for Quick Duplicate Identification
209 l_owner := HZ_IMP_DQM_STAGE.get_owner_name('HZ_DUP_RESULTS', 'TABLE');
210 execute immediate ' truncate table ' || l_owner || '.HZ_DUP_RESULTS';
211 EXCEPTION
212 WHEN OTHERS THEN
213 log('-----------------------------------------------------');
214 log('Error while truncating HZ_DUP_RESULTS');
215 log('Error is ' || SQLERRM);
216 IF SQLCODE = -00054 THEN
217
218 BEGIN
219 log('It appears that another instance of the Batch Duplicate Identification program');
220 log('using a Bulk Match Rule, is running already.');
221 log('Please note that the Batch Duplicate Identification program is incompatible');
222 log('with itself, when using Bulk Match Rules.');
223
224 -- try to get the request id of the other instance of
225 -- SDIB that may be running, with the hard coded concurrent program id
226 -- from seed data
227 FOR req_cur in
228 (select request_id
229 from fnd_concurrent_requests a
230 where concurrent_program_id = 44445
231 and phase_code = 'R'
232 and substr(argument_text, 1, instr(argument_text,',') -1 )
233 in ( select match_rule_id from hz_match_rules_vl where rule_purpose = 'Q')
234 order by actual_start_date
235 )
236 LOOP
237 log('Request Id of the concurrent program running already is ' || req_cur.request_id );
238 exit ;
239
240 END LOOP ;
241
242 -- we have the exception block, just in case the above SQL does not return any data
243 EXCEPTION
244 WHEN OTHERS
245 THEN
246 log('Error occurred while trying to find the request id of the other running instance of SDIB.');
247 log('Error is ' || SQLERRM);
248 NULL ;
249 END ;
250
251 END IF ;
252
253 log('-----------------------------------------------------');
254 RAISE ;
255 END ;
256 END IF ;
257
258
259 -- Initialize return status and message stack
260 FND_MSG_PUB.initialize;
261
262 IF l_rule_id IS NULL OR l_rule_id = 0 THEN
263 -- Find the match rule
264 null;
265
266 -- No MATCH RULE FOUND
267 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_MATCH_RULE');
268 FND_MSG_PUB.ADD;
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271
272 IF p_batch_name IS NULL OR p_batch_name = '' THEN
273 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_NAME');
274 FND_MSG_PUB.ADD;
275 RAISE FND_API.G_EXC_ERROR;
276 END IF;
277
278 --- VJN INTRODUCED CODE FOR QUICK DUPLICATE IDENTIFICATION
279
280 log('Spawning ' || p_num_workers || ' Workers for duplicate identification');
281 FOR I in 1..TO_NUMBER(p_num_workers)
282 LOOP
283 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDQBDW',
284 'Batch Duplicate Worker ' || to_char(i),
285 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
286 TRUE, p_num_workers, TO_CHAR(I), p_rule_id,
287 to_char(l_batch_id),p_subset_defn,p_match_within_subset,p_search_merged);
288 IF l_sub_requests(i) = 0 THEN
289 log('Error submitting worker ' || i);
290 log(fnd_message.get);
291 dup_workers_completed := FALSE;
292 retcode := 2;
293 ELSE
294 log('Submitted request for Worker ' || TO_CHAR(I) );
295 log('Request ID : ' || l_sub_requests(i));
296 END IF;
297 EXIT when l_sub_requests(i) = 0;
298 END LOOP;
299
300 -- wait for completion of all workers
301 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'SYSTEM_PHASE1') ;
302 -- Second Phase
303 ELSE
304 log('***********************************************************');
305 log('Post processing Data cleanup for SDIB');
306 -- l_batch_id := to_number(p_batch_name); bug 6067226
307
308 SELECT dup_batch_id INTO l_batch_id
309 FROM hz_dup_batch
310 where request_id = FND_GLOBAL.conc_request_id;
311
312 OPEN dup_dup_parties(l_batch_id);
313 LOOP
314 FETCH dup_dup_parties into l_dup_dup_id;
315 EXIT when dup_dup_parties%NOTFOUND;
316 log('Party ' || l_dup_dup_id || ' occurs more than once in batch '||l_batch_id);
317 FIRST:=TRUE;
318 open dup_party_sets(l_batch_id,l_dup_dup_id);
319 LOOP
320 FETCH dup_party_sets INTO l_dup_dup_set;
321 EXIT when dup_party_sets%NOTFOUND;
322 log('Dup set id ' || l_dup_dup_set || ' has party '||l_dup_dup_id);
323 IF NOT FIRST THEN
324 log('Party ' || l_dup_dup_id || ' already found as a dup in another set. Deleting.');
325 DELETE from hz_dup_set_parties
326 WHERE dup_set_id=l_dup_dup_set
327 AND dup_party_id=l_dup_dup_id;
328 log('More than one occurence of party id ' ||l_dup_dup_id || ' in dup set '||l_dup_dup_set);
329
330 SELECT count(1) INTO l_num_left
331 FROM hz_dup_set_parties
332 WHERE dup_set_id=l_dup_dup_set;
333 log('Total number of parties in dup set ' ||l_dup_dup_set || ' is '||l_num_left);
334 IF l_num_left=1 THEN
335 SELECT DUP_PARTY_ID INTO l_winner_id FROM HZ_DUP_SET_PARTIES
336 WHERE dup_set_id = l_dup_dup_set;
337 log('Winner party '||l_winner_id||' is the only party in dup set '||l_dup_dup_set||' and this party will be deleted frm the current batch ');
338
339 log('Delete dup sets with one party from hz_dup_set_parties, dup set id'||l_dup_dup_set);
340 DELETE from hz_dup_set_parties
341 WHERE dup_set_id=l_dup_dup_set;
342
343 log('Delete dup sets with no parties from hz_dup_sets, dup set id'||l_dup_dup_set);
347 ELSE
344 DELETE from hz_dup_sets
345 WHERE dup_set_id=l_dup_dup_set;
346 END IF;
348 FIRST:=FALSE;
349 log('First Occurence of party id ' ||l_dup_dup_id || ' in dup set '||l_dup_dup_set);
350 END IF;
351 END LOOP;
352 CLOSE dup_party_sets;
353 END LOOP;
354 CLOSE dup_dup_parties;
355
356 DELETE FROM hz_dup_sets d1 WHERE dup_batch_id = l_batch_id
357 AND NOT EXISTS (SELECT 1 FROM hz_dup_set_parties
358 WHERE dup_set_id = d1.dup_set_id);
359 log('Delete dup sets with no parties from hz_dup_sets'||SQL%ROWCOUNT);
360
361 log('');
362 log('***********************************************************');
363
364 -- AFTER ALL THE WORKERS ARE DONE, SEE IF THEY HAVE ALL COMPLETED NORMALLY
365
366 -- assume that all concurrent dup workers completed normally, unless found otherwise
367 dup_workers_completed := TRUE;
368
369 Select request_id BULK COLLECT into l_sub_requests
370 from Fnd_Concurrent_Requests R
371 Where Parent_Request_Id = FND_GLOBAL.conc_request_id
372 and (phase_code<>'C' or status_code<>'C');
373
374 IF l_sub_requests.count>0 THEN
375 dup_workers_completed:=FALSE;
376 FOR I in 1..l_sub_requests.COUNT LOOP
377 outandlog('Worker with request id ' || l_sub_requests(I) );
378 outandlog('did not complete normally');
379 retcode := 2;
380 END LOOP;
381 END IF;
382 log('p_rule_id '||p_rule_id);
383 --l_batch_id := to_number(p_batch_name);
384 l_rule_id := to_number(p_rule_id);
385 log('match_rule_purpose '||match_rule_purpose);
386 IF dup_workers_completed THEN
387 log('dup_workers_completed TRUE');
388 ELSE
389 log('dup_workers_completed FALSE');
390 END IF;
391 -- if match rule purpose is Quick Duplicate Identification
392 -- call the corresponding API for sanitization,
393 -- provided all the dup_workers have completed normally
394 IF match_rule_purpose = 'Q' and dup_workers_completed
395 THEN
396 HZ_DQM_DUP_ID_PKG.tca_sanitize_report(
397 l_batch_id,
398 l_rule_id,
399 p_subset_defn,
400 p_match_within_subset
401 );
402 -- make sure we truncate the dup results table after all the workers are done
403 l_owner := HZ_IMP_DQM_STAGE.get_owner_name('HZ_DUP_RESULTS', 'TABLE');
404 execute immediate ' truncate table ' || l_owner || '.HZ_DUP_RESULTS';
405
406 END IF;
407
408 select automerge_flag into l_automerge_flag
409 from hz_dup_batch
410 where dup_batch_id = l_batch_id;
411
412 if nvl(l_automerge_flag,'N') = 'Y'
413 then
414 -- start bug 4773387
415 open am_candidates_exist_csr(l_batch_id);
416 fetch am_candidates_exist_csr into l_am_count;
417 close am_candidates_exist_csr;
418 if l_am_count <> 0 then
419 -- end bug 4773387
420 l_req_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHAMRGP','Automerge', to_char(sysdate,'DD-MON-YY HH24:MI:SS'), TRUE, l_batch_id, p_num_workers);
421
422 IF l_req_id = 0 THEN
423 log('Error submitting Automerge request');
424 log(fnd_message.get);
425 ELSE
426 log('Submitted request ID for Automerge: ' || l_req_id );
427 END IF;
428 -- start bug 4773387
429 else
430 log('No automerge candidates, therefore only one reviewable batch has been created.');
431
432 update hz_dup_batch
433 set automerge_flag = 'N',
434 request_type = 'SYSTEM_AUTOMERGE'
435 where dup_batch_id = l_batch_id;
436 end if;
437 -- end bug 4773387
438
439
440 end if;
441
442
443 outandlog('Concurrent Program Execution completed ');
444 outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
445 END IF;
446
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449 outandlog('Expected Error section in Parent concurrent program. Aborting duplicate batch.' ||SQLERRM);
450 retcode := 2;
451 errbuf := errbuf || logerror;
452 FND_FILE.close;
453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
454 outandlog('UnExpected Error section in Parent concurrent program. Aborting duplicate batch.' ||SQLERRM);
455 retcode := 2;
456 errbuf := errbuf || logerror;
457 FND_FILE.close;
458 WHEN OTHERS THEN
459 outandlog('Other Error Section in Parent concurrent program. Aborting duplicate batch.' ||SQLERRM);
460 retcode := 2;
461 errbuf := errbuf || logerror;
462 FND_FILE.close;
463 END;
464
465 PROCEDURE find_party_dups (
466 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
467 p_rule_id IN NUMBER,
468 p_party_id IN NUMBER,
469 x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
470 x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
471 x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
475 x_return_status OUT NOCOPY VARCHAR2,
472 x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list,
473 x_search_ctx_id OUT NOCOPY NUMBER,
474 x_num_matches OUT NOCOPY NUMBER,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2
478 ) IS
479
480 l_return_status VARCHAR2(30);
481 L_msg_count NUMBER;
482 l_msg_data VARCHAR2(2000);
483 l_rule_id NUMBER;
484 l_dup_set_id NUMBER;
485 l_num_matches NUMBER;
486
487 BEGIN
488 l_rule_id := p_rule_id;
489
490 HZ_PARTY_SEARCH.get_party_for_search(
491 FND_API.G_FALSE,l_rule_id, p_party_id, x_party_search_rec,
492 x_party_site_list, x_contact_list, x_contact_point_list,
493 l_return_status, l_msg_count, l_msg_data);
494
495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
496 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
497 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_ACQUIRE_ERROR');
498 FND_MESSAGE.SET_TOKEN('PARTY_ID', TO_CHAR(p_party_id));
499 FND_MSG_PUB.ADD;
500 RAISE FND_API.G_EXC_ERROR;
501 ELSE
502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
503 END IF;
504 ELSE
505 HZ_PARTY_SEARCH.find_duplicate_parties(
506 FND_API.G_FALSE,l_rule_id, p_party_id, NULL,
507 NULL, null, null, l_dup_set_id,x_search_ctx_id, l_num_matches,
508 l_return_status, l_msg_count, l_msg_data);
509 END IF;
510
511 EXCEPTION
512 WHEN FND_API.G_EXC_ERROR THEN
513 x_return_status := FND_API.G_RET_STS_ERROR;
514 FND_MSG_PUB.Count_And_Get(
515 p_encoded => FND_API.G_FALSE,
516 p_count => x_msg_count,
517 p_data => x_msg_data);
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 FND_MSG_PUB.Count_And_Get(
521 p_encoded => FND_API.G_FALSE,
522 p_count => x_msg_count,
523 p_data => x_msg_data);
524 WHEN OTHERS THEN
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_SQL_EXCEP');
527 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
528 FND_MSG_PUB.ADD;
529
530 FND_MSG_PUB.Count_And_Get(
531 p_encoded => FND_API.G_FALSE,
532 p_count => x_msg_count,
533 p_data => x_msg_data);
534 END;
535
536 PROCEDURE find_party_dups (
537 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
538 p_rule_id IN NUMBER,
539 p_party_id IN NUMBER,
540 p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
541 p_contact_ids IN HZ_PARTY_SEARCH.IDList,
542 p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
543 x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
544 x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
545 x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
546 x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list,
547 x_search_ctx_id OUT NOCOPY NUMBER,
548 x_num_matches OUT NOCOPY NUMBER,
549 x_return_status OUT NOCOPY VARCHAR2,
550 x_msg_count OUT NOCOPY NUMBER,
551 x_msg_data OUT NOCOPY VARCHAR2
552 ) IS
553
554 l_return_status VARCHAR2(30);
555 L_msg_count NUMBER;
556 l_msg_data VARCHAR2(2000);
557 l_rule_id NUMBER;
558
559
560 BEGIN
561 l_rule_id := p_rule_id;
562
563 HZ_PARTY_SEARCH.get_search_criteria(
564 FND_API.G_FALSE,l_rule_id, p_party_id, p_party_site_ids,p_contact_ids,
565 p_contact_pt_ids, x_party_search_rec,
566 x_party_site_list, x_contact_list, x_contact_point_list,
567 l_return_status, l_msg_count, l_msg_data);
568
569 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
570 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
571 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_ACQUIRE_ERROR');
572 FND_MESSAGE.SET_TOKEN('PARTY_ID', TO_CHAR(p_party_id));
573 FND_MSG_PUB.ADD;
574 RAISE FND_API.G_EXC_ERROR;
575 ELSE
576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
577 END IF;
578 ELSE
579 HZ_PARTY_SEARCH.find_parties(
580 FND_API.G_FALSE,l_rule_id,
581 x_party_search_rec, x_party_site_list,x_contact_list, x_contact_point_list,
582 'party_id <> '||p_party_id || ' and ROWNUM < 1000',
583 'N', x_search_ctx_id,x_num_matches,x_return_status,
584 x_msg_count, x_msg_data);
585 END IF;
586
587 EXCEPTION
588 WHEN FND_API.G_EXC_ERROR THEN
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 FND_MSG_PUB.Count_And_Get(
591 p_encoded => FND_API.G_FALSE,
592 p_count => x_msg_count,
593 p_data => x_msg_data);
594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596 FND_MSG_PUB.Count_And_Get(
597 p_encoded => FND_API.G_FALSE,
598 p_count => x_msg_count,
599 p_data => x_msg_data);
600 WHEN OTHERS THEN
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605
602 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_SQL_EXCEP');
603 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
604 FND_MSG_PUB.ADD;
606 FND_MSG_PUB.Count_And_Get(
607 p_encoded => FND_API.G_FALSE,
608 p_count => x_msg_count,
609 p_data => x_msg_data);
610 END;
611
612
613
614 PROCEDURE find_dup_parties_worker (
615 errbuf OUT NOCOPY VARCHAR2,
616 retcode OUT NOCOPY VARCHAR2,
617 p_num_workers IN VARCHAR2,
618 p_worker_number IN VARCHAR2,
619 p_rule_id IN VARCHAR2,
620 p_batch_id IN VARCHAR2,
621 p_subset_defn IN VARCHAR2,
622 p_match_within_subset IN VARCHAR2,
623 p_search_merged IN VARCHAR2
624 ) IS
625
626 /* CURSOR dup_dup_parties(cp_dup_batch_id NUMBER) IS
627 select dup_party_id
628 from hz_dup_set_parties
629 where dup_set_batch_id = cp_dup_batch_id --Bug No: 4244529
630 group by dup_party_id
631 having count(*)>1;
632
633 CURSOR dup_party_sets(cp_dup_batch_id NUMBER, cp_dup_party_id NUMBER) IS
634 select dsp.dup_set_id
635 FROM hz_dup_set_parties dsp
636 where dsp.dup_set_batch_id = cp_dup_batch_id --Bug No: 4244529
637 and dsp.dup_party_id=cp_dup_party_id;*/
638
639
640 l_rule_id NUMBER;
641 l_batch_id NUMBER;
642 l_cur_party_id NUMBER;
643 l_search_ctx_id NUMBER;
644
645 TYPE PartyCurTyp IS REF CURSOR;
646 c_parties PartyCurTyp;
647
648 l_return_status VARCHAR2(30);
649 L_msg_count NUMBER;
650 l_msg_data VARCHAR2(2000);
651 l_sqlerr VARCHAR2(2000);
652 l_subset_defn VARCHAR2(2000);
653
654 l_num_workers NUMBER;
655 l_worker_number NUMBER;
656
657 l_num_matches NUMBER;
658 l_match_score NUMBER;
659 l_auto_merge_score NUMBER;
660
661 l_dup_set_id NUMBER;
662 l_dup_dup_set NUMBER;
663 l_dup_dup_id NUMBER;
664 l_num_left NUMBER;
665 l_num_subset NUMBER;
666 l_num_evaluated NUMBER;
667 l_total_matches NUMBER;
668 l_total_dup_sets NUMBER;
669 skipped VARCHAR2(32000):=' ';
670 skip_line_cnt NUMBER:=0;
671
672 t1 NUMBER;
673 t2 NUMBER;
674
675 FIRST BOOLEAN;
676
677 -- VJN INTRODUCED VARIABLES FOR QUICK DUPLICATE IDENTIFICATION
678 match_rule_purpose varchar2(1) ;
679
680 -- bug 5393863
681 party_count NUMBER ;
682 error_count NUMBER ;
683 error_limit NUMBER ;
684 -- bug 5393863
685
686 BEGIN
687
688 l_rule_id := to_number(p_rule_id);
689 l_num_workers := to_number(p_num_workers);
690 l_worker_number := to_number(p_worker_number);
691
692 -- Initialize return status and message stack
693 FND_MSG_PUB.initialize;
694 l_batch_id := TO_NUMBER(p_batch_id);
695 l_num_subset:=0;
696 l_num_evaluated:=0;
697 l_total_matches:=0;
698 l_total_dup_sets:=0;
699 error_limit := 5000 ;--bug 5393863
700
701
702 --- VJN INTRODUCED CODE FOR QUICK DUPLICATE IDENTIFICATION
703
704 select rule_purpose into match_rule_purpose
705 from hz_match_rules_vl
706 where match_rule_id = l_rule_id ;
707
708
709 IF match_rule_purpose = 'Q'
710 THEN
711 log('Starting Concurrent Program ''Batch Quick Duplicate Identification Worker: '||p_worker_number||'''');
712 log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
713 log('subset defn is ' || p_subset_defn );
714
715 IF l_worker_number = l_num_workers THEN
716 l_worker_number := 0;
717 END IF;
718 HZ_DQM_DUP_ID_PKG.tca_dup_id_worker(
719 l_batch_id,
720 l_rule_id,
721 l_worker_number,
722 l_num_workers,
723 p_subset_defn
724 );
725 log('End Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
726 ELSE
727
728 SELECT match_score, auto_merge_score
729 INTO l_match_score, l_auto_merge_score
730 FROM hz_match_rules_vl
731 WHERE match_rule_id = l_rule_id;
732
733 IF l_auto_merge_score is null OR l_auto_merge_score < l_match_score THEN
734 l_auto_merge_score := 999999999;
735 END IF;
736
737 IF l_worker_number = l_num_workers THEN
738 l_worker_number := 0;
739 END IF;
740
741 retcode := 0;
742
743 log('Starting Concurrent Program ''Batch Duplicate Identification Worker: '||p_worker_number||'''');
744 log('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
745 log('NEWLINE');
746
747 log('------------------------------------------------------');
748 log('Start Time before insert to chunk ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
749
750 --Adding the condition of Status = A, to the 2 OPEN c_parties cursors below to fix bug 4669400.
751 --This will make sure that the Merged and Inactive Parties (with status as 'M' and 'I')
755 execute immediate
752 --will not be considered for duplicate idenfication.
753 -- bug 5393863
754 IF p_subset_defn IS NULL THEN
756 'insert /*+ APPEND */ into hz_dup_worker_chunk_gt
757 SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ parties.PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP''
758 AND NVL(parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number '
759 USING l_num_workers, l_worker_number;
760 log('Number of parties inserted into HZ_DUP_WORKER_CHUNK_GT by worker '||l_worker_number||' is '||SQL%ROWCOUNT );
761 ELSE
762 execute immediate
763 'insert /*+ APPEND */ into hz_dup_worker_chunk_gt
764 SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL(parties.STATUS,''A'') = ''A''
765 AND mod(parties.PARTY_ID, :num_workers) = :worker_number AND '||
766 p_subset_defn
767 USING l_num_workers, l_worker_number;
768 log('Number of parties inserted into HZ_DUP_WORKER_CHUNK_GT by worker '||l_worker_number||' is '||SQL%ROWCOUNT );
769 END IF;
770 log('End Time after insert to chunk ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
771 COMMIT;
772 log(' Commit to Chunk complete ');
773
774 -- bug 5393863
775
776 FOR EVALPARTY IN (
777 SELECT PARTY_ID
778 FROM HZ_DUP_WORKER_CHUNK_GT ORDER BY PARTY_ID)
779 LOOP
780 l_cur_party_id := EVALPARTY.PARTY_ID;
781 party_count := party_count + 1 ;
782
783 log('------------------------------------------------------');
784 log('Processing party_id '||l_cur_party_id);
785
786 l_num_subset := l_num_subset+1;
787
788 IF NOT check_party_in_dupset(l_batch_id, l_cur_party_id) THEN
789 BEGIN
790
791 t1:=to_number(to_char(SYSDATE,'SSSSS'));
792 IF p_match_within_subset = 'Y' AND p_subset_defn IS NOT NULL THEN
793 l_subset_defn := 'EXISTS (select 1 FROM HZ_PARTIES parties '||
794 'where parties.party_id = stage.party_id ' ||
795 'and '||p_subset_defn||')';
796 HZ_PARTY_SEARCH.find_duplicate_parties(
797 FND_API.G_TRUE,l_rule_id, l_cur_party_id, l_subset_defn,
798 NULL, l_batch_id, p_search_merged, l_dup_set_id,l_search_ctx_id, l_num_matches,
799 l_return_status, l_msg_count, l_msg_data);
800 ELSE
801 HZ_PARTY_SEARCH.find_duplicate_parties(
802 FND_API.G_TRUE,l_rule_id, l_cur_party_id, NULL,
803 NULL, l_batch_id, p_search_merged, l_dup_set_id,l_search_ctx_id, l_num_matches,
804 l_return_status, l_msg_count, l_msg_data);
805 END IF;
806
807 -- Search is not Successful
808 -- EXPECTED ERRORS :: continue to the next iteration of the loop, log errors,
809 -- until a threshold of errors.
810 -- UNEXPECTED ERRORS :: log error and exit batch.
811 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
812 THEN
813 -- Count Errors
814 error_count := error_count+1;
815 IF(error_count = error_limit)
816 THEN
817 log('Errors exceeded the threshold of errors');
818 log('Exiting ..');
819 RAISE FND_API.G_EXC_ERROR ;
820 END IF;
821
822 -- Expected Errors
823 IF l_return_status = FND_API.G_RET_STS_ERROR
824 THEN
825 log('Expected Error during party_id '||l_cur_party_id);
826 log('Error is '||l_msg_data);
827 log('Continuing ..');
828 -- UnExpected Errors
829 ELSE
830 log('Unexpected Error during party_id '||l_cur_party_id);
831 log('Error is '||l_msg_data);
832 log('Exiting ..');
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
834 END IF;
835 -- Search is Successful
836 ELSE
837 IF l_num_matches>0 THEN
838 l_total_matches := l_total_matches+l_num_matches;
839 l_total_dup_sets := l_total_dup_sets+1;
840 log('Found ' || l_num_matches || ' duplicates for '||l_cur_party_id);
841 remove_non_duplicates(l_cur_party_id,l_dup_set_id);
842 COMMIT;
843 ELSE
844 log('Completed successfully but found no duplicates');
845 END IF;
846 END IF;
847
848 l_num_evaluated := l_num_evaluated+HZ_DQM_SEARCH_UTIL.get_num_eval;
849 IF HZ_DQM_SEARCH_UTIL.get_num_eval>g_print_num_eval THEN
853 IF (t2-t1)>g_print_time_taken THEN
850 log('******* Evaluated '||HZ_DQM_SEARCH_UTIL.get_num_eval||' for party_id '||l_cur_party_id);
851 END IF;
852 t2:=to_number(to_char(SYSDATE,'SSSSS'));
854 log('******* Time taken to evaluate party_id '||l_cur_party_id||' is: '||(t2-t1));
855 END IF;
856 END;
857 ELSE
858 log('l_cur_party_id '||l_cur_party_id||' is not processed ');
859 END IF;
860 END LOOP;
861
862 log(' All Parties processed successfully. Commencing Sanitization');
863
864 /* Fix non-mutually exclusive sets if any */
865 --DELETE FROM hz_dup_set_parties WHERE dup_set_id IN (
866 --SELECT dup_set_id FROM hz_dup_sets d1
867 --WHERE dup_batch_id = l_batch_id
868 --AND EXISTS ( --Bug No: 4244529
869 --SELECT 1
870 --FROM hz_dup_set_parties dp
871 --WHERE dp.DUP_SET_BATCH_ID = d1.dup_batch_id
872 --AND dp.DUP_PARTY_ID = d1.winner_party_id
873 --AND dp.DUP_SET_ID <> d1.dup_set_id));
874
875 --DELETE FROM hz_dup_sets d1 WHERE dup_batch_id = l_batch_id
876 --AND NOT EXISTS (SELECT 1 FROM hz_dup_set_parties
877 --WHERE dup_set_id = d1.dup_set_id);
878
879 log('');
880 log('Total Number of parties in subset '||l_num_subset);
881 log('Total Number of parties scored '||l_num_evaluated);
882 log('Total Number of duplicate_sets identified '||l_total_dup_sets);
883 log('Total Number of duplicates identified '||l_total_matches);
884
885 log('End Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
886 log('THIS WORKER COMPLETED SUCCESSFULLY');
887
888 END IF;
889
890 EXCEPTION
891 WHEN FND_API.G_EXC_ERROR THEN
892 log('--------------------------------');
893 log('Expected Error ' || l_cur_party_id);
894 log('Error: Aborting duplicate batch');
895 log('THIS WORKER ERRORED OUT');
896 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
897 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
898 END LOOP;
899 FND_MESSAGE.CLEAR;
900
901 retcode := 2;
902 errbuf := 'Expected Error ' || l_cur_party_id;
903 FND_FILE.close;
904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
905 log('--------------------------------');
906 log('Unexpected Error ' || l_cur_party_id);
907 log('Error: Aborting duplicate batch');
908 log('THIS WORKER ERRORED OUT');
909 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
910 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
911 END LOOP;
912 FND_MESSAGE.CLEAR;
913
914 errbuf := 'UnExpected Error ' || l_cur_party_id;
915 retcode := 2;
916 FND_FILE.close;
917 WHEN OTHERS THEN
918 log('--------------------------------');
919 log('Unknown Error ' || l_cur_party_id || ' : ' || SQLERRM);
920 log('Error: Aborting duplicate batch');
921 log('THIS WORKER ERRORED OUT');
922 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
923 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
924 END LOOP;
925
926 retcode := 2;
927 errbuf := 'UnExpected SQL Error ' || l_cur_party_id;
928 FND_FILE.close;
929 END;
930
931 FUNCTION check_party_in_dupset (
932 p_batch_id IN NUMBER,
933 p_party_id IN NUMBER,
934 p_dup_set_id IN NUMBER := -1
935 ) RETURN BOOLEAN IS
936
937 CURSOR c_dupset_party IS
938 SELECT dup_party_id
939 FROM hz_dup_set_parties dp
940 WHERE dp.DUP_SET_BATCH_ID = p_batch_id --Bug No: 4244529
941 AND dp.DUP_PARTY_ID = p_party_id
942 AND dp.DUP_SET_ID <> p_dup_set_id;
943
944 l_dup_party_id NUMBER;
945
946 BEGIN
947 OPEN c_dupset_party;
948 FETCH c_dupset_party INTO l_dup_party_id;
949 IF c_dupset_party%FOUND THEN
950 CLOSE c_dupset_party;
951 RETURN TRUE;
952 ELSE
953 CLOSE c_dupset_party;
954 RETURN FALSE;
955 END IF;
956
957 EXCEPTION
958 WHEN OTHERS THEN
959 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_SQL_ERROR');
960 FND_MESSAGE.SET_TOKEN('PROC' ,'check_party_in_dupset');
961 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
962 FND_MSG_PUB.ADD;
963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 END;
965
966 -- Remove mathes that have been indicated as non-duplicates in the
967 -- HZ_DUP_EXCLUSIONS table
968 PROCEDURE remove_non_duplicates (
969 p_cur_party_id IN NUMBER,
970 p_dup_set_id IN NUMBER
971 ) IS
972 winner_count NUMBER;
973 dupset_count NUMBER;
974 BEGIN
975 DELETE FROM HZ_DUP_SET_PARTIES p
976 WHERE p.dup_set_id = p_dup_set_id
977 AND p.dup_party_id <> p_cur_party_id
978 AND EXISTS (
979 SELECT 1 FROM HZ_DUP_EXCLUSIONS d
980 WHERE (
981 (d.PARTY_ID=p_cur_party_id AND d.DUP_PARTY_ID=p.dup_party_id)
982 OR
983 (d.PARTY_ID=p.dup_party_id AND d.DUP_PARTY_ID=p_cur_party_id)
987
984 ) AND (d.FROM_DATE IS NULL OR d.FROM_DATE <= SYSDATE)
985 AND (d.TO_DATE IS NULL OR d.TO_DATE >= SYSDATE)
986 );
988 SELECT COUNT(*) INTO dupset_count FROM HZ_DUP_SET_PARTIES
989 WHERE DUP_SET_ID = p_dup_set_id;
990
991 IF (dupset_count=1) THEN
992 SELECT COUNT(*) INTO winner_count FROM HZ_DUP_SET_PARTIES
993 WHERE DUP_SET_ID = p_dup_set_id
994 and dup_party_id in
995 (select winner_party_id from hz_dup_sets where dup_set_id=p_dup_set_id);
996
997 if(winner_count=1) then
998 DELETE FROM HZ_DUP_SET_PARTIES WHERE DUP_SET_ID=p_dup_set_id;
999 DELETE FROM HZ_DUP_SETS WHERE DUP_SET_ID=p_dup_set_id;
1000 end if;
1001
1002 END IF;
1003
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_SQL_ERROR');
1007 FND_MESSAGE.SET_TOKEN('PROC' ,'remove_non_duplicates');
1008 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1009 FND_MSG_PUB.ADD;
1010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1011 END remove_non_duplicates;
1012
1013 PROCEDURE insert_match_details (
1014 p_search_ctx_id IN NUMBER,
1015 p_dup_set_id IN NUMBER,
1016 p_dup_party_id IN NUMBER
1017 ) IS
1018
1019 BEGIN
1020
1021 INSERT INTO HZ_DUP_MATCH_DETAILS (
1022 DUP_SET_ID,
1023 DUP_PARTY_ID,
1024 WINNER_PARTY_VALUE,
1025 MATCHED_PARTY_VALUE,
1026 MATCHED_ATTRIBUTE,
1027 ASSIGNED_SCORE,
1028 CREATED_BY,
1029 CREATION_DATE,
1030 LAST_UPDATE_LOGIN,
1031 LAST_UPDATE_DATE,
1032 LAST_UPDATED_BY
1033 ) SELECT p_dup_set_id,
1034 p_dup_party_id,
1035 gt.ENTERED_VALUE,
1036 gt.MATCHED_VALUE,
1037 vl.USER_DEFINED_ATTRIBUTE_NAME, -- Bug No: 3820598
1038 gt.ASSIGNED_SCORE,
1039 hz_utility_pub.created_by,
1040 hz_utility_pub.creation_date,
1041 hz_utility_pub.last_update_login,
1042 hz_utility_pub.last_update_date,
1043 hz_utility_pub.user_id
1044 FROM hz_party_score_dtls_gt gt,hz_trans_attributes_vl vl -- Bug No: 3820598
1045 WHERE gt.PARTY_ID = p_dup_party_id
1046 AND gt.SEARCH_CONTEXT_ID = p_search_ctx_id
1047 AND gt.ENTITY = vl.ENTITY_NAME
1048 AND gt.ATTRIBUTE=vl.ATTRIBUTE_NAME;
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_SQL_ERROR');
1052 FND_MESSAGE.SET_TOKEN('PROC' ,'create_dup_set');
1053 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1054 FND_MSG_PUB.ADD;
1055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1056 END insert_match_details;
1057
1058 PROCEDURE get_dup_match_details (
1059 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1060 p_rule_id IN NUMBER,
1061 p_dup_set_id IN NUMBER,
1062 x_return_status OUT NOCOPY VARCHAR2,
1063 x_msg_count OUT NOCOPY NUMBER,
1064 x_msg_data OUT NOCOPY VARCHAR2
1065 ) IS
1066
1067 l_rule_id NUMBER;
1068 l_search_ctx_id NUMBER;
1069 l_winner_party_id NUMBER;
1070
1071 l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
1072 l_party_site_list HZ_PARTY_SEARCH.party_site_list;
1073 l_contact_list HZ_PARTY_SEARCH.contact_list;
1074 l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
1075
1076 l_return_status VARCHAR2(30);
1077 L_msg_count NUMBER;
1078 l_msg_data VARCHAR2(2000);
1079
1080 l_num_matches NUMBER;
1081 l_count NUMBER;
1082
1083 l_dup_set_creation_date HZ_DUP_SETS.creation_date%type;
1084 l_mr_last_updated_date HZ_MATCH_RULES_VL.last_update_date%type;
1085 l_mr_comp_flag VARCHAR2(1);
1086
1087 BEGIN
1088 -- Initialize return status and message stack
1089 x_return_status := FND_API.G_RET_STS_SUCCESS;
1090 IF FND_API.to_Boolean(p_init_msg_list) THEN
1091 FND_MSG_PUB.initialize;
1092 END IF;
1093
1094 SELECT count(1)
1095 INTO l_count
1096 FROM hz_dup_match_details
1097 WHERE dup_set_id = p_dup_set_id;
1098
1099 IF l_count>0 THEN
1100 RETURN;
1101 END IF;
1102
1103 l_rule_id := p_rule_id;
1104 SELECT winner_party_id,creation_date INTO l_winner_party_id,l_dup_set_creation_date
1105 FROM HZ_DUP_SETS
1106 WHERE dup_set_id = p_dup_set_id;
1107
1108 select last_update_date,compilation_flag INTO l_mr_last_updated_date,l_mr_comp_flag
1109 from HZ_MATCH_RULES_VL
1110 where match_rule_id = p_rule_id;
1111
1112 IF l_mr_comp_flag <> 'C' OR l_mr_last_updated_date > l_dup_set_creation_date THEN
1113 FND_MESSAGE.SET_NAME('AR', 'HZ_CDL_NO_MATCH_DETAILS');
1114 FND_MSG_PUB.ADD;
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117
1118 HZ_PARTY_SEARCH.get_party_for_search(
1119 FND_API.G_FALSE,p_rule_id, l_winner_party_id, l_party_rec,
1120 l_party_site_list, l_contact_list, l_cpt_list,
1121 l_return_status, l_msg_count, l_msg_data);
1122
1123 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1124 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_ACQUIRE_ERROR');
1125 FND_MESSAGE.SET_TOKEN('PARTY_ID', TO_CHAR(l_winner_party_id));
1126 FND_MSG_PUB.ADD;
1127 RAISE FND_API.G_EXC_ERROR;
1128 ELSE
1129
1130 FOR DUP IN (SELECT DUP_PARTY_ID
1131 FROM HZ_DUP_SET_PARTIES
1135 l_party_rec, l_party_site_list,l_contact_list, l_cpt_list,
1132 WHERE DUP_SET_ID = p_dup_set_id and dup_party_id <> l_winner_party_id) LOOP
1133 HZ_PARTY_SEARCH.get_score_details (
1134 FND_API.G_FALSE,p_rule_id, DUP.DUP_PARTY_ID,
1136 l_search_ctx_id, l_return_status, l_msg_count, l_msg_data);
1137 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1138 FND_MESSAGE.SET_NAME('AR', 'HZ_PARTY_SEARCH_ERROR');
1139 FND_MESSAGE.SET_TOKEN('PARTY_ID', TO_CHAR(DUP.DUP_PARTY_ID));
1140 FND_MSG_PUB.ADD;
1141 RAISE FND_API.G_EXC_ERROR;
1142 ELSE
1143 insert_match_details (l_search_ctx_id, p_dup_set_id, DUP.DUP_PARTY_ID);
1144 END IF;
1145 END LOOP;
1146 END IF;
1147
1148 EXCEPTION
1149 WHEN FND_API.G_EXC_ERROR THEN
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 FND_MSG_PUB.Count_And_Get(
1152 p_encoded => FND_API.G_FALSE,
1153 p_count => x_msg_count,
1154 p_data => x_msg_data);
1155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1157 FND_MSG_PUB.Count_And_Get(
1158 p_encoded => FND_API.G_FALSE,
1159 p_count => x_msg_count,
1160 p_data => x_msg_data);
1161 WHEN OTHERS THEN
1162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1163 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_SQL_EXCEP');
1164 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1165 FND_MSG_PUB.ADD;
1166
1167 FND_MSG_PUB.Count_And_Get(
1168 p_encoded => FND_API.G_FALSE,
1169 p_count => x_msg_count,
1170 p_data => x_msg_data);
1171 END;
1172
1173
1174
1175 /**
1176 * Procedure to write a message to the out file
1177 **/
1178 PROCEDURE out(
1179 message IN VARCHAR2,
1180 newline IN BOOLEAN DEFAULT TRUE) IS
1181 BEGIN
1182 /*
1183 IF message = 'NEWLINE' THEN
1184 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1185 ELSIF (newline) THEN
1186 FND_FILE.put_line(fnd_file.output,message);
1187 ELSE
1188 FND_FILE.put(fnd_file.output,message);
1189 END IF;
1190 */
1191 null;
1192 END out;
1193
1194 /**
1195 * Procedure to write a message to the log file
1196 **/
1197 PROCEDURE log(
1198 message IN VARCHAR2,
1199 newline IN BOOLEAN DEFAULT TRUE) IS
1200 BEGIN
1201 IF message = 'NEWLINE' THEN
1202 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1203 ELSIF (newline) THEN
1204 FND_FILE.put_line(fnd_file.log,message);
1205 ELSE
1206 FND_FILE.put_line(fnd_file.log,message);
1207 END IF;
1208 END log;
1209
1210 /**
1211 * Procedure to write a message to the out and log files
1212 **/
1213 PROCEDURE outandlog(
1214 message IN VARCHAR2,
1215 newline IN BOOLEAN DEFAULT TRUE) IS
1216 BEGIN
1217 out(message, newline);
1218 log(message);
1219 END outandlog;
1220
1221 /**
1222 * Function to fetch messages of the stack and log the error
1223 * Also returns the error
1224 **/
1225 PROCEDURE logerror IS
1226 l_msg_data VARCHAR2(2000);
1227 BEGIN
1228 FND_MSG_PUB.Reset;
1229
1230 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1231 log(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));
1232 END LOOP;
1233 FND_MSG_PUB.Delete_Msg;
1234 END logerror;
1235
1236 FUNCTION logerror RETURN VARCHAR2 IS
1237 l_msg_data VARCHAR2(2000);
1238 BEGIN
1239 FND_MSG_PUB.Reset;
1240
1241 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1242 l_msg_data := l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1243 END LOOP;
1244 log(l_msg_data);
1245 RETURN l_msg_data;
1246
1247 END logerror;
1248 END;