[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_MERGE_EVENT_PKG
Source
1 PACKAGE BODY HZ_EXTRACT_MERGE_EVENT_PKG AS
2 /*$Header: ARHMEVTB.pls 120.3 2006/03/23 18:02:48 awu noship $ */
3
4 --5093366
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 --------------------------------------
43 --
44 -- PROCEDURE get_account_merge_event_data
45 --
46 -- DESCRIPTION
47 -- Get account merge details.
48 --
49 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
50 --
51 -- ARGUMENTS
52 -- IN: p_init_msg_list
53 -- p_customer_merge_header_id
54 -- OUT:x_account_merge_obj
55 -- x_return_status
56 -- x_msg_count
57 -- x_msg_data
58 -- NOTES
59 --
60 -- MODIFICATION HISTORY
61 --
62 --
63 -- 25-AUG-2005 S V Sowjanya Created.
64
65
66 PROCEDURE get_account_merge_event_data(
67 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
68 p_customer_merge_header_id IN NUMBER,
69 x_account_merge_obj OUT NOCOPY HZ_ACCT_MERGE_OBJ,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2
73 ) IS
74 l_debug_prefix VARCHAR2(30) := '';
75 CURSOR account_merge_details IS
76
77 SELECT HZ_ACCT_MERGE_OBJ(
78 p_customer_merge_header_id,
79 mh.request_id,
80 mh.created_by,
81 mh.creation_date,
82 mh.last_update_login,
83 mh.last_update_date,
84 mh.last_updated_by,
85 HZ_PARTY_ORIG_SYS_REF_OBJ(
86 tp.party_id,
87 tp.party_number,
88 tp.party_name,
89 tp.party_type,
90 HZ_ORIG_SYS_REF_OBJ_TBL()),
91 HZ_PARTY_ORIG_SYS_REF_OBJ(
92 fp.party_id,
93 fp.party_number,
94 fp.party_name,
95 fp.party_type,
96 HZ_ORIG_SYS_REF_OBJ_TBL()),
97 HZ_ACCT_ORIG_SYS_REF_OBJ(
98 ta.cust_account_id,
99 ta.account_name,
100 ta.account_number,
101 HZ_ORIG_SYS_REF_OBJ_TBL()),
102 CAST(MULTISET (
103 SELECT HZ_ACCT_ORIG_SYS_REF_OBJ(
104 fa.cust_account_id,
105 fa.account_name,
106 fa.account_number,
107 HZ_ORIG_SYS_REF_OBJ_TBL())
108 FROM hz_cust_accounts fa
109 WHERE fa.cust_account_id = mh.duplicate_id
110 ) AS HZ_ACCT_ORIG_SYS_REF_OBJ_TBL)
111 )
112 FROM ra_customer_merge_headers mh, hz_cust_accounts ta, hz_parties tp, hz_cust_accounts fa, hz_parties fp
113 WHERE mh.customer_merge_header_id = p_customer_merge_header_id
114 AND ta.cust_account_id = mh.customer_id
115 AND tp.party_id = ta.party_id
116 AND fa.cust_account_id = mh.duplicate_id
117 AND fp.party_id = fa.party_id
118 AND rownum = 1;
119
120
121 BEGIN
122
123 -- initialize API return status to success.
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 -- Initialize message list if p_init_msg_list is set to TRUE
127 IF FND_API.to_Boolean(p_init_msg_list) THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130
131 -- Debug info.
132 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
133 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(+)',
134 p_prefix=>l_debug_prefix,
135 p_msg_level=>fnd_log.level_procedure);
136 END IF;
137
138 open account_merge_details;
139 fetch account_merge_details into x_account_merge_obj;
140 close account_merge_details;
141
142 -- SSM for merge-to acct party obj
143 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
144 (p_init_msg_list => fnd_api.g_false,
145 p_owner_table_id => x_account_merge_obj.merge_to_acct_party_obj.party_id,
146 p_owner_table_name => 'HZ_PARTIES',
147 p_action_type => NULL,
148 x_orig_sys_ref_objs => x_account_merge_obj.merge_to_acct_party_obj.orig_sys_objs,
149 x_return_status => x_return_status,
150 x_msg_count => x_msg_count,
151 x_msg_data => x_msg_data);
152
153 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
154 RAISE FND_API.G_EXC_ERROR;
155 END IF;
156
157 -- SSM for merge-from acct party obj
158 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
159 (p_init_msg_list => fnd_api.g_false,
160 p_owner_table_id => x_account_merge_obj.merge_from_acct_party_obj.party_id,
161 p_owner_table_name => 'HZ_PARTIES',
162 p_action_type => NULL,
163 x_orig_sys_ref_objs => x_account_merge_obj.merge_from_acct_party_obj.orig_sys_objs,
164 x_return_status => x_return_status,
165 x_msg_count => x_msg_count,
166 x_msg_data => x_msg_data);
167
168 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171 -- SSM for merge to account obj
172 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
173 (p_init_msg_list => fnd_api.g_false,
174 p_owner_table_id => x_account_merge_obj.merge_to_account_obj.cust_acct_id,
175 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
176 p_action_type => NULL,
177 x_orig_sys_ref_objs => x_account_merge_obj.merge_to_account_obj.orig_sys_objs,
178 x_return_status => x_return_status,
179 x_msg_count => x_msg_count,
180 x_msg_data => x_msg_data);
181
182 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 FOR I in 1..x_account_merge_obj.merge_from_account_objs.count LOOP
187
188 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
189 (p_init_msg_list => fnd_api.g_false,
190 p_owner_table_id => x_account_merge_obj.merge_from_account_objs(I).cust_acct_id,
191 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
192 p_action_type => NULL,
193 x_orig_sys_ref_objs => x_account_merge_obj.merge_from_account_objs(I).orig_sys_objs,
194 x_return_status => x_return_status,
195 x_msg_count => x_msg_count,
196 x_msg_data => x_msg_data);
197
198 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
199 RAISE FND_API.G_EXC_ERROR;
200 END IF;
201 END LOOP;
202
203 -- Debug info.
204 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
205 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
206 p_msg_data=>x_msg_data,
207 p_msg_type=>'WARNING',
208 p_msg_level=>fnd_log.level_exception);
209 END IF;
210
211 -- Debug info.
212 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
213 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
214 p_prefix=>l_debug_prefix,
215 p_msg_level=>fnd_log.level_procedure);
216 END IF;
217 EXCEPTION
218
219 WHEN fnd_api.g_exc_error THEN
220 x_return_status := fnd_api.g_ret_sts_error;
221
222 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
223 p_count => x_msg_count,
224 p_data => x_msg_data);
225
226 -- Debug info.
227 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
228 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
229 p_msg_data=>x_msg_data,
230 p_msg_type=>'ERROR',
231 p_msg_level=>fnd_log.level_error);
232 END IF;
233 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
234 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
235 p_prefix=>l_debug_prefix,
236 p_msg_level=>fnd_log.level_procedure);
237 END IF;
238 WHEN fnd_api.g_exc_unexpected_error THEN
239 x_return_status := fnd_api.g_ret_sts_unexp_error;
240
241 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
242 p_count => x_msg_count,
243 p_data => x_msg_data);
244
245 -- Debug info.
246 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
247 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
248 p_msg_data=>x_msg_data,
249 p_msg_type=>'UNEXPECTED ERROR',
250 p_msg_level=>fnd_log.level_error);
251 END IF;
252 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
253 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
254 p_prefix=>l_debug_prefix,
255 p_msg_level=>fnd_log.level_procedure);
256 END IF;
257 WHEN OTHERS THEN
258 x_return_status := fnd_api.g_ret_sts_unexp_error;
259
260 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
261 fnd_message.set_token('ERROR' ,SQLERRM);
262 fnd_msg_pub.add;
263
264 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
265 p_count => x_msg_count,
266 p_data => x_msg_data);
267
268 -- Debug info.
269 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
270 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
271 p_msg_data=>x_msg_data,
272 p_msg_type=>'SQL ERROR',
273 p_msg_level=>fnd_log.level_error);
274 END IF;
275 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
276 hz_utility_v2pub.debug(p_message=>'get_account_merge_event_data(-)',
277 p_prefix=>l_debug_prefix,
278 p_msg_level=>fnd_log.level_procedure);
279 END IF;
280 END get_account_merge_event_data;
281
282 --------------------------------------
283 --
284 -- PROCEDURE get_party_merge_event_data
285 --
286 -- DESCRIPTION
287 -- Get party merge details.
288 --
289 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
290 --
291 -- ARGUMENTS
292 -- IN: p_init_msg_list
293 -- p_batch_id
294 -- p_merge_to_party_id
295 -- OUT:x_party_merge_obj
296 -- x_return_status
297 -- x_msg_count
298 -- x_msg_data
299 -- NOTES
300 --
301 -- MODIFICATION HISTORY
302 --
303 --
304 -- 25-AUG-2005 S V Sowjanya Created.
305
306 PROCEDURE get_party_merge_event_data(
307 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
308 p_batch_id IN NUMBER,
309 p_merge_to_party_id IN NUMBER,
313 x_msg_count OUT NOCOPY NUMBER,
310 p_get_merge_detail_flag IN VARCHAR2 := 'N', --5093366
311 x_party_merge_obj OUT NOCOPY HZ_PARTY_MERGE_OBJ,
312 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_data OUT NOCOPY VARCHAR2
315 ) IS
316
317 l_debug_prefix VARCHAR2(30) := '';
318
319 CURSOR party_merge_details IS
320 SELECT HZ_PARTY_MERGE_OBJ(
321 mb.batch_id,
322 mb.batch_name,
323 mp.merge_type,
324 db.automerge_flag,
325 mb.created_by,
326 mb.creation_date,
327 mb.last_update_login,
328 mb.last_update_date,
329 mb.last_updated_by,
330 HZ_PARTY_ORIG_SYS_REF_OBJ(
331 tp.party_id,
332 tp.party_number,
333 tp.party_name,
334 tp.party_type,
335 HZ_ORIG_SYS_REF_OBJ_TBL()),
336 CAST(MULTISET(
337 SELECT HZ_PARTY_ORIG_SYS_REF_OBJ(
338 fp.party_id,
339 fp.party_number,
340 fp.party_name,
341 fp.party_type,
342 HZ_ORIG_SYS_REF_OBJ_TBL())
343 FROM hz_parties fp, hz_merge_parties mp1
344 WHERE mp1.batch_id = p_batch_id
345 AND mp1.to_party_id = p_merge_to_party_id
346 AND fp.party_id = mp1.from_party_id
347 ) AS HZ_PARTY_ORIG_SYS_REF_OBJ_TBL),
348 HZ_PARTY_MERGE_DETAIL_OBJ_TBL() --5093366
349 )
350 FROM hz_merge_batch mb,
351 (SELECT DISTINCT merge_type from hz_merge_parties where batch_id = p_batch_id and to_party_id = p_merge_to_party_id) mp,
352 hz_dup_batch db,
353 hz_dup_sets dset,
354 hz_parties tp
355 WHERE mb.batch_id = p_batch_id
356 AND tp.party_id = p_merge_to_party_id
357 AND mb.batch_id = dset.dup_set_id (+)
358 AND db.dup_batch_id (+)= dset.dup_batch_id
359 ORDER BY mp.merge_type;
360 --5093366
361 CURSOR party_merge_details1 IS
362 SELECT HZ_PARTY_MERGE_DETAIL_OBJ(
363 get_object_type(md.entity_name, mph.from_entity_id),
364 mph.operation_type,
365 mph.from_entity_id,
366 CAST(MULTISET(
367 SELECT HZ_ORIG_SYS_REF_OBJ(
368 NULL,
369 ORIG_SYSTEM_REF_ID,
370 ORIG_SYSTEM,
371 ORIG_SYSTEM_REFERENCE,
372 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
373 OWNER_TABLE_ID,
374 STATUS,
375 REASON_CODE,
376 OLD_ORIG_SYSTEM_REFERENCE,
377 START_DATE_ACTIVE,
378 END_DATE_ACTIVE,
379 PROGRAM_UPDATE_DATE,
380 CREATED_BY_MODULE,
381 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
382 CREATION_DATE,
383 LAST_UPDATE_DATE,
384 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
385 ATTRIBUTE_CATEGORY,
386 ATTRIBUTE1,
387 ATTRIBUTE2,
388 ATTRIBUTE3,
389 ATTRIBUTE4,
390 ATTRIBUTE5,
391 ATTRIBUTE6,
392 ATTRIBUTE7,
393 ATTRIBUTE8,
394 ATTRIBUTE9,
395 ATTRIBUTE10,
396 ATTRIBUTE11,
397 ATTRIBUTE12,
398 ATTRIBUTE13,
399 ATTRIBUTE14,
400 ATTRIBUTE15,
401 ATTRIBUTE16,
402 ATTRIBUTE17,
403 ATTRIBUTE18,
404 ATTRIBUTE19,
405 ATTRIBUTE20
406 )
407 FROM HZ_ORIG_SYS_REFERENCES
408 WHERE OWNER_TABLE_ID = mph.from_entity_id
409 AND OWNER_TABLE_NAME = md.entity_name
410
411 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
412
413 mph.to_entity_id,
414
415 CAST(MULTISET(
416 SELECT HZ_ORIG_SYS_REF_OBJ(
417 NULL,
418 ORIG_SYSTEM_REF_ID,
419 ORIG_SYSTEM,
420 ORIG_SYSTEM_REFERENCE,
421 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
422 OWNER_TABLE_ID,
423 STATUS,
424 REASON_CODE,
425 OLD_ORIG_SYSTEM_REFERENCE,
426 START_DATE_ACTIVE,
427 END_DATE_ACTIVE,
428 PROGRAM_UPDATE_DATE,
429 CREATED_BY_MODULE,
430 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
431 CREATION_DATE,
432 LAST_UPDATE_DATE,
433 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
434 ATTRIBUTE_CATEGORY,
435 ATTRIBUTE1,
436 ATTRIBUTE2,
437 ATTRIBUTE3,
438 ATTRIBUTE4,
439 ATTRIBUTE5,
440 ATTRIBUTE6,
441 ATTRIBUTE7,
442 ATTRIBUTE8,
443 ATTRIBUTE9,
444 ATTRIBUTE10,
445 ATTRIBUTE11,
446 ATTRIBUTE12,
447 ATTRIBUTE13,
448 ATTRIBUTE14,
449 ATTRIBUTE15,
450 ATTRIBUTE16,
451 ATTRIBUTE17,
452 ATTRIBUTE18,
453 ATTRIBUTE19,
454 ATTRIBUTE20
455 )
456 FROM HZ_ORIG_SYS_REFERENCES
457 WHERE OWNER_TABLE_ID = decode(mph.operation_type,'Copy',mph.from_entity_id,mph.to_entity_id)
458 AND OWNER_TABLE_NAME = md.entity_name
459
460 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
461
462 mph.from_parent_entity_id,
463
464 CAST(MULTISET(
465 SELECT HZ_ORIG_SYS_REF_OBJ(
466 NULL,
467 ORIG_SYSTEM_REF_ID,
468 ORIG_SYSTEM,
469 ORIG_SYSTEM_REFERENCE,
470 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
471 OWNER_TABLE_ID,
472 STATUS,
473 REASON_CODE,
474 OLD_ORIG_SYSTEM_REFERENCE,
475 START_DATE_ACTIVE,
476 END_DATE_ACTIVE,
477 PROGRAM_UPDATE_DATE,
478 CREATED_BY_MODULE,
479 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
480 CREATION_DATE,
484 ATTRIBUTE1,
481 LAST_UPDATE_DATE,
482 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
483 ATTRIBUTE_CATEGORY,
485 ATTRIBUTE2,
486 ATTRIBUTE3,
487 ATTRIBUTE4,
488 ATTRIBUTE5,
489 ATTRIBUTE6,
490 ATTRIBUTE7,
491 ATTRIBUTE8,
492 ATTRIBUTE9,
493 ATTRIBUTE10,
494 ATTRIBUTE11,
495 ATTRIBUTE12,
496 ATTRIBUTE13,
497 ATTRIBUTE14,
498 ATTRIBUTE15,
499 ATTRIBUTE16,
500 ATTRIBUTE17,
501 ATTRIBUTE18,
502 ATTRIBUTE19,
503 ATTRIBUTE20
504 )
505 FROM HZ_ORIG_SYS_REFERENCES
506 WHERE OWNER_TABLE_ID = mph.from_parent_entity_id
507 AND OWNER_TABLE_NAME = md.parent_entity_name
508
509 )AS HZ_ORIG_SYS_REF_OBJ_TBL),
510
511 mph.to_parent_entity_id,
512
513
514 CAST(MULTISET(
515 SELECT HZ_ORIG_SYS_REF_OBJ(
516 NULL,
517 ORIG_SYSTEM_REF_ID,
518 ORIG_SYSTEM,
519 ORIG_SYSTEM_REFERENCE,
520 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
521 OWNER_TABLE_ID,
522 STATUS,
523 REASON_CODE,
524 OLD_ORIG_SYSTEM_REFERENCE,
525 START_DATE_ACTIVE,
526 END_DATE_ACTIVE,
527 PROGRAM_UPDATE_DATE,
528 CREATED_BY_MODULE,
529 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
530 CREATION_DATE,
531 LAST_UPDATE_DATE,
532 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
533 ATTRIBUTE_CATEGORY,
534 ATTRIBUTE1,
535 ATTRIBUTE2,
536 ATTRIBUTE3,
537 ATTRIBUTE4,
538 ATTRIBUTE5,
539 ATTRIBUTE6,
540 ATTRIBUTE7,
541 ATTRIBUTE8,
542 ATTRIBUTE9,
543 ATTRIBUTE10,
544 ATTRIBUTE11,
545 ATTRIBUTE12,
546 ATTRIBUTE13,
547 ATTRIBUTE14,
548 ATTRIBUTE15,
549 ATTRIBUTE16,
550 ATTRIBUTE17,
551 ATTRIBUTE18,
552 ATTRIBUTE19,
553 ATTRIBUTE20
554 )
555 FROM HZ_ORIG_SYS_REFERENCES
556 WHERE OWNER_TABLE_ID = mph.to_parent_entity_id
557 AND OWNER_TABLE_NAME = md.parent_entity_name
558
559 )AS HZ_ORIG_SYS_REF_OBJ_TBL)
560
561 )
562 FROM hz_merge_parties mp2,
563 hz_merge_party_history mph,
564 hz_merge_dictionary md
565 WHERE mp2.batch_id = p_batch_id
566 AND mph.batch_party_id = mp2.batch_party_id
567 AND md.merge_dict_id = mph.merge_dict_id
568 AND md.dict_application_id = 222
569 and md.entity_name like 'HZ%';
570
571 BEGIN
572 -- initialize API return status to success.
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575 -- Initialize message list if p_init_msg_list is set to TRUE
576 IF FND_API.to_Boolean(p_init_msg_list) THEN
577 FND_MSG_PUB.initialize;
578 END IF;
579
580 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
581 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(+)',
582 p_prefix=>l_debug_prefix,
583 p_msg_level=>fnd_log.level_procedure);
584 END IF;
585
586 open party_merge_details;
587 fetch party_merge_details into x_party_merge_obj;
588 close party_merge_details;
589 --5093366
590 IF p_get_merge_detail_flag = 'Y' THEN
591 open party_merge_details1;
592 fetch party_merge_details1 BULK COLLECT into x_party_merge_obj.merge_detail_objs;
593 close party_merge_details1;
594 END IF;
595
596 -- SSM for party obj
597 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
598 (p_init_msg_list => fnd_api.g_false,
599 p_owner_table_id => x_party_merge_obj.merge_to_party_obj.party_id,
600 p_owner_table_name => 'HZ_PARTIES',
601 p_action_type => NULL,
602 x_orig_sys_ref_objs => x_party_merge_obj.merge_to_party_obj.orig_sys_objs,
603 x_return_status => x_return_status,
604 x_msg_count => x_msg_count,
605 x_msg_data => x_msg_data);
606
607 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
608 RAISE FND_API.G_EXC_ERROR;
609 END IF;
610
611 FOR I in 1..x_party_merge_obj.merge_from_party_objs.count LOOP
612
613 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
614 (p_init_msg_list => fnd_api.g_false,
615 p_owner_table_id => x_party_merge_obj.merge_from_party_objs(I).party_id,
616 p_owner_table_name => 'HZ_PARTIES',
617 p_action_type => NULL,
618 x_orig_sys_ref_objs => x_party_merge_obj.merge_from_party_objs(I).orig_sys_objs,
619 x_return_status => x_return_status,
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data);
622
623 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626 END LOOP;
627
628
629 -- Debug info.
630 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
631 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
632 p_prefix=>l_debug_prefix,
633 p_msg_level=>fnd_log.level_procedure);
634 END IF;
635 EXCEPTION
636
637 WHEN fnd_api.g_exc_error THEN
641 p_count => x_msg_count,
638 x_return_status := fnd_api.g_ret_sts_error;
639
640 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
642 p_data => x_msg_data);
643
644 -- Debug info.
645 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
646 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
647 p_msg_data=>x_msg_data,
648 p_msg_type=>'ERROR',
649 p_msg_level=>fnd_log.level_error);
650 END IF;
651 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
652 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
653 p_prefix=>l_debug_prefix,
654 p_msg_level=>fnd_log.level_procedure);
655 END IF;
656 WHEN fnd_api.g_exc_unexpected_error THEN
657 x_return_status := fnd_api.g_ret_sts_unexp_error;
658
659 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
660 p_count => x_msg_count,
661 p_data => x_msg_data);
662
663 -- Debug info.
664 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
665 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
666 p_msg_data=>x_msg_data,
667 p_msg_type=>'UNEXPECTED ERROR',
668 p_msg_level=>fnd_log.level_error);
669 END IF;
670 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
671 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
672 p_prefix=>l_debug_prefix,
673 p_msg_level=>fnd_log.level_procedure);
674 END IF;
675 WHEN OTHERS THEN
676 x_return_status := fnd_api.g_ret_sts_unexp_error;
677
678 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
679 fnd_message.set_token('ERROR' ,SQLERRM);
680 fnd_msg_pub.add;
681
682 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
683 p_count => x_msg_count,
684 p_data => x_msg_data);
685
686 -- Debug info.
687 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
688 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
689 p_msg_data=>x_msg_data,
690 p_msg_type=>'SQL ERROR',
691 p_msg_level=>fnd_log.level_error);
692 END IF;
693 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
694 hz_utility_v2pub.debug(p_message=>'get_party_merge_event_data(-)',
695 p_prefix=>l_debug_prefix,
696 p_msg_level=>fnd_log.level_procedure);
697 END IF;
698 END get_party_merge_event_data;
699
700 END HZ_EXTRACT_MERGE_EVENT_PKG;