[Home] [Help]
PACKAGE BODY: APPS.HZ_DUP_PVT
Source
1 PACKAGE BODY HZ_DUP_PVT AS
2 /*$Header: ARHDUPBB.pls 120.43.12010000.3 2009/10/28 18:00:51 awu ship $*/
3
7
4 PROCEDURE log(
5 message IN VARCHAR2,
6 newline IN BOOLEAN DEFAULT TRUE);
8 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
9 RETURN VARCHAR2;
10
11 -- called by Data Librarian UI. Default master based on the profile.
12 -- Dup set has to be created first with random winner party, then change
13 -- winner party id to the one based on defaulting rule.
14
15 procedure default_master(
16 p_dup_set_id IN NUMBER,
17 x_master_party_id OUT NOCOPY NUMBER,
18 x_master_party_name OUT NOCOPY VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2 ) is
22
23 -- party with last update date
24 cursor get_def_master_lu_csr(cp_status varchar2) is
25 select party_id, party_name
26 from(
27 select p.party_id party_id, p.party_name party_name,
28 RANK() OVER (ORDER BY p.last_update_date desc ) rank
29 from hz_parties p, hz_dup_set_parties mp
30 where p.party_id = mp.dup_party_id
31 and mp.dup_set_id = p_dup_set_id
32 and p.status = cp_status
33 and nvl(mp.merge_flag,'Y') = 'Y'
34 )
35 where rank = 1 and rownum=1;
36
37
38 -- party with latest creation date
39 cursor get_def_master_lc_csr(cp_status varchar2) is
40 select party_id, party_name
41 from(
42 select p.party_id party_id,p.party_name party_name,
43 RANK() OVER (ORDER BY p.creation_date desc, p.party_id desc ) rank
44 from hz_parties p, hz_dup_set_parties mp
45 where p.party_id = mp.dup_party_id
46 and mp.dup_set_id = p_dup_set_id
47 and p.status = cp_status
48 and nvl(mp.merge_flag,'Y') = 'Y'
49 )
50 where rank = 1 and rownum=1;
51
52 -- party with earliest creation date
53 cursor get_def_master_ec_csr(cp_status varchar2) is
54 select party_id, party_name
55 from(
56 select p.party_id party_id,p.party_name party_name,
57 RANK() OVER (ORDER BY p.creation_date, p.party_id) rank
58 from hz_parties p, hz_dup_set_parties mp
59 where p.party_id = mp.dup_party_id
60 and mp.dup_set_id = p_dup_set_id
61 and p.status = cp_status
62 and nvl(mp.merge_flag,'Y') = 'Y'
63 )
64 where rank = 1 and rownum=1;
65
66
67 -- Party with Most Accounts
68 cursor get_def_master_macc_csr(cp_status varchar2) is
69 select party_id, party_name
70 from
71 (select party_id, party_name,rank() over (order by cnt desc) rank, last_update_date
72 from (
73 SELECT
74 ca.party_id party_id, party.party_name party_name,
75 count(*) over (partition by ca.party_id) as cnt,
76 ca.last_update_date
77 from
78 HZ_CUST_ACCOUNTS CA,HZ_PARTIES PARTY, HZ_DUP_SETS DS,
79 HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
80 WHERE CA.PARTY_ID =DSP.DUP_PARTY_ID
81 AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
82 AND DS.DUP_SET_ID = DSP.DUP_SET_ID
83 AND CA.PARTY_ID = PARTY.PARTY_ID
84 AND DSP.DUP_SET_ID= p_dup_set_id
85 AND nvl(dsp.merge_flag, 'Y') <> 'N'
86 and party.status = cp_status
87 )
88 order by last_update_date desc )
89 where rank = 1 and rownum=1;
90
91 -- Party with Most Addresses:
92 cursor get_def_master_maddr_csr(cp_status varchar2) is
93 select party_id, party_name
94 from
95 (select party_id, party_name,rank() over (order by cnt desc) rank, last_update_date
96 from (
97 SELECT
98 ps.party_id party_id, party.party_name party_name,
99 count(*) over (partition by ps.party_id) as cnt,
100 ps.last_update_date
101 from HZ_PARTY_SITES PS,HZ_PARTIES PARTY,
102 HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
103 WHERE PS.PARTY_ID =DSP.DUP_PARTY_ID
104 AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
105 AND DS.DUP_SET_ID = DSP.DUP_SET_ID
106 AND PS.PARTY_ID = PARTY.PARTY_ID
107 AND DSP.DUP_SET_ID= p_dup_set_id
108 AND nvl(dsp.merge_flag, 'Y') <> 'N'
109 and party.status = cp_status
110 )
111 order by last_update_date desc )
112 where rank = 1 and rownum=1;
113
114 -- Party with Most Relationships
115 cursor get_def_master_mrel_csr(cp_status varchar2) is
116 select party_id, party_name
117 from (
118 select party_id, party_name, rank() over (order by cnt desc) rank,
119 last_update_date
120 from ( SELECT
121 party.party_id party_id,
122 party.party_name party_name,
123 count(*) over (partition by party.party_id) as cnt,
124 party.last_update_date
125 from HZ_RELATIONSHIPS R,HZ_PARTIES PARTY, HZ_DUP_SETS DS,
126 HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
127 WHERE PARTY.PARTY_ID =DSP.DUP_PARTY_ID
128 AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
129 AND DS.DUP_SET_ID = DSP.DUP_SET_ID
130 AND R.OBJECT_ID = PARTY.PARTY_ID
131 AND DSP.DUP_SET_ID= p_dup_set_id
132 AND nvl(dsp.merge_flag, 'Y') <> 'N'
133 and party.status = cp_status
134 )
135 order by last_update_date desc )
136 where rank = 1 and rownum=1;
137
138 -- Party with highest Certification:
139 cursor get_def_master_hcert_csr(cp_status varchar2) is
140 select party_id, party_name
141 from(
142 select p.party_id party_id, p.party_name party_name,
143 RANK() OVER (ORDER BY p.certification_level, p.last_update_date desc ) rank
144 from hz_parties p, hz_dup_set_parties mp
145 where p.party_id = mp.dup_party_id
146 and mp.dup_set_id = p_dup_set_id
147 and p.status = cp_status
148 and nvl(mp.merge_flag,'Y') = 'Y'
149 )
150 where rank = 1 and rownum=1;
151
152 cursor get_active_party_count_csr is
153 select count(*)
154 from hz_parties p, hz_dup_set_parties mp
155 where p.party_id = mp.dup_party_id
156 and mp.dup_set_id = p_dup_set_id
157 and p.status = 'A';
158
159 cursor get_set_obj_num_csr is
160 select object_version_number
161 from HZ_DUP_SETS
162 where dup_set_id = p_dup_set_id;
163
164 -- validate party_id and party_name combination.
165 cursor check_set_party_exist_csr(cp_party_id number, cp_party_name varchar2) is
166 select count(*)
167 from HZ_DUP_SET_PARTIES dsp, hz_parties p
168 where dsp.dup_set_id = p_dup_set_id
169 and dsp.dup_party_id = p.party_id
170 and dsp.dup_party_id = cp_party_id
171 and p.party_name = cp_party_name;
172
173 -- Bug 4592273: only one active party in the set
174 cursor get_active_party_csr is
175 select p.party_id, p.party_name
176 from hz_parties p, hz_dup_set_parties mp
177 where p.party_id = mp.dup_party_id
178 and mp.dup_set_id = p_dup_set_id
179 and p.status = 'A'
180 and rownum = 1;
181
182
183 l_prof_value varchar2(30);
184 l_active_party_cnt number;
185 l_status varchar2(30);
186 l_set_obj_version_number number;
187 l_master_party_id number;
188 l_lu_master_party_id number;
189 l_lu_master_party_name varchar2(360);
190 l_active_party_id number;
191 l_active_party_name varchar2(360);
192 l_count number;
193
194 begin
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196 open get_active_party_count_csr;
197 fetch get_active_party_count_csr into l_active_party_cnt;
198 close get_active_party_count_csr;
199 if l_active_party_cnt = 0
200 then l_status := 'I';
201 else l_status := 'A';
202 end if;
203
204 /* default to last updated party if other profile returns null master*/
205 open get_def_master_lu_csr(l_status);
206 fetch get_def_master_lu_csr into l_lu_master_party_id, l_lu_master_party_name;
207 close get_def_master_lu_csr;
208
209 l_prof_value := nvl(fnd_profile.value('HZ_PARTY_MASTER_DEFAULT'),'MOST_ACCOUNTS');
210 if l_prof_value = 'MOST_ACCOUNTS'
211 then
212 open get_def_master_macc_csr(l_status);
213 fetch get_def_master_macc_csr into l_master_party_id, x_master_party_name;
214 close get_def_master_macc_csr;
215 elsif l_prof_value = 'LATEST_UPDATE_DATE'
216 then
217 l_master_party_id := l_lu_master_party_id;
218 x_master_party_name := l_lu_master_party_name;
219
220 elsif l_prof_value = 'LATEST_CREATION_DATE'
221 then
222 open get_def_master_lc_csr(l_status);
223 fetch get_def_master_lc_csr into l_master_party_id, x_master_party_name;
224 close get_def_master_lc_csr;
225 elsif l_prof_value = 'EARLIEST_CREATION_DATE'
226 then
227 open get_def_master_ec_csr(l_status);
228 fetch get_def_master_ec_csr into l_master_party_id, x_master_party_name;
229 close get_def_master_ec_csr;
230 elsif l_prof_value = 'MOST_ADDRESSES'
231 then
232 open get_def_master_maddr_csr(l_status);
233 fetch get_def_master_maddr_csr into l_master_party_id, x_master_party_name;
234 close get_def_master_maddr_csr;
235 elsif l_prof_value = 'MOST_RELATIONSHIPS'
236 then
237 open get_def_master_mrel_csr(l_status);
238 fetch get_def_master_mrel_csr into l_master_party_id, x_master_party_name;
239 close get_def_master_mrel_csr;
240 elsif l_prof_value = 'HIGHEST_CERTIFICATION'
241 then
242 open get_def_master_hcert_csr(l_status);
243 fetch get_def_master_hcert_csr into l_master_party_id, x_master_party_name;
244 close get_def_master_hcert_csr;
245 elsif l_prof_value = 'USER_HOOK'
246 then
247 HZ_USER_HOOK_PKG.default_master_user_hook(
248 p_dup_set_id => p_dup_set_id,
249 x_master_party_id => l_master_party_id,
250 x_master_party_name => x_master_party_name,
251 x_return_status => x_return_status,
255 -- validate master party id and master party name
252 x_msg_count => x_msg_count,
253 x_msg_data => x_msg_data );
254
256 open check_set_party_exist_csr(l_master_party_id,x_master_party_name);
257 fetch check_set_party_exist_csr into l_count;
258 close check_set_party_exist_csr;
259
260 IF l_count = 0 or l_master_party_id is null or x_master_party_name is null THEN
261 fnd_message.set_name('AR', 'HZ_DL_USER_HOOK_ERR');
262 fnd_msg_pub.add;
263 x_return_status := fnd_api.g_ret_sts_error;
264 RAISE FND_API.G_EXC_ERROR;
265 end if;
266
267 end if;
268
269 open get_set_obj_num_csr;
270 fetch get_set_obj_num_csr into l_set_obj_version_number;
271 close get_set_obj_num_csr;
272
273
274 if l_master_party_id is null
275 then
276 if l_active_party_cnt = 1 -- only one active party
277 then
278 open get_active_party_csr;
279 fetch get_active_party_csr into l_active_party_id, l_active_party_name;
280 close get_active_party_csr;
281 l_master_party_id := l_active_party_id;
282 x_master_party_name := l_active_party_name;
283 else
284 l_master_party_id := l_lu_master_party_id;
285 x_master_party_name := l_lu_master_party_name;
286 end if;
287 end if;
288
289 if l_master_party_id is not null -- should not be null in general
290 then
291
292 -- update the winner party id to have merge_flag = 'Y'
293
294 update hz_dup_set_parties
295 set merge_flag = 'Y'
296 where dup_set_id = p_dup_set_id
297 and dup_party_id = l_master_party_id;
298
299 update_winner_party (
300 p_dup_set_id => p_dup_set_id
301 ,p_winner_party_id => l_master_party_id
302 ,px_set_obj_version_number => l_set_obj_version_number
303 ,x_return_status => x_return_status
304 ,x_msg_count => x_msg_count
305 ,x_msg_data => x_msg_data );
306 end if;
307
308 x_master_party_id := l_master_party_id;
309
310
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315
316 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
317 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
318 FND_MSG_PUB.ADD;
319
320 FND_MSG_PUB.Count_And_Get(
321 p_encoded => FND_API.G_FALSE,
322 p_count => x_msg_count,
323 p_data => x_msg_data);
324
325 END default_master;
326
327
328 -- This procedure create one dup batch, one dup set and one dup set party record
329 PROCEDURE create_dup (
330 dup_batch_name IN VARCHAR2
331 ,match_rule_id IN NUMBER
332 ,application_id IN NUMBER
333 ,request_type IN VARCHAR2
334 ,winner_party_id IN NUMBER
335 ,status IN VARCHAR2
336 ,assigned_to_user_id IN NUMBER
337 ,merge_type IN VARCHAR2
338 ,party_id IN NUMBER
339 ,score IN NUMBER
340 ,merge_flag IN VARCHAR2
341 ,not_dup IN VARCHAR2
342 ,merge_seq_id IN NUMBER
343 ,merge_batch_id IN NUMBER
344 ,merge_batch_name IN VARCHAR2
345 ,x_dup_batch_id OUT NOCOPY NUMBER
346 ,x_dup_set_id OUT NOCOPY NUMBER
347 ,x_return_status OUT NOCOPY VARCHAR2
348 ,x_msg_count OUT NOCOPY NUMBER
349 ,x_msg_data OUT NOCOPY VARCHAR2
350 ) IS
351
352 l_dup_batch_rec DUP_BATCH_REC_TYPE;
353 l_dup_set_rec DUP_SET_REC_TYPE;
354 l_dup_set_party_tbl DUP_PARTY_TBL_TYPE;
355
356 l_return_status VARCHAR2(30);
357 l_msg_data VARCHAR2(2000);
358 l_msg_count NUMBER;
359
360 BEGIN
361
362 savepoint create_dup_pvt;
363 FND_MSG_PUB.initialize;
364
365 --Initialize API return status to success.
366 x_return_status := FND_API.G_RET_STS_SUCCESS;
367
368 l_dup_batch_rec.dup_batch_name := dup_batch_name;
369 l_dup_batch_rec.match_rule_id := match_rule_id;
370 l_dup_batch_rec.application_id := application_id;
371 l_dup_batch_rec.request_type := request_type;
372 l_dup_set_rec.winner_party_id := winner_party_id;
373 l_dup_set_rec.status := status;
374 l_dup_set_rec.assigned_to_user_id := assigned_to_user_id;
375 l_dup_set_rec.merge_type := merge_type;
376 IF(party_id IS NOT NULL) THEN
377 l_dup_set_party_tbl(1).party_id := party_id;
378 l_dup_set_party_tbl(1).score := score;
379 l_dup_set_party_tbl(1).merge_flag := merge_flag;
380 l_dup_set_party_tbl(1).not_dup := not_dup;
381 l_dup_set_party_tbl(1).merge_seq_id := merge_seq_id;
382 l_dup_set_party_tbl(1).merge_batch_id := merge_batch_id;
383 l_dup_set_party_tbl(1).merge_batch_name := merge_batch_name;
384 END IF;
385
386 create_dup_batch(
387 p_dup_batch_rec => l_dup_batch_rec
388 ,p_dup_set_rec => l_dup_set_rec
389 ,p_dup_party_tbl => l_dup_set_party_tbl
390 ,x_dup_batch_id => x_dup_batch_id
391 ,x_dup_set_id => x_dup_set_id
392 ,x_return_status => x_return_status
393 ,x_msg_count => x_msg_count
394 ,x_msg_data => x_msg_data );
395
396 EXCEPTION
397
398 WHEN FND_API.G_EXC_ERROR THEN
399 ROLLBACK TO create_dup_pvt;
403 p_count => x_msg_count,
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 FND_MSG_PUB.Count_And_Get(
402 p_encoded => FND_API.G_FALSE,
404 p_data => x_msg_data);
405
406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
407 ROLLBACK TO create_dup_pvt;
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 FND_MSG_PUB.Count_And_Get(
410 p_encoded => FND_API.G_FALSE,
411 p_count => x_msg_count,
412 p_data => x_msg_data);
413
414 WHEN OTHERS THEN
415 ROLLBACK TO create_dup_pvt;
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
417 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
418 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
419 FND_MSG_PUB.ADD;
420 FND_MSG_PUB.Count_And_Get(
421 p_encoded => FND_API.G_FALSE,
422 p_count => x_msg_count,
423 p_data => x_msg_data);
424
425 END create_dup;
426
427 -- This procedure create only dup set party record
428 PROCEDURE create_dup_set_party (
429 p_dup_set_id IN NUMBER
430 ,p_dup_set_party_id IN NUMBER
431 ,p_score IN NUMBER
432 ,p_merge_flag IN VARCHAR2
433 ,p_not_dup IN VARCHAR2
434 ,p_merge_seq_id IN NUMBER
435 ,p_merge_batch_id IN NUMBER
436 ,p_merge_batch_name IN VARCHAR2
437 ,x_return_status OUT NOCOPY VARCHAR2
438 ,x_msg_count OUT NOCOPY NUMBER
439 ,x_msg_data OUT NOCOPY VARCHAR2
440 ) IS
441
442 l_dup_set_id NUMBER;
443 l_dup_set_party_id NUMBER;
444 l_score NUMBER;
445 l_not_dup VARCHAR2(1);
446 l_merge_seq_id NUMBER;
447 l_merge_batch_id NUMBER;
448 l_merge_batch_name VARCHAR2(30);
449 l_winner_type VARCHAR2(30);
450 l_cand_type VARCHAR2(30);
451
452 cursor get_winner_party_type(l_dset_id NUMBER) is
453 select party_type
454 from HZ_PARTIES a, HZ_DUP_SETS b
455 where a.party_id = b.winner_party_id
456 and b.dup_set_id = l_dset_id;
457
458 cursor get_party_type(l_party_id NUMBER) is
459 select party_type
460 from HZ_PARTIES
461 where party_id = l_party_id;
462
463 BEGIN
464
465 savepoint create_dup_set_party_pvt;
466 FND_MSG_PUB.initialize;
467
468 --Initialize API return status to success.
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470
471 IF(p_dup_set_id IS NULL OR p_dup_set_id = FND_API.G_MISS_NUM) THEN
472 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
473 FND_MESSAGE.SET_TOKEN('COLUMN' ,'DUP_SET_ID');
474 FND_MSG_PUB.ADD;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 IF(p_dup_set_party_id IS NULL OR p_dup_set_party_id = FND_API.G_MISS_NUM) THEN
479 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
480 FND_MESSAGE.SET_TOKEN('COLUMN' ,'DUP_SET_PARTY_ID');
481 FND_MSG_PUB.ADD;
482 RAISE FND_API.G_EXC_ERROR;
483 END IF;
484
485 l_dup_set_id := p_dup_set_id;
486 l_dup_set_party_id := p_dup_set_party_id;
487
488 OPEN get_winner_party_type(l_dup_set_id);
489 FETCH get_winner_party_type into l_winner_type;
490 CLOSE get_winner_party_type;
491 ----Commented for Bug 5552118
492 /*OPEN get_party_type(l_dup_set_party_id);
493 FETCH get_party_type into l_cand_type;
494 CLOSE get_party_type;
495
496 IF NOT (l_winner_type = l_cand_type) THEN
497 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
498 -- parties have different type in a merge request
499 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_PARTIES');
500 FND_MSG_PUB.ADD;
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503 END IF;*/
504 ----Commented for Bug 5552118
505
506 l_score := p_score;
507 l_not_dup := p_not_dup;
508 l_merge_seq_id := p_merge_seq_id;
509 l_merge_batch_id := p_merge_batch_id;
510 l_merge_batch_name := p_merge_batch_name;
511
512 HZ_DUP_SET_PARTIES_PKG.Insert_Row(
513 p_dup_party_id => l_dup_set_party_id
514 ,p_dup_set_id => l_dup_set_id
515 ,p_merge_flag => 'Y'
516 ,p_not_dup => l_not_dup
517 ,p_score => l_score
518 ,p_merge_seq_id => l_merge_seq_id
519 ,p_merge_batch_id => l_merge_batch_id
520 ,p_merge_batch_name => l_merge_batch_name
521 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
522 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
523 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
524 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
525 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
526 );
527
528 EXCEPTION
529
530 WHEN FND_API.G_EXC_ERROR THEN
531 ROLLBACK TO create_dup_set_party_pvt;
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 FND_MSG_PUB.Count_And_Get(
534 p_encoded => FND_API.G_FALSE,
535 p_count => x_msg_count,
536 p_data => x_msg_data);
537
538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
539 ROLLBACK TO create_dup_set_party_pvt;
540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
541 FND_MSG_PUB.Count_And_Get(
542 p_encoded => FND_API.G_FALSE,
543 p_count => x_msg_count,
544 p_data => x_msg_data);
545
546 WHEN OTHERS THEN
550 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
547 ROLLBACK TO create_dup_set_party_pvt;
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
551 FND_MSG_PUB.ADD;
552 FND_MSG_PUB.Count_And_Get(
553 p_encoded => FND_API.G_FALSE,
554 p_count => x_msg_count,
555 p_data => x_msg_data);
556
557 END create_dup_set_party;
558
559 -- create records in dup batch, dup set and dup set parties
560 PROCEDURE create_dup_batch (
561 p_dup_batch_rec IN DUP_BATCH_REC_TYPE
562 ,p_dup_set_rec IN DUP_SET_REC_TYPE
563 ,p_dup_party_tbl IN DUP_PARTY_TBL_TYPE
564 ,x_dup_batch_id OUT NOCOPY NUMBER
565 ,x_dup_set_id OUT NOCOPY NUMBER
566 ,x_return_status OUT NOCOPY VARCHAR2
567 ,x_msg_count OUT NOCOPY NUMBER
568 ,x_msg_data OUT NOCOPY VARCHAR2
569 ) IS
570
571 l_dup_batch_id NUMBER := null;
572 l_dup_set_id NUMBER := null;
573
574 l_party_id NUMBER;
575 l_temp VARCHAR2(1);
576 l_party_type VARCHAR2(30);
577 l_temp_type VARCHAR2(30);
578
579 cursor get_party_type(l_party_id NUMBER) is
580 select party_type
581 from HZ_PARTIES
582 where party_id = l_party_id;
583
584 BEGIN
585
586 savepoint create_dup_batch_pvt;
587
588 FND_MSG_PUB.initialize;
589
590 --Initialize API return status to success.
591 x_return_status := FND_API.G_RET_STS_SUCCESS;
592
593 IF(p_dup_batch_rec.match_rule_id IS NULL OR
594 p_dup_batch_rec.match_rule_id = FND_API.G_MISS_NUM) THEN
595 FND_MESSAGE.SET_NAME( 'AR', 'HZ_NO_MATCH_RULE' );
596 FND_MSG_PUB.ADD;
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599
600 IF(p_dup_batch_rec.request_type IS NULL OR
601 p_dup_batch_rec.request_type = FND_API.G_MISS_CHAR) THEN
602 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
603 FND_MESSAGE.SET_TOKEN('COLUMN' ,'REQUEST_TYPE');
604 FND_MSG_PUB.ADD;
605 RAISE FND_API.G_EXC_ERROR;
606 END IF;
607
608 IF(p_dup_set_rec.status IS NULL OR
609 p_dup_set_rec.status = FND_API.G_MISS_CHAR) THEN
610 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
611 FND_MESSAGE.SET_TOKEN('COLUMN' ,'STATUS');
612 FND_MSG_PUB.ADD;
613 RAISE FND_API.G_EXC_ERROR;
614 END IF;
615
616 IF(p_dup_set_rec.merge_type = 'PARTY_MERGE') THEN
617
618 -- get the first party type
619 OPEN get_party_type(p_dup_party_tbl(1).party_id);
620 FETCH get_party_type INTO l_party_type;
621 CLOSE get_party_type;
622
623 -- check if the first party type is different than others
624 FOR i IN 2..p_dup_party_tbl.count LOOP
625 OPEN get_party_type(p_dup_party_tbl(i).party_id);
626 FETCH get_party_type INTO l_temp_type;
627 CLOSE get_party_type;
628 IF NOT (l_party_type = l_temp_type) THEN
629 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
630 -- parties have different type in a merge request
631 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_PARTIES');
632 FND_MSG_PUB.ADD;
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635 END IF;
636 END LOOP;
637 END IF;
638
639 HZ_DUP_BATCH_PKG.Insert_Row(
640 px_dup_batch_id => l_dup_batch_id
641 ,p_dup_batch_name => p_dup_batch_rec.dup_batch_name
642 ,p_match_rule_id => p_dup_batch_rec.match_rule_id
643 ,p_application_id => p_dup_batch_rec.application_id
644 ,p_request_type => p_dup_batch_rec.request_type
645 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
646 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
647 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
648 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
649 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
650 );
651
652 x_dup_batch_id := l_dup_batch_id;
653
654 HZ_DUP_SETS_PKG.Insert_Row(
655 px_dup_set_id => l_dup_set_id
656 ,p_dup_batch_id => l_dup_batch_id
657 ,p_winner_party_id => p_dup_set_rec.winner_party_id
658 ,p_status => 'SYSBATCH'
659 ,p_assigned_to_user_id => p_dup_set_rec.assigned_to_user_id
660 ,p_merge_type => p_dup_set_rec.merge_type
661 ,p_object_version_number => 1
662 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
663 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
664 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
665 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
666 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
667 );
668
669 x_dup_set_id := l_dup_set_id;
670
671 FOR i IN 1..p_dup_party_tbl.count LOOP
672 l_party_id := p_dup_party_tbl(i).party_id;
673 HZ_DUP_SET_PARTIES_PKG.Insert_Row(
674 p_dup_party_id => l_party_id
675 ,p_dup_set_id => l_dup_set_id
676 ,p_merge_flag => 'Y'
677 ,p_not_dup => p_dup_party_tbl(i).not_dup
678 ,p_score => p_dup_party_tbl(i).score
679 ,p_merge_seq_id => p_dup_party_tbl(i).merge_seq_id
680 ,p_merge_batch_id => p_dup_party_tbl(i).merge_batch_id
681 ,p_merge_batch_name => p_dup_party_tbl(i).merge_batch_name
682 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
686 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
683 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
684 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
685 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
687 );
688 END LOOP;
689
690 EXCEPTION
691
692 WHEN FND_API.G_EXC_ERROR THEN
693 ROLLBACK TO create_dup_batch_pvt;
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 FND_MSG_PUB.Count_And_Get(
696 p_encoded => FND_API.G_FALSE,
697 p_count => x_msg_count,
698 p_data => x_msg_data);
699
700 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
701 ROLLBACK TO create_dup_batch_pvt;
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 FND_MSG_PUB.Count_And_Get(
704 p_encoded => FND_API.G_FALSE,
705 p_count => x_msg_count,
706 p_data => x_msg_data);
707
708 WHEN OTHERS THEN
709 ROLLBACK TO create_dup_batch_pvt;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
712 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
713 FND_MSG_PUB.ADD;
714 FND_MSG_PUB.Count_And_Get(
715 p_encoded => FND_API.G_FALSE,
716 p_count => x_msg_count,
717 p_data => x_msg_data);
718
719 END create_dup_batch;
720
721 -- create records in dup set and dup set parties
722 PROCEDURE create_dup_set (
723 p_dup_set_rec IN DUP_SET_REC_TYPE
724 ,p_dup_party_tbl IN DUP_PARTY_TBL_TYPE
725 ,x_dup_set_id OUT NOCOPY NUMBER
726 ,x_return_status OUT NOCOPY VARCHAR2
727 ,x_msg_count OUT NOCOPY NUMBER
728 ,x_msg_data OUT NOCOPY VARCHAR2
729 ) IS
730
731 l_dup_set_id NUMBER := null;
732 l_party_id NUMBER;
733 l_temp VARCHAR2(1);
734
735 cursor check_dup_batch_id is
736 select 'X'
737 from HZ_DUP_BATCH
738 where dup_batch_id = p_dup_set_rec.dup_batch_id;
739
740 BEGIN
741
742 savepoint create_dup_set_pvt;
743
744 FND_MSG_PUB.initialize;
745
746 --Initialize API return status to success.
747 x_return_status := FND_API.G_RET_STS_SUCCESS;
748
749 IF(p_dup_set_rec.dup_batch_id IS NULL OR
750 p_dup_set_rec.dup_batch_id = FND_API.G_MISS_NUM) THEN
751 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
752 FND_MESSAGE.SET_TOKEN('COLUMN' ,'DUP_BATCH_ID');
753 FND_MSG_PUB.ADD;
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756
757 OPEN check_dup_batch_id;
758 FETCH check_dup_batch_id INTO l_temp;
759 IF(check_dup_batch_id%NOTFOUND) THEN
760 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_DUP_BATCH' );
761 FND_MSG_PUB.ADD;
762 RAISE FND_API.G_EXC_ERROR;
763 END IF;
764
765 IF(p_dup_set_rec.status IS NULL OR
766 p_dup_set_rec.status = FND_API.G_MISS_CHAR) THEN
767 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
768 FND_MESSAGE.SET_TOKEN('COLUMN' ,'STATUS');
769 FND_MSG_PUB.ADD;
770 RAISE FND_API.G_EXC_ERROR;
771 END IF;
772
773 HZ_DUP_SETS_PKG.Insert_Row(
774 px_dup_set_id => l_dup_set_id
775 ,p_dup_batch_id => p_dup_set_rec.dup_batch_id
776 ,p_winner_party_id => p_dup_set_rec.winner_party_id
777 ,p_status => 'SYSBATCH'
778 ,p_assigned_to_user_id => p_dup_set_rec.assigned_to_user_id
779 ,p_merge_type => p_dup_set_rec.merge_type
780 ,p_object_version_number => 1
781 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
782 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
783 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
784 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
785 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
786 );
787
788 x_dup_set_id := l_dup_set_id;
789
790 FOR i IN 1..p_dup_party_tbl.count LOOP
791 l_party_id := p_dup_party_tbl(i).party_id;
792 HZ_DUP_SET_PARTIES_PKG.Insert_Row(
793 p_dup_party_id => l_party_id
794 ,p_dup_set_id => l_dup_set_id
795 ,p_merge_flag => 'Y'
796 ,p_not_dup => p_dup_party_tbl(i).not_dup
797 ,p_score => p_dup_party_tbl(i).score
798 ,p_merge_seq_id => p_dup_party_tbl(i).merge_seq_id
799 ,p_merge_batch_id => p_dup_party_tbl(i).merge_batch_id
800 ,p_merge_batch_name => p_dup_party_tbl(i).merge_batch_name
801 ,p_created_by => HZ_UTILITY_V2PUB.CREATED_BY
802 ,p_creation_date => HZ_UTILITY_V2PUB.CREATION_DATE
803 ,p_last_update_login => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
804 ,p_last_update_date => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
805 ,p_last_updated_by => HZ_UTILITY_V2PUB.LAST_UPDATED_BY
806 );
807 END LOOP;
808
809 EXCEPTION
810
811 WHEN FND_API.G_EXC_ERROR THEN
812 ROLLBACK TO create_dup_set_pvt;
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 FND_MSG_PUB.Count_And_Get(
815 p_encoded => FND_API.G_FALSE,
816 p_count => x_msg_count,
817 p_data => x_msg_data);
818
819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
820 ROLLBACK TO create_dup_set_pvt;
821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
822 FND_MSG_PUB.Count_And_Get(
823 p_encoded => FND_API.G_FALSE,
824 p_count => x_msg_count,
825 p_data => x_msg_data);
826
827 WHEN OTHERS THEN
828 ROLLBACK TO create_dup_set_pvt;
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
831 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
832 FND_MSG_PUB.ADD;
833 FND_MSG_PUB.Count_And_Get(
834 p_encoded => FND_API.G_FALSE,
835 p_count => x_msg_count,
836 p_data => x_msg_data);
837
838 END create_dup_set;
839
840 -- update winner_party_id in HZ_DUP_SETS table
841 -- and swap the master and candidate
842 PROCEDURE update_winner_party (
843 p_dup_set_id IN NUMBER
844 ,p_winner_party_id IN NUMBER
845 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
846 ,x_return_status OUT NOCOPY VARCHAR2
847 ,x_msg_count OUT NOCOPY NUMBER
848 ,x_msg_data OUT NOCOPY VARCHAR2
849 ) IS
850
851 l_temp VARCHAR2(1);
852 l_set_obj_version_number NUMBER;
853 l_old_winner_party_id NUMBER;
854
855 cursor check_winner_party_exist is
856 select 'X'
857 from HZ_DUP_SET_PARTIES
858 where dup_set_id = p_dup_set_id
859 and dup_party_id = p_winner_party_id
860 and nvl(merge_flag,'Y') = 'Y';
861
862 cursor get_dup_sets_info is
863 select winner_party_id, object_version_number
864 from HZ_DUP_SETS
865 where dup_set_id = p_dup_set_id;
866
867 BEGIN
868
869 savepoint update_winner_party_pvt;
870
871 FND_MSG_PUB.initialize;
872 --Initialize API return status to success.
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 OPEN check_winner_party_exist;
876 FETCH check_winner_party_exist INTO l_temp;
877 IF check_winner_party_exist%NOTFOUND THEN
878 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
879 -- new winner party_id cannot be found in dup_set_parties
880 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_WINNER_PARTY');
881 FND_MSG_PUB.ADD;
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884 END IF;
885 CLOSE check_winner_party_exist;
886
887 OPEN get_dup_sets_info;
888 FETCH get_dup_sets_info into
889 l_old_winner_party_id
890 ,l_set_obj_version_number;
891 CLOSE get_dup_sets_info;
892
893 -- check last_update_date of hz_dup_sets, not hz_dup_set_parties
894 IF (l_set_obj_version_number IS NOT NULL) THEN
895 IF (l_set_obj_version_number <> px_set_obj_version_number) THEN
896 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
897 -- row has been changed by another user.
898 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
899 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DUP_SETS');
900 FND_MSG_PUB.ADD;
901 RAISE FND_API.G_EXC_ERROR;
902 END IF;
903 END IF;
904 END IF;
905
906 px_set_obj_version_number := nvl(l_set_obj_version_number,1)+1;
907
908 -- Swap master and candidate
909 -- in hz_dup_set_parties, winner party has merge_flag = null and other
910 -- candidate has merge_flag = 'Y'. So, swapping master and candidate
911 -- is actually updating the merge_flag
912
913 -- Update old winner party. Set merge_flag = 'Y'
914 UPDATE HZ_DUP_SET_PARTIES
915 SET merge_flag = 'Y'
916 , last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
917 , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
918 , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
919 WHERE dup_party_id = l_old_winner_party_id
920 AND dup_set_id = p_dup_set_id;
921
922 -- no need to set the merge flag back to NULL for master party
923 UPDATE HZ_DUP_SET_PARTIES
924 SET last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
925 , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
926 , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
927 WHERE dup_party_id = p_winner_party_id
928 AND dup_set_id = p_dup_set_id;
929
930 BEGIN
931 -- clean up merge batch tables
932 DELETE HZ_MERGE_PARTY_DETAILS
933 WHERE batch_party_id
934 in (select batch_party_id
935 from HZ_MERGE_PARTIES mp
936 where mp.batch_id = p_dup_set_id);
937 DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;
941 WHEN NO_DATA_FOUND THEN
938 DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;
939 DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;
940 EXCEPTION
942 NULL;
943 END;
944
945 -- not doing any update to HZ_DUP_SETS status since user may
946 -- be in create merge request flow and status should remain
947 -- the same. For merge multiple party flow, the update call
948 -- will be followed by submit_dup call which will update the
949 -- status to 'PREPROCESS' when successfully call conc request
950
951 UPDATE HZ_DUP_SETS
952 SET winner_party_id = p_winner_party_id
953 , last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
954 , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
955 , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
956 , object_version_number = px_set_obj_version_number
957 WHERE dup_set_id = p_dup_set_id;
958
959 EXCEPTION
960
961 WHEN FND_API.G_EXC_ERROR THEN
962 ROLLBACK TO update_winner_party_pvt;
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 FND_MSG_PUB.Count_And_Get(
965 p_encoded => FND_API.G_FALSE,
966 p_count => x_msg_count,
967 p_data => x_msg_data);
968
969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
970 ROLLBACK TO update_winner_party_pvt;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 FND_MSG_PUB.Count_And_Get(
973 p_encoded => FND_API.G_FALSE,
974 p_count => x_msg_count,
975 p_data => x_msg_data);
976
977 WHEN OTHERS THEN
978 ROLLBACK TO update_winner_party_pvt;
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
980 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
981 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
982 FND_MSG_PUB.ADD;
983 FND_MSG_PUB.Count_And_Get(
984 p_encoded => FND_API.G_FALSE,
985 p_count => x_msg_count,
986 p_data => x_msg_data);
987
988 END update_winner_party;
989
990 PROCEDURE delete_dup_party (
991 p_dup_set_id IN NUMBER
992 ,p_dup_party_id IN NUMBER
993 ,p_new_winner_party_id IN NUMBER
994 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
995 ,x_return_status OUT NOCOPY VARCHAR2
996 ,x_msg_count OUT NOCOPY NUMBER
997 ,x_msg_data OUT NOCOPY VARCHAR2
998 ) IS
999
1000 CURSOR get_dup_sets_info IS
1001 select winner_party_id, object_version_number
1002 from HZ_DUP_SETS
1003 where dup_set_id = p_dup_set_id;
1004
1005 CURSOR check_winner_party(x_party_id NUMBER) IS
1006 select 'X'
1007 from HZ_DUP_SETS
1008 where winner_party_id = x_party_id
1009 and dup_set_id = p_dup_set_id;
1010
1011 CURSOR check_dup_party(x_party_id NUMBER) IS
1012 select 'X'
1013 from HZ_DUP_SET_PARTIES
1014 where dup_set_id = p_dup_set_id
1015 and dup_party_id = x_party_id;
1016
1017 l_check VARCHAR2(1);
1018 l_winner_party_id NUMBER;
1019 l_set_obj_version_number NUMBER;
1020
1021 BEGIN
1022
1023 savepoint delete_dup_party_pvt;
1024
1025 FND_MSG_PUB.initialize;
1026 --Initialize API return status to success.
1027 x_return_status := FND_API.G_RET_STS_SUCCESS;
1028
1029 OPEN get_dup_sets_info;
1030 FETCH get_dup_sets_info into
1031 l_winner_party_id
1032 ,l_set_obj_version_number;
1033 CLOSE get_dup_sets_info;
1034
1035 -- check last_update_date of hz_dup_sets, not hz_dup_set_parties
1036 IF (l_set_obj_version_number IS NOT NULL) THEN
1037 IF (l_set_obj_version_number <> px_set_obj_version_number) THEN
1038 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1039 -- row has been changed by another user.
1040 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1041 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DUP_SETS');
1042 FND_MSG_PUB.ADD;
1043 RAISE FND_API.G_EXC_ERROR;
1044 END IF;
1045 END IF;
1046 END IF;
1047
1048 px_set_obj_version_number := nvl(l_set_obj_version_number,1)+1;
1049
1050 IF(p_new_winner_party_id IS NULL) THEN
1051 -- delete candidate only
1052 OPEN check_winner_party(p_dup_party_id);
1053 FETCH check_winner_party INTO l_check;
1054 IF check_winner_party%FOUND THEN
1055 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1056 -- cannot remove winner party without specifying a new winner party
1057 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1058 FND_MESSAGE.SET_TOKEN('COLUMN' ,'NEW_WINNER_PARTY_ID');
1059 FND_MSG_PUB.ADD;
1060 RAISE FND_API.G_EXC_ERROR;
1061 END IF;
1062 END IF;
1063 CLOSE check_winner_party;
1064
1065 UPDATE HZ_DUP_SETS
1066 set object_version_number = px_set_obj_version_number
1067 , last_update_date = hz_utility_v2pub.last_update_date
1068 , last_updated_by = hz_utility_v2pub.last_updated_by
1069 , last_update_login = hz_utility_v2pub.last_update_login
1070 where dup_set_id = p_dup_set_id;
1071
1072 -- remove dup party
1073 UPDATE HZ_DUP_SET_PARTIES
1074 SET merge_flag = 'N'
1075 , last_update_date = hz_utility_v2pub.last_update_date
1076 , last_updated_by = hz_utility_v2pub.last_updated_by
1077 , last_update_login = hz_utility_v2pub.last_update_login
1078 WHERE dup_set_id = p_dup_set_id
1079 AND dup_party_id = p_dup_party_id;
1080
1081 ELSE
1082 -- delete winner party and specify new winner party also
1086 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1083 OPEN check_winner_party(p_dup_party_id);
1084 FETCH check_winner_party INTO l_check;
1085 IF check_winner_party%FOUND THEN
1087 -- cannot remove winner party without specifying a new winner party
1088 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_WINNER_PARTY');
1089 FND_MSG_PUB.ADD;
1090 RAISE FND_API.G_EXC_ERROR;
1091 END IF;
1092 END IF;
1093 CLOSE check_winner_party;
1094
1095 OPEN check_dup_party(p_new_winner_party_id);
1096 FETCH check_dup_party INTO l_check;
1097 IF check_dup_party%NOTFOUND THEN
1098 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1099 -- cannot remove winner party without specifying a new winner party
1100 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DUP_PARTY');
1101 FND_MSG_PUB.ADD;
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104 END IF;
1105 CLOSE check_dup_party;
1106
1107 UPDATE HZ_DUP_SETS
1108 set winner_party_id = p_new_winner_party_id
1109 , object_version_number = px_set_obj_version_number
1110 , last_update_date = hz_utility_v2pub.last_update_date
1111 , last_updated_by = hz_utility_v2pub.last_updated_by
1112 , last_update_login = hz_utility_v2pub.last_update_login
1113 where dup_set_id = p_dup_set_id;
1114
1115 UPDATE HZ_DUP_SET_PARTIES
1116 SET merge_flag = 'N'
1117 , last_update_date = hz_utility_v2pub.last_update_date
1118 , last_updated_by = hz_utility_v2pub.last_updated_by
1119 , last_update_login = hz_utility_v2pub.last_update_login
1120 WHERE dup_set_id = p_dup_set_id
1121 AND dup_party_id = p_dup_party_id;
1122
1123 END IF;
1124
1125 BEGIN
1126 -- clean up merge batch tables
1127 DELETE HZ_MERGE_PARTY_DETAILS
1128 WHERE batch_party_id
1129 in (select batch_party_id
1130 from HZ_MERGE_PARTIES mp
1131 where mp.batch_id = p_dup_set_id);
1132 DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;
1133 DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;
1134 DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;
1135 EXCEPTION
1136 WHEN NO_DATA_FOUND THEN
1137 NULL;
1138 END;
1139
1140 EXCEPTION
1141
1142 WHEN FND_API.G_EXC_ERROR THEN
1143 ROLLBACK TO delete_dup_party_pvt;
1144 x_return_status := FND_API.G_RET_STS_ERROR;
1145 FND_MSG_PUB.Count_And_Get(
1146 p_encoded => FND_API.G_FALSE,
1147 p_count => x_msg_count,
1148 p_data => x_msg_data);
1149
1150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1151 ROLLBACK TO delete_dup_party_pvt;
1152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1153 FND_MSG_PUB.Count_And_Get(
1154 p_encoded => FND_API.G_FALSE,
1155 p_count => x_msg_count,
1156 p_data => x_msg_data);
1157
1158 WHEN OTHERS THEN
1159 ROLLBACK TO delete_dup_party_pvt;
1160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1161 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1162 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1163 FND_MSG_PUB.ADD;
1164 FND_MSG_PUB.Count_And_Get(
1165 p_encoded => FND_API.G_FALSE,
1166 p_count => x_msg_count,
1167 p_data => x_msg_data);
1168
1169 END delete_dup_party;
1170
1171 --
1172 -- Reset merge_type of HZ_DUP_SETS and hard delete candidate which has merge_flag = 'N'
1173 -- this procedure is used in system duplicate identification flow
1174 --
1175 -- If a duplicate set found by system duplicate identification program has more than
1176 -- one party involved, the merge_type of this dup set is set to PARTY_MERGE. Howvever,
1177 -- on UI, user is allowed to remove candidates from a dup set. If user removed all
1178 -- candidates except master and click submit to create merge request. The merge_type of
1179 -- dup set should be restamped as 'SAME_PARTY_MERGE' for single party. All candidates
1180 -- should be removed except the master in HZ_DUP_SET_PARTIES.
1181 --
1182 PROCEDURE reset_merge_type (
1183 p_dup_set_id IN NUMBER
1184 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
1185 ,x_return_status OUT NOCOPY VARCHAR2
1186 ,x_msg_count OUT NOCOPY NUMBER
1187 ,x_msg_data OUT NOCOPY VARCHAR2
1188 ) IS
1189
1190 CURSOR get_dup_sets_info IS
1191 select winner_party_id, object_version_number
1192 from HZ_DUP_SETS
1193 where dup_set_id = p_dup_set_id;
1194
1195 CURSOR check_only_master IS
1196 select count(1)
1197 from HZ_DUP_SET_PARTIES
1198 where dup_set_id = p_dup_set_id;
1199
1200 CURSOR check_winner_party IS
1201 select 'X'
1202 from HZ_DUP_SETS
1203 where winner_party_id =
1204 ( select dup_party_id
1205 from HZ_DUP_SET_PARTIES
1206 where dup_set_id = p_dup_set_id
1207 and nvl(merge_flag,'Y') <> 'N' )
1208 and dup_set_id = p_dup_set_id;
1209
1210 l_check VARCHAR2(1);
1211 l_winner_party_id NUMBER;
1212 l_set_obj_version_number NUMBER;
1213 l_count NUMBER;
1214
1215 BEGIN
1216
1217 savepoint reset_merge_type_pvt;
1218
1219 FND_MSG_PUB.initialize;
1220 --Initialize API return status to success.
1221 x_return_status := FND_API.G_RET_STS_SUCCESS;
1222
1223 OPEN get_dup_sets_info;
1224 FETCH get_dup_sets_info into
1225 l_winner_party_id
1226 ,l_set_obj_version_number;
1227 CLOSE get_dup_sets_info;
1228
1232 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1229 -- check last_update_date of hz_dup_sets, not hz_dup_set_parties
1230 IF (l_set_obj_version_number IS NOT NULL) THEN
1231 IF (l_set_obj_version_number <> px_set_obj_version_number) THEN
1233 -- row has been changed by another user.
1234 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1235 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DUP_SETS');
1236 FND_MSG_PUB.ADD;
1237 RAISE FND_API.G_EXC_ERROR;
1238 END IF;
1239 END IF;
1240 END IF;
1241
1242 px_set_obj_version_number := nvl(l_set_obj_version_number,1)+1;
1243
1244 OPEN check_winner_party;
1245 FETCH check_winner_party INTO l_check;
1246 IF check_winner_party%NOTFOUND THEN
1247 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1248 -- the remain party in HZ_DUP_SET_PARTIES which has merge_flag <> 'N'
1249 -- is not the same as the winner_party_id in HZ_DUP_SETS
1250 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_SEL_MASTER');
1251 FND_MSG_PUB.ADD;
1252 RAISE FND_API.G_EXC_ERROR;
1253 END IF;
1254 END IF;
1255 CLOSE check_winner_party;
1256
1257 BEGIN
1258 UPDATE HZ_DUP_SETS
1259 set merge_type = 'SAME_PARTY_MERGE'
1260 , object_version_number = px_set_obj_version_number
1261 , last_update_date = hz_utility_v2pub.last_update_date
1262 , last_updated_by = hz_utility_v2pub.last_updated_by
1263 , last_update_login = hz_utility_v2pub.last_update_login
1264 where dup_set_id = p_dup_set_id;
1265
1266 DELETE HZ_DUP_SET_PARTIES
1267 where dup_party_id not in
1268 ( select winner_party_id
1269 from HZ_DUP_SETS
1270 where dup_set_id = p_dup_set_id )
1271 and dup_set_id = p_dup_set_id;
1272 EXCEPTION
1273 WHEN NO_DATA_FOUND THEN
1274 NULL;
1275 END;
1276
1277 EXCEPTION
1278
1279 WHEN FND_API.G_EXC_ERROR THEN
1280 ROLLBACK TO reset_merge_type_pvt;
1281 x_return_status := FND_API.G_RET_STS_ERROR;
1282 FND_MSG_PUB.Count_And_Get(
1283 p_encoded => FND_API.G_FALSE,
1284 p_count => x_msg_count,
1285 p_data => x_msg_data);
1286
1287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288 ROLLBACK TO reset_merge_type_pvt;
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1290 FND_MSG_PUB.Count_And_Get(
1291 p_encoded => FND_API.G_FALSE,
1292 p_count => x_msg_count,
1293 p_data => x_msg_data);
1294
1295 WHEN OTHERS THEN
1296 ROLLBACK TO reset_merge_type_pvt;
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1299 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1300 FND_MSG_PUB.ADD;
1301 FND_MSG_PUB.Count_And_Get(
1302 p_encoded => FND_API.G_FALSE,
1303 p_count => x_msg_count,
1304 p_data => x_msg_data);
1305
1306 END reset_merge_type;
1307
1308 PROCEDURE reject_merge (
1309 p_dup_set_id IN NUMBER
1310 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
1311 ,p_init_msg_list IN VARCHAR2
1312 ,x_return_status OUT NOCOPY VARCHAR2
1313 ,x_msg_count OUT NOCOPY NUMBER
1314 ,x_msg_data OUT NOCOPY VARCHAR2 )
1315 IS
1316
1317 cursor get_dup_sets_info is
1318 select winner_party_id, object_version_number
1319 from HZ_DUP_SETS
1320 where dup_set_id = p_dup_set_id;
1321
1322 l_winner_party_id NUMBER;
1323 l_set_obj_version_number NUMBER;
1324
1325 BEGIN
1326
1327 savepoint reject_merge_pvt;
1328
1329 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1330 FND_MSG_PUB.initialize;
1331 END IF;
1332
1333 --Initialize API return status to success.
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335
1336 OPEN get_dup_sets_info;
1337 FETCH get_dup_sets_info into
1338 l_winner_party_id
1339 ,l_set_obj_version_number;
1340 CLOSE get_dup_sets_info;
1341
1342 -- check last_update_date of hz_dup_sets, not hz_dup_set_parties
1343 IF (l_set_obj_version_number IS NOT NULL) THEN
1344 IF (l_set_obj_version_number <> px_set_obj_version_number) THEN
1345 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1346 -- row has been changed by another user.
1347 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1348 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DUP_SETS');
1349 FND_MSG_PUB.ADD;
1350 RAISE FND_API.G_EXC_ERROR;
1351 END IF;
1352 END IF;
1353 END IF;
1354
1355 px_set_obj_version_number := nvl(l_set_obj_version_number,1)+1;
1356
1357 BEGIN
1358 -- clean up merge batch tables
1359 DELETE HZ_MERGE_PARTY_DETAILS
1360 WHERE batch_party_id
1361 in (select batch_party_id
1362 from HZ_MERGE_PARTIES mp
1363 where mp.batch_id = p_dup_set_id);
1364 DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;
1365 DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;
1366 DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;
1367 EXCEPTION
1368 WHEN NO_DATA_FOUND THEN
1369 NULL;
1370 END;
1371
1372 UPDATE HZ_DUP_SETS
1373 set status = 'REJECTED'
1374 , object_version_number = px_set_obj_version_number
1375 , last_update_date = hz_utility_v2pub.last_update_date
1379
1376 , last_updated_by = hz_utility_v2pub.last_updated_by
1377 , last_update_login = hz_utility_v2pub.last_update_login
1378 where dup_set_id = p_dup_set_id;
1380 EXCEPTION
1381
1382 WHEN FND_API.G_EXC_ERROR THEN
1383 ROLLBACK TO reject_merge_pvt;
1384 x_return_status := FND_API.G_RET_STS_ERROR;
1385 FND_MSG_PUB.Count_And_Get(
1386 p_encoded => FND_API.G_FALSE,
1387 p_count => x_msg_count,
1388 p_data => x_msg_data);
1389
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 ROLLBACK TO reject_merge_pvt;
1392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1393 FND_MSG_PUB.Count_And_Get(
1394 p_encoded => FND_API.G_FALSE,
1395 p_count => x_msg_count,
1396 p_data => x_msg_data);
1397
1398 WHEN OTHERS THEN
1399 ROLLBACK TO reject_merge_pvt;
1400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1401 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1402 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1403 FND_MSG_PUB.ADD;
1404 FND_MSG_PUB.Count_And_Get(
1405 p_encoded => FND_API.G_FALSE,
1406 p_count => x_msg_count,
1407 p_data => x_msg_data);
1408
1409 END reject_merge;
1410
1411 procedure set_default_assign_to_user(p_dup_set_id in number) is
1412
1413 cursor get_dl_resp_user_csr is
1414 select u.USER_ID
1415 from
1416 FND_USER u,
1417 wf_local_user_roles wur,
1418 FND_RESPONSIBILITY r,
1419 FND_MENU_ENTRIES m,
1420 FND_MENU_ENTRIES mp,
1421 FND_MENUS_VL mv
1422 where wur.user_name = u.user_name
1423 and wur.ROLE_ORIG_SYSTEM_ID = r.responsibility_id
1424 and wur.role_orig_system = 'FND_RESP'
1425 and not wur.role_name like 'FND_RESP|%|ANY'
1426 and wur.partition_id = 2
1427 and ( ( ( wur.start_date is NULL )
1428 or ( trunc ( sysdate ) >= trunc ( wur.start_date ) ) )
1429 and ( ( wur.expiration_date is NULL )
1430 or ( trunc ( sysdate ) < trunc ( wur.expiration_date ) ) )
1431 and ( ( wur.user_start_date is NULL )
1432 or ( trunc ( sysdate ) >= trunc ( wur.user_start_date ) ) )
1433 and ( ( wur.user_end_date is NULL )
1434 or ( trunc ( sysdate ) < trunc ( wur.user_end_date ) ) )
1435 and ( ( wur.role_start_date is NULL )
1436 or ( trunc ( sysdate ) >= trunc ( wur.role_start_date ) ) )
1437 and ( ( wur.role_end_date is NULL )
1438 or ( trunc ( sysdate ) < trunc ( wur.role_end_date ) ) ) )
1439 and r.menu_id = mp.menu_id
1440 and mp.sub_menu_id = m.menu_id
1441 and m.sub_menu_id = mv.menu_id
1442 and mv.menu_name = 'IMC_NG_DATA_QUALITY'
1443 and u.user_id = HZ_UTILITY_V2PUB.CREATED_BY
1444 and not exists (
1445 select 'X'
1446 from FND_RESP_FUNCTIONS rf, FND_MENUS m
1447 where rf.action_id = m.menu_id
1448 and r.responsibility_id = rf.responsibility_id
1449 and m.menu_name = 'IMC_NG_DATA_QUALITY'
1450 and rf.rule_type = 'M')
1451 and rownum = 1;
1452
1453 l_user_id number;
1454
1455 begin
1456 open get_dl_resp_user_csr;
1457 fetch get_dl_resp_user_csr into l_user_id;
1458 close get_dl_resp_user_csr;
1459
1460 if l_user_id is not null
1461 then
1462
1463 UPDATE HZ_DUP_SETS
1464 SET assigned_to_user_id = l_user_id
1465 , last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE
1466 , last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
1467 , last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1468 , object_version_number = object_version_number+1
1469 WHERE dup_set_id = p_dup_set_id;
1470 end if;
1471
1472 end;
1473
1474
1475 PROCEDURE submit_dup (
1476 p_dup_set_id IN NUMBER
1477 ,x_request_id OUT NOCOPY NUMBER
1478 ,x_return_status OUT NOCOPY VARCHAR2
1479 ,x_msg_count OUT NOCOPY NUMBER
1480 ,x_msg_data OUT NOCOPY VARCHAR2
1481 )
1482 IS
1483
1484 l_request_id NUMBER := NULL;
1485
1486 l_last_request_id NUMBER;
1487 l_conc_phase VARCHAR2(80);
1488 l_conc_status VARCHAR2(80);
1489 l_conc_dev_phase VARCHAR2(30);
1490 l_conc_dev_status VARCHAR2(30);
1491 l_message VARCHAR2(240);
1492 call_status boolean;
1493 l_dup_status VARCHAR2(30);
1494 retcode number;
1495
1496 BEGIN
1497
1498 x_return_status := FND_API.G_RET_STS_SUCCESS;
1499 fnd_msg_pub.initialize;
1500
1501 SAVEPOINT submit_dup;
1502
1503 SELECT status , request_id
1504 INTO l_dup_status ,l_last_request_id
1505 FROM hz_dup_sets
1506 WHERE dup_set_id = p_dup_set_id;
1507
1508 IF l_last_request_id IS NOT NULL THEN
1509 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(
1510 request_id => l_last_request_id,
1511 phase => l_conc_phase,
1512 status => l_conc_status,
1513 dev_phase => l_conc_dev_phase,
1514 dev_status => l_conc_dev_status,
1515 message => l_message ) ;
1516
1517 IF l_conc_dev_phase <> 'COMPLETE' THEN
1518 FND_MESSAGE.set_name('AR', 'HZ_CANNOT_SUBMIT_PROCESSING');
1519 FND_MSG_PUB.ADD;
1520 RAISE FND_API.G_EXC_ERROR;
1521 ELSE
1522 IF l_conc_status <>'NORMAL' THEN
1523 l_request_id := fnd_request.submit_request('AR','ARHDUPB','Create Merge Batch',to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
1524 FALSE,p_dup_set_id );
1525
1526 IF l_request_id = 0 THEN
1527 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1528 FND_MSG_PUB.ADD;
1529 RAISE FND_API.G_EXC_ERROR;
1530 END IF;
1531
1535 request_id = l_request_id,
1532 IF l_request_id is not null Then
1533 UPDATE HZ_DUP_SETS
1534 SET status = 'PREPROCESS',
1536 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1537 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1538 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1539 WHERE dup_set_id = p_dup_set_id;
1540 END IF;
1541
1542 ELSE --if completed normally
1543 FND_MESSAGE.set_name('AR', 'HZ_CANNOT_SUBMIT_REQUEST');
1544 FND_MSG_PUB.ADD;
1545 RAISE FND_API.G_EXC_ERROR;
1546 END IF; --l_conc_status
1547 END IF; --l_conc_dev_phase
1548 ELSE ---last_request_id is null
1549 l_request_id := fnd_request.submit_request('AR','ARHDUPB','Create Merge Batch',to_char(sysdate,'DD-MON-YY HH24:MI:SS')
1550 ,FALSE,p_dup_set_id );
1551
1552 IF l_request_id = 0 THEN
1553 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1554 FND_MSG_PUB.ADD;
1555 RAISE FND_API.G_EXC_ERROR;
1556 END IF;
1557
1558 IF l_request_id is not null Then
1559 UPDATE HZ_DUP_SETS
1560 SET status = 'PREPROCESS',
1561 request_id = l_request_id,
1562 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1563 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1564 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1565 WHERE dup_set_id = p_dup_set_id;
1566 END IF;
1567 END IF; ---last_request_id
1568
1569 set_default_assign_to_user(p_dup_set_id);
1570
1571 x_request_id := l_request_id;
1572
1573 -- standard call to get message count and if count is 1, get message info.
1574 FND_MSG_PUB.Count_And_Get(
1575 p_encoded => FND_API.G_FALSE,
1576 p_count => x_msg_count,
1577 p_data => x_msg_data);
1578
1579 EXCEPTION
1580 WHEN FND_API.G_EXC_ERROR THEN
1581 ROLLBACK to submit_dup;
1582 x_return_status := FND_API.G_RET_STS_ERROR;
1583 FND_MSG_PUB.Count_And_Get(
1584 p_encoded => FND_API.G_FALSE,
1585 p_count => x_msg_count,
1586 p_data => x_msg_data);
1587
1588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1589 ROLLBACK to submit_dup;
1590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1591 FND_MSG_PUB.Count_And_Get(
1592 p_encoded => FND_API.G_FALSE,
1593 p_count => x_msg_count,
1594 p_data => x_msg_data);
1595
1596 WHEN OTHERS THEN
1597 ROLLBACK to submit_dup;
1598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1599
1600 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1601 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1602 FND_MSG_PUB.ADD;
1603
1604 FND_MSG_PUB.Count_And_Get(
1605 p_encoded => FND_API.G_FALSE,
1606 p_count => x_msg_count,
1607 p_data => x_msg_data);
1608
1609 END submit_dup;
1610
1611 PROCEDURE create_merge (
1612 Errbuf OUT NOCOPY VARCHAR2
1613 ,Retcode OUT NOCOPY VARCHAR2
1614 ,p_dup_set_id IN NUMBER )
1615
1616 IS
1617
1618 l_dup_set_id NUMBER;
1619 l_merge_batch_id NUMBER;
1620 l_return_status VARCHAR2(30);
1621 l_msg_count NUMBER;
1622 l_msg_data VARCHAR2(2000);
1623 l_dummy VARCHAR2(1);
1624 l_set_obj_version_number NUMBER;
1625 l_default_mapping VARCHAR2(1) DEFAULT 'Y';--Bug 5635453
1626
1627 cursor check_merge_batch is
1628 select 'X'
1629 from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp
1630 where mb.batch_id = mp.batch_id
1631 and mb.batch_id = p_dup_set_id;
1632
1633 cursor get_dup_set_obj_version is
1634 select nvl(object_version_number,-1)
1635 from HZ_DUP_SETS
1636 where dup_set_id = p_dup_set_id;
1637
1638 BEGIN
1639
1640 -- Stamp concurrent request id to dup sets
1641 UPDATE HZ_DUP_SETS
1642 SET REQUEST_ID = hz_utility_v2pub.request_id
1643 WHERE dup_set_id = p_dup_set_id;
1644 COMMIT;
1645
1646 savepoint create_merge_pvt;
1647
1648 FND_MSG_PUB.initialize;
1649
1650 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create Merge Batch concurrent program');
1651 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1652 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Parameters - ');
1653 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Merge ID : '||p_dup_set_id);
1654 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1655
1656 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Start: validate_overlapping_merge_req');
1657 HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
1658 p_dup_set_id => p_dup_set_id,
1659 p_merge_batch_id => null,
1660 p_init_msg_list => FND_API.G_FALSE,
1661 p_reject_req_flag => 'Y',
1662 x_return_status => l_return_status,
1663 x_msg_count => l_msg_count,
1664 x_msg_data => l_msg_data);
1665
1666 FND_FILE.PUT_LINE (FND_FILE.LOG, 'End: validate_overlapping_merge_req');
1667 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1668
1669 /* error messages have been pushed into message stack in above procedure */
1670 IF l_return_status = 'E' THEN
1671 RAISE FND_API.G_EXC_ERROR;
1672 ELSIF l_return_status = 'U' THEN
1673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1674 END IF;
1675
1676 l_dup_set_id := p_dup_set_id;
1677
1678 OPEN get_dup_set_obj_version;
1679 FETCH get_dup_set_obj_version into l_set_obj_version_number;
1683 l_set_obj_version_number := NULL;
1680 CLOSE get_dup_set_obj_version;
1681
1682 IF(l_set_obj_version_number = -1) THEN
1684 END IF;
1685
1686 -- check if the merge batch is already created
1687 open check_merge_batch;
1688 fetch check_merge_batch into l_dummy;
1689 if(check_merge_batch%NOTFOUND) then
1690
1691 BEGIN
1692
1693 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1694 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'Checking Profile Value for HZ_DL_DQM_MERGE_SUGG');
1695 --Bug 5635453
1696 fnd_profile.get(
1697 name => 'HZ_DL_DQM_MERGE_SUGG',
1698 val => l_default_mapping
1699 );
1700
1701 If (NVL(l_default_mapping,'Y') <>'N') THEN
1702 l_default_mapping := 'Y';
1703 END IF;
1704
1705 --Bug 5635453
1706 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Start: create_merge_batch');
1707 HZ_MERGE_DUP_PVT.create_merge_batch (
1708 p_dup_set_id => l_dup_set_id
1709 ,p_default_mapping => l_default_mapping--Bug 5635453
1710 ,p_object_version_number => l_set_obj_version_number
1711 ,x_merge_batch_id => l_merge_batch_id
1712 ,x_return_status => l_return_status
1713 ,x_msg_count => l_msg_count
1714 ,x_msg_data => l_msg_data
1715 );
1716 FND_FILE.PUT_LINE (FND_FILE.LOG, 'End: create_merge_batch');
1717 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 l_return_status := 'U';
1722 NULL;
1723 END;
1724
1725 IF(l_set_obj_version_number = -1) THEN
1726 l_set_obj_version_number := 2;
1727 ELSE
1728 l_set_obj_version_number := l_set_obj_version_number + 1;
1729 END IF;
1730
1731 IF (l_return_status = 'E') THEN
1732 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1733 FND_FILE.PUT_LINE (FND_FILE.LOG, 'HZ_DUP_PVT.create_merge expected error. ');
1734 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1735 RAISE FND_API.G_EXC_ERROR;
1736 ELSIF (l_return_status = 'U') THEN
1737 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1738 FND_FILE.PUT_LINE (FND_FILE.LOG, 'HZ_DUP_PVT.create_merge unexpected error. ');
1739 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1741 ELSIF (l_return_status = 'S') THEN
1742 UPDATE HZ_DUP_SETS
1743 set status = 'REQUESTED'
1744 , last_update_date = hz_utility_v2pub.last_update_date
1745 , last_updated_by = hz_utility_v2pub.last_updated_by
1746 , last_update_login = hz_utility_v2pub.last_update_login
1747 where dup_set_id = l_dup_set_id;
1748 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1749 FND_FILE.PUT_LINE (FND_FILE.LOG, 'call create_merge_batch without error. ');
1750 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1751 COMMIT;
1752 END IF; -- return_status
1753 else
1754 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1755 FND_FILE.PUT_LINE (FND_FILE.LOG, 'merge batch/parties exists. ');
1756 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1757 RAISE FND_API.G_EXC_ERROR;
1758 end if; -- check_merge_batch
1759 close check_merge_batch;
1760
1761 FND_FILE.PUT_LINE (FND_FILE.LOG, 'validate_overlapping_merge_req second try');
1762 HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
1763 p_dup_set_id => p_dup_set_id,
1764 p_merge_batch_id => null,
1765 p_init_msg_list => FND_API.G_FALSE,
1766 p_reject_req_flag => 'Y',
1767 x_return_status => l_return_status,
1768 x_msg_count => l_msg_count,
1769 x_msg_data => l_msg_data);
1770
1771 FND_FILE.PUT_LINE (FND_FILE.LOG, 'End: validate_overlapping_merge_req second try');
1772 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ');
1773
1774 /* error messages have been pushed into message stack in above procedure */
1775 IF l_return_status = 'E' THEN
1776 RAISE FND_API.G_EXC_ERROR;
1777 ELSIF l_return_status = 'U' THEN
1778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1779 END IF;
1780
1781 EXCEPTION
1782
1783 WHEN FND_API.G_EXC_ERROR THEN
1784 ROLLBACK TO create_merge_pvt;
1785 UPDATE HZ_DUP_SETS
1786 SET STATUS = 'ERROR'
1787 WHERE DUP_SET_ID = p_dup_set_id;
1788 COMMIT;
1789 Retcode := 2;
1790 log('HZ_DUP_PVT.create_merge - Expected Error Encountered');
1791 log(' ');
1792 Errbuf := logerror(SQLERRM);
1793
1794 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1795 ROLLBACK TO create_merge_pvt;
1796 UPDATE HZ_DUP_SETS
1797 SET STATUS = 'ERROR'
1798 WHERE DUP_SET_ID = p_dup_set_id;
1799 COMMIT;
1800 Retcode := 2;
1801 log('HZ_DUP_PVT.create_merge - Unexpected Error Encountered');
1802 log(' ');
1803 Errbuf := logerror(SQLERRM);
1804
1805 WHEN OTHERS THEN
1806 ROLLBACK TO create_merge_pvt;
1807 UPDATE HZ_DUP_SETS
1808 SET STATUS = 'ERROR'
1809 WHERE DUP_SET_ID = p_dup_set_id;
1810 COMMIT;
1811 Retcode := 2;
1812 log('HZ_DUP_PVT.create_merge - Others Error Encountered');
1813 log(' ');
1814 Errbuf := logerror(SQLERRM);
1815
1816 END create_merge;
1817
1818 PROCEDURE log(
1819 message IN VARCHAR2,
1820 newline IN BOOLEAN DEFAULT TRUE
1821 ) IS
1822 BEGIN
1823 IF message = 'NEWLINE' THEN
1824 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1825 ELSIF (newline) THEN
1826 FND_FILE.put_line(fnd_file.log,message);
1827 ELSE
1828 FND_FILE.put(fnd_file.log,message);
1829 END IF;
1830 END log;
1831
1832 /*-----------------------------------------------------------------------
1836 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
1833 | Function to fetch messages of the stack and log the error
1834 | Also returns the error
1835 |-----------------------------------------------------------------------*/
1837 RETURN VARCHAR2 IS
1838 l_msg_data VARCHAR2(2000);
1839 BEGIN
1840 FND_MSG_PUB.Reset;
1841
1842 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1843 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1844 END LOOP;
1845 IF (SQLERRM IS NOT NULL) THEN
1846 l_msg_data := l_msg_data || SQLERRM;
1847 END IF;
1848 log(l_msg_data);
1849 RETURN l_msg_data;
1850 END logerror;
1851
1852 PROCEDURE validate_party_modeling( p_party_ids IN VARCHAR2,
1853 x_cert_warn OUT NOCOPY VARCHAR2,
1854 x_reln_warn OUT NOCOPY VARCHAR2,
1855 x_reln_token OUT NOCOPY VARCHAR2
1856 )
1857 IS
1858 l_count NUMBER;
1859 l_prev_hier_type VARCHAR2(30);
1860 l_prev_parent_id NUMBER;
1861 l_token_str VARCHAR2(4000);
1862 l_regid_str VARCHAR2(2000);
1863 TYPE c_ref IS REF CURSOR;
1864 c_cursor c_ref;
1865 l_parent_id NUMBER;
1866 l_child_id NUMBER;
1867 l_top_parent_flag varchar2(1);
1868 l_level_number NUMBER;
1869 l_hierarchy_type VARCHAR2(30);
1870 l_parent_reg_id VARCHAR2(30);
1871 l_child_reg_id VARCHAR2(30);
1872 l_prev_par_reg_id VARCHAR2(30);
1873 BEGIN
1874
1875 ---Per PM, take out Party Modeling for Certification Level
1876 /* OPEN c_cursor for 'select count(*) '||
1877 ' from HZ_PARTIES pa '||
1878 ' where pa.certification_level = 100 '||
1879 ' and pa.party_id IN ('||p_party_ids||')';
1880 FETCH c_cursor INTO l_count;
1881 if (l_count > 1) then
1882 x_cert_warn := 'HZ_PMODEL_PROTECT_CERT_WARN';
1883 end if;
1884 CLOSE c_cursor; */
1885
1886 IF NVL(fnd_profile.value('HZ_DL_PROTECT_PARTY_MODELING'), 'Y') = 'Y' THEN
1887 l_count := 0;
1888 ---Party Modeling for relationship Hierarchy
1889 open c_cursor FOR ' select distinct parent_id,child_id,nvl(top_parent_flag,''N'') top_parent_flag,level_number,hierarchy_type,'||
1890 ' p1.party_number parent_reg_id,p2.party_number child_reg_id '||
1891 ' from hz_hierarchy_nodes h,hz_parties p1,hz_parties p2 '||
1892 ' where EXISTS (select 1 from hz_hierarchy_nodes h2 '||
1893 ' where h2.parent_id = h.parent_id '||
1894 ' and h2.hierarchy_type = h.hierarchy_type '||
1895 ' and nvl(h2.top_parent_flag,''N'') = ''Y'' '||
1896 ' and h2.level_number = 0 '||
1897 ' )'||
1898 ' and h.parent_id = p1.party_id'||
1899 ' and h.child_id = p2.party_id'||
1900 ' and (nvl(top_parent_flag,''N'')=''N'' and level_number <> 0) '||
1901 ' and h.child_id IN ('|| p_party_ids ||')'||
1902 ' and sysdate between h.effective_start_date and h.effective_end_date'||
1903 ' order by hierarchy_type,parent_id,nvl(top_parent_flag,''N'') desc,level_number';
1904 LOOP
1905 FETCH c_cursor INTO l_parent_id,l_child_id,l_top_parent_flag,l_level_number,l_hierarchy_type,l_parent_reg_id,l_child_reg_id;
1906 EXIT WHEN c_cursor%NOTFOUND;
1907 IF(l_parent_id <> l_child_id) THEN
1908 IF(l_prev_hier_type IS NULL OR l_prev_hier_type <> l_hierarchy_type) THEN
1909 IF(l_count > 0 OR nvl(instr(l_regid_str,l_prev_par_reg_id),0) > 0 ) THEN /* to handle the cases like C is child of A in hierarchy H1 */
1910 l_token_str := rtrim(l_token_str,',') || l_regid_str; /* and C is child of B in hierarchy H2 and A and C are in the merge. */
1911 l_regid_str := null;
1912 END IF;
1913 IF(nvl(instr(p_party_ids,l_parent_id),0) > 0) THEN
1914 l_regid_str :='& '||l_parent_reg_id||','||l_child_reg_id||',';
1915 ELSE
1916 l_regid_str :='& '||l_child_reg_id||',';
1917 END IF;
1918 l_count := 0;
1919 ELSE
1920 IF(l_prev_parent_id IS NULL OR l_parent_id <> l_prev_parent_id) THEN
1921 IF(nvl(instr(l_regid_str,l_parent_reg_id),0) = 0) THEN
1922 IF(l_count > 0 OR nvl(instr(l_regid_str,l_prev_par_reg_id),0) > 0 ) THEN
1923 l_token_str := rtrim(l_token_str,',') || l_regid_str;
1924 l_regid_str := null;
1925 END IF;
1926 IF(nvl(instr(p_party_ids,l_parent_id),0) > 0) THEN
1927 l_regid_str :='& '||l_parent_reg_id||','||l_child_reg_id||',';
1928 ELSE
1929 l_regid_str :='& '||l_child_reg_id||',';
1930 END IF;
1931 END IF;
1932 END IF;
1933 IF(nvl(instr(l_regid_str,l_child_reg_id),0) = 0) THEN
1934 l_regid_str := l_regid_str || l_child_reg_id||',';
1935 l_count := l_count+1;
1936 END IF;
1937 END IF;
1938 l_prev_parent_id := l_parent_id;
1939 l_prev_par_reg_id := l_parent_reg_id;
1940 l_prev_hier_type := l_hierarchy_type;
1941 END IF;
1942 END LOOP;
1943 if(c_cursor%ISOPEN )then
1944 close c_cursor;
1945 end if;
1946 IF(l_regid_str IS NOT NULL AND (l_count > 0 OR nvl(instr(l_regid_str,l_parent_reg_id),0) > 0)) THEN
1947 l_token_str := rtrim(l_token_str,',') || l_regid_str;
1948 END IF;
1949 IF(l_token_str IS NOT NULL) THEN
1950 l_token_str := rtrim(l_token_str,',');
1951 x_reln_token := l_token_str;
1952 x_reln_warn := 'HZ_PMODEL_PROTECT_REL_WARN';
1953 END IF;
1954 end if; -- if nvl(fnd_profile.value...
1955 END;
1956
1957 FUNCTION get_automerge_candidate(p_party_score NUMBER, p_automerge_score NUMBER)
1958 RETURN VARCHAR2 IS
1959 BEGIN
1960 If (p_party_score >= p_automerge_score) THEN
1961 RETURN 'Y';
1962 ELSE
1963 RETURN 'N';
1964 END IF;
1965 END;
1966
1967 FUNCTION get_update_flag(x_dup_set_id NUMBER)
1968 RETURN VARCHAR2 IS
1969 update_count NUMBER;
1973 open update_dupset(x_dup_Set_id);
1970 CURSOR update_dupset(p_dup_set_id NUMBER) IS
1971 select count(*) from hz_dup_set_parties where dup_set_id=p_dup_set_id and remove_flag is not null;
1972 BEGIN
1974 fetch update_dupset into update_count;
1975 close update_dupset;
1976
1977 If ( update_count>0) THEN
1978 RETURN 'Y';
1979 ELSE
1980 RETURN 'N';
1981 END IF;
1982 END;
1983
1984 procedure party_merge_dss_check(p_merge_batch_id in number,
1985 x_dss_update_flag out nocopy varchar2,
1986 x_return_status OUT NOCOPY VARCHAR2,
1987 x_msg_count OUT NOCOPY NUMBER,
1988 x_msg_data OUT NOCOPY VARCHAR2 ) is
1989
1990 cursor get_merge_parties_csr is
1991 select party_id
1992 from hz_merge_parties mp, hz_parties party
1993 where (party.party_id = mp.from_party_id or party.party_id = mp.to_party_id)
1994 and mp.batch_id = p_merge_batch_id;
1995
1996 l_party_id number;
1997 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1998 dss_msg_count NUMBER := 0;
1999 dss_msg_data VARCHAR2(2000):= null;
2000 l_test_security VARCHAR2(1):= 'F';
2001 l_dl_dss_prof VARCHAR2(1);
2002
2003 begin
2004 x_return_status := FND_API.G_RET_STS_SUCCESS;
2005 FND_MSG_PUB.initialize;
2006
2007 x_dss_update_flag := 'Y';
2008 l_dl_dss_prof := NVL(fnd_profile.value('HZ_DL_DISPLAY_LOCK'), 'N');
2009 open get_merge_parties_csr;
2010 loop
2011 fetch get_merge_parties_csr into l_party_id;
2012 exit when get_merge_parties_csr%notfound;
2013
2014 -- DSS Check if dss profile enabled and dl dss rule is not set to F(do not check DSS)
2015 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' and l_dl_dss_prof <> 'F' THEN
2016
2017 l_test_security := hz_dss_util_pub.test_instance(
2018 p_operation_code => 'UPDATE',
2019 p_db_object_name => 'HZ_PARTIES',
2020 p_instance_pk1_value => l_party_id,
2021 p_user_name => fnd_global.user_name,
2022 x_return_status => dss_return_status,
2023 x_msg_count => dss_msg_count,
2024 x_msg_data => dss_msg_data);
2025
2026 if dss_return_status <> fnd_api.g_ret_sts_success THEN
2027 x_dss_update_flag := 'N';
2028 RAISE FND_API.G_EXC_ERROR;
2029 end if;
2030
2031 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
2032
2033 if l_dl_dss_prof = 'S'
2034 then -- show error at submission
2035 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_MERGE_PROTECTION');
2036
2037 else -- show error at mapping
2038 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_DSS_NO_PRIVILEGE');
2039 end if;
2040
2041 FND_MSG_PUB.ADD;
2042 x_dss_update_flag := 'N';
2043 close get_merge_parties_csr;
2044 RAISE FND_API.G_EXC_ERROR;
2045 end if;
2046 END IF;
2047 end loop;
2048 close get_merge_parties_csr;
2049
2050 -- standard call to get message count and if count is 1, get message info.
2051 FND_MSG_PUB.Count_And_Get(
2052 p_encoded => FND_API.G_FALSE,
2053 p_count => x_msg_count,
2054 p_data => x_msg_data);
2055
2056 EXCEPTION
2057 WHEN FND_API.G_EXC_ERROR THEN
2058 x_return_status := FND_API.G_RET_STS_ERROR;
2059 FND_MSG_PUB.Count_And_Get(
2060 p_encoded => FND_API.G_FALSE,
2061 p_count => x_msg_count,
2062 p_data => x_msg_data);
2063 return; -- only find one is ok
2064 WHEN OTHERS THEN
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2067 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2068 FND_MSG_PUB.ADD;
2069 FND_MSG_PUB.Count_And_Get(
2070 p_encoded => FND_API.G_FALSE,
2071 p_count => x_msg_count,
2072 p_data => x_msg_data);
2073 end party_merge_dss_check;
2074
2075 function show_dss_lock(p_dup_set_id in number) return varchar2 is
2076
2077 l_dss_update_flag varchar2(1);
2078 l_return_status VARCHAR2(1);
2079 l_msg_count NUMBER;
2080 l_msg_data VARCHAR2(2000);
2081
2082 begin
2083 if NVL(fnd_profile.value('HZ_DL_DISPLAY_LOCK'), 'N') = 'Y'
2084 then
2085 party_merge_dss_check(p_merge_batch_id => p_dup_set_id,
2086 x_dss_update_flag => l_dss_update_flag,
2087 x_return_status => l_return_status,
2088 x_msg_count => l_msg_count,
2089 x_msg_data => l_msg_data);
2090 if l_dss_update_flag = 'N'
2091 then return 'Y';
2092 else return 'N';
2093 end if;
2094 else
2095 -- in ('F'(no dss check),'N'(check dss at mapping and don't show access column),'S'(only check dss at submission)
2096 return 'N';
2097 end if;
2098 end show_dss_lock;
2099
2100 PROCEDURE reprocess_merge_request (
2101 p_dup_set_id IN NUMBER
2102 ,x_request_id OUT NOCOPY NUMBER
2103 ,x_return_status OUT NOCOPY VARCHAR2
2104 ,x_msg_count OUT NOCOPY NUMBER
2105 ,x_msg_data OUT NOCOPY VARCHAR2 ) is
2106
2107 cursor validate_reprocess_req_csr is
2108 select count(*)
2109 from hz_dup_sets
2110 where status in ('REJECTED', 'COMPLETED','SUBMITTED')
2111 and dup_set_id = p_dup_set_id;
2112 l_count number;
2113 begin
2114
2115 FND_MSG_PUB.initialize;
2119 open validate_reprocess_req_csr;
2116 --Initialize API return status to success.
2117 x_return_status := FND_API.G_RET_STS_SUCCESS;
2118
2120 fetch validate_reprocess_req_csr into l_count;
2121 close validate_reprocess_req_csr;
2122
2123 if l_count > 0
2124 then
2125 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MRQ_REPROCESS_ERR' );
2126 FND_MSG_PUB.ADD;
2127 RAISE FND_API.G_EXC_ERROR;
2128 end if;
2129
2130 begin
2131 -- clean up merge batch tables
2132 DELETE HZ_MERGE_PARTY_DETAILS
2133 WHERE batch_party_id
2134 in (select batch_party_id
2135 from HZ_MERGE_PARTIES mp
2136 where mp.batch_id = p_dup_set_id);
2137 DELETE HZ_MERGE_BATCH WHERE batch_id = p_dup_set_id;
2138 DELETE HZ_MERGE_PARTIES WHERE batch_id = p_dup_set_id;
2139 DELETE HZ_MERGE_ENTITY_ATTRIBUTES WHERE merge_batch_id = p_dup_set_id;
2140 EXCEPTION
2141 WHEN NO_DATA_FOUND THEN
2142 NULL;
2143 end;
2144
2145 submit_dup (
2146 p_dup_set_id => p_dup_set_id
2147 ,x_request_id => x_request_id
2148 ,x_return_status => x_return_status
2149 ,x_msg_count => x_msg_count
2150 ,x_msg_data => x_msg_data);
2151
2152 EXCEPTION
2153
2154 WHEN FND_API.G_EXC_ERROR THEN
2155 x_return_status := FND_API.G_RET_STS_ERROR;
2156 FND_MSG_PUB.Count_And_Get(
2157 p_encoded => FND_API.G_FALSE,
2158 p_count => x_msg_count,
2159 p_data => x_msg_data);
2160
2161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2163 FND_MSG_PUB.Count_And_Get(
2164 p_encoded => FND_API.G_FALSE,
2165 p_count => x_msg_count,
2166 p_data => x_msg_data);
2167
2168 WHEN OTHERS THEN
2169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2170 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2171 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2172 FND_MSG_PUB.ADD;
2173 FND_MSG_PUB.Count_And_Get(
2174 p_encoded => FND_API.G_FALSE,
2175 p_count => x_msg_count,
2176 p_data => x_msg_data);
2177
2178
2179 end reprocess_merge_request;
2180
2181 procedure reset_dup_set_status is
2182
2183 cursor get_submitted_count_csr is
2184 select count(*)
2185 from hz_dup_sets
2186 where status = 'SUBMITTED'
2187 and rownum = 1; /* as long as we have one submitted status */
2188 l_count number;
2189 begin
2190
2191 open get_submitted_count_csr;
2192 fetch get_submitted_count_csr into l_count;
2193 close get_submitted_count_csr;
2194
2195 if l_count > 0
2196 then
2197 update hz_dup_sets
2198 set status = 'ERROR'
2199 where dup_set_id in
2200 (select dup_set_id
2201 from HZ_MERGE_BATCH mb,
2202 HZ_DUP_SETS ds,
2203 Fnd_Concurrent_Requests r,
2204 FND_CONCURRENT_PROGRAMS cp
2205 where
2206 mb.batch_id = ds.dup_set_id
2207 and R.Program_Application_ID = 222
2208 and r.request_id = mb.request_id
2209 and cp.application_id = R.Program_Application_ID
2210 and cp.concurrent_program_id = r.concurrent_program_id
2211 and cp.concurrent_program_name = 'ARHPMERGE'
2212 and r.phase_code = 'C'
2213 and ds.status ='SUBMITTED');
2214
2215
2216 /* handle the case that requests have been deleted from the Fnd_Concurrent_Requests*/
2217
2218 update hz_dup_sets
2219 set status = 'ERROR'
2220 where dup_set_id in
2221 (select dup_set_id
2222 from HZ_MERGE_BATCH mb,
2223 HZ_DUP_SETS ds,
2224 Fnd_Concurrent_Requests r
2225 where
2226 mb.batch_id = ds.dup_set_id
2227 and r.request_id(+) = mb.request_id
2228 and ds.status ='SUBMITTED'
2229 and r.request_id is null);
2230
2231 commit;
2232
2233 end if;
2234
2235 end reset_dup_set_status;
2236
2237 procedure get_match_rule_thresholds(p_match_rule_id in number,
2238 x_match_threshold out nocopy number,
2239 x_automerge_threshold out nocopy number) is
2240
2241 cursor get_match_rule_thresholds_csr is
2242 select match_score, nvl(auto_merge_score, 101)
2243 from hz_match_rules_vl
2244 where match_rule_id = p_match_rule_id;
2245
2246 l_match_rule_id number;
2247
2248 begin
2249 open get_match_rule_thresholds_csr;
2250 fetch get_match_rule_thresholds_csr into x_match_threshold,x_automerge_threshold;
2251 close get_match_rule_thresholds_csr;
2252
2253 end get_match_rule_thresholds;
2254
2255 procedure get_most_matching_party(p_search_ctx_id in number,
2256 p_new_party_id in number,
2257 x_party_id out nocopy number,
2258 x_match_score out nocopy number,
2259 x_party_name out nocopy varchar2) is
2260
2261 cursor get_party_with_highest_score_c is
2262 SELECT party_id, score, party_name
2263 FROM (SELECT mpg.party_id party_id, mpg.score, p.party_name,
2264 RANK() OVER (ORDER BY score desc) rank
2265 FROM hz_matched_parties_gt mpg, hz_parties p
2266 WHERE mpg.party_id = p.party_id
2267 AND mpg.search_context_id = p_search_ctx_id
2268 AND mpg.party_id <> p_new_party_id -- newly created id
2269 ORDER BY p.last_update_date desc)
2270 WHERE rank = 1 and rownum = 1;
2271
2272 begin
2273 open get_party_with_highest_score_c;
2274 fetch get_party_with_highest_score_c into x_party_id, x_match_score, x_party_name;
2275 if get_party_with_highest_score_c%NOTFOUND
2276 then
2277 x_party_id := null;
2278 end if;
2279 close get_party_with_highest_score_c;
2280 end get_most_matching_party;
2281
2282 procedure validate_master_party_id(px_party_id in out nocopy number,
2283 x_overlap_merge_req_id out nocopy number) is
2284
2285 cursor validate_master_party_id_csr is
2286 select to_party_id, mb.batch_id
2287 from hz_merge_batch mb, hz_merge_parties mp
2288 where mb.batch_id = mp.batch_id
2289 and mp.from_party_id = px_party_id
2290 and mp.to_party_id <> px_party_id
2291 and mb.batch_status <> 'COMPLETED' and rownum = 1;
2292
2293 l_party_id number;
2294 begin
2295 open validate_master_party_id_csr;
2296 fetch validate_master_party_id_csr into l_party_id, x_overlap_merge_req_id;
2297 close validate_master_party_id_csr;
2298 if l_party_id is not null
2299 then
2300 px_party_id := l_party_id; -- if px_party_id is from_id in merge request,
2301 -- change it to to_party_id in the merge request.
2302 end if;
2303 end validate_master_party_id;
2304
2305 END HZ_DUP_PVT;