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