[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_MERGE
Source
1 PACKAGE BODY HZ_PARTY_MERGE AS
2 /* $Header: ARHPMERB.pls 120.69.12020000.2 2013/03/27 07:24:13 vsegu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_PARTY_MERGE';
5 G_TCA_APP_ID CONSTANT VARCHAR2(30) := '222';
6 G_REQUEST_TYPE VARCHAR2(30);
7
8 /*******************Private Procedures forward declarations ********/
9 PROCEDURE do_merge(
10 p_batch_party_id IN NUMBER,
11 p_entity_name IN VARCHAR2,
12 p_par_entity_name IN VARCHAR2,
13 p_from_id IN NUMBER,
14 p_to_id IN OUT NOCOPY NUMBER,
15 p_par_from_id IN NUMBER,
16 p_par_to_id IN NUMBER,
17 p_rule_set_name IN VARCHAR2,
18 p_batch_id IN NUMBER,
19 p_batch_commit IN VARCHAR2,
20 p_preview IN VARCHAR2,
21 p_dict_id IN NUMBER,
22 p_log_padding IN VARCHAR2,
23 x_error_msg IN OUT NOCOPY VARCHAR2,
24 x_return_status IN OUT NOCOPY VARCHAR2);
25
26 PROCEDURE exec_merge_r(
27 p_entity_name IN VARCHAR2,
28 p_proc_name IN HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE,
29 p_from_id IN ROWID,
30 x_to_id IN OUT NOCOPY ROWID,
31 p_par_from_id IN NUMBER,
32 p_par_to_id IN NUMBER,
33 p_parent_entity IN HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE,
34 p_batch_id IN NUMBER,
35 p_batch_party_id IN NUMBER,
36 x_return_status IN OUT NOCOPY VARCHAR2);
37
38
39 PROCEDURE exec_merge(
40 p_entity_name IN VARCHAR2,
41 p_proc_name IN HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE,
42 p_from_id IN NUMBER,
43 x_to_id IN OUT NOCOPY NUMBER,
44 p_par_from_id IN NUMBER,
45 p_par_to_id IN NUMBER,
46 p_parent_entity IN HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE,
47 p_batch_id IN NUMBER,
48 p_batch_party_id IN NUMBER,
49 x_return_status IN OUT NOCOPY VARCHAR2);
50
51 PROCEDURE lock_batch(
52 p_batch_id IN VARCHAR2,
53 x_return_status IN OUT NOCOPY VARCHAR2);
54
55 PROCEDURE lock_records(
56 p_entity_name IN VARCHAR2,
57 p_pk_column_name IN VARCHAR2,
58 p_fk_column_name IN VARCHAR2,
59 p_join_str IN VARCHAR2,
60 p_join_clause IN VARCHAR2,
61 p_rule_set_name IN VARCHAR2,
62 x_return_status IN OUT NOCOPY VARCHAR2);
63
64 PROCEDURE delete_merged_records(
65 p_batch_party_id IN NUMBER,
66 x_return_status IN OUT NOCOPY VARCHAR2);
67
68
69 FUNCTION get_record_desc(
70 p_record_pk IN NUMBER,
71 p_entity_name IN VARCHAR2,
72 p_pk_col_name IN VARCHAR2,
73 p_desc_col_name IN VARCHAR2,
74 x_return_status IN OUT NOCOPY VARCHAR2)
75 RETURN VARCHAR2;
76
77 FUNCTION get_record_desc_r(
78 p_record_pk IN ROWID,
79 p_entity_name IN VARCHAR2,
80 p_desc_col_name IN VARCHAR2,
81 x_return_status IN OUT NOCOPY VARCHAR2)
82 RETURN VARCHAR2;
83
84 PROCEDURE setup_dnb_data(
85 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
86 x_to_id IN OUT NOCOPY NUMBER,
87 p_batch_party_id IN NUMBER
88 );
89
90 PROCEDURE insert_party_site_details (
91 p_from_party_id IN NUMBER,
92 p_to_party_id IN NUMBER,
93 p_batch_party_id IN NUMBER
94 );
95
96 PROCEDURE do_same_party_merge (
97 p_batch_party_id IN NUMBER,
98 p_entity_name IN VARCHAR2,
99 p_from_id IN NUMBER,
100 p_to_id IN OUT NOCOPY NUMBER,
101 p_rule_set_name IN VARCHAR2,
102 p_batch_id IN NUMBER,
103 p_batch_commit IN VARCHAR2,
104 p_preview IN VARCHAR2,
105 p_log_padding IN VARCHAR2,
106 x_error_msg IN OUT NOCOPY VARCHAR2,
107 x_return_status IN OUT NOCOPY VARCHAR2);
108
109
110 PROCEDURE out(
111 message IN VARCHAR2,
112 newline IN BOOLEAN DEFAULT TRUE);
113
114 PROCEDURE log(
115 message IN VARCHAR2,
116 newline IN BOOLEAN DEFAULT TRUE);
117
118
119 PROCEDURE outandlog(
120 message IN VARCHAR2,
121 newline IN BOOLEAN DEFAULT TRUE);
122
123 PROCEDURE pre_merge(
124 p_to_party_id IN NUMBER,
125 p_batch_id IN NUMBER);
126
127 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
128
129 /*FUNCTION get_col_type(
130 p_table VARCHAR2,
131 p_column VARCHAR2,
132 p_app_name VARCHAR2)
133 RETURN VARCHAR2;*/
134
135 FUNCTION alternate_get_col_type(
136 p_table VARCHAR2,
137 p_column VARCHAR2)
138 RETURN VARCHAR2;
139
140 --bug 4634891
141 PROCEDURE exec_merge(
142 p_entity_name IN VARCHAR2,
143 p_proc_name IN VARCHAR2,
144 p_batch_id IN NUMBER,
145 p_request_id IN NUMBER,
146 x_return_status IN OUT NOCOPY VARCHAR2);
147
148 PROCEDURE check_int_ext_party_type(
149 p_dup_set_id IN NUMBER,
150 p_int_party OUT NOCOPY VARCHAR2,
151 p_ext_party OUT NOCOPY VARCHAR2,
152 p_merge_ok OUT NOCOPY VARCHAR2);
153
154 -------------Global Variables and lists--------------------
155 g_request_id HZ_MERGE_PARTY_HISTORY.request_id%TYPE;
156 g_user_id HZ_MERGE_PARTY_HISTORY.last_updated_by%TYPE;
157 g_created_by HZ_MERGE_PARTY_HISTORY.created_by%TYPE;
158 g_last_update_login HZ_MERGE_PARTY_HISTORY.last_update_login%TYPE;
159 g_creation_date DATE;
160 g_last_update_date DATE;
161
162 g_merge_delete_flag VARCHAR2(1);
163 g_cur_merge_dict_id NUMBER := 0;
164 g_num_sub_entities NUMBER :=-1;
165 g_cur_proc_name VARCHAR2(255);
166 G_PROC_CURSOR INTEGER;
167
168 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
169 TYPE CharList IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
170 TYPE ErrorList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
171
172 H_batch_party_id NumberList;
173 H_from_id NumberList;
174 H_to_id NumberList;
175 H_from_fk_id NumberList;
176 H_to_fk_id NumberList;
177
178 /**** Bug 2541514
179 H_from_desc CharList;
180 H_to_desc CharList;
181 ****/
182 H_from_desc ErrorList;
183 H_to_desc ErrorList;
184
185 H_merge_dict_id NumberList;
186 H_op_type CharList;
187
188 I_batch_party_id NumberList;
189 I_from_id NumberList;
190 I_to_id NumberList;
191 I_from_fk_id NumberList;
192 I_to_fk_id NumberList;
193
194 /**** Bug 2541514
195 I_from_desc CharList;
196 I_to_desc CharList;
197 ****/
198 I_from_desc ErrorList;
199 I_to_desc ErrorList;
200
201 I_merge_dict_id NumberList;
202 I_op_type CharList;
203 I_Error ErrorList;
204
205 H_Counter NUMBER := 0;
206 I_Counter NUMBER := 0;
207
208 g_crm_app_list NumberList;
209 g_skip_dict_id NumberList;
210
211 g_inv_merge_dict VARCHAR2(4000);
212 g_inv_merge_dict_cnt NUMBER;
213
214 --------------------Public procedures body---------------------------
215
216 PROCEDURE init_globals IS
217 --4534175
218 l_count number;
219 TYPE ref_cur IS REF CURSOR;
220 c1 ref_cur;
221 --4534175
222 l_count1 number;
223 BEGIN
224
225 g_request_id :=hz_utility_pub.request_id;
226 g_created_by :=hz_utility_pub.created_by;
227 g_creation_date :=hz_utility_pub.creation_date;
228 g_last_update_login:=hz_utility_pub.last_update_login;
229 g_last_update_date :=hz_utility_pub.last_update_date;
230 g_user_id :=hz_utility_pub.user_id;
231
232 FND_FILE.put_line(FND_FILE.log,'Request ID:'||g_request_id||'#');
233 FND_FILE.put_line(FND_FILE.log,'User ID:'||g_user_id||'#');
234 FND_FILE.put_line(FND_FILE.log,'Creation Date:'||TO_CHAR(g_creation_date)||'#');
235 FND_FILE.put_line(FND_FILE.log,'Last Update Date:'||TO_CHAR(g_last_update_date)||'#');
236 FND_FILE.put_line(FND_FILE.log,'Last Update Login:'||g_last_update_login||'#');
237 FND_FILE.put_line(FND_FILE.log,'Created By:'||g_created_by||'#');
238 FND_FILE.put_line(FND_FILE.log,'Request ID:'||g_request_id||'#');
239 g_skip_dict_id.DELETE;
240 FOR ENTITY IN (
241 SELECT DISTINCT MERGE_DICT_ID, ENTITY_NAME
242 FROM HZ_MERGE_DICTIONARY WHERE DICT_APPLICATION_ID<>222
243 AND NVL(batch_merge_flag, 'N') <> 'Y') LOOP
244
245 BEGIN
246 --4534175 EXECUTE IMMEDIATE 'DECLARE x NUMBER; BEGIN SELECT 1 INTO x FROM dual where exists ( select 1 from '||ENTITY.ENTITY_NAME||'); END;';
247 l_count := 0;
248
249 IF ENTITY.ENTITY_NAME = 'JTF_FM_CONTENT_HISTORY_V' THEN
250
251 l_count1 := 0;
252 OPEN c1 FOR 'SELECT 1 FROM jtf_fm_content_history WHERE rownum = 1';
253 FETCH c1 into l_count;
254 CLOSE c1;
255
256 OPEN c1 FOR 'SELECT 1 FROM jtf_fm_processed WHERE rownum = 1';
257 FETCH c1 into l_count1;
258 CLOSE c1;
259
260 IF l_count = 0 OR l_count1 = 0 THEN
261 g_skip_dict_id(ENTITY.MERGE_DICT_ID):=1;
262 END IF;
263
264 --Modified for bug 8370389
265 ELSIF ENTITY.ENTITY_NAME = 'WSH_SUPPLIER_SF_SITES_V' THEN
266 l_count := 0;
267 OPEN c1 FOR 'select 1
268 from dual
269 where exists(
270 SELECT /*+ first_rows(1) index_ffs(hp HZ_PARTY_SITE_USES_N1)*/ 1
271 FROM hz_party_site_uses hp
272 WHERE site_use_type = ''SUPPLIER_SHIP_FROM''
273 AND ROWNUM=1) ';
274 FETCH c1 into l_count;
275 CLOSE c1;
276
277 IF l_count = 0 THEN
278 g_skip_dict_id(ENTITY.MERGE_DICT_ID):=1;
279 END IF;
280 --End of modifications for bug 8370389
281
282 ELSE
283
284 OPEN c1 FOR 'SELECT 1 FROM '||ENTITY.ENTITY_NAME||' WHERE rownum = 1';
285 FETCH c1 into l_count;
286 CLOSE c1;
287 IF l_count = 0 THEN
288 g_skip_dict_id(ENTITY.MERGE_DICT_ID):=1;
289 END IF;
290 END IF; --ENTITY_NAME
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 g_skip_dict_id(ENTITY.MERGE_DICT_ID):=1;
294 WHEN OTHERS THEN /*bug 3754365*/
295 NULL;
296 END;
297 END LOOP;
298 END;
299
300
301 PROCEDURE batch_merge(
302 errbuf OUT NOCOPY VARCHAR2,
303 retcode OUT NOCOPY VARCHAR2,
304 p_batch_id IN VARCHAR2,
305 p_preview IN VARCHAR2
306 ) IS
307
308 CURSOR c_batch(cp_batch_id NUMBER) IS
309 SELECT batch_name, rule_set_name, batch_status, batch_delete, batch_commit
310 FROM HZ_MERGE_BATCH
311 WHERE batch_id = cp_batch_id;
312
313 -----Cursor for grouping parties based on distinct merge_to party-----
314 CURSOR c_pre_merge(cp_batch_id NUMBER) IS
315 SELECT DISTINCT(to_party_id)
316 FROM HZ_MERGE_PARTIES mp, hz_parties p
317 WHERE batch_id = cp_batch_id
318 AND p.party_id = mp.from_party_id
319 AND p.party_type <> 'PARTY_RELATIONSHIP';
320
321 CURSOR c_pre_merge_type(cp_batch_id NUMBER, cp_to_party_id NUMBER) IS
322 SELECT mp.merge_type
323 FROM HZ_MERGE_PARTIES mp, hz_parties p
324 WHERE mp.batch_id = cp_batch_id
325 AND mp.to_party_id = cp_to_party_id
326 AND p.party_id = mp.to_party_id
327 AND p.party_type <> 'PARTY_RELATIONSHIP'
328 AND mp.merge_type = 'PARTY_MERGE'
329 AND rownum=1;
330
331 -----Cursor to get merge from-merge to pair parties and the type-----
332 CURSOR c_batch_details(cp_batch_id NUMBER,cp_to_party_id IN NUMBER) IS
333 SELECT batch_party_id, merge_type, from_party_id, to_party_id, merge_status,party_type,
334 decode(op.actual_content_source, 'DNB', 'DNB', NULL), merge_reason_code
335 FROM HZ_MERGE_PARTIES mp, HZ_PARTIES pt, hz_organization_profiles op
336 WHERE batch_id = cp_batch_id
337 AND pt.party_id = mp.from_party_id
338 AND op.party_id(+) = pt.party_id
339 AND op.actual_content_source(+) = 'DNB'
340 AND op.effective_end_date IS NULL
341 AND ( mp.to_party_id = cp_to_party_id
342 OR (pt.party_type = 'PARTY_RELATIONSHIP'
343 AND exists (
344 select 1 FROM HZ_RELATIONSHIPS r
345 WHERE (r.party_id = mp.to_party_id or r.party_id = mp.from_party_id)
346 AND r.OBJECT_ID IN (SELECT from_party_id
347 FROM hz_merge_parties
348 WHERE batch_id = cp_batch_id AND to_party_id = cp_to_party_id))) )
349 ORDER BY decode(pt.party_type, 'PARTY_RELATIONSHIP',1,
350 decode(mp.merge_type, 'PARTY_MERGE',2,
351 'SAME_PARTY_MERGE',3,4)), 7, op.last_update_date desc; --5000614
352
353
354 -----Cursor for the merge of party sites within the same party-----
355 CURSOR c_batch_party_sites(cp_batch_party_id NUMBER) IS
356 SELECT merge_from_entity_id, merge_to_entity_id
357 FROM HZ_MERGE_PARTY_DETAILS
358 WHERE batch_party_id = cp_batch_party_id
359 AND ENTITY_NAME = 'HZ_PARTY_SITES'
360 AND merge_from_entity_id <> merge_to_entity_id;
361
362 -----Cursor for the merge of Contact Points within the same party-----
363 CURSOR c_batch_contact_points(cp_batch_party_id NUMBER) IS
364 SELECT merge_from_entity_id, merge_to_entity_id
365 FROM HZ_MERGE_PARTY_DETAILS
366 WHERE batch_party_id = cp_batch_party_id
367 AND ENTITY_NAME = 'HZ_CONTACT_POINTS'
368 AND merge_from_entity_id <> merge_to_entity_id;
369
370 -----Cursor for the merge of Relationships/Contacts within the same party-----
371 CURSOR c_batch_relationships(cp_batch_party_id NUMBER) IS
372 SELECT merge_from_entity_id, merge_to_entity_id
373 FROM HZ_MERGE_PARTY_DETAILS
374 WHERE batch_party_id = cp_batch_party_id
375 AND ENTITY_NAME = 'HZ_PARTY_RELATIONSHIPS'
376 AND merge_from_entity_id <> merge_to_entity_id;
377
378
379 CURSOR c_dict_id(cp_ruleset_name VARCHAR2, cp_entity_name VARCHAR2) IS
380 SELECT merge_dict_id
381 FROM HZ_MERGE_DICTIONARY
382 WHERE RULE_SET_NAME = cp_ruleset_name
383 AND ENTITY_NAME = cp_entity_name;
384
385 cursor c_request_type(cp_batch_id NUMBER) is
386 select dbat.request_type
387 from
388 HZ_DUP_BATCH dbat,
389 HZ_DUP_SETS dset,
390 HZ_MERGE_BATCH mb
391 where
392 dbat.dup_batch_id = dset.dup_batch_id
393 and mb.batch_id = dset.dup_set_id
394 and mb.batch_id = cp_batch_id;
395
396 cursor c_dict_no_fktype is
397 select entity_name, fk_column_name, merge_dict_id, dict_application_id
398 from hz_merge_dictionary
399 where fk_data_type is null;
400
401 cursor app_name(app_id NUMBER) IS
402 Select application_short_name from fnd_application where application_id=app_id;
403
404 cursor get_batch_party_id_csr is
405 select batch_party_id
406 from hz_merge_parties
407 where batch_id = p_batch_id
408 and rownum=1;
409
410 --start bug 4634891
411
412 CURSOR batch_merge_procedures IS
413 select merge_dict_id, entity_name, procedure_name
414 from hz_merge_dictionary
415 where merge_dict_id in (
416 select min(merge_dict_id)
417 from HZ_MERGE_DICTIONARY where batch_merge_flag = 'Y'
418 group by procedure_name);
419
420 CURSOR merge_to_parties IS
421 select DISTINCT(to_party_id)
422 from HZ_MERGE_PARTIES mp
423 where batch_id = p_batch_id
424 and merge_status = 'DONE';
425
426
427 l_proc_name HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE;
428 --end bug 4634891
429
430 l_batch_name HZ_MERGE_BATCH.BATCH_NAME%TYPE;
431 l_rule_set_name HZ_MERGE_BATCH.RULE_SET_NAME%TYPE;
432 l_batch_status HZ_MERGE_BATCH.BATCH_STATUS%TYPE;
433 l_merge_status HZ_MERGE_PARTIES.MERGE_STATUS%TYPE;
434
435 l_batch_commit HZ_MERGE_BATCH.BATCH_COMMIT%TYPE;
436 l_batch_delete HZ_MERGE_BATCH.BATCH_DELETE%TYPE;
437
438 l_pre_merge_to_party_id HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE;
439 l_pre_merge_type HZ_MERGE_PARTIES.MERGE_TYPE%TYPE;
440
441 l_batch_party_id HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE;
442 l_merge_type HZ_MERGE_PARTIES.MERGE_TYPE%TYPE;
443
444 l_from_party_id HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE;
445 l_to_party_id HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE;
446
447 l_from_site_id HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE;
448 l_to_site_id HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE;
449
450 l_from_cp_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
451 l_to_cp_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
452
453 l_from_rel_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
454 l_to_rel_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
455
456 l_sub_entity_name HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE;
457 l_sub_fk_column_name HZ_MERGE_DICTIONARY.FK_COLUMN_NAME%TYPE;
458
459
460 l_num_merged NUMBER;
461
462 l_glob_return_status VARCHAR2(200);
463 l_return_status VARCHAR2(200);
464
465 l_dict_id NUMBER;
466 l_merge_dict_id NUMBER;
467 l_batch_id NUMBER;
468 l_error_msg VARCHAR2(2000);
469
470 l_dict_app_id NUMBER;
471 l_app_id NUMBER;
472 error VARCHAR2(2000);
473
474 l_mb_spt BOOLEAN := FALSE;
475 l_mr_spt BOOLEAN := FALSE;
476
477 --Bug No: 3267877--
478 l_batch_merge_spt BOOLEAN := FALSE;
479 --End of Bug No: 3267877--
480
481 l_from_rel_party_id NUMBER;
482 l_to_rel_party_id NUMBER;
483 l_tmp NUMBER;
484 l_party_type VARCHAR2(255);
485
486 l_ret_status VARCHAR2(1);
487 l_msg_count NUMBER;
488 l_msg_data VARCHAR2(2000);
489 l_source_if_dnb VARCHAR2(2000);
490
491 l_data_type VARCHAR2(255);
492 l_app_name VARCHAR2(100);
493 l_dss_orig_prof_val varchar2(30);
494 l_dss_update_flag varchar2(1);
495 l_batch_pid HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE;
496 --4230396
497 l_key VARCHAR2(240);
498 l_list WF_PARAMETER_LIST_T;
499 --4230396
500 l_int_party VARCHAR2(4000);
501 l_ext_party VARCHAR2(4000);
502 l_ret_merge_ok VARCHAR2(1);
503
504 BEGIN
505 g_inv_merge_dict := null;
506 g_inv_merge_dict_cnt := 0;
507
508 I_Counter := 0;
509 H_Counter := 0;
510
511 retcode := 0;
512
513
514 outandlog('Starting Concurrent Program ''Batch Party Merge''');
515 outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
516 outandlog('NEWLINE');
517
518 outandlog('*********** Processing Merge Batch. ID: '||p_batch_id);
519
520 ---Bug 2440553 Savepoint for the start of batch_merge
521 --SAVEPOINT batch_merge; commented out and moved to after commit for bug 3267877.
522
523 FND_MSG_PUB.initialize;
524 init_globals;
525
526 --Initialize API return status to success.
527 l_glob_return_status := FND_API.G_RET_STS_SUCCESS;
528 l_return_status := FND_API.G_RET_STS_SUCCESS;
529
530
531 open get_batch_party_id_csr;
532 fetch get_batch_party_id_csr into l_batch_pid;
533 close get_batch_party_id_csr;
534 --log('l batch party id'|| l_batch_pid);
535
536 -- If batch not found error out
537 IF (p_batch_id IS NOT NULL) THEN
538 l_batch_id := TO_NUMBER(p_batch_id);
539 ELSE
540 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
541 FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
542 FND_MSG_PUB.ADD;
543 RAISE FND_API.G_EXC_ERROR;
544 END IF;
545
546 -- Open the batch cursor and fetch batch details
547 OPEN c_batch(l_batch_id);
548 FETCH c_batch INTO l_batch_name, l_rule_set_name, l_batch_status,
549 l_batch_delete, l_batch_commit;
550 IF (c_batch%NOTFOUND) THEN
551 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
552 FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
553 FND_MSG_PUB.ADD;
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556
557 -- If batch already complete error out
558 IF (l_batch_status = 'COMPLETE') THEN
559 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_COMPLETE');
560 FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
561 FND_MSG_PUB.ADD;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564 CLOSE c_batch;
565
566 -- bug 4865280 :check for internal external party types. Veto merge if both party types are present in batch
567 check_int_ext_party_type(
568 p_dup_set_id => l_batch_id,
569 p_int_party => l_int_party,
570 p_ext_party => l_ext_party,
571 p_merge_ok => l_ret_merge_ok);
572
573 IF (l_ret_merge_ok = 'N') THEN
574 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_INTERNAL_PARTY_IND');
575 FND_MESSAGE.SET_TOKEN('PARTY_INT', l_int_party);
576 FND_MESSAGE.SET_TOKEN('PARTY_EXT', l_ext_party);
577 FND_MSG_PUB.ADD;
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580
581 HZ_MERGE_DUP_PVT.validate_overlapping_merge_req(
582 p_dup_set_id => null,
583 p_merge_batch_id => l_batch_id,
584 p_init_msg_list => FND_API.G_FALSE,
585 p_reject_req_flag => 'Y',
586 x_return_status => l_ret_status,
587 x_msg_count => l_msg_count,
588 x_msg_data => l_msg_data);
589
590 /* error messages have been pushed into message stack in above procedure */
591 IF l_ret_status = 'E' THEN
592 RAISE FND_API.G_EXC_ERROR;
593 ELSIF l_ret_status = 'U' THEN
594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
595 END IF;
596
597 -- check party merge DSS - update party privilege.
598 hz_dup_pvt.party_merge_dss_check(p_merge_batch_id => l_batch_id,
599 x_dss_update_flag => l_dss_update_flag,
600 x_return_status => l_ret_status,
601 x_msg_count => l_msg_count,
602 x_msg_data => l_msg_data);
603
604 /* error messages have been pushed into message stack in above procedure */
605 IF l_ret_status = 'E' THEN
606 RAISE FND_API.G_EXC_ERROR;
607 ELSIF l_ret_status = 'U' THEN
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END IF;
610
611 -- Disable DSS in party merge for sub entities. Will enabled it at the end of the merge.
612 l_dss_orig_prof_val := NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N');
613 if l_dss_orig_prof_val = 'Y'
614 then
615 fnd_profile.put('HZ_DSS_ENABLED','N');
616 end if;
617
618 open c_dict_no_fktype;
619 loop
620 fetch c_dict_no_fktype into l_sub_entity_name, l_sub_fk_column_name,
621 l_merge_dict_id, l_app_id;
622 EXIT WHEN c_dict_no_fktype%NOTFOUND;
623
624 open app_name(l_app_id);
625 fetch app_name into l_app_name;
626 close app_name;
627
628 l_data_type:=get_col_type(l_sub_entity_name,l_sub_fk_column_name,l_app_name);
629
630 update hz_merge_dictionary
631 set fk_data_type = l_data_type
632 where merge_dict_id = l_merge_dict_id;
633
634 end loop;
635 close c_dict_no_fktype;
636
637 open c_request_type(l_batch_id);
638 fetch c_request_type into G_REQUEST_TYPE;
639 close c_request_type;
640
641 -- Log messages to out and log files
642 outandlog('Batch Name: '||l_batch_name);
643 outandlog('Request Type: ' || G_REQUEST_TYPE);
644 outandlog('Ruleset: '||l_rule_set_name);
645 outandlog('NEWLINE');
646
647 hz_common_pub.disable_cont_source_security;
648
649
650 -- Stamp concurrent request id to batch
651 UPDATE HZ_MERGE_BATCH
652 SET REQUEST_ID = hz_utility_pub.request_id
653 WHERE batch_id = p_batch_id;
654
655 -- If not preview mode update batch status to COMPLETE
656 IF p_preview <> 'Y' then
657 -- Update the merge batch status to COMPLETE
658 UPDATE HZ_MERGE_BATCH
659 SET BATCH_STATUS = 'SUBMITTED'
660 WHERE BATCH_ID = p_batch_id;
661
662 BEGIN
663 UPDATE HZ_DUP_SETS
664 SET STATUS = 'SUBMITTED'
665 WHERE dup_set_id = p_batch_id;
666 EXCEPTION
667 WHEN NO_DATA_FOUND THEN
668 NULL;
669 END;
670
671 END IF;
672
673 COMMIT;
674 --Bug No: 3267877
675 SAVEPOINT batch_merge;
676 l_batch_merge_spt:=TRUE;
677
678 -- Lock HZ_MERGE_BATCH, HZ_MERGE_PARTIES and HZ_MERGE_PARTY_DETAILS records
679 log ('.... Locking batch for execution');
680 lock_batch(p_batch_id, l_return_status);
681 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
682 ROLLBACK to batch_merge;
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685
686
687 -----Pre-Merge for all the distict merge_to parties where the parties are
688 -----not present on the to side and something else is also getting merged
689 -----to it.
690
691 g_automerge_flag := 'N';
692 BEGIN
693 SELECT NVL(automerge_flag, 'N') INTO g_automerge_flag
694 FROM hz_dup_sets ds, hz_dup_batch db
695 WHERE ds.dup_set_id = l_batch_id
696 AND db.dup_batch_id = ds.dup_batch_id
697 AND rownum = 1;
698 EXCEPTION
699 WHEN OTHERS THEN
700 g_automerge_flag := 'N';
701 END;
702
703
704 OPEN c_pre_merge(l_batch_id);
705 LOOP
706 FETCH c_pre_merge INTO l_pre_merge_to_party_id;
707 EXIT WHEN c_pre_merge%NOTFOUND;
708
709 open c_pre_merge_type(l_batch_id, l_pre_merge_to_party_id);
710 fetch c_pre_merge_type into l_pre_merge_type;
711 close c_pre_merge_type;
712
713 if l_pre_merge_type is null
714 then
715 l_pre_merge_type := 'SAME_PARTY_MERGE';
716 end if;
717
718 -- Save point for the start of the batch
719 SAVEPOINT merge_group;
720 l_mb_spt := TRUE;
721
722 -----Call Pre-Merge for across parties type merge
723 if l_pre_merge_type <> 'SAME_PARTY_MERGE' then
724 pre_merge(p_to_party_id => l_pre_merge_to_party_id,
725 p_batch_id => l_batch_id);
726 end if;
727 ------Loop through org in batch and setup DNB data for merge.
728 OPEN c_batch_details(l_batch_id, l_pre_merge_to_party_id);
729 LOOP
730 -- Fetch the merge party details
731 FETCH c_batch_details INTO l_batch_party_id, l_merge_type, l_from_party_id,
732 l_to_party_id, l_merge_status,l_party_type,l_source_if_dnb, g_merge_reason_code;
733 EXIT WHEN c_batch_details%NOTFOUND;
734
735 IF l_party_type = 'ORGANIZATION' AND l_merge_type = 'PARTY_MERGE' THEN -- Bug 3313609
736 setup_dnb_data(
737 l_from_party_id,l_to_party_id,l_batch_party_id);
738 END IF;
739 END LOOP;
740 CLOSE c_batch_details;
741
742 ----- Loop through each pre merge party and perform the merge
743 OPEN c_batch_details(l_batch_id, l_pre_merge_to_party_id);
744 LOOP
745 FND_MSG_PUB.initialize;
746
747 -- Initialize return status and error buffer
748 l_return_status := FND_API.G_RET_STS_SUCCESS;
749 l_error_msg := '';
750 g_merge_reason_code := NULL;
751
752 -- Fetch the merge party details
753 FETCH c_batch_details INTO l_batch_party_id, l_merge_type, l_from_party_id,
754 l_to_party_id, l_merge_status,l_party_type,l_source_if_dnb, g_merge_reason_code;
755 EXIT WHEN c_batch_details%NOTFOUND;
756
757 -- If this party has not already been merge proceed with merge
758 IF l_merge_status <> 'DONE' THEN
759 g_merge_delete_flag := l_batch_delete;
760
761 -- Check type of merge
762 IF l_merge_type = 'PARTY_MERGE' THEN
763 outandlog('.... Merging Parties: From Party ID='||l_from_party_id ||
764 ',To Party ID='||l_to_party_id);
765
766 -- Fetch the dictionary id for the HZ_PARTIES entity
767 OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
768 FETCH c_dict_id INTO l_dict_id;
769 IF c_dict_id%NOTFOUND THEN
770 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
771 FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
772 FND_MSG_PUB.ADD;
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775 CLOSE c_dict_id;
776
777 log('');
778 log('Parties');
779 g_cur_merge_dict_id := 0;
780 g_num_sub_entities :=-1;
781
782 --4307667
783 IF (HZ_PARTY_USG_ASSIGNMENT_PVT.allow_party_merge('T',l_from_party_id,l_to_party_id,l_msg_count,l_error_msg) <> 'Y') THEN
784 l_return_status := FND_API.G_RET_STS_ERROR;
785 raise FND_API.G_EXC_ERROR;
786 END IF;
787
788 -- Call the recursive merge procedure performing this merge
789 do_merge(p_batch_party_id =>l_batch_party_id,
790 p_entity_name =>'HZ_PARTIES',
791 p_par_entity_name =>NULL,
792 p_from_id =>l_from_party_id,
793 p_to_id =>l_to_party_id,
794 p_par_from_id =>NULL,
795 p_par_to_id =>NULL,
796 p_rule_set_name =>l_rule_set_name,
797 p_batch_id =>l_batch_id,
798 p_batch_commit =>l_batch_commit,
799 p_preview =>p_preview,
800 p_dict_id =>l_dict_id,
801 p_log_padding =>' ',
802 x_error_msg =>l_error_msg,
803 x_return_status =>l_return_status);
804
805 ---For merging party sites/contact points/relationships within same party
806 ELSIF l_merge_type = 'SAME_PARTY_MERGE' THEN
807
808 OPEN c_dict_id(l_rule_set_name,'HZ_PARTY_SITES');
809 FETCH c_dict_id INTO l_dict_id;
810 IF c_dict_id%NOTFOUND THEN
811 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
812 FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
813 FND_MSG_PUB.ADD;
814 RAISE FND_API.G_EXC_ERROR;
815 END IF;
816 CLOSE c_dict_id;
817
818 -- Loop through the merge party sites
819 OPEN c_batch_party_sites(l_batch_party_id);
820 LOOP
821 FETCH c_batch_party_sites INTO l_from_site_id, l_to_site_id;
822 EXIT WHEN c_batch_party_sites%NOTFOUND;
823
824 IF l_to_site_id IS NOT NULL THEN
825 log('');
826 log('Party Sites');
827
828 g_cur_merge_dict_id := 0;
829 g_num_sub_entities :=-1;
830
831 -- Perform the party site merge within the same party
832 do_merge(p_batch_party_id =>l_batch_party_id,
833 p_entity_name =>'HZ_PARTY_SITES',
834 p_par_entity_name =>NULL,
835 p_from_id =>l_from_site_id,
836 p_to_id =>l_to_site_id,
837 p_par_from_id =>l_pre_merge_to_party_id, --5093366 passing party_id to do_merge for history
838 p_par_to_id =>l_pre_merge_to_party_id,
839 p_rule_set_name =>l_rule_set_name,
840 p_batch_id =>l_batch_id,
841 p_batch_commit =>l_batch_commit,
842 p_preview =>p_preview,
843 p_dict_id =>l_dict_id,
844 p_log_padding =>' ',
845 x_error_msg =>l_error_msg,
846 x_return_status =>l_return_status);
847
848 END IF;
849 EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
850 END LOOP;
851 CLOSE c_batch_party_sites;
852
853 --if the prev. merge was success only then do the next step
854 if l_return_status = FND_API.G_RET_STS_SUCCESS then
855 OPEN c_dict_id(l_rule_set_name,'HZ_CONTACT_POINTS');
856 FETCH c_dict_id INTO l_dict_id;
857 IF c_dict_id%NOTFOUND THEN
858 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
859 FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_CONTACT_POINTS');
860 FND_MSG_PUB.ADD;
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863 CLOSE c_dict_id;
864
865 -- Loop through the merge contact points
866 OPEN c_batch_contact_points(l_batch_party_id);
867 LOOP
868 FETCH c_batch_contact_points INTO l_from_cp_id, l_to_cp_id;
869 EXIT WHEN c_batch_contact_points%NOTFOUND;
870
871 IF l_to_cp_id IS NOT NULL THEN
872 outandlog('.... Merging Contact Points for party, ID='||l_from_party_id);
873 log('');
874 log('Contact Points');
875
876 g_cur_merge_dict_id := 0;
877 g_num_sub_entities :=-1;
878
879 -- Perform the Contact Points merge
880 do_merge(
881 p_batch_party_id =>l_batch_party_id,
882 p_entity_name =>'HZ_CONTACT_POINTS',
883 p_par_entity_name =>NULL,
884 p_from_id =>l_from_cp_id,
885 p_to_id =>l_to_cp_id,
886 p_par_from_id =>l_pre_merge_to_party_id, --5093366 passing party_id to do_merge for history
887 p_par_to_id =>l_pre_merge_to_party_id,
888 p_rule_set_name =>l_rule_set_name,
889 p_batch_id =>l_batch_id,
890 p_batch_commit =>l_batch_commit,
891 p_preview =>p_preview,
892 p_dict_id =>l_dict_id,
893 p_log_padding =>' ',
894 x_error_msg =>l_error_msg,
895 x_return_status =>l_return_status);
896
897 END IF;
898 EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
899 END LOOP;
900 CLOSE c_batch_contact_points;
901
902 end if; --l_return status
903
904
905
906 ---if the prev. merge was success only then do the next step
907 if l_return_status = FND_API.G_RET_STS_SUCCESS then
908 OPEN c_dict_id(l_rule_set_name,'HZ_PARTY_RELATIONSHIPS');
909 FETCH c_dict_id INTO l_dict_id;
910 IF c_dict_id%NOTFOUND THEN
911 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
912 FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_RELATIONSHIPS');
913 FND_MSG_PUB.ADD;
914 RAISE FND_API.G_EXC_ERROR;
915 END IF;
916 CLOSE c_dict_id;
917
918 -- Loop through the merge relationships
919 OPEN c_batch_relationships(l_batch_party_id);
920 LOOP
921 FETCH c_batch_relationships INTO l_from_rel_id, l_to_rel_id;
922 EXIT WHEN c_batch_relationships%NOTFOUND;
923
924 l_from_rel_party_id :=
925 HZ_MERGE_UTIL.get_reln_party_id(l_from_rel_id);
926 l_to_rel_party_id :=
927 HZ_MERGE_UTIL.get_reln_party_id(l_to_rel_id);
928
929 IF l_from_rel_party_id IS NOT NULL AND
930 l_to_rel_party_id IS NOT NULL AND
931 l_from_rel_party_id<>l_to_rel_party_id THEN
932 BEGIN
933 SELECT 1 INTO l_tmp
934 FROM HZ_MERGE_PARTIES
935 WHERE batch_id = l_batch_id
936 AND from_party_id = l_from_rel_party_id
937 AND to_party_id = l_to_rel_party_id
938 AND merge_status = 'DONE';
939
940 l_to_rel_id := NULL;
941 EXCEPTION
942 WHEN NO_DATA_FOUND THEN
943 NULL;
944 END;
945 END IF;
946
947
948 IF l_to_rel_id IS NOT NULL THEN
949 outandlog('....Merging Contacts for party, ID='||l_from_party_id);
950 log('');
951 log('Contacts');
952 g_cur_merge_dict_id := 0;
953 g_num_sub_entities :=-1;
954
955 -- Perform the Contact merge
956 do_merge(
957 p_batch_party_id =>l_batch_party_id,
958 p_entity_name =>'HZ_PARTY_RELATIONSHIPS',
959 p_par_entity_name =>NULL,
960 p_from_id =>l_from_rel_id,
961 p_to_id =>l_to_rel_id,
962 p_par_from_id =>l_pre_merge_to_party_id, --5093366 passing party_id to do_merge for history
963 p_par_to_id =>l_pre_merge_to_party_id,
964 p_rule_set_name =>l_rule_set_name,
965 p_batch_id =>l_batch_id,
966 p_batch_commit =>l_batch_commit,
967 p_preview =>p_preview,
968 p_dict_id =>l_dict_id,
969 p_log_padding =>' ',
970 x_error_msg =>l_error_msg,
971 x_return_status =>l_return_status);
972
973 END IF;
974 EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
975 END LOOP;
976 CLOSE c_batch_relationships;
977 end if; --l_return_status
978
979 ELSE --type of merge
980 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_TYPE');
981 FND_MSG_PUB.ADD;
982 RAISE FND_API.G_EXC_ERROR;
983 END IF; --type of merge
984
985 -- If the party was successfully merged, update merge status to 'DONE'
986 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
987
988 store_merge_history(null,null,null,null,null,null,null,null,null,'Y');
989 -- If delete not vetoed, perform delete
990 IF g_merge_delete_flag = 'Y' THEN
991 delete_merged_records(l_batch_party_id, l_return_status);
992 END IF;
993
994 UPDATE HZ_MERGE_PARTIES
995 SET MERGE_STATUS = 'DONE',
996 last_update_date = hz_utility_v2pub.last_update_date,
997 last_updated_by = hz_utility_v2pub.last_updated_by,
998 last_update_login = hz_utility_v2pub.last_update_login
999 WHERE batch_party_id = l_batch_party_id;
1000
1001 ELSE -- Errors encountered in merge
1002 -- Save the global return status (Across all merge parties in batch)
1003 l_glob_return_status := l_return_status;
1004 out('Error (check log)');
1005 retcode := 1;
1006 errbuf := errbuf || l_error_msg;
1007 EXIT;
1008 END IF;
1009
1010 log('*************************************************');
1011
1012 ELSE --l_merge_status = 'DONE'
1013 outandlog('.... Merging Partes: From Party ID='||l_from_party_id ||
1014 ',To Party ID='||l_to_party_id);
1015 outandlog('Merge already complete');
1016 END IF;
1017
1018 IF g_cur_proc_name IS NOT NULL THEN
1019 g_cur_proc_name := null;
1020 dbms_sql.close_cursor(g_proc_cursor);
1021 END IF;
1022 END LOOP;
1023 CLOSE c_batch_details;
1024
1025 if l_pre_merge_type <> 'SAME_PARTY_MERGE' AND
1026 l_return_status = FND_API.G_RET_STS_SUCCESS then
1027
1028 /* Merge all to records that are getting merged into the transferred from records */
1029 FOR TO_RECORDS IN (
1030 SELECT mp.batch_party_id, merge_from_entity_id, merge_to_entity_id, ENTITY_NAME
1031 FROM HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_DETAILS md
1032 WHERE mp.batch_party_id=md.batch_party_id
1033 AND mp.to_party_id = l_pre_merge_to_party_id
1034 AND mp.batch_id = l_batch_id
1035 AND md.mandatory_merge = 'T') LOOP
1036 do_same_party_merge (
1037 p_batch_party_id=>TO_RECORDS.batch_party_id,
1038 p_entity_name=>TO_RECORDS.ENTITY_NAME,
1039 p_from_id=>TO_RECORDS.merge_from_entity_id,
1040 p_to_id=>TO_RECORDS.merge_to_entity_id,
1041 p_rule_set_name=>l_rule_set_name,
1042 p_batch_id=>l_batch_id,
1043 p_batch_commit=>l_batch_commit,
1044 p_preview=>p_preview,
1045 p_log_padding=>' ',
1046 x_error_msg=>l_error_msg,
1047 x_return_status=>l_return_status);
1048 EXIT WHEN l_return_status <> FND_API.G_RET_STS_SUCCESS;
1049 END LOOP;
1050
1051 IF l_return_status = FND_API.G_RET_STS_SUCCESS then
1052 BEGIN
1053
1054 SELECT 1 INTO l_tmp
1055 FROM HZ_MERGE_ENTITY_ATTRIBUTES
1056 WHERE merge_to_party_id = l_pre_merge_to_party_id
1057 AND merge_batch_id = l_batch_id
1058 AND ROWNUM=1;
1059
1060 SELECT decode(party_type,'PERSON','HZ_PERSON_PROFILES',
1061 'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
1062 'HZ_ORGANIZATION_PROFILES') INTO l_party_type
1063 FROM HZ_PARTIES
1064 WHERE party_id=l_pre_merge_to_party_id;
1065
1066
1067 HZ_MERGE_ENTITY_ATTRI_PVT.do_profile_attribute_merge(
1068 l_batch_id,
1069 l_pre_merge_to_party_id,
1070 l_party_type,
1071 l_return_status);
1072
1073 ---Bug 2723616 raise the message passed by profile attr API
1074 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1075 l_msg_data := logerror;
1076 END IF;
1077
1078
1079 EXCEPTION
1080 WHEN NO_DATA_FOUND THEN
1081 NULL;
1082 WHEN OTHERS THEN
1083 FND_FILE.put_line(FND_FILE.log,'l_return_status ' || l_return_status);
1084 FND_FILE.put_line(FND_FILE.log,'Error ' || SQLERRM);
1085 l_return_status:= FND_API.G_RET_STS_ERROR;
1086 END;
1087 END IF;
1088 END IF;
1089
1090 --4114041
1091 IF (l_glob_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1092 l_glob_return_status := l_return_status;
1093 END IF;
1094
1095 --If merge mode is not preview
1096 IF p_preview <> 'Y'AND
1097 l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1098 null;
1099 -- COMMIT; Should not commit here, need to run batch merge procs after this. 4634891
1100 ELSE
1101 ROLLBACK to merge_group;
1102 END IF;
1103
1104 END LOOP;
1105 CLOSE c_pre_merge;
1106
1107 --bug 4634891
1108 -- populate hz_merge_party_log table before executing batch merge procs for team to query
1109 store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1110
1111
1112 OPEN batch_merge_procedures;
1113 LOOP
1114
1115 l_sub_entity_name := NULL;
1116 l_merge_dict_id := NULL;
1117 FETCH batch_merge_procedures INTO l_merge_dict_id, l_sub_entity_name, l_proc_name;
1118 EXIT WHEN batch_merge_procedures%NOTFOUND;
1119 exec_merge(l_sub_entity_name,l_proc_name,p_batch_id,g_request_id,l_return_status);
1120
1121 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1122 outandlog('Executed batch merge procedure '||l_proc_name||' successfully');
1123 ELSE
1124 l_glob_return_status := l_return_status;
1125 ROLLBACK TO batch_merge;
1126 outandlog('Batch merge procedure '||l_proc_name||' is not successful');
1127 l_error_msg := logerror;
1128 --bug 4916777
1129 IF FND_MSG_PUB.Count_Msg <=0 THEN
1130 l_error_msg := 'Error executing batch merge procedure ' || l_proc_name || ' for entity ' || l_sub_entity_name;
1131 END IF;
1132 I_Counter:=0;
1133 store_merge_log(l_batch_pid, -1,
1134 -1,-1, -1,null,null,
1135 -1, 'Error', l_error_msg);
1136 store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1137 --bug 4916777
1138 outandlog('****ERROR*** : '||l_error_msg);
1139
1140 EXIT;
1141 END IF;
1142 END LOOP;
1143 CLOSE batch_merge_procedures;
1144 --bug 4634891
1145
1146
1147 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED','BO_EVENTS_ENABLED')) THEN
1148
1149 HZ_BES_BO_UTIL_PKG.upd_entity_ids(hz_utility_pub.request_id);
1150
1151 END IF;
1152
1153 -- Check if the whole batch was successfully executed
1154 IF l_glob_return_status = FND_API.G_RET_STS_SUCCESS THEN
1155 outandlog('.... Merge batch successfully executed');
1156
1157 -- If not preview mode update batch status to COMPLETE
1158 IF p_preview <> 'Y' then
1159 -- Update the merge batch status to COMPLETE
1160 UPDATE HZ_MERGE_BATCH
1161 SET BATCH_STATUS = 'COMPLETE'
1162 WHERE BATCH_ID = p_batch_id;
1163
1164 BEGIN
1165 UPDATE HZ_DUP_SETS
1166 SET STATUS = 'COMPLETED'
1167 WHERE dup_set_id = p_batch_id;
1168 EXCEPTION
1169 WHEN NO_DATA_FOUND THEN
1170 NULL;
1171 END;
1172
1173 END IF;
1174
1175 -- If not preview mode and batch commit set at batch level, commit the batch
1176 IF p_preview <> 'Y' THEN
1177 outandlog('.... Commit complete');
1178 COMMIT;
1179 -- If preview mode, rollback all the merged parties
1180 ELSIF p_preview='Y' THEN
1181 ROLLBACK to batch_merge ;
1182 outandlog('.... Preview generation complete. Merge transaction rolled back');
1183 outandlog('.... To Execute and commit run merge without preview');
1184 END IF;
1185 ELSE
1186 outandlog('.... One or more of the Merges in the batch had errors.');
1187 outandlog('.... Please check the log file');
1188
1189 -- If not preview mode and batch commit is set at merge party level,
1190 -- the set batch status to PART_COMPLETE if any of the merged parties
1191 -- is succesfully complete
1192 IF p_preview <> 'Y' THEN
1193
1194 SELECT count(*) INTO l_num_merged FROM HZ_MERGE_PARTIES
1195 WHERE batch_id = p_batch_id
1196 AND merge_status = 'DONE';
1197
1198 IF l_num_merged > 0 THEN
1199 UPDATE HZ_MERGE_BATCH
1200 SET BATCH_STATUS = 'PART_COMPLETE'
1201 WHERE BATCH_ID = p_batch_id;
1202
1203 ELSE
1204 --BUG 4199594
1205 UPDATE HZ_MERGE_BATCH
1206 SET BATCH_STATUS = 'ERROR'
1207 WHERE BATCH_ID = p_batch_id;
1208
1209 retcode:=2;
1210 END IF;
1211
1212 BEGIN
1213 UPDATE HZ_DUP_SETS
1214 SET STATUS = 'ERROR'
1215 WHERE DUP_SET_ID = p_batch_id;
1216 EXCEPTION
1217 WHEN NO_DATA_FOUND THEN
1218 NULL;
1219 END;
1220
1221 COMMIT;
1222 END IF;
1223
1224 --4114041
1225 IF l_num_merged > 0 THEN
1226 outandlog('.... Party merge concurrent program completed partially');
1227 ELSE
1228 outandlog('.... No changes from the batch have been applied');
1229 END IF;
1230 END IF;
1231
1232 -- set back orig. DSS profile
1233 fnd_profile.put('HZ_DSS_ENABLED',l_dss_orig_prof_val);
1234
1235 hz_common_pub.enable_cont_source_security;
1236
1237 IF g_inv_merge_dict is not null THEN
1238 log('');
1239 log('CAUTION: The following tables that are registered in the Party Merge');
1240 log('dictionary were introduced(or modified) in 11.5.6. If you are not at 11.5.6, Party Merge');
1241 log('will skip over these tables and columns because they are not be available in');
1242 log('your database. This should not affect the functionality of Party Merge.');
1243 log('');
1244 log(g_inv_merge_dict);
1245 g_inv_merge_dict := NULL;
1246 g_inv_merge_dict_cnt := 0;
1247 END IF;
1248
1249 outandlog('Concurrent Program Execution completed ');
1250 outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1251
1252 -- FND_FILE.close;
1253 --4634891
1254 IF p_preview <> 'Y' THEN
1255 OPEN merge_to_parties;
1256 LOOP
1257 l_pre_merge_to_party_id := NULL;
1258 FETCH merge_to_parties INTO l_pre_merge_to_party_id;
1259 EXIT WHEN merge_to_parties%NOTFOUND;
1260 --4230396
1261 l_key := HZ_EVENT_PKG.item_key('oracle.apps.ar.hz.Party.merge');
1262 -- initialization of object variables
1263 l_list := WF_PARAMETER_LIST_T();
1264 -- add parameters to list
1265 wf_event.addParameterToList(p_name => 'batch_id',
1266 p_value => l_batch_id,
1267 p_parameterlist => l_list);
1268 wf_event.addParameterToList(p_name => 'merge_to_party_id',
1269 p_value => l_pre_merge_to_party_id,
1270 p_parameterlist => l_list);
1271 wf_event.addParameterToList(p_name => 'Q_CORRELATION_ID',
1272 p_value => 'oracle.apps.ar.hz.Party.merge',
1273 p_parameterlist => l_list);
1274 -- Raise Event
1275 HZ_EVENT_PKG.raise_event(
1276 p_event_name => 'oracle.apps.ar.hz.Party.merge',
1277 p_event_key => l_key,
1278 p_parameters => l_list );
1279 l_list.DELETE;
1280 --4230396
1281 END LOOP;
1282 CLOSE merge_to_parties;
1283 END IF;
1284 --4634891
1285
1286
1287 EXCEPTION
1288 WHEN FND_API.G_EXC_ERROR THEN
1289 --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1290 IF p_preview = 'Y' AND l_batch_merge_spt THEN
1291 ROLLBACK to batch_merge;
1292 ELSE
1293 IF l_mb_spt THEN
1294 ROLLBACK to merge_group;
1295 ELSIF l_batch_merge_spt THEN
1296 ROLLBACK to batch_merge;
1297 END IF;
1298 END IF;
1299 UPDATE HZ_DUP_SETS
1300 set status = 'ERROR'
1301 where dup_set_id = p_batch_id;
1302 commit;
1303 hz_common_pub.enable_cont_source_security;
1304 outandlog('Error: Aborting Batch');
1305 outandlog('Error: '|| SQLERRM);
1306 retcode := 2;
1307 errbuf := errbuf || logerror;
1308 store_merge_log(l_batch_pid, -1,
1309 -1,-1, -1,null,null,
1310 -1, 'Error', errbuf);
1311 store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1312 -- FND_FILE.close;
1313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314 --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1315 IF p_preview = 'Y' AND l_batch_merge_spt THEN
1316 ROLLBACK to batch_merge;
1317 ELSE
1318 IF l_mr_spt THEN
1319 ROLLBACK to merge_group;
1320 ELSIF l_batch_merge_spt THEN
1321 ROLLBACK to batch_merge;
1322 END IF;
1323 END IF;
1324 UPDATE HZ_DUP_SETS
1325 set status = 'ERROR'
1326 where dup_set_id = p_batch_id;
1327 commit;
1328 hz_common_pub.enable_cont_source_security;
1329 outandlog('Error: Aborting Batch');
1330 outandlog('Error: '|| SQLERRM);
1331 retcode := 2;
1332 errbuf := errbuf || logerror;
1333 store_merge_log(l_batch_pid, -1,
1334 -1,-1, -1,null,null,
1335 -1, 'Error', errbuf);
1336 store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1337 -- FND_FILE.close;
1338 WHEN OTHERS THEN
1339 --store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1340 IF p_preview = 'Y' AND l_batch_merge_spt THEN
1341 ROLLBACK to batch_merge;
1342 ELSE
1343 IF l_mr_spt THEN
1344 ROLLBACK to merge_group;
1345 ELSIF l_batch_merge_spt THEN
1346 ROLLBACK to batch_merge;
1347 END IF;
1348 END IF;
1349 UPDATE HZ_DUP_SETS
1350 set status = 'ERROR'
1351 where dup_set_id = p_batch_id;
1352 commit;
1353 hz_common_pub.enable_cont_source_security;
1354 outandlog('Error: Aborting Batch');
1355 outandlog('Error: '|| SQLERRM);
1356 retcode := 2;
1357 errbuf := errbuf || logerror;
1358 store_merge_log(l_batch_pid, -1,
1359 -1,-1, -1,null,null,
1360 -1, 'Error', errbuf);
1361 store_merge_log(null, null, null,null, null,null, null, null, null,null,'Y');
1362 -- FND_FILE.close;
1363 END batch_merge;
1364
1365 /*
1366 Procedure to perform merge for a to_record merging into a from_record
1367 */
1368 PROCEDURE do_same_party_merge (
1369 p_batch_party_id IN NUMBER,
1370 p_entity_name IN VARCHAR2,
1371 p_from_id IN NUMBER,
1372 p_to_id IN OUT NOCOPY NUMBER,
1373 p_rule_set_name IN VARCHAR2,
1374 p_batch_id IN NUMBER,
1375 p_batch_commit IN VARCHAR2,
1376 p_preview IN VARCHAR2,
1377 p_log_padding IN VARCHAR2,
1378 x_error_msg IN OUT NOCOPY VARCHAR2,
1379 x_return_status IN OUT NOCOPY VARCHAR2) IS
1380
1381 CURSOR c_dict_id(cp_ruleset_name VARCHAR2, cp_entity_name VARCHAR2) IS
1382 SELECT merge_dict_id, DESCRIPTION
1383 FROM HZ_MERGE_DICTIONARY
1384 WHERE RULE_SET_NAME = cp_ruleset_name
1385 AND ENTITY_NAME = cp_entity_name;
1386
1387 l_dict_id NUMBER;
1388 l_desc HZ_MERGE_DICTIONARY.DESCRIPTION%TYPE;
1389
1390 BEGIN
1391 OPEN c_dict_id(p_rule_set_name,p_entity_name);
1392 FETCH c_dict_id INTO l_dict_id, l_desc;
1393 IF c_dict_id%NOTFOUND THEN
1394 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1395 FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
1396 FND_MSG_PUB.ADD;
1397 RAISE FND_API.G_EXC_ERROR;
1398 END IF;
1399 CLOSE c_dict_id;
1400
1401 log('');
1402 log(l_desc);
1403
1404 g_cur_merge_dict_id := 0;
1405 g_num_sub_entities :=-1;
1406
1407 -- Perform the party site merge within the same party
1408 do_merge(p_batch_party_id =>p_batch_party_id,
1409 p_entity_name => p_entity_name,
1410 p_par_entity_name =>NULL,
1411 p_from_id =>p_from_id,
1412 p_to_id =>p_to_id,
1413 p_par_from_id =>NULL,
1414 p_par_to_id =>NULL,
1415 p_rule_set_name =>p_rule_set_name,
1416 p_batch_id =>p_batch_id,
1417 p_batch_commit =>p_batch_commit,
1418 p_preview =>p_preview,
1419 p_dict_id =>l_dict_id,
1420 p_log_padding =>p_log_padding||' ',
1421 x_error_msg =>x_error_msg,
1422 x_return_status =>x_return_status);
1423 END;
1424
1425 /*-----------------------------------------------------------------------------
1426 | The main engine procedure that performs the merge
1427 | Recursively calls the merge procedures for each sub-record of each subentity
1428 |------------------------------------------------------------------------------*/
1429
1430 PROCEDURE do_merge(
1431 p_batch_party_id IN NUMBER,
1432 p_entity_name IN VARCHAR2,
1433 p_par_entity_name IN VARCHAR2,
1434 p_from_id IN NUMBER,
1435 p_to_id IN OUT NOCOPY NUMBER,
1436 p_par_from_id IN NUMBER,
1437 p_par_to_id IN NUMBER,
1438 p_rule_set_name IN VARCHAR2,
1439 p_batch_id IN NUMBER,
1440 p_batch_commit IN VARCHAR2,
1441 p_preview IN VARCHAR2,
1442 p_dict_id IN NUMBER,
1443 p_log_padding IN VARCHAR2,
1444 x_error_msg IN OUT NOCOPY VARCHAR2,
1445 x_return_status IN OUT NOCOPY VARCHAR2) IS
1446
1447 -- Fetch dictionary details for the entity (Merge procedure)
1448 CURSOR c_dict_details(cp_merge_dict_id NUMBER) IS
1449 SELECT PROCEDURE_NAME, PK_COLUMN_NAME,nvl(DESC_COLUMN_NAME,PK_COLUMN_NAME),
1450 FK_COLUMN_NAME, PARENT_ENTITY_NAME
1451 FROM hz_merge_dictionary
1452 WHERE merge_dict_id = cp_merge_dict_id;
1453
1454 -- Fecth merge party details from the dictionary
1455 CURSOR c_party_details(cp_ent_name VARCHAR2, cp_pk_value NUMBER) IS
1456 SELECT merge_to_entity_id
1457 FROM hz_merge_party_details
1458 WHERE merge_from_entity_id = cp_pk_value AND
1459 batch_party_id = p_batch_party_id AND
1460 entity_name = cp_ent_name;
1461
1462
1463 l_proc_name HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE;
1464 l_pk_column_name HZ_MERGE_DICTIONARY.PK_COLUMN_NAME%TYPE;
1465 l_desc_column_name HZ_MERGE_DICTIONARY.DESC_COLUMN_NAME%TYPE;
1466 l_fk_column_name HZ_MERGE_DICTIONARY.FK_COLUMN_NAME%TYPE;
1467 l_parent_entity_name HZ_MERGE_DICTIONARY.PARENT_ENTITY_NAME%TYPE;
1468 l_sub_entity_name HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE;
1469 l_sub_fk_column_name HZ_MERGE_DICTIONARY.FK_COLUMN_NAME%TYPE;
1470 l_sub_fk_column_type HZ_MERGE_DICTIONARY.FK_DATA_TYPE%TYPE;
1471 l_sub_pk_column_name HZ_MERGE_DICTIONARY.PK_COLUMN_NAME%TYPE;
1472 l_proc_name_b HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE;
1473 l_pk_column_name_b HZ_MERGE_DICTIONARY.PK_COLUMN_NAME%TYPE;
1474 l_desc_column_name_b HZ_MERGE_DICTIONARY.DESC_COLUMN_NAME%TYPE;
1475 l_fk_column_name_b HZ_MERGE_DICTIONARY.FK_COLUMN_NAME%TYPE;
1476 l_parent_entity_name_b HZ_MERGE_DICTIONARY.PARENT_ENTITY_NAME%TYPE;
1477
1478 l_sub_to_id NUMBER;
1479 l_op_type VARCHAR2(50);
1480 l_join_clause VARCHAR2(2000);
1481
1482 -- REF Cursor to fetch sub-records in each sub-entity
1483 TYPE SubRecType IS REF CURSOR;
1484 c_sub_records SubRecType;
1485 l_sub_pk_value NUMBER;
1486 l_sub_pk_value_r ROWID;
1487
1488 l_merge_dict_id NUMBER;
1489 l_from_rec_desc VARCHAR2(2000);
1490 l_to_rec_desc VARCHAR2(2000);
1491 l_desc VARCHAR2(2000);
1492
1493 l_pmerge_apps VARCHAR2(2000) := NULL;
1494
1495 TYPE SubEntType IS REF CURSOR;
1496 c_sub_entities SubEntType;
1497
1498 l_subrec_str VARCHAR2(2000);
1499 l_subent_cnt NUMBER := 0;
1500 l_null_id NUMBER;
1501 l_null_id_r ROWID;
1502
1503 l_bulk_flag VARCHAR2(1);
1504
1505 l_op VARCHAR2(30);
1506 rownumber NUMBER;
1507 l_mand VARCHAR2(30);
1508 l_hint VARCHAR2(255);
1509
1510 BEGIN
1511
1512 -- Merge this entity
1513
1514 -- Fetch dict details (merge procedure)
1515 OPEN c_dict_details(p_dict_id);
1516 FETCH c_dict_details INTO l_proc_name, l_pk_column_name,l_desc_column_name,
1517 l_fk_column_name, l_parent_entity_name;
1518
1519 -- If not found error out
1520 IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1521 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1522 FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
1523 FND_MSG_PUB.ADD;
1524 RAISE FND_API.G_EXC_ERROR;
1525 END IF;
1526 CLOSE c_dict_details;
1527
1528
1529 l_from_rec_desc := get_record_desc(p_from_id,p_entity_name, l_pk_column_name,
1530 l_desc_column_name, x_return_status);
1531 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1532
1533 -- Write a log message
1534 log(p_log_padding|| l_from_rec_desc, FALSE);
1535
1536 -- Execute the merge procedure for the entity
1537 exec_merge(p_entity_name,
1538 l_proc_name,
1539 p_from_id,
1540 p_to_id,
1541 p_par_from_id,
1542 p_par_to_id,
1543 p_par_entity_name,
1544 p_batch_id,
1545 p_batch_party_id,
1546 x_return_status);
1547
1548 -- Check if the merge procedure returned an error
1549 IF (x_return_status = FND_API.G_RET_STS_SUCCESS OR
1550 x_return_status = 'N') THEN
1551 -- Log the merged records
1552
1553 -- If the to_id is different from from_id .. then the operation performed
1554 -- is a merge
1555 IF ((p_to_id IS NOT NULL AND p_to_id <> FND_API.G_MISS_NUM)
1556 AND p_to_id <> p_from_id) THEN
1557 -- Store in the history and log
1558
1559 IF (p_to_id <> 0) THEN
1560 l_mand := 'N';
1561 IF x_return_status='N' THEN
1562 l_mand := 'C';
1563 l_op := 'Copy';
1564 ELSE
1565 l_op := 'Merge';
1566 END IF;
1567
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569
1570 l_to_rec_desc := get_record_desc(p_to_id,p_entity_name, l_pk_column_name,
1571 l_desc_column_name, x_return_status);
1572 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1573
1574 IF p_entity_name = 'HZ_PARTY_SITES' AND l_mand <> 'C' THEN
1575 BEGIN
1576 SELECT mandatory_merge INTO l_mand
1577 FROM hz_merge_party_details
1578 WHERE batch_party_id = p_batch_party_id
1579 AND merge_from_entity_id = p_from_id
1580 AND entity_name = p_entity_name;
1581
1582 EXCEPTION
1583 WHEN NO_DATA_FOUND THEN
1584 l_mand := 'N';
1585 END;
1586 END IF;
1587
1588 IF l_mand IS NOT NULL AND l_mand = 'C' THEN
1589 l_op := 'Copy';
1590 -- Write to log file
1591 log(' copied to ' || l_to_rec_desc);
1592 ELSE
1593 -- Write to log file
1594 log(' merged with ' || l_to_rec_desc);
1595 END IF;
1596
1597 store_merge_history(p_batch_party_id, p_from_id,
1598 p_to_id,p_par_from_id, p_par_to_id,l_from_rec_desc,
1599 l_to_rec_desc, p_dict_id, l_op);
1600 END IF;
1601 ELSE
1602 log(' discarded (DNB data)');
1603 END IF;
1604
1605 -- Else the operation performed is transfer
1606 -- However also check of the parent IDs are different.
1607 ELSIF p_par_from_id <> p_par_to_id THEN
1608 store_merge_history(p_batch_party_id,p_from_id,
1609 p_from_id,p_par_from_id, p_par_to_id,l_from_rec_desc, null, p_dict_id, --5093366 replaced p_to_id with p_from_id
1610 'Transfer');
1611
1612 -- Since the id of the record does not change, the to_id is the
1613 -- same as the from_id
1614 p_to_id := p_from_id;
1615 log(' : Transferred '|| l_fk_column_name ||' from '|| p_par_from_id ||' to '||p_par_to_id);
1616 --return do not go for sub-entities if its transfer
1617 return;
1618
1619 -- To and From Ids are same, and Parent IDs are same ..
1620 -- nothing has been done
1621 ELSE
1622 -- Set To Id to the From ID .. this is just to continue further down the
1623 -- the hierarchy
1624 log(' : Record not modified');
1625 p_to_id := p_from_id;
1626 END IF;
1627 ELSE
1628 log('', TRUE);
1629 log(' **** Error **** ', TRUE);
1630 log('Error: Merge failed in procedure '||l_proc_name||' for entity '||p_entity_name,TRUE); --4634891
1631 x_error_msg := logerror;
1632
1633 --bug 4916777
1634 IF FND_MSG_PUB.Count_Msg <=0 THEN
1635 x_error_msg := 'Error executing procedure ' || l_proc_name || ' for entity ' || p_entity_name;
1636 log(x_error_msg);
1637 END IF;
1638 --bug 4916777
1639 -- Log the error in the table and return
1640 store_merge_log(p_batch_party_id, p_from_id,
1641 p_to_id,p_par_from_id, p_par_to_id,l_from_rec_desc,null,
1642 p_dict_id, 'Error', x_error_msg);
1643 RETURN;
1644 END IF;
1645 ELSE
1646 log('', TRUE);
1647 log(' **** Error **** ',TRUE);
1648 log('Error: Merge failed in procedure HZ_PARTY_MERGE.GET_RECORD_DESC for entity '||p_entity_name); --4634891
1649 x_error_msg := logerror;
1650
1651 -- Log the error in the table and return
1652 store_merge_log(p_batch_party_id, p_from_id,
1653 p_to_id,p_par_from_id, p_par_to_id,l_from_rec_desc,null, p_dict_id, 'Error',
1654 x_error_msg);
1655 RETURN;
1656 END IF;
1657
1658 IF g_cur_merge_dict_id = p_dict_id AND g_num_sub_entities = 0 THEN
1659 RETURN;
1660 END IF;
1661 g_cur_merge_dict_id:=p_dict_id;
1662 g_num_sub_entities:=-1;
1663
1664 -- Merge the sub-entities .. For each subentity fetch the records and
1665 -- call the merge procedure
1666 -- If data from 'IMPORT', only TCA entities need to be merged.
1667
1668 l_pmerge_apps:=null;
1669 if G_REQUEST_TYPE = 'IMPORT'
1670 then
1671 l_pmerge_apps := G_TCA_APP_ID;
1672 else
1673
1674 IF FND_PROFILE.VALUE('HZ_PARTY_MERGE_APPLICATIONS') IS NOT NULL THEN
1675 l_pmerge_apps := '(' || G_TCA_APP_ID || ', ' ||
1676 FND_PROFILE.VALUE('HZ_PARTY_MERGE_APPLICATIONS') || ')';
1677 END IF;
1678 END IF;
1679
1680 IF l_pmerge_apps IS NOT NULL THEN
1681
1682
1683
1684 OPEN c_sub_entities FOR
1685 'SELECT MERGE_DICT_ID, ENTITY_NAME, FK_COLUMN_NAME, FK_DATA_TYPE, PK_COLUMN_NAME,'||
1686 'JOIN_CLAUSE, DESCRIPTION, PROCEDURE_NAME, BULK_FLAG '||
1687 'FROM HZ_MERGE_DICTIONARY ' ||
1688 'WHERE PARENT_ENTITY_NAME = :pentity' ||
1689 ' AND RULE_SET_NAME = :ruleset '||
1690 ' AND DICT_APPLICATION_ID IN ' || l_pmerge_apps ||
1691 ' AND NVL(BATCH_MERGE_FLAG,''N'') <> ''Y'' '|| --bug4634891
1692 ' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
1693 ELSE
1694 OPEN c_sub_entities FOR
1695 'SELECT MERGE_DICT_ID, ENTITY_NAME, FK_COLUMN_NAME, FK_DATA_TYPE, PK_COLUMN_NAME,'||
1696 'JOIN_CLAUSE, DESCRIPTION, PROCEDURE_NAME, BULK_FLAG '||
1697 'FROM HZ_MERGE_DICTIONARY ' ||
1698 'WHERE PARENT_ENTITY_NAME = :pentity' ||
1699 ' AND RULE_SET_NAME = :ruleset '||
1700 ' AND NVL(BATCH_MERGE_FLAG,''N'') <> ''Y'' '|| --bug4634891
1701 ' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
1702
1703 end if;
1704
1705
1706 l_subent_cnt := 0;
1707 LOOP
1708 -- Fetch the subentities
1709 FETCH c_sub_entities INTO l_merge_dict_id, l_sub_entity_name,
1710 l_sub_fk_column_name, l_sub_fk_column_type,l_sub_pk_column_name, l_join_clause, l_desc,
1711 l_proc_name, l_bulk_flag;
1712 EXIT WHEN c_sub_entities%NOTFOUND;
1713
1714 IF NOT g_skip_dict_id.EXISTS(l_merge_dict_id) THEN
1715
1716 l_subent_cnt := l_subent_cnt+1;
1717
1718 -- Invalid subentity in dictionary .. error out
1719 IF (l_sub_entity_name is null or l_sub_fk_column_name is null or
1720 l_sub_fk_column_name is null) THEN
1721 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1722 FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1723 FND_MSG_PUB.ADD;
1724
1725 x_error_msg:=logerror;
1726 store_merge_log(p_batch_party_id, -1,
1727 -1,p_from_id, p_to_id,null,null,
1728 l_merge_dict_id, 'Error', x_error_msg);
1729 log('Error Entity Name: '||l_sub_entity_name);
1730 log('Error FK Column: '||l_sub_fk_column_name);
1731
1732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1733 RETURN;
1734 END IF;
1735
1736 --log('fk data type: '||l_sub_fk_column_type);
1737
1738 l_hint := '';
1739 IF l_desc IS NOT NULL AND l_desc like 'HINT:%' THEN
1740 l_hint := substr(l_desc,6,instr(l_desc, '/',1,2)-5);
1741 l_desc := replace(l_desc,'HINT:'||l_hint);
1742 END IF;
1743
1744 IF l_hint IS NULL THEN
1745 IF (l_sub_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN --4500011
1746 l_subrec_str := 'SELECT RELATIONSHIP_ID FROM HZ_RELATIONSHIPS'
1747 ||' WHERE '|| l_sub_fk_column_name;
1748 ELSE
1749 l_subrec_str := 'SELECT ' || l_sub_pk_column_name || ' FROM '
1750 ||l_sub_entity_name || ' WHERE '|| l_sub_fk_column_name;
1751 END IF;
1752 ELSE
1753 l_subrec_str := 'SELECT ' || l_hint ||' '|| l_sub_pk_column_name || ' FROM '
1754 ||l_sub_entity_name || ' S WHERE '|| l_sub_fk_column_name;
1755 END IF;
1756
1757 IF l_sub_fk_column_type = 'VARCHAR2' THEN
1758 l_subrec_str := l_subrec_str || ' = TO_CHAR(:p_from_id)';
1759 ELSE
1760 l_subrec_str := l_subrec_str || ' = :p_from_id';
1761 END IF;
1762
1763 IF l_join_clause IS NOT NULL THEN
1764 l_subrec_str := l_subrec_str || ' AND ' || l_join_clause;
1765 END IF;
1766 --4500011
1767 IF (l_sub_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
1768 l_subrec_str := l_subrec_str || ' AND ' || ' subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
1769 END IF;
1770
1771 BEGIN
1772 OPEN c_sub_records FOR l_subrec_str using p_from_id;
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 IF SQLCODE=-904 THEN
1776
1777 IF nvl(instrb(g_inv_merge_dict, l_sub_entity_name),0)=0 THEN
1778 g_inv_merge_dict_cnt := g_inv_merge_dict_cnt+1;
1779 IF g_inv_merge_dict_cnt = 1 THEN
1780 g_inv_merge_dict := g_inv_merge_dict || rpad(l_sub_entity_name,28);
1781 ELSIF g_inv_merge_dict_cnt = 3 THEN
1782 g_inv_merge_dict := g_inv_merge_dict || ' ' || l_sub_entity_name || fnd_global.local_chr(13) || fnd_global.local_chr(10);
1783 g_inv_merge_dict_cnt:=0;
1784 ELSE
1785 g_inv_merge_dict := g_inv_merge_dict || ' ' || rpad(l_sub_entity_name,28);
1786 END IF;
1787 END IF;
1788 l_subrec_str:=NULL;
1789 ELSIF SQLCODE=-942 THEN
1790 IF nvl(instrb(g_inv_merge_dict, l_sub_entity_name),0)=0 THEN
1791 g_inv_merge_dict_cnt := g_inv_merge_dict_cnt+1;
1792 IF g_inv_merge_dict_cnt = 1 THEN
1793 g_inv_merge_dict := g_inv_merge_dict || rpad(l_sub_entity_name,28);
1794 ELSIF g_inv_merge_dict_cnt = 3 THEN
1795 g_inv_merge_dict := g_inv_merge_dict || ' ' || l_sub_entity_name ||
1796 fnd_global.local_chr(13) || fnd_global.local_chr(10);
1797 g_inv_merge_dict_cnt:=0;
1798 ELSE
1799 g_inv_merge_dict := g_inv_merge_dict || ' ' || rpad(l_sub_entity_name,28);
1800 END IF;
1801 END IF;
1802 l_subrec_str:=NULL;
1803 ELSE
1804 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1805 FND_MESSAGE.SET_TOKEN('ERROR' ,'do_merge ' || SQLERRM);
1806 FND_MSG_PUB.ADD;
1807
1808 x_error_msg:=logerror;
1809 store_merge_log(p_batch_party_id, -1,
1810 -1,p_from_id, p_to_id,null,null,
1811 l_merge_dict_id, 'Error', x_error_msg);
1812 log('Error Entity Name: '||l_sub_entity_name);
1813 log('Error FK Column: '||l_sub_fk_column_name);
1814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1815 RETURN;
1816 END IF;
1817 END;
1818
1819 -- Handle the case where the Primary key has been defined as the
1820 -- ROWID. No recursion for this type of PK definition.
1821 IF l_sub_pk_column_name='ROWID' THEN
1822
1823 -- Fetch dictionary details
1824 OPEN c_dict_details(l_merge_dict_id);
1825 FETCH c_dict_details INTO l_proc_name_b, l_pk_column_name_b,l_desc_column_name_b,
1826 l_fk_column_name_b, l_parent_entity_name_b;
1827 -- If not found error out
1828 IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1829 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1830 FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1831 FND_MSG_PUB.ADD;
1832 RAISE FND_API.G_EXC_ERROR;
1833 END IF;
1834 CLOSE c_dict_details;
1835 log(p_log_padding || ' '|| l_desc);
1836
1837 rownumber := 0;
1838
1839 LOOP
1840 EXIT WHEN l_subrec_str IS NULL;
1841 l_sub_pk_value_r := NULL;
1842 FETCH c_sub_records INTO l_sub_pk_value_r;
1843 EXIT WHEN c_sub_records%NOTFOUND;
1844
1845 rownumber:=rownumber+1;
1846
1847 IF l_bulk_flag IS NULL OR l_bulk_flag = 'N' THEN
1848 l_from_rec_desc := get_record_desc_r(l_sub_pk_value_r,l_sub_entity_name,
1849 l_desc_column_name_b, x_return_status);
1850 log(p_log_padding || ' ' || l_from_rec_desc, FALSE);
1851 END IF;
1852
1853 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1854
1855 IF rownumber=1 OR l_bulk_flag IS NULL OR l_bulk_flag <> 'Y' THEN
1856 -- Execute the merge procedure for the entity
1857 exec_merge_r(l_sub_entity_name,
1858 l_proc_name_b,
1859 l_sub_pk_value_r,
1860 l_null_id_r,
1861 p_from_id,
1862 p_to_id,
1863 p_entity_name,
1864 p_batch_id,
1865 p_batch_party_id,
1866 x_return_status);
1867 END IF;
1868
1869 -- Check if the merge procedure returned an error
1870 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1871 IF p_from_id <> p_to_id THEN
1872 store_merge_history(p_batch_party_id,-1,
1873 -1,p_from_id, p_to_id,l_from_rec_desc, null, p_dict_id,
1874 'Transfer');
1875
1876 IF l_bulk_flag IS NOT NULL AND l_bulk_flag = 'Y' THEN
1877 IF rownumber = 1 THEN
1878 log(p_log_padding || ' ' || ' Bulk merge completed. Transferred '|| l_fk_column_name_b ||' from '|| p_from_id ||' to '||p_to_id);
1879 END IF;
1880 ELSE
1881 -- Since the id of the record does not change, the to_id is the
1882 -- same as the from_id
1883 log(' : Transferred '|| l_fk_column_name_b ||' from '|| p_from_id ||' to '||p_to_id);
1884 END IF;
1885
1886 -- To and From Ids are same, and Parent IDs are same ..
1887 -- nothing has been done
1888 ELSE
1889 -- Set To Id to the From ID .. this is just to continue further down the
1890 -- the hierarchy
1891 log(' : Record not modified');
1892 END IF;
1893 ELSE
1894 log('', TRUE);
1895 log(' **** Error **** ', TRUE);
1896 log('Error: Merge failed in procedure '||l_proc_name_b||' for entity '||l_sub_entity_name,TRUE); --4634891
1897 x_error_msg := logerror;
1898 --bug 4916777
1899 IF FND_MSG_PUB.Count_Msg <=0 THEN
1900 x_error_msg := 'Error executing procedure ' || l_proc_name || ' for entity ' || p_entity_name;
1901 log(x_error_msg);
1902 END IF ;
1903 --bug 4916777
1904 -- Log the error in the table and return
1905 store_merge_log(p_batch_party_id, -1,
1906 -1,p_from_id, p_to_id,l_from_rec_desc,null,
1907 l_merge_dict_id, 'Error', x_error_msg);
1908 RETURN;
1909 END IF;
1910 ELSE
1911 log('', TRUE);
1912 log(' **** Error **** ', TRUE);
1913 log('Error: Merge failed in procedure HZ_PARTY_MERGE.GET_RECORD_DESC for entity '||l_sub_entity_name); --4634891
1914 x_error_msg := logerror;
1915
1916 -- Log the error in the table and return
1917 store_merge_log(p_batch_party_id, -1,
1918 -1,p_from_id, p_to_id,null,null,
1919 l_merge_dict_id, 'Error', x_error_msg);
1920 RETURN;
1921 END IF;
1922 EXIT WHEN l_bulk_flag IS NOT NULL AND l_bulk_flag = 'Y';
1923 END LOOP;
1924
1925 ELSE
1926
1927 IF l_bulk_flag IS NOT NULL AND l_bulk_flag = 'Y' THEN
1928 rownumber := 0;
1929
1930
1931 LOOP
1932 EXIT WHEN l_subrec_str IS NULL;
1933 FETCH c_sub_records INTO l_sub_pk_value;
1934 EXIT WHEN c_sub_records%NOTFOUND;
1935
1936 rownumber:=rownumber+1;
1937
1938 IF rownumber=1 THEN
1939 log(p_log_padding || ' '|| l_desc);
1940
1941 -- Fetch dict details (merge procedure)
1942 OPEN c_dict_details(l_merge_dict_id);
1943 FETCH c_dict_details INTO l_proc_name_b, l_pk_column_name_b,l_desc_column_name_b,
1944 l_fk_column_name_b, l_parent_entity_name_b;
1945
1946 -- If not found error out
1947 IF (c_dict_details%NOTFOUND or l_proc_name = null) THEN
1948 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1949 FND_MESSAGE.SET_TOKEN('ENTITY' ,l_sub_entity_name);
1950 FND_MSG_PUB.ADD;
1951 RAISE FND_API.G_EXC_ERROR;
1952 END IF;
1953 CLOSE c_dict_details;
1954 END IF;
1955
1956 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1957 IF rownumber=1 THEN
1958
1959 -- Execute the merge procedure for the entity
1960 exec_merge(l_sub_entity_name,
1961 l_proc_name_b,
1962 null,
1963 l_null_id,
1964 p_from_id,
1965 p_to_id,
1966 p_entity_name,
1967 p_batch_id,
1968 p_batch_party_id,
1969 x_return_status);
1970 log(p_log_padding || ' Bulk merge completed. Transferred '|| l_fk_column_name_b ||' from '|| p_from_id ||' to '||p_to_id);
1971 END IF;
1972
1973 -- Check if the merge procedure returned an error
1974 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1975 -- Write to log file
1976 store_merge_history(p_batch_party_id, l_sub_pk_value ,
1977 null,p_from_id, p_to_id,null,
1978 null, l_merge_dict_id, 'Transfer');
1979 ELSE
1980 log('', TRUE);
1981 log(' **** Error **** ', TRUE);
1982 log('Error: Merge failed in procedure '||l_proc_name_b||' for entity '||l_sub_entity_name,TRUE); --4634891
1983 x_error_msg := logerror;
1984 --bug 4916777
1985 IF FND_MSG_PUB.Count_Msg <=0 THEN
1986 x_error_msg := 'Error executing procedure ' || l_proc_name || ' for entity ' || p_entity_name;
1987 log(x_error_msg);
1988 END IF ;
1989 --bug 4916777
1990 -- Log the error in the table and return
1991 store_merge_log(p_batch_party_id, -1,
1992 -1,p_from_id, p_to_id,null,null,
1993 l_merge_dict_id, 'Error', x_error_msg);
1994 RETURN;
1995 END IF;
1996 ELSE
1997 log('', TRUE);
1998 log(' **** Error **** ', TRUE);
1999 x_error_msg := logerror;
2000
2001 -- Log the error in the table and return
2002 store_merge_log(p_batch_party_id, -1,
2003 -1,p_from_id, p_to_id,null,null,
2004 l_merge_dict_id, 'Error', x_error_msg);
2005 RETURN;
2006 END IF;
2007
2008 END LOOP;
2009 ELSE
2010 -- Loop through each sub-entity record
2011 LOOP
2012 EXIT WHEN l_subrec_str IS NULL;
2013 l_sub_to_id := NULL;
2014 l_sub_pk_value := NULL;
2015 FETCH c_sub_records INTO l_sub_pk_value;
2016 EXIT WHEN c_sub_records%NOTFOUND;
2017
2018 -- Fetch party details for the sub-entity
2019 OPEN c_party_details(l_sub_entity_name, l_sub_pk_value);
2020 FETCH c_party_details INTO l_sub_to_id;
2021 CLOSE c_party_details;
2022
2023 log(p_log_padding || ' '|| l_desc);
2024
2025 -- Recursive call to merge the sub-entity record
2026 do_merge(p_batch_party_id => p_batch_party_id,
2027 p_entity_name => l_sub_entity_name,
2028 p_par_entity_name=> p_entity_name,
2029 p_from_id => l_sub_pk_value,
2030 p_to_id => l_sub_to_id,
2031 p_par_from_id => p_from_id,
2032 p_par_to_id => p_to_id,
2033 p_rule_set_name => p_rule_set_name,
2034 p_batch_id => p_batch_id,
2035 p_batch_commit => p_batch_commit,
2036 p_preview => p_preview,
2037 p_dict_id => l_merge_dict_id,
2038 p_log_padding => p_log_padding || ' ',
2039 x_error_msg => x_error_msg,
2040 x_return_status => x_return_status);
2041
2042 -- If not successful abort merge and return
2043 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2044 RETURN;
2045 END IF;
2046 END LOOP;
2047 END IF;
2048 END IF;
2049 IF l_subrec_str IS NOT NULL THEN
2050 CLOSE c_sub_records;
2051 END IF;
2052
2053 END IF; /* skip_dict_id */
2054 END LOOP;
2055 CLOSE c_sub_entities;
2056 IF l_subent_cnt = 0 THEN
2057 g_num_sub_entities := 0;
2058 END IF;
2059
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2063 FND_MESSAGE.SET_TOKEN('ERROR' ,'do_merge ' || SQLERRM);
2064 FND_MSG_PUB.ADD;
2065
2066 x_error_msg:=logerror;
2067 store_merge_log(p_batch_party_id, -1,
2068 -1,p_from_id, p_to_id,null,null,
2069 l_merge_dict_id, 'Error', x_error_msg);
2070 log('Error Entity Name: '||l_sub_entity_name);
2071 log('Error FK Column: '||l_sub_fk_column_name);
2072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2073 END do_merge;
2074
2075 /*------------------------------------------------------------------------------
2076 | Procedure to lock HZ_MERGE_BATCH, HZ_MERGE_PARTIES and HZ_MERGE_PARTY_DETAILS
2077 | for a given batch
2078 |-------------------------------------------------------------------------------*/
2079
2080 PROCEDURE lock_batch(
2081 p_batch_id IN VARCHAR2,
2082 x_return_status IN OUT NOCOPY VARCHAR2) IS
2083
2084 BEGIN
2085 -- Lock merge party details
2086 EXECUTE IMMEDIATE 'SELECT batch_party_id from HZ_MERGE_PARTY_DETAILS ' ||
2087 'WHERE batch_party_id IN ' ||
2088 ' (SELECT batch_party_id from HZ_MERGE_PARTIES '||
2089 ' WHERE batch_id = :batchid ) FOR UPDATE NOWAIT' USING p_batch_id;
2090
2091 -- Lock merge parties
2092 EXECUTE IMMEDIATE 'SELECT batch_party_id from HZ_MERGE_PARTIES ' ||
2093 'WHERE batch_id = :batchid FOR UPDATE NOWAIT' USING p_batch_id;
2094
2095 -- Lock batch
2096 EXECUTE IMMEDIATE 'SELECT batch_id from HZ_MERGE_BATCH ' ||
2097 'WHERE batch_id = :batchid FOR UPDATE NOWAIT' USING p_batch_id;
2098
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_BATCH_LOCK_ERROR');
2102 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2103 FND_MSG_PUB.ADD;
2104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2105 END lock_batch;
2106
2107 /*---------------------------------------------------------------------------------------
2108 | Procedure to recursively lock all records for entities defined in the merge dictionary
2109 | for a given party id or party site id
2110 |---------------------------------------------------------------------------------------*/
2111 PROCEDURE lock_records(
2112 p_entity_name IN VARCHAR2,
2113 p_pk_column_name IN VARCHAR2,
2114 p_fk_column_name IN VARCHAR2,
2115 p_join_str IN VARCHAR2,
2116 p_join_clause IN VARCHAR2,
2117 p_rule_set_name IN VARCHAR2,
2118 x_return_status IN OUT NOCOPY VARCHAR2) IS
2119
2120 -- Dummy join clause
2121 l_join_clause VARCHAR2(2000) := '1=1';
2122
2123 l_sub_ent_name HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE;
2124 l_sub_fkcol HZ_MERGE_DICTIONARY.FK_COLUMN_NAME%TYPE;
2125 l_sub_pkcol HZ_MERGE_DICTIONARY.PK_COLUMN_NAME%TYPE;
2126 l_sub_joincl VARCHAR2(2000);
2127
2128 lockstr VARCHAR2(32000);
2129 str_lockstr VARCHAR2(32000);
2130 l_sqlstr VARCHAR2(2000);
2131 l_pmerge_apps VARCHAR2(2000) := NULL;
2132 l_app_name VARCHAR2(100);
2133 l_app_id NUMBER;
2134
2135 TYPE SubEntType IS REF CURSOR;
2136 c_dict_details SubEntType;
2137
2138 cursor app_name(app_id NUMBER) IS
2139 Select application_short_name from fnd_application where application_id=app_id;
2140
2141 BEGIN
2142
2143 -- Dynamically constructed SQL statement for locking all records
2144 -- The where clause in this statement builds up with each recursive call
2145 IF p_join_clause is not null THEN
2146 lockstr := 'SELECT ' || p_pk_column_name || ' FROM ' ||
2147 p_entity_name || ' WHERE ' ||
2148 p_fk_column_name || ' IN ' || p_join_str || ' AND ' ||
2149 replace(upper(p_join_clause), 'GROUP BY ' ||
2150 upper(p_pk_column_name));
2151 ELSE
2152 lockstr := 'SELECT ' || p_pk_column_name || ' FROM ' ||
2153 p_entity_name || ' WHERE ' || p_fk_column_name ||
2154 ' IN ' || p_join_str;
2155 END IF;
2156
2157 BEGIN
2158 -- Execute the dynamic SQL statement and lock records
2159 EXECUTE IMMEDIATE lockstr || ' FOR UPDATE NOWAIT';
2160 EXCEPTION
2161 WHEN OTHERS THEN
2162 IF SQLCODE=-904 THEN
2163 lockstr:=null;
2164 ELSIF SQLCODE=-942 THEN
2165 lockstr:=null;
2166 ELSE
2167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2168 END IF;
2169 END;
2170
2171 IF lockstr IS NULL THEN
2172 RETURN;
2173 END IF;
2174
2175 IF lockstr IS NOT NULL THEN
2176 IF FND_PROFILE.VALUE('HZ_PARTY_MERGE_APPLICATIONS') IS NOT NULL THEN
2177 l_pmerge_apps := '(' || G_TCA_APP_ID || ', ' ||
2178 FND_PROFILE.VALUE('HZ_PARTY_MERGE_APPLICATIONS') || ')';
2179 OPEN c_dict_details FOR
2180 'SELECT ENTITY_NAME, PK_COLUMN_NAME, FK_COLUMN_NAME, JOIN_CLAUSE, DICT_APPLICATION_ID'||
2181 'FROM HZ_MERGE_DICTIONARY ' ||
2182 'WHERE PARENT_ENTITY_NAME = :pentity' ||
2183 ' AND RULE_SET_NAME = :ruleset '||
2184 ' AND DICT_APPLICATION_ID IN ' || l_pmerge_apps ||
2185 ' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
2186 ELSE
2187 OPEN c_dict_details FOR
2188 'SELECT ENTITY_NAME, PK_COLUMN_NAME, FK_COLUMN_NAME, JOIN_CLAUSE, DICT_APPLICATION_ID '||
2189 'FROM HZ_MERGE_DICTIONARY ' ||
2190 'WHERE PARENT_ENTITY_NAME = :pentity' ||
2191 ' AND RULE_SET_NAME = :ruleset '||
2192 ' ORDER BY SEQUENCE_NO ' USING p_entity_name, p_rule_set_name;
2193 END IF;
2194 END IF;
2195
2196
2197 -- Loop through all sub entities
2198 LOOP
2199 EXIT WHEN lockstr is null;
2200 FETCH c_dict_details INTO l_sub_ent_name, l_sub_pkcol, l_sub_fkcol,
2201 l_sub_joincl,l_app_id;
2202 EXIT WHEN c_dict_details%NOTFOUND;
2203 open app_name(l_app_id);
2204 fetch app_name into l_app_name;
2205 close app_name;
2206 -- Recursive call to lock records for each sub-entity
2207 -- (NOTE: The key here is passing the current "lockstr" to the recursive
2208 -- call .. this is added as part of the nested where clause in the next leve)
2209 IF get_col_type(l_sub_ent_name, l_sub_fkcol,l_app_name) = 'VARCHAR2' THEN
2210 str_lockstr := 'SELECT TO_CHAR(' || p_pk_column_name || ') FROM ' ||
2211 p_entity_name || ' WHERE ' || p_fk_column_name || ' IN ' ||
2212 p_join_str || ' AND ' || l_join_clause;
2213 lock_records(l_sub_ent_name, l_sub_pkcol, l_sub_fkcol, '(' ||str_lockstr
2214 ||')', l_sub_joincl,p_rule_set_name, x_return_status);
2215 ELSE
2216 lock_records(l_sub_ent_name, l_sub_pkcol, l_sub_fkcol, '(' ||lockstr
2217 ||')', l_sub_joincl,p_rule_set_name, x_return_status);
2218 END IF;
2219
2220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2221 RETURN;
2222 END IF;
2223
2224 END LOOP;
2225 CLOSE c_dict_details;
2226 EXCEPTION
2227 WHEN OTHERS THEN
2228 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_LOCK_ERROR');
2229 FND_MESSAGE.SET_TOKEN('ENTITY', p_entity_name);
2230 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM || ' : ' || lockstr || ' subent ' || l_sub_ent_name);
2231 FND_MSG_PUB.ADD;
2232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2233 END lock_records;
2234
2235 /*------------------------------------------------------------------------------
2236 | Procedure to delete the merged records for a batch party
2237 |------------------------------------------------------------------------------*/
2238 PROCEDURE delete_merged_records(
2239 p_batch_party_id IN NUMBER,
2240 x_return_status IN OUT NOCOPY VARCHAR2) IS
2241
2242 CURSOR c_deleted_records IS
2243 SELECT h.from_entity_id, d.entity_name, d.pk_column_name
2244 FROM HZ_MERGE_PARTY_HISTORY h, HZ_MERGE_DICTIONARY d
2245 WHERE h.merge_dict_id = d.merge_dict_id
2246 AND h.batch_party_id = p_batch_party_id
2247 AND h.request_id = hz_utility_pub.request_id
2248 AND h.operation_type = 'Merge'
2249 ORDER BY h.merge_dict_id desc;
2250
2251 l_record_id HZ_MERGE_PARTY_HISTORY.FROM_ENTITY_ID%TYPE;
2252 l_entity_name HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE;
2253 l_pkcol HZ_MERGE_DICTIONARY.PK_COLUMN_NAME%TYPE;
2254
2255 rec_delete VARCHAR2(2000);
2256
2257 BEGIN
2258
2259 log('Deleting merged records');
2260
2261 OPEN c_deleted_records;
2262 LOOP
2263 FETCH c_deleted_records INTO l_record_id, l_entity_name, l_pkcol;
2264
2265 EXIT WHEN c_deleted_records%NOTFOUND;
2266
2267 IF l_entity_name = 'HZ_PARTIES' OR
2268 l_entity_name = 'HZ_PARTY_SITES' OR
2269 l_entity_name = 'HZ_ORGANIZATION_PROFILES' OR
2270 l_entity_name = 'HZ_PERSON_PROFILES' OR
2271 l_entity_name = 'HZ_ORG_CONTACTS' OR
2272 l_entity_name = 'HZ_PARTY_USG_ASSIGNMENTS' --4307667
2273 THEN
2274 rec_delete := 'UPDATE ' || l_entity_name ||
2275 ' SET STATUS = ''D'' WHERE ' ||
2276 l_pkcol || ' = :pk';
2277 ELSIF l_entity_name = 'HZ_PARTY_RELATIONSHIPS' THEN --4500011
2278 rec_delete := 'UPDATE HZ_RELATIONSHIPS' ||
2279 ' SET STATUS = ''D'' WHERE ' ||
2280 'RELATIONSHIP_ID' || ' = :pk'||'AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
2281 ELSE
2282 -- Construct dynamic SQL query to fetch description
2283 rec_delete := 'DELETE FROM ' || l_entity_name ||
2284 ' WHERE ' || l_pkcol || ' = :pk';
2285 END IF;
2286 EXECUTE IMMEDIATE rec_delete USING l_record_id;
2287 END LOOP;
2288 log('Delete complete');
2289 EXCEPTION
2290 WHEN OTHERS THEN
2291 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_DELETE_ERROR');
2292 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2293 FND_MSG_PUB.ADD;
2294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2295 END delete_merged_records;
2296
2297 /*-------------------------------------------------------------------
2298 | Function to fetch a record description
2299 |--------------------------------------------------------------------*/
2300
2301 FUNCTION get_record_desc(
2302 p_record_pk IN NUMBER,
2303 p_entity_name IN VARCHAR2,
2304 p_pk_col_name IN VARCHAR2,
2305 p_desc_col_name IN VARCHAR2,
2306 x_return_status IN OUT NOCOPY VARCHAR2)
2307 RETURN VARCHAR2 IS
2308
2309 rec_query VARCHAR2(2000);
2310 l_desc VARCHAR2(2000);
2311
2312 BEGIN
2313
2314 IF p_desc_col_name = p_pk_col_name THEN
2315 RETURN TO_CHAR(p_record_pk);
2316 END IF;
2317
2318 -- Counstruct dynamic SQL query to fetch description
2319 --4500011
2320
2321 IF (p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
2322 rec_query := 'SELECT hz_merge_util.get_party_reln_description(relationship_id) FROM HZ_RELATIONSHIPS'
2323 || ' WHERE RELATIONSHIP_ID = '|| ':pk'
2324 || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
2325
2326 ELSE
2327 rec_query := 'SELECT ' || p_desc_col_name ||
2328 ' FROM ' || p_entity_name || ' WHERE ' ||
2329 p_pk_col_name || ' = :pk';
2330 END IF;
2331
2332 -- Execute dynamic SQL query
2333 EXECUTE IMMEDIATE rec_query INTO l_desc USING p_record_pk;
2334 RETURN '"' || l_desc || '" (ID:' || p_record_pk || ')';
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 FND_MESSAGE.SET_NAME('AR', 'HZ_REC_DESC_ERROR');
2338 FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
2339 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2340 FND_MSG_PUB.ADD;
2341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2342 RETURN NULL;
2343 END get_record_desc;
2344
2345 /*------------------------------------------------------------------------------
2346 | Function to fetch a record description
2347 |------------------------------------------------------------------------------*/
2348
2349 FUNCTION get_record_desc_r(
2350 p_record_pk IN ROWID,
2351 p_entity_name IN VARCHAR2,
2352 p_desc_col_name IN VARCHAR2,
2353 x_return_status IN OUT NOCOPY VARCHAR2)
2354 RETURN VARCHAR2 IS
2355
2356 rec_query VARCHAR2(2000);
2357 l_desc VARCHAR2(2000);
2358
2359 BEGIN
2360
2361 IF p_desc_col_name IS NULL THEN
2362 RETURN p_record_pk;
2363 END IF;
2364
2365 -- Construct dynamic SQL query to fetch description
2366 rec_query := 'SELECT ' || p_desc_col_name ||
2367 ' FROM ' || p_entity_name || ' WHERE ROWID = :pk';
2368
2369 -- Execute dynamic SQL query
2370 EXECUTE IMMEDIATE rec_query INTO l_desc USING p_record_pk;
2371 RETURN '"' || l_desc || '" (ROWID:' || p_record_pk || ')';
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 FND_MESSAGE.SET_NAME('AR', 'HZ_REC_DESC_ERROR');
2375 FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
2376 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2377 FND_MSG_PUB.ADD;
2378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2379 RETURN NULL;
2380 END get_record_desc_r;
2381
2382
2383 /*------------------------------------------------------------------
2384 | Procedure to execute the merge procedure using Dynamic SQL
2385 |------------------------------------------------------------------*/
2386
2387 PROCEDURE exec_merge(
2388 p_entity_name IN VARCHAR2,
2389 p_proc_name IN HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE,
2390 p_from_id IN NUMBER,
2391 x_to_id IN OUT NOCOPY NUMBER,
2392 p_par_from_id IN NUMBER,
2393 p_par_to_id IN NUMBER,
2394 p_parent_entity IN HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE,
2395 p_batch_id IN NUMBER,
2396 p_batch_party_id IN NUMBER,
2397 x_return_status IN OUT NOCOPY VARCHAR2) IS
2398
2399 plsql_block VARCHAR2(400);
2400 l_return_status VARCHAR2(30);
2401 r NUMBER;
2402
2403 BEGIN
2404
2405 l_return_status := FND_API.G_RET_STS_SUCCESS;
2406 IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2407 IF g_cur_proc_name IS NOT NULL THEN
2408 dbms_sql.close_cursor(g_proc_cursor);
2409 END IF;
2410 g_cur_proc_name := p_proc_name;
2411 -- Create a dynamic SQL block to execute the merge procedure
2412 plsql_block := 'BEGIN '||
2413 p_proc_name||'(:p_entity_name, :from_id,'||
2414 ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2415 ':batch_party_id,:x_return_status);'||
2416 'END;';
2417
2418 g_proc_cursor := dbms_sql.open_cursor;
2419 dbms_sql.parse(g_proc_cursor, plsql_block, 2);
2420 END IF;
2421
2422 dbms_sql.bind_variable(g_proc_cursor, 'p_entity_name', p_entity_name);
2423 dbms_sql.bind_variable(g_proc_cursor, 'from_id', p_from_id);
2424 dbms_sql.bind_variable(g_proc_cursor, 'to_id', x_to_id);
2425 dbms_sql.bind_variable(g_proc_cursor, 'par_from_id', p_par_from_id);
2426 dbms_sql.bind_variable(g_proc_cursor, 'par_to_id', p_par_to_id);
2427 dbms_sql.bind_variable(g_proc_cursor, 'par_entity', p_parent_entity);
2428 dbms_sql.bind_variable(g_proc_cursor, 'batch_id', p_batch_id);
2429 dbms_sql.bind_variable(g_proc_cursor, 'batch_party_id', p_batch_party_id);
2430 dbms_sql.bind_variable(g_proc_cursor, 'x_return_status', l_return_status);
2431 r := dbms_sql.execute(g_proc_cursor);
2432 dbms_sql.variable_value(g_proc_cursor,'to_id',x_to_id);
2433 dbms_sql.variable_value(g_proc_cursor,'x_return_status',l_return_status);
2434
2435 x_return_status := l_return_status;
2436
2437 /*
2438 l_return_status := FND_API.G_RET_STS_SUCCESS;
2439 -- Create a dynamic SQL block to execute the merge procedure
2440 plsql_block := 'BEGIN '||
2441 p_proc_name||'(:p_entity_name, :from_id,'||
2442 ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2443 ':batch_party_id,:x_return_status);'||
2444 'END;';
2445
2446 -- Execute the dynamic PLSQL block
2447 EXECUTE IMMEDIATE plsql_block USING
2448 p_entity_name, p_from_id, IN OUT NOCOPY x_to_id, p_par_from_id, p_par_to_id,
2449 p_parent_entity, p_batch_id,p_batch_party_id,IN OUT NOCOPY l_return_status;
2450
2451 -- Set return status
2452 x_return_status := l_return_status;
2453 */
2454
2455 EXCEPTION
2456 WHEN OTHERS THEN
2457 log('');
2458 log('');
2459 log('****** Error executing merge procedure : ' || p_proc_name );
2460 log('for table : ' || p_entity_name);
2461 log('Data exists in table for the merge-to party. Aborting merge');
2462 log('');
2463 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2464 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2465 FND_MSG_PUB.ADD;
2466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2467 END exec_merge;
2468
2469 /*-----------------------------------------------------------------
2470 | Procedure to execute the merge procedure using Dynamic SQL
2471 |-----------------------------------------------------------------*/
2472
2473 PROCEDURE exec_merge_r(
2474 p_entity_name IN VARCHAR2,
2475 p_proc_name IN HZ_MERGE_DICTIONARY.PROCEDURE_NAME%TYPE,
2476 p_from_id IN ROWID,
2477 x_to_id IN OUT NOCOPY ROWID,
2478 p_par_from_id IN NUMBER,
2479 p_par_to_id IN NUMBER,
2480 p_parent_entity IN HZ_MERGE_DICTIONARY.ENTITY_NAME%TYPE,
2481 p_batch_id IN NUMBER,
2482 p_batch_party_id IN NUMBER,
2483 x_return_status IN OUT NOCOPY VARCHAR2) IS
2484
2485 plsql_block VARCHAR2(400);
2486 l_return_status VARCHAR2(30);
2487 r NUMBER;
2488
2489 BEGIN
2490
2491 l_return_status := FND_API.G_RET_STS_SUCCESS;
2492 IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2493 IF g_cur_proc_name IS NOT NULL THEN
2494 dbms_sql.close_cursor(g_proc_cursor);
2495 END IF;
2496 g_cur_proc_name := p_proc_name;
2497 -- Create a dynamic SQL block to execute the merge procedure
2498 plsql_block := 'BEGIN '||
2499 p_proc_name||'(:p_entity_name, :from_id,'||
2500 ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2501 ':batch_party_id,:x_return_status);'||
2502 'END;';
2503
2504 g_proc_cursor := dbms_sql.open_cursor;
2505 dbms_sql.parse(g_proc_cursor, plsql_block, 2);
2506 END IF;
2507
2508 dbms_sql.bind_variable(g_proc_cursor, 'p_entity_name', p_entity_name);
2509 dbms_sql.bind_variable(g_proc_cursor, 'from_id', p_from_id);
2510 dbms_sql.bind_variable(g_proc_cursor, 'to_id', x_to_id);
2511 dbms_sql.bind_variable(g_proc_cursor, 'par_from_id', p_par_from_id);
2512 dbms_sql.bind_variable(g_proc_cursor, 'par_to_id', p_par_to_id);
2513 dbms_sql.bind_variable(g_proc_cursor, 'par_entity', p_parent_entity);
2514 dbms_sql.bind_variable(g_proc_cursor, 'batch_id', p_batch_id);
2515 dbms_sql.bind_variable(g_proc_cursor, 'batch_party_id', p_batch_party_id);
2516 dbms_sql.bind_variable(g_proc_cursor, 'x_return_status', l_return_status);
2517 r := dbms_sql.execute(g_proc_cursor);
2518 dbms_sql.variable_value(g_proc_cursor,'to_id',x_to_id);
2519 dbms_sql.variable_value(g_proc_cursor,'x_return_status',l_return_status);
2520
2521 x_return_status := l_return_status;
2522
2523 /*
2524 l_return_status := FND_API.G_RET_STS_SUCCESS;
2525 -- Create a dynamic SQL block to execute the merge procedure
2526 plsql_block := 'BEGIN '||
2527 p_proc_name||'(:p_entity_name, :from_id,'||
2528 ':to_id, :par_from_id,:par_to_id,:par_entity, :batch_id, '||
2529 ':batch_party_id,:x_return_status);'||
2530 'END;';
2531
2532 -- Execute the dynamic PLSQL block
2533 EXECUTE IMMEDIATE plsql_block USING
2534 p_entity_name, p_from_id, IN OUT NOCOPY x_to_id, p_par_from_id, p_par_to_id,
2535 p_parent_entity, p_batch_id,p_batch_party_id,IN OUT NOCOPY l_return_status;
2536
2537 -- Set return status
2538 x_return_status := l_return_status;
2539 */
2540
2541 EXCEPTION
2542 WHEN OTHERS THEN
2543 log('');
2544 log('');
2545 log('****** Error executing merge procedure : ' || p_proc_name );
2546 log('for table : ' || p_entity_name);
2547 log('Data exists in table for the merge-to party. Aborting merge');
2548 log('');
2549 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2550 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2551 FND_MSG_PUB.ADD;
2552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2553 END exec_merge_r;
2554
2555 --bug 4634891 created overloaded procedure exec_merge
2556 /*------------------------------------------------------------------
2557 | Procedure to execute the batch merge procedure using Dynamic SQL
2558 |------------------------------------------------------------------*/
2559
2560 PROCEDURE exec_merge(
2561 p_entity_name IN VARCHAR2,
2562 p_proc_name IN VARCHAR2,
2563 p_batch_id IN NUMBER,
2564 p_request_id IN NUMBER,
2565 x_return_status IN OUT NOCOPY VARCHAR2) IS
2566
2567 plsql_block VARCHAR2(400);
2568 l_return_status VARCHAR2(30);
2569 r NUMBER;
2570
2571 BEGIN
2572
2573 l_return_status := FND_API.G_RET_STS_SUCCESS;
2574 IF p_proc_name <> g_cur_proc_name OR g_cur_proc_name IS NULL THEN
2575 IF g_cur_proc_name IS NOT NULL THEN
2576 dbms_sql.close_cursor(g_proc_cursor);
2577 END IF;
2578 g_cur_proc_name := p_proc_name;
2579 -- Create a dynamic SQL block to execute the merge procedure
2580 plsql_block := 'BEGIN '||
2581 p_proc_name||'(:batch_id, '||
2582 ':request_id,:x_return_status);'||
2583 'END;';
2584
2585 g_proc_cursor := dbms_sql.open_cursor;
2586 dbms_sql.parse(g_proc_cursor, plsql_block, 2);
2587 END IF;
2588
2589
2590 dbms_sql.bind_variable(g_proc_cursor, 'batch_id', p_batch_id);
2591 dbms_sql.bind_variable(g_proc_cursor, 'request_id', p_request_id);
2592 dbms_sql.bind_variable(g_proc_cursor, 'x_return_status', l_return_status);
2593 r := dbms_sql.execute(g_proc_cursor);
2594 dbms_sql.variable_value(g_proc_cursor,'x_return_status',l_return_status);
2595
2596 x_return_status := l_return_status;
2597
2598 EXCEPTION
2599 WHEN OTHERS THEN
2600 log('');
2601 log('');
2602 log('****** Error executing merge procedure : ' || p_proc_name );
2603 log('for table : ' || p_entity_name);
2604 log('Data exists in table for the merge-to party. Aborting merge');
2605 log('');
2606 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2607 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2608 FND_MSG_PUB.ADD;
2609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2610 END exec_merge;
2611
2612 /*------------------------------------------------------------------------
2613 | Procedure to store merge history record
2614 |------------------------------------------------------------------------*/
2615
2616 PROCEDURE store_merge_history(
2617 p_batch_party_id IN HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE,
2618 p_from_id IN HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE,
2619 p_to_id IN HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE,
2620 p_from_fk_id IN HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE,
2621 p_to_fk_id IN HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE,
2622 p_from_desc IN HZ_MERGE_PARTY_HISTORY.FROM_ENTITY_DESC%TYPE,
2623 p_to_desc IN HZ_MERGE_PARTY_HISTORY.TO_ENTITY_DESC%TYPE,
2624 p_merge_dict_id IN HZ_MERGE_DICTIONARY.MERGE_DICT_ID%TYPE,
2625 p_op_type IN HZ_MERGE_PARTY_HISTORY.OPERATION_TYPE%TYPE,
2626 p_flush IN VARCHAR2 := 'N') IS
2627
2628 BEGIN
2629
2630 IF p_flush <> 'Y' THEN
2631 -- Store in the log table
2632 store_merge_log(
2633 p_batch_party_id, p_from_id, p_to_id, p_from_fk_id, p_to_fk_id,
2634 p_from_desc, p_to_desc, p_merge_dict_id, p_op_type);
2635 END IF;
2636
2637 IF H_Counter = 1000 OR p_flush = 'Y' THEN
2638 FORALL I IN 1..H_Counter
2639 -- Store in the history table
2640 INSERT INTO HZ_MERGE_PARTY_HISTORY(
2641 batch_party_id,
2642 request_id,
2643 from_entity_id,
2644 to_entity_id,
2645 from_parent_entity_id,
2646 to_parent_entity_id,
2647 from_entity_desc,
2648 to_entity_desc,
2649 merge_dict_id,
2650 operation_type,
2651 created_by,
2652 creation_date,
2653 last_update_login,
2654 last_update_date,
2655 last_updated_by)
2656 VALUES (
2657 H_batch_party_id(I),
2658 g_request_id, -- Bug No : 2998004 hz_utility_pub.request_id,
2659 H_from_id(I),
2660 H_to_id(I),
2661 H_from_fk_id(I),
2662 H_to_fk_id(I),
2663 H_from_desc(I),
2664 H_to_desc(I),
2665 H_merge_dict_id(I),
2666 H_op_type(I),
2667 g_created_by, -- hz_utility_pub.created_by,
2668 g_creation_date, -- hz_utility_pub.creation_date,
2669 g_last_update_login, -- hz_utility_pub.last_update_login,
2670 g_last_update_date, -- hz_utility_pub.last_update_date,
2671 g_user_id -- hz_utility_pub.user_id
2672 );
2673 H_Counter := 0;
2674 END IF;
2675 IF p_flush = 'Y' THEN
2676 RETURN;
2677 END IF;
2678
2679 H_Counter := H_Counter+1;
2680 H_batch_party_id(H_Counter) := p_batch_party_id;
2681 IF p_from_id=FND_API.G_MISS_NUM THEN
2682 H_from_id(H_Counter) := null;
2683 ELSE
2684 H_from_id(H_Counter) := p_from_id;
2685 END IF;
2686 IF p_to_id=FND_API.G_MISS_NUM THEN
2687 H_to_id(H_Counter) := null;
2688 ELSE
2689 H_to_id(H_Counter) := p_to_id;
2690 END IF;
2691 IF p_from_fk_id=FND_API.G_MISS_NUM THEN
2692 H_from_fk_id(H_Counter) := null;
2693 ELSE
2694 H_from_fk_id(H_Counter) := p_from_fk_id;
2695 END IF;
2696 IF p_to_fk_id=FND_API.G_MISS_NUM THEN
2697 H_to_fk_id(H_Counter) := null;
2698 ELSE
2699 H_to_fk_id(H_Counter) := p_to_fk_id;
2700 END IF;
2701 H_from_desc(H_Counter) := p_from_desc;
2702 H_to_desc(H_Counter) := p_to_desc;
2703 H_merge_dict_id(H_Counter) := p_merge_dict_id;
2704 H_op_type(H_Counter) := p_op_type;
2705
2706 EXCEPTION
2707 WHEN OTHERS THEN
2708 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2709 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2710 FND_MSG_PUB.ADD;
2711 RAISE FND_API.G_EXC_ERROR;
2712 END store_merge_history;
2713
2714 /*---------------------------------------------------------------------------
2715 | Procedure to store the merge procedure in the log table.
2716 | Executes in an autonomous transaction since we always want this to
2717 | be commited irrespective of the merge mode or result
2718 |---------------------------------------------------------------------------*/
2719
2720 PROCEDURE store_merge_log(
2721 p_batch_party_id IN HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE,
2722 p_from_id IN HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE,
2723 p_to_id IN HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE,
2724 p_from_fk_id IN HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE,
2725 p_to_fk_id IN HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE,
2726 p_from_desc IN HZ_MERGE_PARTY_HISTORY.FROM_ENTITY_DESC%TYPE,
2727 p_to_desc IN HZ_MERGE_PARTY_HISTORY.TO_ENTITY_DESC%TYPE,
2728 p_merge_dict_id IN HZ_MERGE_DICTIONARY.MERGE_DICT_ID%TYPE,
2729 p_op_type IN HZ_MERGE_PARTY_LOG.OPERATION_TYPE%TYPE,
2730 p_error IN HZ_MERGE_PARTY_LOG.ERROR_MESSAGES%TYPE
2731 DEFAULT NULL,
2732 p_flush IN VARCHAR2 := 'N') IS
2733 PRAGMA AUTONOMOUS_TRANSACTION;
2734 BEGIN
2735
2736 IF I_Counter = 1000 OR p_flush = 'Y' THEN
2737 FORALL I IN 1..I_Counter
2738 -- Insert inyo the log table
2739 INSERT INTO HZ_MERGE_PARTY_LOG(
2740 batch_party_id,
2741 request_id,
2742 from_entity_id,
2743 to_entity_id,
2744 from_parent_entity_id,
2745 to_parent_entity_id,
2746 from_entity_desc,
2747 to_entity_desc,
2748 merge_dict_id,
2749 error_messages,
2750 operation_type,
2751 created_by,
2752 creation_date,
2753 last_update_login,
2754 last_update_date,
2755 last_updated_by)
2756 VALUES (
2757 I_batch_party_id(I),
2758 g_request_id, -- Bug No : 2998004 hz_utility_pub.request_id,
2759 I_from_id(I),
2760 I_to_id(I),
2761 I_from_fk_id(I),
2762 I_to_fk_id(I),
2763 I_from_desc(I),
2764 I_to_desc(I),
2765 I_merge_dict_id(I),
2766 I_error(I),
2767 I_op_type(I),
2768 g_created_by, -- hz_utility_pub.created_by,
2769 g_creation_date, -- hz_utility_pub.creation_date,
2770 g_last_update_login, -- hz_utility_pub.last_update_login,
2771 g_last_update_date, -- hz_utility_pub.last_update_date,
2772 g_user_id -- hz_utility_pub.user_id
2773 );
2774
2775 I_Counter := 0;
2776 -- Commit the log entry
2777 COMMIT;
2778 END IF;
2779
2780 I_Counter := I_Counter+1;
2781 I_batch_party_id(I_Counter) := p_batch_party_id;
2782 IF p_from_id=FND_API.G_MISS_NUM THEN
2783 I_from_id(I_Counter) := null;
2784 ELSE
2785 I_from_id(I_Counter) := p_from_id;
2786 END IF;
2787 IF p_to_id=FND_API.G_MISS_NUM THEN
2788 I_to_id(I_Counter) := null;
2789 ELSE
2790 I_to_id(I_Counter) := p_to_id;
2791 END IF;
2792 IF p_from_fk_id=FND_API.G_MISS_NUM THEN
2793 I_from_fk_id(I_Counter) := null;
2794 ELSE
2795 I_from_fk_id(I_Counter) := p_from_fk_id;
2796 END IF;
2797 IF p_to_fk_id=FND_API.G_MISS_NUM THEN
2798 I_to_fk_id(I_Counter) := null;
2799 ELSE
2800 I_to_fk_id(I_Counter) := p_to_fk_id;
2801 END IF;
2802
2803 I_from_desc(I_Counter) := p_from_desc;
2804 I_to_desc(I_Counter) := p_to_desc;
2805 I_merge_dict_id(I_Counter) := p_merge_dict_id;
2806 I_op_type(I_Counter) := p_op_type;
2807 I_error(I_Counter) := p_error;
2808
2809 EXCEPTION
2810 WHEN OTHERS THEN
2811 -- ROLLBACK to batch_merge;
2812 ROLLBACK; -- bug 3947633
2813 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
2814 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2815 FND_MSG_PUB.ADD;
2816 RAISE FND_API.G_EXC_ERROR;
2817 END store_merge_log;
2818
2819 /*---------------------------------------------------------------------------
2820 | Procedure to write a message to the out NOCOPY file
2821 |----------------------------------------------------------------------------*/
2822
2823 PROCEDURE out(
2824 message IN VARCHAR2,
2825 newline IN BOOLEAN DEFAULT TRUE) IS
2826 BEGIN
2827 IF message = 'NEWLINE' THEN
2828 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
2829 ELSIF (newline) THEN
2830 FND_FILE.put_line(fnd_file.output,message);
2831 ELSE
2832 FND_FILE.put(fnd_file.output,message);
2833 END IF;
2834 END out;
2835
2836 /*------------------------------------------------------------------------
2837 | Procedure to write a message to the log file
2838 |------------------------------------------------------------------------*/
2839
2840 PROCEDURE log(
2841 message IN VARCHAR2,
2842 newline IN BOOLEAN DEFAULT TRUE) IS
2843 BEGIN
2844
2845 IF message = 'NEWLINE' THEN
2846 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
2847 ELSIF (newline) THEN
2848 FND_FILE.put_line(fnd_file.log,message);
2849 ELSE
2850 FND_FILE.put(fnd_file.log,message);
2851 END IF;
2852 END log;
2853
2854 /*------------------------------------------------------------------------
2855 | Procedure to write a message to the out NOCOPY and log files
2856 |-------------------------------------------------------------------------*/
2857
2858 PROCEDURE outandlog(
2859 message IN VARCHAR2,
2860 newline IN BOOLEAN DEFAULT TRUE) IS
2861 BEGIN
2862 out(message, newline);
2863 log(message, newline);
2864 END outandlog;
2865
2866 /*--------------------------------------------------------------------------
2867 | NAME: pre_merge
2868 | PARAMETERS: p_to_party_id IN NUMBER and p_batch_id IN NUMBER
2869 | DESCRIPTION: This procedure transfers the party site, contacts and
2870 | contact points to the to_party_id from the from party if
2871 | the from party is getting transferred and the another record
2872 | is merging into that record.
2873 ----------------------------------------------------------------------------*/
2874 PROCEDURE pre_merge(
2875 p_to_party_id IN NUMBER,
2876 p_batch_id IN NUMBER) IS
2877
2878 CURSOR pre_batch(cp_batch_id NUMBER) IS
2879 SELECT batch_name, rule_set_name ,batch_status, batch_delete, batch_commit
2880 FROM HZ_MERGE_BATCH
2881 WHERE batch_id = cp_batch_id;
2882
2883
2884 CURSOR party_sites_for_pre_merge(cp_to_party_id IN NUMBER, cp_batch_id IN NUMBER) IS
2885 select merge_to_entity_id, p.from_party_id, p.batch_party_id
2886 from hz_merge_party_details d1, hz_merge_parties p
2887 where entity_name = 'HZ_PARTY_SITES'
2888 and p.batch_party_id = d1.batch_party_id
2889 and d1.batch_party_id IN ( select batch_party_id
2890 from hz_merge_parties
2891 where to_party_id = cp_to_party_id
2892 and to_party_id<>from_party_id
2893 and batch_id = cp_batch_id)
2894 and merge_from_entity_id = merge_to_entity_id --transfer operation
2895 and exists ( --it should be a merge-to
2896 select 1 --for another mapping
2897 from hz_merge_party_details d2
2898 where d2.merge_to_entity_id = d1.merge_from_entity_id
2899 and d2.entity_name = 'HZ_PARTY_SITES'
2900 and batch_party_id IN ( select batch_party_id
2901 from hz_merge_parties
2902 where to_party_id = cp_to_party_id
2903 and to_party_id<>from_party_id
2904 and batch_id = cp_batch_id)
2905 and d2.merge_from_entity_id <> d1.merge_to_entity_id );
2906
2907 CURSOR rel_for_pre_merge(cp_to_party_id IN NUMBER, cp_batch_id IN NUMBER) IS
2908 select merge_to_entity_id, p.from_party_id, p.batch_party_id
2909 from hz_merge_party_details d1, hz_merge_parties p
2910 where entity_name = 'HZ_PARTY_RELATIONSHIPS'
2911 and p.batch_party_id = d1.batch_party_id
2912 and d1.batch_party_id IN ( select batch_party_id
2913 from hz_merge_parties
2914 where to_party_id = cp_to_party_id
2915 and to_party_id<>from_party_id
2916 and batch_id = cp_batch_id)
2917 and merge_from_entity_id = merge_to_entity_id --transfer operation
2918 and exists ( --it should be a merge-to
2919 select 1 --for another mapping
2920 from hz_merge_party_details d2
2921 where d2.merge_to_entity_id = d1.merge_from_entity_id
2922 and d2.entity_name = 'HZ_PARTY_RELATIONSHIPS'
2923 and batch_party_id IN (
2924 select batch_party_id
2925 from hz_merge_parties
2926 where to_party_id = cp_to_party_id
2927 and to_party_id<>from_party_id
2928 and batch_id = cp_batch_id)
2929 and d2.merge_from_entity_id <> d1.merge_to_entity_id );
2930
2931 l_batch_name HZ_MERGE_BATCH.BATCH_NAME%TYPE;
2932 l_rule_set_name HZ_MERGE_BATCH.RULE_SET_NAME%TYPE;
2933 l_batch_status HZ_MERGE_BATCH.BATCH_STATUS%TYPE;
2934 l_merge_status HZ_MERGE_PARTIES.MERGE_STATUS%TYPE;
2935
2936 l_batch_commit HZ_MERGE_BATCH.BATCH_COMMIT%TYPE;
2937 l_batch_delete HZ_MERGE_BATCH.BATCH_DELETE%TYPE;
2938
2939 l_batch_party_id HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE;
2940 l_from_id HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE;
2941 l_merge_to_entity_id hz_merge_party_details.merge_to_entity_id%TYPE;
2942 l_new_party_site_id hz_party_sites.party_site_id%type;
2943 l_proc_name hz_merge_dictionary.procedure_name%type;
2944 l_subject_id HZ_RELATIONSHIPS.SUBJECT_ID%TYPE;
2945 l_object_id HZ_RELATIONSHIPS.OBJECT_ID%TYPE;
2946
2947 l_return_status VARCHAR2(200);
2948
2949 l_from_party_id NUMBER;
2950 l_from_rec_desc varchar2(2000);
2951 l_to_rec_desc varchar2(2000);
2952 pre_return_status VARCHAR2(200);
2953 pre_log_padding VARCHAR2(2000) := ' ';
2954
2955 l_merge_dict_id NUMBER;
2956
2957 BEGIN
2958
2959 --- Open the batch cursor and fetch batch details---
2960 OPEN pre_batch(p_batch_id);
2961 FETCH pre_batch INTO l_batch_name, l_rule_set_name,
2962 l_batch_status, l_batch_delete,l_batch_commit;
2963 IF (pre_batch%NOTFOUND) THEN
2964 FND_MESSAGE.SET_NAME('AR', 'HZ_BATCH_NOTFOUND');
2965 FND_MESSAGE.SET_TOKEN('BATCHID', p_batch_id);
2966 FND_MSG_PUB.ADD;
2967 RAISE FND_API.G_EXC_ERROR;
2968 END IF;
2969 CLOSE pre_batch;
2970
2971 --Initialise the log variables
2972 l_from_rec_desc := null;
2973 l_to_rec_desc := null;
2974 pre_return_status := null;
2975 pre_log_padding := ' ';
2976
2977
2978 ----------Pre merge for HZ_PARTY_SITES ---------------
2979 OPEN party_sites_for_pre_merge(p_to_party_id , p_batch_id);
2980 LOOP
2981 l_return_status := FND_API.G_RET_STS_SUCCESS;
2982 FETCH party_sites_for_pre_merge INTO l_merge_to_entity_id, l_from_party_id, l_batch_party_id;
2983 EXIT WHEN party_sites_for_pre_merge%NOTFOUND;
2984 l_new_party_site_id := l_merge_to_entity_id;
2985 IF l_merge_to_entity_id IS NOT NULL THEN
2986
2987 l_proc_name := 'HZ_MERGE_PKG.party_site_merge';
2988 g_cur_merge_dict_id := 0;
2989 g_num_sub_entities :=-1;
2990
2991 l_from_rec_desc :=hz_merge_util.get_party_site_description(l_merge_to_entity_id);
2992
2993 -- Execute the merge procedure for the entity
2994 exec_merge(
2995 p_entity_name => 'HZ_PARTY_SITES',
2996 p_proc_name => l_proc_name,
2997 p_from_id => l_merge_to_entity_id,
2998 x_to_id => l_new_party_site_id,
2999 p_par_from_id => l_from_party_id,
3000 p_par_to_id => p_to_party_id,
3001 p_parent_entity => 'HZ_PARTIES',
3002 p_batch_id => p_batch_id,
3003 p_batch_party_id=> l_batch_party_id,
3004 x_return_status => l_return_status);
3005
3006
3007 -- Check if the merge procedure returned an error
3008 IF (l_return_status = FND_API.G_RET_STS_SUCCESS OR
3009 l_return_status = 'N' ) THEN
3010 IF l_new_party_site_id <> 0 THEN
3011
3012 -- Write to log file
3013 log('Pre Merge: '|| l_from_rec_desc ||' copied to ID:' || l_new_party_site_id );
3014 ELSE
3015 log('Pre Merge: '|| l_from_rec_desc ||'discarded (DNB data) ');
3016 END IF;
3017
3018 ELSE
3019 RAISE FND_API.G_EXC_ERROR;
3020 END IF;
3021
3022 -- set the party sites status to A so that it is picked in merge that follows
3023 --the hz_merge_pkg.do_party_site_merge sets it to 'M' in pre-merge
3024 UPDATE HZ_PARTY_SITES
3025 SET
3026 STATUS = 'A',
3027 last_update_date = hz_utility_pub.last_update_date,
3028 last_updated_by = hz_utility_pub.user_id,
3029 last_update_login = hz_utility_pub.last_update_login,
3030 request_id = hz_utility_pub.request_id,
3031 program_application_id = hz_utility_pub.program_application_id,
3032 program_id = hz_utility_pub.program_id,
3033 program_update_date = sysdate
3034 WHERE party_site_id = l_merge_to_entity_id;
3035
3036
3037 UPDATE hz_merge_party_details
3038 set merge_to_entity_id = l_new_party_site_id
3039 where batch_party_id IN (select batch_party_id from hz_merge_parties
3040 where batch_id = p_batch_id)
3041 and merge_to_entity_id = l_merge_to_entity_id
3042 and entity_name = 'HZ_PARTY_SITES';
3043
3044 UPDATE hz_merge_party_details
3045 set mandatory_merge = 'C'
3046 where batch_party_id IN (select batch_party_id from hz_merge_parties
3047 where batch_id = p_batch_id)
3048 and merge_from_entity_id = l_merge_to_entity_id
3049 and merge_to_entity_id = l_new_party_site_id
3050 and entity_name = 'HZ_PARTY_SITES';
3051
3052 END IF;
3053 END LOOP;
3054
3055 l_return_status := FND_API.G_RET_STS_SUCCESS;
3056 ----------Pre merge for HZ_RELATIONSHIPS ---------------
3057
3058 --Initialise the log variables
3059 l_from_rec_desc := null;
3060 l_to_rec_desc := null;
3061
3062 OPEN rel_for_pre_merge(p_to_party_id , p_batch_id);
3063 LOOP
3064 BEGIN
3065 FETCH rel_for_pre_merge INTO l_merge_to_entity_id, l_from_party_id, l_batch_party_id;
3066 EXIT WHEN rel_for_pre_merge%NOTFOUND;
3067
3068 IF l_merge_to_entity_id IS NOT NULL THEN
3069
3070 ----subject object stuff------
3071 select subject_id , object_id
3072 into l_subject_id , l_object_id
3073 from HZ_RELATIONSHIPS --4500011
3074 where relationship_id = l_merge_to_entity_id
3075 and subject_table_name = 'HZ_PARTIES'
3076 and object_table_name = 'HZ_PARTIES'
3077 and directional_flag = 'F';
3078
3079 if l_subject_id = l_from_party_id then
3080 l_proc_name := 'HZ_MERGE_PKG.party_reln_subject_merge';
3081 l_merge_dict_id := 8;
3082 elsif l_object_id = l_from_party_id then
3083 l_proc_name := 'HZ_MERGE_PKG.party_reln_object_merge';
3084 l_merge_dict_id := 6;
3085 else
3086 l_proc_name := null;
3087 end if;
3088
3089
3090 g_cur_merge_dict_id := 0;
3091 g_num_sub_entities :=-1;
3092
3093 IF l_proc_name IS NOT NULL THEN
3094
3095 -- Execute the merge procedure for the entity
3096 exec_merge(
3097 p_entity_name => 'HZ_PARTY_RELATIONSHIPS',
3098 p_proc_name => l_proc_name,
3099 p_from_id => l_merge_to_entity_id,
3100 x_to_id => l_merge_to_entity_id,
3101 p_par_from_id => l_from_party_id,
3102 p_par_to_id => p_to_party_id,
3103 p_parent_entity => 'HZ_PARTIES',
3104 p_batch_id => p_batch_id,
3105 p_batch_party_id=> l_batch_party_id,
3106 x_return_status => l_return_status);
3107
3108 l_from_rec_desc := hz_merge_util.get_party_reln_description(l_merge_to_entity_id);
3109
3110 -- Check if the merge procedure returned an error
3111 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3112 -- Write to log file
3113 store_merge_history(l_batch_party_id, l_merge_to_entity_id ,
3114 l_merge_to_entity_id,l_from_party_id, p_to_party_id,l_from_rec_desc,
3115 null, l_merge_dict_id, 'Transfer');
3116 -- Write to log file
3117 IF l_subject_id = l_from_party_id THEN
3118 log('Pre Merge : '||l_from_rec_desc||' transferred subject ID to ' || p_to_party_id);
3119 ELSIF l_object_id = l_from_party_id THEN
3120 log('Pre Merge : '||l_from_rec_desc||' transferred object ID to ' || p_to_party_id);
3121 END IF;
3122
3123 ELSE
3124 -- Log the error in the table and return
3125 store_merge_log(l_batch_party_id, l_merge_to_entity_id,
3126 l_merge_to_entity_id,l_from_party_id, p_to_party_id,
3127 l_from_rec_desc,null,
3128 l_merge_dict_id, 'Error', logerror);
3129 RAISE FND_API.G_EXC_ERROR;
3130 END IF;
3131 END IF; --l_proc_name
3132 END IF; --l_merge_to_entity_id
3133
3134
3135 EXCEPTION
3136 WHEN NO_DATA_FOUND THEN
3137 exit;
3138
3139 END;
3140 END LOOP;
3141
3142
3143 END pre_merge;
3144
3145
3146 /*-----------------------------------------------------------------------
3147 | Function to fetch messages of the stack and log the error
3148 | Also returns the error
3149 |-----------------------------------------------------------------------*/
3150 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
3151 RETURN VARCHAR2 IS
3152
3153 l_msg_data VARCHAR2(2000);
3154 BEGIN
3155 FND_MSG_PUB.Reset;
3156
3157 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3158 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3159 END LOOP;
3160 IF (SQLERRM IS NOT NULL) THEN
3161 l_msg_data := l_msg_data || SQLERRM;
3162 END IF;
3163 log(l_msg_data);
3164 RETURN l_msg_data;
3165 END logerror;
3166
3167 /*-----------------------------------------------------------------------
3168 | Procedure for vetoing the delete
3169 |-----------------------------------------------------------------------*/
3170
3171 PROCEDURE veto_delete IS
3172
3173 BEGIN
3174 IF g_merge_delete_flag = 'Y' THEN
3175 g_merge_delete_flag := 'N';
3176 END IF;
3177 END veto_delete;
3178
3179 PROCEDURE get_merge_to_record_id
3180 (
3181 p_api_version IN NUMBER,
3182 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
3183 p_record_id IN NUMBER,
3184 p_entity_name IN VARCHAR2,
3185 x_is_merged OUT NOCOPY VARCHAR2,
3186 x_merge_to_record_id OUT NOCOPY NUMBER,
3187 x_merge_to_record_desc OUT NOCOPY VARCHAR2,
3188 x_return_status OUT NOCOPY VARCHAR2,
3189 x_msg_count OUT NOCOPY NUMBER,
3190 x_msg_data OUT NOCOPY VARCHAR2
3191 ) IS
3192
3193 CURSOR c_merge_to_rec (cp_merge_from_id NUMBER) IS
3194 SELECT mh.TO_ENTITY_ID, mh.TO_ENTITY_DESC
3195 FROM HZ_MERGE_PARTY_HISTORY mh, HZ_MERGE_DICTIONARY md
3196 WHERE mh.merge_dict_id = md.merge_dict_id
3197 AND md.entity_name = p_entity_name
3198 AND mh.from_entity_id = cp_merge_from_id;
3199
3200 l_api_name CONSTANT VARCHAR2(30) := 'get_merge_to_record_id';
3201 l_api_version CONSTANT NUMBER := 1.0;
3202 l_merge_to_id NUMBER;
3203 l_merge_to_desc HZ_MERGE_PARTY_HISTORY.TO_ENTITY_DESC%TYPE;
3204
3205 BEGIN
3206
3207 --Standard call to check for call compatibility.
3208 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3209 l_api_name, G_PKG_NAME) THEN
3210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3211 END IF;
3212
3213 --Initialize message list if p_init_msg_list is set to TRUE.
3214 IF FND_API.to_Boolean(p_init_msg_list) THEN
3215 FND_MSG_PUB.initialize;
3216 END IF;
3217
3218 --Initialize API return status to success.
3219 x_return_status := FND_API.G_RET_STS_SUCCESS;
3220
3221 l_merge_to_id := p_record_id;
3222
3223 LOOP
3224 OPEN c_merge_to_rec(l_merge_to_id);
3225 FETCH c_merge_to_rec INTO l_merge_to_id, l_merge_to_desc;
3226 EXIT WHEN c_merge_to_rec%NOTFOUND;
3227 CLOSE c_merge_to_rec;
3228 END LOOP;
3229 CLOSE c_merge_to_rec;
3230
3231 IF l_merge_to_id <> p_record_id THEN
3232 x_merge_to_record_id := l_merge_to_id;
3233 x_merge_to_record_desc := l_merge_to_desc;
3234 x_is_merged := FND_API.G_TRUE;
3235 ELSE
3236 x_is_merged := FND_API.G_FALSE;
3237 END IF;
3238
3239 EXCEPTION
3240 WHEN OTHERS THEN
3241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3242
3243 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3244 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3245 FND_MSG_PUB.ADD;
3246
3247 FND_MSG_PUB.Count_And_Get(
3248 p_encoded => FND_API.G_FALSE,
3249 p_count => x_msg_count,
3250 p_data => x_msg_data);
3251 END get_merge_to_record_id;
3252
3253 /*-----------------------------------------------------------------------
3254 | FUNCTION alternate_get_col_type
3255 |-----------------------------------------------------------------------*/
3256 FUNCTION alternate_get_col_type(
3257 p_table VARCHAR2,
3258 p_column VARCHAR2)
3259 RETURN VARCHAR2 IS
3260
3261 c number;
3262 d number;
3263 col_cnt integer;
3264 f boolean;
3265 rec_tab dbms_sql.desc_tab;
3266 col_num number;
3267 dtype VARCHAR2(255);
3268
3269 BEGIN
3270 c := dbms_sql.open_cursor;
3271 dbms_sql.parse(c, 'select '||p_column||' from '||p_table, dbms_sql.NATIVE);
3272 d := dbms_sql.execute(c);
3273 dbms_sql.describe_columns(c, col_cnt, rec_tab);
3274 IF rec_tab(1).col_type = 1 THEN
3275 dtype:='VARCHAR2';
3276 ELSIF rec_tab(1).col_type = 2 THEN
3277 dtype:='NUMBER';
3278 ELSIF rec_tab(1).col_type = 12 THEN
3279 dtype:='DATE';
3280 END IF;
3281 dbms_sql.close_cursor(c);
3282
3283 RETURN dtype;
3284 EXCEPTION
3285 WHEN OTHERS THEN
3286 /* FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3287 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3288 FND_MSG_PUB.ADD;
3289 RAISE FND_API.G_EXC_ERROR;*/
3290 RETURN NULL;
3291 END;
3292
3293 /*-----------------------------------------------------------------------
3294 | FUNCTION get_col_type
3295 |-----------------------------------------------------------------------*/
3296 FUNCTION get_col_type(
3297 p_table VARCHAR2,
3298 p_column VARCHAR2,
3299 p_app_name VARCHAR2)
3300 RETURN VARCHAR2 IS
3301
3302 CURSOR data_type(l_schema1 VARCHAR2) IS
3303 SELECT DATA_TYPE FROM sys.all_tab_columns
3304 WHERE table_name = p_table
3305 AND COLUMN_NAME = p_column and owner = l_schema1;
3306
3307 l_data_type VARCHAR2(106);
3308 l_bool BOOLEAN;
3309 l_status VARCHAR2(255);
3310 l_schema VARCHAR2(255);
3311 l_tmp VARCHAR2(2000);
3312
3313 BEGIN
3314
3315 l_bool := fnd_installation.GET_APP_INFO(p_app_name,l_status,l_tmp,l_schema);
3316
3317 OPEN data_type(l_schema);
3318 FETCH data_type INTO l_data_type;
3319 IF data_type%NOTFOUND THEN
3320 CLOSE data_type;
3321 RETURN alternate_get_col_type(p_table,p_column);
3322 END IF;
3323 CLOSE data_type;
3324
3325 RETURN l_data_type;
3326 EXCEPTION
3327 WHEN OTHERS THEN
3328 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3329 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3330 FND_MSG_PUB.ADD;
3331 RAISE FND_API.G_EXC_ERROR;
3332 END get_col_type;
3333
3334
3335 /*-----------------------------------------------------------------------
3336 | PROCEDURE check_party_in_merge_batch
3337 |-----------------------------------------------------------------------*/
3338
3339 PROCEDURE check_party_in_merge_batch
3340 (
3341 p_api_version IN NUMBER,
3342 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
3343 p_party_id IN NUMBER,
3344 x_in_merge OUT NOCOPY VARCHAR2,
3345 x_batch_id OUT NOCOPY NUMBER,
3346 x_batch_name OUT NOCOPY VARCHAR2,
3347 x_batch_created_by OUT NOCOPY VARCHAR2,
3348 x_batch_creation_date OUT NOCOPY DATE,
3349 x_return_status OUT NOCOPY VARCHAR2,
3350 x_msg_count OUT NOCOPY NUMBER,
3351 x_msg_data OUT NOCOPY VARCHAR2
3352 ) IS
3353
3354 CURSOR c_merge_batch IS
3355 SELECT b.batch_id, b.batch_name, b.created_by, b.creation_date
3356 FROM HZ_MERGE_PARTIES p, HZ_MERGE_BATCH b
3357 WHERE b.batch_id = p.batch_id
3358 AND b.batch_status <> 'COMPLETE'
3359 AND (p.from_party_id = p_party_id
3360 OR p.to_party_id = p_party_id);
3361
3362 l_api_name CONSTANT VARCHAR2(30) := 'check_party_in_merge_batch';
3363 l_api_version CONSTANT NUMBER := 1.0;
3364
3365 l_batch_id NUMBER;
3366 l_batch_name VARCHAR2(200);
3367 l_batch_created_by NUMBER;
3368 l_batch_created_on DATE;
3369
3370 BEGIN
3371
3372 --Standard call to check for call compatibility.
3373 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
3374 l_api_name, G_PKG_NAME) THEN
3375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3376 END IF;
3377
3378 --Initialize message list if p_init_msg_list is set to TRUE.
3379 IF FND_API.to_Boolean(p_init_msg_list) THEN
3380 FND_MSG_PUB.initialize;
3381 END IF;
3382
3383 --Initialize API return status to success.
3384 x_return_status := FND_API.G_RET_STS_SUCCESS;
3385 x_in_merge := FND_API.G_FALSE;
3386
3387 OPEN c_merge_batch;
3388 FETCH c_merge_batch INTO l_batch_id, l_batch_name, l_batch_created_by,
3389 l_batch_created_on;
3390 IF c_merge_batch%FOUND THEN
3391 x_batch_id := l_batch_id;
3392 x_batch_name := l_batch_name;
3393 x_batch_created_by := l_batch_created_by;
3394 x_batch_creation_date := l_batch_created_on;
3395 x_in_merge := FND_API.G_TRUE;
3396 END IF;
3397
3398 EXCEPTION
3399 WHEN OTHERS THEN
3400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3401
3402 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
3403 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3404 FND_MSG_PUB.ADD;
3405
3406 FND_MSG_PUB.Count_And_Get(
3407 p_count => x_msg_count,
3408 p_data => x_msg_data);
3409 END check_party_in_merge_batch;
3410
3411
3412 PROCEDURE setup_dnb_data(
3413 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
3414 x_to_id IN OUT NOCOPY NUMBER,
3415 p_batch_party_id IN NUMBER
3416 ) IS
3417
3418 CURSOR c_party_type(cp_party_id NUMBER) IS
3419 SELECT party_type
3420 FROM HZ_PARTIES
3421 WHERE party_id = cp_party_id;
3422
3423 CURSOR c_duns IS
3424 SELECT duns_number_c, last_update_date, organization_profile_id,actual_content_source
3425 FROM HZ_ORGANIZATION_PROFILES
3426 WHERE party_id = p_from_id
3427 AND EFFECTIVE_END_DATE IS NULL
3428 AND actual_content_source = 'DNB'
3429 AND nvl(status, 'A') = 'A';
3430
3431 CURSOR c_duns1 IS
3432 SELECT duns_number_c , last_update_date, organization_profile_id,actual_content_source
3433 FROM HZ_ORGANIZATION_PROFILES
3434 WHERE party_id = x_to_id
3435 AND EFFECTIVE_END_DATE IS NULL
3436 AND actual_content_source = 'DNB'
3437 AND nvl(status, 'A') = 'A';
3438
3439 CURSOR c_branch IS
3440 SELECT 1
3441 FROM HZ_RELATIONSHIPS --4500011
3442 WHERE content_source_type = 'DNB'
3443 AND subject_id = p_from_id
3444 AND object_id = x_to_id
3445 AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
3446 AND subject_table_name = 'HZ_PARTIES'
3447 AND object_table_name = 'HZ_PARTIES'
3448 AND directional_flag = 'F';
3449
3450 l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3451 l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3452 l_from_duns_number VARCHAR2(255);
3453 l_to_duns_number VARCHAR2(255);
3454 l_temp NUMBER;
3455
3456 l_to_is_branch VARCHAR2(1) := 'N';
3457
3458 case1 BOOLEAN := FALSE;
3459 case2 BOOLEAN := FALSE;
3460 case3 BOOLEAN := FALSE;
3461 case_new VARCHAR2(5) := 'FALSE';
3462
3463 l_from NUMBER;
3464 l_to NUMBER;
3465 l_to_loc_id NUMBER;
3466 l_to_subj_id NUMBER;
3467
3468 l_to_profile_id NUMBER;
3469 l_from_profile_id NUMBER;
3470 l_from_last_upd_date DATE;
3471 l_to_last_upd_date DATE;
3472
3473 l_msg_data VARCHAR2(2000);
3474 l_msg_count NUMBER;
3475 l_return_status VARCHAR2(255);
3476 l_actual_content_source VARCHAR2(2000);
3477 l_obj_version_number NUMBER;
3478 l_rel_to_party_id NUMBER;
3479 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
3480 l_batch_party_id NUMBER;
3481 l_batch_id NUMBER;
3482 l_hierarchy_rec HZ_HIERARCHY_PUB.HIERARCHY_NODE_REC_TYPE;
3483
3484 BEGIN
3485
3486 -- Handle DNB Data in the parties
3487
3488 -- Firstly check how DNB data needs to be handled.
3489 -- case1 or case2 or case3 -> both parties have DNB data
3490 -- case1 - FROM has newer DNB
3491 -- case2 - TO has newer DNB
3492 -- case3 - TO and FROM have different DUNS numbers
3493 -- l_to_is_branch - TO is a branch of FROM
3494
3495 OPEN c_duns;
3496 FETCH c_duns INTO l_from_duns_number, l_from_last_upd_date, l_from_profile_id,l_actual_content_source;
3497 IF c_duns%FOUND THEN
3498 OPEN c_duns1;
3499 FETCH c_duns1 INTO l_to_duns_number, l_to_last_upd_date, l_to_profile_id,l_actual_content_source;
3500 IF c_duns1%FOUND THEN
3501 IF l_from_duns_number = l_to_duns_number THEN
3502 IF l_to_last_upd_date>=l_from_last_upd_date THEN -- Case 2
3503 case2 := true;
3504 ELSE
3505 case1 := true;
3506 END IF;
3507 ELSE
3508 case3 := true;
3509
3510 OPEN c_branch;
3511 FETCH c_branch INTO l_temp;
3512 IF c_branch%FOUND THEN
3513 l_to_is_branch := 'Y';
3514 END IF;
3515 CLOSE c_branch;
3516
3517 END IF;
3518 END IF;
3519 CLOSE c_duns1;
3520 END IF;
3521 CLOSE c_duns;
3522
3523 -- IF both parties have DNB data, populate HZ_MERGE_PARTY_DETAILS to
3524 -- indicate how the DNB data needs to be handled.
3525 if case1 OR case2 OR case3 THEN
3526
3527 -- *******************************************
3528 -- Handle DNB data in HZ_ORGANIZATION_PROFILES
3529
3530 IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
3531 HZ_PARTY_V2PUB.get_organization_rec(
3532 FND_API.G_FALSE,
3533 p_from_id,
3534 'DNB',
3535 l_organization_rec,
3536 l_return_status,
3537 l_msg_count,
3538 l_msg_data);
3539
3540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3541 IF l_msg_data IS NULL THEN
3542 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3543 l_msg_data := l_msg_data ||
3544 FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3545 END LOOP;
3546 END IF;
3547 RETURN;
3548 END IF;
3549
3550 SELECT object_version_number INTO l_obj_version_number
3551 FROM HZ_PARTIES
3552 WHERE party_id = x_to_id;
3553
3554 l_organization_rec.party_rec.party_id := x_to_id;
3555 l_organization_rec.created_by_module := NULL;
3556 l_organization_rec.application_id := NULL;
3557 l_organization_rec.party_rec.party_number := NULL;
3558 l_organization_rec.party_rec.orig_system_reference := NULL;
3559 HZ_PARTY_V2PUB.update_organization(
3560 FND_API.G_FALSE,
3561 l_organization_rec,
3562 l_obj_version_number,
3563 l_to_profile_id,
3564 l_return_status,
3565 l_msg_count,
3566 l_msg_data);
3567
3568 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3569 IF l_msg_data IS NULL THEN
3570 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
3571 l_msg_data := l_msg_data ||
3572 FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
3573 END LOOP;
3574 END IF;
3575 FND_FILE.put_line (FND_FILE.log,'Warning .. Error updating Org Profile of x_to_id ' || l_msg_data);
3576 -- Bug Fix : 3116262.
3577 --RETURN;
3578 RAISE FND_API.G_EXC_ERROR;
3579 END IF;
3580
3581 END IF;
3582
3583 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3584 p_batch_party_id,
3585 'HZ_ORGANIZATION_PROFILES',
3586 l_from_profile_id,
3587 l_to_profile_id,
3588 'Y',
3589 hz_utility_pub.created_by,
3590 hz_utility_pub.creation_Date,
3591 hz_utility_pub.last_update_login,
3592 hz_utility_pub.last_update_date,
3593 hz_utility_pub.last_updated_by);
3594
3595
3596
3597 -- *******************************************
3598 -- Handle DNB data in HZ_PARTY_SITES/HZ_LOCATIONS
3599
3600 -- Keep to's data.
3601 -- Inactivate the from's active party site.
3602 UPDATE HZ_PARTY_SITES ps
3603 SET STATUS = 'M',
3604 end_date_active = trunc(SYSDATE-1),
3605 last_update_date = hz_utility_pub.last_update_date,
3606 last_updated_by = hz_utility_pub.user_id,
3607 last_update_login = hz_utility_pub.last_update_login,
3608 request_id = hz_utility_pub.request_id,
3609 program_application_id = hz_utility_pub.program_application_id,
3610 program_id = hz_utility_pub.program_id,
3611 program_update_date = sysdate
3612 WHERE party_id= p_from_id
3613 AND actual_content_source = 'DNB'
3614 AND nvl(status,'A') in ('A','I');
3615
3616 -- *******************************************
3617 -- Handle DNB data in HZ_RELATIONSHIPS
3618 -- Keep only master party relationships
3619 FOR FROM_REL IN (
3620 SELECT relationship_id, relationship_type, subject_id, party_id, start_date, end_date, relationship_code,
3621 direction_code
3622 FROM HZ_RELATIONSHIPS
3623 WHERE actual_content_source = 'DNB'
3624 AND nvl(status, 'A') IN ('A','I')
3625 AND object_id = p_from_id
3626 AND (end_date is null OR end_date>SYSDATE)) LOOP
3627
3628 UPDATE HZ_RELATIONSHIPS
3629 SET end_date = TRUNC(SYSDATE-1),
3630 status = 'M',
3631 last_update_date = hz_utility_pub.last_update_date,
3632 last_updated_by = hz_utility_pub.user_id,
3633 last_update_login = hz_utility_pub.last_update_login,
3634 request_id = hz_utility_pub.request_id,
3635 program_application_id = hz_utility_pub.program_application_id,
3636 program_id = hz_utility_pub.program_id,
3637 program_update_date = sysdate
3638 WHERE relationship_id =FROM_REL.RELATIONSHIP_ID;
3639
3640 ----Inactivating SSM Record
3641 UPDATE HZ_ORIG_SYS_REFERENCES
3642 SET STATUS = 'I',
3643 END_DATE_ACTIVE =trunc(SYSDATE-1),
3644 last_update_date = hz_utility_pub.last_update_date,
3645 last_updated_by = hz_utility_pub.user_id,
3646 last_update_login = hz_utility_pub.last_update_login,
3647 request_id = hz_utility_pub.request_id,
3648 program_application_id = hz_utility_pub.program_application_id,
3649 program_id = hz_utility_pub.program_id,
3650 program_update_date = sysdate
3651 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
3652 AND OWNER_TABLE_ID = FROM_REL.PARTY_ID
3653 AND ORIG_SYSTEM = 'DNB'
3654 AND STATUS = 'A';
3655
3656 UPDATE HZ_PARTIES
3657 SET status = 'M',
3658 last_update_date = hz_utility_pub.last_update_date,
3659 last_updated_by = hz_utility_pub.user_id,
3660 last_update_login = hz_utility_pub.last_update_login,
3661 request_id = hz_utility_pub.request_id,
3662 program_application_id = hz_utility_pub.program_application_id,
3663 program_id = hz_utility_pub.program_id,
3664 program_update_date = sysdate
3665 WHERE party_id = FROM_REL.party_id;
3666
3667 IF FROM_REL.relationship_type = 'DNB_HIERARCHY' THEN
3668 l_hierarchy_rec.status := 'M';
3669 l_hierarchy_rec.hierarchy_type := FROM_REL.relationship_type;
3670 l_hierarchy_rec.effective_end_date :=sysdate;
3671 l_hierarchy_rec.relationship_id := FROM_REL.RELATIONSHIP_ID;
3672
3673 IF FROM_REL.direction_code = 'P' THEN
3674 -- assign the subject to parent for hierarchy
3675 l_hierarchy_rec.parent_id := FROM_REL.subject_id;
3676 l_hierarchy_rec.parent_table_name := 'HZ_PARTIES';
3677 l_hierarchy_rec.parent_object_type := 'ORGANIZATION';
3678 l_hierarchy_rec.child_id := p_from_id;
3679 l_hierarchy_rec.child_table_name := 'HZ_PARTIES';
3680 l_hierarchy_rec.child_object_type := 'ORGANIZATION';
3681 ELSIF FROM_REL.direction_code = 'C' THEN
3682 -- assign the object to parent
3683 l_hierarchy_rec.parent_id := p_from_id;
3684 l_hierarchy_rec.parent_table_name := 'HZ_PARTES';
3685 l_hierarchy_rec.parent_object_type := 'ORGANIZATION';
3686 l_hierarchy_rec.child_id := FROM_REL.subject_id;
3687 l_hierarchy_rec.child_table_name := 'HZ_PARTIES';
3688 l_hierarchy_rec.child_object_type := 'ORGANIZATION';
3689
3690 END IF;
3691
3692 HZ_HIERARCHY_PUB.update_link(
3693 p_init_msg_list => FND_API.G_FALSE,
3694 p_hierarchy_node_rec => l_hierarchy_rec,
3695 x_return_status => l_return_status,
3696 x_msg_count => l_msg_count,
3697 x_msg_data => l_msg_data
3698 );
3699
3700 END IF;
3701
3702
3703 END LOOP;
3704
3705 -- *******************************************
3706 -- Handle DNB data in HZ_CONTACT_POINTS
3707
3708
3709 FOR FROM_CP IN (
3710 SELECT contact_point_id, phone_line_type, contact_point_type
3711 FROM HZ_CONTACT_POINTS
3712 WHERE owner_table_name = 'HZ_PARTIES'
3713 AND actual_content_source = 'DNB'
3714 AND nvl(status, 'A') = 'A'
3715 AND owner_table_id = p_from_id) LOOP
3716 case_new := 'FALSE';
3717 BEGIN
3718 BEGIN
3719 SELECT contact_point_id INTO l_to
3720 FROM HZ_CONTACT_POINTS
3721 WHERE owner_table_name = 'HZ_PARTIES'
3722 AND actual_content_source = 'DNB'
3723 AND nvl(phone_line_type,'X') = nvl(FROM_CP.phone_line_type,'X')--bug 5221273
3724 AND contact_point_type = FROM_CP.contact_point_type --bug 5221273
3725 AND nvl(status, 'A') = 'A'
3726 AND owner_table_id = x_to_id
3727 and rownum = 1; --bug 5221273;
3728 EXCEPTION
3729 WHEN NO_DATA_FOUND THEN
3730 NULL;
3731 END;
3732
3733
3734 -- From Newer
3735 IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
3736 -- Merge to into from
3737 --If Exactly same then Merge
3738
3739 BEGIN
3740 Select 'True' INTO case_new FROM HZ_CONTACT_POINTS
3741 WHERE contact_point_id = FROM_CP.contact_point_id
3742 AND (CONTACT_POINT_TYPE ||
3743 STATUS ||
3744 EDI_TRANSACTION_HANDLING ||
3745 EDI_ID_NUMBER ||
3746 EDI_PAYMENT_METHOD ||
3747 EDI_PAYMENT_FORMAT ||
3748 EDI_REMITTANCE_METHOD ||
3749 EDI_REMITTANCE_INSTRUCTION ||
3750 EDI_TP_HEADER_ID ||
3751 EDI_ECE_TP_LOCATION_CODE ||
3752 EMAIL_FORMAT ||
3753 TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
3754 TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
3755 PHONE_CALLING_CALENDAR ||
3756 DECLARED_BUSINESS_PHONE_FLAG ||
3757 PHONE_PREFERRED_ORDER ||
3758 TELEPHONE_TYPE ||
3759 TIME_ZONE ||
3760 PHONE_TOUCH_TONE_TYPE_FLAG ||
3761 PHONE_AREA_CODE ||
3762 PHONE_COUNTRY_CODE ||
3763 PHONE_NUMBER ||
3764 PHONE_EXTENSION ||
3765 PHONE_LINE_TYPE ||
3766 TELEX_NUMBER ||
3767 WEB_TYPE )
3768 = (SELECT
3769 CONTACT_POINT_TYPE ||
3770 STATUS ||
3771 EDI_TRANSACTION_HANDLING ||
3772 EDI_ID_NUMBER ||
3773 EDI_PAYMENT_METHOD ||
3774 EDI_PAYMENT_FORMAT ||
3775 EDI_REMITTANCE_METHOD ||
3776 EDI_REMITTANCE_INSTRUCTION ||
3777 EDI_TP_HEADER_ID ||
3778 EDI_ECE_TP_LOCATION_CODE ||
3779 EMAIL_FORMAT ||
3780 TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
3781 TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
3782 PHONE_CALLING_CALENDAR ||
3783 DECLARED_BUSINESS_PHONE_FLAG ||
3784 PHONE_PREFERRED_ORDER ||
3785 TELEPHONE_TYPE ||
3786 TIME_ZONE ||
3787 PHONE_TOUCH_TONE_TYPE_FLAG ||
3788 PHONE_AREA_CODE ||
3789 PHONE_COUNTRY_CODE ||
3790 PHONE_NUMBER ||
3791 PHONE_EXTENSION ||
3792 PHONE_LINE_TYPE ||
3793 TELEX_NUMBER ||
3794 WEB_TYPE
3795 FROM HZ_CONTACT_POINTS
3796 WHERE contact_point_id = l_to)
3797 AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
3798 SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
3799 FROM HZ_CONTACT_POINTS
3800 WHERE contact_point_id = l_to)
3801 AND nvl(URL, 'NOURL') = (
3802 SELECT nvl(URL, 'NOURL')
3803 FROM HZ_CONTACT_POINTS
3804 WHERE contact_point_id = l_to);
3805
3806 EXCEPTION
3807 WHEN NO_DATA_FOUND THEN
3808 NULL;
3809
3810 END;
3811
3812 IF case_new = 'True' THEN
3813 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3814 p_batch_party_id,
3815 'HZ_CONTACT_POINTS',
3816 l_to,
3817 FROM_CP.contact_point_id,
3818 'T',
3819 hz_utility_pub.created_by,
3820 hz_utility_pub.creation_Date,
3821 hz_utility_pub.last_update_login,
3822 hz_utility_pub.last_update_date,
3823 hz_utility_pub.last_updated_by);
3824 ELSE
3825 UPDATE HZ_CONTACT_POINTS
3826 SET actual_content_source = 'USER_ENTERED',
3827 last_update_date =hz_utility_pub.last_update_date,
3828 last_updated_by = hz_utility_pub.last_updated_by,
3829 last_update_login=hz_utility_pub.last_update_login,
3830 request_id = hz_utility_pub.request_id,
3831 program_application_id = hz_utility_pub.program_application_id,
3832 program_id = hz_utility_pub.program_id,
3833 program_update_date = sysdate
3834 WHERE contact_point_id = l_to;
3835 ----Inactivating SSM Record
3836 UPDATE HZ_ORIG_SYS_REFERENCES
3837 SET STATUS = 'I',
3838 END_DATE_ACTIVE =trunc(SYSDATE-1),
3839 last_update_date = hz_utility_pub.last_update_date,
3840 last_updated_by = hz_utility_pub.user_id,
3841 last_update_login = hz_utility_pub.last_update_login,
3842 request_id = hz_utility_pub.request_id,
3843 program_application_id = hz_utility_pub.program_application_id,
3844 program_id = hz_utility_pub.program_id,
3845 program_update_date = sysdate
3846 WHERE OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
3847 AND OWNER_TABLE_ID = l_to
3848 AND ORIG_SYSTEM = 'DNB'
3849 AND STATUS = 'A';
3850 END IF;
3851 ELSE -- To Newer
3852 -- Merge from into to
3853 --Perform Exact Dup check
3854
3855 BEGIN
3856 Select 'True' INTO case_new FROM HZ_CONTACT_POINTS
3857 WHERE contact_point_id = FROM_CP.contact_point_id
3858 AND
3859 CONTACT_POINT_TYPE ||
3860 STATUS ||
3861 EDI_TRANSACTION_HANDLING ||
3862 EDI_ID_NUMBER ||
3863 EDI_PAYMENT_METHOD ||
3864 EDI_PAYMENT_FORMAT ||
3865 EDI_REMITTANCE_METHOD ||
3866 EDI_REMITTANCE_INSTRUCTION ||
3867 EDI_TP_HEADER_ID ||
3868 EDI_ECE_TP_LOCATION_CODE ||
3869 EMAIL_FORMAT ||
3870 TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
3871 TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
3872 PHONE_CALLING_CALENDAR ||
3873 DECLARED_BUSINESS_PHONE_FLAG ||
3874 PHONE_PREFERRED_ORDER ||
3875 TELEPHONE_TYPE ||
3876 TIME_ZONE ||
3877 PHONE_TOUCH_TONE_TYPE_FLAG ||
3878 PHONE_AREA_CODE ||
3879 PHONE_COUNTRY_CODE ||
3880 PHONE_NUMBER ||
3881 PHONE_EXTENSION ||
3882 PHONE_LINE_TYPE ||
3883 TELEX_NUMBER ||
3884 WEB_TYPE
3885 = (SELECT
3886 CONTACT_POINT_TYPE ||
3887 STATUS ||
3888 EDI_TRANSACTION_HANDLING ||
3889 EDI_ID_NUMBER ||
3890 EDI_PAYMENT_METHOD ||
3891 EDI_PAYMENT_FORMAT ||
3892 EDI_REMITTANCE_METHOD ||
3893 EDI_REMITTANCE_INSTRUCTION ||
3894 EDI_TP_HEADER_ID ||
3895 EDI_ECE_TP_LOCATION_CODE ||
3896 EMAIL_FORMAT ||
3897 TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
3898 TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
3899 PHONE_CALLING_CALENDAR ||
3900 DECLARED_BUSINESS_PHONE_FLAG ||
3901 PHONE_PREFERRED_ORDER ||
3902 TELEPHONE_TYPE ||
3903 TIME_ZONE ||
3904 PHONE_TOUCH_TONE_TYPE_FLAG ||
3905 PHONE_AREA_CODE ||
3906 PHONE_COUNTRY_CODE ||
3907 PHONE_NUMBER ||
3908 PHONE_EXTENSION ||
3909 PHONE_LINE_TYPE ||
3910 TELEX_NUMBER ||
3911 WEB_TYPE
3912 FROM HZ_CONTACT_POINTS
3913 WHERE contact_point_id = l_to)
3914 AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
3915 SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
3916 FROM HZ_CONTACT_POINTS
3917 WHERE contact_point_id = l_to)
3918 AND nvl(URL, 'NOURL') = (
3919 SELECT nvl(URL, 'NOURL')
3920 FROM HZ_CONTACT_POINTS
3921 WHERE contact_point_id = l_to);
3922
3923 EXCEPTION
3924 WHEN No_Data_Found THEN
3925 NULL;
3926
3927 END;
3928
3929 IF case_new = 'True' THEN --Populate HZ_MERGE_PARTY_DETAILS
3930 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3931 p_batch_party_id,
3932 'HZ_CONTACT_POINTS',
3933 FROM_CP.contact_point_id,
3934 l_to,
3935 'Y',
3936 hz_utility_pub.created_by,
3937 hz_utility_pub.creation_Date,
3938 hz_utility_pub.last_update_login,
3939 hz_utility_pub.last_update_date,
3940 hz_utility_pub.last_updated_by);
3941 ELSE
3942 UPDATE HZ_CONTACT_POINTS
3943 SET actual_content_source = 'USER_ENTERED',
3944 last_update_date =hz_utility_pub.last_update_date,
3945 last_updated_by = hz_utility_pub.last_updated_by,
3946 last_update_login=hz_utility_pub.last_update_login,
3947 request_id = hz_utility_pub.request_id,
3948 program_application_id = hz_utility_pub.program_application_id,
3949 program_id = hz_utility_pub.program_id,
3950 program_update_date = sysdate
3951 WHERE contact_point_id = FROM_CP.contact_point_id;
3952 ----Inactivating SSM Record
3953 UPDATE HZ_ORIG_SYS_REFERENCES
3954 SET STATUS = 'I',
3955 END_DATE_ACTIVE =trunc(SYSDATE-1),
3956 last_update_date = hz_utility_pub.last_update_date,
3957 last_updated_by = hz_utility_pub.user_id,
3958 last_update_login = hz_utility_pub.last_update_login,
3959 request_id = hz_utility_pub.request_id,
3960 program_application_id = hz_utility_pub.program_application_id,
3961 program_id = hz_utility_pub.program_id,
3962 program_update_date = sysdate
3963 WHERE OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
3964 AND OWNER_TABLE_ID =FROM_CP.contact_point_id
3965 AND ORIG_SYSTEM = 'DNB'
3966 AND STATUS = 'A';
3967 END IF;
3968 END IF;
3969 EXCEPTION
3970 WHEN NO_DATA_FOUND THEN
3971 -- Transfer
3972 NULL;
3973 END;
3974 END LOOP;
3975
3976 -- *******************************************
3977 -- Handle DNB data in HZ_CREDIT_RATINGS
3978 FOR FROM_CR IN (
3979 SELECT credit_rating_id, rated_as_of_date
3980 FROM HZ_CREDIT_RATINGS
3981 WHERE actual_content_source = 'DNB'
3982 AND party_id = p_from_id) LOOP
3983
3984
3985 BEGIN
3986 SELECT credit_rating_id INTO l_to
3987 FROM HZ_CREDIT_RATINGS
3988 WHERE actual_content_source = 'DNB'
3989 AND party_id = x_to_id
3990 AND trunc(rated_as_of_date)=trunc(FROM_CR.rated_as_of_date);
3991
3992 -- From Newer
3993 IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
3994 -- Bug 3236556 - Delete the credit ratings of the to party
3995 DELETE FROM HZ_CREDIT_RATINGS
3996 WHERE credit_rating_id = l_to;
3997 -- Bug 3236556 - Since the to party no longer exists we must not insert a row with merge details.
3998 /*-- Merge to into from
3999 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4000 p_batch_party_id,
4001 'HZ_CREDIT_RATINGS',
4002 l_to,
4003 FROM_CR.credit_rating_id,
4004 'T',
4005 hz_utility_pub.created_by,
4006 hz_utility_pub.creation_Date,
4007 hz_utility_pub.last_update_login,
4008 hz_utility_pub.last_update_date,
4009 hz_utility_pub.last_updated_by);
4010 */
4011 ELSE -- To Newer
4012 -- Merge from into to
4013 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4014 p_batch_party_id,
4015 'HZ_CREDIT_RATINGS',
4016 FROM_CR.credit_rating_id,
4017 l_to,
4018 'Y',
4019 hz_utility_pub.created_by,
4020 hz_utility_pub.creation_Date,
4021 hz_utility_pub.last_update_login,
4022 hz_utility_pub.last_update_date,
4023 hz_utility_pub.last_updated_by);
4024 END IF;
4025 EXCEPTION
4026 WHEN NO_DATA_FOUND THEN
4027 -- Transfer
4028 NULL;
4029 END;
4030 END LOOP;
4031
4032 -- *******************************************
4033 -- Handle DNB data in HZ_CODE_ASSIGNMENTS
4034 FOR FROM_CA IN (
4035 SELECT code_assignment_id, class_category, class_code
4036 FROM HZ_CODE_ASSIGNMENTS
4037 WHERE owner_table_name = 'HZ_PARTIES'
4038 AND content_source_type = 'DNB'
4039 AND nvl(status, 'A') = 'A'
4040 AND owner_table_id = p_from_id) LOOP
4041
4042 BEGIN
4043 SELECT code_assignment_id INTO l_to
4044 FROM HZ_CODE_ASSIGNMENTS
4045 WHERE owner_table_name = 'HZ_PARTIES'
4046 AND content_source_type = 'DNB'
4047 AND nvl(status, 'A') = 'A'
4048 AND owner_table_id = x_to_id
4049 AND class_category = FROM_CA.class_category
4050 AND class_code = FROM_CA.class_code
4051 AND rownum=1; --3197084
4052
4053 -- From Newer
4054 IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
4055 -- Merge to into from
4056 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4057 p_batch_party_id,
4058 'HZ_CODE_ASSIGNMENTS',
4059 FROM_CA.code_assignment_id,
4060 l_to,
4061 'Y',
4062 hz_utility_pub.created_by,
4063 hz_utility_pub.creation_Date,
4064 hz_utility_pub.last_update_login,
4065 hz_utility_pub.last_update_date,
4066 hz_utility_pub.last_updated_by);
4067 ELSE -- To Newer
4068 -- Merge from into to
4069 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4070 p_batch_party_id,
4071 'HZ_CODE_ASSIGNMENTS',
4072 FROM_CA.code_assignment_id,
4073 l_to,
4074 'Y',
4075 hz_utility_pub.created_by,
4076 hz_utility_pub.creation_Date,
4077 hz_utility_pub.last_update_login,
4078 hz_utility_pub.last_update_date,
4079 hz_utility_pub.last_updated_by);
4080 END IF;
4081 EXCEPTION
4082 WHEN NO_DATA_FOUND THEN
4083 -- Transfer
4084 NULL;
4085 END;
4086 END LOOP;
4087
4088 -- *******************************************
4089 -- Handle DNB data in HZ_FINANCIAL_REPORTS
4090 FOR FROM_FR IN (
4091 SELECT financial_report_id,
4092 type_of_financial_report,
4093 TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
4094 DOCUMENT_REFERENCE ||
4095 ISSUED_PERIOD ||
4096 TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
4097 TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
4098 actual_content_source con_cat --for bug 6600935
4099 FROM HZ_FINANCIAL_REPORTS
4100 WHERE actual_content_source = 'DNB'
4101 AND nvl(status, 'A') = 'A'
4102 AND party_id = p_from_id) LOOP
4103
4104 BEGIN
4105 SELECT financial_report_id INTO l_to
4106 FROM HZ_FINANCIAL_REPORTS
4107 WHERE actual_content_source = 'DNB'
4108 AND nvl(status, 'A') = 'A'
4109 AND type_of_financial_report=FROM_FR.type_of_financial_report
4110 AND TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
4111 DOCUMENT_REFERENCE ||
4112 ISSUED_PERIOD ||
4113 TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
4114 TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
4115 actual_content_source=FROM_FR.con_cat --for bug 6600935
4116 AND party_id = x_to_id;
4117
4118 IF case1 OR (case3 and l_to_is_branch = 'Y') THEN
4119
4120 /*Bug 3236556*/
4121 DELETE FROM HZ_FINANCIAL_REPORTS
4122 WHERE financial_report_id = l_to;
4123
4124 DELETE FROM HZ_FINANCIAL_NUMBERS
4125 WHERE financial_report_id = l_to;
4126
4127 /*
4128 UPDATE HZ_FINANCIAL_REPORTS
4129 SET status = 'I',
4130 last_update_date = hz_utility_pub.last_update_date,
4131 last_updated_by = hz_utility_pub.user_id,
4132 last_update_login = hz_utility_pub.last_update_login,
4133 request_id = hz_utility_pub.request_id,
4134 program_application_id = hz_utility_pub.program_application_id,
4135 program_id = hz_utility_pub.program_id,
4136 program_update_date = sysdate
4137 WHERE financial_report_id = l_to;
4138
4139 UPDATE HZ_FINANCIAL_NUMBERS
4140 SET status='I',
4141 last_update_date = hz_utility_pub.last_update_date,
4142 last_updated_by = hz_utility_pub.user_id,
4143 last_update_login = hz_utility_pub.last_update_login,
4144 request_id = hz_utility_pub.request_id,
4145 program_application_id = hz_utility_pub.program_application_id,
4146 program_id = hz_utility_pub.program_id,
4147 program_update_date = sysdate
4148 WHERE financial_report_id = l_to;
4149 */
4150 ELSE
4151 UPDATE HZ_FINANCIAL_REPORTS
4152 SET status = 'M',
4153 last_update_date = hz_utility_pub.last_update_date,
4154 last_updated_by = hz_utility_pub.user_id,
4155 last_update_login = hz_utility_pub.last_update_login,
4156 request_id = hz_utility_pub.request_id,
4157 program_application_id = hz_utility_pub.program_application_id,
4158 program_id = hz_utility_pub.program_id,
4159 program_update_date = sysdate
4160 WHERE financial_report_id = FROM_FR.financial_report_id;
4161
4162 UPDATE HZ_FINANCIAL_NUMBERS
4163 SET status='M',
4164 last_update_date = hz_utility_pub.last_update_date,
4165 last_updated_by = hz_utility_pub.user_id,
4166 last_update_login = hz_utility_pub.last_update_login,
4167 request_id = hz_utility_pub.request_id,
4168 program_application_id = hz_utility_pub.program_application_id,
4169 program_id = hz_utility_pub.program_id,
4170 program_update_date = sysdate
4171 WHERE financial_report_id = FROM_FR.financial_report_id; -- Bug 3313609
4172
4173 END IF;
4174 EXCEPTION
4175 WHEN NO_DATA_FOUND THEN
4176 -- Transfer
4177 NULL;
4178 END;
4179 END LOOP;
4180 END IF;
4181
4182
4183
4184 EXCEPTION
4185 WHEN OTHERS THEN
4186 log('Error in setup DNB data: '||SQLERRM);
4187 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
4188 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4189 FND_MSG_PUB.ADD;
4190 APP_EXCEPTION.RAISE_EXCEPTION;
4191 END;
4192
4193 PROCEDURE insert_party_site_details (
4194 p_from_party_id IN NUMBER,
4195 p_to_party_id IN NUMBER,
4196 p_batch_party_id IN NUMBER
4197 ) IS
4198
4199 --Cursor for inserting Party sites that are non-DNB
4200 CURSOR c_from_ps_loc IS
4201 SELECT party_site_id, ps.location_id
4202 FROM HZ_PARTY_SITES ps
4203 WHERE ps.party_id = p_from_party_id
4204 AND ps.actual_content_source <> 'DNB'
4205 AND nvl(status, 'A') = 'A';
4206
4207 CURSOR c_dup_to_ps(cp_loc_id NUMBER) IS
4208 SELECT party_site_id
4209 FROM HZ_PARTY_SITES ps
4210 WHERE ps.party_id = p_to_party_id
4211 AND ps.location_id = cp_loc_id
4212 AND ps.actual_content_source <> 'DNB'
4213 AND nvl(status, 'A') = 'A';
4214
4215 l_ps_id NUMBER;
4216 l_loc_id NUMBER;
4217 l_dup_ps_id NUMBER;
4218 l_sqerr VARCHAR2(2000);
4219
4220 BEGIN
4221
4222 OPEN c_from_ps_loc;
4223 LOOP
4224 FETCH c_from_ps_loc INTO l_ps_id, l_loc_id;
4225 EXIT WHEN c_from_ps_loc%NOTFOUND;
4226 IF p_from_party_id <> p_to_party_id THEN
4227 OPEN c_dup_to_ps(l_loc_id);
4228 FETCH c_dup_to_ps INTO l_dup_ps_id;
4229
4230 IF c_dup_to_ps%FOUND THEN
4231 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4232 p_batch_party_id,
4233 'HZ_PARTY_SITES',
4234 l_ps_id,
4235 l_dup_ps_id,
4236 'Y',
4237 hz_utility_pub.created_by,
4238 hz_utility_pub.creation_Date,
4239 hz_utility_pub.last_update_login,
4240 hz_utility_pub.last_update_date,
4241 hz_utility_pub.last_updated_by);
4242 ELSE
4243 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4244 p_batch_party_id,
4245 'HZ_PARTY_SITES',
4246 l_ps_id,
4247 l_ps_id,
4248 'Y',
4249 hz_utility_pub.created_by,
4250 hz_utility_pub.creation_Date,
4251 hz_utility_pub.last_update_login,
4252 hz_utility_pub.last_update_date,
4253 hz_utility_pub.last_updated_by);
4254 END IF;
4255 CLOSE c_dup_to_ps;
4256 END IF;
4257 END LOOP;
4258 CLOSE c_from_ps_loc;
4259 EXCEPTION
4260 WHEN OTHERS THEN
4261 log('Error in DNB insert rel party site details : '||SQLERRM);
4262 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4263 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4264 APP_EXCEPTION.RAISE_EXCEPTION;
4265 END insert_party_site_details;
4266
4267 PROCEDURE check_int_ext_party_type(
4268 p_dup_set_id IN NUMBER,
4269 p_int_party OUT NOCOPY VARCHAR2,
4270 p_ext_party OUT NOCOPY VARCHAR2,
4271 p_merge_ok OUT NOCOPY VARCHAR2)
4272 IS
4273 l_merge_ok varchar2(1);
4274 flag varchar2(1);
4275 flag_prev varchar2(1);
4276
4277 cursor org_c(l_dup_set_id in NUMBER)
4278 is
4279 select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4280 from HZ_merge_parties dup, hz_parties hp, hz_organization_profiles orgpf
4281 where dup.batch_id = l_dup_set_id
4282 and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
4283 and hp.party_type = 'ORGANIZATION'
4284 and hp.party_id = orgpf.party_id(+)
4285 and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
4286
4287 /* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4288 from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_organization_profiles orgpf
4289 where dup.dup_set_id = l_dup_set_id
4290 and dup.dup_party_id = hp.party_id
4291 and hp.party_type = 'ORGANIZATION'
4292 and hp.party_id = orgpf.party_id(+)
4293 and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate)
4294 and NVL(dup.MERGE_FLAG,'Y') <> 'N';*/
4295
4296
4297 party_rec org_c%rowtype;
4298
4299 cursor person_c(l_dup_set_id in NUMBER)
4300 is
4301 select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4302 from HZ_merge_parties dup, hz_parties hp, hz_person_profiles orgpf
4303 where dup.batch_id = l_dup_set_id
4304 and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
4305 and hp.party_type = 'PERSON'
4306 and hp.party_id = orgpf.party_id(+)
4307 and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
4308
4309 /* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4310 from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_person_profiles orgpf
4311 where dup.dup_set_id = l_dup_set_id
4312 and dup.dup_party_id = hp.party_id
4313 and hp.party_type = 'PERSON'
4314 and hp.party_id = orgpf.party_id(+)
4315 and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate)
4316 and NVL(dup.MERGE_FLAG,'Y') <> 'N';*/
4317
4318 BEGIN
4319 l_merge_ok := 'Y';
4320 flag_prev := null;
4321
4322 -- check for organizations
4323 open org_c(p_dup_set_id);
4324 fetch org_c into party_rec;
4325 while (org_c%found)
4326 loop
4327 if(party_rec.internal_flag = 'Y')
4328 then
4329 if(p_int_party is null)
4330 then
4331 p_int_party := party_rec.party_name;
4332 else
4333 p_int_party := p_int_party || ',' ||party_rec.party_name;
4334 end if;
4335 else
4336 if(p_ext_party is null)
4337 then
4338 p_ext_party := party_rec.party_name;
4339 else
4340 p_ext_party := substr(p_ext_party || ',' ||party_rec.party_name, 1, 4000);
4341 end if;
4342
4343 end if;
4344 if ( flag_prev is null)
4345 then
4346 flag_prev := party_rec.internal_flag;
4347 end if;
4348 if(flag_prev = party_rec.internal_flag)
4349 then
4350 flag_prev := party_rec.internal_flag;
4351 else
4352 l_merge_ok := 'N';
4353 end if;
4354
4355 fetch org_c into party_rec;
4356 end loop;
4357 close org_c;
4358
4359
4360 -- check for person
4361 flag_prev := null;
4362 open person_c(p_dup_set_id);
4363 fetch person_c into party_rec;
4364 while (person_c%found)
4365 loop
4366 if(party_rec.internal_flag = 'Y')
4367 then
4368 if(p_int_party is null)
4369 then
4370 p_int_party := party_rec.party_name;
4371 else
4372 p_int_party := p_int_party || ',' ||party_rec.party_name;
4373 end if;
4374 else
4375 if(p_ext_party is null)
4376 then
4377 p_ext_party := party_rec.party_name;
4378 else
4379 p_ext_party := substr(p_ext_party || ',' ||party_rec.party_name, 1, 4000);
4380 end if;
4381
4382 end if;
4383 if ( flag_prev is null)
4384 then
4385 flag_prev := party_rec.internal_flag;
4386 end if;
4387 if(flag_prev = party_rec.internal_flag)
4388 then
4389 flag_prev := party_rec.internal_flag;
4390 else
4391 l_merge_ok := 'N';
4392 end if;
4393
4394 fetch person_c into party_rec;
4395 end loop;
4396 close person_c;
4397
4398 p_merge_ok := l_merge_ok;
4399
4400 EXCEPTION
4401 WHEN OTHERS THEN
4402 log('Error in check internal/external party type: '||SQLERRM);
4403 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4404 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4405 FND_MSG_PUB.ADD;
4406 APP_EXCEPTION.RAISE_EXCEPTION;
4407 END check_int_ext_party_type;
4408
4409
4410 END HZ_PARTY_MERGE;