[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_MERGE_EVENT_PKG
Source
4 --5093366
1 PACKAGE BODY HZ_EXTRACT_MERGE_EVENT_PKG AS
2 /*$Header: ARHMEVTB.pls 120.3.12010000.10 2009/06/09 09:43:36 vsegu ship $ */
3
5 FUNCTION get_object_type(
6 p_table_name IN VARCHAR2,
7 p_table_id IN NUMBER
8 ) RETURN VARCHAR2 IS
9
10 object_type VARCHAR2(30);
11
12 CURSOR get_contact_point_type IS
13 SELECT contact_point_type
14 FROM hz_contact_points
15 WHERE contact_point_id = p_table_id;
16 BEGIN
17 object_type := NULL;
18 object_type := HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(p_table_name,p_table_id);
19
20 IF object_type IS null THEN
21 IF p_table_name = 'HZ_CONTACT_POINTS' THEN
22 OPEN get_contact_point_type;
23 FETCH get_contact_point_type INTO object_type;
24 CLOSE get_contact_point_type;
25 ELSIF p_table_name = 'HZ_ORG_CONTACTS' THEN
26 return 'ORG_CONTACT';
27 ELSIF (p_table_name = 'HZ_FINANCIAL_REPORTS') THEN
28 return 'FIN_REPORT';
29 ELSIF (p_table_name = 'HZ_EMPLOYMENT_HISTORY') THEN
30 return 'EMP_HIST';
31 ELSIF (p_table_name = 'HZ_CUSTOMER_PROFILES') THEN
32 return 'CUST_PROFILE';
33 ELSE
34 return p_table_name;
35 END IF;
36 END IF;
37
38 return object_type;
39
40 END get_object_type;
41
42 --Function returns operating unit name
43 FUNCTION get_operating_unit( p_org_id NUMBER) RETURN VARCHAR2 IS
44 l_operating_unit VARCHAR2(240);
45 BEGIN
46 SELECT name INTO l_operating_unit
47 FROM hr_operating_units
48 WHERE organization_id = p_org_id;
49
50 return l_operating_unit;
51
52 END get_operating_unit;
53
54 --------------------------------------
55 --
56 -- PROCEDURE get_account_merge_event_data
57 --
58 -- DESCRIPTION
59 -- Get account merge details.
60 --
61 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
62 --
63 -- ARGUMENTS
64 -- IN: p_init_msg_list
65 -- p_customer_merge_header_id
66 -- OUT:x_account_merge_obj
67 -- x_return_status
68 -- x_msg_count
69 -- x_msg_data
70 -- NOTES
71 --
72 -- MODIFICATION HISTORY
73 --
74 --
75 -- 25-AUG-2005 S V Sowjanya Created.
76
77
78 PROCEDURE get_account_merge_event_data(
79 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
80 p_customer_merge_header_id IN NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2
81 x_account_merge_obj OUT NOCOPY HZ_ACCT_MERGE_OBJ,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
85 ) IS
86 l_debug_prefix VARCHAR2(30) := '';
87 CURSOR account_merge_details IS
88
89 SELECT HZ_ACCT_MERGE_OBJ(
90 p_customer_merge_header_id,
91 mh.request_id,
92 mh.created_by,
93 mh.creation_date,
94 mh.last_update_login,
95 mh.last_update_date,
96 mh.last_updated_by,
97 HZ_PARTY_ORIG_SYS_REF_OBJ(
98 tp.party_id,
99 tp.party_number,
100 tp.party_name,
101 tp.party_type,
102 HZ_ORIG_SYS_REF_OBJ_TBL()),
103 HZ_PARTY_ORIG_SYS_REF_OBJ(
104 fp.party_id,
105 fp.party_number,
106 fp.party_name,
107 fp.party_type,
108 HZ_ORIG_SYS_REF_OBJ_TBL()),
109 HZ_ACCT_ORIG_SYS_REF_OBJ(
110 ta.cust_account_id,
111 ta.account_name,
112 ta.account_number,
113 HZ_ORIG_SYS_REF_OBJ_TBL()),
114 CAST(MULTISET (
115 SELECT HZ_ACCT_ORIG_SYS_REF_OBJ(
116 fa.cust_account_id,
117 fa.account_name,
118 fa.account_number,
119 HZ_ORIG_SYS_REF_OBJ_TBL())
120 FROM hz_cust_accounts_m fa
121 WHERE fa.cust_account_id = mh.duplicate_id
122 ) AS HZ_ACCT_ORIG_SYS_REF_OBJ_TBL)
123 )
124 FROM ra_customer_merge_headers mh, hz_cust_accounts ta, hz_parties tp, hz_cust_accounts_m fa, hz_parties fp
125 WHERE mh.customer_merge_header_id = p_customer_merge_header_id
126 AND ta.cust_account_id = mh.customer_id
127 AND tp.party_id = ta.party_id
128 AND fa.cust_account_id = mh.duplicate_id
129 AND fp.party_id = fa.party_id
130 AND rownum = 1;
131
132
133 BEGIN
134
135 -- initialize API return status to success.
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137
138 -- Initialize message list if p_init_msg_list is set to TRUE
139 IF FND_API.to_Boolean(p_init_msg_list) THEN
140 FND_MSG_PUB.initialize;
141 END IF;
142
143 -- Debug info.
144 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
145 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(+)',
146 p_prefix=>l_debug_prefix,
147 p_msg_level=>fnd_log.level_procedure);
148 END IF;
149
150 open account_merge_details;
151 fetch account_merge_details into x_account_merge_obj;
152 close account_merge_details;
153
154 -- SSM for merge-to acct party obj
155 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
156 (p_init_msg_list => fnd_api.g_false,
157 p_owner_table_id => x_account_merge_obj.merge_to_acct_party_obj.party_id,
158 p_owner_table_name => 'HZ_PARTIES',
159 p_action_type => NULL,
160 x_orig_sys_ref_objs => x_account_merge_obj.merge_to_acct_party_obj.orig_sys_objs,
161 x_return_status => x_return_status,
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data);
164
165 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
166 RAISE FND_API.G_EXC_ERROR;
167 END IF;
168
169 -- SSM for merge-from acct party obj
170 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
171 (p_init_msg_list => fnd_api.g_false,
172 p_owner_table_id => x_account_merge_obj.merge_from_acct_party_obj.party_id,
173 p_owner_table_name => 'HZ_PARTIES',
174 p_action_type => NULL,
175 x_orig_sys_ref_objs => x_account_merge_obj.merge_from_acct_party_obj.orig_sys_objs,
176 x_return_status => x_return_status,
177 x_msg_count => x_msg_count,
178 x_msg_data => x_msg_data);
179
180 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183 -- SSM for merge to account obj
184 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
185 (p_init_msg_list => fnd_api.g_false,
186 p_owner_table_id => x_account_merge_obj.merge_to_account_obj.cust_acct_id,
187 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
188 p_action_type => NULL,
189 x_orig_sys_ref_objs => x_account_merge_obj.merge_to_account_obj.orig_sys_objs,
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data);
193
194 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 FOR I in 1..x_account_merge_obj.merge_from_account_objs.count LOOP
199
200 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
201 (p_init_msg_list => fnd_api.g_false,
202 p_owner_table_id => x_account_merge_obj.merge_from_account_objs(I).cust_acct_id,
203 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
204 p_action_type => NULL,
205 x_orig_sys_ref_objs => x_account_merge_obj.merge_from_account_objs(I).orig_sys_objs,
206 x_return_status => x_return_status,
207 x_msg_count => x_msg_count,
208 x_msg_data => x_msg_data);
209
210 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
211 RAISE FND_API.G_EXC_ERROR;
212 END IF;
213 END LOOP;
214
215 -- Debug info.
216 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
217 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
218 p_msg_data=>x_msg_data,
219 p_msg_type=>'WARNING',
220 p_msg_level=>fnd_log.level_exception);
221 END IF;
222
223 -- Debug info.
224 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
225 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
226 p_prefix=>l_debug_prefix,
227 p_msg_level=>fnd_log.level_procedure);
228 END IF;
229 EXCEPTION
230
231 WHEN fnd_api.g_exc_error THEN
232 x_return_status := fnd_api.g_ret_sts_error;
233
234 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
235 p_count => x_msg_count,
236 p_data => x_msg_data);
237
238 -- Debug info.
239 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
240 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
241 p_msg_data=>x_msg_data,
242 p_msg_type=>'ERROR',
243 p_msg_level=>fnd_log.level_error);
244 END IF;
245 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
246 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
247 p_prefix=>l_debug_prefix,
248 p_msg_level=>fnd_log.level_procedure);
249 END IF;
250 WHEN fnd_api.g_exc_unexpected_error THEN
251 x_return_status := fnd_api.g_ret_sts_unexp_error;
252
253 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
254 p_count => x_msg_count,
255 p_data => x_msg_data);
256
257 -- Debug info.
258 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
259 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
260 p_msg_data=>x_msg_data,
261 p_msg_type=>'UNEXPECTED ERROR',
262 p_msg_level=>fnd_log.level_error);
263 END IF;
264 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
265 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
266 p_prefix=>l_debug_prefix,
267 p_msg_level=>fnd_log.level_procedure);
268 END IF;
269 WHEN OTHERS THEN
270 x_return_status := fnd_api.g_ret_sts_unexp_error;
271
272 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
273 fnd_message.set_token('ERROR' ,SQLERRM);
274 fnd_msg_pub.add;
275
276 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
277 p_count => x_msg_count,
278 p_data => x_msg_data);
279
280 -- Debug info.
281 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
282 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
283 p_msg_data=>x_msg_data,
284 p_msg_type=>'SQL ERROR',
285 p_msg_level=>fnd_log.level_error);
286 END IF;
287 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
288 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
289 p_prefix=>l_debug_prefix,
290 p_msg_level=>fnd_log.level_procedure);
291 END IF;
292 END get_account_merge_event_data;
293
294 --------------------------------------
295 --
296 -- PROCEDURE get_account_merge_event_data
297 --
298 -- DESCRIPTION
299 -- Get account merge details.
300 --
301 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
302 --
303 -- ARGUMENTS
304 -- IN: p_init_msg_list
305 -- p_customer_merge_header_id
306 -- p_get_merge_detail_flag
307 -- OUT:x_account_merge_v2_obj
308 -- x_return_status
309 -- x_msg_count
310 -- x_msg_data
311 -- NOTES
312 --
313 -- MODIFICATION HISTORY
314 --
315 --
316 -- 6-Jan-2009 S V Sowjanya Created.
317
318
319 PROCEDURE get_account_merge_event_data(
320 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
321 p_customer_merge_header_id IN NUMBER,
322 p_get_merge_detail_flag IN VARCHAR2 := 'N',
323 x_account_merge_v2_obj OUT NOCOPY HZ_ACCOUNT_MERGE_V2_OBJ,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2
327 ) IS
328 l_debug_prefix VARCHAR2(30) := '';
329 CURSOR account_merge_details IS
330
331 SELECT HZ_ACCOUNT_MERGE_V2_OBJ(
332 p_customer_merge_header_id,
333 mh.delete_duplicate_flag,
334 mh.request_id,
335 mh.created_by,
336 mh.creation_date,
337 mh.last_update_login,
338 mh.last_update_date,
339 mh.last_updated_by,
340 HZ_PARTY_ORIG_SYS_REF_OBJ(
341 tp.party_id,
342 tp.party_number,
343 tp.party_name,
344 tp.party_type,
345 HZ_ORIG_SYS_REF_OBJ_TBL()),
346 HZ_PARTY_ORIG_SYS_REF_OBJ(
347 fp.party_id,
348 fp.party_number,
349 fp.party_name,
350 fp.party_type,
351 HZ_ORIG_SYS_REF_OBJ_TBL()),
352 HZ_ACCT_ORIG_SYS_REF_OBJ(
353 ta.cust_account_id,
354 ta.account_name,
355 ta.account_number,
356 HZ_ORIG_SYS_REF_OBJ_TBL()),
357 CAST(MULTISET (
358 SELECT HZ_ACCT_ORIG_SYS_REF_OBJ(
359 fa.cust_account_id,
360 fa.account_name,
361 fa.account_number,
362 HZ_ORIG_SYS_REF_OBJ_TBL())
363 FROM hz_cust_accounts_m fa
364 WHERE fa.cust_account_id = mh.duplicate_id
365 ) AS HZ_ACCT_ORIG_SYS_REF_OBJ_TBL),
366 HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --ACCT_SITE_OBJS
367 HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --ACCT_SITE_USES_OBJS
368 HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --ACCT_ROLE_OBJS
369 HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --CUSTOMER_PROFILE_OBJS
370 HZ_ACCT_MERGE_DETAIL_OBJ_TBL(), --CUST_PROFILE_AMT_OBJS
371 HZ_ACCT_MERGE_DETAIL_OBJ_TBL() --ACCT_REL_OBJS
372 )
373 FROM ra_customer_merge_headers mh, hz_cust_accounts ta, hz_parties tp, hz_cust_accounts_m fa, hz_parties fp
374 WHERE mh.customer_merge_header_id = p_customer_merge_header_id
375 AND ta.cust_account_id = mh.customer_id
376 AND tp.party_id = ta.party_id
377 AND fa.cust_account_id = mh.duplicate_id
378 AND fp.party_id = fa.party_id
379 AND rownum = 1;
380
381 CURSOR get_site_details IS
382
383 SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
384 get_object_type('HZ_CUST_ACCT_SITES', cm.duplicate_address_id),
385 decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer'),
386 cm.org_id,
387 get_operating_unit(cm.org_id),
388 cm.duplicate_address_id,
389 CAST(MULTISET(
390 SELECT HZ_ORIG_SYS_REF_OBJ(
391 NULL,
392 ORIG_SYSTEM_REF_ID,
393 ORIG_SYSTEM,
394 ORIG_SYSTEM_REFERENCE,
395 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
396 OWNER_TABLE_ID,
397 STATUS,
398 REASON_CODE,
399 OLD_ORIG_SYSTEM_REFERENCE,
400 START_DATE_ACTIVE,
401 END_DATE_ACTIVE,
402 PROGRAM_UPDATE_DATE,
403 CREATED_BY_MODULE,
404 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
405 CREATION_DATE,
406 LAST_UPDATE_DATE,
407 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
408 ATTRIBUTE_CATEGORY,
409 ATTRIBUTE1,
410 ATTRIBUTE2,
411 ATTRIBUTE3,
412 ATTRIBUTE4,
413 ATTRIBUTE5,
414 ATTRIBUTE6,
415 ATTRIBUTE7,
416 ATTRIBUTE8,
417 ATTRIBUTE9,
418 ATTRIBUTE10,
419 ATTRIBUTE11,
420 ATTRIBUTE12,
421 ATTRIBUTE13,
422 ATTRIBUTE14,
423 ATTRIBUTE15,
424 ATTRIBUTE16,
425 ATTRIBUTE17,
426 ATTRIBUTE18,
427 ATTRIBUTE19,
428 ATTRIBUTE20
429 )
430 FROM HZ_ORIG_SYS_REFERENCES
431 WHERE OWNER_TABLE_ID = cm.duplicate_address_id
432 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
433 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
434 cm.customer_address_id,
435 CAST(MULTISET(
436 SELECT HZ_ORIG_SYS_REF_OBJ(
437 NULL,
438 ORIG_SYSTEM_REF_ID,
439 ORIG_SYSTEM,
440 ORIG_SYSTEM_REFERENCE,
441 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
442 OWNER_TABLE_ID,
443 STATUS,
444 REASON_CODE,
445 OLD_ORIG_SYSTEM_REFERENCE,
446 START_DATE_ACTIVE,
447 END_DATE_ACTIVE,
448 PROGRAM_UPDATE_DATE,
449 CREATED_BY_MODULE,
450 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
451 CREATION_DATE,
452 LAST_UPDATE_DATE,
453 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
454 ATTRIBUTE_CATEGORY,
455 ATTRIBUTE1,
456 ATTRIBUTE2,
457 ATTRIBUTE3,
458 ATTRIBUTE4,
459 ATTRIBUTE5,
460 ATTRIBUTE6,
461 ATTRIBUTE7,
462 ATTRIBUTE8,
463 ATTRIBUTE9,
464 ATTRIBUTE10,
465 ATTRIBUTE11,
466 ATTRIBUTE12,
467 ATTRIBUTE13,
468 ATTRIBUTE14,
469 ATTRIBUTE15,
470 ATTRIBUTE16,
471 ATTRIBUTE17,
472 ATTRIBUTE18,
473 ATTRIBUTE19,
474 ATTRIBUTE20
475 )
476 FROM HZ_ORIG_SYS_REFERENCES
477 WHERE OWNER_TABLE_ID = cm.customer_address_id
478 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
479 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
480 cm.duplicate_id,
481 CAST(MULTISET(
482 SELECT HZ_ORIG_SYS_REF_OBJ(
483 NULL,
484 ORIG_SYSTEM_REF_ID,
485 ORIG_SYSTEM,
486 ORIG_SYSTEM_REFERENCE,
490 REASON_CODE,
487 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
488 OWNER_TABLE_ID,
489 STATUS,
491 OLD_ORIG_SYSTEM_REFERENCE,
492 START_DATE_ACTIVE,
493 END_DATE_ACTIVE,
494 PROGRAM_UPDATE_DATE,
495 CREATED_BY_MODULE,
496 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
497 CREATION_DATE,
498 LAST_UPDATE_DATE,
499 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
500 ATTRIBUTE_CATEGORY,
501 ATTRIBUTE1,
502 ATTRIBUTE2,
503 ATTRIBUTE3,
504 ATTRIBUTE4,
505 ATTRIBUTE5,
506 ATTRIBUTE6,
507 ATTRIBUTE7,
508 ATTRIBUTE8,
509 ATTRIBUTE9,
510 ATTRIBUTE10,
511 ATTRIBUTE11,
512 ATTRIBUTE12,
513 ATTRIBUTE13,
514 ATTRIBUTE14,
515 ATTRIBUTE15,
516 ATTRIBUTE16,
517 ATTRIBUTE17,
518 ATTRIBUTE18,
519 ATTRIBUTE19,
520 ATTRIBUTE20
521 )
522 FROM HZ_ORIG_SYS_REFERENCES
523 WHERE OWNER_TABLE_ID = cm.duplicate_id
524 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
525 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
526 cm.customer_id,
527 CAST(MULTISET(
528 SELECT HZ_ORIG_SYS_REF_OBJ(
529 NULL,
530 ORIG_SYSTEM_REF_ID,
531 ORIG_SYSTEM,
532 ORIG_SYSTEM_REFERENCE,
533 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
534 OWNER_TABLE_ID,
535 STATUS,
536 REASON_CODE,
537 OLD_ORIG_SYSTEM_REFERENCE,
538 START_DATE_ACTIVE,
539 END_DATE_ACTIVE,
540 PROGRAM_UPDATE_DATE,
541 CREATED_BY_MODULE,
542 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
543 CREATION_DATE,
544 LAST_UPDATE_DATE,
545 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
546 ATTRIBUTE_CATEGORY,
547 ATTRIBUTE1,
548 ATTRIBUTE2,
549 ATTRIBUTE3,
550 ATTRIBUTE4,
551 ATTRIBUTE5,
552 ATTRIBUTE6,
553 ATTRIBUTE7,
554 ATTRIBUTE8,
555 ATTRIBUTE9,
556 ATTRIBUTE10,
557 ATTRIBUTE11,
558 ATTRIBUTE12,
559 ATTRIBUTE13,
560 ATTRIBUTE14,
561 ATTRIBUTE15,
562 ATTRIBUTE16,
563 ATTRIBUTE17,
564 ATTRIBUTE18,
565 ATTRIBUTE19,
566 ATTRIBUTE20
567 )
568 FROM HZ_ORIG_SYS_REFERENCES
569 WHERE OWNER_TABLE_ID = cm.customer_id
570 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
571 )AS HZ_ORIG_SYS_REF_OBJ_TBL)
572 )
573 FROM (SELECT distinct duplicate_address_id, customer_address_id,
574 duplicate_id, customer_id, org_id, customer_createsame
575 FROM ra_customer_merges cm
576 WHERE customer_merge_header_id = p_customer_merge_header_id
577 AND duplicate_id <> customer_id) cm;
578
579 CURSOR get_site_use_details IS
580
581 SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
582 get_object_type('HZ_CUST_SITE_USES', cm.duplicate_site_id),
583 decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer'),
584 cm.org_id,
585 get_operating_unit(cm.org_id),
586 cm.duplicate_site_id,
587 CAST(MULTISET(
588 SELECT HZ_ORIG_SYS_REF_OBJ(
589 NULL,
590 ORIG_SYSTEM_REF_ID,
591 ORIG_SYSTEM,
592 ORIG_SYSTEM_REFERENCE,
593 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
594 OWNER_TABLE_ID,
595 STATUS,
596 REASON_CODE,
597 OLD_ORIG_SYSTEM_REFERENCE,
598 START_DATE_ACTIVE,
599 END_DATE_ACTIVE,
600 PROGRAM_UPDATE_DATE,
601 CREATED_BY_MODULE,
602 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
603 CREATION_DATE,
604 LAST_UPDATE_DATE,
605 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
606 ATTRIBUTE_CATEGORY,
607 ATTRIBUTE1,
608 ATTRIBUTE2,
609 ATTRIBUTE3,
610 ATTRIBUTE4,
611 ATTRIBUTE5,
612 ATTRIBUTE6,
613 ATTRIBUTE7,
614 ATTRIBUTE8,
615 ATTRIBUTE9,
616 ATTRIBUTE10,
617 ATTRIBUTE11,
618 ATTRIBUTE12,
619 ATTRIBUTE13,
620 ATTRIBUTE14,
621 ATTRIBUTE15,
622 ATTRIBUTE16,
623 ATTRIBUTE17,
624 ATTRIBUTE18,
625 ATTRIBUTE19,
626 ATTRIBUTE20
627 )
628 FROM HZ_ORIG_SYS_REFERENCES
629 WHERE OWNER_TABLE_ID = cm.duplicate_site_id
630 AND OWNER_TABLE_NAME = 'HZ_CUST_SITE_USES_ALL'
631 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
632 cm.customer_site_id,
633 CAST(MULTISET(
634 SELECT HZ_ORIG_SYS_REF_OBJ(
635 NULL,
636 ORIG_SYSTEM_REF_ID,
637 ORIG_SYSTEM,
638 ORIG_SYSTEM_REFERENCE,
639 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
640 OWNER_TABLE_ID,
641 STATUS,
642 REASON_CODE,
643 OLD_ORIG_SYSTEM_REFERENCE,
644 START_DATE_ACTIVE,
645 END_DATE_ACTIVE,
646 PROGRAM_UPDATE_DATE,
647 CREATED_BY_MODULE,
648 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
649 CREATION_DATE,
650 LAST_UPDATE_DATE,
651 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
652 ATTRIBUTE_CATEGORY,
653 ATTRIBUTE1,
654 ATTRIBUTE2,
655 ATTRIBUTE3,
656 ATTRIBUTE4,
657 ATTRIBUTE5,
658 ATTRIBUTE6,
659 ATTRIBUTE7,
660 ATTRIBUTE8,
661 ATTRIBUTE9,
662 ATTRIBUTE10,
663 ATTRIBUTE11,
664 ATTRIBUTE12,
665 ATTRIBUTE13,
666 ATTRIBUTE14,
667 ATTRIBUTE15,
668 ATTRIBUTE16,
669 ATTRIBUTE17,
670 ATTRIBUTE18,
671 ATTRIBUTE19,
672 ATTRIBUTE20
673 )
674 FROM HZ_ORIG_SYS_REFERENCES
675 WHERE OWNER_TABLE_ID = cm.customer_site_id
676 AND OWNER_TABLE_NAME = 'HZ_CUST_SITE_USES_ALL'
677 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
678 cm.duplicate_address_id,
679 CAST(MULTISET(
680 SELECT HZ_ORIG_SYS_REF_OBJ(
681 NULL,
682 ORIG_SYSTEM_REF_ID,
683 ORIG_SYSTEM,
684 ORIG_SYSTEM_REFERENCE,
685 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
686 OWNER_TABLE_ID,
687 STATUS,
688 REASON_CODE,
689 OLD_ORIG_SYSTEM_REFERENCE,
690 START_DATE_ACTIVE,
691 END_DATE_ACTIVE,
692 PROGRAM_UPDATE_DATE,
693 CREATED_BY_MODULE,
694 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
695 CREATION_DATE,
696 LAST_UPDATE_DATE,
697 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
698 ATTRIBUTE_CATEGORY,
699 ATTRIBUTE1,
700 ATTRIBUTE2,
701 ATTRIBUTE3,
702 ATTRIBUTE4,
703 ATTRIBUTE5,
704 ATTRIBUTE6,
705 ATTRIBUTE7,
706 ATTRIBUTE8,
707 ATTRIBUTE9,
708 ATTRIBUTE10,
709 ATTRIBUTE11,
710 ATTRIBUTE12,
711 ATTRIBUTE13,
712 ATTRIBUTE14,
713 ATTRIBUTE15,
714 ATTRIBUTE16,
715 ATTRIBUTE17,
716 ATTRIBUTE18,
717 ATTRIBUTE19,
718 ATTRIBUTE20
719 )
720 FROM HZ_ORIG_SYS_REFERENCES
721 WHERE OWNER_TABLE_ID = cm.duplicate_address_id
722 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
723 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
724 cm.customer_address_id,
725 CAST(MULTISET(
726 SELECT HZ_ORIG_SYS_REF_OBJ(
727 NULL,
728 ORIG_SYSTEM_REF_ID,
729 ORIG_SYSTEM,
730 ORIG_SYSTEM_REFERENCE,
731 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
732 OWNER_TABLE_ID,
733 STATUS,
734 REASON_CODE,
735 OLD_ORIG_SYSTEM_REFERENCE,
736 START_DATE_ACTIVE,
737 END_DATE_ACTIVE,
738 PROGRAM_UPDATE_DATE,
739 CREATED_BY_MODULE,
740 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
741 CREATION_DATE,
742 LAST_UPDATE_DATE,
743 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
744 ATTRIBUTE_CATEGORY,
745 ATTRIBUTE1,
746 ATTRIBUTE2,
747 ATTRIBUTE3,
748 ATTRIBUTE4,
749 ATTRIBUTE5,
750 ATTRIBUTE6,
751 ATTRIBUTE7,
752 ATTRIBUTE8,
753 ATTRIBUTE9,
754 ATTRIBUTE10,
755 ATTRIBUTE11,
756 ATTRIBUTE12,
757 ATTRIBUTE13,
758 ATTRIBUTE14,
759 ATTRIBUTE15,
760 ATTRIBUTE16,
761 ATTRIBUTE17,
762 ATTRIBUTE18,
763 ATTRIBUTE19,
764 ATTRIBUTE20
765 )
766 FROM HZ_ORIG_SYS_REFERENCES
767 WHERE OWNER_TABLE_ID = cm.customer_address_id
768 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
769 )AS HZ_ORIG_SYS_REF_OBJ_TBL)
770 )
771 FROM ra_customer_merges cm
772 WHERE customer_merge_header_id = p_customer_merge_header_id;
773
774 CURSOR get_customer_profiles IS
775
776 SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
777 get_object_type('HZ_CUSTOMER_PROFILES', from_profile_id),
778 operation,
779 NULL,
780 NULL,
781 from_profile_id,
782 HZ_ORIG_SYS_REF_OBJ_TBL(),
783 to_profile_id,
784 HZ_ORIG_SYS_REF_OBJ_TBL(),
785 from_parent_id,
786 HZ_ORIG_SYS_REF_OBJ_TBL(),
787 to_parent_id,
788 HZ_ORIG_SYS_REF_OBJ_TBL()
789 )
790 FROM (SELECT 'Merge' operation, cpf.cust_account_profile_id from_profile_id,
791 cpt.cust_account_profile_id to_profile_id,
792 cmh.duplicate_id from_parent_id, cmh.customer_id to_parent_id
793 FROM ra_customer_merge_headers cmh, hz_customer_profiles_m cpf, hz_customer_profiles cpt
794 WHERE cmh.customer_merge_header_id = p_customer_merge_header_id
795 AND cmh.duplicate_id <> cmh.customer_id
796 AND cpf.cust_account_id = cmh.duplicate_id
797 AND cpf.customer_merge_header_id = p_customer_merge_header_id
801
798 AND cpt.cust_account_id = cmh.customer_id
799 AND cpf.site_use_id IS NULL
800 AND cpt.site_use_id IS NULL
802 UNION
803
804 SELECT decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer') operation,
805 cpf.cust_account_profile_id from_profile_id,
806 cpt.cust_account_profile_id to_profile_id,
807 cm.duplicate_site_id from_parent_id,
808 cm.customer_site_id to_parent_id
809 FROM ra_customer_merges cm, hz_customer_profiles_m cpf, hz_customer_profiles cpt
810 WHERE cm.customer_merge_header_id = p_customer_merge_header_id
811 AND cpf.customer_merge_header_id = p_customer_merge_header_id
812 AND cpf.cust_account_id = cm.duplicate_id
813 AND cpf.site_use_id = cm.duplicate_site_id
814 AND cpt.cust_account_id = cm.customer_id
815 AND cpt.site_use_id = cm.customer_site_id);
816
817 CURSOR get_cust_profile_amts IS
818
819 SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
820 get_object_type('HZ_CUST_PROFILE_AMTS', from_profile_amt_id),
821 operation,
822 NULL,
823 NULL,
824 from_profile_amt_id,
825 HZ_ORIG_SYS_REF_OBJ_TBL(),
826 to_profile_amt_id,
827 HZ_ORIG_SYS_REF_OBJ_TBL(),
828 from_parent_id,
829 HZ_ORIG_SYS_REF_OBJ_TBL(),
830 to_parent_id,
831 HZ_ORIG_SYS_REF_OBJ_TBL()
832 )
833 FROM (SELECT 'Merge' operation, cpaf.cust_acct_profile_amt_id from_profile_amt_id,
834 cpat.cust_acct_profile_amt_id to_profile_amt_id,
835 cmh.duplicate_id from_parent_id, cmh.customer_id to_parent_id
836 FROM ra_customer_merge_headers cmh, hz_cust_profile_amts_m cpaf, hz_cust_profile_amts cpat
837 WHERE cmh.customer_merge_header_id = p_customer_merge_header_id
838 AND cmh.duplicate_id <> cmh.customer_id
839 AND cpaf.customer_merge_header_id = p_customer_merge_header_id
840 AND cpaf.cust_account_id = cmh.duplicate_id
841 AND cpat.cust_account_id = cmh.customer_id
842 AND cpaf.currency_code = cpat.currency_code
843 AND cpaf.site_use_id IS NULL
844 AND cpat.site_use_id IS NULL
845
846 UNION
847
848 SELECT decode(cm.customer_createsame, 'N', 'Merge', 'Y', 'Transfer') operation,
849 cpaf.cust_acct_profile_amt_id from_profile_amt_id,
850 cpat.cust_acct_profile_amt_id to_profile_amt_id,
851 cm.duplicate_site_id from_parent_id,
852 cm.customer_site_id to_parent_id
853 FROM ra_customer_merges cm, hz_cust_profile_amts_m cpaf, hz_cust_profile_amts cpat
854 WHERE cm.customer_merge_header_id = p_customer_merge_header_id
855 AND cpaf.customer_merge_header_id = p_customer_merge_header_id
856 AND cpaf.cust_account_id = cm.duplicate_id
857 AND cpaf.site_use_id = cm.duplicate_site_id
858 AND cpat.cust_account_id = cm.customer_id
859 AND cpat.site_use_id = cm.customer_site_id
860 AND cpaf.currency_code = cpat.currency_code);
861
862 CURSOR get_account_roles IS
863 SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
864 get_object_type('HZ_CUST_ACCOUNT_ROLES',from_role_id),
865 operation,
866 NULL,
867 NULL,
868 from_role_id,
869 HZ_ORIG_SYS_REF_OBJ_TBL(),
870 to_role_id,
871 HZ_ORIG_SYS_REF_OBJ_TBL(),
872 from_parent_id,
873 HZ_ORIG_SYS_REF_OBJ_TBL(),
874 to_parent_id,
875 HZ_ORIG_SYS_REF_OBJ_TBL()
876 )
877 FROM (SELECT DISTINCT carf.cust_account_role_id from_role_id,
878 carf.cust_account_role_id to_role_id,
879 Nvl(carf.cust_acct_site_id,cm.duplicate_id) from_parent_id,
880 Decode(carf.cust_acct_site_id, NULL,cm.customer_id,cm.customer_address_id) to_parent_id, 'Transfer' operation
881 FROM (SELECT DISTINCT duplicate_id, duplicate_address_id, customer_address_id, customer_id
882 FROM ra_customer_merges cm
883 WHERE cm.customer_merge_header_id = p_customer_merge_header_id
884 AND cm.duplicate_id <> cm.customer_id) cm, hz_cust_account_roles_m carf
885 WHERE carf.customer_merge_header_id = p_customer_merge_header_id
886 AND ((carf.cust_account_id = cm.duplicate_id AND carf.cust_acct_site_id = cm.duplicate_address_id)
887 OR (carf.cust_account_id = cm.duplicate_id AND carf.cust_acct_site_id IS NULL))
888 AND NOT EXISTS (SELECT 'Y'
889 FROM hz_cust_account_roles
890 WHERE cust_account_role_id = carf.cust_account_role_id
891 AND cust_account_id = cm.duplicate_id));
892
893 CURSOR get_acct_rels(duplicate_id number, customer_id number) IS
894 SELECT HZ_ACCOUNT_MERGE_DETAIL_OBJ(
895 get_object_type('HZ_CUST_ACCT_RELATE_ALL',crelf.cust_acct_relate_id),
896 decode(crelt.created_by_module,'HZ_TCA_CUSTOMER_MERGE','Transfer','Merge'),
897 crelt.org_id,
898 get_operating_unit(crelt.org_id),
899 crelf.cust_acct_relate_id,
900 HZ_ORIG_SYS_REF_OBJ_TBL(),
901 crelt.cust_acct_relate_id,
902 HZ_ORIG_SYS_REF_OBJ_TBL(),
903 duplicate_id,
904 CAST(MULTISET(
905 SELECT HZ_ORIG_SYS_REF_OBJ(
906 NULL,
907 ORIG_SYSTEM_REF_ID,
908 ORIG_SYSTEM,
909 ORIG_SYSTEM_REFERENCE,
910 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
911 OWNER_TABLE_ID,
912 STATUS,
913 REASON_CODE,
914 OLD_ORIG_SYSTEM_REFERENCE,
915 START_DATE_ACTIVE,
916 END_DATE_ACTIVE,
917 PROGRAM_UPDATE_DATE,
918 CREATED_BY_MODULE,
922 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
919 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
920 CREATION_DATE,
921 LAST_UPDATE_DATE,
923 ATTRIBUTE_CATEGORY,
924 ATTRIBUTE1,
925 ATTRIBUTE2,
926 ATTRIBUTE3,
927 ATTRIBUTE4,
928 ATTRIBUTE5,
929 ATTRIBUTE6,
930 ATTRIBUTE7,
931 ATTRIBUTE8,
932 ATTRIBUTE9,
933 ATTRIBUTE10,
934 ATTRIBUTE11,
935 ATTRIBUTE12,
936 ATTRIBUTE13,
937 ATTRIBUTE14,
938 ATTRIBUTE15,
939 ATTRIBUTE16,
940 ATTRIBUTE17,
941 ATTRIBUTE18,
942 ATTRIBUTE19,
943 ATTRIBUTE20
944 )
945 FROM HZ_ORIG_SYS_REFERENCES
946 WHERE OWNER_TABLE_ID = duplicate_id
947 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
948 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
949 customer_id,
950 CAST(MULTISET(
951 SELECT HZ_ORIG_SYS_REF_OBJ(
952 NULL,
953 ORIG_SYSTEM_REF_ID,
954 ORIG_SYSTEM,
955 ORIG_SYSTEM_REFERENCE,
956 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
957 OWNER_TABLE_ID,
958 STATUS,
959 REASON_CODE,
960 OLD_ORIG_SYSTEM_REFERENCE,
961 START_DATE_ACTIVE,
962 END_DATE_ACTIVE,
963 PROGRAM_UPDATE_DATE,
964 CREATED_BY_MODULE,
965 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
966 CREATION_DATE,
967 LAST_UPDATE_DATE,
968 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
969 ATTRIBUTE_CATEGORY,
970 ATTRIBUTE1,
971 ATTRIBUTE2,
972 ATTRIBUTE3,
973 ATTRIBUTE4,
974 ATTRIBUTE5,
975 ATTRIBUTE6,
976 ATTRIBUTE7,
977 ATTRIBUTE8,
978 ATTRIBUTE9,
979 ATTRIBUTE10,
980 ATTRIBUTE11,
981 ATTRIBUTE12,
982 ATTRIBUTE13,
983 ATTRIBUTE14,
984 ATTRIBUTE15,
985 ATTRIBUTE16,
986 ATTRIBUTE17,
987 ATTRIBUTE18,
988 ATTRIBUTE19,
989 ATTRIBUTE20
990 )
991 FROM HZ_ORIG_SYS_REFERENCES
992 WHERE OWNER_TABLE_ID = customer_id
993 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
994 )AS HZ_ORIG_SYS_REF_OBJ_TBL)
995 )
996 FROM hz_cust_acct_relate_all_m crelf, hz_cust_acct_relate_all crelt
997 WHERE ((crelf.cust_account_id = duplicate_id
998 AND crelt.cust_account_id = customer_id
999 AND crelt.related_cust_account_id = crelf.related_cust_account_id)
1000 OR
1001 (crelf.related_cust_account_id = duplicate_id
1002 AND crelt.related_cust_account_id = customer_id
1003 AND crelt.cust_account_id = crelf.cust_account_id))
1004 AND crelt.org_id IN (SELECT org_id FROM ra_customer_merges
1005 WHERE customer_merge_header_id = p_customer_merge_header_id)
1006 AND crelf.org_id = crelt.org_id;
1007
1008 l_site_use_id NUMBER;
1009 l_header_id NUMBER;
1010 BEGIN
1011
1012 -- initialize API return status to success.
1013 x_return_status := FND_API.G_RET_STS_SUCCESS;
1014
1015 -- Initialize message list if p_init_msg_list is set to TRUE
1016 IF FND_API.to_Boolean(p_init_msg_list) THEN
1017 FND_MSG_PUB.initialize;
1018 END IF;
1019
1020 -- Debug info.
1021 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1022 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(+)',
1023 p_prefix=>l_debug_prefix,
1024 p_msg_level=>fnd_log.level_procedure);
1025 END IF;
1026
1027 IF p_get_merge_detail_flag NOT IN ('Y','N') THEN
1028 FND_MESSAGE.SET_NAME('AR','HZ_API_VAL_DEP_FIELDS');
1029 FND_MESSAGE.SET_TOKEN('COLUMN1','getMergeDetailFlag');
1030 FND_MESSAGE.SET_TOKEN('VALUE1',p_get_merge_detail_flag);
1031 FND_MESSAGE.SET_TOKEN('COLUMN2','getMergeDetailFlag');
1032 FND_MESSAGE.SET_TOKEN('VALUE2','Y/N');
1033 FND_MSG_PUB.ADD();
1034 RAISE fnd_api.g_exc_error;
1035 END IF;
1036
1037 BEGIN
1038 SELECT customer_merge_header_id INTO l_header_id
1039 FROM ra_customer_merge_headers
1040 WHERE customer_merge_header_id = p_customer_merge_header_id;
1041 EXCEPTION
1042 WHEN NO_DATA_FOUND THEN
1043 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_TCA_ID');
1044 FND_MSG_PUB.ADD();
1045 RAISE fnd_api.g_exc_error;
1046 END;
1047
1048 OPEN account_merge_details;
1049 FETCH account_merge_details into x_account_merge_v2_obj;
1050 CLOSE account_merge_details;
1051 -- SSM for merge-to acct party obj
1052 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1053 (p_init_msg_list => fnd_api.g_false,
1054 p_owner_table_id => x_account_merge_v2_obj.merge_to_acct_party_obj.party_id,
1055 p_owner_table_name => 'HZ_PARTIES',
1056 p_action_type => NULL,
1057 x_orig_sys_ref_objs => x_account_merge_v2_obj.merge_to_acct_party_obj.orig_sys_objs,
1058 x_return_status => x_return_status,
1059 x_msg_count => x_msg_count,
1060 x_msg_data => x_msg_data);
1061
1062 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065
1066 -- SSM for merge-from acct party obj
1067 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1068 (p_init_msg_list => fnd_api.g_false,
1069 p_owner_table_id => x_account_merge_v2_obj.merge_from_acct_party_obj.party_id,
1070 p_owner_table_name => 'HZ_PARTIES',
1071 p_action_type => NULL,
1072 x_orig_sys_ref_objs => x_account_merge_v2_obj.merge_from_acct_party_obj.orig_sys_objs,
1076
1073 x_return_status => x_return_status,
1074 x_msg_count => x_msg_count,
1075 x_msg_data => x_msg_data);
1077 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1078 RAISE FND_API.G_EXC_ERROR;
1079 END IF;
1080 -- SSM for merge to account obj
1081 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1082 (p_init_msg_list => fnd_api.g_false,
1083 p_owner_table_id => x_account_merge_v2_obj.merge_to_account_obj.cust_acct_id,
1084 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1085 p_action_type => NULL,
1086 x_orig_sys_ref_objs => x_account_merge_v2_obj.merge_to_account_obj.orig_sys_objs,
1087 x_return_status => x_return_status,
1088 x_msg_count => x_msg_count,
1089 x_msg_data => x_msg_data);
1090
1091 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1092 RAISE FND_API.G_EXC_ERROR;
1093 END IF;
1094
1095 FOR I in 1..x_account_merge_v2_obj.merge_from_account_objs.count LOOP
1096
1097 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1098 (p_init_msg_list => fnd_api.g_false,
1099 p_owner_table_id => x_account_merge_v2_obj.merge_from_account_objs(I).cust_acct_id,
1100 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1101 p_action_type => NULL,
1102 x_orig_sys_ref_objs => x_account_merge_v2_obj.merge_from_account_objs(I).orig_sys_objs,
1103 x_return_status => x_return_status,
1104 x_msg_count => x_msg_count,
1105 x_msg_data => x_msg_data);
1106
1107 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1108 RAISE FND_API.G_EXC_ERROR;
1109 END IF;
1110 END LOOP;
1111
1112 IF p_get_merge_detail_flag = 'N' THEN
1113 Return;
1114 END IF;
1115
1116 OPEN get_site_details;
1117 FETCH get_site_details BULK COLLECT INTO x_account_merge_v2_obj.acct_site_objs;
1118 CLOSE get_site_details;
1119
1120 OPEN get_site_use_details;
1121 FETCH get_site_use_details BULK COLLECT INTO x_account_merge_v2_obj.acct_site_uses_objs;
1122 CLOSE get_site_use_details;
1123
1124
1125 OPEN get_customer_profiles;
1126 FETCH get_customer_profiles BULK COLLECT INTO x_account_merge_v2_obj.customer_profile_objs;
1127 CLOSE get_customer_profiles;
1128
1129 FOR I IN 1..x_account_merge_v2_obj.customer_profile_objs.count LOOP
1130 l_site_use_id := null;
1131 SELECT site_use_id INTO l_site_use_id
1132 FROM hz_customer_profiles_m
1133 WHERE cust_account_profile_id = x_account_merge_v2_obj.customer_profile_objs(I).from_object_id;
1134
1135 IF l_site_use_id IS NOT NULL THEN
1136
1137 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1138 (p_init_msg_list => fnd_api.g_false,
1139 p_owner_table_id => x_account_merge_v2_obj.customer_profile_objs(I).from_parent_object_id,
1140 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL',
1141 p_action_type => NULL,
1142 x_orig_sys_ref_objs => x_account_merge_v2_obj.customer_profile_objs(I).from_parent_obj_sys_ref_objs,
1143 x_return_status => x_return_status,
1144 x_msg_count => x_msg_count,
1145 x_msg_data => x_msg_data);
1146
1147 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150
1151 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1152 (p_init_msg_list => fnd_api.g_false,
1153 p_owner_table_id => x_account_merge_v2_obj.customer_profile_objs(I).to_parent_object_id,
1154 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL',
1155 p_action_type => NULL,
1156 x_orig_sys_ref_objs => x_account_merge_v2_obj.customer_profile_objs(I).to_parent_obj_sys_ref_objs,
1157 x_return_status => x_return_status,
1158 x_msg_count => x_msg_count,
1159 x_msg_data => x_msg_data);
1160
1161 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1162 RAISE FND_API.G_EXC_ERROR;
1163 END IF;
1164
1165 ELSE
1166 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1167 (p_init_msg_list => fnd_api.g_false,
1168 p_owner_table_id => x_account_merge_v2_obj.customer_profile_objs(I).from_parent_object_id,
1169 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1170 p_action_type => NULL,
1171 x_orig_sys_ref_objs => x_account_merge_v2_obj.customer_profile_objs(I).from_parent_obj_sys_ref_objs,
1172 x_return_status => x_return_status,
1173 x_msg_count => x_msg_count,
1174 x_msg_data => x_msg_data);
1175
1176 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1177 RAISE FND_API.G_EXC_ERROR;
1178 END IF;
1179
1180 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1181 (p_init_msg_list => fnd_api.g_false,
1182 p_owner_table_id => x_account_merge_v2_obj.customer_profile_objs(I).to_parent_object_id,
1183 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1184 p_action_type => NULL,
1185 x_orig_sys_ref_objs => x_account_merge_v2_obj.customer_profile_objs(I).to_parent_obj_sys_ref_objs,
1186 x_return_status => x_return_status,
1190 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1187 x_msg_count => x_msg_count,
1188 x_msg_data => x_msg_data);
1189
1191 RAISE FND_API.G_EXC_ERROR;
1192 END IF;
1193
1194 END IF; --l_site_use_id
1195 END LOOP; --customer_profiles
1196
1197
1198 OPEN get_cust_profile_amts;
1199 FETCH get_cust_profile_amts BULK COLLECT INTO x_account_merge_v2_obj.cust_profile_amt_objs;
1200 CLOSE get_cust_profile_amts;
1201
1202 FOR I IN 1..x_account_merge_v2_obj.cust_profile_amt_objs.count LOOP
1203 l_site_use_id := null;
1204 SELECT site_use_id INTO l_site_use_id
1205 FROM hz_cust_profile_amts_m
1206 WHERE cust_acct_profile_amt_id = x_account_merge_v2_obj.cust_profile_amt_objs(I).from_object_id;
1207
1208 IF l_site_use_id IS NOT NULL THEN
1209
1210 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1211 (p_init_msg_list => fnd_api.g_false,
1212 p_owner_table_id => x_account_merge_v2_obj.cust_profile_amt_objs(I).from_parent_object_id,
1213 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL',
1214 p_action_type => NULL,
1215 x_orig_sys_ref_objs => x_account_merge_v2_obj.cust_profile_amt_objs(I).from_parent_obj_sys_ref_objs,
1216 x_return_status => x_return_status,
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data);
1219
1220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1221 RAISE FND_API.G_EXC_ERROR;
1222 END IF;
1223
1224 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1225 (p_init_msg_list => fnd_api.g_false,
1226 p_owner_table_id => x_account_merge_v2_obj.cust_profile_amt_objs(I).to_parent_object_id,
1227 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL',
1228 p_action_type => NULL,
1229 x_orig_sys_ref_objs => x_account_merge_v2_obj.cust_profile_amt_objs(I).to_parent_obj_sys_ref_objs,
1230 x_return_status => x_return_status,
1231 x_msg_count => x_msg_count,
1232 x_msg_data => x_msg_data);
1233
1234 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1235 RAISE FND_API.G_EXC_ERROR;
1236 END IF;
1237
1238 ELSE
1239 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1240 (p_init_msg_list => fnd_api.g_false,
1241 p_owner_table_id => x_account_merge_v2_obj.cust_profile_amt_objs(I).from_parent_object_id,
1242 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1243 p_action_type => NULL,
1244 x_orig_sys_ref_objs => x_account_merge_v2_obj.cust_profile_amt_objs(I).from_parent_obj_sys_ref_objs,
1245 x_return_status => x_return_status,
1246 x_msg_count => x_msg_count,
1247 x_msg_data => x_msg_data);
1248
1249 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1250 RAISE FND_API.G_EXC_ERROR;
1251 END IF;
1252
1253 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1254 (p_init_msg_list => fnd_api.g_false,
1255 p_owner_table_id => x_account_merge_v2_obj.cust_profile_amt_objs(I).to_parent_object_id,
1256 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1257 p_action_type => NULL,
1258 x_orig_sys_ref_objs => x_account_merge_v2_obj.cust_profile_amt_objs(I).to_parent_obj_sys_ref_objs,
1259 x_return_status => x_return_status,
1260 x_msg_count => x_msg_count,
1261 x_msg_data => x_msg_data);
1262
1263 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1264 RAISE FND_API.G_EXC_ERROR;
1265 END IF;
1266
1267 END IF; --l_site_use_id
1268 END LOOP; --customer_profile_amts
1269
1270 OPEN get_account_roles;
1271 FETCH get_account_roles BULK COLLECT INTO x_account_merge_v2_obj.acct_role_objs;
1272 CLOSE get_account_roles;
1273
1274 FOR I in 1..x_account_merge_v2_obj.acct_role_objs.count LOOP
1275
1276 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1277 (p_init_msg_list => fnd_api.g_false,
1278 p_owner_table_id => x_account_merge_v2_obj.acct_role_objs(I).from_object_id,
1279 p_owner_table_name => 'HZ_CUST_ACCOUNT_ROLES',
1280 p_action_type => NULL,
1281 x_orig_sys_ref_objs => x_account_merge_v2_obj.acct_role_objs(I).from_object_sys_ref_objs,
1282 x_return_status => x_return_status,
1283 x_msg_count => x_msg_count,
1284 x_msg_data => x_msg_data);
1285
1286 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1287 RAISE FND_API.G_EXC_ERROR;
1288 END IF;
1289
1290 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1291 (p_init_msg_list => fnd_api.g_false,
1292 p_owner_table_id => x_account_merge_v2_obj.acct_role_objs(I).to_object_id,
1293 p_owner_table_name => 'HZ_CUST_ACCOUNT_ROLES',
1294 p_action_type => NULL,
1295 x_orig_sys_ref_objs => x_account_merge_v2_obj.acct_role_objs(I).to_object_sys_ref_objs,
1296 x_return_status => x_return_status,
1297 x_msg_count => x_msg_count,
1298 x_msg_data => x_msg_data);
1299
1300 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1304 l_site_use_id := null;
1301 RAISE FND_API.G_EXC_ERROR;
1302 END IF;
1303
1305
1306 SELECT cust_acct_site_id INTO l_site_use_id
1307 FROM hz_cust_account_roles_m
1308 WHERE cust_account_role_id = x_account_merge_v2_obj.acct_role_objs(I).from_object_id;
1309
1310 IF l_site_use_id IS NOT NULL THEN
1311
1312 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1313 (p_init_msg_list => fnd_api.g_false,
1314 p_owner_table_id => x_account_merge_v2_obj.acct_role_objs(I).from_parent_object_id,
1315 p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
1316 p_action_type => NULL,
1317 x_orig_sys_ref_objs => x_account_merge_v2_obj.acct_role_objs(I).from_parent_obj_sys_ref_objs,
1318 x_return_status => x_return_status,
1319 x_msg_count => x_msg_count,
1320 x_msg_data => x_msg_data);
1321
1322 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1323 RAISE FND_API.G_EXC_ERROR;
1324 END IF;
1325
1326 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1327 (p_init_msg_list => fnd_api.g_false,
1328 p_owner_table_id => x_account_merge_v2_obj.acct_role_objs(I).to_parent_object_id,
1329 p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
1330 p_action_type => NULL,
1331 x_orig_sys_ref_objs => x_account_merge_v2_obj.acct_role_objs(I).to_parent_obj_sys_ref_objs,
1332 x_return_status => x_return_status,
1333 x_msg_count => x_msg_count,
1334 x_msg_data => x_msg_data);
1335
1336 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 END IF;
1339
1340 ELSE
1341 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1342 (p_init_msg_list => fnd_api.g_false,
1343 p_owner_table_id => x_account_merge_v2_obj.acct_role_objs(I).from_parent_object_id,
1344 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1345 p_action_type => NULL,
1346 x_orig_sys_ref_objs => x_account_merge_v2_obj.acct_role_objs(I).from_parent_obj_sys_ref_objs,
1347 x_return_status => x_return_status,
1348 x_msg_count => x_msg_count,
1349 x_msg_data => x_msg_data);
1350
1351 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354
1355 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1356 (p_init_msg_list => fnd_api.g_false,
1357 p_owner_table_id => x_account_merge_v2_obj.acct_role_objs(I).to_parent_object_id,
1358 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
1359 p_action_type => NULL,
1360 x_orig_sys_ref_objs => x_account_merge_v2_obj.acct_role_objs(I).to_parent_obj_sys_ref_objs,
1361 x_return_status => x_return_status,
1362 x_msg_count => x_msg_count,
1363 x_msg_data => x_msg_data);
1364
1365 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1366 RAISE FND_API.G_EXC_ERROR;
1367 END IF;
1368
1369 END IF; --l_site_use_id
1370 END LOOP; --cust_account_roles
1371
1372 OPEN get_acct_rels(x_account_merge_v2_obj.merge_from_account_objs(1).cust_acct_id,
1373 x_account_merge_v2_obj.merge_to_account_obj.cust_acct_id);
1374 FETCH get_acct_rels BULK COLLECT INTO x_account_merge_v2_obj.acct_rel_objs;
1375 CLOSE get_acct_rels;
1376
1377 -- Debug info.
1378 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1379 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
1380 p_prefix=>l_debug_prefix,
1381 p_msg_level=>fnd_log.level_procedure);
1382 END IF;
1383
1384 EXCEPTION
1385
1386 WHEN fnd_api.g_exc_error THEN
1387 x_return_status := fnd_api.g_ret_sts_error;
1388
1389 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1390 p_count => x_msg_count,
1391 p_data => x_msg_data);
1392
1393 -- Debug info.
1394 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1395 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1396 p_msg_data=>x_msg_data,
1397 p_msg_type=>'ERROR',
1398 p_msg_level=>fnd_log.level_error);
1399 END IF;
1400 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1401 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
1402 p_prefix=>l_debug_prefix,
1403 p_msg_level=>fnd_log.level_procedure);
1404 END IF;
1405 WHEN fnd_api.g_exc_unexpected_error THEN
1406 x_return_status := fnd_api.g_ret_sts_unexp_error;
1407
1408 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1409 p_count => x_msg_count,
1410 p_data => x_msg_data);
1411
1412 -- Debug info.
1413 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1414 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1415 p_msg_data=>x_msg_data,
1416 p_msg_type=>'UNEXPECTED ERROR',
1417 p_msg_level=>fnd_log.level_error);
1418 END IF;
1422 p_msg_level=>fnd_log.level_procedure);
1419 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1420 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
1421 p_prefix=>l_debug_prefix,
1423 END IF;
1424 WHEN OTHERS THEN
1425 x_return_status := fnd_api.g_ret_sts_unexp_error;
1426
1427 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1428 fnd_message.set_token('ERROR' ,SQLERRM);
1429 fnd_msg_pub.add;
1430
1431 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1432 p_count => x_msg_count,
1433 p_data => x_msg_data);
1434
1435 -- Debug info.
1436 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1437 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1438 p_msg_data=>x_msg_data,
1439 p_msg_type=>'SQL ERROR',
1440 p_msg_level=>fnd_log.level_error);
1441 END IF;
1442 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1443 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
1444 p_prefix=>l_debug_prefix,
1445 p_msg_level=>fnd_log.level_procedure);
1446 END IF;
1447
1448 END get_account_merge_event_data;
1449
1450 --------------------------------------
1451 --
1452 -- PROCEDURE get_party_merge_event_data
1453 --
1454 -- DESCRIPTION
1455 -- Get party merge details.
1456 --
1457 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1458 --
1459 -- ARGUMENTS
1460 -- IN: p_init_msg_list
1461 -- p_batch_id
1462 -- p_merge_to_party_id
1463 -- OUT:x_party_merge_obj
1464 -- x_return_status
1465 -- x_msg_count
1466 -- x_msg_data
1467 -- NOTES
1468 --
1469 -- MODIFICATION HISTORY
1470 --
1471 --
1472 -- 25-AUG-2005 S V Sowjanya Created.
1473
1474 PROCEDURE get_party_merge_event_data(
1475 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1476 p_batch_id IN NUMBER,
1477 p_merge_to_party_id IN NUMBER,
1478 p_get_merge_detail_flag IN VARCHAR2 := 'N', --5093366
1479 x_party_merge_obj OUT NOCOPY HZ_PARTY_MERGE_OBJ,
1480 x_return_status OUT NOCOPY VARCHAR2,
1481 x_msg_count OUT NOCOPY NUMBER,
1482 x_msg_data OUT NOCOPY VARCHAR2
1483 ) IS
1484
1485 l_debug_prefix VARCHAR2(30) := '';
1486 l_merge_to_party_id NUMBER;
1487 l_batch_id NUMBER;
1488 l_merge_status VARCHAR2(30);
1489 CURSOR party_merge_details IS
1490 SELECT HZ_PARTY_MERGE_OBJ(
1491 mb.batch_id,
1492 mb.batch_name,
1493 mp.merge_type,
1494 db.automerge_flag,
1495 mb.created_by,
1496 mb.creation_date,
1497 mb.last_update_login,
1498 mb.last_update_date,
1499 mb.last_updated_by,
1500 HZ_PARTY_ORIG_SYS_REF_OBJ(
1501 tp.party_id,
1502 tp.party_number,
1503 tp.party_name,
1504 tp.party_type,
1505 HZ_ORIG_SYS_REF_OBJ_TBL()),
1506 CAST(MULTISET(
1507 SELECT HZ_PARTY_ORIG_SYS_REF_OBJ(
1508 fp.party_id,
1509 fp.party_number,
1510 fp.party_name,
1511 fp.party_type,
1512 HZ_ORIG_SYS_REF_OBJ_TBL())
1513 FROM hz_parties fp, hz_merge_parties mp1
1514 WHERE mp1.batch_id = p_batch_id
1515 AND mp1.to_party_id = l_merge_to_party_id
1516 AND fp.party_id = mp1.from_party_id
1517 AND mp1.merge_type = mp.merge_type
1518 ) AS HZ_PARTY_ORIG_SYS_REF_OBJ_TBL),
1519 HZ_PARTY_MERGE_DETAIL_OBJ_TBL() --5093366
1520 )
1521 FROM hz_merge_batch mb,
1522 (SELECT DISTINCT merge_type from hz_merge_parties where batch_id = p_batch_id and to_party_id = l_merge_to_party_id) mp,
1523 hz_dup_batch db,
1524 hz_dup_sets dset,
1525 hz_parties tp
1526 WHERE mb.batch_id = p_batch_id
1527 AND tp.party_id = l_merge_to_party_id
1528 AND mb.batch_id = dset.dup_set_id (+)
1529 AND db.dup_batch_id (+)= dset.dup_batch_id
1530 ORDER BY mp.merge_type;
1531 --5093366
1532 CURSOR party_merge_details1 IS
1533 SELECT HZ_PARTY_MERGE_DETAIL_OBJ(
1534 get_object_type(md.entity_name, mph.from_entity_id),
1535 mph.operation_type,
1536 mph.from_entity_id,
1537 CAST(MULTISET(
1538 SELECT HZ_ORIG_SYS_REF_OBJ(
1539 NULL,
1540 ORIG_SYSTEM_REF_ID,
1541 ORIG_SYSTEM,
1542 ORIG_SYSTEM_REFERENCE,
1543 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1544 OWNER_TABLE_ID,
1545 STATUS,
1546 REASON_CODE,
1547 OLD_ORIG_SYSTEM_REFERENCE,
1548 START_DATE_ACTIVE,
1549 END_DATE_ACTIVE,
1550 PROGRAM_UPDATE_DATE,
1551 CREATED_BY_MODULE,
1552 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1553 CREATION_DATE,
1554 LAST_UPDATE_DATE,
1555 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1556 ATTRIBUTE_CATEGORY,
1557 ATTRIBUTE1,
1558 ATTRIBUTE2,
1559 ATTRIBUTE3,
1560 ATTRIBUTE4,
1561 ATTRIBUTE5,
1562 ATTRIBUTE6,
1563 ATTRIBUTE7,
1564 ATTRIBUTE8,
1565 ATTRIBUTE9,
1566 ATTRIBUTE10,
1567 ATTRIBUTE11,
1568 ATTRIBUTE12,
1569 ATTRIBUTE13,
1570 ATTRIBUTE14,
1571 ATTRIBUTE15,
1572 ATTRIBUTE16,
1573 ATTRIBUTE17,
1574 ATTRIBUTE18,
1575 ATTRIBUTE19,
1579 WHERE OWNER_TABLE_ID = mph.from_entity_id
1576 ATTRIBUTE20
1577 )
1578 FROM HZ_ORIG_SYS_REFERENCES
1580 AND OWNER_TABLE_NAME = md.entity_name
1581
1582 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
1583
1584 mph.to_entity_id,
1585
1586 CAST(MULTISET(
1587 SELECT HZ_ORIG_SYS_REF_OBJ(
1588 NULL,
1589 ORIG_SYSTEM_REF_ID,
1590 ORIG_SYSTEM,
1591 ORIG_SYSTEM_REFERENCE,
1592 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1593 OWNER_TABLE_ID,
1594 STATUS,
1595 REASON_CODE,
1596 OLD_ORIG_SYSTEM_REFERENCE,
1597 START_DATE_ACTIVE,
1598 END_DATE_ACTIVE,
1599 PROGRAM_UPDATE_DATE,
1600 CREATED_BY_MODULE,
1601 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1602 CREATION_DATE,
1603 LAST_UPDATE_DATE,
1604 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1605 ATTRIBUTE_CATEGORY,
1606 ATTRIBUTE1,
1607 ATTRIBUTE2,
1608 ATTRIBUTE3,
1609 ATTRIBUTE4,
1610 ATTRIBUTE5,
1611 ATTRIBUTE6,
1612 ATTRIBUTE7,
1613 ATTRIBUTE8,
1614 ATTRIBUTE9,
1615 ATTRIBUTE10,
1616 ATTRIBUTE11,
1617 ATTRIBUTE12,
1618 ATTRIBUTE13,
1619 ATTRIBUTE14,
1620 ATTRIBUTE15,
1621 ATTRIBUTE16,
1622 ATTRIBUTE17,
1623 ATTRIBUTE18,
1624 ATTRIBUTE19,
1625 ATTRIBUTE20
1626 )
1627 FROM HZ_ORIG_SYS_REFERENCES
1628 WHERE OWNER_TABLE_ID = decode(mph.operation_type,'Copy',mph.from_entity_id,mph.to_entity_id)
1629 AND OWNER_TABLE_NAME = md.entity_name
1630
1631 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
1632
1633 mph.from_parent_entity_id,
1634
1635 CAST(MULTISET(
1636 SELECT HZ_ORIG_SYS_REF_OBJ(
1637 NULL,
1638 ORIG_SYSTEM_REF_ID,
1639 ORIG_SYSTEM,
1640 ORIG_SYSTEM_REFERENCE,
1641 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1642 OWNER_TABLE_ID,
1643 STATUS,
1644 REASON_CODE,
1645 OLD_ORIG_SYSTEM_REFERENCE,
1646 START_DATE_ACTIVE,
1647 END_DATE_ACTIVE,
1648 PROGRAM_UPDATE_DATE,
1649 CREATED_BY_MODULE,
1650 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1651 CREATION_DATE,
1652 LAST_UPDATE_DATE,
1653 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1654 ATTRIBUTE_CATEGORY,
1655 ATTRIBUTE1,
1656 ATTRIBUTE2,
1657 ATTRIBUTE3,
1658 ATTRIBUTE4,
1659 ATTRIBUTE5,
1660 ATTRIBUTE6,
1661 ATTRIBUTE7,
1662 ATTRIBUTE8,
1663 ATTRIBUTE9,
1664 ATTRIBUTE10,
1665 ATTRIBUTE11,
1666 ATTRIBUTE12,
1667 ATTRIBUTE13,
1668 ATTRIBUTE14,
1669 ATTRIBUTE15,
1670 ATTRIBUTE16,
1671 ATTRIBUTE17,
1672 ATTRIBUTE18,
1673 ATTRIBUTE19,
1674 ATTRIBUTE20
1675 )
1676 FROM HZ_ORIG_SYS_REFERENCES
1677 WHERE OWNER_TABLE_ID = mph.from_parent_entity_id
1678 AND OWNER_TABLE_NAME = md.parent_entity_name
1679
1680 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
1681
1682 mph.to_parent_entity_id,
1683
1684
1685 CAST(MULTISET(
1686 SELECT HZ_ORIG_SYS_REF_OBJ(
1687 NULL,
1688 ORIG_SYSTEM_REF_ID,
1689 ORIG_SYSTEM,
1690 ORIG_SYSTEM_REFERENCE,
1691 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1692 OWNER_TABLE_ID,
1693 STATUS,
1694 REASON_CODE,
1695 OLD_ORIG_SYSTEM_REFERENCE,
1696 START_DATE_ACTIVE,
1697 END_DATE_ACTIVE,
1698 PROGRAM_UPDATE_DATE,
1699 CREATED_BY_MODULE,
1700 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1701 CREATION_DATE,
1702 LAST_UPDATE_DATE,
1703 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1704 ATTRIBUTE_CATEGORY,
1705 ATTRIBUTE1,
1706 ATTRIBUTE2,
1707 ATTRIBUTE3,
1708 ATTRIBUTE4,
1709 ATTRIBUTE5,
1710 ATTRIBUTE6,
1711 ATTRIBUTE7,
1712 ATTRIBUTE8,
1713 ATTRIBUTE9,
1714 ATTRIBUTE10,
1715 ATTRIBUTE11,
1716 ATTRIBUTE12,
1717 ATTRIBUTE13,
1718 ATTRIBUTE14,
1719 ATTRIBUTE15,
1720 ATTRIBUTE16,
1721 ATTRIBUTE17,
1722 ATTRIBUTE18,
1723 ATTRIBUTE19,
1724 ATTRIBUTE20
1725 )
1726 FROM HZ_ORIG_SYS_REFERENCES
1727 WHERE OWNER_TABLE_ID = mph.to_parent_entity_id
1728 AND OWNER_TABLE_NAME = md.parent_entity_name
1729
1730 )AS HZ_ORIG_SYS_REF_OBJ_TBL)
1731
1732 )
1733 FROM hz_merge_parties mp2,
1734 hz_merge_party_history mph,
1735 hz_merge_dictionary md
1736 WHERE mp2.batch_id = p_batch_id
1737 AND mp2.to_party_id = l_merge_to_party_id
1738 AND mph.batch_party_id = mp2.batch_party_id
1739 AND md.merge_dict_id = mph.merge_dict_id
1740 AND md.dict_application_id = 222
1741 and md.entity_name like 'HZ%';
1742
1743 BEGIN
1744 -- initialize API return status to success.
1745 x_return_status := FND_API.G_RET_STS_SUCCESS;
1746
1747 -- Initialize message list if p_init_msg_list is set to TRUE
1748 IF FND_API.to_Boolean(p_init_msg_list) THEN
1749 FND_MSG_PUB.initialize;
1750 END IF;
1751
1752 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1756 END IF;
1753 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(+)',
1754 p_prefix=>l_debug_prefix,
1755 p_msg_level=>fnd_log.level_procedure);
1757
1758 IF p_get_merge_detail_flag NOT IN ('Y','N') THEN
1759 FND_MESSAGE.SET_NAME('AR','HZ_API_VAL_DEP_FIELDS');
1760 FND_MESSAGE.SET_TOKEN('COLUMN1','getMergeDetailFlag');
1761 FND_MESSAGE.SET_TOKEN('VALUE1',p_get_merge_detail_flag);
1762 FND_MESSAGE.SET_TOKEN('COLUMN2','getMergeDetailFlag');
1763 FND_MESSAGE.SET_TOKEN('VALUE2','Y/N');
1764 FND_MSG_PUB.ADD();
1765 RAISE fnd_api.g_exc_error;
1766 END IF;
1767
1768 BEGIN
1769 SELECT batch_id, batch_status INTO l_batch_id, l_merge_status
1770 FROM hz_merge_batch
1771 WHERE batch_id = p_batch_id;
1772 EXCEPTION
1773 WHEN NO_DATA_FOUND THEN
1774 FND_MESSAGE.SET_NAME('AR','HZ_INVALID_DUP_BATCH');
1775 FND_MSG_PUB.ADD();
1776 RAISE fnd_api.g_exc_error;
1777 END;
1778 IF p_merge_to_party_id IS NOT NULL THEN
1779 BEGIN
1780 SELECT to_party_id INTO l_merge_to_party_id
1781 FROM hz_merge_parties
1782 WHERE batch_id = p_batch_id
1783 AND to_party_id = p_merge_to_party_id
1784 AND rownum = 1;
1785 EXCEPTION
1786 WHEN NO_DATA_FOUND THEN
1787 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_TCA_ID');
1788 FND_MSG_PUB.ADD();
1789 RAISE fnd_api.g_exc_error;
1790 END;
1791 ELSE
1792 SELECT to_party_id INTO l_merge_to_party_id
1793 FROM hz_merge_parties
1794 WHERE batch_id = p_batch_id
1795 AND NVL(merge_reason_code , 'DEDUPE') <> 'DUPLICATE_RELN_PARTY'
1796 AND rownum = 1;
1797 END IF;
1798
1799 IF l_merge_status <> 'COMPLETE' THEN
1800 FND_MESSAGE.SET_NAME('AR','HZ_CANNOT_SUBMIT_PROCESSING');
1801 FND_MSG_PUB.ADD();
1802 RAISE fnd_api.g_exc_error;
1803 END IF;
1804
1805 open party_merge_details;
1806 fetch party_merge_details into x_party_merge_obj;
1807 close party_merge_details;
1808 --5093366
1809 IF p_get_merge_detail_flag = 'Y' THEN
1810 open party_merge_details1;
1811 fetch party_merge_details1 BULK COLLECT into x_party_merge_obj.merge_detail_objs;
1812 close party_merge_details1;
1813 END IF;
1814
1815 -- SSM for party obj
1816 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1817 (p_init_msg_list => fnd_api.g_false,
1818 p_owner_table_id => x_party_merge_obj.merge_to_party_obj.party_id,
1819 p_owner_table_name => 'HZ_PARTIES',
1820 p_action_type => NULL,
1821 x_orig_sys_ref_objs => x_party_merge_obj.merge_to_party_obj.orig_sys_objs,
1822 x_return_status => x_return_status,
1823 x_msg_count => x_msg_count,
1824 x_msg_data => x_msg_data);
1825
1826 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1827 RAISE FND_API.G_EXC_ERROR;
1828 END IF;
1829
1830 FOR I in 1..x_party_merge_obj.merge_from_party_objs.count LOOP
1831
1832 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
1833 (p_init_msg_list => fnd_api.g_false,
1834 p_owner_table_id => x_party_merge_obj.merge_from_party_objs(I).party_id,
1835 p_owner_table_name => 'HZ_PARTIES',
1836 p_action_type => NULL,
1837 x_orig_sys_ref_objs => x_party_merge_obj.merge_from_party_objs(I).orig_sys_objs,
1838 x_return_status => x_return_status,
1839 x_msg_count => x_msg_count,
1840 x_msg_data => x_msg_data);
1841
1842 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1843 RAISE FND_API.G_EXC_ERROR;
1844 END IF;
1845 END LOOP;
1846
1847
1848 -- Debug info.
1849 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1850 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
1851 p_prefix=>l_debug_prefix,
1852 p_msg_level=>fnd_log.level_procedure);
1853 END IF;
1854 EXCEPTION
1855
1856 WHEN fnd_api.g_exc_error THEN
1857 x_return_status := fnd_api.g_ret_sts_error;
1858
1859 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1860 p_count => x_msg_count,
1861 p_data => x_msg_data);
1862
1863 -- Debug info.
1864 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1865 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1866 p_msg_data=>x_msg_data,
1867 p_msg_type=>'ERROR',
1868 p_msg_level=>fnd_log.level_error);
1869 END IF;
1870 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1871 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
1872 p_prefix=>l_debug_prefix,
1873 p_msg_level=>fnd_log.level_procedure);
1874 END IF;
1875 WHEN fnd_api.g_exc_unexpected_error THEN
1876 x_return_status := fnd_api.g_ret_sts_unexp_error;
1877
1878 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1879 p_count => x_msg_count,
1880 p_data => x_msg_data);
1881
1882 -- Debug info.
1883 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1884 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1885 p_msg_data=>x_msg_data,
1886 p_msg_type=>'UNEXPECTED ERROR',
1890 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
1887 p_msg_level=>fnd_log.level_error);
1888 END IF;
1889 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1891 p_prefix=>l_debug_prefix,
1892 p_msg_level=>fnd_log.level_procedure);
1893 END IF;
1894 WHEN OTHERS THEN
1895 x_return_status := fnd_api.g_ret_sts_unexp_error;
1896
1897 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1898 fnd_message.set_token('ERROR' ,SQLERRM);
1899 fnd_msg_pub.add;
1900
1901 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1902 p_count => x_msg_count,
1903 p_data => x_msg_data);
1904
1905 -- Debug info.
1906 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1907 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1908 p_msg_data=>x_msg_data,
1909 p_msg_type=>'SQL ERROR',
1910 p_msg_level=>fnd_log.level_error);
1911 END IF;
1912 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1913 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
1914 p_prefix=>l_debug_prefix,
1915 p_msg_level=>fnd_log.level_procedure);
1916 END IF;
1917 END get_party_merge_event_data;
1918
1919 END HZ_EXTRACT_MERGE_EVENT_PKG;