[Home] [Help]
PACKAGE BODY: APPS.HZ_MERGE_DUP_PVT
Source
1 PACKAGE BODY HZ_MERGE_DUP_PVT AS
2 /* $Header: ARHCMBAB.pls 120.87.12010000.2 2008/11/18 07:10:18 vsegu ship $ */
3
4 --Declaration of Private procedures
5 PROCEDURE insert_party_details( cp_batch_party_id IN NUMBER,
6 cp_from_party_id IN NUMBER,
7 cp_to_party_id IN NUMBER,
8 p_def_to_entity IN VARCHAR2 DEFAULT 'N');
9
10 PROCEDURE insert_reln_parties( p_batch_party_id IN NUMBER,
11 p_batch_id IN NUMBER);
12
13 PROCEDURE insert_party_site_details (
14 p_from_party_id IN NUMBER,
15 p_to_party_id IN NUMBER,
16 p_batch_party_id IN NUMBER,
17 p_reln_parties IN VARCHAR2 DEFAULT 'N');
18
19 PROCEDURE insert_party_reln_details (
20 p_from_party_id IN NUMBER,
21 p_to_party_id IN NUMBER,
22 p_batch_party_id IN NUMBER,
23 p_def_mapping IN VARCHAR2 DEFAULT 'N');
24
25 PROCEDURE insert_sugg_reln_ps_details (
26 p_from_party_id IN NUMBER,
27 p_to_party_id IN NUMBER,
28 p_batch_party_id IN NUMBER,
29 p_reln_parties IN VARCHAR2 DEFAULT 'N'
30 );
31
32 PROCEDURE insert_sugg_reln_party(
33 p_batch_id IN NUMBER,
34 p_from_rel_party_id IN NUMBER,
35 p_to_rel_party_id IN NUMBER,
36 x_batch_party_id OUT NOCOPY NUMBER
37 );
38
39 --
40 -- PROCEDURE Create_Merge_Batch
41 --
42 -- DESCRIPTION
43 -- Creates a merge batch from a dup set
44 --
45 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
46 --
47 -- ARGUMENTS
48 -- IN:
49 -- p_dup_set_id ID of the duplicate set identified by DQM
50 -- Duplicate Identification program
51 -- p_default_mapping Y/N if the DQM smart search has to be enabled
52 -- p_batch_name Parameter for the Batch name
53 -- OUT:
54 -- x_merge_batch_id Phone country code.
55 -- x_return_status Return status after the call. The status can
56 -- be fnd_api.g_ret_sts_success (success),
57 -- fnd_api.g_ret_sts_error (error),
58 -- fnd_api.g_ret_sts_unexp_error
59 -- (unexpected error).
60 -- x_msg_count Number of messages in message stack.
61 -- x_msg_data Message text if x_msg_count is 1.
62 --
63 -- NOTES
64 --
65 -- MODIFICATION HISTORY
66 --
67 -- 04/01/2002 Jyoti Pandey o Created.
68 --
69 --
70
71 PROCEDURE Create_Merge_Batch(
72 p_dup_set_id IN NUMBER,
73 p_default_mapping IN VARCHAR2,
74 p_object_version_number IN OUT NOCOPY NUMBER,
75 x_merge_batch_id OUT NOCOPY NUMBER,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2 ) IS
79
80 l_merge_from HZ_DUP_SET_PARTIES.DUP_PARTY_ID%TYPE;
81 l_merge_to HZ_DUP_SET_PARTIES.DUP_PARTY_ID%TYPE;
82 l_merge_type HZ_DUP_SETS.MERGE_TYPE%TYPE;
83 l_merge_type2 HZ_DUP_SETS.MERGE_TYPE%TYPE;
84 l_count NUMBER := 0;
85 l_dup_set_count NUMBER;
86 l_default_mapping VARCHAR2(1);
87 l_party_type VARCHAR2(255);
88
89 l_batch_id HZ_MERGE_BATCH.BATCH_ID%TYPE;
90 l_batch_name HZ_MERGE_BATCH.BATCH_NAME%TYPE;
91 l_batch_party_id HZ_DUP_SET_PARTIES.DUP_PARTY_ID%TYPE;
92 l_object_version_number NUMBER;
93 db_object_version_number NUMBER;
94 l_automerge_flag varchar2(1);
95 l_addr_match_rule NUMBER := 0;
96 l_reln_match_rule NUMBER := 0;
97
98 CURSOR c_batch_name IS
99 SELECT substrb(party_name, 1, 60) || ' (' || p_dup_set_id||')',
100 winner_party_id, merge_type
101 FROM HZ_DUP_SETS, HZ_PARTIES
102 WHERE winner_party_id = party_id
103 AND dup_set_id = p_dup_set_id;
104
105 cursor get_automerge_flag_csr is
106 select nvl(db.automerge_flag,'N')
107 from hz_dup_batch db, hz_dup_sets ds
108 where db.dup_batch_id = ds.dup_batch_id
109 and ds.dup_set_id = p_dup_set_id
110 and rownum=1;
111
112 --Cursor to get the parties to be merged
113 CURSOR get_merge_parties_csr(cp_winner_party_id NUMBER) IS
114 SELECT DUP_PARTY_ID
115 FROM HZ_DUP_SET_PARTIES
116 WHERE DUP_SET_ID = p_dup_set_id
117 AND nvl(MERGE_FLAG,'Y')<>'N';
118
119 cursor sugg_request_exist_csr is
120 select count(*)
121 from hz_dup_batch db, hz_dup_sets ds
122 where db.dup_batch_id = ds.dup_batch_id
123 and db.match_rule_id = -1
124 and db.dup_batch_name like 'SUGG:%'
125 and ds.dup_set_id = p_dup_set_id;
126
127 l_count1 number := 0;
128 l_created_by_module varchar2(30) := 'DL';
129
130 BEGIN
131
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 fnd_msg_pub.initialize;
135 l_batch_id := null;
136
137 SAVEPOINT create_merge_batch;
138
139 --check if the dup_set_id is valid
140 OPEN c_batch_name;
141 FETCH c_batch_name INTO l_batch_name, l_merge_to, l_merge_type;
142 IF c_batch_name%NOTFOUND THEN
143 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
144 FND_MESSAGE.SET_TOKEN('PARAMETER','DUP_SET_ID' );
145 FND_MSG_PUB.ADD;
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148 CLOSE c_batch_name;
149
150 IF p_default_mapping = null THEN
151 l_default_mapping := 'N';
152 ELSE l_default_mapping := p_default_mapping;
153 END IF;
154
155
156 l_batch_id:= p_dup_set_id;
157
158 open sugg_request_exist_csr;
159 fetch sugg_request_exist_csr into l_count1;
160 close sugg_request_exist_csr;
161
162 if l_count1 <> 0
163 then
164 l_batch_name := 'SUGG: '|| l_batch_name;
165 l_created_by_module := 'DL_SUGG';
166 end if;
167
168
169
170 --Create a merge batch in HZ_MERGE_BATCH
171 HZ_MERGE_BATCH_PKG.INSERT_ROW(
172 px_BATCH_ID => l_batch_id,
173 p_RULE_SET_NAME => 'DEFAULT',
174 p_BATCH_NAME => l_batch_name,
175 p_REQUEST_ID => NULL,
176 p_BATCH_STATUS => 'MAPPING_PENDING',
177 p_BATCH_COMMIT => 'B',
178 p_BATCH_DELETE => 'N',
179 p_MERGE_REASON_CODE => 'DEDUPE',
180 p_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
181 p_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
182 p_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
183 p_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
184 p_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
185
186 -- update the created_by_module to 'DL' since this api is only used by data librarian
187 -- to create merge batch data
188 UPDATE HZ_MERGE_BATCH
189 SET CREATED_BY_MODULE = l_created_by_module
190 WHERE batch_id = p_dup_set_id;
191
192 IF l_merge_type='PARTY_MERGE' THEN
193 --Get Parties Merging in that batch
194 OPEN get_merge_parties_csr(l_merge_to);
195 LOOP
196 FETCH get_merge_parties_csr INTO l_merge_from;
197 EXIT WHEN get_merge_parties_csr%NOTFOUND;
198
199 l_batch_party_id := null;
200 -- New for J minipack. Add master party
201 IF l_merge_from = l_merge_to THEN
202 l_merge_type2 := 'SAME_PARTY_MERGE';
203 ELSE
204 l_merge_type2 := 'PARTY_MERGE';
205 END IF;
206
207 -- call table-handler to insert the record in HZ_MERGE_PARTIES
208 HZ_MERGE_PARTIES_PKG.INSERT_ROW(
209 px_BATCH_PARTY_ID => l_batch_party_id,
210 p_batch_id => l_batch_id,
211 p_merge_type => l_merge_type2,
212 p_from_party_id => l_merge_from,
213 p_to_party_id => l_merge_to,
214 p_merge_reason_code => 'DEDUPE',
215 p_merge_status => 'PENDING',
216 p_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
217 p_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
218 p_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
219 p_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
220 p_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
221
222 --Call to insert details
223 IF l_default_mapping = 'Y' THEN
224 insert_party_details(l_batch_party_id,
225 l_merge_from ,
226 l_merge_to ,
227 'Y' );
228 ELSE
229 insert_party_details(l_batch_party_id,
230 l_merge_from ,
231 l_merge_to);
232 END IF;
233
234 --Call to insert relationships
235 insert_reln_parties(l_batch_party_id ,
236 l_batch_id );
237
238 END LOOP;
239 CLOSE get_merge_parties_csr;
240
241 open get_automerge_flag_csr;
242 fetch get_automerge_flag_csr into l_automerge_flag;
243 close get_automerge_flag_csr;
244
245 if l_automerge_flag = 'N' or (l_automerge_flag = 'Y' and nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT'), 'MASTER') <> 'MASTER')
246 then
247
248 SELECT decode(party_type,'PERSON','HZ_PERSON_PROFILES',
249 'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
250 'HZ_ORGANIZATION_PROFILES') INTO l_party_type
251 FROM HZ_PARTIES
252 WHERE party_id=l_merge_to;
253
254 HZ_MERGE_ENTITY_ATTRI_PVT.create_merge_attributes(
255 l_batch_id, l_merge_to, l_party_type,
256 x_return_status, x_msg_count, x_msg_data);
257
258 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
259 ROLLBACK to create_merge_batch;
260 RETURN;
261 END IF;
262 end if; -- if l_automerge_flag = 'N'
263 ELSIF l_merge_type='SAME_PARTY_MERGE' THEN
264
265 l_batch_party_id := null;
266 l_merge_from := l_merge_to;
267
268 -- call table-handler to insert the record in HZ_MERGE_PARTIES
269 HZ_MERGE_PARTIES_PKG.INSERT_ROW(
270 px_BATCH_PARTY_ID => l_batch_party_id,
271 p_batch_id => l_batch_id,
272 p_merge_type => 'SAME_PARTY_MERGE',
273 p_from_party_id => l_merge_from,
274 p_to_party_id => l_merge_to,
275 p_merge_reason_code => 'DEDUPE',
276 p_merge_status => 'PENDING',
277 p_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
278 p_CREATION_DATE => HZ_UTILITY_V2PUB.CREATION_DATE,
279 p_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
280 p_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
281 p_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
282
283 END IF;
284 if p_default_mapping = 'Y'
285 then
286 -- call suggested default to populate suggested default mapping
287 -- to temp table HZ_MERGE_PARTIES_SUGG and HZ_MERGE_PARTYDTLS_SUGG
288 suggested_defaults(l_batch_id, x_return_status, x_msg_count, x_msg_data);
289
290 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
291 ROLLBACK to create_merge_batch;
292 RETURN;
293 END IF;
294
295 -- based on the profile value to decide if the user want to show
296 -- suggested default mapping at the beginning
297 -- or if automerge profile is on(pass 'Y' to p_default_mapping)
298 -- and it's an automerge dupset.
299
300 l_addr_match_rule := fnd_profile.value('HZ_SUGG_ADDR_MATCH_RULE');
301 l_reln_match_rule := fnd_profile.value('HZ_SUGG_RELN_MATCH_RULE');
302
303 -- Address
304 IF(fnd_profile.value('HZ_SHOW_SUGG_ADDR') = 'Y'
305 or (l_automerge_flag = 'Y' and l_addr_match_rule <>0 and l_addr_match_rule is not null )) THEN
306 apply_suggested_default(l_batch_id, 'HZ_PARTY_SITES', x_return_status, x_msg_count, x_msg_data);
307 END IF;
308 -- Relationship
309 IF(fnd_profile.value('HZ_SHOW_SUGG_RELN') = 'Y'
310 or (l_automerge_flag = 'Y' and l_reln_match_rule <>0 and l_reln_match_rule is not null)) THEN
311 apply_suggested_default(l_batch_id, 'HZ_PARTY_RELATIONSHIPS', x_return_status, x_msg_count, x_msg_data);
312 END IF;
313
314 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
315 ROLLBACK to create_merge_batch;
316 RETURN;
317 END IF;
318 end if; -- p_default_mapping = 'Y'
319
320 --get the object_version_number of the record in hz_dup_sets
321 --for locking purpose
322
323 SELECT object_version_number
324 INTO db_object_version_number
325 FROM hz_dup_sets
326 WHERE dup_set_id = p_dup_set_id
327 FOR UPDATE OF dup_set_id;
328
329 --if the 2 object version numbers are same then continue
330 --else raise exception
331 IF (
332 (db_object_version_number IS NULL AND p_object_version_number IS NULL )
333 OR ( db_object_version_number IS NOT NULL AND
334 p_object_version_number IS NOT NULL AND
335 db_object_version_number = p_object_version_number )
336 ) THEN
337
338 l_object_version_number := NVL(p_object_version_number, 1) + 1;
339
340 --Update the dup set table status and merge_batch_id column
341 UPDATE HZ_DUP_SETS
342 SET object_version_number = l_object_version_number
343 WHERE dup_set_id = p_dup_set_id;
344
345 p_object_version_number := l_object_version_number;
346 ELSE
347 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
348 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_dup_sets');
349 FND_MSG_PUB.ADD;
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352
353 ---Default mapping of the entities
354 IF p_default_mapping = 'Y' THEN
355 --call the smart search API for default mapping
356 null;
357 END IF;
358
359 x_merge_batch_id := l_batch_id;
360
361 -- standard call to get message count and if count is 1, get message info.
362 FND_MSG_PUB.Count_And_Get(
363 p_encoded => FND_API.G_FALSE,
364 p_count => x_msg_count,
365 p_data => x_msg_data);
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK to create_merge_batch;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 FND_MSG_PUB.Count_And_Get(
372 p_encoded => FND_API.G_FALSE,
373 p_count => x_msg_count,
374 p_data => x_msg_data);
375
376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
377 ROLLBACK to create_merge_batch;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 FND_MSG_PUB.Count_And_Get(
380 p_encoded => FND_API.G_FALSE,
381 p_count => x_msg_count,
382 p_data => x_msg_data);
383
384 WHEN OTHERS THEN
385 ROLLBACK to create_merge_batch;
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387
388 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
389 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
390 FND_MSG_PUB.ADD;
391
392 FND_MSG_PUB.Count_And_Get(
393 p_encoded => FND_API.G_FALSE,
394 p_count => x_msg_count,
395 p_data => x_msg_data);
396
397 END Create_Merge_Batch;
398
399 --Start of DLProject Changes
400 FUNCTION isContactGroupRelType(p_from_rel_id NUMBER,p_to_rel_id NUMBER)
401 RETURN boolean
402 IS
403
404 cursor c_contact_rel(p_relationship_id NUMBER) IS
405 select count(distinct code.owner_table_id)
406 from hz_code_assignments code
407 where code.class_category = 'RELATIONSHIP_TYPE_GROUP'
408 and code.class_code = 'PARTY_REL_GRP_CONTACTS'
409 and code.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
410 and exists ( select 1 from hz_relationship_types rt,
411 HZ_RELATIONSHIPS r
412 where r.relationship_id = p_relationship_id
413 and r.relationship_type = rt.relationship_type
414 and r.relationship_code = rt.forward_rel_code
415 and r.subject_type = rt.subject_type
416 and r.object_type = rt.object_type
417 and r.directional_flag = decode(rt.direction_code, 'N','F',
418 r.directional_flag)
419 and code.owner_table_id = rt.relationship_type_id
420 );
421
422 l_flag boolean;
423 l_count NUMBER;
424 BEGIN
425 l_flag := false;
426
427 open c_contact_rel(p_from_rel_id);
428 fetch c_contact_rel into l_count;
429 if(l_count >0)then
430 close c_contact_rel;
431 open c_contact_rel(p_to_rel_id);
432 fetch c_contact_rel into l_count;
433 if(l_count >0)then
434 l_flag := true;
435 end if;
436 end if;
437 close c_contact_rel;
438 return l_flag;
439 END;
440 --End of DLProject Changes
441
442 --
443 -- PROCEDURE map_detail_record
444 --
445 -- DESCRIPTION
446 -- Specifies Merging or transfer of merge from to the merge to
447 -- record.
448 --
449 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
450 --
451 -- ARGUMENTS
452 -- IN:
453 -- p_batch_party_id Batch_Party_id from the merge tables
454 -- p_entity Name of the entity HZ_PARTY_SITES,
455 -- HZ_PARTY_RELATIONSHIPS etc.
456 -- p_from_entity_id ID of the from record
457 -- p_to_entity_id ID of the to record
458
459 -- OUT:
460 -- x_return_status Return status after the call. The status can
461 -- be fnd_api.g_ret_sts_success (success),
462 -- fnd_api.g_ret_sts_error (error),
463 -- fnd_api.g_ret_sts_unexp_error
464 -- (unexpected error).
465 -- x_msg_count Number of messages in message stack.
466 -- x_msg_data Message text if x_msg_count is 1.
467 --
468 -- NOTES
469 --
470 -- MODIFICATION HISTORY
471 --
472 -- 04/01/2002 Jyoti Pandey o Created.
473 --
474 -- 05/10/2005 S V Sowjanya o Bug 4569674: Modified Tax jurisdiction validation
475 --
476
477 PROCEDURE map_detail_record(
478 p_batch_party_id IN NUMBER,
479 p_entity IN VARCHAR2,
480 p_from_entity_id IN NUMBER,
481 p_to_entity_id IN NUMBER,
482 p_object_version_number IN OUT NOCOPY NUMBER,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2) IS
486
487 l_total_count NUMBER;
488 l_count NUMBER;
489 l_batch_id NUMBER;
490 l_status HZ_MERGE_BATCH.BATCH_STATUS%TYPE;
491 l_loc NUMBER;
492 l_org NUMBER;
493 l_object_version_number NUMBER;
494 db_object_version_number NUMBER;
495
496 l_from_rel_type HZ_RELATIONSHIPS.RELATIONSHIP_CODE%TYPE; --4500011
497 l_to_rel_type HZ_RELATIONSHIPS.RELATIONSHIP_CODE%TYPE; --4500011
498 rel_batch_party_id NUMBER;
499 l_from_rel_party_id NUMBER;
500 l_to_rel_party_id NUMBER;
501 l_rel_party_count NUMBER;
502
503 l_mm VARCHAR2(255);
504 l_tmp NUMBER;
505 l_tmp2 NUMBER;
506 l_merge_to NUMBER;
507 l_dup_set_id NUMBER;
508 l_check NUMBER;
509 l_cust_sites NUMBER;
510
511 CURSOR c_address (cp_ps_id number) is
512 SELECT l.country,
513 l.city,
514 l.state,
515 l.county,
516 l.province,
517 l.postal_code,
518 ps.status,
519 ps.location_id --bug 4569674
520 FROM hz_locations l
521 , hz_party_sites ps
522 WHERE ps.party_site_id = cp_ps_id
523 AND ps.location_id = l.location_id;
524
525 CURSOR c_cust_site_check IS
526 select 1 from hz_cust_acct_sites_all where party_site_id=p_from_entity_id;
527
528
529 l_from_country HZ_LOCATIONS.COUNTRY%TYPE;
530 l_to_country HZ_LOCATIONS.COUNTRY%TYPE;
531 l_from_city HZ_LOCATIONS.CITY%TYPE;
532 l_to_city HZ_LOCATIONS.CITY%TYPE;
533 l_from_state HZ_LOCATIONS.STATE%TYPE;
534 l_to_state HZ_LOCATIONS.CITY%TYPE;
535 l_from_county HZ_LOCATIONS.COUNTY%TYPE;
536 l_to_county HZ_LOCATIONS.COUNTY%TYPE;
537 l_from_province HZ_LOCATIONS.PROVINCE%TYPE;
538 l_to_province HZ_LOCATIONS.PROVINCE%TYPE;
539 l_from_postal_code HZ_LOCATIONS.postal_code%TYPE;
540 l_to_postal_code HZ_LOCATIONS.postal_code%TYPE;
541 l_from_ps_status varchar2(1);
542 l_to_ps_status varchar2(1);
543 l_strucutre_id NUMBER;
544 l_qualifier VARCHAR2(30);
545 l_err_flg VARCHAR2(1) := 'N';
546
547 --bug 4569674
548 l_merge_yn VARCHAR2(2);
549 l_from_location_id NUMBER;
550 l_to_location_id NUMBER;
551
552 --Start of DLProject Changes
553 l_rel_type_count NUMBER;
554 --End of DLProject Changes
555
556 BEGIN
557
558 fnd_msg_pub.initialize;
559 x_return_status := FND_API.G_RET_STS_SUCCESS;
560
561 SAVEPOINT map_detail_record;
562
563 SELECT distinct(batch_id)
564 INTO l_batch_id
565 FROM hz_merge_parties
566 WHERE batch_party_id = p_batch_party_id;
567
568 IF (p_batch_party_id is null) OR (l_batch_id IS NULL )
569 OR (p_entity is null) OR (p_from_entity_id IS NULL) THEN
570 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
571 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BATCH_PARTY_ID');
572 FND_MSG_PUB.ADD;
573 RAISE FND_API.G_EXC_ERROR;
574 END IF;
575
576 BEGIN
577 SELECT mandatory_merge, merge_to_entity_id INTO l_mm, l_merge_to
578 FROM hz_merge_party_details
579 where batch_party_id=p_batch_party_id
580 AND entity_name = p_entity
581 AND merge_from_entity_id = p_from_entity_id;
582
583 IF l_mm = 'Y' THEN
584 FND_MESSAGE.set_name('AR','HZ_MAND_MERGE_ERROR');
585 FND_MESSAGE.set_token('ENTITY1',p_from_entity_id);
586 FND_MESSAGE.set_token('ENTITY2',p_to_entity_id);
587 FND_MSG_PUB.ADD;
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590
591 IF p_from_entity_id = l_merge_to THEN
592 /* Check if any entity is merged to p_from_entity_id */
593 SELECT count(1) INTO l_tmp
594 FROM hz_merge_party_details md1, hz_merge_party_details md2,
595 hz_merge_parties mp1, hz_merge_parties mp2
596 where md1.batch_party_id=p_batch_party_id
597 AND md1.batch_party_id = mp1.batch_party_id
598 AND mp1.batch_id = mp2.batch_id
599 AND md2.batch_party_id=mp2.batch_party_id
600 AND md2.entity_name = p_entity
601 AND md2.merge_to_entity_id = p_from_entity_id
602 AND md2.merge_from_entity_id<>p_from_entity_id;
603
604 /* Check if p_to_entity_id is merged to another entity */
605 SELECT count(1) INTO l_tmp2
606 FROM hz_merge_party_details md1, hz_merge_party_details md2,
607 hz_merge_parties mp1, hz_merge_parties mp2
608 where md1.batch_party_id=p_batch_party_id
609 AND md1.batch_party_id = mp1.batch_party_id
610 AND mp1.batch_id = mp2.batch_id
611 AND md2.batch_party_id=mp2.batch_party_id
612 AND md2.entity_name = p_entity
613 AND md2.merge_from_entity_id = p_to_entity_id
614 AND md2.merge_to_entity_id<>p_to_entity_id;
615
616 IF l_tmp>0 OR l_tmp2>0 THEN
617 FND_MESSAGE.set_name('AR','HZ_CANNOT_UNMAP_ERROR');
618 FND_MSG_PUB.ADD;
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621 END IF;
622
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
626 FND_MESSAGE.SET_TOKEN('PARAMETER', 'MERGE_FROM_ENTITY_ID');
627 FND_MSG_PUB.ADD;
628 RAISE FND_API.G_EXC_ERROR;
629 END;
630
631 ---Validation for locations/Party sites
632 IF p_entity = 'HZ_PARTY_SITES' THEN
633 IF p_to_entity_id IS NOT NULL THEN
634 OPEN c_cust_site_check;
635 Fetch c_cust_site_check into l_cust_sites;
636 IF c_cust_site_check %NOTFOUND THEN
637 null;
638 ELSE
639 OPEN c_address (p_from_entity_id);
640 FETCH c_address INTO l_from_country,l_from_city,l_from_state,
641 l_from_county,l_from_province,l_from_postal_code, l_from_ps_status, l_from_location_id;
642 CLOSE c_address;
643
644 OPEN c_address (p_to_entity_id);
645 FETCH c_address INTO l_to_country,l_to_city,l_to_state,
646 l_to_county,l_to_province,l_to_postal_code, l_to_ps_status, l_to_location_id;
647 CLOSE c_address;
648
649 if l_from_ps_status = 'A' and l_to_ps_status = 'I'
650 then
651 FND_MESSAGE.set_name('AR','HZ_DL_ADDR_MASTER_ERR');
652 FND_MSG_PUB.ADD;
653 RAISE FND_API.G_EXC_ERROR;
654 end if;
655 --bug 4569674
656 l_merge_yn := null;
657
658 ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
659 l_to_location_id,
660 FND_API.G_FALSE,
661 l_merge_yn,
662 x_return_status,
663 x_msg_count,
664 x_msg_data);
665 IF l_merge_yn = 'N' THEN
666 FND_MESSAGE.set_name('AR','HZ_PS_LOC_ASSIGN_ERROR');
667 FND_MSG_PUB.ADD;
668 RAISE FND_API.G_EXC_ERROR;
669 END IF;
670
671 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
672 RAISE FND_API.G_EXC_ERROR;
673 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676 --bug 4569674
677 END IF;
678 END IF;
679 CLOSE c_cust_site_check;
680 ELSIF p_entity = 'HZ_PARTY_RELATIONSHIPS' then
681
682 l_from_rel_party_id :=
683 HZ_MERGE_UTIL.get_reln_party_id(p_from_entity_id);
684 l_to_rel_party_id :=
685 HZ_MERGE_UTIL.get_reln_party_id(p_to_entity_id);
686
687 SELECT count(*) into l_rel_party_count
688 FROM hz_merge_parties
689 WHERE batch_id = l_batch_id
690 AND merge_type = 'PARTY_MERGE'
691 AND from_party_id = l_from_rel_party_id;
692
693 /* Clean up merge parties */
694 DELETE FROM HZ_MERGE_PARTIES
695 WHERE batch_id = l_batch_id
696 AND merge_type = 'PARTY_MERGE'
697 AND (from_party_id = l_from_rel_party_id
698 OR to_party_id = l_from_rel_party_id);
699
700 IF ( (p_to_entity_id is not null ) AND
701 (p_from_entity_id <> p_to_entity_id) ) THEN
702 --check if the relationship types are same
703 select pr1.relationship_code from_rel_type,
704 pr2.relationship_code to_rel_type
705 into l_from_rel_type,l_to_rel_type
706 from hz_relationships pr1, hz_relationships pr2 --bug 4500011 replaced hz_party_relationships with hz_relationships
707 where pr1.relationship_id = p_from_entity_id
708 and pr2.relationship_id = p_to_entity_id
709 AND pr1.subject_table_name = 'HZ_PARTIES'
710 AND pr1.object_table_name = 'HZ_PARTIES'
711 AND pr1.directional_flag = 'F'
712 AND pr2.subject_table_name = 'HZ_PARTIES'
713 AND pr2.object_table_name = 'HZ_PARTIES'
714 AND pr2.directional_flag = 'F';
715
716 IF l_from_rel_type <> l_to_rel_type THEN
717
718 --Don't raise the error , If both the relationships are in the contact relationship group
719
720 IF(NOT isContactGroupRelType(p_from_entity_id,p_to_entity_id)) THEN
721 FND_MESSAGE.set_name('AR','HZ_REL_NOT_SIMILAR');
722 FND_MSG_PUB.ADD;
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725
726 END IF;
727
728 IF l_from_rel_party_id IS NOT NULL AND
729 l_to_rel_party_id IS NOT NULL THEN
730
731 HZ_MERGE_PARTIES_PKG.Insert_Row(
732 rel_batch_party_id,
733 l_BATCH_ID,
734 'PARTY_MERGE',
735 l_from_rel_party_id,
736 l_to_rel_party_id,
737 'DUPLICATE_RELN_PARTY',
738 'PENDING',
739 HZ_UTILITY_V2PUB.CREATED_BY,
740 HZ_UTILITY_V2PUB.CREATION_DATE,
741 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
742 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
743 HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
744
745 --Also insert the Party sites for reln Party
746 -- if there are any
747 insert_party_site_details(l_from_rel_party_id,
748 l_to_rel_party_id,
749 rel_batch_party_id,
750 'Y');
751 END IF; --l_rel_party_count
752
753 ELSIF p_to_entity_id is null THEN
754
755 IF l_rel_party_count > 0 THEN
756
757 select batch_party_id into rel_batch_party_id
758 from hz_merge_parties
759 where batch_id = l_batch_id
760 and merge_type = 'PARTY_MERGE'
761 and from_party_id = l_from_rel_party_id;
762
763 HZ_MERGE_PARTIES_PKG.delete_Row(rel_batch_party_id);
764
765 DELETE FROM hz_merge_party_details
766 WHERE batch_party_id = rel_batch_party_id;
767
768 END IF; --l_rel_party_count
769
770 END IF; --p_to_entity_id is not null
771 END IF; --p_entity
772
773 --get the object_version_number of the record in hz_merge_party_details
774 --for locking purpose
775
776 SELECT object_version_number
777 INTO db_object_version_number
778 FROM hz_merge_party_details
779 WHERE merge_from_entity_id = p_from_entity_id
780 AND batch_party_id = p_batch_party_id
781 AND entity_name = p_entity
782 FOR UPDATE OF merge_from_entity_id, batch_party_id,entity_name nowait;
783
784
785 IF(db_object_version_number IS NOT NULL AND p_object_version_number IS NULL) THEN
786 p_object_version_number := db_object_version_number;
787 END IF;
788
789 --if the 2 object version numbers are same then continue
790 --else raise exception
791
792 IF (
793 (db_object_version_number IS NULL AND p_object_version_number IS NULL )
794 OR ( db_object_version_number IS NOT NULL AND
795 p_object_version_number IS NOT NULL AND
796 db_object_version_number = p_object_version_number )
797 ) THEN
798
799 ---Update the HZ_MERGE_PARTY_DETAILS table
800 l_object_version_number := NVL(p_object_version_number, 1) + 1;
801
802 UPDATE HZ_MERGE_PARTY_DETAILS
803 SET merge_to_entity_id = p_to_entity_id,
804 object_version_number = l_object_version_number
805 WHERE merge_from_entity_id = p_from_entity_id
806 AND batch_party_id = p_batch_party_id
807 AND entity_name = p_entity;
808
809 p_object_version_number := l_object_version_number;
810
811 SELECT batch_id
812 INTO l_dup_set_id
813 FROM hz_merge_parties
814 WHERE batch_party_id = p_batch_party_id
815 AND ROWNUM = 1;
816
817 UPDATE HZ_DUP_SETS
818 SET STATUS = 'MAPPING',
819 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
820 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
821 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
822 WHERE DUP_SET_ID = l_dup_set_id;
823
824 UPDATE HZ_MERGE_BATCH
825 SET batch_status = 'IN_PROCESS',
826 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
827 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
828 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
829 WHERE BATCH_ID = l_dup_set_id;
830
831 ELSE
832 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
833 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_merge_party_details');
834 FND_MSG_PUB.ADD;
835 RAISE FND_API.G_EXC_ERROR;
836 END IF;
837
838
839 -- standard call to get message count and if count is 1, get message info.
840 FND_MSG_PUB.Count_And_Get(
841 p_encoded => FND_API.G_FALSE,
842 p_count => x_msg_count,
843 p_data => x_msg_data);
844
845 EXCEPTION
846 WHEN FND_API.G_EXC_ERROR THEN
847 ROLLBACK to map_detail_record ;
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 FND_MSG_PUB.Count_And_Get(
850 p_encoded => FND_API.G_FALSE,
851 p_count => x_msg_count,
852 p_data => x_msg_data);
853
854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
855 ROLLBACK to map_detail_record ;
856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
857 FND_MSG_PUB.Count_And_Get(
858 p_encoded => FND_API.G_FALSE,
859 p_count => x_msg_count,
860 p_data => x_msg_data);
861
862 WHEN OTHERS THEN
863 ROLLBACK to map_detail_record;
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
865
866 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
867 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
868 FND_MSG_PUB.ADD;
869
870
871 FND_MSG_PUB.Count_And_Get(
872 p_encoded => FND_API.G_FALSE,
873 p_count => x_msg_count,
874 p_data => x_msg_data);
875
876 END map_detail_record;
877
878 --
879 -- PROCEDURE map_within_party
880 --
881 -- DESCRIPTION
882 -- Specifies Merging or transfer of merge from to the merge to
883 -- record for Party Merge within the same party.
884 --
885 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
886 --
887 -- ARGUMENTS
888 -- IN:
889 -- p_batch_party_id Batch_Party_id from the merge tables
890 -- p_entity Name of the entity HZ_PARTY_SITES,
891 -- HZ_RELATIONSHIPS etc.
892 -- p_from_entity_id ID of the from record
893 -- p_to_entity_id ID of the to record
894
895 -- OUT:
896 -- x_return_status Return status after the call. The status can
897 -- be fnd_api.g_ret_sts_success (success),
898 -- fnd_api.g_ret_sts_error (error),
899 -- fnd_api.g_ret_sts_unexp_error
900 -- (unexpected error).
901 -- x_msg_count Number of messages in message stack.
902 -- x_msg_data Message text if x_msg_count is 1.
903 --
904 -- NOTES
905 --
906 -- MODIFICATION HISTORY
907 --
908 -- 04/01/2002 Jyoti Pandey o Created.
909 -- 05/10/2005 S V Sowjanya o Bug 4569674: Modified Tax jurisdiction validation
910 PROCEDURE map_within_party(
911 p_batch_party_id IN NUMBER,
912 p_entity IN VARCHAR2,
913 p_from_entity_id IN NUMBER,
914 p_to_entity_id IN NUMBER,
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_msg_count OUT NOCOPY NUMBER,
917 x_msg_data OUT NOCOPY VARCHAR2) IS
918
919 l_batch_id NUMBER;
920 l_from_party_id NUMBER;
921 l_to_party_id NUMBER;
922
923 l_merge_type HZ_MERGE_PARTIES.MERGE_TYPE%TYPE;
924 l_status HZ_MERGE_BATCH.BATCH_STATUS%TYPE;
925 l_from_site_party_id NUMBER;
926 l_to_site_party_id NUMBER;
927 l_from_rel_type HZ_RELATIONSHIPS.RELATIONSHIP_TYPE%TYPE;
928 l_to_rel_type HZ_RELATIONSHIPS.RELATIONSHIP_TYPE%TYPE;
929 l_from_dflag HZ_RELATIONSHIPS.directional_flag%type;
930 l_to_dflag HZ_RELATIONSHIPS.directional_flag%type;
931
932 l_map_dtl_count NUMBER;
933 rel_batch_party_id NUMBER;
934 l_from_rel_party_id NUMBER;
935 l_to_rel_party_id NUMBER;
936 l_rel_party_count NUMBER;
937 l_loc NUMBER;
938 l_org NUMBER;
939 l_total_count NUMBER;
940 l_cust_sites NUMBER;
941
942 ----------------3738622-------------------------------------
943 /*CURSOR c_loc_assignments(cp_from_ps_id NUMBER, cp_to_ps_id NUMBER) IS
944 SELECT la.loc_id, la.org_id
945 FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
946 WHERE ps.party_site_id = cp_from_ps_id
947 AND la.location_id = ps.location_id
948 MINUS
949 SELECT la.loc_id, la.org_id
950 FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
951 WHERE ps.party_site_id = cp_to_ps_id
952 AND la.location_id = ps.location_id;
953 */
954
955 CURSOR c_address (cp_ps_id number) is
956 SELECT l.country,
957 l.city,
958 l.state,
959 l.county,
960 l.province,
961 l.postal_code,
962 ps.status,
963 ps.location_id --bug 4569674
964 FROM hz_locations l
965 , hz_party_sites ps
966 WHERE ps.party_site_id = cp_ps_id
967 AND ps.location_id = l.location_id;
968
969 CURSOR c_cust_site_check IS
970 select 1 from hz_cust_acct_sites_all where party_site_id=p_from_entity_id;
971
972
973 l_count NUMBER;
974 l_from_country HZ_LOCATIONS.COUNTRY%TYPE;
975 l_to_country HZ_LOCATIONS.COUNTRY%TYPE;
976 l_from_city HZ_LOCATIONS.CITY%TYPE;
977 l_to_city HZ_LOCATIONS.CITY%TYPE;
978 l_from_state HZ_LOCATIONS.STATE%TYPE;
979 l_to_state HZ_LOCATIONS.CITY%TYPE;
980 l_from_county HZ_LOCATIONS.COUNTY%TYPE;
981 l_to_county HZ_LOCATIONS.COUNTY%TYPE;
982 l_from_province HZ_LOCATIONS.PROVINCE%TYPE;
983 l_to_province HZ_LOCATIONS.PROVINCE%TYPE;
984 l_from_postal_code HZ_LOCATIONS.postal_code%TYPE;
985 l_to_postal_code HZ_LOCATIONS.postal_code%TYPE;
986 l_from_ps_status varchar2(1);
987 l_to_ps_status varchar2(1);
988 l_strucutre_id NUMBER;
989 l_qualifier VARCHAR2(30);
990 l_err_flg VARCHAR2(1) := 'N';
991
992 --bug 4569674
993 l_merge_yn VARCHAR2(2);
994 l_from_location_id NUMBER;
995 l_to_location_id NUMBER;
996 ----------------3738622-------------------------------------
997
998 CURSOR c_map_detail_record_exist(cp_batch_party_id IN NUMBER,
999 cp_entity_name IN VARCHAR2,
1000 cp_from_entity_id IN NUMBER) IS
1001 SELECT count(1)
1002 FROM hz_merge_party_details
1003 WHERE batch_party_id = cp_batch_party_id
1004 AND entity_name = cp_entity_name
1005 AND merge_from_entity_id = cp_from_entity_id;
1006
1007
1008 BEGIN
1009
1010 x_return_status := FND_API.G_RET_STS_SUCCESS;
1011 fnd_msg_pub.initialize;
1012 SAVEPOINT map_within_party;
1013
1014 --Get the batch_id and party_id for the same party merge
1015 SELECT DISTINCT batch_id, from_party_id, to_party_id ,merge_type
1016 INTO l_batch_id, l_from_party_id, l_to_party_id , l_merge_type
1017 FROM hz_merge_parties
1018 WHERE batch_party_id = p_batch_party_id;
1019
1020 ---Check for valid batch id
1021 IF (p_batch_party_id is null) or (l_batch_id is null )
1022 OR (p_entity is null) or (p_from_entity_id is null) THEN
1023 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
1024 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BATCH_PARTY_ID');
1025 FND_MSG_PUB.ADD;
1026 RAISE FND_API.G_EXC_ERROR;
1027 end if;
1028
1029 --check if the from and to_party are the same
1030 IF l_from_party_id <> l_to_party_id then
1031 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
1032 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BATCH_PARTY_ID');
1033 FND_MSG_PUB.ADD;
1034 RAISE FND_API.G_EXC_ERROR;
1035 END IF;
1036
1037
1038 IF l_merge_type <> 'SAME_PARTY_MERGE' THEN
1039 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
1040 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BATCH_PARTY_ID');
1041 FND_MSG_PUB.ADD;
1042 RAISE FND_API.G_EXC_ERROR;
1043 END IF;
1044
1045 --check if the record already exists
1046 OPEN c_map_detail_record_exist(p_batch_party_id,p_entity,p_from_entity_id);
1047 FETCH c_map_detail_record_exist into l_map_dtl_count;
1048 CLOSE c_map_detail_record_exist;
1049
1050 IF l_map_dtl_count > 0 AND p_to_entity_id IS NOT NULL THEN
1051 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_REC_PRESENT');
1052 FND_MESSAGE.SET_TOKEN('ID', p_from_entity_id);
1053 FND_MSG_PUB.ADD;
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056
1057
1058
1059 ---Validation for locations/Party sites
1060 IF p_entity = 'HZ_PARTY_SITES' THEN
1061
1062 IF p_to_entity_id IS NOT NULL THEN
1063 -----------Bug No: 3738622---------------------------------
1064 /*OPEN c_loc_assignments(p_from_entity_id,p_to_entity_id);
1065 FETCH c_loc_assignments INTO l_loc, l_org;
1066 IF c_loc_assignments%FOUND THEN
1067 CLOSE c_loc_assignments;
1068 FND_MESSAGE.set_name('AR','HZ_PS_LOC_ASSIGN_ERROR');
1069 FND_MSG_PUB.ADD;
1070 RAISE FND_API.G_EXC_ERROR;
1071 END IF;
1072 */
1073 OPEN c_cust_site_check;
1074 Fetch c_cust_site_check into l_cust_sites;
1075 IF c_cust_site_check %NOTFOUND THEN
1076 null;
1077 ELSE
1078 OPEN c_address (p_from_entity_id);
1079 FETCH c_address INTO l_from_country,l_from_city,l_from_state,
1080 l_from_county,l_from_province,l_from_postal_code, l_from_ps_status,l_from_location_id;
1081 CLOSE c_address;
1082
1083 OPEN c_address (p_to_entity_id);
1084 FETCH c_address INTO l_to_country,l_to_city,l_to_state,
1085 l_to_county,l_to_province,l_to_postal_code, l_to_ps_status,l_to_location_id;
1086 CLOSE c_address;
1087
1088 if l_from_ps_status = 'A' and l_to_ps_status = 'I'
1089 then
1090 FND_MESSAGE.set_name('AR','HZ_DL_ADDR_MASTER_ERR');
1091 FND_MSG_PUB.ADD;
1092 RAISE FND_API.G_EXC_ERROR;
1093 end if;
1094
1095 --bug 4569674
1096 l_merge_yn := null;
1097
1098 ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
1099 l_to_location_id,
1100 FND_API.G_FALSE,
1101 l_merge_yn,
1102 x_return_status,
1103 x_msg_count,
1104 x_msg_data);
1105 IF l_merge_yn = 'N' THEN
1106 FND_MESSAGE.set_name('AR','HZ_PS_LOC_ASSIGN_ERROR');
1107 FND_MSG_PUB.ADD;
1108 RAISE FND_API.G_EXC_ERROR;
1109 END IF;
1110
1111
1112 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1113 RAISE FND_API.G_EXC_ERROR;
1114 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 END IF;
1117 --4569674
1118 -----------Bug No: 3738622---------------------------------
1119
1120 --check if the party sites are for the same party
1121 select ps1.party_id from_site_party_id ,
1122 ps2.party_id to_site_party_id
1123 into l_from_site_party_id, l_to_site_party_id
1124 from hz_party_sites ps1 , hz_party_sites ps2
1125 where ps1.party_site_id = p_from_entity_id
1126 and ps2.party_site_id = p_to_entity_id;
1127
1128 --Both from and to party_site_id's should point to same party_id
1129 --and that party should be the one that is getting merged
1130 IF ( (l_from_site_party_id <> l_to_site_party_id) AND
1131 (l_from_site_party_id <> l_from_party_id) ) THEN
1132 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
1133 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BATCH_PARTY_ID');
1134 FND_MSG_PUB.ADD;
1135 RAISE FND_API.G_EXC_ERROR;
1136 END IF;
1137 END IF;
1138 CLOSE c_cust_site_check;
1139 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1140 p_batch_party_id,
1141 'HZ_PARTY_SITES',
1142 p_from_entity_id,
1143 p_to_entity_id,
1144 'N',
1145 hz_utility_v2pub.created_by,
1146 hz_utility_v2pub.creation_Date,
1147 hz_utility_v2pub.last_update_login,
1148 hz_utility_v2pub.last_update_date,
1149 hz_utility_v2pub.last_updated_by);
1150
1151 ELSE
1152 IF l_map_dtl_count >0 THEN
1153 HZ_MERGE_PARTY_DETAILS_PKG.delete_row(
1154 p_batch_party_id, 'HZ_PARTY_SITES', p_from_entity_id);
1155 END IF;
1156 END IF;
1157
1158 ELSIF p_entity = 'HZ_PARTY_RELATIONSHIPS' THEN
1159
1160 IF p_to_entity_id IS NOT NULL THEN
1161 select r1.relationship_type from_rel_type ,
1162 r1.directional_flag from_dflag,
1163 r2.relationship_type to_rel_type,
1164 r2.directional_flag to_dflag
1165 into l_from_rel_type, l_from_dflag,
1166 l_to_rel_type , l_to_dflag
1167 from hz_relationships r1, hz_relationships r2
1168 where r1.relationship_id = p_from_entity_id
1169 and r1.object_id = l_from_party_id
1170 and r2.relationship_id = p_to_entity_id
1171 and r2.object_id = l_to_party_id;
1172
1173
1174 IF l_from_rel_type <> l_to_rel_type OR l_from_dflag <> l_to_dflag THEN
1175 IF(NOT isContactGroupRelType(p_from_entity_id,p_to_entity_id)) THEN
1176 FND_MESSAGE.set_name('AR','HZ_REL_NOT_SIMILAR');
1177 FND_MSG_PUB.ADD;
1178 RAISE FND_API.G_EXC_ERROR;
1179 END IF;
1180 END IF;
1181
1182 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1183 p_batch_party_id,
1184 'HZ_PARTY_RELATIONSHIPS',
1185 p_from_entity_id,
1186 p_to_entity_id,
1187 'N',
1188 hz_utility_v2pub.created_by,
1189 hz_utility_v2pub.creation_Date,
1190 hz_utility_v2pub.last_update_login,
1191 hz_utility_v2pub.last_update_date,
1192 hz_utility_v2pub.last_updated_by);
1193
1194
1195 l_from_rel_party_id :=
1196 HZ_MERGE_UTIL.get_reln_party_id(p_from_entity_id);
1197 l_to_rel_party_id :=
1198 HZ_MERGE_UTIL.get_reln_party_id(p_to_entity_id);
1199
1200 SELECT count(1) INTO l_rel_party_count
1201 FROM hz_merge_parties
1202 WHERE batch_id = l_batch_id
1203 AND merge_type = 'PARTY_MERGE'
1204 AND from_party_id = l_from_rel_party_id;
1205
1206
1207 IF ( (l_from_rel_party_id is not null ) AND
1208 (l_to_rel_party_id is not null) AND
1209 (l_from_rel_party_id <> l_to_rel_party_id) ) THEN
1210
1211 --Insert parties if the rel party is not present already
1212 IF l_rel_party_count = 0 THEN
1213
1214 HZ_MERGE_PARTIES_PKG.Insert_Row(
1215 rel_batch_party_id,
1216 l_batch_id,
1217 'PARTY_MERGE',
1218 l_from_rel_party_id,
1219 l_to_rel_party_id,
1220 'DUPLICATE_RELN_PARTY',
1221 'PENDING',
1222 HZ_UTILITY_V2PUB.CREATED_BY,
1223 HZ_UTILITY_V2PUB.CREATION_DATE,
1224 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1225 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1226 HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
1227
1228 --Also insert the Party sites for reln Party
1229 -- if there are any
1230 insert_party_site_details(l_from_rel_party_id,
1231 l_to_rel_party_id,
1232 rel_batch_party_id,'Y');
1233
1234 END IF; --l_rel_party_count
1235 end if; --l_from_rel_party_id is not null
1236 ELSE
1237 IF l_map_dtl_count>0 THEN
1238 HZ_MERGE_PARTY_DETAILS_PKG.delete_row(
1239 p_batch_party_id, 'HZ_PARTY_RELATIONSHIPS', p_from_entity_id);
1240
1241 l_from_rel_party_id :=
1242 HZ_MERGE_UTIL.get_reln_party_id(p_from_entity_id);
1243 l_to_rel_party_id :=
1244 HZ_MERGE_UTIL.get_reln_party_id(p_to_entity_id);
1245
1246 SELECT count(1) INTO l_rel_party_count
1247 FROM hz_merge_parties
1248 WHERE batch_id = l_batch_id
1249 AND merge_type = 'PARTY_MERGE'
1250 AND from_party_id = l_from_rel_party_id;
1251
1252
1253 IF l_rel_party_count > 0 THEN
1254
1255 SELECT batch_party_id into rel_batch_party_id
1256 FROM hz_merge_parties
1257 WHERE batch_id = l_batch_id
1258 AND merge_type = 'PARTY_MERGE'
1259 AND from_party_id = l_from_rel_party_id;
1260
1261 HZ_MERGE_PARTIES_PKG.delete_Row(rel_batch_party_id);
1262
1263 DELETE FROM hz_merge_party_details
1264 WHERE batch_party_id = rel_batch_party_id;
1265
1266 end if; --l_rel_party_count
1267 end if; -- l_map_dtl_count
1268 end if; -- p_to_entity_id
1269
1270
1271 end if; --p_entity
1272
1273 UPDATE HZ_DUP_SETS
1274 SET STATUS = 'MAPPING',
1275 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1276 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1277 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1278 WHERE DUP_SET_ID = (SELECT batch_id FROM hz_merge_parties
1279 WHERE batch_party_id = p_batch_party_id
1280 AND ROWNUM = 1);
1281
1282 UPDATE HZ_MERGE_BATCH
1283 SET batch_status = 'IN_PROCESS',
1284 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1285 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1286 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1287 WHERE BATCH_ID = (SELECT batch_id FROM hz_merge_parties
1288 WHERE batch_party_id = p_batch_party_id
1289 AND ROWNUM = 1);
1290
1291 -- standard call to get message count and if count is 1, get message info.
1292 FND_MSG_PUB.Count_And_Get(
1293 p_encoded => FND_API.G_FALSE,
1294 p_count => x_msg_count,
1295 p_data => x_msg_data);
1296
1297 EXCEPTION
1298 WHEN FND_API.G_EXC_ERROR THEN
1299 ROLLBACK to map_within_party;
1300 x_return_status := FND_API.G_RET_STS_ERROR;
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 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1307 ROLLBACK to map_within_party;
1308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1309 FND_MSG_PUB.Count_And_Get(
1310 p_encoded => FND_API.G_FALSE,
1311 p_count => x_msg_count,
1312 p_data => x_msg_data);
1313
1314 WHEN OTHERS THEN
1315 ROLLBACK to map_within_party;
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317
1318 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1319 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1320 FND_MSG_PUB.ADD;
1321
1322
1323 FND_MSG_PUB.Count_And_Get(
1324 p_encoded => FND_API.G_FALSE,
1325 p_count => x_msg_count,
1326 p_data => x_msg_data);
1327
1328 END map_within_party;
1329
1330
1331 -- PROCEDURE submit_batch
1332 --
1333 -- DESCRIPTION
1334 -- Submits a concurrent request for the batch if the mapping
1335 -- is complete
1336 --
1337 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1338 --
1339 -- ARGUMENTS
1340 -- IN:
1341 -- p_batch_id ID of the batch
1342
1343 -- OUT:
1344 -- x_request_id Request ID of the concurrent program
1345 -- x_return_status Return status after the call. The status can
1346 -- be fnd_api.g_ret_sts_success (success),
1347 -- fnd_api.g_ret_sts_error (error),
1348 -- fnd_api.g_ret_sts_unexp_error
1349 -- (unexpected error).
1350 -- x_msg_count Number of messages in message stack.
1351 -- x_msg_data Message text if x_msg_count is 1.
1352 --
1353 -- NOTES
1354 --
1355 -- MODIFICATION HISTORY
1356 --
1357 -- 04/01/2002 Jyoti Pandey o Created.
1358 --
1359 --
1360
1361 PROCEDURE submit_batch(
1362 p_batch_id IN NUMBER,
1363 p_preview IN VARCHAR2,
1364 x_request_id OUT NOCOPY NUMBER,
1365 x_return_status OUT NOCOPY VARCHAR2,
1366 x_msg_count OUT NOCOPY NUMBER,
1367 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1368
1369 l_request_id NUMBER := NULL;
1370 l_preview VARCHAR2(1);
1371
1372 l_last_request_id NUMBER;
1373 l_conc_phase VARCHAR2(80);
1374 l_conc_status VARCHAR2(80);
1375 l_conc_dev_phase VARCHAR2(30);
1376 l_conc_dev_status VARCHAR2(30);
1377 l_message VARCHAR2(240);
1378 call_status boolean;
1379 l_batch_status HZ_MERGE_BATCH.BATCH_STATUS%TYPE;
1380 l_dup_set_status VARCHAR2(30);
1381 retcode number;
1382
1383 BEGIN
1384
1385 x_return_status := FND_API.G_RET_STS_SUCCESS;
1386 fnd_msg_pub.initialize;
1387
1388 SAVEPOINT submit_batch;
1389
1390 IF p_preview is null THEN
1391 l_preview := 'Y';
1392 ELSE
1393 l_preview := p_preview;
1394 END IF;
1395
1396 SELECT batch_status , request_id
1397 INTO l_batch_status ,l_last_request_id
1398 FROM hz_merge_batch
1399 WHERE batch_id = p_batch_id;
1400
1401 -- a data librarian dup set must have merge batch
1402 SELECT ds.status
1403 INTO l_dup_set_status
1404 FROM hz_dup_sets ds, hz_merge_batch mb
1405 WHERE ds.dup_set_id = mb.batch_id
1406 AND ds.dup_set_id = p_batch_id;
1407
1408 IF l_last_request_id IS NOT NULL THEN
1409 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(
1410 request_id => l_last_request_id,
1411 phase => l_conc_phase,
1412 status => l_conc_status,
1413 dev_phase => l_conc_dev_phase,
1414 dev_status => l_conc_dev_status,
1415 message => l_message ) ;
1416
1417 IF l_conc_dev_phase <> 'COMPLETE' THEN
1418 FND_MESSAGE.set_name('AR', 'HZ_CANNOT_SUBMIT_PROCESSING');
1419 FND_MSG_PUB.ADD;
1420 RAISE FND_API.G_EXC_ERROR;
1421 ELSE
1422 IF l_conc_dev_status <>'NORMAL' THEN
1423 l_request_id := fnd_request.submit_request('AR',
1424 'ARHPMERGE',
1425 'Party Merge Concurrent Request',
1426 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
1427 FALSE,
1428 p_batch_id, l_preview );
1429
1430 IF l_request_id = 0 THEN
1431 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1432 -- FND_MESSAGE.RETRIEVE(l_message);
1433 FND_MSG_PUB.ADD;
1434 RAISE FND_API.G_EXC_ERROR;
1435 retcode := 2;
1436 RETURN;
1437 END IF;
1438
1439 ELSE --if completed normally and the status of the dup sets is still ERROR
1440 -- Added MAPPING for bug 3327496
1441 IF l_dup_set_status in ('ERROR','MAPPING') THEN
1442 l_request_id := fnd_request.submit_request('AR',
1443 'ARHPMERGE',
1444 'Party Merge Concurrent Request',
1445 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
1446 FALSE,
1447 p_batch_id, l_preview );
1448 IF l_request_id = 0 THEN
1449 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1450 FND_MSG_PUB.ADD;
1451 RAISE FND_API.G_EXC_ERROR;
1452 retcode := 2;
1453 RETURN;
1454 END IF;
1455 ELSE
1456 FND_MESSAGE.set_name('AR', 'HZ_CANNOT_SUBMIT_REQUEST');
1457 FND_MSG_PUB.ADD;
1458 RAISE FND_API.G_EXC_ERROR;
1459 END IF;
1460 END IF; --l_conc_status
1461 END IF; --l_conc_dev_phase
1462
1463 ELSE ---last_request_id is null
1464 l_request_id := fnd_request.submit_request('AR'
1465 ,'ARHPMERGE'
1466 ,'Party Merge Concurrent Request'
1467 ,to_char(sysdate,'DD-MON-YY HH24:MI:SS')
1468 ,FALSE,
1469 to_char(p_batch_id), l_preview );
1470
1471 IF l_request_id = 0 THEN
1472 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1473 -- FND_MESSAGE.RETRIEVE(l_message);
1474 FND_MSG_PUB.ADD;
1475 RAISE FND_API.G_EXC_ERROR;
1476 -- retcode := 2;
1477 -- RETURN;
1478 END IF;
1479
1480 END IF; ---last_request_id
1481
1482 -- if batch is resubmitted, update the status
1483 -- fix bug 3081305
1484 IF l_request_id is not null THen
1485 UPDATE HZ_MERGE_BATCH
1486 SET batch_status = 'SUBMITTED' ,
1487 request_id = l_request_id,
1488 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1489 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1490 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1491 WHERE batch_id = p_batch_id;
1492
1493 UPDATE HZ_DUP_SETS
1494 SET status = 'SUBMITTED',
1495 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1496 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1497 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1498 WHERE dup_set_id = p_batch_id;
1499 END IF;
1500
1501 x_request_id := l_request_id;
1502
1503 -- standard call to get message count and if count is 1, get message info.
1504 FND_MSG_PUB.Count_And_Get(
1505 p_encoded => FND_API.G_FALSE,
1506 p_count => x_msg_count,
1507 p_data => x_msg_data);
1508
1509 EXCEPTION
1510 WHEN FND_API.G_EXC_ERROR THEN
1511 ROLLBACK to submit_batch;
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 FND_MSG_PUB.Count_And_Get(
1514 p_encoded => FND_API.G_FALSE,
1515 p_count => x_msg_count,
1516 p_data => x_msg_data);
1517
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 ROLLBACK to submit_batch;
1520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1521 FND_MSG_PUB.Count_And_Get(
1522 p_encoded => FND_API.G_FALSE,
1523 p_count => x_msg_count,
1524 p_data => x_msg_data);
1525
1526 WHEN OTHERS THEN
1527 ROLLBACK to submit_batch;
1528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1529
1530 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1531 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1532 FND_MSG_PUB.ADD;
1533
1534 FND_MSG_PUB.Count_And_Get(
1535 p_encoded => FND_API.G_FALSE,
1536 p_count => x_msg_count,
1537 p_data => x_msg_data);
1538 END submit_batch;
1539
1540
1541 --Body of the Private procedures
1542 PROCEDURE insert_party_details( cp_batch_party_id IN NUMBER,
1543 cp_from_party_id IN NUMBER,
1544 cp_to_party_id IN NUMBER,
1545 p_def_to_entity IN VARCHAR2 DEFAULT 'N') IS
1546 BEGIN
1547
1548 -----Insert Party Site details
1549 insert_party_site_details(
1550 cp_from_party_id,
1551 cp_to_party_id,
1552 cp_batch_party_id,
1553 p_def_to_entity);
1554
1555 -----Insert Party Relations details
1556 insert_party_reln_details(
1557 cp_from_party_id,
1558 cp_to_party_id,
1559 cp_batch_party_id,
1560 p_def_to_entity);
1561
1562 END insert_party_details;
1563
1564
1565 PROCEDURE insert_reln_parties(p_batch_party_id IN NUMBER,
1566 p_batch_id IN NUMBER)IS
1567
1568 CURSOR merged_relns(cp_batch_party_id NUMBER) IS
1569 SELECT merge_from_entity_id, merge_to_entity_id,
1570 HZ_MERGE_UTIL.get_reln_party_id(merge_from_entity_id) from_reln_party_id,
1571 HZ_MERGE_UTIL.get_reln_party_id(merge_to_entity_id) to_reln_party_id
1572 FROM hz_merge_party_details
1573 WHERE batch_party_id = cp_batch_party_id
1574 AND entity_name = 'HZ_PARTY_RELATIONSHIPS'
1575 AND merge_to_entity_id IS NOT NULL
1576 AND merge_from_entity_id IS NOT NULL
1577 AND merge_from_entity_id <> merge_to_entity_id;
1578
1579 l_from_rel_id NUMBER := NULL;
1580 l_to_rel_id NUMBER := NULL;
1581 l_from_reln_party_id NUMBER := NULL;
1582 l_to_reln_party_id NUMBER := NULL;
1583
1584 l_batch_party_id NUMBER := NULL;
1585 l_rel_status varchar2(1);
1586 l_id number;
1587 l_from_rel_status VARCHAR2(1);--Bug6703948
1588 BEGIN
1589
1590 l_batch_party_id := p_batch_party_id;
1591
1592 OPEN merged_relns(l_batch_party_id);
1593 LOOP
1594 FETCH merged_relns INTO l_from_rel_id, l_to_rel_id,
1595 l_from_reln_party_id, l_to_reln_party_id;
1596 EXIT WHEN merged_relns%NOTFOUND;
1597
1598 IF l_to_reln_party_id IS NOT NULL AND
1599 l_from_reln_party_id IS NOT NULL THEN
1600
1601 l_batch_party_id := null;
1602
1603 -- 5194384
1604 select status into l_rel_status
1605 from hz_parties
1606 where party_id = l_to_reln_party_id;
1607
1608 --Bug6703948
1609 select status into l_from_rel_status
1610 from hz_parties
1611 where party_id = l_from_reln_party_id;
1612
1613 IF l_from_rel_status <> 'I' THEN --Bug6703948
1614
1615 if l_rel_status = 'I' -- switch from id and to id
1616 then
1617 l_id := l_from_reln_party_id;
1618 l_from_reln_party_id := l_to_reln_party_id;
1619 l_to_reln_party_id := l_id;
1620 end if;
1621 END IF; --Bug6703948
1622 HZ_MERGE_PARTIES_PKG.Insert_Row(
1623 l_batch_party_id,
1624 p_BATCH_ID,
1625 'PARTY_MERGE',
1626 l_from_reln_party_id,
1627 l_to_reln_party_id,
1628 'DUPLICATE_RELN_PARTY',
1629 'PENDING',
1630 HZ_UTILITY_V2PUB.CREATED_BY,
1631 HZ_UTILITY_V2PUB.CREATION_DATE,
1632 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1633 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1634 HZ_UTILITY_V2PUB.LAST_UPDATED_BY);
1635
1636 --Also insert the Party sites for reln Party
1637 -- if there are any
1638 insert_party_site_details(l_from_reln_party_id,
1639 l_to_reln_party_id,
1640 l_batch_party_id,
1641 'Y');
1642
1643 END IF;
1644 END LOOP;
1645 CLOSE merged_relns;
1646
1647 EXCEPTION
1648 WHEN OTHERS THEN
1649 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1650 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1651 APP_EXCEPTION.RAISE_EXCEPTION;
1652 END insert_reln_parties;
1653
1654
1655 PROCEDURE insert_party_site_details (
1656 p_from_party_id IN NUMBER,
1657 p_to_party_id IN NUMBER,
1658 p_batch_party_id IN NUMBER,
1659 p_reln_parties IN VARCHAR2 DEFAULT 'N'
1660 ) IS
1661
1662 --Cursor for inserting Party sites that are non-DNB
1663 CURSOR c_from_ps_loc(merge_type VARCHAR2) IS
1664 SELECT party_site_id, ps.location_id
1665 FROM HZ_PARTY_SITES ps
1666 WHERE ps.party_id = p_from_party_id
1667 AND (merge_type = 'S' OR ps.actual_content_source <>'DNB')--Bug No.4114254
1668 AND nvl(status, 'A') in ('A','I');
1669
1670
1671
1672
1673 CURSOR c_dup_to_ps(cp_loc_id NUMBER,merge_type VARCHAR2) IS
1674 SELECT party_site_id
1675 FROM HZ_PARTY_SITES ps
1676 WHERE ps.party_id = p_to_party_id
1677 AND ps.location_id = cp_loc_id
1678 AND (merge_type = 'S' OR ps.actual_content_source <>'DNB')--Bug No. 4114254
1679 AND nvl(status, 'A') in ('A','I');
1680
1681
1682
1683
1684 l_ps_id NUMBER;
1685 l_loc_id NUMBER;
1686 l_dup_ps_id NUMBER;
1687 l_sqerr VARCHAR2(2000);
1688 l_mandatory_merge VARCHAR2(1);
1689 l_to_entity_id NUMBER;
1690 l_merge_type VARCHAR2(2000);
1691 l_case VARCHAR2(1);
1692
1693 BEGIN
1694 SELECT dset.merge_type INTO l_merge_type
1695 FROM HZ_DUP_SETS dset,HZ_MERGE_PARTIES mpar
1696 WHERE dset.dup_set_id = mpar.batch_id
1697 AND mpar.batch_party_id = p_batch_party_id;
1698
1699 IF l_merge_type = 'SAME_PARTY_MERGE' THEN
1700 l_case := 'S';
1701 ELSE
1702 l_case := 'M';
1703 END IF;
1704
1705 OPEN c_from_ps_loc(l_case);
1706 LOOP
1707 FETCH c_from_ps_loc INTO l_ps_id, l_loc_id;
1708 EXIT WHEN c_from_ps_loc%NOTFOUND;
1709 IF p_from_party_id <> p_to_party_id THEN
1710 l_mandatory_merge := 'Y';
1711 ELSE
1712 l_mandatory_merge := 'N';
1713 END IF;
1714
1715 OPEN c_dup_to_ps(l_loc_id,l_case);
1716 FETCH c_dup_to_ps INTO l_dup_ps_id;
1717
1718 IF c_dup_to_ps%FOUND THEN
1719 IF (p_reln_parties = 'N') AND (l_mandatory_merge = 'N') THEN
1720 l_to_entity_id := l_ps_id;
1721 ELSE
1722 l_to_entity_id := l_dup_ps_id;
1723 END IF;
1724
1725 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1726 p_batch_party_id,
1727 'HZ_PARTY_SITES',
1728 l_ps_id,
1729 l_to_entity_id,
1730 l_mandatory_merge,
1731 hz_utility_v2pub.created_by,
1732 hz_utility_v2pub.creation_Date,
1733 hz_utility_v2pub.last_update_login,
1734 hz_utility_v2pub.last_update_date,
1735 hz_utility_v2pub.last_updated_by);
1736 ELSE
1737 l_to_entity_id := l_ps_id;
1738
1739 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1740 p_batch_party_id,
1741 'HZ_PARTY_SITES',
1742 l_ps_id,
1743 l_to_entity_id,
1744 'N',
1745 hz_utility_v2pub.created_by,
1746 hz_utility_v2pub.creation_Date,
1747 hz_utility_v2pub.last_update_login,
1748 hz_utility_v2pub.last_update_date,
1749 hz_utility_v2pub.last_updated_by);
1750 END IF;
1751 CLOSE c_dup_to_ps;
1752 END LOOP;
1753 CLOSE c_from_ps_loc;
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1757 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1758 APP_EXCEPTION.RAISE_EXCEPTION;
1759 END insert_party_site_details;
1760
1761
1762 PROCEDURE insert_party_reln_details (
1763 p_from_party_id IN NUMBER,
1764 p_to_party_id IN NUMBER,
1765 p_batch_party_id IN NUMBER,
1766 p_def_mapping IN VARCHAR2 DEFAULT 'N'
1767 ) IS
1768
1769 CURSOR c_from_reln(l_batch_id NUMBER,merge_type VARCHAR2) IS
1770 SELECT relationship_id, subject_id, object_id,
1771 relationship_code, actual_content_source, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
1772 FROM HZ_RELATIONSHIPS r
1773 WHERE (subject_id = p_from_party_id
1774 OR object_id = p_from_party_id)
1775 AND nvl(status, 'A') IN ('A','I')
1776 AND directional_flag = 'F'
1777 AND subject_table_name = 'HZ_PARTIES'
1778 AND object_table_name = 'HZ_PARTIES'
1779 AND (merge_type ='S' OR actual_content_source <> 'DNB')--Bug No. 4114254
1780 AND not exists
1781 ( select 1
1782 from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1783 where a.batch_party_id = b.batch_party_id
1784 and b.merge_from_entity_id = r.relationship_id
1785 and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1786 and a.batch_id = l_batch_id );
1787
1788
1789 CURSOR c_dup_sub_reln(
1790 c_batch_id NUMBER,cp_party_rel_code VARCHAR2, cp_obj_id NUMBER,
1791 cp_subj_id NUMBER, from_start_date date, from_end_date date,merge_type VARCHAR2)
1792 IS
1793 SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
1794 FROM HZ_RELATIONSHIPS r
1795 WHERE subject_id = cp_subj_id
1796 AND object_id = cp_obj_id
1797 AND relationship_code = cp_party_rel_code
1798 --OR exists (select 1 from hz_relationship_types where relationship_type = cp_party_relationship_type
1799 --and forward_code=backward_code))
1800 AND ((start_date between from_start_date and from_end_date)
1801 or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
1802 or(start_date<from_start_date and nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))>from_end_date))
1803 AND nvl(status, 'A') IN ('A','I') --BugNo:2940087
1804 --AND directional_flag = 'F' --BugNo:2940087
1805 AND subject_table_name = 'HZ_PARTIES'
1806 AND object_table_name = 'HZ_PARTIES'
1807 AND (merge_type ='S' OR actual_content_source <> 'DNB') --Bug No. 4114254
1808 AND not exists --4651128
1809 ( select 1
1810 from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1811 where a.batch_party_id = b.batch_party_id
1812 and b.merge_from_entity_id = r.relationship_id
1813 and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1814 and a.batch_id = c_batch_id );
1815
1816 --4651128
1817 CURSOR c_get_from_parties(c_batch_id NUMBER) IS
1818 SELECT dup_party_id
1819 FROM hz_dup_set_parties
1820 WHERE dup_set_id = c_batch_id
1821 AND dup_party_id <> p_from_party_id
1822 AND dup_party_id <> p_to_party_id;
1823
1824
1825 CURSOR check_dup_sub_reln(
1826 c_batch_id NUMBER, cp_party_rel_code VARCHAR2, cp_subj_id NUMBER,
1827 from_start_date date, from_end_date date)
1828 IS
1829 SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
1830 FROM HZ_RELATIONSHIPS r
1831 WHERE
1832 relationship_id in (select distinct b.merge_to_entity_id
1833 from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1834 where a.batch_party_id = b.batch_party_id
1835 and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1836 and a.batch_id = c_batch_id)
1837 AND subject_id = cp_subj_id
1838 AND relationship_code = cp_party_rel_code
1839 AND ((start_date between from_start_date and from_end_date)
1840 or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
1841 or(start_date<from_start_date and nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))>from_end_date))
1842 AND nvl(status, 'A') IN ('A','I')
1843 AND directional_flag = 'F'
1844 AND subject_table_name = 'HZ_PARTIES'
1845 AND object_table_name = 'HZ_PARTIES';
1846
1847 CURSOR check_dup_obj_reln(
1848 c_batch_id NUMBER, cp_party_rel_code VARCHAR2, cp_obj_id NUMBER,
1849 from_start_date date, from_end_date date)
1850 IS
1851 SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
1852 FROM HZ_RELATIONSHIPS r
1853 WHERE
1854 relationship_id in (select distinct b.merge_to_entity_id
1855 from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1856 where a.batch_party_id = b.batch_party_id
1857 and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1858 and a.batch_id = c_batch_id)
1859 AND object_id = cp_obj_id
1860 AND relationship_code = cp_party_rel_code
1861 AND ((start_date between from_start_date and from_end_date)
1862 or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
1863 or(start_date<from_start_date and nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))>from_end_date))
1864 AND nvl(status, 'A') IN ('A','I')
1865 AND directional_flag = 'F'
1866 AND subject_table_name = 'HZ_PARTIES'
1867 AND object_table_name = 'HZ_PARTIES';
1868 --4651128
1869
1870
1871
1872
1873 --bug 4867151 start
1874 CURSOR c_self_reln(rel_id NUMBER, batch_id NUMBER, to_id NUMBER) IS
1875 select 'Y' from hz_relationships where relationship_id=rel_id
1876 and (subject_id IN (SELECT dup_party_id FROM HZ_dup_set_PARTIES WHERE dup_set_id=batch_id))
1877 and (object_id IN (SELECT dup_party_id FROM HZ_dup_set_PARTIES WHERE dup_set_id=batch_id))
1878 AND directional_flag='F';
1879 --bug 4867151 end
1880
1881 /* Commented out for BugNo:2940087 */
1882 /*CURSOR c_dup_ob_reln(cp_party_relationship_type VARCHAR2, cp_subj_id NUMBER)
1883 IS
1884 SELECT relationship_id
1885 FROM HZ_RELATIONSHIPS
1886 WHERE object_id = p_to_party_id
1887 AND subject_id = cp_subj_id
1888 AND relationship_code = cp_party_relationship_type
1889 AND nvl(status, 'A') = 'A'
1890 AND directional_flag = 'F'
1891 AND subject_table_name = 'HZ_PARTIES'
1892 AND object_table_name = 'HZ_PARTIES'
1893 AND actual_content_source <> 'DNB';
1894 */
1895 l_pr_id NUMBER;
1896 l_dup_pr_id NUMBER;
1897 l_dup_start_date HZ_RELATIONSHIPS.start_date%TYPE;
1898 l_dup_end_date HZ_RELATIONSHIPS.end_date%TYPE;
1899
1900 l_subj_id NUMBER;
1901 l_obj_id NUMBER;
1902 l_reltype HZ_RELATIONSHIPS.relationship_code%TYPE;
1903 l_relcode HZ_RELATIONSHIPS.relationship_code%TYPE;
1904 l_contype HZ_RELATIONSHIPS.actual_content_source%TYPE;
1905 l_start_date HZ_RELATIONSHIPS.start_date%TYPE;
1906 l_end_date HZ_RELATIONSHIPS.end_date%TYPE;
1907
1908 l_batch_id NUMBER;
1909 l_batch_party_id NUMBER;
1910 l_mandatory_merge VARCHAR2(1);
1911 l_case VARCHAR2(1);
1912 l_merge_type VARCHAR2(2000);
1913 l_party_id HZ_MERGE_PARTIES.from_party_id%TYPE;
1914 l_temp_flag varchar2(1);--bug 4867151
1915 ---Bug No.5400786
1916 l_temp VARCHAR2(1);
1917 ----Bug No. 5400786
1918
1919 -- bug 5194384
1920 l_rel_status varchar2(1);
1921 l_id number;
1922 l_to_id number;
1923 l_to_rel_status VARCHAR2(1); --6703948
1924 cursor c_get_rel_status(cp_id number) is
1925 select status
1926 from hz_relationships
1927 where relationship_id = cp_id
1928 and rownum = 1;
1929
1930 cursor c_check_inactive_to_id(cp_id number) is
1931 select merge_to_entity_id
1932 from hz_merge_party_details
1933 where merge_from_entity_id = merge_to_entity_id
1934 and merge_from_entity_id = cp_id;
1935
1936
1937 BEGIN
1938 l_temp := 'N';
1939 SELECT dset.merge_type, mpar.batch_id INTO l_merge_type, l_batch_id
1940 FROM HZ_DUP_SETS dset,HZ_MERGE_PARTIES mpar
1941 WHERE dset.dup_set_id = mpar.batch_id
1942 AND mpar.batch_party_id = p_batch_party_id;
1943
1944 IF l_merge_type = 'SAME_PARTY_MERGE' THEN
1945 l_case := 'S';
1946 ELSE
1947 l_case := 'M';
1948 END IF;
1949 --get relationship in which from_party_id is either subject or object
1950 OPEN c_from_reln(l_batch_id,l_case);
1951 LOOP
1952 l_dup_pr_id := -1;
1953 l_temp := 'N'; --6082014
1954 FETCH c_from_reln INTO l_pr_id, l_subj_id, l_obj_id, l_relcode,
1955 l_contype, l_start_date, l_end_date;
1956 EXIT WHEN c_from_reln%NOTFOUND;
1957
1958 -- IF l_contype <> 'DNB' THEN--Bug No.4114254
1959
1960 --if the from party is the subject in reln.
1961 IF l_subj_id=p_from_party_id THEN
1962
1963 OPEN c_dup_sub_reln(l_batch_id,l_relcode, l_obj_id, p_to_party_id,l_start_date,l_end_date,l_case);
1964 FETCH c_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
1965 IF c_dup_sub_reln%NOTFOUND THEN
1966 l_dup_pr_id := -1;
1967 END IF;
1968 CLOSE c_dup_sub_reln;
1969 -- start 4651128
1970 IF l_dup_pr_id = -1 THEN
1971 OPEN check_dup_obj_reln(l_batch_id, l_relcode,l_obj_id,l_start_date,l_end_date);
1972 FETCH check_dup_obj_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
1973 IF check_dup_obj_reln%NOTFOUND THEN
1974 l_dup_pr_id := -1;
1975 END IF;
1976 CLOSE check_dup_obj_reln;
1977 END IF;
1978
1979 IF l_dup_pr_id = -1 THEN
1980 OPEN c_get_from_parties(l_batch_id); --4651128
1981 LOOP
1982 FETCH c_get_from_parties INTO l_party_id;
1983 EXIT WHEN c_get_from_parties%NOTFOUND;
1984 OPEN c_dup_sub_reln(l_batch_id,l_relcode, l_obj_id, l_party_id,l_start_date,l_end_date,l_case);
1985 FETCH c_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
1986 IF c_dup_sub_reln%FOUND THEN
1987 CLOSE c_dup_sub_reln;
1988 EXIT;
1989 ELSE
1990 l_dup_pr_id := -1;
1991 END IF;
1992 CLOSE c_dup_sub_reln;
1993 END LOOP;
1994 CLOSE c_get_from_parties;
1995 END IF;
1996 --end 4651128
1997 ELSIF l_obj_id=p_from_party_id THEN
1998
1999 OPEN c_dup_sub_reln(l_batch_id,l_relcode,p_to_party_id,l_subj_id,l_start_date,l_end_date,l_case);
2000 FETCH c_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
2001 IF c_dup_sub_reln%NOTFOUND THEN
2002 l_dup_pr_id := -1;
2003 END IF;
2004 CLOSE c_dup_sub_reln;
2005
2006 -- start 4651128
2007
2008 IF l_dup_pr_id = -1 THEN
2009 OPEN check_dup_sub_reln(l_batch_id,l_relcode,l_subj_id,l_start_date,l_end_date);
2010 FETCH check_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
2011 IF check_dup_sub_reln%NOTFOUND THEN
2012 l_dup_pr_id := -1;
2013 END IF;
2014 CLOSE check_dup_sub_reln;
2015 END IF;
2016
2017
2018 IF l_dup_pr_id = -1 THEN
2019 OPEN c_get_from_parties(l_batch_id); --4651128
2020 LOOP
2021 FETCH c_get_from_parties INTO l_party_id;
2022 EXIT WHEN c_get_from_parties%NOTFOUND;
2023 OPEN c_dup_sub_reln(l_batch_id,l_relcode, l_party_id, l_subj_id,l_start_date,l_end_date,l_case);
2024 FETCH c_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
2025 IF c_dup_sub_reln%FOUND THEN
2026 CLOSE c_dup_sub_reln;
2027 EXIT;
2028 ELSE
2029 --Transfer
2030 l_dup_pr_id := -1;
2031 END IF;
2032 CLOSE c_dup_sub_reln;
2033 END LOOP;
2034 CLOSE c_get_from_parties;
2035 END IF;
2036 --end 4651128
2037 END IF;
2038 -- END IF;
2039 --bug 4867151 start
2040
2041 l_temp_flag := 'N';
2042
2043 OPEN c_self_reln(l_pr_id, l_batch_id, p_to_party_id);
2044 FETCH c_self_reln INTO l_temp_flag;
2045 CLOSE c_self_reln;
2046 --bug 4867151 end
2047
2048 -- bug 5194384
2049 open c_get_rel_status(l_dup_pr_id);
2050 fetch c_get_rel_status into l_rel_status;
2051 close c_get_rel_status;
2052
2053 --bug6703948
2054 open c_get_rel_status(l_pr_id);
2055 fetch c_get_rel_status into l_to_rel_status;
2056 close c_get_rel_status;
2057
2058 IF l_to_rel_status <> 'I' THEN --bug6703948
2059 if l_rel_status = 'I' -- switch from id and to id
2060 then
2061 l_id := l_pr_id;
2062 l_pr_id := l_dup_pr_id;
2063 l_dup_pr_id := l_id;
2064 end if;
2065 END IF;--bug6703948
2066
2067 IF l_temp_flag<>'Y' THEN --bug 4867151
2068
2069 IF l_dup_pr_id <> -1 THEN
2070
2071 IF p_from_party_id <> p_to_party_id AND l_pr_id <> l_dup_pr_id THEN
2072 l_mandatory_merge := 'Y';
2073 ELSE
2074 l_mandatory_merge := 'N';
2075 END IF;
2076
2077 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
2078 p_batch_party_id,
2079 'HZ_PARTY_RELATIONSHIPS',
2080 l_pr_id,
2081 l_dup_pr_id,
2082 l_mandatory_merge,
2083 hz_utility_v2pub.created_by,
2084 hz_utility_v2pub.creation_Date,
2085 hz_utility_v2pub.last_update_login,
2086 hz_utility_v2pub.last_update_date,
2087 hz_utility_v2pub.last_updated_by);
2088
2089 ELSE
2090
2091 --5400786
2092 l_temp := 'Y';
2093 ---5400786
2094 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
2095 p_batch_party_id,
2096 'HZ_PARTY_RELATIONSHIPS',
2097 l_pr_id,
2098 l_pr_id,
2099 'N',
2100 hz_utility_v2pub.created_by,
2101 hz_utility_v2pub.creation_Date,
2102 hz_utility_v2pub.last_update_login,
2103 hz_utility_v2pub.last_update_date,
2104 hz_utility_v2pub.last_updated_by);
2105
2106 END IF;
2107 END IF;--l_temp_flag-- bug 4867151
2108 -- bug 5194384
2109 open c_check_inactive_to_id(l_pr_id);
2110 fetch c_check_inactive_to_id into l_to_id;
2111 close c_check_inactive_to_id;
2112 --5400786
2113 if l_to_id = l_pr_id AND l_temp <> 'Y' -- inactive id
2114 --5400786
2115 then
2116 update HZ_MERGE_PARTY_DETAILS
2117 set
2118 merge_from_entity_id = l_dup_pr_id,
2119 merge_to_entity_id = l_dup_pr_id
2120 where merge_to_entity_id = l_pr_id
2121 and merge_from_entity_id = merge_to_entity_id; --bug 608201
2122 end if;
2123 END LOOP;
2124 CLOSE c_from_reln;
2125 EXCEPTION
2126 WHEN OTHERS THEN
2127 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2128 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2129 APP_EXCEPTION.RAISE_EXCEPTION;
2130 END insert_party_reln_details;
2131
2132 /*=======================================================================+
2133 | DESCRIPTION |
2134 | Suggested default will be run when creating new batch |
2135 | For same party merge, only mapped records exist in merge party |
2136 | For multiple parties merge, all records exist. To identify which |
2137 | record is merged, merge_from_entity_id <> merge_to_entity_id |
2138 *=======================================================================*/
2139
2140 -- create records in dup batch, dup set and dup set parties
2141 PROCEDURE suggested_defaults (
2142 p_batch_id IN NUMBER
2143 ,x_return_status OUT NOCOPY VARCHAR2
2144 ,x_msg_count OUT NOCOPY NUMBER
2145 ,x_msg_data OUT NOCOPY VARCHAR2
2146 ) IS
2147
2148 l_merge_type VARCHAR2(30);
2149 l_addr_match_rule NUMBER := 0;
2150 l_reln_match_rule NUMBER := 0;
2151 l_default_addr_rule NUMBER := 0;
2152 l_default_relat_rule NUMBER := 0;
2153
2154 cursor get_dup_sets_info(l_dup_set_id NUMBER) is
2155 select merge_type
2156 from HZ_DUP_SETS
2157 where dup_set_id = l_dup_set_id;
2158
2159 BEGIN
2160
2161 savepoint suggested_defaults;
2162
2163 fnd_msg_pub.initialize;
2164 --Initialize API return status to success.
2165 x_return_status := FND_API.G_RET_STS_SUCCESS;
2166
2167 IF(p_batch_id IS NULL OR
2168 p_batch_id = FND_API.G_MISS_NUM) THEN
2169 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2170 FND_MESSAGE.SET_TOKEN('COLUMN' ,'DUP_SET_ID');
2171 FND_MSG_PUB.ADD;
2172 RAISE FND_API.G_EXC_ERROR;
2173 END IF;
2174
2175 open get_dup_sets_info(p_batch_id);
2176 fetch get_dup_sets_info into l_merge_type;
2177 close get_dup_sets_info;
2178
2179 -- clean up temporary table for suggested defaults
2180 DELETE FROM HZ_MERGE_PARTYDTLS_SUGG
2181 WHERE batch_party_id in
2182 ( SELECT batch_party_id
2183 FROM HZ_MERGE_PARTIES_SUGG
2184 WHERE batch_id = p_batch_id );
2185
2186 DELETE FROM HZ_MERGE_PARTIES_SUGG
2187 WHERE batch_id = p_batch_id;
2188
2189 INSERT INTO HZ_MERGE_PARTIES_SUGG
2190 (
2191 batch_party_id
2192 ,batch_id
2193 ,merge_type
2194 ,from_party_id
2195 ,to_party_id
2196 ,merge_reason_code
2197 ,merge_status
2198 ,created_by
2199 ,creation_date
2200 ,last_update_login
2201 ,last_update_date
2202 ,last_updated_by
2203 )
2204 SELECT
2205 batch_party_id
2206 ,batch_id
2207 ,merge_type
2208 ,from_party_id
2209 ,to_party_id
2210 ,merge_reason_code
2211 ,merge_status
2212 ,created_by
2213 ,creation_date
2214 ,last_update_login
2215 ,last_update_date
2216 ,last_updated_by
2217 FROM HZ_MERGE_PARTIES
2218 WHERE batch_id = p_batch_id;
2219
2220 INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
2221 (
2222 batch_party_id
2223 ,entity_name
2224 ,merge_from_entity_id
2225 ,merge_to_entity_id
2226 ,mandatory_merge
2227 ,created_by
2228 ,creation_date
2229 ,last_update_login
2230 ,last_update_date
2231 ,last_updated_by
2232 ,object_version_number
2233 )
2234 SELECT
2235 batch_party_id
2236 ,entity_name
2237 ,merge_from_entity_id
2238 ,merge_to_entity_id
2239 ,mandatory_merge
2240 ,created_by
2241 ,creation_date
2242 ,last_update_login
2243 ,last_update_date
2244 ,last_updated_by
2245 ,object_version_number
2246 FROM HZ_MERGE_PARTY_DETAILS
2247 WHERE batch_party_id IN
2248 ( SELECT batch_party_id
2249 FROM HZ_MERGE_PARTIES_SUGG
2250 WHERE batch_id = p_batch_id );
2251
2252 -- get match_rule_id from profile
2253
2254 select min(match_rule_id) into l_default_addr_rule
2255 from HZ_MATCH_RULES_VL
2256 where rule_name = 'DL ADDRESS DEFAULT';
2257
2258 select min(match_rule_id) into l_default_relat_rule
2259 from HZ_MATCH_RULES_VL
2260 where rule_name = 'DL RELATIONSHIP DEFAULT';
2261
2262 l_addr_match_rule := nvl(fnd_profile.value('HZ_SUGG_ADDR_MATCH_RULE'),l_default_addr_rule);
2263 l_reln_match_rule := nvl(fnd_profile.value('HZ_SUGG_RELN_MATCH_RULE'),l_default_relat_rule);
2264
2265 if((l_addr_match_rule = 0) OR (l_reln_match_rule = 0)) then
2266 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_MATCH_RULE');
2267 FND_MSG_PUB.ADD;
2268 RAISE FND_API.G_EXC_ERROR;
2269 end if;
2270
2271 -- call suggested defaults for address and relationship
2272 suggested_party_sites(p_batch_id, l_merge_type, l_addr_match_rule);
2273 suggested_party_reln(p_batch_id, l_merge_type, l_reln_match_rule);
2274
2275 EXCEPTION
2276
2277 WHEN FND_API.G_EXC_ERROR THEN
2278 ROLLBACK TO suggested_defaults;
2279 x_return_status := FND_API.G_RET_STS_ERROR;
2280 FND_MSG_PUB.Count_And_Get(
2281 p_encoded => FND_API.G_FALSE,
2282 p_count => x_msg_count,
2283 p_data => x_msg_data);
2284
2285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2286 ROLLBACK TO suggested_defaults;
2287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2288 FND_MSG_PUB.Count_And_Get(
2289 p_encoded => FND_API.G_FALSE,
2290 p_count => x_msg_count,
2291 p_data => x_msg_data);
2292
2293 WHEN OTHERS THEN
2294 ROLLBACK TO suggested_defaults;
2295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2296 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2297 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2298 FND_MSG_PUB.ADD;
2299 FND_MSG_PUB.Count_And_Get(
2300 p_encoded => FND_API.G_FALSE,
2301 p_count => x_msg_count,
2302 p_data => x_msg_data);
2303
2304 END suggested_defaults;
2305
2306 PROCEDURE suggested_party_sites (
2307 p_batch_id IN NUMBER
2308 ,p_merge_type IN VARCHAR2
2309 ,p_rule_id IN NUMBER
2310 ) IS
2311
2312 -- this is for merge multiple parties, get all sites which are not mandatory merge
2313 -- should not add condition merge_type = 'PARTY_MERGE'. For multiple parties merge,
2314 -- merge_type may be 'SAME_PARTY_MERGE'. It is because for master party sites in
2315 -- merge party details table, the merge_type is 'SAME_PARTY_MERGE'
2316 cursor not_mandatory_sites_mp is
2317 select merge_from_entity_id
2318 from HZ_MERGE_PARTYDTLS_SUGG mpd
2319 , HZ_MERGE_PARTIES_SUGG mp
2320 , hz_party_sites ps
2321 where mpd.batch_party_id = mp.batch_party_id
2322 and mp.batch_id = p_batch_id
2323 and ps.party_id = mp.from_party_id
2324 and ps.party_site_id = mpd.merge_from_entity_id
2325 and mpd.entity_name = 'HZ_PARTY_SITES'
2326 and mpd.merge_to_entity_id = mpd.merge_from_entity_id
2327 order by ps.status,mp.merge_type desc; -- make sure to process active sites first;
2328
2329 -- this is for cleanse single party, find out the batch_party_id
2330 cursor get_merge_party_id is
2331 select batch_party_id, from_party_id
2332 from HZ_MERGE_PARTIES_SUGG
2333 where batch_id = p_batch_id
2334 and merge_type = 'SAME_PARTY_MERGE';
2335
2336 -- this is for cleanse single party, get all sites which are not mandatory merge
2337 cursor not_mandatory_sites_sp(l_master_party NUMBER) is
2338 select party_site_id
2339 from HZ_PARTY_SITES ps
2340 where party_id = l_master_party
2341 and status in ('A','I')
2342 and not exists
2343 ( select 1
2344 from HZ_MERGE_PARTIES_SUGG mp
2345 , HZ_MERGE_PARTYDTLS_SUGG mpd
2346 where mp.batch_id = p_batch_id
2347 and mp.batch_party_id = mpd.batch_party_id
2348 and mpd.entity_name = 'HZ_PARTY_SITES'
2349 and mpd.merge_from_entity_id = ps.party_site_id)
2350 order by ps.status,decode(ps.actual_content_source,'DNB',1,2); -- make sure to process active sites first;
2351
2352 -- for merge multiple parties
2353 -- check if the site is already mapped to some other site
2354 cursor check_mapped_sites_mp(l_from_site_id NUMBER) is
2355 select 'X'
2356 from HZ_MERGE_PARTIES_SUGG mp
2357 , HZ_MERGE_PARTYDTLS_SUGG mpd
2358 where mpd.merge_from_entity_id = l_from_site_id
2359 and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
2360 and mpd.batch_party_id = mp.batch_party_id
2361 and mp.batch_id = p_batch_id
2362 and mpd.entity_name = 'HZ_PARTY_SITES';
2363
2364 -- this is for cleanse single party
2365 -- since l_master_party is passed for the DQM search, we can assume that all matched party sites
2366 -- will be from the same l_master_party
2367 -- need to check if the matched addr is already mapped by other sites
2368 -- therefore, check if the site_id = merge_to_entity_id
2369 cursor get_suggested_addr(l_search_ctx_id NUMBER, l_master_site NUMBER) is
2370 select party_site_id
2371 from HZ_MATCHED_PARTY_SITES_GT mps
2372 where mps.search_context_id = l_search_ctx_id
2373 and mps.party_site_id <> l_master_site
2374 and not exists
2375 ( select 1
2376 from HZ_MERGE_PARTIES_SUGG mp
2377 , HZ_MERGE_PARTYDTLS_SUGG mpd
2378 where mpd.merge_to_entity_id = mps.party_site_id
2379 and mpd.batch_party_id = mp.batch_party_id
2380 and mp.batch_id = p_batch_id
2381 and mpd.entity_name = 'HZ_PARTY_SITES');
2382
2383 -- for cleanse single party
2384 -- need to check if sites is already mapped to other site
2385 -- only mapped sites appear in HZ_MERGE_PARTYDTLS_SUGG, therefore if
2386 -- merge_from_entity_id = pass in site_id, then this site is mapped
2387 cursor check_mapped_sites_sp(l_from_site_id NUMBER) is
2388 select 'X'
2389 from HZ_MERGE_PARTIES_SUGG mp
2390 , HZ_MERGE_PARTYDTLS_SUGG mpd
2391 where mpd.merge_from_entity_id = l_from_site_id
2392 and mpd.batch_party_id = mp.batch_party_id
2393 and mp.batch_id = p_batch_id
2394 and mpd.entity_name = 'HZ_PARTY_SITES';
2395
2396 CURSOR c_get_matched_ps(l_search_context_id NUMBER) IS
2397 SELECT party_site_id
2398 FROM HZ_MATCHED_PARTY_SITES_GT
2399 WHERE search_context_id = l_search_context_id;
2400
2401 CURSOR c_cust_site_check(p_from_entity_id NUMBER) IS
2402 select 1 from hz_cust_acct_sites_all where party_site_id=p_from_entity_id;
2403
2404 --4114254
2405 CURSOR c_get_orig_system(p_party_site_id NUMBER) IS
2406 select o.orig_system
2407 from hz_party_sites ps, hz_orig_systems_b o
2408 where ps.party_site_id = p_party_site_id
2409 and o.orig_system = ps.actual_content_source
2410 and o.orig_system_type = 'PURCHASED';
2411
2412
2413
2414 l_dummy VARCHAR2(1);
2415 l_master_party NUMBER;
2416 l_batch_party_id NUMBER;
2417 l_master_site NUMBER;
2418 l_merge_from_site NUMBER;
2419 l_search_ctx_id NUMBER;
2420 l_num_matches NUMBER;
2421 l_return_status VARCHAR2(30);
2422 l_msg_count NUMBER;
2423 l_msg_data VARCHAR2(2000);
2424 l_loc NUMBER;
2425 l_org NUMBER;
2426 l_temp_from_site NUMBER;
2427 l_count NUMBER;
2428
2429 l_from_country HZ_LOCATIONS.COUNTRY%TYPE;
2430 l_to_country HZ_LOCATIONS.COUNTRY%TYPE;
2431 l_from_city HZ_LOCATIONS.CITY%TYPE;
2432 l_to_city HZ_LOCATIONS.CITY%TYPE;
2433 l_from_state HZ_LOCATIONS.STATE%TYPE;
2434 l_to_state HZ_LOCATIONS.CITY%TYPE;
2435 l_from_county HZ_LOCATIONS.COUNTY%TYPE;
2436 l_to_county HZ_LOCATIONS.COUNTY%TYPE;
2437 l_from_province HZ_LOCATIONS.PROVINCE%TYPE;
2438 l_to_province HZ_LOCATIONS.PROVINCE%TYPE;
2439 l_from_postal_code HZ_LOCATIONS.postal_code%TYPE;
2440 l_to_postal_code HZ_LOCATIONS.postal_code%TYPE;
2441 l_structure_id NUMBER;
2442 l_qualifier VARCHAR2(30);
2443 l_err_flg VARCHAR2(1) := 'N';
2444 l_cust_sites NUMBER;
2445
2446 --4569674
2447 l_merge_yn VARCHAR2(2);
2448 l_from_location_id NUMBER;
2449 l_to_location_id NUMBER;
2450
2451 --4114254
2452 l_from_orig_system HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM%TYPE;
2453 l_to_orig_system HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM%TYPE;
2454 l_flag_merge varchar2(1);
2455
2456 BEGIN
2457
2458 IF(p_merge_type = 'PARTY_MERGE') THEN
2459 -- get all sites that are not set to mandatory merge at the beginning
2460 OPEN not_mandatory_sites_mp;
2461 LOOP
2462 FETCH not_mandatory_sites_mp into l_master_site;
2463 EXIT WHEN not_mandatory_sites_mp%NOTFOUND;
2464 -- call DQM search to find duplicate addr for l_master_site
2465 -- restrict the search on those party sites among those merge parties
2466
2467 -- if site is not merged, create the mapping
2468 OPEN check_mapped_sites_mp(l_master_site);
2469 FETCH check_mapped_sites_mp into l_dummy;
2470 IF (check_mapped_sites_mp%NOTFOUND) THEN
2471
2472 HZ_PARTY_SEARCH.find_duplicate_party_sites
2473 (
2474 p_init_msg_list => FND_API.G_TRUE
2475 ,p_rule_id => p_rule_id
2476 ,p_party_site_id => l_master_site
2477 ,p_party_id => NULL
2478 ,p_restrict_sql => ' PARTY_SITE_ID IN (SELECT /*+ SELECTIVE_PS */ MERGE_FROM_ENTITY_ID' ||
2479 ' FROM HZ_MERGE_PARTYDTLS_SUGG mpd, HZ_MERGE_PARTIES_SUGG mp' ||
2480 ' WHERE mpd.MERGE_TO_ENTITY_ID = mpd.MERGE_FROM_ENTITY_ID' ||
2481 ' AND mpd.BATCH_PARTY_ID = mp.BATCH_PARTY_ID' ||
2482 ' AND mpd.ENTITY_NAME = ''HZ_PARTY_SITES''' ||
2483 ' AND mp.BATCH_ID = '|| p_batch_id ||')'
2484 ,p_match_type => 'OR'
2485 ,x_search_ctx_id => l_search_ctx_id
2486 ,x_num_matches => l_num_matches
2487 ,x_return_status => l_return_status
2488 ,x_msg_count => l_msg_count
2489 ,x_msg_data => l_msg_data
2490 );
2491
2492 -- check loc assignment if number of match is greater than 0
2493 IF (l_num_matches > 0) THEN
2494 OPEN c_get_matched_ps(l_search_ctx_id);
2495 LOOP
2496 FETCH c_get_matched_ps INTO l_temp_from_site;
2497 EXIT WHEN c_get_matched_ps%NOTFOUND;
2498 l_merge_yn := null;
2499
2500 --check if the party site has account sites
2501 OPEN c_cust_site_check(l_temp_from_site);
2502 Fetch c_cust_site_check into l_cust_sites;
2503 IF c_cust_site_check %NOTFOUND THEN
2504 null;
2505 ELSE
2506 --4569674
2507 SELECT location_id into l_from_location_id
2508 FROM hz_party_sites
2509 WHERE party_site_id = l_temp_from_site;
2510
2511 SELECT location_id into l_to_location_id
2512 FROM hz_party_sites
2513 WHERE party_site_id = l_master_site;
2514
2515
2516 ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
2517 l_to_location_id,
2518 FND_API.G_FALSE,
2519 l_merge_yn,
2520 l_return_status,
2521 l_msg_count,
2522 l_msg_data);
2523 IF l_merge_yn = 'N' THEN
2524 DELETE FROM HZ_MATCHED_PARTY_SITES_GT
2525 WHERE search_context_id = l_search_ctx_id
2526 AND party_site_id = l_temp_from_site;
2527 l_num_matches := l_num_matches - 1;
2528 END IF;
2529
2530
2531 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2532 RAISE FND_API.G_EXC_ERROR;
2533 END IF;
2534
2535 --4569674
2536 END IF;
2537 CLOSE c_cust_site_check;
2538
2539 --4114254
2540 IF NVL(l_merge_yn,'Y') <> 'N' THEN
2541
2542 OPEN c_get_orig_system(l_temp_from_site);
2543 FETCH c_get_orig_system INTO l_from_orig_system;
2544
2545 IF c_get_orig_system%FOUND THEN
2546 CLOSE c_get_orig_system;
2547 OPEN c_get_orig_system(l_master_site);
2548 FETCH c_get_orig_system INTO l_to_orig_system;
2549
2550 IF (c_get_orig_system%FOUND AND l_to_orig_system <> l_from_orig_system) OR
2551 (c_get_orig_system%NOTFOUND) THEN
2552
2553 DELETE FROM HZ_MATCHED_PARTY_SITES_GT
2554 WHERE search_context_id = l_search_ctx_id
2555 AND party_site_id = l_temp_from_site;
2556
2557 l_num_matches := l_num_matches - 1;
2558 END IF;
2559 CLOSE c_get_orig_system;
2560 ELSE
2561 CLOSE c_get_orig_system;
2562 END IF;
2563 END IF;
2564 --4114254
2565 END LOOP;
2566 CLOSE c_get_matched_ps;
2567 END IF;
2568
2569 -- if found any match, update the merge party details temp table
2570 IF (l_num_matches > 0) THEN
2571
2572 UPDATE HZ_MERGE_PARTYDTLS_SUGG
2573 SET merge_to_entity_id = l_master_site
2574 , mandatory_merge = 'N'
2575 , last_update_date = hz_utility_v2pub.last_update_date
2576 , last_updated_by = hz_utility_v2pub.last_updated_by
2577 , last_update_login = hz_utility_v2pub.last_update_login
2578 WHERE batch_party_id IN
2579 ( SELECT batch_party_id
2580 FROM HZ_MERGE_PARTIES_SUGG
2581 WHERE batch_id = p_batch_id )
2582 AND merge_from_entity_id IN
2583 ( SELECT party_site_id
2584 FROM HZ_MATCHED_PARTY_SITES_GT matchps
2585 , HZ_MERGE_PARTYDTLS_SUGG mpd
2586 , HZ_MERGE_PARTIES_SUGG mps
2587 WHERE matchps.search_context_id = l_search_ctx_id
2588 AND matchps.party_site_id = mpd.merge_from_entity_id
2589 AND mpd.entity_name = 'HZ_PARTY_SITES'
2590 AND mpd.merge_to_entity_id = mpd.merge_from_entity_id
2591 AND mpd.batch_party_id = mps.batch_party_id
2592 AND mps.batch_id = p_batch_id
2593 AND NOT EXISTS
2594 ( SELECT 1
2595 FROM HZ_MERGE_PARTYDTLS_SUGG mpdi
2596 WHERE mpdi.batch_party_id = mpd.batch_party_id
2597 AND mpdi.merge_to_entity_id = matchps.party_site_id
2598 AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
2599 )
2600 );
2601 END IF; -- l_num_matches > 0
2602 END IF; -- check_mapped_sites_mp
2603 CLOSE check_mapped_sites_mp;
2604 END LOOP; -- not_mandatory_sites_mp
2605 CLOSE not_mandatory_sites_mp;
2606 ELSIF(p_merge_type = 'SAME_PARTY_MERGE') THEN
2607 -- get batch_party_id and master party_id of the merge request, only one record for same party merge
2608 OPEN get_merge_party_id;
2609 FETCH get_merge_party_id INTO l_batch_party_id, l_master_party;
2610 CLOSE get_merge_party_id;
2611 -- find out all not mandatory merge sites
2612 OPEN not_mandatory_sites_sp(l_master_party);
2613 LOOP
2614 FETCH not_mandatory_sites_sp into l_master_site;
2615 EXIT WHEN not_mandatory_sites_sp%NOTFOUND;
2616
2617 HZ_PARTY_SEARCH.find_duplicate_party_sites
2618 (
2619 p_init_msg_list => FND_API.G_TRUE
2620 ,p_rule_id => p_rule_id
2621 ,p_party_site_id => l_master_site
2622 ,p_party_id => l_master_party
2623 ,p_restrict_sql => NULL
2624 ,p_match_type => 'OR'
2625 ,x_search_ctx_id => l_search_ctx_id
2626 ,x_num_matches => l_num_matches
2627 ,x_return_status => l_return_status
2628 ,x_msg_count => l_msg_count
2629 ,x_msg_data => l_msg_data
2630 );
2631
2632 IF (l_num_matches > 0) THEN
2633 -- find out all matched sites from HZ_MATCHED_PARTY_SITES_GT
2634 OPEN get_suggested_addr(l_search_ctx_id, l_master_site);
2635 LOOP
2636 FETCH get_suggested_addr into l_merge_from_site;
2637 EXIT WHEN get_suggested_addr%NOTFOUND;
2638
2639 l_merge_yn := null;
2640
2641 -- if site is not merged, create the mapping
2642 OPEN check_mapped_sites_sp(l_merge_from_site);
2643 FETCH check_mapped_sites_sp into l_dummy;
2644 IF (check_mapped_sites_sp%NOTFOUND) THEN
2645
2646 --4569674
2647 SELECT location_id into l_from_location_id
2648 FROM hz_party_sites
2649 WHERE party_site_id = l_merge_from_site;
2650
2651 SELECT location_id into l_to_location_id
2652 FROM hz_party_sites
2653 WHERE party_site_id = l_master_site;
2654
2655 ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
2656 l_to_location_id,
2657 FND_API.G_FALSE,
2658 l_merge_yn,
2659 l_return_status,
2660 l_msg_count,
2661 l_msg_data);
2662
2663
2664 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2665 RAISE FND_API.G_EXC_ERROR;
2666 END IF;
2667
2668 IF NVL(l_merge_yn,'Y') = 'Y' THEN
2669
2670 l_flag_merge := 'Y';
2671 OPEN c_get_orig_system(l_merge_from_site);
2672 FETCH c_get_orig_system INTO l_from_orig_system;
2673
2674 IF c_get_orig_system%FOUND THEN
2675 CLOSE c_get_orig_system;
2676 OPEN c_get_orig_system(l_master_site);
2677 FETCH c_get_orig_system INTO l_to_orig_system;
2678
2679 IF (c_get_orig_system%FOUND AND
2680 l_to_orig_system <> l_from_orig_system
2681 ) OR
2682 (c_get_orig_system%NOTFOUND)
2683 THEN
2684 l_flag_merge :='N';
2685 END IF;
2686 CLOSE c_get_orig_system;
2687 ELSE /* c_get_orig_system%NOTFOUND */
2688 CLOSE c_get_orig_system;
2689 END IF; /* c_get_orig_system%FOUND */
2690
2691 IF l_flag_merge = 'Y' THEN
2692 INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
2693 (
2694 batch_party_id
2695 ,entity_name
2696 ,merge_from_entity_id
2697 ,merge_to_entity_id
2698 ,mandatory_merge
2699 ,created_by
2700 ,creation_date
2701 ,last_update_login
2702 ,last_update_date
2703 ,last_updated_by
2704 ,object_version_number
2705 )
2706 VALUES
2707 (
2708 l_batch_party_id
2709 ,'HZ_PARTY_SITES'
2710 ,l_merge_from_site
2711 ,l_master_site
2712 ,'N'
2713 ,hz_utility_v2pub.created_by
2714 ,hz_utility_v2pub.creation_date
2715 ,hz_utility_v2pub.last_update_login
2716 ,hz_utility_v2pub.last_update_date
2717 ,hz_utility_v2pub.last_updated_by
2718 ,1
2719 );
2720 END IF;--l_flag_merge
2721 END IF; --l_merge_yn
2722 --4569674
2723 END IF; -- check_mapped_sites
2724 CLOSE check_mapped_sites_sp;
2725 END LOOP;
2726 CLOSE get_suggested_addr;
2727 END IF; -- l_num_matches > 0
2728 END LOOP; -- not_mandatory_sites_sp
2729 CLOSE not_mandatory_sites_sp;
2730 END IF;
2731
2732 EXCEPTION
2733
2734 WHEN OTHERS THEN
2735 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2736 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2737 APP_EXCEPTION.RAISE_EXCEPTION;
2738
2739 END suggested_party_sites;
2740
2741 PROCEDURE suggested_party_reln (
2742 p_batch_id IN NUMBER
2743 ,p_merge_type IN VARCHAR2
2744 ,p_rule_id IN NUMBER
2745 ) IS
2746
2747 -- this is for merge multiple parties, get all relationships which are not mapped
2748 -- as mandatory merge
2749 -- all merge parties are the subject of the relationship
2750 -- e.g. Oracle Contact Peter
2751 -- Oracle Corp Contact Peter
2752 -- Oracle Inc Contact Peter
2753 -- Assume Oracle Corp and Oracle Inc ---merge---> Oracle
2754 -- Then all relationships retrieved will be based on subject_id = Oracle,
2755 -- Oracle Corp and Oracle Inc
2756 cursor not_mandatory_reln_mp is
2757 select rel.relationship_id, rel.relationship_type, rel.relationship_code, rel.object_id, rel.subject_id, rel.subject_type, rel.object_type
2758 from HZ_MERGE_PARTYDTLS_SUGG mpd
2759 , HZ_MERGE_PARTIES_SUGG mp
2760 , HZ_RELATIONSHIPS rel
2761 where mpd.batch_party_id = mp.batch_party_id
2762 and mp.batch_id = p_batch_id
2763 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
2764 and mpd.merge_from_entity_id = mpd.merge_to_entity_id
2765 and mpd.merge_from_entity_id = rel.relationship_id
2766 and rel.subject_id in (
2767 select dup_party_id
2768 from HZ_DUP_SET_PARTIES
2769 where dup_set_id = p_batch_id
2770 and nvl(merge_flag,'Y') <> 'N'
2771 ) ;
2772
2773 -- this is for cleanse single party, pass in subject master which is the party to be cleansed
2774 cursor not_mandatory_reln_sp(l_master_party_id NUMBER) is
2775 select rel.relationship_id, rel.relationship_type, rel.relationship_code, rel.object_id, rel.subject_type, rel.object_type
2776 from HZ_RELATIONSHIPS rel
2777 where subject_id = l_master_party_id
2778 and not exists
2779 ( select 1
2780 from HZ_MERGE_PARTIES_SUGG mp
2781 , HZ_MERGE_PARTYDTLS_SUGG mpd
2782 , HZ_RELATIONSHIPS rel2
2783 where mp.batch_id = p_batch_id
2784 and mp.batch_party_id = mpd.batch_party_id
2785 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
2786 and mpd.merge_from_entity_id = rel2.relationship_id
2787 and rel2.relationship_id = rel.relationship_id
2788 and rel2.object_id = rel.object_id
2789 and rel2.subject_id = rel.subject_id
2790 and rel2.subject_type = rel.subject_type
2791 and rel2.object_Type = rel.object_type
2792 and rel2.relationship_code = rel.relationship_code ) order by decode(rel.actual_content_source,'DNB',1,2);
2793
2794 -- this is for cleanse single party, since relationship may have party record and each party record will have
2795 -- corresponding party sites to merge. Those batch_party_id will be different. Therefore, we need to filter
2796 -- by merge_type = 'SAME_PARTY_MERGE'
2797 cursor get_merge_party_id is
2798 select batch_party_id, from_party_id
2799 from HZ_MERGE_PARTIES_SUGG
2800 where batch_id = p_batch_id
2801 and merge_type = 'SAME_PARTY_MERGE';
2802
2803 -- this is for cleanse single party, check if the matched parties is already mapped
2804 cursor get_suggested_reln(l_search_ctx_id NUMBER, l_reln_obj_id NUMBER, l_master_party NUMBER
2805 , l_reln_type VARCHAR2, l_reln_code VARCHAR2, l_reln_sbj_type VARCHAR2
2806 , l_reln_obj_type VARCHAR2) is
2807 select relationship_id
2808 from HZ_MATCHED_PARTIES_GT mpgt, HZ_RELATIONSHIPS rel
2809 where mpgt.search_context_id = l_search_ctx_id
2810 and mpgt.party_id <> l_reln_obj_id
2811 and mpgt.party_id = rel.object_id
2812 and rel.subject_id = l_master_party
2813 and rel.relationship_code = l_reln_code
2814 and rel.relationship_type = l_reln_type
2815 and not exists
2816 ( select 1
2817 from HZ_MERGE_PARTIES_SUGG mp
2818 , HZ_MERGE_PARTYDTLS_SUGG mpd
2819 where mpd.merge_to_entity_id = rel.relationship_id
2820 and mpd.batch_party_id = mp.batch_party_id
2821 and mp.batch_id = p_batch_id
2822 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS');
2823
2824 -- need to check if relationship is already mapped as merge_from_entity_id
2825 cursor check_mapped_reln(l_from_rel_id NUMBER) is
2826 select 'X'
2827 from HZ_MERGE_PARTIES_SUGG mp
2828 , HZ_MERGE_PARTYDTLS_SUGG mpd
2829 where mpd.merge_from_entity_id = l_from_rel_id
2830 and mpd.batch_party_id = mp.batch_party_id
2831 and mp.batch_id = p_batch_id
2832 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS';
2833
2834 --4114254
2835 CURSOR c_get_orig_system_r(rel_id NUMBER) IS
2836 select o.orig_system
2837 from hz_orig_systems_b o,hz_relationships r
2838 where r.relationship_id = rel_id
2839 and o.orig_system = r.actual_content_source
2840 and o.orig_system_type = 'PURCHASED'
2841 and directional_flag = 'F';
2842 --4114254
2843
2844 cursor c_get_rel_status(cp_id number) is
2845 select status
2846 from hz_relationships
2847 where relationship_id = cp_id
2848 and rownum = 1;
2849
2850 TYPE merge_from_reln_tbl IS TABLE OF NUMBER;
2851 l_merge_from_reln_tbl merge_from_reln_tbl;
2852
2853 l_dummy VARCHAR2(1);
2854 l_reln_id NUMBER;
2855 l_reln_obj_id NUMBER;
2856 l_reln_sbj_id NUMBER;
2857 l_reln_type VARCHAR2(30);
2858 l_reln_code VARCHAR2(30);
2859 l_reln_bpty_id NUMBER;
2860 l_reln_sbj_type VARCHAR2(30);
2861 l_reln_obj_type VARCHAR2(30);
2862 l_rel_party_count NUMBER;
2863 l_batch_party_id NUMBER;
2864 l_from_rel_party_id NUMBER;
2865 l_to_rel_party_id NUMBER;
2866 l_merge_from_reln NUMBER;
2867 l_master_party NUMBER;
2868 l_dup_set_id NUMBER;
2869 l_search_ctx_id NUMBER;
2870 l_num_matches NUMBER;
2871 l_return_status VARCHAR2(30);
2872 l_msg_count NUMBER;
2873 l_msg_data VARCHAR2(2000);
2874
2875 --4114254
2876 l_from_orig_system HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM%TYPE;
2877 l_to_orig_system HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM%TYPE;
2878 l_to_orig_system_type HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM_TYPE%TYPE;
2879 l_flag_merge VARCHAR2(1);
2880 l_rel_status varchar2(1);
2881
2882 BEGIN
2883
2884 IF(p_merge_type = 'PARTY_MERGE') THEN
2885 -- find out all not mandatory merge relationships
2886 -- Need to get subject_id, object_id of the relationship
2887 -- Subject_id will be the merge parties
2888 -- Object_id will be the related parties
2889 -- By passing object_id to DQM and return matched related parties which are related to merge parties
2890 OPEN not_mandatory_reln_mp;
2891 LOOP
2892 FETCH not_mandatory_reln_mp into l_reln_id, l_reln_type, l_reln_code, l_reln_obj_id, l_reln_sbj_id, l_reln_sbj_type, l_reln_obj_type;
2893 EXIT WHEN not_mandatory_reln_mp%NOTFOUND;
2894
2895 -- call DQM search to find out matched parties
2896 HZ_PARTY_SEARCH.find_duplicate_parties
2897 (
2898 p_init_msg_list => FND_API.G_TRUE
2899 ,p_rule_id => p_rule_id
2900 ,p_party_id => l_reln_obj_id
2901 ,p_restrict_sql => 'stage.PARTY_ID IN (SELECT /*+ SELECTIVE */ OBJECT_ID' ||
2902 ' FROM HZ_RELATIONSHIPS rel' ||
2903 ' WHERE rel.SUBJECT_TYPE = '''||l_reln_sbj_type||''''||
2904 ' AND rel.OBJECT_TYPE = '''||l_reln_obj_type||''''||
2905 ' AND rel.RELATIONSHIP_TYPE = '''||l_reln_type||''''||
2906 ' AND rel.SUBJECT_ID IN (select dup_party_id' ||
2907 ' from HZ_DUP_SET_PARTIES where dup_set_id = '|| p_batch_id||
2908 ' and nvl(merge_flag,''Y'''||')'||' <> ''N'''||')'||
2909 ' AND rel.RELATIONSHIP_CODE = '''|| l_reln_code||''' )'
2910 ,p_match_type => 'OR'
2911 ,p_dup_batch_id => NULL
2912 ,p_search_merged => 'N'
2913 ,x_dup_set_id => l_dup_set_id
2914 ,x_search_ctx_id => l_search_ctx_id
2915 ,x_num_matches => l_num_matches
2916 ,x_return_status => l_return_status
2917 ,x_msg_count => l_msg_count
2918 ,x_msg_data => l_msg_data
2919 );
2920
2921 -- if found any matches
2922 IF (l_num_matches > 0) THEN
2923
2924 select o.orig_system,o.orig_system_type into l_to_orig_system,l_to_orig_system_type
2925 from hz_orig_systems_b o,hz_relationships r
2926 where r.relationship_id = l_reln_id
2927 and o.orig_system = r.actual_content_source
2928 and directional_flag = 'F';
2929
2930 -- start bug 5194384
2931 open c_get_rel_status(l_reln_id);
2932 fetch c_get_rel_status into l_rel_status;
2933 close c_get_rel_status;
2934
2935 if l_num_matches = 1 and l_rel_status = 'I'
2936 then
2937 begin
2938 select relationship_id into l_reln_id --get active rel id
2939 from hz_relationships
2940 where object_id = l_reln_obj_id
2941 and status = 'A'
2942 and rownum = 1;
2943 exception WHEN NO_DATA_FOUND THEN null;
2944 end;
2945 end if;
2946 if not(l_num_matches > 1 and l_rel_status = 'I')
2947 then
2948 -- end bug 5194384
2949
2950 UPDATE HZ_MERGE_PARTYDTLS_SUGG
2951 SET merge_to_entity_id = l_reln_id
2952 , mandatory_merge = 'N'
2953 , last_update_date = hz_utility_v2pub.last_update_date
2954 , last_updated_by = hz_utility_v2pub.last_updated_by
2955 , last_update_login = hz_utility_v2pub.last_update_login
2956 WHERE batch_party_id in
2957 ( SELECT batch_party_id
2958 FROM HZ_MERGE_PARTIES_SUGG
2959 WHERE batch_id = p_batch_id )
2960 AND merge_from_entity_id IN
2961 ( SELECT rel.relationship_id
2962 FROM HZ_MATCHED_PARTIES_GT matchpty
2963 , HZ_MERGE_PARTYDTLS_SUGG mpd
2964 , HZ_MERGE_PARTIES_SUGG mps
2965 , HZ_RELATIONSHIPS rel
2966 , HZ_ORIG_SYSTEMS_B O
2967 WHERE matchpty.search_context_id = l_search_ctx_id
2968 AND matchpty.party_id = rel.object_id
2969 AND rel.relationship_code = l_reln_code
2970 AND rel.relationship_type = l_reln_type
2971 AND rel.relationship_id = mpd.merge_from_entity_id
2972 AND rel.subject_type = l_reln_sbj_type
2973 AND rel.object_type = l_reln_obj_type
2974 AND mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
2975 AND mpd.merge_to_entity_id = mpd.merge_from_entity_id
2976 AND mpd.batch_party_id = mps.batch_party_id
2977 AND mps.batch_id = p_batch_id
2978 AND o.orig_system = rel.actual_content_source
2979 AND decode(o.orig_system_type,'PURCHASED',(decode(l_to_orig_system_type,'PURCHASED',(decode(o.orig_system,l_to_orig_system,1,0)),0)),1)= 1
2980 AND NOT EXISTS
2981 ( SELECT 1
2982 FROM HZ_MERGE_PARTYDTLS_SUGG mpdi,
2983 HZ_MERGE_PARTIES_SUGG mpsi
2984 WHERE mpdi.batch_party_id = mpsi.batch_party_id
2985 AND mpsi.batch_id = mps.batch_id
2986 AND mpdi.merge_to_entity_id = rel.relationship_id
2987 AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
2988 )
2989 ) RETURNING merge_from_entity_id BULK COLLECT INTO l_merge_from_reln_tbl;
2990
2991 -- as merge multiple party may find more than 1 duplicate reln parties
2992 -- we need to loop through all parties and check for relationship party
2993 FOR i IN 1..l_merge_from_reln_tbl.COUNT LOOP
2994
2995 -- check to see if there exist any relationship party, then do the party merge
2996 l_from_rel_party_id := hz_merge_util.get_reln_party_id(l_merge_from_reln_tbl(i));
2997 l_to_rel_party_id := hz_merge_util.get_reln_party_id(l_reln_id);
2998
2999 select count(1) into l_rel_party_count
3000 from HZ_MERGE_PARTIES
3001 where batch_id = p_batch_id
3002 and merge_type = 'PARTY_MERGE'
3003 and merge_reason_code = 'DUPLICATE_RELN_PARTY'
3004 and from_party_id = l_from_rel_party_id;
3005
3006 -- if not found any DUPLICATE_RELN_PARTY for the merge batch, create one
3007 IF l_rel_party_count = 0 AND l_from_rel_party_id IS NOT NULL AND l_to_rel_party_id IS NOT NULL THEN
3008 insert_sugg_reln_party(p_batch_id
3009 ,l_from_rel_party_id
3010 ,l_to_rel_party_id
3011 ,l_reln_bpty_id);
3012 -- check to see if those relationship party has party_site, merge it as mandatory
3013 -- Also insert the Party sites for reln Party if there are any
3014 insert_sugg_reln_ps_details(l_from_rel_party_id
3015 ,l_to_rel_party_id
3016 ,l_reln_bpty_id, 'Y');
3017 END IF; --l_rel_party_count
3018 END LOOP; --count the number of reln_id and loop to insert relationship's party
3019 end if; -- not (l_num_matches > 1...
3020 END IF; -- l_num_matches > 0
3021 END LOOP; -- not_mandatory_reln_mp
3022 CLOSE not_mandatory_reln_mp;
3023 ELSIF (p_merge_type = 'SAME_PARTY_MERGE') THEN
3024 -- get batch_party_id and master party_id of the merge request, only one record for same party merge
3025 OPEN get_merge_party_id;
3026 FETCH get_merge_party_id INTO l_batch_party_id, l_master_party;
3027 CLOSE get_merge_party_id;
3028
3029 -- find out all not mandatory merge relationships
3030 OPEN not_mandatory_reln_sp(l_master_party);
3031 LOOP
3032 FETCH not_mandatory_reln_sp into l_reln_id, l_reln_type, l_reln_code, l_reln_obj_id, l_reln_sbj_type, l_reln_obj_type;
3033 EXIT WHEN not_mandatory_reln_sp%NOTFOUND;
3034
3035 HZ_PARTY_SEARCH.find_duplicate_parties
3036 (
3037 p_init_msg_list => FND_API.G_TRUE
3038 ,p_rule_id => p_rule_id
3039 ,p_party_id => l_reln_obj_id
3040 ,p_restrict_sql => 'stage.PARTY_ID IN (SELECT /*+ SELECTIVE */ OBJECT_ID' ||
3041 ' FROM HZ_RELATIONSHIPS rel' ||
3042 ' WHERE rel.SUBJECT_ID = '|| l_master_party||
3043 ' AND rel.SUBJECT_TYPE = '''||l_reln_sbj_type||''''||
3044 ' AND rel.OBJECT_TYPE = '''||l_reln_obj_type||''''||
3045 ' AND rel.relationship_type = '''||l_reln_type||''''||
3046 ' AND rel.relationship_code = '''|| l_reln_code||''' )'
3047 ,p_match_type => 'OR'
3048 ,p_dup_batch_id => NULL
3049 ,p_search_merged => 'N'
3050 ,x_dup_set_id => l_dup_set_id
3051 ,x_search_ctx_id => l_search_ctx_id
3052 ,x_num_matches => l_num_matches
3053 ,x_return_status => l_return_status
3054 ,x_msg_count => l_msg_count
3055 ,x_msg_data => l_msg_data
3056 );
3057
3058 IF (l_num_matches > 0) THEN
3059 OPEN get_suggested_reln(l_search_ctx_id, l_reln_obj_id, l_master_party, l_reln_type, l_reln_code, l_reln_sbj_type, l_reln_obj_type);
3060 LOOP
3061 FETCH get_suggested_reln into l_merge_from_reln;
3062 EXIT WHEN get_suggested_reln%NOTFOUND;
3063
3064 -- if relationship is not mapped, then create mapping
3065 OPEN check_mapped_reln(l_merge_from_reln);
3066 FETCH check_mapped_reln into l_dummy;
3067 IF (check_mapped_reln%NOTFOUND) THEN
3068
3069 --4114254
3070 l_flag_merge := 'Y';
3071 OPEN c_get_orig_system_r(l_merge_from_reln);
3072 FETCH c_get_orig_system_r INTO l_from_orig_system;
3073
3074 IF c_get_orig_system_r%FOUND THEN
3075 CLOSE c_get_orig_system_r;
3076 OPEN c_get_orig_system_r(l_reln_id);
3077 FETCH c_get_orig_system_r INTO l_to_orig_system;
3078
3079 IF (c_get_orig_system_r%FOUND AND
3080 l_to_orig_system <> l_from_orig_system
3081 ) OR
3082 (c_get_orig_system_r%NOTFOUND)
3083 THEN
3084 l_flag_merge := 'N';
3085 END IF;
3086 CLOSE c_get_orig_system_r;
3087 ELSE /* c_get_orig_system%NOTFOUND */
3088 CLOSE c_get_orig_system_r;
3089 END IF; /* c_get_orig_system%FOUND */
3090
3091 IF l_flag_merge = 'Y' THEN
3092 INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3093 (
3094 batch_party_id
3095 ,entity_name
3096 ,merge_from_entity_id
3097 ,merge_to_entity_id
3098 ,mandatory_merge
3099 ,created_by
3100 ,creation_date
3101 ,last_update_login
3102 ,last_update_date
3103 ,last_updated_by
3104 ,object_version_number
3105 )
3106 VALUES
3107 (
3108 l_batch_party_id
3109 ,'HZ_PARTY_RELATIONSHIPS'
3110 ,l_merge_from_reln
3111 ,l_reln_id
3112 ,'N'
3113 ,hz_utility_v2pub.created_by
3114 ,hz_utility_v2pub.creation_date
3115 ,hz_utility_v2pub.last_update_login
3116 ,hz_utility_v2pub.last_update_date
3117 ,hz_utility_v2pub.last_updated_by
3118 ,1
3119 );
3120
3121 -- find out those related party sites
3122 l_from_rel_party_id := hz_merge_util.get_reln_party_id(l_merge_from_reln);
3123 l_to_rel_party_id := hz_merge_util.get_reln_party_id(l_reln_id);
3124
3125 select count(1) into l_rel_party_count
3126 from HZ_MERGE_PARTIES
3127 where batch_id = p_batch_id
3128 and merge_type = 'PARTY_MERGE'
3129 and merge_reason_code = 'DUPLICATE_RELN_PARTY'
3130 and from_party_id = l_from_rel_party_id;
3131
3132 -- if the relationship has party record, then do merge on those relationship party
3133 if((l_from_rel_party_id is not null) and
3134 (l_to_rel_party_id is not null) and
3135 (l_from_rel_party_id <> l_to_rel_party_id)) then
3136 if(l_rel_party_count = 0) then
3137 -- insert party relationship merge record
3138 insert_sugg_reln_party(p_batch_id
3139 ,l_from_rel_party_id
3140 ,l_to_rel_party_id
3141 ,l_reln_bpty_id);
3142 -- insert relationship party's sites record
3143 insert_sugg_reln_ps_details(l_from_rel_party_id
3144 ,l_to_rel_party_id
3145 ,l_reln_bpty_id, 'Y');
3146 end if; -- check if the relationship party record has been added to HZ_MERGE_PARTIES
3147 end if; -- check if there exist relationship party
3148
3149 END IF;--l_merge_flag
3150 END IF; -- check_mapped_reln
3151 CLOSE check_mapped_reln;
3152 END LOOP; -- get_suggested_addr
3153 CLOSE get_suggested_reln;
3154 END IF; -- l_num_matches > 0
3155 END LOOP; -- not_mandatory_reln_sp
3156 CLOSE not_mandatory_reln_sp;
3157 END IF; -- p_merge_type = 'SAME_PARTY_MERGE'
3158
3159 EXCEPTION
3160
3161 WHEN OTHERS THEN
3162 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3163 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3164 APP_EXCEPTION.RAISE_EXCEPTION;
3165
3166 END suggested_party_reln;
3167
3168 PROCEDURE insert_sugg_reln_ps_details (
3169 p_from_party_id IN NUMBER,
3170 p_to_party_id IN NUMBER,
3171 p_batch_party_id IN NUMBER,
3172 p_reln_parties IN VARCHAR2 DEFAULT 'N'
3173 ) IS
3174
3175 --Cursor for inserting Party sites that are non-DNB
3176 CURSOR c_from_ps_loc IS
3177 SELECT party_site_id, ps.location_id
3178 FROM HZ_PARTY_SITES ps
3179 WHERE ps.party_id = p_from_party_id
3180 AND ps.actual_content_source <>'DNB'
3181 AND nvl(status, 'A') in ('A','I');
3182
3183 CURSOR c_dup_to_ps(cp_loc_id NUMBER) IS
3184 SELECT party_site_id
3185 FROM HZ_PARTY_SITES ps
3186 WHERE ps.party_id = p_to_party_id
3187 AND ps.location_id = cp_loc_id
3188 AND ps.actual_content_source <>'DNB'
3189 AND nvl(status, 'A') in ('A','I');
3190
3191 l_ps_id NUMBER;
3192 l_loc_id NUMBER;
3193 l_dup_ps_id NUMBER;
3194 l_sqerr VARCHAR2(2000);
3195 l_mandatory_merge VARCHAR2(1);
3196
3197 BEGIN
3198
3199 OPEN c_from_ps_loc;
3200 LOOP
3201 FETCH c_from_ps_loc INTO l_ps_id, l_loc_id;
3202 EXIT WHEN c_from_ps_loc%NOTFOUND;
3203 IF p_from_party_id <> p_to_party_id THEN
3204 l_mandatory_merge := 'Y';
3205 ELSE
3206 l_mandatory_merge := 'N';
3207 END IF;
3208
3209 OPEN c_dup_to_ps(l_loc_id);
3210 FETCH c_dup_to_ps INTO l_dup_ps_id;
3211 IF c_dup_to_ps%FOUND THEN
3212 INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3213 (
3214 batch_party_id
3215 ,entity_name
3216 ,merge_from_entity_id
3217 ,merge_to_entity_id
3218 ,mandatory_merge
3219 ,created_by
3220 ,creation_date
3221 ,last_update_login
3222 ,last_update_date
3223 ,last_updated_by
3224 ,object_version_number
3225 )
3226 VALUES
3227 (
3228 p_batch_party_id
3229 ,'HZ_PARTY_SITES'
3230 ,l_ps_id
3231 ,l_dup_ps_id
3232 ,l_mandatory_merge
3233 ,hz_utility_v2pub.created_by
3234 ,hz_utility_v2pub.creation_date
3235 ,hz_utility_v2pub.last_update_login
3236 ,hz_utility_v2pub.last_update_date
3237 ,hz_utility_v2pub.last_updated_by
3238 ,1
3239 );
3240 ELSE
3241 IF p_reln_parties = 'N' THEN
3242 INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3243 (
3244 batch_party_id
3245 ,entity_name
3246 ,merge_from_entity_id
3247 ,merge_to_entity_id
3248 ,mandatory_merge
3249 ,created_by
3250 ,creation_date
3251 ,last_update_login
3252 ,last_update_date
3253 ,last_updated_by
3254 ,object_version_number
3255 )
3256 VALUES
3257 (
3258 p_batch_party_id
3259 ,'HZ_PARTY_SITES'
3260 ,l_ps_id
3261 ,null
3262 ,'N'
3263 ,hz_utility_v2pub.created_by
3264 ,hz_utility_v2pub.creation_date
3265 ,hz_utility_v2pub.last_update_login
3266 ,hz_utility_v2pub.last_update_date
3267 ,hz_utility_v2pub.last_updated_by
3268 ,1
3269 );
3270 ELSE
3271 INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3272 (
3273 batch_party_id
3274 ,entity_name
3275 ,merge_from_entity_id
3276 ,merge_to_entity_id
3277 ,mandatory_merge
3278 ,created_by
3279 ,creation_date
3280 ,last_update_login
3281 ,last_update_date
3282 ,last_updated_by
3283 ,object_version_number
3284 )
3285 VALUES
3286 (
3287 p_batch_party_id
3288 ,'HZ_PARTY_SITES'
3289 ,l_ps_id
3290 ,l_ps_id
3291 ,'N'
3292 ,hz_utility_v2pub.created_by
3293 ,hz_utility_v2pub.creation_date
3294 ,hz_utility_v2pub.last_update_login
3295 ,hz_utility_v2pub.last_update_date
3296 ,hz_utility_v2pub.last_updated_by
3297 ,1
3298 );
3299 END IF; -- p_reln_parties = 'N'
3300 END IF;
3301 CLOSE c_dup_to_ps;
3302 END LOOP;
3303 CLOSE c_from_ps_loc;
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3307 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3308 APP_EXCEPTION.RAISE_EXCEPTION;
3309 END insert_sugg_reln_ps_details;
3310
3311 PROCEDURE insert_sugg_reln_party(
3312 p_batch_id IN NUMBER,
3313 p_from_rel_party_id IN NUMBER,
3314 p_to_rel_party_id IN NUMBER,
3315 x_batch_party_id OUT NOCOPY NUMBER
3316 ) IS
3317
3318 BEGIN
3319
3320 select HZ_MERGE_PARTIES_S.nextval into x_batch_party_id
3321 from dual;
3322
3323 INSERT INTO HZ_MERGE_PARTIES_SUGG
3324 (
3325 BATCH_PARTY_ID
3326 ,BATCH_ID
3327 ,MERGE_TYPE
3328 ,FROM_PARTY_ID
3329 ,TO_PARTY_ID
3330 ,MERGE_REASON_CODE
3331 ,MERGE_STATUS
3332 ,created_by
3333 ,creation_date
3334 ,last_update_login
3335 ,last_update_date
3336 ,last_updated_by
3337 )
3338 VALUES
3339 (
3340 x_batch_party_id
3341 ,p_batch_id
3342 ,'PARTY_MERGE'
3343 ,p_from_rel_party_id
3344 ,p_to_rel_party_id
3345 ,'DUPLICATE_RELN_PARTY'
3346 ,'PENDING'
3347 ,hz_utility_v2pub.created_by
3348 ,hz_utility_v2pub.creation_date
3349 ,hz_utility_v2pub.last_update_login
3350 ,hz_utility_v2pub.last_update_date
3351 ,hz_utility_v2pub.last_updated_by
3352 );
3353
3354 EXCEPTION
3355 WHEN OTHERS THEN
3356 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3357 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3358 APP_EXCEPTION.RAISE_EXCEPTION;
3359 END insert_sugg_reln_party;
3360
3361 --
3362 -- PROCEDURE apply_suggested_default
3363 --
3364 -- DESCRIPTION
3365 -- Copy suggested default mapping to HZ_MERGE_PARTY_DETAILS
3366 --
3367 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3368 --
3369 -- ARGUMENTS
3370 -- IN:
3371 -- p_batch_id ID of the merge batch
3372 -- p_entity_name HZ_PARTY_SITES - Addresses or HZ_RELATIONSHIPS - Relationships
3373 -- p_merge_type Merge type of the dup set
3374 --
3375 -- OUT:
3376 -- x_return_status Return status after the call. The status can
3377 -- be fnd_api.g_ret_sts_success (success),
3378 -- fnd_api.g_ret_sts_error (error),
3379 -- fnd_api.g_ret_sts_unexp_error
3380 -- (unexpected error).
3381 -- x_msg_count Number of messages in message stack.
3382 -- x_msg_data Message text if x_msg_count is 1.
3383 --
3384 -- NOTES
3385 --
3386 -- MODIFICATION HISTORY
3387 --
3388 -- 10/09/2002 Arnold Ng o Created.
3389 --
3390 --
3391 PROCEDURE apply_suggested_default (
3392 p_batch_id IN NUMBER
3393 ,p_entity_name IN VARCHAR2
3394 ,x_return_status OUT NOCOPY VARCHAR2
3395 ,x_msg_count OUT NOCOPY NUMBER
3396 ,x_msg_data OUT NOCOPY VARCHAR2
3397 ) IS
3398 BEGIN
3399
3400 savepoint apply_suggested_default;
3401
3402 x_return_status := FND_API.G_RET_STS_SUCCESS;
3403
3404 DELETE FROM HZ_MERGE_PARTY_DETAILS
3405 WHERE BATCH_PARTY_ID IN
3406 ( SELECT BATCH_PARTY_ID
3407 FROM HZ_MERGE_PARTIES
3408 WHERE BATCH_ID = p_batch_id )
3409 AND ENTITY_NAME = p_entity_name;
3410
3411 INSERT INTO HZ_MERGE_PARTY_DETAILS
3412 (
3413 batch_party_id
3414 ,entity_name
3415 ,merge_from_entity_id
3416 ,merge_to_entity_id
3417 ,mandatory_merge
3418 ,created_by
3419 ,creation_date
3420 ,last_update_login
3421 ,last_updated_by
3422 ,last_update_date
3423 ,object_version_number
3424 )
3425 SELECT
3426 batch_party_id
3427 ,entity_name
3428 ,merge_from_entity_id
3429 ,merge_to_entity_id
3430 ,mandatory_merge
3431 ,hz_utility_v2pub.created_by
3432 ,hz_utility_v2pub.creation_date
3433 ,hz_utility_v2pub.last_update_login
3434 ,hz_utility_v2pub.last_updated_by
3435 ,hz_utility_v2pub.last_update_date
3436 ,1
3437 FROM HZ_MERGE_PARTYDTLS_SUGG
3438 WHERE entity_name = p_entity_name
3439 AND batch_party_id IN
3440 ( SELECT batch_party_id
3441 FROM HZ_MERGE_PARTIES_SUGG
3442 WHERE batch_id = p_batch_id );
3443
3444 -- if entity is HZ_RELATIONSHIPS, insert DUPLICATE_RELN_PARTY record to
3445 -- HZ_MERGE_PARTIES table as well
3446
3447 IF(p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
3448
3449 DELETE FROM HZ_MERGE_PARTIES
3450 WHERE batch_id = p_batch_id
3451 AND merge_reason_code = 'DUPLICATE_RELN_PARTY';
3452
3453 INSERT INTO HZ_MERGE_PARTIES
3454 (
3455 batch_party_id
3456 ,batch_id
3457 ,merge_type
3458 ,from_party_id
3459 ,to_party_id
3460 ,merge_reason_code
3461 ,merge_status
3462 ,created_by
3463 ,creation_date
3464 ,last_update_login
3465 ,last_updated_by
3466 ,last_update_date
3467 )
3468 SELECT
3469 batch_party_id
3470 ,batch_id
3471 ,merge_type
3472 ,from_party_id
3473 ,to_party_id
3474 ,merge_reason_code
3475 ,merge_status
3476 ,created_by
3477 ,creation_date
3478 ,last_update_login
3479 ,last_updated_by
3480 ,last_update_date
3481 FROM HZ_MERGE_PARTIES_SUGG mp
3482 WHERE mp.batch_id = p_batch_id
3483 AND mp.merge_reason_code = 'DUPLICATE_RELN_PARTY';
3484
3485 END IF;
3486
3487 EXCEPTION
3488
3489 WHEN FND_API.G_EXC_ERROR THEN
3490 ROLLBACK TO apply_suggested_default;
3491 x_return_status := FND_API.G_RET_STS_ERROR;
3492 FND_MSG_PUB.Count_And_Get(
3493 p_encoded => FND_API.G_FALSE,
3494 p_count => x_msg_count,
3495 p_data => x_msg_data);
3496
3497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3498 ROLLBACK TO apply_suggested_default;
3499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3500 FND_MSG_PUB.Count_And_Get(
3501 p_encoded => FND_API.G_FALSE,
3502 p_count => x_msg_count,
3503 p_data => x_msg_data);
3504
3505 WHEN OTHERS THEN
3506 ROLLBACK TO apply_suggested_default;
3507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3508 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3509 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3510 FND_MSG_PUB.ADD;
3511 FND_MSG_PUB.Count_And_Get(
3512 p_encoded => FND_API.G_FALSE,
3513 p_count => x_msg_count,
3514 p_data => x_msg_data);
3515
3516 END apply_suggested_default;
3517
3518 --
3519 -- PROCEDURE clear_suggested_default
3520 --
3521 -- DESCRIPTION
3522 -- Clear address/relationship mapping
3523 --
3524 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3525 --
3526 -- ARGUMENTS
3527 -- IN:
3528 -- p_batch_id ID of the merge batch
3529 -- p_entity_name HZ_PARTY_SITES - Addresses or HZ_RELATIONSHIPS - Relationships
3530 -- p_merge_type Merge type of the dup set
3531 --
3532 -- OUT:
3533 -- x_return_status Return status after the call. The status can
3534 -- be fnd_api.g_ret_sts_success (success),
3535 -- fnd_api.g_ret_sts_error (error),
3536 -- fnd_api.g_ret_sts_unexp_error
3537 -- (unexpected error).
3538 -- x_msg_count Number of messages in message stack.
3539 -- x_msg_data Message text if x_msg_count is 1.
3540 --
3541 -- NOTES
3542 --
3543 -- MODIFICATION HISTORY
3544 --
3545 -- 10/09/2002 Arnold Ng o Created.
3546 --
3547 --
3548 PROCEDURE clear_suggested_default (
3549 p_batch_id IN NUMBER
3550 ,p_entity_name IN VARCHAR2
3551 ,p_merge_type IN VARCHAR2
3552 ,x_return_status OUT NOCOPY VARCHAR2
3553 ,x_msg_count OUT NOCOPY NUMBER
3554 ,x_msg_data OUT NOCOPY VARCHAR2
3555 ) IS
3556
3557 CURSOR find_mand_reln IS
3558 SELECT HZ_MERGE_UTIL.get_reln_party_id(a.merge_from_entity_id)
3559 , HZ_MERGE_UTIL.get_reln_party_id(a.merge_from_entity_id)
3560 FROM HZ_MERGE_PARTYDTLS_SUGG a
3561 , HZ_MERGE_PARTIES_SUGG b
3562 WHERE b.batch_id = p_batch_id
3563 AND a.entity_name = 'HZ_PARTY_RELATIONSHIPS'
3564 AND a.batch_party_id = b.batch_party_id
3565 AND a.mandatory_merge = 'Y';
3566
3567 l_reln_from_pid NUMBER;
3568 l_reln_to_pid NUMBER;
3569 l_reln_bpty_id NUMBER;
3570
3571 BEGIN
3572
3573
3574 savepoint clear_suggested_default;
3575
3576 x_return_status := FND_API.G_RET_STS_SUCCESS;
3577
3578 IF(p_merge_type = 'PARTY_MERGE') THEN
3579
3580
3581 -- copy mapping from suggested defaults table
3582 UPDATE HZ_MERGE_PARTY_DETAILS mpd
3583 SET mpd.merge_from_entity_id =
3584 ( SELECT merge_from_entity_id
3585 FROM HZ_MERGE_PARTYDTLS_SUGG mps
3586 WHERE mpd.batch_party_id = mps.batch_party_id
3587 AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3588 AND mpd.entity_name = mps.entity_name ),
3589 mpd.mandatory_merge =
3590 ( SELECT mandatory_merge
3591 FROM HZ_MERGE_PARTYDTLS_SUGG mps
3592 WHERE mpd.batch_party_id = mps.batch_party_id
3593 AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3594 AND mpd.entity_name = mps.entity_name ),
3595 mpd.last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN ,
3596 mpd.last_updated_by = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
3597 mpd.last_update_date = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
3598 mpd.object_version_number = nvl(mpd.object_version_number,1)+1
3599 WHERE entity_name = p_entity_name
3600 AND batch_party_id IN
3601 ( SELECT batch_party_id
3602 FROM HZ_MERGE_PARTIES_SUGG
3603 WHERE batch_id = p_batch_id );
3604
3605
3606
3607 -- clean up all non-mandatory merge sites and reln mapping in merge party details
3608 UPDATE HZ_MERGE_PARTY_DETAILS
3609 SET merge_to_entity_id = merge_from_entity_id
3610 WHERE mandatory_merge <> 'Y'
3611 AND entity_name = p_entity_name
3612 AND batch_party_id IN
3613 ( SELECT batch_party_id
3614 FROM HZ_MERGE_PARTIES
3615 WHERE batch_id = p_batch_id );
3616
3617
3618 ELSIF(p_merge_type = 'SAME_PARTY_MERGE') THEN
3619
3620 -- remove all mapping
3621 DELETE HZ_MERGE_PARTY_DETAILS
3622 WHERE entity_name = p_entity_name
3623 AND batch_party_id IN
3624 ( SELECT batch_party_id
3625 FROM HZ_MERGE_PARTIES
3626 WHERE batch_id = p_batch_id );
3627
3628 -- only insert those mandatory merge mapping
3629 INSERT INTO HZ_MERGE_PARTY_DETAILS
3630 (
3631 batch_party_id
3632 ,entity_name
3633 ,merge_from_entity_id
3634 ,merge_to_entity_id
3635 ,mandatory_merge
3636 ,created_by
3637 ,creation_date
3638 ,last_update_login
3639 ,last_updated_by
3640 ,last_update_date
3641 ,object_version_number
3642 )
3643 SELECT
3644 batch_party_id
3645 ,entity_name
3646 ,merge_from_entity_id
3647 ,merge_to_entity_id
3648 ,mandatory_merge
3649 ,hz_utility_v2pub.created_by
3650 ,hz_utility_v2pub.creation_date
3651 ,hz_utility_v2pub.last_update_login
3652 ,hz_utility_v2pub.last_updated_by
3653 ,hz_utility_v2pub.last_update_date
3654 ,1
3655 FROM HZ_MERGE_PARTYDTLS_SUGG
3656 WHERE mandatory_merge = 'Y'
3657 AND entity_name = p_entity_name
3658 AND batch_party_id IN
3659 ( SELECT batch_party_id
3660 FROM HZ_MERGE_PARTIES_SUGG
3661 WHERE batch_id = p_batch_id );
3662
3663 END IF;
3664
3665 IF(p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
3666
3667 -- remove all DUPLICATE_RELN_PARTY record in HZ_MERGE_PARTIES first
3668 -- then recreate them by finding out all relationship mandatory
3669 -- merge at HZ_MERGE_PARTY_DETAILS
3670
3671
3672 DELETE FROM HZ_MERGE_PARTIES mp
3673 WHERE mp.batch_id = p_batch_id
3674 AND mp.merge_reason_code = 'DUPLICATE_RELN_PARTY';
3675
3676
3677 OPEN find_mand_reln;
3678 LOOP
3679 FETCH find_mand_reln INTO l_reln_from_pid, l_reln_to_pid;
3680 EXIT WHEN find_mand_reln%NOTFOUND;
3681
3682
3683 insert_sugg_reln_party(p_batch_id
3684 ,l_reln_from_pid
3685 ,l_reln_to_pid
3686 ,l_reln_bpty_id);
3687
3688
3689 -- check to see if those relationship party has party_site, merge it as mandatory
3690 -- Also insert the Party sites for reln Party if there are any
3691 insert_sugg_reln_ps_details(l_reln_from_pid
3692 ,l_reln_to_pid
3693 ,l_reln_bpty_id, 'Y');
3694
3695
3696 END LOOP;
3697
3698
3699 CLOSE find_mand_reln;
3700
3701 END IF;
3702
3703 EXCEPTION
3704
3705 WHEN FND_API.G_EXC_ERROR THEN
3706 ROLLBACK TO clear_suggested_default;
3707 x_return_status := FND_API.G_RET_STS_ERROR;
3708 FND_MSG_PUB.Count_And_Get(
3709 p_encoded => FND_API.G_FALSE,
3710 p_count => x_msg_count,
3711 p_data => x_msg_data);
3712
3713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3714 ROLLBACK TO clear_suggested_default;
3715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3716 FND_MSG_PUB.Count_And_Get(
3717 p_encoded => FND_API.G_FALSE,
3718 p_count => x_msg_count,
3719 p_data => x_msg_data);
3720
3721 WHEN OTHERS THEN
3722 ROLLBACK TO clear_suggested_default;
3723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3724 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
3725 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3726 FND_MSG_PUB.ADD;
3727 FND_MSG_PUB.Count_And_Get(
3728 p_encoded => FND_API.G_FALSE,
3729 p_count => x_msg_count,
3730 p_data => x_msg_data);
3731
3732 END clear_suggested_default;
3733
3734 --
3735 -- PROCEDURE create_reln_sysbatch
3736 --
3737 -- DESCRIPTION
3738 -- Create dup batch based on relationship merge of a merge batch
3739 -- If there exists relationship mapping, this procedure will create
3740 -- dup batch for both parties involved in the relationship merge
3741 -- E.g.: Peter-Contact Of-Oracle merge to Peter2-Contact Of-Oracle
3742 -- Then, create dup batch for Peter merge to Peter2
3743 --
3744 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3745 --
3746 -- ARGUMENTS
3747 -- IN:
3748 -- p_batch_id ID of the merge batch
3749 -- p_merge_type Merge type of the dup set
3750 --
3751 -- OUT:
3752 -- x_return_status Return status after the call. The status can
3753 -- be fnd_api.g_ret_sts_success (success),
3754 -- fnd_api.g_ret_sts_error (error),
3755 -- fnd_api.g_ret_sts_unexp_error
3756 -- (unexpected error).
3757 -- x_msg_count Number of messages in message stack.
3758 -- x_msg_data Message text if x_msg_count is 1.
3759 --
3760 -- NOTES
3761 --
3762 -- MODIFICATION HISTORY
3763 --
3764 -- 10/09/2002 Arnold Ng o Created.
3765 --
3766 --
3767 PROCEDURE create_reln_sysbatch (
3768 p_batch_id IN NUMBER
3769 ,p_merge_type IN VARCHAR2
3770 ,x_return_status OUT NOCOPY VARCHAR2
3771 ,x_msg_count OUT NOCOPY NUMBER
3772 ,x_msg_data OUT NOCOPY VARCHAR2
3773 ) IS
3774
3775 CURSOR get_reln_party_sp IS
3776 select to_rel.subject_id, count(1)
3777 from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3778 , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3779 , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3780 where mpd.batch_party_id = mp.batch_party_id
3781 and mp.batch_id = p_batch_id
3782 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
3783 and mpd.merge_from_entity_id = from_rel.relationship_id
3784 and mpd.merge_to_entity_id = to_rel.relationship_id
3785 and from_rel.object_id =
3786 ( select winner_party_id
3787 from HZ_DUP_SETS where dup_set_id = p_batch_id )
3788 and to_rel.object_id =
3789 ( select winner_party_id
3790 from HZ_DUP_SETS where dup_set_id = p_batch_id )
3791 and from_rel.subject_id = from_pty.party_id
3792 and to_rel.subject_id = to_pty.party_id
3793 group by to_rel.subject_id;
3794
3795 CURSOR get_reln_party_mp IS
3796 select to_rel.subject_id, count(1)
3797 from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3798 , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3799 , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3800 where mpd.batch_party_id = mp.batch_party_id
3801 and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
3802 and mp.batch_id = p_batch_id
3803 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
3804 and mpd.merge_from_entity_id = from_rel.relationship_id
3805 and mpd.merge_to_entity_id = to_rel.relationship_id
3806 and from_rel.object_id in
3807 ( select dup_party_id
3808 from HZ_DUP_SET_PARTIES
3809 where dup_set_id = p_batch_id
3810 and nvl(merge_flag,'Y') <> 'N' )
3811 and to_rel.object_id in
3812 ( select dup_party_id
3813 from HZ_DUP_SET_PARTIES
3814 where dup_set_id = p_batch_id
3815 and nvl(merge_flag,'Y') <> 'N' )
3816 and from_rel.subject_id = from_pty.party_id
3817 and to_rel.subject_id = to_pty.party_id
3818 group by to_rel.subject_id;
3819
3820 CURSOR get_reln_from_party_sp(l_to_party_id NUMBER) IS
3821 select from_rel.subject_id
3822 from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3823 , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3824 , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3825 where mpd.batch_party_id = mp.batch_party_id
3826 and mp.batch_id = p_batch_id
3827 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
3828 and mpd.merge_from_entity_id = from_rel.relationship_id
3829 and mpd.merge_to_entity_id = to_rel.relationship_id
3830 and from_rel.object_id =
3831 ( select winner_party_id
3832 from HZ_DUP_SETS where dup_set_id = p_batch_id )
3833 and to_rel.object_id =
3834 ( select winner_party_id
3835 from HZ_DUP_SETS where dup_set_id = p_batch_id )
3836 and from_rel.subject_id = from_pty.party_id
3837 and to_rel.subject_id = l_to_party_id
3838 and to_rel.subject_id = to_pty.party_id;
3839
3840 CURSOR get_reln_from_party_mp(l_to_party_id NUMBER) IS
3841 select from_rel.subject_id
3842 from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3843 , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3844 , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3845 where mpd.batch_party_id = mp.batch_party_id
3846 and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
3847 and mp.batch_id = p_batch_id
3848 and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
3849 and mpd.merge_from_entity_id = from_rel.relationship_id
3850 and mpd.merge_to_entity_id = to_rel.relationship_id
3851 and from_rel.object_id in
3852 ( select dup_party_id
3853 from HZ_DUP_SET_PARTIES
3854 where dup_set_id = p_batch_id
3855 and nvl(merge_flag,'Y') <> 'N' )
3856 and to_rel.object_id in
3857 ( select dup_party_id
3858 from HZ_DUP_SET_PARTIES
3859 where dup_set_id = p_batch_id
3860 and nvl(merge_flag,'Y') <> 'N' )
3861 and from_rel.subject_id = from_pty.party_id
3862 and to_rel.subject_id = to_pty.party_id
3863 and to_rel.subject_id = l_to_party_id;
3864
3865 CURSOR get_master_party is
3866 select party_name
3867 from HZ_DUP_SETS a, HZ_PARTIES b
3868 where a.winner_party_id = b.party_id
3869 and a.dup_set_id = p_batch_id;
3870
3871 CURSOR get_cand_party(l_party_id NUMBER) is
3872 select party_name
3873 from HZ_PARTIES
3874 where party_id = l_party_id;
3875
3876 cursor is_sugg_request_done_csr is
3877 select count(*)
3878 from hz_merge_batch
3879 where batch_id = p_batch_id
3880 and created_by_module = 'DL_DONESUGG';
3881
3882
3883 l_from_party_id NUMBER;
3884 l_to_party_id NUMBER;
3885
3886 l_dup_batch_rec HZ_DUP_PVT.DUP_BATCH_REC_TYPE;
3887 l_dup_set_rec HZ_DUP_PVT.DUP_SET_REC_TYPE;
3888 l_dup_party_tbl HZ_DUP_PVT.DUP_PARTY_TBL_TYPE;
3889 l_dup_batch_id NUMBER;
3890 l_dup_set_id NUMBER;
3891 l_return_status VARCHAR2(30);
3892 l_msg_count NUMBER;
3893 l_msg_data VARCHAR2(2000);
3894 l_set_count NUMBER;
3895 l_count NUMBER;
3896 l_party_name VARCHAR2(360);
3897 l_master_party VARCHAR2(360);
3898 l_cand_party VARCHAR2(360);
3899 l_set_obj_version_number NUMBER;
3900 l_merge_batch_id NUMBER;
3901 l_request_id number;
3902 BEGIN
3903
3904 x_return_status := FND_API.G_RET_STS_SUCCESS;
3905
3906 IF (NVL(FND_PROFILE.VALUE('HZ_DL_CREATE_SUGG_MERGE_REQ'), 'Y') = 'Y') THEN
3907
3908 open is_sugg_request_done_csr;
3909 fetch is_sugg_request_done_csr into l_count;
3910 close is_sugg_request_done_csr;
3911
3912 if l_count <>0
3913 then return;
3914 end if;
3915
3916 fnd_msg_pub.initialize;
3917
3918 savepoint create_reln_sysbatch;
3919
3920 update hz_merge_batch
3921 set created_by_module = 'DL_DONESUGG'
3922 where batch_id = p_batch_id;
3923
3924
3925 OPEN get_master_party;
3926 FETCH get_master_party into l_master_party;
3927 CLOSE get_master_party;
3928
3929 IF(p_merge_type = 'PARTY_MERGE') THEN
3930 OPEN get_reln_party_mp;
3931 LOOP
3932 FETCH get_reln_party_mp INTO l_to_party_id, l_set_count;
3933 EXIT WHEN get_reln_party_mp%NOTFOUND;
3934
3935 OPEN get_cand_party(l_to_party_id);
3936 FETCH get_cand_party INTO l_cand_party;
3937 CLOSE get_cand_party;
3938
3939 -- initialize the table in each loop
3940
3941 IF ( l_dup_party_tbl.COUNT > 0 ) THEN
3942 l_dup_party_tbl.DELETE ;
3943 END IF ;
3944
3945 l_dup_batch_rec.dup_batch_name := 'SUGG: '||l_cand_party||' - '||l_master_party||'('||p_batch_id||')';
3946 l_dup_batch_rec.match_rule_id := -1;
3947 l_dup_batch_rec.application_id := 222;
3948 l_dup_batch_rec.request_type := 'SYSTEM_GENERATED';
3949 l_dup_batch_id := NULL;
3950 l_dup_set_rec.winner_party_id := l_to_party_id;
3951 l_dup_set_rec.status := 'SYSBATCH';
3952 l_dup_set_rec.assigned_to_user_id := NULL;
3953 l_dup_set_rec.merge_type := 'PARTY_MERGE';
3954 l_dup_party_tbl(1).party_id := l_to_party_id;
3955 l_dup_party_tbl(1).score := -1;
3956 l_dup_party_tbl(1).merge_flag := NULL;
3957
3958 l_count := 2;
3959
3960 OPEN get_reln_from_party_mp(l_to_party_id);
3961 LOOP
3962 FETCH get_reln_from_party_mp INTO l_from_party_id;
3963 EXIT WHEN get_reln_from_party_mp%NOTFOUND;
3964
3965 l_dup_party_tbl(l_count).party_id := l_from_party_id;
3966 l_dup_party_tbl(l_count).score := -1;
3967 l_dup_party_tbl(l_count).merge_flag := 'Y';
3968 l_count := l_count + 1;
3969
3970 END LOOP;
3971 CLOSE get_reln_from_party_mp;
3972
3973 if l_from_party_id <> l_to_party_id
3974 then
3975 HZ_DUP_PVT.create_dup_batch(
3976 p_dup_batch_rec => l_dup_batch_rec
3977 ,p_dup_set_rec => l_dup_set_rec
3978 ,p_dup_party_tbl => l_dup_party_tbl
3979 ,x_dup_batch_id => l_dup_batch_id
3980 ,x_dup_set_id => l_dup_set_id
3981 ,x_return_status => l_return_status
3982 ,x_msg_count => l_msg_count
3983 ,x_msg_data => l_msg_data );
3984 end if;
3985 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3986 ROLLBACK to create_reln_sysbatch;
3987 RETURN;
3988 END IF;
3989
3990 hz_dup_pvt.submit_dup (
3991 p_dup_set_id => l_dup_set_id
3992 ,x_request_id => l_request_id
3993 ,x_return_status => l_return_status
3994 ,x_msg_count => l_msg_count
3995 ,x_msg_data => l_msg_data);
3996
3997 END LOOP;
3998 CLOSE get_reln_party_mp;
3999 ELSIF(p_merge_type = 'SAME_PARTY_MERGE') THEN
4000 OPEN get_reln_party_sp;
4001 LOOP
4002 FETCH get_reln_party_sp INTO l_to_party_id, l_set_count;
4003 EXIT WHEN get_reln_party_sp%NOTFOUND;
4004
4005 OPEN get_cand_party(l_to_party_id);
4006 FETCH get_cand_party INTO l_cand_party;
4007 CLOSE get_cand_party;
4008
4009 -- initialize the table in each loop
4010
4011 IF ( l_dup_party_tbl.COUNT > 0 ) THEN
4012 l_dup_party_tbl.DELETE ;
4013 END IF ;
4014
4015 l_dup_batch_rec.dup_batch_name := 'SUGG: '||l_cand_party||' - '||l_master_party||'('||p_batch_id||')';
4016 l_dup_batch_rec.match_rule_id := -1;
4017 l_dup_batch_rec.application_id := 222;
4018 l_dup_batch_rec.request_type := 'SYSTEM_GENERATED';
4019 l_dup_batch_id := NULL;
4020 l_dup_set_rec.winner_party_id := l_to_party_id;
4021 l_dup_set_rec.status := 'SYSBATCH';
4022 l_dup_set_rec.assigned_to_user_id := NULL;
4023 l_dup_set_rec.merge_type := 'PARTY_MERGE';
4024 l_dup_party_tbl(1).party_id := l_to_party_id;
4025 l_dup_party_tbl(1).score := -1;
4026 l_dup_party_tbl(1).merge_flag := NULL;
4027
4028 l_count := 2;
4029
4030 OPEN get_reln_from_party_sp(l_to_party_id);
4031 LOOP
4032 FETCH get_reln_from_party_sp INTO l_from_party_id;
4033 EXIT WHEN get_reln_from_party_sp%NOTFOUND;
4034
4035 l_dup_party_tbl(l_count).party_id := l_from_party_id;
4036 l_dup_party_tbl(l_count).score := -1;
4037 l_dup_party_tbl(l_count).merge_flag := 'Y';
4038 l_count := l_count + 1;
4039
4040 END LOOP;
4041 CLOSE get_reln_from_party_sp;
4042 if l_from_party_id <> l_to_party_id
4043 then
4044 HZ_DUP_PVT.create_dup_batch(
4045 p_dup_batch_rec => l_dup_batch_rec
4046 ,p_dup_set_rec => l_dup_set_rec
4047 ,p_dup_party_tbl => l_dup_party_tbl
4048 ,x_dup_batch_id => l_dup_batch_id
4049 ,x_dup_set_id => l_dup_set_id
4050 ,x_return_status => x_return_status
4051 ,x_msg_count => x_msg_count
4052 ,x_msg_data => x_msg_data );
4053 end if;
4054 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4055 ROLLBACK to create_reln_sysbatch;
4056 RETURN;
4057 end if;
4058
4059 hz_dup_pvt.submit_dup (
4060 p_dup_set_id => l_dup_set_id
4061 ,x_request_id => l_request_id
4062 ,x_return_status => l_return_status
4063 ,x_msg_count => l_msg_count
4064 ,x_msg_data => l_msg_data);
4065
4066 END LOOP;
4067 CLOSE get_reln_party_sp;
4068 END IF;
4069 end if; -- (NVL(FND_PROFILE.VALUE('HZ_DL_CREATE_SUGG_MERGE_REQ')
4070
4071 EXCEPTION
4072
4073 WHEN FND_API.G_EXC_ERROR THEN
4074 ROLLBACK TO create_reln_sysbatch;
4075 x_return_status := FND_API.G_RET_STS_ERROR;
4076 FND_MSG_PUB.Count_And_Get(
4077 p_encoded => FND_API.G_FALSE,
4078 p_count => x_msg_count,
4079 p_data => x_msg_data);
4080
4081 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4082 ROLLBACK TO create_reln_sysbatch;
4083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4084 FND_MSG_PUB.Count_And_Get(
4085 p_encoded => FND_API.G_FALSE,
4086 p_count => x_msg_count,
4087 p_data => x_msg_data);
4088
4089 WHEN OTHERS THEN
4090 ROLLBACK TO create_reln_sysbatch;
4091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4092 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4093 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4094 FND_MSG_PUB.ADD;
4095 FND_MSG_PUB.Count_And_Get(
4096 p_encoded => FND_API.G_FALSE,
4097 p_count => x_msg_count,
4098 p_data => x_msg_data);
4099
4100 END create_reln_sysbatch;
4101
4102 --
4103 -- PROCEDURE delete_mapping
4104 --
4105 -- DESCRIPTION
4106 -- Remove all profile attributes/address/relationship mapping.
4107 --
4108 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4109 --
4110 -- ARGUMENTS
4111 -- IN:
4112 -- p_batch_id ID of the merge batch
4113 -- p_merge_type Merge type of the dup set
4114 --
4115 -- OUT:
4116 -- x_return_status Return status after the call. The status can
4117 -- be fnd_api.g_ret_sts_success (success),
4118 -- fnd_api.g_ret_sts_error (error),
4119 -- fnd_api.g_ret_sts_unexp_error
4120 -- (unexpected error).
4121 -- x_msg_count Number of messages in message stack.
4122 -- x_msg_data Message text if x_msg_count is 1.
4123 --
4124 -- NOTES
4125 --
4126 -- MODIFICATION HISTORY
4127 --
4128 -- 10/09/2002 Arnold Ng o Created.
4129 --
4130 --
4131 PROCEDURE delete_mapping (
4132 p_batch_id IN NUMBER
4133 ,p_merge_type IN VARCHAR2
4134 ,x_return_status OUT NOCOPY VARCHAR2
4135 ,x_msg_count OUT NOCOPY NUMBER
4136 ,x_msg_data OUT NOCOPY VARCHAR2 )
4137 IS
4138
4139 l_batch_id NUMBER;
4140 l_merge_type VARCHAR2(30);
4141 l_party_type VARCHAR2(30);
4142 l_merge_to NUMBER;
4143
4144 BEGIN
4145
4146 x_return_status := FND_API.G_RET_STS_SUCCESS;
4147 fnd_msg_pub.initialize;
4148
4149 savepoint delete_mapping;
4150
4151 l_batch_id := p_batch_id;
4152 l_merge_type := p_merge_type;
4153
4154 IF(p_merge_type = 'PARTY_MERGE') THEN
4155 -- remove all attribute mapping
4156 DELETE FROM HZ_MERGE_ENTITY_ATTRIBUTES
4157 WHERE merge_batch_id = l_batch_id;
4158
4159 -- repopulate all attribute default data
4160 SELECT decode(pty.party_type,'PERSON','HZ_PERSON_PROFILES',
4161 'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
4162 'HZ_ORGANIZATION_PROFILES'),
4163 a.winner_party_id
4164 INTO l_party_type, l_merge_to
4165 FROM HZ_DUP_SETS a, HZ_PARTIES pty
4166 WHERE a.dup_set_id = p_batch_id
4167 AND a.winner_party_id = pty.party_id;
4168
4169 HZ_MERGE_ENTITY_ATTRI_PVT.create_merge_attributes(
4170 p_batch_id, l_merge_to, l_party_type,
4171 x_return_status, x_msg_count, x_msg_data);
4172
4173 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4174 ROLLBACK to delete_mapping;
4175 RETURN;
4176 END IF;
4177 END IF;
4178
4179 -- remove all party sites mapping
4180 clear_suggested_default (
4181 p_batch_id => l_batch_id
4182 ,p_entity_name => 'HZ_PARTY_SITES'
4183 ,p_merge_type => l_merge_type
4184 ,x_return_status => x_return_status
4185 ,x_msg_count => x_msg_count
4186 ,x_msg_data => x_msg_data );
4187
4188 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4189 ROLLBACK to delete_mapping;
4190 RETURN;
4191 END IF;
4192
4193 -- remove all party relationships mapping
4194 clear_suggested_default (
4195 p_batch_id => l_batch_id
4196 ,p_entity_name => 'HZ_PARTY_RELATIONSHIPS'
4197 ,p_merge_type => l_merge_type
4198 ,x_return_status => x_return_status
4199 ,x_msg_count => x_msg_count
4200 ,x_msg_data => x_msg_data );
4201
4202 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
4203 ROLLBACK to delete_mapping;
4204 RETURN;
4205 END IF;
4206
4207 EXCEPTION
4208
4209 WHEN FND_API.G_EXC_ERROR THEN
4210 ROLLBACK TO delete_mapping;
4211 x_return_status := FND_API.G_RET_STS_ERROR;
4212 FND_MSG_PUB.Count_And_Get(
4213 p_encoded => FND_API.G_FALSE,
4214 p_count => x_msg_count,
4215 p_data => x_msg_data);
4216
4217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4218 ROLLBACK TO delete_mapping;
4219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4220 FND_MSG_PUB.Count_And_Get(
4221 p_encoded => FND_API.G_FALSE,
4222 p_count => x_msg_count,
4223 p_data => x_msg_data);
4224
4225 WHEN OTHERS THEN
4226 ROLLBACK TO delete_mapping;
4227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4228 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4229 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4230 FND_MSG_PUB.ADD;
4231 FND_MSG_PUB.Count_And_Get(
4232 p_encoded => FND_API.G_FALSE,
4233 p_count => x_msg_count,
4234 p_data => x_msg_data);
4235
4236 END delete_mapping;
4237
4238 --
4239 -- PROCEDURE unmap_child_records
4240 --
4241 -- DESCRIPTION
4242 -- Unmap all child entities and make them transferred.
4243 --
4244 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
4245 --
4246 -- ARGUMENTS
4247 -- IN:
4248 -- p_merge_batch_id ID of the merge batch
4249 -- p_entity Name of the entity HZ_PARTY_SITES,
4250 -- HZ_RELATIONSHIPS etc.
4251 -- p_entity_id ID of the entity
4252 -- p_merge_type Merge type of the dup set
4253 --
4254 -- OUT:
4255 -- x_return_status Return status after the call. The status can
4256 -- be fnd_api.g_ret_sts_success (success),
4257 -- fnd_api.g_ret_sts_error (error),
4258 -- fnd_api.g_ret_sts_unexp_error
4259 -- (unexpected error).
4260 -- x_msg_count Number of messages in message stack.
4261 -- x_msg_data Message text if x_msg_count is 1.
4262 --
4263 -- NOTES
4264 --
4265 -- MODIFICATION HISTORY
4266 --
4267 -- 10/09/2002 Tasman Tang o Created.
4268 --
4269 --
4270
4271 PROCEDURE unmap_child_records(
4272 p_merge_batch_id IN NUMBER,
4273 p_entity IN VARCHAR2,
4274 p_entity_id IN NUMBER,
4275 p_merge_type IN VARCHAR2,
4276 x_return_status OUT NOCOPY VARCHAR2,
4277 x_msg_count OUT NOCOPY NUMBER,
4278 x_msg_data OUT NOCOPY VARCHAR2) IS
4279
4280 l_batch_id NUMBER;
4281 l_child_entity_id NUMBER;
4282 l_batch_party_id NUMBER;
4283 l_obj_ver_number NUMBER;
4284
4285 CURSOR c_children(cp_merge_batch_id NUMBER, cp_entity VARCHAR2, cp_entity_id NUMBER) IS
4286 SELECT mpd.merge_from_entity_id, mpd.batch_party_id, mpd.object_version_number
4287 from hz_merge_parties mp, hz_merge_party_details mpd
4288 WHERE mp.batch_id=cp_merge_batch_id
4289 AND mpd.entity_name = p_entity
4290 AND mp.batch_party_id = mpd.batch_party_id
4291 AND mpd.merge_from_entity_id <> cp_entity_id
4292 AND mpd.merge_to_entity_id = cp_entity_id;
4293
4294 BEGIN
4295
4296 fnd_msg_pub.initialize;
4297 x_return_status := FND_API.G_RET_STS_SUCCESS;
4298
4299 SAVEPOINT unmap_child_records;
4300
4301 IF (p_merge_batch_id is null) OR (p_merge_type IS NULL )
4302 OR (p_entity is null) OR (p_entity_id IS NULL) THEN
4303 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_BATCH_PARAM');
4304 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BATCH_PARTY_ID');
4305 FND_MSG_PUB.ADD;
4306 RAISE FND_API.G_EXC_ERROR;
4307 END IF;
4308
4309 OPEN c_children(p_merge_batch_id, p_entity, p_entity_id);
4310 FETCH c_children INTO l_child_entity_id, l_batch_party_id, l_obj_ver_number;
4311
4312 IF c_children%NOTFOUND THEN
4313 -- No need to unmap children if there is no child
4314 RETURN;
4315
4316 ELSE
4317 LOOP
4318 IF p_merge_type = 'PARTY_MERGE' THEN
4319 map_detail_record(
4320 l_batch_party_id,
4321 p_entity,
4322 l_child_entity_id,
4323 l_child_entity_id,
4324 l_obj_ver_number,
4325 x_return_status,
4326 x_msg_count,
4327 x_msg_data);
4328 ELSIF p_merge_type = 'SAME_PARTY_MERGE' THEN
4329 map_within_party(
4330 l_batch_party_id,
4331 p_entity,
4332 l_child_entity_id,
4333 null,
4334 x_return_status,
4335 x_msg_count,
4336 x_msg_data);
4337 END IF;
4338
4339 FETCH c_children INTO l_child_entity_id, l_batch_party_id, l_obj_ver_number;
4340 EXIT WHEN c_children%NOTFOUND;
4341 END LOOP;
4342
4343 END IF;
4344
4345 -- standard call to get message count and if count is 1, get message info.
4346 FND_MSG_PUB.Count_And_Get(
4347 p_encoded => FND_API.G_FALSE,
4348 p_count => x_msg_count,
4349 p_data => x_msg_data);
4350
4351 EXCEPTION
4352 WHEN FND_API.G_EXC_ERROR THEN
4353 ROLLBACK to unmap_child_records ;
4354 x_return_status := FND_API.G_RET_STS_ERROR;
4355 FND_MSG_PUB.Count_And_Get(
4356 p_encoded => FND_API.G_FALSE,
4357 p_count => x_msg_count,
4358 p_data => x_msg_data);
4359
4360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4361 ROLLBACK to unmap_child_records ;
4362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4363 FND_MSG_PUB.Count_And_Get(
4364 p_encoded => FND_API.G_FALSE,
4365 p_count => x_msg_count,
4366 p_data => x_msg_data);
4367
4368 WHEN OTHERS THEN
4369 ROLLBACK to unmap_child_records;
4370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4371
4372 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4373 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4374 FND_MSG_PUB.ADD;
4375
4376
4377 FND_MSG_PUB.Count_And_Get(
4378 p_encoded => FND_API.G_FALSE,
4379 p_count => x_msg_count,
4380 p_data => x_msg_data);
4381
4382 END unmap_child_records;
4383
4384 function get_party_number(p_party_id in number) return varchar2 is
4385
4386 cursor get_party_number_csr is
4387 select party_number
4388 from hz_parties
4389 where party_id = p_party_id;
4390 l_party_number varchar2(30);
4391 begin
4392 open get_party_number_csr;
4393 fetch get_party_number_csr into l_party_number;
4394 close get_party_number_csr;
4395 return l_party_number;
4396 end get_party_number;
4397
4398
4399 -- Check existing overlapped merge batch is coming from FORM UI('F') or Data Librarian UI('D')
4400 function get_merge_batch_data_source(p_merge_batch_id in number) return varchar2 is
4401
4402 cursor get_form_merge_batch_csr is
4403 /* select 'x'
4404 from hz_merge_parties mp
4405 where mp.batch_id = p_merge_batch_id
4406 and not exists ( select 'x'
4407 from hz_dup_set_parties dsp
4408 where dsp.dup_set_id = mp.batch_id
4409 and mp.batch_id = p_merge_batch_id); */
4410 -- Added new column created_by_module in hz_merge_batch instead of above checking
4411 select 'x'
4412 from hz_merge_batch
4413 where batch_id = p_merge_batch_id
4414 and nvl(created_by_module,'##') <> 'DL';
4415
4416 l_tmp varchar2(1);
4417 begin
4418 open get_form_merge_batch_csr;
4419 fetch get_form_merge_batch_csr into l_tmp;
4420 if get_form_merge_batch_csr%FOUND
4421 then
4422 close get_form_merge_batch_csr;
4423 return 'F';
4424 else
4425 close get_form_merge_batch_csr;
4426 return 'D';
4427 end if;
4428 end get_merge_batch_data_source;
4429
4430
4431 -- If it has been called from DL project, pass in p_dup_set_id
4432 -- if it has been called from party merge concurrent, pass in merge_batch_id only
4433 -- and pass in null for p_dup_set_id
4434 -- set p_reject_req_flag = 'N' if call this procedure from DL UI.
4435 procedure validate_overlapping_merge_req(
4436 p_dup_set_id IN NUMBER,
4437 p_merge_batch_id IN NUMBER,
4438 p_init_msg_list IN VARCHAR2,
4439 p_reject_req_flag IN VARCHAR2,
4440 x_return_status OUT NOCOPY VARCHAR2,
4441 x_msg_count OUT NOCOPY NUMBER,
4442 x_msg_data OUT NOCOPY VARCHAR2 ) is
4443
4444 cursor dset_overlap_merged_party_csr is
4445 select party.party_number,ds.object_version_number
4446 from hz_parties party, hz_dup_sets ds, hz_dup_set_parties dsp, hz_dup_batch db
4447 where party.party_id =dsp.dup_party_id
4448 and db.dup_batch_id = ds.dup_batch_id
4449 and ds.dup_set_id = dsp.dup_set_id
4450 and party.status = 'M'
4451 and ds.dup_set_id = p_dup_set_id;
4452
4453 cursor dset_overlap_req_party_csr is
4454 select distinct mp.batch_id,dsp.dup_party_id,ds.object_version_number
4455 from hz_merge_batch mb, hz_merge_parties mp,
4456 hz_dup_sets ds, hz_dup_set_parties dsp, hz_dup_batch db
4457 where mp.batch_id <> ds.dup_set_id
4458 and mb.batch_id = mp.batch_id
4459 and db.dup_batch_id = ds.dup_batch_id
4460 and ds.dup_set_id = dsp.dup_set_id
4461 and (dsp.dup_party_id = mp.from_party_id or dsp.dup_party_id = mp.to_party_id)
4462 and nvl(dsp.merge_flag,'Y') <> 'N'
4463 and mb.batch_status not in ('COMPLETE','PART_COMPLETE')
4464 and ds.dup_set_id = p_dup_set_id;
4465
4466 cursor batch_overlap_merged_party_csr is
4467 select distinct party.party_number
4468 from hz_parties party, hz_merge_parties mp, hz_merge_batch mb
4469 where (party.party_id = mp.from_party_id or party.party_id = mp.to_party_id)
4470 and party.status = 'M'
4471 and mp.batch_id = p_merge_batch_id
4472 and mb.batch_id = mp.batch_id
4473 and mb.batch_status not in ('COMPLETE','PART_COMPLETE'); --4114041
4474
4475 /* from DL UI merge_batch_id = dup_set_id */
4476 cursor batch_dset_obj_ver_num_csr is
4477 select object_version_number
4478 from hz_dup_sets
4479 where dup_set_id = p_merge_batch_id;
4480
4481 cursor get_merged_rel_party_csr is
4482 select mp2.batch_party_id
4483 from hz_parties p1, hz_merge_parties mp2
4484 where p1.party_id = mp2.from_party_id
4485 and p1.status = 'M'
4486 and mp2.merge_reason_code = 'DUPLICATE_RELN_PARTY'
4487 and mp2.batch_id = p_merge_batch_id;
4488
4489 l_batch_id number;
4490 l_party_id number;
4491 l_party_number varchar2(30);
4492 l_object_version_number number;
4493 l_err_reported number := 0;
4494 l_batch_party_id number;
4495
4496 begin
4497 x_return_status := FND_API.G_RET_STS_SUCCESS;
4498
4499 -- Initialize message list if p_init_msg_list is set to TRUE.
4500 IF FND_API.to_Boolean( p_init_msg_list )
4501 THEN
4502 FND_MSG_PUB.initialize;
4503 END IF;
4504
4505 if p_dup_set_id is not null -- data passed in from Data librarian(DL) UI
4506 -- or sys dup identification from FORM
4507 then
4508 open dset_overlap_merged_party_csr;
4509 loop
4510 fetch dset_overlap_merged_party_csr into l_party_number,
4511 l_object_version_number;
4512 exit when dset_overlap_merged_party_csr%NOTFOUND;
4513 if l_party_number is not null
4514 then
4515 x_return_status := FND_API.G_RET_STS_ERROR;
4516 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_PARTY_ALREADY_MERGED');
4517 FND_MESSAGE.SET_TOKEN('ID', l_party_number );
4518 FND_MSG_PUB.ADD;
4519 end if;
4520 end loop;
4521 close dset_overlap_merged_party_csr;
4522
4523 open dset_overlap_req_party_csr;
4524 loop
4525 fetch dset_overlap_req_party_csr into l_batch_id,l_party_id,l_object_version_number;
4526 exit when dset_overlap_req_party_csr%NOTFOUND;
4527 if l_batch_id is not null
4528 then
4529 l_party_number := get_party_number(l_party_id);
4530 x_return_status := FND_API.G_RET_STS_ERROR;
4531 if get_merge_batch_data_source(l_batch_id) = 'D' -- from DL UI
4532 then
4533 FND_MESSAGE.SET_NAME('AR', 'HZ_PM_MR_EXIST_DL');
4534 FND_MESSAGE.SET_TOKEN('ID', l_party_number );
4535 FND_MESSAGE.SET_TOKEN('REQUEST_ID', l_batch_id);
4536 FND_MSG_PUB.ADD;
4537 else -- from FORM UI
4538 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_MR_EXIST_FORM_WARNING');
4539 FND_MESSAGE.SET_TOKEN('ID', l_party_number );
4540 FND_MESSAGE.SET_TOKEN('BATCH_ID', l_batch_id);
4541 FND_MSG_PUB.ADD;
4542 end if;
4543 end if;
4544 end loop;
4545 close dset_overlap_req_party_csr;
4546
4547 IF (p_reject_req_flag = 'Y' and x_return_status<>FND_API.G_RET_STS_SUCCESS)
4548 THEN
4549
4550 /*
4551 HZ_DUP_PVT.reject_merge (
4552 p_dup_set_id => p_dup_set_id
4553 ,px_set_obj_version_number => l_object_version_number
4554 ,p_init_msg_list => FND_API.G_FALSE
4555 ,x_return_status => x_return_status
4556 ,x_msg_count => x_msg_count
4557 ,x_msg_data => x_msg_data);
4558 */
4559
4560 -- Update dup set status to 'Error'
4561 UPDATE HZ_DUP_SETS
4562 SET STATUS = 'ERROR',
4563 OBJECT_VERSION_NUMBER = nvl(OBJECT_VERSION_NUMBER,1)+1,
4564 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
4565 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
4566 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
4567 WHERE DUP_SET_ID = p_dup_set_id;
4568
4569 x_return_status := FND_API.G_RET_STS_ERROR;
4570 end if;
4571
4572 FND_MSG_PUB.Count_And_Get(
4573 p_encoded => FND_API.G_FALSE,
4574 p_count => x_msg_count,
4575 p_data => x_msg_data);
4576
4577 elsif p_merge_batch_id is not null -- data passed in from party merge
4578 -- concurrent program
4579 then
4580
4581 -- bug 5094383: delete merged relationship parties
4582 open get_merged_rel_party_csr;
4583 loop
4584 fetch get_merged_rel_party_csr into l_batch_party_id;
4585 exit when get_merged_rel_party_csr%NOTFOUND;
4586
4587 if l_batch_party_id is not null
4588 then
4589 delete from hz_merge_parties where batch_party_id = l_batch_party_id;
4590 delete from hz_merge_party_details where batch_party_id = l_batch_party_id;
4591
4592 end if;
4593 end loop;
4594 close get_merged_rel_party_csr;
4595
4596 open batch_overlap_merged_party_csr;
4597 loop
4598 fetch batch_overlap_merged_party_csr into l_party_number;
4599 exit when batch_overlap_merged_party_csr%NOTFOUND;
4600 if l_party_number is not null
4601 then
4602 FND_FILE.put_line(fnd_file.log,'The Party with Registry ID ' || l_party_number || ' has already been merged.');
4603
4604 if l_err_reported = 0 then
4605 x_return_status := FND_API.G_RET_STS_ERROR;
4606 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_PARTY_ALREADY_MERGED');
4607 FND_MESSAGE.SET_TOKEN('ID', l_party_number );
4608 FND_MSG_PUB.ADD;
4609 l_err_reported := 1;
4610 end if;
4611 end if;
4612 end loop;
4613 close batch_overlap_merged_party_csr;
4614
4615 end if;
4616
4617 EXCEPTION
4618 WHEN OTHERS THEN
4619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4620 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4621 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4622 FND_MSG_PUB.ADD;
4623 FND_MSG_PUB.Count_And_Get(
4624 p_encoded => FND_API.G_FALSE,
4625 p_count => x_msg_count,
4626 p_data => x_msg_data);
4627
4628 end validate_overlapping_merge_req;
4629
4630 --private function
4631 function get_addresses(p_to_site_id in number, get_from_addr_flag in varchar2) return varchar2
4632 is
4633 cursor get_to_addr_csr is
4634 select hz_format_pub.format_address(l.location_id,null,null,', ')
4635 from hz_party_sites ps, hz_locations l
4636 where ps.location_id = l.location_id
4637 and ps.party_site_id = p_to_site_id;
4638
4639 cursor get_from_addr_csr is
4640 select hz_format_pub.format_address(l.location_id,null,null,', ')
4641 from hz_merge_parties p,
4642 hz_merge_party_details pd,
4643 hz_party_sites s,
4644 hz_locations l,
4645 hz_parties hp
4646 where p.batch_party_id = pd.batch_party_id
4647 and pd.entity_name = 'HZ_PARTY_SITES'
4648 and pd.merge_from_entity_id = s.party_site_id
4649 and s.location_id = l.location_id
4650 and hp.party_id = p.from_party_id
4651 and pd.merge_to_entity_id = p_to_site_id;
4652
4653 l_addr varchar2(2000);
4654 l_to_addr varchar2(2000);
4655 l_concat_addr varchar2(2000):='';
4656
4657 begin
4658 open get_to_addr_csr;
4659 fetch get_to_addr_csr into l_to_addr;
4660 close get_to_addr_csr;
4661
4662 if get_from_addr_flag = 'N'
4663 then
4664
4665 return l_to_addr;
4666 else
4667 open get_from_addr_csr;
4668 loop
4669 fetch get_from_addr_csr into l_addr;
4670 exit when get_from_addr_csr%NOTFOUND;
4671 l_concat_addr := l_concat_addr ||'"'||l_addr||'"'||', ';
4672
4673 end loop;
4674 close get_from_addr_csr;
4675 return l_concat_addr||'"'||l_to_addr||'"';
4676 end if;
4677
4678 end get_addresses;
4679
4680 -- Only called from Data Librarian UI
4681 function is_acct_site_merge_required(p_merge_batch_id in number) return varchar2 is
4682
4683 cursor acct_site_merge_required_csr is
4684 SELECT 'Y'
4685 FROM hz_party_sites ps1,
4686 hz_cust_acct_sites_all as1,
4687 hz_cust_accounts ca1,
4688 hz_merge_parties p1,
4689 hz_merge_party_details pd1
4690 WHERE p1.batch_id = p_merge_batch_id
4691 AND ps1.party_site_id = as1.party_site_id
4692 and ca1.cust_account_id = as1.cust_account_id
4693 and p1.batch_party_id = pd1.batch_party_id
4694 and pd1.entity_name = 'HZ_PARTY_SITES'
4695 and pd1.merge_from_entity_id = ps1.party_site_id
4696 and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
4697 AND exists
4698 ( select 1 from hz_party_sites ps2,
4699 hz_cust_acct_sites_all as2,
4700 hz_merge_parties p2,
4701 hz_merge_party_details pd2
4702 where p2.batch_id = p_merge_batch_id
4703 and ps2.party_site_id = as2.party_site_id
4704 and as2.cust_account_id = as1.cust_account_id
4705 and as2.org_id = as1.org_id
4706 and p2.batch_party_id = pd2.batch_party_id
4707 and pd2.entity_name = 'HZ_PARTY_SITES'
4708 and pd2.merge_to_entity_id = ps2.party_site_id
4709 and pd2.merge_from_entity_id <> pd2.merge_to_entity_id
4710 and rownum = 1);
4711 l_required varchar2(1);
4712 begin
4713 open acct_site_merge_required_csr;
4714 fetch acct_site_merge_required_csr into l_required;
4715 close acct_site_merge_required_csr;
4716 if l_required = 'Y'
4717 then return ('Y');
4718 else return ('N');
4719 end if;
4720 end;
4721
4722 -- Only called from Data Librarian UI
4723 procedure site_merge_warning(
4724 p_merge_batch_id IN NUMBER,
4725 p_generate_note_flag IN VARCHAR2,
4726 x_return_status OUT NOCOPY VARCHAR2,
4727 x_msg_count OUT NOCOPY NUMBER,
4728 x_msg_data OUT NOCOPY VARCHAR2 ) is
4729
4730
4731 CURSOR check_site_merge_csr IS
4732 SELECT distinct ca1.account_number, pd1.merge_to_entity_id
4733 FROM hz_party_sites ps1,
4734 hz_cust_acct_sites_all as1,
4735 hz_cust_accounts ca1,
4736 hz_merge_parties p1,
4737 hz_merge_party_details pd1
4738 WHERE p1.batch_id = p_merge_batch_id
4739 AND ps1.party_site_id = as1.party_site_id
4740 and ca1.cust_account_id = as1.cust_account_id
4741 and p1.batch_party_id = pd1.batch_party_id
4742 and pd1.entity_name = 'HZ_PARTY_SITES'
4743 and pd1.merge_from_entity_id = ps1.party_site_id
4744 and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
4745 AND exists
4746 ( select 1 from hz_party_sites ps2,
4747 hz_cust_acct_sites_all as2,
4748 hz_merge_parties p2,
4749 hz_merge_party_details pd2
4750 where p2.batch_id = p_merge_batch_id
4751 and ps2.party_site_id = as2.party_site_id
4752 and as2.cust_account_id = as1.cust_account_id
4753 and as2.org_id = as1.org_id
4754 and p2.batch_party_id = pd2.batch_party_id
4755 and pd2.entity_name = 'HZ_PARTY_SITES'
4756 and pd2.merge_to_entity_id = ps2.party_site_id
4757 and pd2.merge_from_entity_id <> pd2.merge_to_entity_id
4758 and rownum = 1);
4759
4760 l_from_addr varchar2(2000) :='';
4761 l_from_id number;
4762 l_to_id number;
4763 l_account_number varchar2(30);
4764 l_note_text varchar2(2000);
4765 str varchar2(2000);
4766 l_jtf_note_id number;
4767 l_concat_addr varchar2(2000);
4768 l_to_entity_id number;
4769 l_to_addr varchar2(2000);
4770 l_msg varchar2(2000);
4771 l_messages varchar2(2000) :='';
4772
4773 begin
4774 FND_MSG_PUB.initialize; -- make sure only show warning messages
4775 x_return_status := FND_API.G_RET_STS_SUCCESS;
4776 open check_site_merge_csr;
4777 loop
4778 fetch check_site_merge_csr into l_account_number, l_to_entity_id;
4779 exit when check_site_merge_csr%NOTFOUND;
4780 if l_account_number is not null
4781 then
4782 if p_generate_note_flag = 'N'
4783 then
4784 l_concat_addr := get_addresses(l_to_entity_id,'Y');
4785
4786 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_ACCTSITEDUP_INFO');
4787 FND_MESSAGE.SET_TOKEN('ACCTNUM', l_account_number);
4788 FND_MESSAGE.SET_TOKEN('CONCAT_DUP_ADDR', l_concat_addr);
4789 FND_MSG_PUB.ADD;
4790
4791 elsif p_generate_note_flag = 'Y'
4792 then
4793 l_to_addr := get_addresses(l_to_entity_id,'N');
4794
4795 FND_MSG_PUB.initialize;-- only want the current message
4796 FND_MESSAGE.SET_NAME('AR', 'HZ_DL_ACCTSITEDUP_NOTE');
4797 FND_MESSAGE.SET_TOKEN('ACCTNUM', l_account_number);
4798 FND_MESSAGE.SET_TOKEN('MERGETO_ADDR', l_to_addr);
4799 FND_MSG_PUB.ADD;
4800
4801 l_note_text := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
4802
4803 str := 'BEGIN '||
4804 'JTF_NOTES_PUB.Create_note(null,null,1,FND_API.g_false,FND_API.g_true,100,'
4805 ||':x_return_status,:x_msg_count,:x_msg_data,'||'null,'||':p_merge_batch_id,'
4806 ||'''MERGE_DUP_SET'''||','||':l_note_text,'
4807 ||'null,''I'',0,SYSDATE,'||':l_jtf_note_id'||',SYSDATE,0,SYSDATE,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,'||'''GENERAL'''||');'||'END;';
4808 --fnd_file.put_line(fnd_file.log, str);
4809 EXECUTE IMMEDIATE str USING
4810 out x_return_status,out x_msg_count, out x_msg_data,p_merge_batch_id,
4811 l_note_text, out l_jtf_note_id;
4812
4813 end if;
4814 end if;
4815 end loop;
4816 close check_site_merge_csr;
4817
4818 if p_generate_note_flag = 'N'
4819 then
4820 FND_MSG_PUB.Count_And_Get(
4821 p_encoded => FND_API.G_FALSE,
4822 p_count => x_msg_count,
4823 p_data => x_msg_data);
4824
4825 if x_msg_count = 1
4826 then
4827 l_messages := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
4828 else
4829 FOR l_index IN 1..x_msg_count LOOP
4830 l_msg := FND_MSG_PUB.Get(
4831 p_msg_index => l_index,
4832 p_encoded => FND_API.G_FALSE);
4833 --l_messages:=l_messages||fnd_global.local_chr(10)||l_msg||fnd_global.local_chr(10);
4834 l_messages:=l_messages||' <br> '||l_msg||' <br> ';
4835 END LOOP;
4836 end if;
4837 x_msg_data := ' <html> '||l_messages||' </html> ';
4838 --fnd_file.put_line(fnd_file.log, x_msg_data);
4839 end if;
4840
4841 EXCEPTION
4842 WHEN OTHERS THEN
4843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4844 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4845 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4846 FND_MSG_PUB.ADD;
4847 FND_MSG_PUB.Count_And_Get(
4848 p_encoded => FND_API.G_FALSE,
4849 p_count => x_msg_count,
4850 p_data => x_msg_data);
4851
4852 end site_merge_warning;
4853
4854
4855 END HZ_MERGE_DUP_PVT;