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