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