1 PACKAGE BODY AMS_PARTY_MERGE_PVT AS
2 /* $Header: amsvprmb.pls 115.28 2004/04/09 04:19:37 julou ship $ */
3 -----------------------------------------------------------------------
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_PARTY_MERGE_PVT';
5
6 PROCEDURE REG_PARTY_MERGE
7 ( p_entity_name IN VARCHAR2
8 ,p_from_id IN NUMBER
9 ,p_to_id IN OUT NOCOPY NUMBER
10 ,p_from_fk_id IN NUMBER
11 ,p_to_fk_id IN NUMBER
12 ,p_parent_entity_name IN VARCHAR2
13 ,p_batch_id IN NUMBER
14 ,p_batch_party_id IN NUMBER
15 ,x_return_status IN OUT NOCOPY VARCHAR2
16 ) is
17 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
18 l_api_version_number CONSTANT NUMBER := 1.0;
19 l_merge_reason_code VARCHAR2(30);
20
21 BEGIN
22
23 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.REG_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
24
25 x_return_status := FND_API.G_RET_STS_SUCCESS;
26
27 select merge_reason_code into l_merge_reason_code
28 from HZ_MERGE_BATCH
29 where batch_id = p_batch_id;
30
31 IF l_merge_reason_code = 'DUPLICATE' THEN
32 -- ***************************************************************************
33 -- if reason code is duplicate then allow the party merge to happen without
34 -- any validations.
35 -- ***************************************************************************
36 null;
37 ELSE
38 -- ***************************************************************************
39 -- if there are any validations to be done, include it in this section
40 -- ***************************************************************************
41 null;
42 END IF;
43
44 -- ***************************************************************************
45 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
46 -- needs to be done. Set Merged To Id is same as Merged From Id and return
47 -- ***************************************************************************
48 if p_from_fk_id = p_to_fk_id then
49 p_to_id := p_from_id;
50 return;
51 end if;
52
53 -- ***************************************************************************
54 -- If the parent has changed(ie. Parent is getting merged) then transfer the
55 -- dependent record to the new parent. Before transferring check if a similar
56 -- dependent record exists on the new parent. If a duplicate exists then do
57 -- not transfer and return the id of the duplicate record as the Merged To Id
58 -- ***************************************************************************
59
60 -- ***************************************************************************
61 -- Add your own logic if you need to take care of the following cases
62 -- Check the if record duplicate if change party_id from merge-from
63 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
64 -- situation
65 --
66 -- customer_id address_id contact_id
67 -- =========== ========== ==========
68 -- 1200 1100
69 -- 1300 1400
70 --
71 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
72 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
73 -- therefore, if changing 1200 to 1300 (customer_id)
74 -- and 1100 to 1400 (address_id), then it will cause unique
75 -- key violation assume that all other fields are the same
76 -- So, please check if you need to check for record duplication
77 -- ***************************************************************************
78
79 IF p_from_fk_id <> p_to_fk_id THEN
80 BEGIN
81 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
82
83 UPDATE AMS_EVENT_REGISTRATIONS
84 set REGISTRANT_PARTY_ID = p_to_fk_id,
85 last_update_date = hz_utility_pub.last_update_date,
86 last_updated_by = hz_utility_pub.user_id,
87 last_update_login = hz_utility_pub.last_update_login,
88 program_application_id = hz_utility_pub.program_application_id,
89 program_id = hz_utility_pub.program_id,
90 program_update_date = sysdate
91 where REGISTRANT_PARTY_ID = p_from_fk_id;
92
93 -- following part added by soagrawa on 17-jan-2003
94 -- for bug# 2696534. Also refer to bug# 1539211
95
96 UPDATE AMS_EVENT_REGISTRATIONS
97 set REGISTRANT_CONTACT_ID = p_to_fk_id,
98 last_update_date = hz_utility_pub.last_update_date,
99 last_updated_by = hz_utility_pub.user_id,
100 last_update_login = hz_utility_pub.last_update_login,
101 program_application_id = hz_utility_pub.program_application_id,
102 program_id = hz_utility_pub.program_id,
103 program_update_date = sysdate
104 where REGISTRANT_CONTACT_ID = p_from_fk_id;
105
106 -- following part removed by soagrawa on 17-jan-2003
107 -- for bug# 2696534. Also refer to bug# 1539211
108
109 /*
110 ELSIF p_parent_entity_name = 'HZ_ORG_CONTACTS' THEN -- merge org_contact
111 UPDATE AMS_EVENT_REGISTRATIONS
112 set REGISTRANT_CONTACT_ID = p_to_fk_id,
113 last_update_date = hz_utility_pub.last_update_date,
114 last_updated_by = hz_utility_pub.user_id,
115 last_update_login = hz_utility_pub.last_update_login,
116 program_application_id = hz_utility_pub.program_application_id,
117 program_id = hz_utility_pub.program_id,
118 program_update_date = sysdate
119 where REGISTRANT_CONTACT_ID = p_from_fk_id;
120 */
121 END IF;
122 EXCEPTION
123 WHEN OTHERS THEN
124 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 raise;
127 END;
128 END IF;
129
130 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.REG_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
131
132 END REG_PARTY_MERGE;
133
134 PROCEDURE ATN_PARTY_MERGE
135 ( p_entity_name IN VARCHAR2
136 ,p_from_id IN NUMBER
137 ,p_to_id IN OUT NOCOPY NUMBER
138 ,p_from_fk_id IN NUMBER
139 ,p_to_fk_id IN NUMBER
140 ,p_parent_entity_name IN VARCHAR2
141 ,p_batch_id IN NUMBER
142 ,p_batch_party_id IN NUMBER
143 ,x_return_status IN OUT NOCOPY VARCHAR2
144 ) is
145 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
146 l_api_version_number CONSTANT NUMBER := 1.0;
147 l_merge_reason_code VARCHAR2(30);
148
149 BEGIN
150
151 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.ATN_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
152
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 select merge_reason_code into l_merge_reason_code
156 from HZ_MERGE_BATCH
157 where batch_id = p_batch_id;
158
159 IF l_merge_reason_code = 'DUPLICATE' THEN
160 -- ***************************************************************************
161 -- if reason code is duplicate then allow the party merge to happen without
162 -- any validations.
163 -- ***************************************************************************
164 null;
165 ELSE
166 -- ***************************************************************************
167 -- if there are any validations to be done, include it in this section
168 -- ***************************************************************************
169 null;
170 END IF;
171
172 -- ***************************************************************************
173 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
174 -- needs to be done. Set Merged To Id is same as Merged From Id and return
175 -- ***************************************************************************
176 if p_from_fk_id = p_to_fk_id then
177 p_to_id := p_from_id;
178 return;
179 end if;
180
181 -- ***************************************************************************
182 -- If the parent has changed(ie. Parent is getting merged) then transfer the
183 -- dependent record to the new parent. Before transferring check if a similar
184 -- dependent record exists on the new parent. If a duplicate exists then do
185 -- not transfer and return the id of the duplicate record as the Merged To Id
186 -- ***************************************************************************
187
188 -- ***************************************************************************
189 -- Add your own logic if you need to take care of the following cases
190 -- Check the if record duplicate if change party_id from merge-from
191 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
192 -- situation
193 --
194 -- customer_id address_id contact_id
195 -- =========== ========== ==========
196 -- 1200 1100
197 -- 1300 1400
198 --
199 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
200 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
201 -- therefore, if changing 1200 to 1300 (customer_id)
202 -- and 1100 to 1400 (address_id), then it will cause unique
203 -- key violation assume that all other fields are the same
204 -- So, please check if you need to check for record duplication
205 -- ***************************************************************************
206
207 IF p_from_fk_id <> p_to_fk_id THEN
208 BEGIN
209 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
210 UPDATE AMS_EVENT_REGISTRATIONS
211 set ATTENDANT_PARTY_ID = p_to_fk_id,
212 last_update_date = hz_utility_pub.last_update_date,
213 last_updated_by = hz_utility_pub.user_id,
214 last_update_login = hz_utility_pub.last_update_login,
215 program_application_id = hz_utility_pub.program_application_id,
216 program_id = hz_utility_pub.program_id,
217 program_update_date = sysdate
218 where ATTENDANT_PARTY_ID = p_from_fk_id;
219
220 -- following part added by soagrawa on 17-jan-2003
221 -- for bug# 2696534. Also refer to bug# 1539211
222
223 UPDATE AMS_EVENT_REGISTRATIONS
224 set ATTENDANT_CONTACT_ID = p_to_fk_id,
225 last_update_date = hz_utility_pub.last_update_date,
226 last_updated_by = hz_utility_pub.user_id,
227 last_update_login = hz_utility_pub.last_update_login,
228 program_application_id = hz_utility_pub.program_application_id,
229 program_id = hz_utility_pub.program_id,
230 program_update_date = sysdate
231 where ATTENDANT_CONTACT_ID = p_from_fk_id;
232
233
234 -- following part removed by soagrawa on 17-jan-2003
235 -- for bug# 2696534. Also refer to bug# 1539211
236
237 /* ELSIF p_parent_entity_name = 'HZ_ORG_CONTACTS' THEN -- merge org_contact
238 UPDATE AMS_EVENT_REGISTRATIONS
239 set ATTENDANT_CONTACT_ID = p_to_fk_id,
240 last_update_date = hz_utility_pub.last_update_date,
241 last_updated_by = hz_utility_pub.user_id,
242 last_update_login = hz_utility_pub.last_update_login,
243 program_application_id = hz_utility_pub.program_application_id,
244 program_id = hz_utility_pub.program_id,
245 program_update_date = sysdate
246 where ATTENDANT_CONTACT_ID = p_from_fk_id;
247 */
248 END IF;
249 EXCEPTION
250 WHEN OTHERS THEN
251 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 raise;
254 END;
255 END IF;
256
257 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.ATN_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
258
259 END ATN_PARTY_MERGE;
260
261 -----------------------------------------------------------------------
262 -- PROCEDURE
263 -- Channel_Party_Merge
264 --
265 -- HISTORY
266 -- 07/15/2000 ptendulk Created.
267 -----------------------------------------------------------------------
268 PROCEDURE Channel_Party_Merge
269 ( p_entity_name IN VARCHAR2
270 ,p_from_id IN NUMBER
271 ,p_to_id IN OUT NOCOPY NUMBER
272 ,p_from_fk_id IN NUMBER
273 ,p_to_fk_id IN NUMBER
274 ,p_parent_entity_name IN VARCHAR2
275 ,p_batch_id IN NUMBER
276 ,p_batch_party_id IN NUMBER
277 ,x_return_status IN OUT NOCOPY VARCHAR2
278 ) is
279 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
280 l_api_version_number CONSTANT NUMBER := 1.0;
281 l_merge_reason_code VARCHAR2(30);
282
283 BEGIN
284
285 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
286
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 select merge_reason_code into l_merge_reason_code
290 from HZ_MERGE_BATCH
291 where batch_id = p_batch_id;
292
293 IF l_merge_reason_code = 'DUPLICATE' THEN
294 -- ***************************************************************************
295 -- if reason code is duplicate then allow the party merge to happen without
296 -- any validations.
297 -- ***************************************************************************
298 null;
299 ELSE
300 -- ***************************************************************************
301 -- if there are any validations to be done, include it in this section
302 -- ***************************************************************************
303 null;
304 END IF;
305
306 -- ***************************************************************************
307 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
308 -- needs to be done. Set Merged To Id is same as Merged From Id and return
309 -- ***************************************************************************
310 if p_from_fk_id = p_to_fk_id then
311 p_to_id := p_from_id;
312 return;
313 end if;
314
315 -- ***************************************************************************
316 -- If the parent has changed(ie. Parent is getting merged) then transfer the
317 -- dependent record to the new parent. Before transferring check if a similar
318 -- dependent record exists on the new parent. If a duplicate exists then do
319 -- not transfer and return the id of the duplicate record as the Merged To Id
320 -- ***************************************************************************
321
322 -- ***************************************************************************
323 -- Add your own logic if you need to take care of the following cases
324 -- Check the if record duplicate if change party_id from merge-from
325 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
326 -- situation
327 --
328 -- customer_id address_id contact_id
329 -- =========== ========== ==========
330 -- 1200 1100
331 -- 1300 1400
332 --
333 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
334 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
335 -- therefore, if changing 1200 to 1300 (customer_id)
336 -- and 1100 to 1400 (address_id), then it will cause unique
337 -- key violation assume that all other fields are the same
338 -- So, please check if you need to check for record duplication
339 -- ***************************************************************************
340
341 IF p_from_fk_id <> p_to_fk_id THEN
342 BEGIN
343 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
344 -- Following lines of code is added by ptendulk on 14-May-2001
345 -- to do the party merge for table ams_channels_b
346 UPDATE AMS_CHANNELS_B
347 SET party_id = p_to_fk_id,
348 last_update_date = hz_utility_pub.last_update_date,
352 program_id = hz_utility_pub.program_id,
349 last_updated_by = hz_utility_pub.user_id,
350 last_update_login = hz_utility_pub.last_update_login,
351 program_application_id = hz_utility_pub.program_application_id,
353 program_update_date = sysdate
354 where party_id = p_from_fk_id;
355
356 END IF;
357 EXCEPTION
358 WHEN OTHERS THEN
359 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 raise;
362 END;
363 END IF;
364
365 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
366
367 END Channel_Party_Merge;
368
369 -----------------------------------------------------------------------
370 -- PROCEDURE
371 -- Party_src_Party_Merge
372 --
373 -- HISTORY
374 -- 07/15/2000 USingh Created.
375 -----------------------------------------------------------------------
376 PROCEDURE Party_src_Party_Merge
377 ( p_entity_name IN VARCHAR2
378 ,p_from_id IN NUMBER
379 ,p_to_id IN OUT NOCOPY NUMBER
380 ,p_from_fk_id IN NUMBER
381 ,p_to_fk_id IN NUMBER
382 ,p_parent_entity_name IN VARCHAR2
383 ,p_batch_id IN NUMBER
384 ,p_batch_party_id IN NUMBER
385 ,x_return_status IN OUT NOCOPY VARCHAR2
386 ) is
387 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
388 l_api_version_number CONSTANT NUMBER := 1.0;
389 l_merge_reason_code VARCHAR2(30);
390
391 BEGIN
392
393 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
394
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 select merge_reason_code into l_merge_reason_code
398 from HZ_MERGE_BATCH
399 where batch_id = p_batch_id;
400
401 IF l_merge_reason_code = 'DUPLICATE' THEN
402 -- ***************************************************************************
403 -- if reason code is duplicate then allow the party merge to happen without
404 -- any validations.
405 -- ***************************************************************************
406 null;
407 ELSE
408 -- ***************************************************************************
409 -- if there are any validations to be done, include it in this section
410 -- ***************************************************************************
411 null;
412 END IF;
413
414 -- ***************************************************************************
415 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
416 -- needs to be done. Set Merged To Id is same as Merged From Id and return
417 -- ***************************************************************************
418 if p_from_fk_id = p_to_fk_id then
419 p_to_id := p_from_id;
420 return;
421 end if;
422
423 -- ***************************************************************************
424 -- If the parent has changed(ie. Parent is getting merged) then transfer the
425 -- dependent record to the new parent. Before transferring check if a similar
426 -- dependent record exists on the new parent. If a duplicate exists then do
427 -- not transfer and return the id of the duplicate record as the Merged To Id
428 -- ***************************************************************************
429
430 -- ***************************************************************************
431 -- Add your own logic if you need to take care of the following cases
432 -- Check the if record duplicate if change party_id from merge-from
433 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
434 -- situation
435 --
436 -- customer_id address_id contact_id
437 -- =========== ========== ==========
438 -- 1200 1100
439 -- 1300 1400
440 --
441 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
442 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
443 -- therefore, if changing 1200 to 1300 (customer_id)
444 -- and 1100 to 1400 (address_id), then it will cause unique
445 -- key violation assume that all other fields are the same
446 -- So, please check if you need to check for record duplication
447 -- ***************************************************************************
448
449 IF p_from_fk_id <> p_to_fk_id THEN
450 BEGIN
451 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
452 -- Following lines of code is added by ptendulk on 14-May-2001
453 -- to do the party merge for table ams_channels_b
454 UPDATE AMS_PARTY_SOURCES
455 SET party_id = p_to_fk_id,
456 last_update_date = hz_utility_pub.last_update_date,
457 last_updated_by = hz_utility_pub.user_id,
458 last_update_login = hz_utility_pub.last_update_login,
459 program_application_id = hz_utility_pub.program_application_id,
460 program_id = hz_utility_pub.program_id,
461 program_update_date = sysdate
462 where party_id = p_from_fk_id;
463
467 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
464 END IF;
465 EXCEPTION
466 WHEN OTHERS THEN
468 x_return_status := FND_API.G_RET_STS_ERROR;
469 raise;
470 END;
471 END IF;
472
473 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
474
475 END Party_src_Party_Merge;
476
477 -----------------------------------------------------------------------
478 -- PROCEDURE
479 -- Segment_Party_Merge
480 --
481 -- HISTORY
482 -- 05/15/2001 yxliu Created.
483 -----------------------------------------------------------------------
484 PROCEDURE Segment_Party_Merge
485 ( p_entity_name IN VARCHAR2
486 ,p_from_id IN NUMBER
487 ,p_to_id IN OUT NOCOPY NUMBER
488 ,p_from_fk_id IN NUMBER
489 ,p_to_fk_id IN NUMBER
490 ,p_parent_entity_name IN VARCHAR2
491 ,p_batch_id IN NUMBER
492 ,p_batch_party_id IN NUMBER
493 ,x_return_status IN OUT NOCOPY VARCHAR2
494 ) is
495 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
496 l_api_version_number CONSTANT NUMBER := 1.0;
497 l_merge_reason_code VARCHAR2(30);
498
499 BEGIN
500
501 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.SEGMENT_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
502
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504
505 select merge_reason_code into l_merge_reason_code
506 from HZ_MERGE_BATCH
507 where batch_id = p_batch_id;
508
509 IF l_merge_reason_code = 'DUPLICATE' THEN
510 -- ***************************************************************************
511 -- if reason code is duplicate then allow the party merge to happen without
512 -- any validations.
513 -- ***************************************************************************
514 null;
515 ELSE
516 -- ***************************************************************************
517 -- if there are any validations to be done, include it in this section
518 -- ***************************************************************************
519 null;
520 END IF;
521
522 -- ***************************************************************************
523 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
524 -- needs to be done. Set Merged To Id is same as Merged From Id and return
525 -- ***************************************************************************
526 if p_from_fk_id = p_to_fk_id then
527 p_to_id := p_from_id;
528 return;
529 end if;
530
531 -- ***************************************************************************
532 -- If the parent has changed(ie. Parent is getting merged) then transfer the
533 -- dependent record to the new parent. Before transferring check if a similar
534 -- dependent record exists on the new parent. If a duplicate exists then do
535 -- not transfer and return the id of the duplicate record as the Merged To Id
536 -- ***************************************************************************
537
538 -- ***************************************************************************
539 -- Add your own logic if you need to take care of the following cases
540 -- Check the if record duplicate if change party_id from merge-from
541 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
542 -- situation
543 --
544 -- customer_id address_id contact_id
545 -- =========== ========== ==========
546 -- 1200 1100
547 -- 1300 1400
548 --
549 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
550 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
551 -- therefore, if changing 1200 to 1300 (customer_id)
552 -- and 1100 to 1400 (address_id), then it will cause unique
553 -- key violation assume that all other fields are the same
554 -- So, please check if you need to check for record duplication
555 -- ***************************************************************************
556
557 IF p_from_fk_id <> p_to_fk_id THEN
558 BEGIN
559 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
560 -- Following lines of code is added by yxliu on 15-May-2001
561 -- to do the party merge for table ams_party_market_segments
562 UPDATE AMS_PARTY_MARKET_SEGMENTS
563 SET party_id = p_to_fk_id,
564 last_update_date = hz_utility_pub.last_update_date,
565 last_updated_by = hz_utility_pub.user_id,
566 last_update_login = hz_utility_pub.last_update_login,
567 program_application_id = hz_utility_pub.program_application_id,
568 program_id = hz_utility_pub.program_id,
569 program_update_date = sysdate
570 where party_id = p_from_fk_id;
571
572 END IF;
573 EXCEPTION
574 WHEN OTHERS THEN
575 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 raise;
578 END;
579 END IF;
580
584
581 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.SEGMENT_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
582
583 END Segment_Party_Merge;
585 -----------------------------------------------------------------------
586 -- PROCEDURE
587 -- Post_Cust_Party_Merge
588 --
589 -- HISTORY
590 -- 05/21/2001 ryedator Created.
591 -----------------------------------------------------------------------
592 PROCEDURE Post_Cust_Party_Merge
593 ( p_entity_name IN VARCHAR2
594 ,p_from_id IN NUMBER
595 ,p_to_id IN OUT NOCOPY NUMBER
596 ,p_from_fk_id IN NUMBER
597 ,p_to_fk_id IN NUMBER
598 ,p_parent_entity_name IN VARCHAR2
599 ,p_batch_id IN NUMBER
600 ,p_batch_party_id IN NUMBER
601 ,x_return_status IN OUT NOCOPY VARCHAR2
602 ) is
603 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
604 l_api_version_number CONSTANT NUMBER := 1.0;
605 l_merge_reason_code VARCHAR2(30);
606
607 BEGIN
608
609 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.POST_CUST_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
610
611 x_return_status := FND_API.G_RET_STS_SUCCESS;
612
613 select merge_reason_code into l_merge_reason_code
614 from HZ_MERGE_BATCH
615 where batch_id = p_batch_id;
616
617 IF l_merge_reason_code = 'DUPLICATE' THEN
618 -- ***************************************************************************
619 -- if reason code is duplicate then allow the party merge to happen without
620 -- any validations.
621 -- ***************************************************************************
622 null;
623 ELSE
624 -- ***************************************************************************
625 -- if there are any validations to be done, include it in this section
626 -- ***************************************************************************
627 null;
628 END IF;
629
630 -- ***************************************************************************
631 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
632 -- needs to be done. Set Merged To Id is same as Merged From Id and return
633 -- ***************************************************************************
634 if p_from_fk_id = p_to_fk_id then
635 p_to_id := p_from_id;
636 return;
637 end if;
638
639 -- ***************************************************************************
640 -- If the parent has changed(ie. Parent is getting merged) then transfer the
641 -- dependent record to the new parent. Before transferring check if a similar
642 -- dependent record exists on the new parent. If a duplicate exists then do
643 -- not transfer and return the id of the duplicate record as the Merged To Id
644 -- ***************************************************************************
645
646 -- ***************************************************************************
647 -- Add your own logic if you need to take care of the following cases
648 -- Check the if record duplicate if change party_id from merge-from
649 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
650 -- situation
651 --
652 -- customer_id address_id contact_id
653 -- =========== ========== ==========
654 -- 1200 1100
655 -- 1300 1400
656 --
657 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
658 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
659 -- therefore, if changing 1200 to 1300 (customer_id)
660 -- and 1100 to 1400 (address_id), then it will cause unique
661 -- key violation assume that all other fields are the same
662 -- So, please check if you need to check for record duplication
663 -- ***************************************************************************
664
665 IF p_from_fk_id <> p_to_fk_id THEN
666 BEGIN
667 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
668 -- Following lines of code is added by ptendulk on 14-May-2001
669 -- to do the party merge for table ams_iba_postings_b
670 UPDATE AMS_IBA_POSTINGS_B
671 SET customer_party_id = p_to_fk_id,
672 last_update_date = hz_utility_pub.last_update_date,
673 last_updated_by = hz_utility_pub.user_id,
674 last_update_login = hz_utility_pub.last_update_login
675 --, program_application_id = hz_utility_pub.program_application_id,
676 -- program_id = hz_utility_pub.program_id,
677 -- program_update_date = sysdate
678 where customer_party_id = p_from_fk_id;
679
680 END IF;
681 EXCEPTION
682 WHEN OTHERS THEN
683 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 raise;
686 END;
687 END IF;
688
689 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.POST_CUST_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
690
691 END Post_Cust_Party_Merge;
692
693 -----------------------------------------------------------------------
694 -- PROCEDURE
695 -- Post_Affl_Party_Merge
696 --
697 -- HISTORY
698 -- 05/22/2000 ryedator Created.
702 ,p_from_id IN NUMBER
699 -----------------------------------------------------------------------
700 PROCEDURE Post_Affl_Party_Merge
701 ( p_entity_name IN VARCHAR2
703 ,p_to_id IN OUT NOCOPY NUMBER
704 ,p_from_fk_id IN NUMBER
705 ,p_to_fk_id IN NUMBER
706 ,p_parent_entity_name IN VARCHAR2
707 ,p_batch_id IN NUMBER
708 ,p_batch_party_id IN NUMBER
709 ,x_return_status IN OUT NOCOPY VARCHAR2
710 ) is
711 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
712 l_api_version_number CONSTANT NUMBER := 1.0;
713 l_merge_reason_code VARCHAR2(30);
714
715 BEGIN
716
717 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.POST_AFFL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
718
719 x_return_status := FND_API.G_RET_STS_SUCCESS;
720
721 select merge_reason_code into l_merge_reason_code
722 from HZ_MERGE_BATCH
723 where batch_id = p_batch_id;
724
725 IF l_merge_reason_code = 'DUPLICATE' THEN
726 -- ***************************************************************************
727 -- if reason code is duplicate then allow the party merge to happen without
728 -- any validations.
729 -- ***************************************************************************
730 null;
731 ELSE
732 -- ***************************************************************************
733 -- if there are any validations to be done, include it in this section
734 -- ***************************************************************************
735 null;
736 END IF;
737
738 -- ***************************************************************************
739 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
740 -- needs to be done. Set Merged To Id is same as Merged From Id and return
741 -- ***************************************************************************
742 if p_from_fk_id = p_to_fk_id then
743 p_to_id := p_from_id;
744 return;
745 end if;
746
747 -- ***************************************************************************
748 -- If the parent has changed(ie. Parent is getting merged) then transfer the
749 -- dependent record to the new parent. Before transferring check if a similar
750 -- dependent record exists on the new parent. If a duplicate exists then do
751 -- not transfer and return the id of the duplicate record as the Merged To Id
752 -- ***************************************************************************
753
754 -- ***************************************************************************
755 -- Add your own logic if you need to take care of the following cases
756 -- Check the if record duplicate if change party_id from merge-from
757 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
758 -- situation
759 --
760 -- customer_id address_id contact_id
761 -- =========== ========== ==========
762 -- 1200 1100
763 -- 1300 1400
764 --
765 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
766 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
767 -- therefore, if changing 1200 to 1300 (customer_id)
768 -- and 1100 to 1400 (address_id), then it will cause unique
769 -- key violation assume that all other fields are the same
770 -- So, please check if you need to check for record duplication
771 -- ***************************************************************************
772
773 IF p_from_fk_id <> p_to_fk_id THEN
774 BEGIN
775 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
776 -- Following lines of code is added by ptendulk on 14-May-2001
777 -- to do the party merge for table ams_iba_postings_b
778 UPDATE AMS_IBA_POSTINGS_B
779 SET affiliate_party_id = p_to_fk_id,
780 last_update_date = hz_utility_pub.last_update_date,
781 last_updated_by = hz_utility_pub.user_id,
782 last_update_login = hz_utility_pub.last_update_login
783 -- ,program_application_id = hz_utility_pub.program_application_id,
784 -- program_id = hz_utility_pub.program_id,
785 -- program_update_date = sysdate
786 where affiliate_party_id = p_from_fk_id;
787
788 END IF;
789 EXCEPTION
790 WHEN OTHERS THEN
791 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 raise;
794 END;
795 END IF;
796
797 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.POST_AFFL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
798
799 END Post_Affl_Party_Merge;
800
801
802 -----------------------------------------------------------------------
803 -- PROCEDURE
804 -- Campaign_Partner_Merge
805 --
806 -- HISTORY
807 -- 07/30/2001 mgudivak Created.
808 -----------------------------------------------------------------------
809 PROCEDURE Campaign_Partner_Merge
810 ( p_entity_name IN VARCHAR2
814 ,p_to_fk_id IN NUMBER
811 ,p_from_id IN NUMBER
812 ,p_to_id IN OUT NOCOPY NUMBER
813 ,p_from_fk_id IN NUMBER
815 ,p_parent_entity_name IN VARCHAR2
816 ,p_batch_id IN NUMBER
817 ,p_batch_party_id IN NUMBER
818 ,x_return_status IN OUT NOCOPY VARCHAR2
819 ) is
820 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
821 l_api_version_number CONSTANT NUMBER := 1.0;
822 l_merge_reason_code VARCHAR2(30);
823
824 BEGIN
825
826 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CAMPAIGN_PARTNER_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
827
828 x_return_status := FND_API.G_RET_STS_SUCCESS;
829
830 select merge_reason_code into l_merge_reason_code
831 from HZ_MERGE_BATCH
832 where batch_id = p_batch_id;
833
834 -- ***************************************************************************
835 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
836 -- needs to be done. Set Merged To Id is same as Merged From Id and return
837 -- ***************************************************************************
838 if p_from_fk_id = p_to_fk_id then
839 p_to_id := p_from_id;
840 return;
841 end if;
842
843
844 IF p_from_fk_id <> p_to_fk_id THEN
845 BEGIN
846 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
847
848 UPDATE AMS_ACT_PARTNERS
849 SET partner_id = p_to_fk_id,
850 last_update_date = hz_utility_pub.last_update_date,
851 last_updated_by = hz_utility_pub.user_id,
852 last_update_login = hz_utility_pub.last_update_login
853 --, program_application_id = hz_utility_pub.program_application_id,
854 -- program_id = hz_utility_pub.program_id,
855 -- program_update_date = sysdate
856 where partner_id = p_from_fk_id;
857
858 END IF;
859 EXCEPTION
860 WHEN OTHERS THEN
861 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 raise;
864 END;
865 END IF;
866
867 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CAMPAIGN_PARTNER_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
868
869 END Campaign_Partner_Merge;
870
871 -----------------------------------------------------------------------
872 -- PROCEDURE
873 -- Campaign_VAD_Merge
874 --
875 -- HISTORY
876 -- 07/30/2001 mgudivak Created.
877 -----------------------------------------------------------------------
878 PROCEDURE Campaign_VAD_Merge
879 ( p_entity_name IN VARCHAR2
880 ,p_from_id IN NUMBER
881 ,p_to_id IN OUT NOCOPY NUMBER
882 ,p_from_fk_id IN NUMBER
883 ,p_to_fk_id IN NUMBER
884 ,p_parent_entity_name IN VARCHAR2
885 ,p_batch_id IN NUMBER
886 ,p_batch_party_id IN NUMBER
887 ,x_return_status IN OUT NOCOPY VARCHAR2
888 ) is
889 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
890 l_api_version_number CONSTANT NUMBER := 1.0;
891 l_merge_reason_code VARCHAR2(30);
892
893 BEGIN
894
895 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CAMPAIGN_VAD_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
896
897 x_return_status := FND_API.G_RET_STS_SUCCESS;
898
899 select merge_reason_code into l_merge_reason_code
900 from HZ_MERGE_BATCH
901 where batch_id = p_batch_id;
902
903 -- ***************************************************************************
904 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
905 -- needs to be done. Set Merged To Id is same as Merged From Id and return
906 -- ***************************************************************************
907 if p_from_fk_id = p_to_fk_id then
908 p_to_id := p_from_id;
909 return;
910 end if;
911
912
913 IF p_from_fk_id <> p_to_fk_id THEN
914 BEGIN
915 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
916
917 UPDATE AMS_ACT_PARTNERS
918 SET preferred_vad_id = p_to_fk_id,
919 last_update_date = hz_utility_pub.last_update_date,
920 last_updated_by = hz_utility_pub.user_id,
921 last_update_login = hz_utility_pub.last_update_login
922 --,program_application_id = hz_utility_pub.program_application_id,
923 -- program_id = hz_utility_pub.program_id,
924 -- program_update_date = sysdate
925 where preferred_vad_id = p_from_fk_id;
926
927 END IF;
928 EXCEPTION
929 WHEN OTHERS THEN
930 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 raise;
933 END;
934 END IF;
935
939
936 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CAMPAIGN_VAD_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
937
938 END Campaign_VAD_Merge;
940 -----------------------------------------------------------------------
941 -- PROCEDURE
942 -- Campaign_Contact_Merge
943 --
944 -- HISTORY
945 -- 07/30/2001 mgudivak Created.
946 -----------------------------------------------------------------------
947 PROCEDURE Campaign_Contact_Merge
948 ( p_entity_name IN VARCHAR2
949 ,p_from_id IN NUMBER
950 ,p_to_id IN OUT NOCOPY NUMBER
951 ,p_from_fk_id IN NUMBER
952 ,p_to_fk_id IN NUMBER
953 ,p_parent_entity_name IN VARCHAR2
954 ,p_batch_id IN NUMBER
955 ,p_batch_party_id IN NUMBER
956 ,x_return_status IN OUT NOCOPY VARCHAR2
957 ) is
958 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
959 l_api_version_number CONSTANT NUMBER := 1.0;
960 l_merge_reason_code VARCHAR2(30);
961
962 BEGIN
963
964 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CAMPAIGN_CONTACT_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
965
966 x_return_status := FND_API.G_RET_STS_SUCCESS;
967
968 select merge_reason_code into l_merge_reason_code
969 from HZ_MERGE_BATCH
970 where batch_id = p_batch_id;
971
972 -- ***************************************************************************
973 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
974 -- needs to be done. Set Merged To Id is same as Merged From Id and return
975 -- ***************************************************************************
976 if p_from_fk_id = p_to_fk_id then
977 p_to_id := p_from_id;
978 return;
979 end if;
980
981
982 IF p_from_fk_id <> p_to_fk_id THEN
983 BEGIN
984 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
985
986 UPDATE AMS_ACT_PARTNERS
987 SET primary_contact_id = p_to_fk_id,
988 last_update_date = hz_utility_pub.last_update_date,
989 last_updated_by = hz_utility_pub.user_id,
990 last_update_login = hz_utility_pub.last_update_login
991 --, program_application_id = hz_utility_pub.program_application_id,
992 -- program_id = hz_utility_pub.program_id,
993 -- program_update_date = sysdate
994 where primary_contact_id = p_from_fk_id;
995
996 END IF;
997 EXCEPTION
998 WHEN OTHERS THEN
999 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 raise;
1002 END;
1003 END IF;
1004
1005 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CAMPAIGN_CONTACT_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1006
1007 END Campaign_Contact_Merge;
1008 -----------------------------------------------------------------------
1009 FUNCTION check_party_exists(p_party_id IN number)
1010 RETURN varchar2
1011 IS
1012 l_trade_profile_id number;
1013 l_return_flag varchar2(30);
1014
1015 CURSOR get_party_data(p_party_id in number) IS
1016 select trade_profile_id
1017 from ozf_cust_trd_prfls_all
1018 where party_id = p_party_id
1019 and cust_account_id is null;
1020
1021 BEGIN
1022 OPEN get_party_data(p_party_id);
1023 FETCH get_party_data INTO l_trade_profile_id;
1024 CLOSE get_party_data;
1025
1026 IF l_trade_profile_id is null THEN
1027 l_return_flag := 'FALSE';
1028 ELSE
1029 l_return_flag := 'TRUE';
1030 END IF;
1031
1032 RETURN l_return_flag;
1033
1034 END check_party_exists;
1035 -----------------------------------------------------------------------
1036 -- PROCEDURE
1037 -- Trade_Profile_Party_Merge
1038 --
1039 -- HISTORY
1040 -- 07/30/2001 mgudivak Created.
1041 -----------------------------------------------------------------------
1042 PROCEDURE Trade_Profile_Party_Merge
1043 ( p_entity_name IN VARCHAR2
1044 ,p_from_id IN NUMBER
1045 ,p_to_id IN OUT NOCOPY NUMBER
1046 ,p_from_fk_id IN NUMBER
1047 ,p_to_fk_id IN NUMBER
1048 ,p_parent_entity_name IN VARCHAR2
1049 ,p_batch_id IN NUMBER
1050 ,p_batch_party_id IN NUMBER
1051 ,x_return_status IN OUT NOCOPY VARCHAR2
1052 ) is
1053 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1054 l_api_version_number CONSTANT NUMBER := 1.0;
1055 l_merge_reason_code VARCHAR2(30);
1056
1057 l_to_party_exists varchar2(20);
1058 BEGIN
1059 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1060 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TRADE_PROFILE_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1061
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063
1064 select merge_reason_code into l_merge_reason_code
1065 from HZ_MERGE_BATCH
1066 where batch_id = p_batch_id;
1067
1071 -- ***************************************************************************
1068 -- ***************************************************************************
1069 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1070 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1072 if p_from_fk_id = p_to_fk_id then
1073 p_to_id := p_from_id;
1074 return;
1075 end if;
1076
1077
1078 IF p_from_fk_id <> p_to_fk_id THEN
1079 BEGIN
1080 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1081
1082 l_to_party_exists := check_party_exists(p_to_fk_id);
1083
1084 IF l_to_party_exists = 'FALSE' THEN
1085 -- update the from party profile to to_party
1086 UPDATE OZF_CUST_TRD_PRFLS_ALL
1087 SET party_id = p_to_fk_id,
1088 last_update_date = hz_utility_pub.last_update_date,
1089 last_updated_by = hz_utility_pub.user_id,
1090 last_update_login = hz_utility_pub.last_update_login,
1091 program_application_id = hz_utility_pub.program_application_id,
1092 program_id = hz_utility_pub.program_id,
1093 program_update_date = sysdate
1094 where party_id = p_from_fk_id;
1095 ELSIF l_to_party_exists = 'TRUE' THEN
1096 -- delete the from party profile since to_party profile exists
1097 DELETE FROM OZF_CUST_TRD_PRFLS_ALL
1098 WHERE party_id = p_from_fk_id
1099 AND cust_account_id is null;
1100 END IF;
1101
1102 END IF;
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1106 x_return_status := FND_API.G_RET_STS_ERROR;
1107 raise;
1108 END;
1109 END IF;
1110
1111 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TRADE_PROFILE_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1112 */
1113 NULL;
1114 END Trade_Profile_Party_Merge;
1115
1116 -----------------------------------------------------------------------
1117 -- PROCEDURE
1118 -- Claim_Broker_Merge
1119 --
1120 -- HISTORY
1121 -- 07/30/2001 mgudivak Created.
1122 -----------------------------------------------------------------------
1123 PROCEDURE Claim_Broker_Merge
1124 ( p_entity_name IN VARCHAR2
1125 ,p_from_id IN NUMBER
1126 ,p_to_id IN OUT NOCOPY NUMBER
1127 ,p_from_fk_id IN NUMBER
1128 ,p_to_fk_id IN NUMBER
1129 ,p_parent_entity_name IN VARCHAR2
1130 ,p_batch_id IN NUMBER
1131 ,p_batch_party_id IN NUMBER
1132 ,x_return_status IN OUT NOCOPY VARCHAR2
1133 ) is
1134 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1135 l_api_version_number CONSTANT NUMBER := 1.0;
1136 l_merge_reason_code VARCHAR2(30);
1137
1138 BEGIN
1139 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1140 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_BROKER_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1141
1142 x_return_status := FND_API.G_RET_STS_SUCCESS;
1143
1144 select merge_reason_code into l_merge_reason_code
1145 from HZ_MERGE_BATCH
1146 where batch_id = p_batch_id;
1147
1148 -- ***************************************************************************
1149 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1150 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1151 -- ***************************************************************************
1152 if p_from_fk_id = p_to_fk_id then
1153 p_to_id := p_from_id;
1154 return;
1155 end if;
1156
1157
1158 IF p_from_fk_id <> p_to_fk_id THEN
1159 BEGIN
1160 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1161
1162 UPDATE OZF_CLAIMS_ALL
1163 SET broker_id = p_to_fk_id,
1164 last_update_date = hz_utility_pub.last_update_date,
1165 last_updated_by = hz_utility_pub.user_id,
1166 last_update_login = hz_utility_pub.last_update_login,
1167 program_application_id = hz_utility_pub.program_application_id,
1168 program_id = hz_utility_pub.program_id,
1169 program_update_date = sysdate
1170 where broker_id = p_from_fk_id;
1171
1172 END IF;
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 raise;
1178 END;
1179 END IF;
1180
1181 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_BROKER_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1182 */
1183 NULL;
1184 END Claim_Broker_Merge;
1185
1186 -----------------------------------------------------------------------
1187 -- PROCEDURE
1188 -- Claim_Contact_Merge
1189 --
1190 -- HISTORY
1191 -- 07/30/2001 mgudivak Created.
1192 -----------------------------------------------------------------------
1193 PROCEDURE Claim_Contact_Merge
1197 ,p_from_fk_id IN NUMBER
1194 ( p_entity_name IN VARCHAR2
1195 ,p_from_id IN NUMBER
1196 ,p_to_id IN OUT NOCOPY NUMBER
1198 ,p_to_fk_id IN NUMBER
1199 ,p_parent_entity_name IN VARCHAR2
1200 ,p_batch_id IN NUMBER
1201 ,p_batch_party_id IN NUMBER
1202 ,x_return_status IN OUT NOCOPY VARCHAR2
1203 ) is
1204 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1205 l_api_version_number CONSTANT NUMBER := 1.0;
1206 l_merge_reason_code VARCHAR2(30);
1207
1208 BEGIN
1209 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1210 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_CONTACT_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1211
1212 x_return_status := FND_API.G_RET_STS_SUCCESS;
1213
1214 select merge_reason_code into l_merge_reason_code
1215 from HZ_MERGE_BATCH
1216 where batch_id = p_batch_id;
1217
1218 -- ***************************************************************************
1219 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1220 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1221 -- ***************************************************************************
1222 if p_from_fk_id = p_to_fk_id then
1223 p_to_id := p_from_id;
1224 return;
1225 end if;
1226
1227
1228 IF p_from_fk_id <> p_to_fk_id THEN
1229 BEGIN
1230 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1231
1232 UPDATE OZF_CLAIMS_ALL
1233 SET contact_id = p_to_fk_id,
1234 last_update_date = hz_utility_pub.last_update_date,
1235 last_updated_by = hz_utility_pub.user_id,
1236 last_update_login = hz_utility_pub.last_update_login,
1237 program_application_id = hz_utility_pub.program_application_id,
1238 program_id = hz_utility_pub.program_id,
1239 program_update_date = sysdate
1240 where contact_id = p_from_fk_id;
1241
1242 END IF;
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 raise;
1248 END;
1249 END IF;
1250
1251 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_CONTACT_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1252 */
1253 NULL;
1254 END Claim_Contact_Merge;
1255
1256
1257 -----------------------------------------------------------------------
1258 -- PROCEDURE
1259 -- Claim_History_Broker_Merge
1260 --
1261 -- HISTORY
1262 -- 07/30/2001 mgudivak Created.
1263 -----------------------------------------------------------------------
1264 PROCEDURE Claim_History_Broker_Merge
1265 ( p_entity_name IN VARCHAR2
1266 ,p_from_id IN NUMBER
1267 ,p_to_id IN OUT NOCOPY NUMBER
1268 ,p_from_fk_id IN NUMBER
1269 ,p_to_fk_id IN NUMBER
1270 ,p_parent_entity_name IN VARCHAR2
1271 ,p_batch_id IN NUMBER
1272 ,p_batch_party_id IN NUMBER
1273 ,x_return_status IN OUT NOCOPY VARCHAR2
1274 ) is
1275 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1276 l_api_version_number CONSTANT NUMBER := 1.0;
1277 l_merge_reason_code VARCHAR2(30);
1278
1279 BEGIN
1280 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1281 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_HISTORY_BROKER_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1282
1283 x_return_status := FND_API.G_RET_STS_SUCCESS;
1284
1285 select merge_reason_code into l_merge_reason_code
1286 from HZ_MERGE_BATCH
1287 where batch_id = p_batch_id;
1288
1289 -- ***************************************************************************
1290 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1291 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1292 -- ***************************************************************************
1293 if p_from_fk_id = p_to_fk_id then
1294 p_to_id := p_from_id;
1295 return;
1296 end if;
1297
1298
1299 IF p_from_fk_id <> p_to_fk_id THEN
1300 BEGIN
1301 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1302
1303 UPDATE OZF_CLAIMS_HISTORY_ALL
1304 SET broker_id = p_to_fk_id,
1305 last_update_date = hz_utility_pub.last_update_date,
1306 last_updated_by = hz_utility_pub.user_id,
1307 last_update_login = hz_utility_pub.last_update_login,
1308 program_application_id = hz_utility_pub.program_application_id,
1309 program_id = hz_utility_pub.program_id,
1310 program_update_date = sysdate
1311 where broker_id = p_from_fk_id;
1312
1313 END IF;
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1317 x_return_status := FND_API.G_RET_STS_ERROR;
1321
1318 raise;
1319 END;
1320 END IF;
1322 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_HISTORY_BROKER_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1323 */
1324 NULL;
1325 END Claim_History_Broker_Merge;
1326
1327
1328 -----------------------------------------------------------------------
1329 -- PROCEDURE
1330 -- Claim_History_Contact_Merge
1331 --
1332 -- HISTORY
1333 -- 07/30/2001 mgudivak Created.
1334 -----------------------------------------------------------------------
1335 PROCEDURE Claim_History_Contact_Merge
1336 ( p_entity_name IN VARCHAR2
1337 ,p_from_id IN NUMBER
1338 ,p_to_id IN OUT NOCOPY NUMBER
1339 ,p_from_fk_id IN NUMBER
1340 ,p_to_fk_id IN NUMBER
1341 ,p_parent_entity_name IN VARCHAR2
1342 ,p_batch_id IN NUMBER
1343 ,p_batch_party_id IN NUMBER
1344 ,x_return_status IN OUT NOCOPY VARCHAR2
1345 ) is
1346 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1347 l_api_version_number CONSTANT NUMBER := 1.0;
1348 l_merge_reason_code VARCHAR2(30);
1349
1350 BEGIN
1351 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1352 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_HISTORY_CONTACT_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1353
1354 x_return_status := FND_API.G_RET_STS_SUCCESS;
1355
1356 select merge_reason_code into l_merge_reason_code
1357 from HZ_MERGE_BATCH
1358 where batch_id = p_batch_id;
1359
1360 -- ***************************************************************************
1361 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1362 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1363 -- ***************************************************************************
1364 if p_from_fk_id = p_to_fk_id then
1365 p_to_id := p_from_id;
1366 return;
1367 end if;
1368
1369
1370 IF p_from_fk_id <> p_to_fk_id THEN
1371 BEGIN
1372 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1373
1374 UPDATE OZF_CLAIMS_HISTORY_ALL
1375 SET contact_id = p_to_fk_id,
1376 last_update_date = hz_utility_pub.last_update_date,
1377 last_updated_by = hz_utility_pub.user_id,
1378 last_update_login = hz_utility_pub.last_update_login,
1379 program_application_id = hz_utility_pub.program_application_id,
1380 program_id = hz_utility_pub.program_id,
1381 program_update_date = sysdate
1382 where contact_id = p_from_fk_id;
1383
1384 END IF;
1385 EXCEPTION
1386 WHEN OTHERS THEN
1387 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1388 x_return_status := FND_API.G_RET_STS_ERROR;
1389 raise;
1390 END;
1391 END IF;
1392
1393 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CLAIM_HISTORY_CONTACT_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1394 */
1395 NULL;
1396 END Claim_History_Contact_Merge;
1397
1398 -----------------------------------------------------------------------
1399 -- PROCEDURE
1400 -- Budget_Party_Merge
1401 --
1402 -- HISTORY
1403 -- 07/30/2001 mgudivak Created.
1404 -----------------------------------------------------------------------
1405 PROCEDURE Budget_Party_Merge
1406 ( p_entity_name IN VARCHAR2
1407 ,p_from_id IN NUMBER
1408 ,p_to_id IN OUT NOCOPY NUMBER
1409 ,p_from_fk_id IN NUMBER
1410 ,p_to_fk_id IN NUMBER
1411 ,p_parent_entity_name IN VARCHAR2
1412 ,p_batch_id IN NUMBER
1413 ,p_batch_party_id IN NUMBER
1414 ,x_return_status IN OUT NOCOPY VARCHAR2
1415 ) is
1416 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1417 l_api_version_number CONSTANT NUMBER := 1.0;
1418 l_merge_reason_code VARCHAR2(30);
1419
1420 BEGIN
1421 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1422 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.BUDGET_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1423
1424 x_return_status := FND_API.G_RET_STS_SUCCESS;
1425
1426 select merge_reason_code into l_merge_reason_code
1427 from HZ_MERGE_BATCH
1428 where batch_id = p_batch_id;
1429
1430 -- ***************************************************************************
1431 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1432 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1433 -- ***************************************************************************
1434 if p_from_fk_id = p_to_fk_id then
1435 p_to_id := p_from_id;
1436 return;
1437 end if;
1438
1439
1440 IF p_from_fk_id <> p_to_fk_id THEN
1441 BEGIN
1442 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1443
1444 UPDATE AMS_ACT_BUDGETS
1445 SET budget_source_id = p_to_fk_id,
1446 last_update_date = hz_utility_pub.last_update_date,
1450 -- program_id = hz_utility_pub.program_id,
1447 last_updated_by = hz_utility_pub.user_id,
1448 last_update_login = hz_utility_pub.last_update_login
1449 -- ,program_application_id = hz_utility_pub.program_application_id,
1451 -- program_update_date = sysdate
1452 where budget_source_id = p_from_fk_id
1453 and budget_source_type = 'PTNR' ;
1454
1455 END IF;
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1459 x_return_status := FND_API.G_RET_STS_ERROR;
1460 raise;
1461 END;
1462 END IF;
1463
1464 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.BUDGET_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1465 */
1466 NULL;
1467 END Budget_Party_Merge;
1468
1469 -----------------------------------------------------------------------
1470 -- PROCEDURE
1471 -- Budget_Vendor_Merge
1472 --
1473 -- HISTORY
1474 -- 07/30/2001 mgudivak Created.
1475 -----------------------------------------------------------------------
1476 PROCEDURE Budget_Vendor_Merge
1477 ( p_entity_name IN VARCHAR2
1478 ,p_from_id IN NUMBER
1479 ,p_to_id IN OUT NOCOPY NUMBER
1480 ,p_from_fk_id IN NUMBER
1481 ,p_to_fk_id IN NUMBER
1482 ,p_parent_entity_name IN VARCHAR2
1483 ,p_batch_id IN NUMBER
1484 ,p_batch_party_id IN NUMBER
1485 ,x_return_status IN OUT NOCOPY VARCHAR2
1486 ) is
1487 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1488 l_api_version_number CONSTANT NUMBER := 1.0;
1489 l_merge_reason_code VARCHAR2(30);
1490
1491 BEGIN
1492 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1493 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.BUDGET_VENDOR_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1494
1495 x_return_status := FND_API.G_RET_STS_SUCCESS;
1496
1497 select merge_reason_code into l_merge_reason_code
1498 from HZ_MERGE_BATCH
1499 where batch_id = p_batch_id;
1500
1501 -- ***************************************************************************
1502 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1503 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1504 -- ***************************************************************************
1505 if p_from_fk_id = p_to_fk_id then
1506 p_to_id := p_from_id;
1507 return;
1508 end if;
1509
1510
1511 IF p_from_fk_id <> p_to_fk_id THEN
1512 BEGIN
1513 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1514
1515 UPDATE AMS_ACT_BUDGETS
1516 SET vendor_id = p_to_fk_id,
1517 last_update_date = hz_utility_pub.last_update_date,
1518 last_updated_by = hz_utility_pub.user_id,
1519 last_update_login = hz_utility_pub.last_update_login
1520 -- ,program_application_id = hz_utility_pub.program_application_id,
1521 -- program_id = hz_utility_pub.program_id,
1522 -- program_update_date = sysdate
1523 where vendor_id = p_from_fk_id;
1524
1525 END IF;
1526 EXCEPTION
1527 WHEN OTHERS THEN
1528 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1529 x_return_status := FND_API.G_RET_STS_ERROR;
1530 raise;
1531 END;
1532 END IF;
1533
1534 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.BUDGET_VENDOR_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1535 */
1536 NULL;
1537 END Budget_Vendor_Merge;
1538
1539 PROCEDURE OFFER_PARTY_MERGE
1540 ( p_entity_name IN VARCHAR2
1541 ,p_from_id IN NUMBER
1542 ,p_to_id IN OUT NOCOPY NUMBER
1543 ,p_from_fk_id IN NUMBER
1544 ,p_to_fk_id IN NUMBER
1545 ,p_parent_entity_name IN VARCHAR2
1546 ,p_batch_id IN NUMBER
1547 ,p_batch_party_id IN NUMBER
1548 ,x_return_status IN OUT NOCOPY VARCHAR2
1549 ) is
1550 l_api_name CONSTANT VARCHAR2(30) := 'OFFER_PARTY_MERGE';
1551 l_api_version_number CONSTANT NUMBER := 1.0;
1552 l_merge_reason_code VARCHAR2(30);
1553
1554 BEGIN
1555 /* julou 08-APR-2004 migrated to ozfvprmb.pls
1556 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.OFFER_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1557
1558 x_return_status := FND_API.G_RET_STS_SUCCESS;
1559
1560 select merge_reason_code into l_merge_reason_code
1561 from HZ_MERGE_BATCH
1562 where batch_id = p_batch_id;
1563
1564 -- ***************************************************************************
1565 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1566 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1567 -- ***************************************************************************
1568 if p_from_fk_id = p_to_fk_id then
1569 p_to_id := p_from_id;
1570 return;
1571 end if;
1572
1573
1577
1574 IF p_from_fk_id <> p_to_fk_id THEN
1575 BEGIN
1576 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1578 UPDATE AMS_OFFERS
1579 SET BUYING_GROUP_CONTACT_ID = p_to_fk_id,
1580 last_update_date = hz_utility_pub.last_update_date,
1581 last_updated_by = hz_utility_pub.user_id,
1582 last_update_login = hz_utility_pub.last_update_login
1583 where BUYING_GROUP_CONTACT_ID = p_from_fk_id;
1584
1585 END IF;
1586 EXCEPTION
1587 WHEN OTHERS THEN
1588 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1589 x_return_status := FND_API.G_RET_STS_ERROR;
1590 raise;
1591 END;
1592 END IF;
1593
1594 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.OFFER_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1595 */
1596 NULL;
1597 END OFFER_PARTY_Merge;
1598
1599
1600 -----------------------------------------------------------------------
1601 -- PROCEDURE
1602 -- Product_Comp_Party_Merge
1603 --
1604 -- HISTORY
1605 -- 09/24/2001 abhola Created.
1606 -----------------------------------------------------------------------
1607 PROCEDURE Product_Comp_Party_Merge
1608 ( p_entity_name IN VARCHAR2
1609 ,p_from_id IN NUMBER
1610 ,p_to_id IN OUT NOCOPY NUMBER
1611 ,p_from_fk_id IN NUMBER
1612 ,p_to_fk_id IN NUMBER
1613 ,p_parent_entity_name IN VARCHAR2
1614 ,p_batch_id IN NUMBER
1615 ,p_batch_party_id IN NUMBER
1616 ,x_return_status IN OUT NOCOPY VARCHAR2
1617 ) is
1618 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1619 l_api_version_number CONSTANT NUMBER := 1.0;
1620 l_merge_reason_code VARCHAR2(30);
1621
1622 BEGIN
1623
1624 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.Product_Comp_Party_Merge start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1625
1626 x_return_status := FND_API.G_RET_STS_SUCCESS;
1627
1628 select merge_reason_code into l_merge_reason_code
1629 from HZ_MERGE_BATCH
1630 where batch_id = p_batch_id;
1631
1632 -- ***************************************************************************
1633 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1634 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1635 -- ***************************************************************************
1636 if p_from_fk_id = p_to_fk_id then
1637 p_to_id := p_from_id;
1638 return;
1639 end if;
1640
1641
1642 IF p_from_fk_id <> p_to_fk_id THEN
1643 BEGIN
1644 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1645
1646 UPDATE ams_competitor_products_b
1647 SET COMPETITOR_PARTY_ID = p_to_fk_id,
1648 last_update_date = hz_utility_pub.last_update_date,
1649 last_updated_by = hz_utility_pub.user_id,
1650 last_update_login = hz_utility_pub.last_update_login
1651 where COMPETITOR_PARTY_ID = p_from_fk_id ;
1652
1653 END IF;
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1657 x_return_status := FND_API.G_RET_STS_ERROR;
1658 raise;
1659 END;
1660 END IF;
1661
1662 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.Product_Comp_Party_Merge end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1663
1664 END Product_Comp_Party_Merge;
1665
1666 -----------------------------------------------------------------------
1667 -- PROCEDURE
1668 -- PLACEMENT_SITE_PARTY_MERGE
1669 --
1670 -- HISTORY
1671 -- 03/05/2002 sodixit Created.
1672 -----------------------------------------------------------------------
1673 PROCEDURE PLACEMENT_SITE_PARTY_MERGE
1674 ( p_entity_name IN VARCHAR2
1675 ,p_from_id IN NUMBER
1676 ,p_to_id IN OUT NOCOPY NUMBER
1677 ,p_from_fk_id IN NUMBER
1678 ,p_to_fk_id IN NUMBER
1679 ,p_parent_entity_name IN VARCHAR2
1680 ,p_batch_id IN NUMBER
1681 ,p_batch_party_id IN NUMBER
1682 ,x_return_status IN OUT NOCOPY VARCHAR2
1683 ) is
1684 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1685 l_api_version_number CONSTANT NUMBER := 1.0;
1686 l_merge_reason_code VARCHAR2(30);
1687
1688 BEGIN
1689
1690 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.PLACEMENT_SITE_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1691
1692 x_return_status := FND_API.G_RET_STS_SUCCESS;
1693
1694 select merge_reason_code into l_merge_reason_code
1695 from HZ_MERGE_BATCH
1696 where batch_id = p_batch_id;
1697
1698 -- ***************************************************************************
1699 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1700 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1701 -- ***************************************************************************
1702 if p_from_fk_id = p_to_fk_id then
1706
1703 p_to_id := p_from_id;
1704 return;
1705 end if;
1707
1708 IF p_from_fk_id <> p_to_fk_id THEN
1709 BEGIN
1710 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1711
1712 UPDATE AMS_IBA_PL_SITES_B
1713 SET SITE_CATEGORY_OBJECT_ID = p_to_fk_id,
1714 last_update_date = hz_utility_pub.last_update_date,
1715 last_updated_by = hz_utility_pub.user_id,
1716 last_update_login = hz_utility_pub.last_update_login
1717 where SITE_CATEGORY_OBJECT_ID = p_from_fk_id
1718 and SITE_CATEGORY_TYPE = 'AFFILIATES' ;
1719
1720 END IF;
1721 EXCEPTION
1722 WHEN OTHERS THEN
1723 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1724 x_return_status := FND_API.G_RET_STS_ERROR;
1725 raise;
1726 END;
1727 END IF;
1728
1729 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.PLACEMENT_SITE_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1730
1731 END PLACEMENT_SITE_PARTY_MERGE;
1732
1733 -----------------------------------------------------------------------
1734 -- PROCEDURE
1735 -- Src_lines_Party_Merge
1736 --
1737 -- HISTORY
1738 -- 01/09/2003 USingh Created.
1739 -----------------------------------------------------------------------
1740 PROCEDURE Src_lines_Party_Merge
1741 ( p_entity_name IN VARCHAR2
1742 ,p_from_id IN NUMBER
1743 ,p_to_id IN OUT NOCOPY NUMBER
1744 ,p_from_fk_id IN NUMBER
1745 ,p_to_fk_id IN NUMBER
1746 ,p_parent_entity_name IN VARCHAR2
1747 ,p_batch_id IN NUMBER
1748 ,p_batch_party_id IN NUMBER
1749 ,x_return_status IN OUT NOCOPY VARCHAR2
1750 ) is
1751 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1752 l_api_version_number CONSTANT NUMBER := 1.0;
1753 l_merge_reason_code VARCHAR2(30);
1754
1755 BEGIN
1756
1757 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1758
1759 x_return_status := FND_API.G_RET_STS_SUCCESS;
1760
1761 select merge_reason_code into l_merge_reason_code
1762 from HZ_MERGE_BATCH
1763 where batch_id = p_batch_id;
1764
1765 IF l_merge_reason_code = 'DUPLICATE' THEN
1766 -- ***************************************************************************
1767 -- if reason code is duplicate then allow the party merge to happen without
1768 -- any validations.
1769 -- ***************************************************************************
1770 null;
1771 ELSE
1772 -- ***************************************************************************
1773 -- if there are any validations to be done, include it in this section
1774 -- ***************************************************************************
1775 null;
1776 END IF;
1777
1778 -- ***************************************************************************
1779 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1780 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1781 -- ***************************************************************************
1782 if p_from_fk_id = p_to_fk_id then
1783 p_to_id := p_from_id;
1784 return;
1785 end if;
1786
1787 -- ***************************************************************************
1788 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1789 -- dependent record to the new parent. Before transferring check if a similar
1790 -- dependent record exists on the new parent. If a duplicate exists then do
1791 -- not transfer and return the id of the duplicate record as the Merged To Id
1792 -- ***************************************************************************
1793
1794 -- ***************************************************************************
1795 -- Add your own logic if you need to take care of the following cases
1796 -- Check the if record duplicate if change party_id from merge-from
1797 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
1798 -- situation
1799 --
1800 -- customer_id address_id contact_id
1801 -- =========== ========== ==========
1802 -- 1200 1100
1803 -- 1300 1400
1804 --
1805 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
1806 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
1807 -- therefore, if changing 1200 to 1300 (customer_id)
1808 -- and 1100 to 1400 (address_id), then it will cause unique
1809 -- key violation assume that all other fields are the same
1810 -- So, please check if you need to check for record duplication
1811 -- ***************************************************************************
1812
1813 IF p_from_fk_id <> p_to_fk_id THEN
1814 BEGIN
1815 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1816 -- Following lines of code is added by ptendulk on 14-May-2001
1817 -- to do the party merge for table ams_channels_b
1818 UPDATE AMS_IMP_SOURCE_LINES
1819 SET party_id = p_to_fk_id,
1820 last_update_date = hz_utility_pub.last_update_date,
1824
1821 last_updated_by = hz_utility_pub.user_id,
1822 last_update_login = hz_utility_pub.last_update_login
1823 where party_id = p_from_fk_id;
1825 END IF;
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1829 x_return_status := FND_API.G_RET_STS_ERROR;
1830 raise;
1831 END;
1832 END IF;
1833
1834 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1835
1836 END Src_lines_Party_Merge;
1837
1838 -----------------------------------------------------------------------
1839 -- PROCEDURE
1840 -- List_entries_Party_Merge
1841 --
1842 -- HISTORY
1843 -- 01/09/2003 USingh Created.
1844 -----------------------------------------------------------------------
1845 PROCEDURE List_entries_Party_Merge
1846 ( p_entity_name IN VARCHAR2
1847 ,p_from_id IN NUMBER
1848 ,p_to_id IN OUT NOCOPY NUMBER
1849 ,p_from_fk_id IN NUMBER
1850 ,p_to_fk_id IN NUMBER
1851 ,p_parent_entity_name IN VARCHAR2
1852 ,p_batch_id IN NUMBER
1853 ,p_batch_party_id IN NUMBER
1854 ,x_return_status IN OUT NOCOPY VARCHAR2
1855 ) is
1856 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1857 l_api_version_number CONSTANT NUMBER := 1.0;
1858 l_merge_reason_code VARCHAR2(30);
1859
1860 BEGIN
1861
1862 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1863
1864 x_return_status := FND_API.G_RET_STS_SUCCESS;
1865
1866 select merge_reason_code into l_merge_reason_code
1867 from HZ_MERGE_BATCH
1868 where batch_id = p_batch_id;
1869
1870 IF l_merge_reason_code = 'DUPLICATE' THEN
1871 -- ***************************************************************************
1872 -- if reason code is duplicate then allow the party merge to happen without
1873 -- any validations.
1874 -- ***************************************************************************
1875 null;
1876 ELSE
1877 -- ***************************************************************************
1878 -- if there are any validations to be done, include it in this section
1879 -- ***************************************************************************
1880 null;
1881 END IF;
1882
1883 -- ***************************************************************************
1884 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1885 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1886 -- ***************************************************************************
1887 if p_from_fk_id = p_to_fk_id then
1888 p_to_id := p_from_id;
1889 return;
1890 end if;
1891
1892 -- ***************************************************************************
1893 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1894 -- dependent record to the new parent. Before transferring check if a similar
1895 -- dependent record exists on the new parent. If a duplicate exists then do
1896 -- not transfer and return the id of the duplicate record as the Merged To Id
1897 -- ***************************************************************************
1898
1899 -- ***************************************************************************
1900 -- Add your own logic if you need to take care of the following cases
1901 -- Check the if record duplicate if change party_id from merge-from
1902 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
1903 -- situation
1904 --
1905 -- customer_id address_id contact_id
1906 -- =========== ========== ==========
1907 -- 1200 1100
1908 -- 1300 1400
1909 --
1910 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
1911 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
1912 -- therefore, if changing 1200 to 1300 (customer_id)
1913 -- and 1100 to 1400 (address_id), then it will cause unique
1914 -- key violation assume that all other fields are the same
1915 -- So, please check if you need to check for record duplication
1916 -- ***************************************************************************
1917
1918 IF p_from_fk_id <> p_to_fk_id THEN
1919 BEGIN
1920 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
1921 -- Following lines of code is added by ptendulk on 14-May-2001
1922 -- to do the party merge for table ams_channels_b
1923 UPDATE AMS_LIST_ENTRIES
1924 SET party_id = p_to_fk_id,
1925 last_update_date = hz_utility_pub.last_update_date,
1926 last_updated_by = hz_utility_pub.user_id,
1927 last_update_login = hz_utility_pub.last_update_login
1928 where party_id = p_from_fk_id;
1929
1930 END IF;
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
1934 x_return_status := FND_API.G_RET_STS_ERROR;
1935 raise;
1936 END;
1937 END IF;
1938
1942
1939 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1940
1941 END List_entries_Party_Merge;
1943 -----------------------------------------------------------------------
1944 -- PROCEDURE
1945 -- Listentries_Parent_Party_Merge
1946 --
1947 -- HISTORY
1948 -- 01/09/2003 USingh Created.
1949 -----------------------------------------------------------------------
1950 PROCEDURE Listentries_Parent_Party_Merge
1951 ( p_entity_name IN VARCHAR2
1952 ,p_from_id IN NUMBER
1953 ,p_to_id IN OUT NOCOPY NUMBER
1954 ,p_from_fk_id IN NUMBER
1955 ,p_to_fk_id IN NUMBER
1956 ,p_parent_entity_name IN VARCHAR2
1957 ,p_batch_id IN NUMBER
1958 ,p_batch_party_id IN NUMBER
1959 ,x_return_status IN OUT NOCOPY VARCHAR2
1960 ) is
1961 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
1962 l_api_version_number CONSTANT NUMBER := 1.0;
1963 l_merge_reason_code VARCHAR2(30);
1964
1965 BEGIN
1966
1967 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
1968
1969 x_return_status := FND_API.G_RET_STS_SUCCESS;
1970
1971 select merge_reason_code into l_merge_reason_code
1972 from HZ_MERGE_BATCH
1973 where batch_id = p_batch_id;
1974
1975 IF l_merge_reason_code = 'DUPLICATE' THEN
1976 -- ***************************************************************************
1977 -- if reason code is duplicate then allow the party merge to happen without
1978 -- any validations.
1979 -- ***************************************************************************
1980 null;
1981 ELSE
1982 -- ***************************************************************************
1983 -- if there are any validations to be done, include it in this section
1984 -- ***************************************************************************
1985 null;
1986 END IF;
1987
1988 -- ***************************************************************************
1989 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1990 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1991 -- ***************************************************************************
1992 if p_from_fk_id = p_to_fk_id then
1993 p_to_id := p_from_id;
1994 return;
1995 end if;
1996
1997 -- ***************************************************************************
1998 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1999 -- dependent record to the new parent. Before transferring check if a similar
2000 -- dependent record exists on the new parent. If a duplicate exists then do
2001 -- not transfer and return the id of the duplicate record as the Merged To Id
2002 -- ***************************************************************************
2003
2004 -- ***************************************************************************
2005 -- Add your own logic if you need to take care of the following cases
2006 -- Check the if record duplicate if change party_id from merge-from
2007 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2008 -- situation
2009 --
2010 -- customer_id address_id contact_id
2011 -- =========== ========== ==========
2012 -- 1200 1100
2013 -- 1300 1400
2014 --
2015 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2016 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2017 -- therefore, if changing 1200 to 1300 (customer_id)
2018 -- and 1100 to 1400 (address_id), then it will cause unique
2019 -- key violation assume that all other fields are the same
2020 -- So, please check if you need to check for record duplication
2021 -- ***************************************************************************
2022
2023 IF p_from_fk_id <> p_to_fk_id THEN
2024 BEGIN
2025 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2026 -- Following lines of code is added by ptendulk on 14-May-2001
2027 -- to do the party merge for table ams_channels_b
2028 UPDATE AMS_LIST_ENTRIES
2029 SET parent_party_id = p_to_fk_id,
2030 last_update_date = hz_utility_pub.last_update_date,
2031 last_updated_by = hz_utility_pub.user_id,
2032 last_update_login = hz_utility_pub.last_update_login
2033 where parent_party_id = p_from_fk_id;
2034
2035 END IF;
2036 EXCEPTION
2037 WHEN OTHERS THEN
2038 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
2039 x_return_status := FND_API.G_RET_STS_ERROR;
2040 raise;
2041 END;
2042 END IF;
2043
2044 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.CHANNEL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2045
2046 END Listentries_Parent_Party_Merge;
2047
2048 -----------------------------------------------------------------------
2049 -- PROCEDURE
2050 -- Venues_Party_Merge created for (ams_venues_b)
2051 --
2052 -- HISTORY
2053 -- 06-Mar-2003 Musman Created
2054 -----------------------------------------------------------------------
2058 ,p_to_id IN OUT NOCOPY NUMBER
2055 PROCEDURE Venues_Party_Merge
2056 ( p_entity_name IN VARCHAR2
2057 ,p_from_id IN NUMBER
2059 ,p_from_fk_id IN NUMBER
2060 ,p_to_fk_id IN NUMBER
2061 ,p_parent_entity_name IN VARCHAR2
2062 ,p_batch_id IN NUMBER
2063 ,p_batch_party_id IN NUMBER
2064 ,x_return_status IN OUT NOCOPY VARCHAR2
2065 ) is
2066 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2067 l_api_version_number CONSTANT NUMBER := 1.0;
2068 l_merge_reason_code VARCHAR2(30);
2069
2070 BEGIN
2071
2072 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.VENUES_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2073
2074 x_return_status := FND_API.G_RET_STS_SUCCESS;
2075
2076 select merge_reason_code into l_merge_reason_code
2077 from HZ_MERGE_BATCH
2078 where batch_id = p_batch_id;
2079
2080 IF l_merge_reason_code = 'DUPLICATE' THEN
2081 -- ***************************************************************************
2082 -- if reason code is duplicate then allow the party merge to happen without
2083 -- any validations.
2084 -- ***************************************************************************
2085 null;
2086 ELSE
2087 -- ***************************************************************************
2088 -- if there are any validations to be done, include it in this section
2089 -- ***************************************************************************
2090 null;
2091 END IF;
2092
2093 -- ***************************************************************************
2094 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2095 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2096 -- ***************************************************************************
2097 if p_from_fk_id = p_to_fk_id then
2098 p_to_id := p_from_id;
2099 return;
2100 end if;
2101
2102 -- ***************************************************************************
2103 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2104 -- dependent record to the new parent. Before transferring check if a similar
2105 -- dependent record exists on the new parent. If a duplicate exists then do
2106 -- not transfer and return the id of the duplicate record as the Merged To Id
2107 -- ***************************************************************************
2108
2109 -- ***************************************************************************
2110 -- Add your own logic if you need to take care of the following cases
2111 -- Check the if record duplicate if change party_id from merge-from
2112 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2113 -- situation
2114 --
2115 -- customer_id address_id contact_id
2116 -- =========== ========== ==========
2117 -- 1200 1100
2118 -- 1300 1400
2119 --
2120 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2121 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2122 -- therefore, if changing 1200 to 1300 (customer_id)
2123 -- and 1100 to 1400 (address_id), then it will cause unique
2124 -- key violation assume that all other fields are the same
2125 -- So, please check if you need to check for record duplication
2126 -- ***************************************************************************
2127
2128 IF p_from_fk_id <> p_to_fk_id THEN
2129 BEGIN
2130 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2131 -- Following lines of code is added by musman on 6-Mar-2003
2132 -- to do the party merge for table ams_venues_b
2133 UPDATE AMS_VENUES_B
2134 SET party_id = p_to_fk_id,
2135 last_update_date = hz_utility_pub.last_update_date,
2136 last_updated_by = hz_utility_pub.user_id,
2137 last_update_login = hz_utility_pub.last_update_login
2138 --enabled_flag = 'N' -- added to fix bug#3483075:anchaudh
2139 where party_id = p_from_fk_id;
2140
2141 END IF;
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
2145 x_return_status := FND_API.G_RET_STS_ERROR;
2146 raise;
2147 END;
2148 END IF;
2149
2150 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.VENUES_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2151
2152 END Venues_Party_Merge;
2153
2154 -----------------------------------------------------------------------
2155 -- PROCEDURE
2156 -- Offer_Denorm_Party_Merge
2157 --
2158 -- HISTORY
2159 -- 14-APR-2003 julou Created.
2160 -----------------------------------------------------------------------
2161 PROCEDURE Offer_Denorm_Party_Merge
2162 ( p_entity_name IN VARCHAR2
2163 ,p_from_id IN NUMBER
2164 ,p_to_id IN OUT NOCOPY NUMBER
2165 ,p_from_fk_id IN NUMBER
2166 ,p_to_fk_id IN NUMBER
2167 ,p_parent_entity_name IN VARCHAR2
2168 ,p_batch_id IN NUMBER
2169 ,p_batch_party_id IN NUMBER
2173 l_api_version_number CONSTANT NUMBER := 1.0;
2170 ,x_return_status IN OUT NOCOPY VARCHAR2
2171 ) IS
2172 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2174 l_merge_reason_code VARCHAR2(30);
2175
2176 CURSOR c_list_header_id IS
2177 SELECT qp_list_header_id
2178 FROM ams_offer_parties
2179 WHERE party_id = p_from_fk_id;
2180
2181 CURSOR c_is_duplicate(l_list_header_id NUMBER, l_party_id NUMBER) IS
2182 SELECT 'Y'
2183 FROM ams_offer_parties
2184 WHERE qp_list_header_id = l_list_header_id
2185 AND party_id = l_party_id;
2186
2187 l_is_duplicate VARCHAR2(10);
2188
2189 BEGIN
2190 /* julou 08-APR-2004 migrated to ozfvprmb.pls
2191 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.OFFER_DENORM_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2192
2193 x_return_status := FND_API.G_RET_STS_SUCCESS;
2194
2195 SELECT merge_reason_code INTO l_merge_reason_code
2196 FROM HZ_MERGE_BATCH
2197 WHERE batch_id = p_batch_id;
2198
2199 IF l_merge_reason_code = 'DUPLICATE' THEN
2200 -- ***************************************************************************
2201 -- if reason code is duplicate then allow the party merge to happen without
2202 -- any validations.
2203 -- ***************************************************************************
2204 null;
2205 ELSE
2206 -- ***************************************************************************
2207 -- if there are any validations to be done, include it in this section
2208 -- ***************************************************************************
2209 null;
2210 END IF;
2211
2212 -- ***************************************************************************
2213 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2214 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2215 -- ***************************************************************************
2216 if p_from_fk_id = p_to_fk_id then
2217 p_to_id := p_from_id;
2218 return;
2219 end if;
2220
2221 -- ***************************************************************************
2222 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2223 -- dependent record to the new parent. Before transferring check if a similar
2224 -- dependent record exists on the new parent. If a duplicate exists then do
2225 -- not transfer and return the id of the duplicate record as the Merged To Id
2226 -- ***************************************************************************
2227
2228 -- ***************************************************************************
2229 -- Add your own logic if you need to take care of the following cases
2230 -- Check the if record duplicate if change party_id from merge-from
2231 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2232 -- situation
2233 --
2234 -- customer_id address_id contact_id
2235 -- =========== ========== ==========
2236 -- 1200 1100
2237 -- 1300 1400
2238 --
2239 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2240 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2241 -- therefore, if changing 1200 to 1300 (customer_id)
2242 -- and 1100 to 1400 (address_id), then it will cause unique
2243 -- key violation assume that all other fields are the same
2244 -- So, please check if you need to check for record duplication
2245 -- ***************************************************************************
2246
2247 IF p_from_fk_id <> p_to_fk_id THEN
2248 BEGIN
2249 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2250 FOR l_list_header_id IN c_list_header_id LOOP
2251 l_is_duplicate := NULL;
2252
2253 OPEN c_is_duplicate(l_list_header_id.qp_list_header_id, p_to_fk_id);
2254 FETCH c_is_duplicate INTO l_is_duplicate;
2255 CLOSE c_is_duplicate;
2256
2257 IF l_is_duplicate = 'Y' THEN
2258 DELETE FROM ams_offer_parties
2259 WHERE qp_list_header_id = l_list_header_id.qp_list_header_id
2260 AND party_id = p_from_fk_id;
2261 ELSE
2262 UPDATE ams_offer_parties
2263 SET party_id = p_to_fk_id
2264 ,last_update_date = hz_utility_pub.last_update_date
2265 ,last_updated_by = hz_utility_pub.user_id
2266 ,last_update_login = hz_utility_pub.last_update_login
2267 WHERE qp_list_header_id = l_list_header_id.qp_list_header_id
2268 AND party_id = p_from_fk_id;
2269 END IF;
2270
2271 END LOOP;
2272 END IF;
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
2276 x_return_status := FND_API.G_RET_STS_ERROR;
2277 raise;
2278 END;
2279 END IF;
2280
2281 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.OFFER_DENORM_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2282 */
2283 NULL;
2284 END Offer_Denorm_Party_Merge;
2285
2286
2287 -----------------------------------------------------------------------
2288 -- PROCEDURE
2289 -- Resources_Party_Merge created for (ams_act_resources)
2290 --
2291 -- HISTORY
2295
2292 -- 16-May-2003 soagrawa Created
2293 -----------------------------------------------------------------------
2294
2296
2297 PROCEDURE Resources_Party_Merge
2298 ( p_entity_name IN VARCHAR2
2299 ,p_from_id IN NUMBER
2300 ,p_to_id IN OUT NOCOPY NUMBER
2301 ,p_from_fk_id IN NUMBER
2302 ,p_to_fk_id IN NUMBER
2303 ,p_parent_entity_name IN VARCHAR2
2304 ,p_batch_id IN NUMBER
2305 ,p_batch_party_id IN NUMBER
2306 ,x_return_status IN OUT NOCOPY VARCHAR2
2307 ) is
2308 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2309 l_api_version_number CONSTANT NUMBER := 1.0;
2310 l_merge_reason_code VARCHAR2(30);
2311
2312 BEGIN
2313
2314
2315 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.RESOURCES_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2316
2317 x_return_status := FND_API.G_RET_STS_SUCCESS;
2318
2319 select merge_reason_code into l_merge_reason_code
2320 from HZ_MERGE_BATCH
2321 where batch_id = p_batch_id;
2322
2323 IF l_merge_reason_code = 'DUPLICATE' THEN
2324 --***************************************************************************
2325 -- if reason code is duplicate then allow the party merge to happen without
2326 -- any validations.
2327 --***************************************************************************
2328 null;
2329 ELSE
2330 --***************************************************************************
2331 -- if there are any validations to be done, include it in this section
2332 --***************************************************************************
2333 null;
2334 END IF;
2335
2336 --***************************************************************************
2337 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2338 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2339 --***************************************************************************
2340 if p_from_fk_id = p_to_fk_id then
2341 p_to_id := p_from_id;
2342 return;
2343 end if;
2344
2345 --***************************************************************************
2346 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2347 -- dependent record to the new parent. Before transferring check if a similar
2348 -- dependent record exists on the new parent. If a duplicate exists then do
2349 -- not transfer and return the id of the duplicate record as the Merged To Id
2350 --***************************************************************************
2351
2352 --***************************************************************************
2353 -- Add your own logic if you need to take care of the following cases
2354 -- Check the if record duplicate if change party_id from merge-from
2355 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2356 -- situation
2357 --
2358 -- customer_id address_id contact_id
2359 -- =========== ========== ==========
2360 -- 1200 1100
2361 -- 1300 1400
2362 --
2363 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2364 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2365 -- therefore, if changing 1200 to 1300 (customer_id)
2366 -- and 1100 to 1400 (address_id), then it will cause unique
2367 -- key violation assume that all other fields are the same
2368 -- So, please check if you need to check for record duplication
2369 --***************************************************************************
2370
2371 IF p_from_fk_id <> p_to_fk_id THEN
2372 BEGIN
2373 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2374
2375
2376 -- Following lines of code is added by soagrawa on 23-Mar-2003
2377 -- to do the party merge for table ams_act_resources
2378
2379 UPDATE AMS_ACT_RESOURCES
2380 SET resource_id = p_to_fk_id,
2381 last_update_date = hz_utility_pub.last_update_date,
2382 last_updated_by = hz_utility_pub.user_id,
2383 last_update_login = hz_utility_pub.last_update_login
2384 where resource_id = p_from_fk_id;
2385
2386 END IF;
2387 EXCEPTION
2388 WHEN OTHERS THEN
2389 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
2390 x_return_status := FND_API.G_RET_STS_ERROR;
2391 raise;
2392 END;
2393 END IF;
2394
2395
2396 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.RESOURCES_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2397
2398 END Resources_Party_Merge;
2399
2400 -----------------------------------------------------------------------
2401 -- PROCEDURE
2402 -- Agendas_Party_Merge created for (ams_agendas_b)
2403 --
2404 -- HISTORY
2405 -- 09-May-2003 dbiswas Created
2406 -----------------------------------------------------------------------
2407
2408 PROCEDURE Agendas_Party_Merge
2409 ( p_entity_name IN VARCHAR2
2410 ,p_from_id IN NUMBER
2411 ,p_to_id IN OUT NOCOPY NUMBER
2412 ,p_from_fk_id IN NUMBER
2413 ,p_to_fk_id IN NUMBER
2414 ,p_parent_entity_name IN VARCHAR2
2415 ,p_batch_id IN NUMBER
2419 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2416 ,p_batch_party_id IN NUMBER
2417 ,x_return_status IN OUT NOCOPY VARCHAR2
2418 ) is
2420 l_api_version_number CONSTANT NUMBER := 1.0;
2421 l_merge_reason_code VARCHAR2(30);
2422
2423 BEGIN
2424
2425
2426 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.AGENDAS_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2427
2428 x_return_status := FND_API.G_RET_STS_SUCCESS;
2429
2430 select merge_reason_code into l_merge_reason_code
2431 from HZ_MERGE_BATCH
2432 where batch_id = p_batch_id;
2433
2434 IF l_merge_reason_code = 'DUPLICATE' THEN
2435 --***************************************************************************
2436 -- if reason code is duplicate then allow the party merge to happen without
2437 -- any validations.
2438 --***************************************************************************
2439 null;
2440 ELSE
2441 --***************************************************************************
2442 -- if there are any validations to be done, include it in this section
2443 --***************************************************************************
2444 null;
2445 END IF;
2446
2447 --***************************************************************************
2448 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2449 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2450 --***************************************************************************
2451 if p_from_fk_id = p_to_fk_id then
2452 p_to_id := p_from_id;
2453 return;
2454 end if;
2455
2456 --***************************************************************************
2457 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2458 -- dependent record to the new parent. Before transferring check if a similar
2459 -- dependent record exists on the new parent. If a duplicate exists then do
2460 -- not transfer and return the id of the duplicate record as the Merged To Id
2461 --***************************************************************************
2462
2463 --***************************************************************************
2464 -- Add your own logic if you need to take care of the following cases
2465 -- Check the if record duplicate if change party_id from merge-from
2466 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2467 -- situation
2468 --
2469 -- customer_id address_id contact_id
2470 -- =========== ========== ==========
2471 -- 1200 1100
2472 -- 1300 1400
2473 --
2474 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2475 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2476 -- therefore, if changing 1200 to 1300 (customer_id)
2477 -- and 1100 to 1400 (address_id), then it will cause unique
2478 -- key violation assume that all other fields are the same
2479 -- So, please check if you need to check for record duplication
2480 --***************************************************************************
2481
2482 IF p_from_fk_id <> p_to_fk_id THEN
2483 BEGIN
2484 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2485
2486
2487 -- Following lines of code is added by dbiswas on 09-Mar-2003
2488 -- to do the party merge for table ams_agendas_b
2489 UPDATE AMS_AGENDAS_B
2490 SET coordinator_id = p_to_fk_id,
2491 last_update_date = hz_utility_pub.last_update_date,
2492 last_updated_by = hz_utility_pub.user_id,
2493 last_update_login = hz_utility_pub.last_update_login
2494 where coordinator_id = p_from_fk_id;
2495
2496 END IF;
2497 EXCEPTION
2498 WHEN OTHERS THEN
2499 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
2500 x_return_status := FND_API.G_RET_STS_ERROR;
2501 raise;
2502 END;
2503 END IF;
2504
2505
2506 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.AGENDAS_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2507
2508 END Agendas_Party_Merge;
2509
2510 -----------------------------------------------------------------------
2511 -- PROCEDURE
2512 -- TCOP_CHANNEL_PARTY_MERGE created for (AMS_TCOP_CHANNEL_SUMMARY)
2513 --
2514 -- HISTORY
2515 -- 02-Jan-2004 mayjain Created
2516 -----------------------------------------------------------------------
2517 PROCEDURE TCOP_CHANNEL_PARTY_MERGE
2518 ( p_entity_name IN VARCHAR2
2519 ,p_from_id IN NUMBER
2520 ,p_to_id IN OUT NOCOPY NUMBER
2521 ,p_from_fk_id IN NUMBER
2522 ,p_to_fk_id IN NUMBER
2523 ,p_parent_entity_name IN VARCHAR2
2524 ,p_batch_id IN NUMBER
2525 ,p_batch_party_id IN NUMBER
2526 ,x_return_status IN OUT NOCOPY VARCHAR2
2527 )
2528 is
2529 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2530 l_api_version_number CONSTANT NUMBER := 1.0;
2531 l_merge_reason_code VARCHAR2(30);
2532
2533
2534
2535 BEGIN
2536
2537
2538 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_CHANNEL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2539
2540 x_return_status := FND_API.G_RET_STS_SUCCESS;
2541
2542 select merge_reason_code into l_merge_reason_code
2543 from HZ_MERGE_BATCH
2544 where batch_id = p_batch_id;
2545
2546 IF l_merge_reason_code = 'DUPLICATE' THEN
2547 --***************************************************************************
2548 -- if reason code is duplicate then allow the party merge to happen without
2549 -- any validations.
2550 --***************************************************************************
2551 null;
2552 ELSE
2553 --***************************************************************************
2554 -- if there are any validations to be done, include it in this section
2555 --***************************************************************************
2556 null;
2557 END IF;
2558
2559 --***************************************************************************
2560 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2561 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2562 --***************************************************************************
2563 if p_from_fk_id = p_to_fk_id then
2567
2564 p_to_id := p_from_id;
2565 return;
2566 end if;
2568 --***************************************************************************
2569 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2570 -- dependent record to the new parent. Before transferring check if a similar
2571 -- dependent record exists on the new parent. If a duplicate exists then do
2572 -- not transfer and return the id of the duplicate record as the Merged To Id
2573 --***************************************************************************
2574
2575 --***************************************************************************
2576 -- Add your own logic if you need to take care of the following cases
2577 -- Check the if record duplicate if change party_id from merge-from
2578 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2579 -- situation
2580 --
2581 -- customer_id address_id contact_id
2582 -- =========== ========== ==========
2583 -- 1200 1100
2584 -- 1300 1400
2585 --
2586 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2587 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2588 -- therefore, if changing 1200 to 1300 (customer_id)
2589 -- and 1100 to 1400 (address_id), then it will cause unique
2590 -- key violation assume that all other fields are the same
2591 -- So, please check if you need to check for record duplication
2592 --***************************************************************************
2593
2594 IF p_from_fk_id <> p_to_fk_id THEN
2595 BEGIN
2596 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2597
2598 -- Delete all the rows with which have the p_from_fk_id and media_id combination same as p_to_fk_id and media_id
2599 DELETE FROM AMS_TCOP_CHANNEL_SUMMARY
2600 WHERE party_id = p_from_fk_id
2601 and channel_summary_id in
2602 (SELECT f.channel_summary_id
2603 FROM AMS_TCOP_CHANNEL_SUMMARY f, AMS_TCOP_CHANNEL_SUMMARY t
2604 WHERE f.party_id = p_from_fk_id
2605 AND t.party_id = p_to_fk_id
2606 AND f.MEDIA_ID = t.MEDIA_ID);
2607
2608 -- Update the rows which do not satisfy the above criteria
2609 UPDATE AMS_TCOP_CHANNEL_SUMMARY
2610 SET party_id = p_to_fk_id,
2614 WHERE party_id = p_from_fk_id
2611 last_update_date = hz_utility_pub.last_update_date,
2612 last_updated_by = hz_utility_pub.user_id,
2613 last_update_login = hz_utility_pub.last_update_login
2615 and channel_summary_id not in
2616 (SELECT f.channel_summary_id
2617 FROM AMS_TCOP_CHANNEL_SUMMARY f, AMS_TCOP_CHANNEL_SUMMARY t
2618 WHERE f.party_id = p_from_fk_id
2619 AND t.party_id = p_to_fk_id
2620 AND f.MEDIA_ID = t.MEDIA_ID);
2621
2622 END IF;
2623 EXCEPTION
2624 WHEN OTHERS THEN
2625
2626 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
2627 x_return_status := FND_API.G_RET_STS_ERROR;
2628 raise;
2629 END;
2630 END IF;
2631
2632
2633 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_CHANNEL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2634
2635 END TCOP_CHANNEL_PARTY_MERGE;
2636
2637
2638
2639 -----------------------------------------------------------------------
2640 -- PROCEDURE
2641 -- TCOP_CONTACT_PARTY_MERGE created for (AMS_TCOP_CONTACTS)
2642 --
2643 -- HISTORY
2644 -- 02-Jan-2004 mayjain Created
2645 -----------------------------------------------------------------------
2646 PROCEDURE TCOP_CONTACT_PARTY_MERGE
2647 ( p_entity_name IN VARCHAR2
2648 ,p_from_id IN NUMBER
2649 ,p_to_id IN OUT NOCOPY NUMBER
2650 ,p_from_fk_id IN NUMBER
2651 ,p_to_fk_id IN NUMBER
2652 ,p_parent_entity_name IN VARCHAR2
2653 ,p_batch_id IN NUMBER
2654 ,p_batch_party_id IN NUMBER
2655 ,x_return_status IN OUT NOCOPY VARCHAR2
2656 )
2657 is
2658 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2659 l_api_version_number CONSTANT NUMBER := 1.0;
2660 l_merge_reason_code VARCHAR2(30);
2661
2662
2663
2664 BEGIN
2665
2666
2667 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_CONTACT_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2668
2669 x_return_status := FND_API.G_RET_STS_SUCCESS;
2670
2671 select merge_reason_code into l_merge_reason_code
2672 from HZ_MERGE_BATCH
2673 where batch_id = p_batch_id;
2674
2675 IF l_merge_reason_code = 'DUPLICATE' THEN
2676 --***************************************************************************
2677 -- if reason code is duplicate then allow the party merge to happen without
2678 -- any validations.
2679 --***************************************************************************
2680 null;
2681 ELSE
2682 --***************************************************************************
2683 -- if there are any validations to be done, include it in this section
2684 --***************************************************************************
2685 null;
2689 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2686 END IF;
2687
2688 --***************************************************************************
2690 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2691 --***************************************************************************
2692 if p_from_fk_id = p_to_fk_id then
2693 p_to_id := p_from_id;
2694 return;
2695 end if;
2696
2697 --***************************************************************************
2698 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2699 -- dependent record to the new parent. Before transferring check if a similar
2700 -- dependent record exists on the new parent. If a duplicate exists then do
2701 -- not transfer and return the id of the duplicate record as the Merged To Id
2702 --***************************************************************************
2703
2704 --***************************************************************************
2705 -- Add your own logic if you need to take care of the following cases
2706 -- Check the if record duplicate if change party_id from merge-from
2707 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2708 -- situation
2709 --
2710 -- customer_id address_id contact_id
2711 -- =========== ========== ==========
2712 -- 1200 1100
2713 -- 1300 1400
2714 --
2715 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2716 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2717 -- therefore, if changing 1200 to 1300 (customer_id)
2718 -- and 1100 to 1400 (address_id), then it will cause unique
2719 -- key violation assume that all other fields are the same
2720 -- So, please check if you need to check for record duplication
2721 --***************************************************************************
2722
2723 IF p_from_fk_id <> p_to_fk_id THEN
2724 BEGIN
2725 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2726
2727 DELETE FROM AMS_TCOP_CONTACTS
2728 WHERE party_id = p_from_fk_id
2729 and CONTACT_ID in
2730 (SELECT f.CONTACT_ID
2731 FROM AMS_TCOP_CONTACTS f, AMS_TCOP_CONTACTS t
2732 WHERE f.party_id = p_from_fk_id
2733 AND t.party_id = p_to_fk_id
2734 AND f.SCHEDULE_ID = t.SCHEDULE_ID);
2735
2736
2737 UPDATE AMS_TCOP_CONTACTS
2738 SET party_id = p_to_fk_id,
2739 last_update_date = hz_utility_pub.last_update_date,
2740 last_updated_by = hz_utility_pub.user_id,
2741 last_update_login = hz_utility_pub.last_update_login
2742 WHERE party_id = p_from_fk_id
2743 and CONTACT_ID not in
2744 (SELECT f.CONTACT_ID
2745 FROM AMS_TCOP_CONTACTS f, AMS_TCOP_CONTACTS t
2746 WHERE f.party_id = p_from_fk_id
2747 AND t.party_id = p_to_fk_id
2748 AND f.SCHEDULE_ID = t.SCHEDULE_ID);
2749
2750 END IF;
2751 EXCEPTION
2752 WHEN OTHERS THEN
2753
2754 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
2755 x_return_status := FND_API.G_RET_STS_ERROR;
2756 raise;
2757 END;
2758 END IF;
2759
2760
2761 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_CONTACT_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2762
2763 END TCOP_CONTACT_PARTY_MERGE;
2764
2765
2769 --
2766 -----------------------------------------------------------------------
2767 -- PROCEDURE
2768 -- TCOP_CONTACT_SUMM_PARTY_MERGE created for (AMS_TCOP_CONTACT_SUMMARY)
2770 -- HISTORY
2771 -- 02-Jan-2004 mayjain Created
2772 -----------------------------------------------------------------------
2773 PROCEDURE TCOP_CONTACT_SUMM_PARTY_MERGE
2774 ( p_entity_name IN VARCHAR2
2775 ,p_from_id IN NUMBER
2776 ,p_to_id IN OUT NOCOPY NUMBER
2777 ,p_from_fk_id IN NUMBER
2778 ,p_to_fk_id IN NUMBER
2779 ,p_parent_entity_name IN VARCHAR2
2780 ,p_batch_id IN NUMBER
2781 ,p_batch_party_id IN NUMBER
2782 ,x_return_status IN OUT NOCOPY VARCHAR2
2783 )
2784 is
2785 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2786 l_api_version_number CONSTANT NUMBER := 1.0;
2787 l_merge_reason_code VARCHAR2(30);
2788 l_party_val_flag VARCHAR2(1);
2789 l_temp_num NUMBER;
2790
2791 CURSOR party_cur(to_party NUMBER)
2792 IS
2793 SELECT 1
2794 FROM AMS_TCOP_CONTACT_SUMMARY
2795 WHERE party_id = to_party;
2796
2797 BEGIN
2798
2799
2800 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_CONTACT_SUMM_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2801
2802 x_return_status := FND_API.G_RET_STS_SUCCESS;
2803
2804 select merge_reason_code into l_merge_reason_code
2805 from HZ_MERGE_BATCH
2806 where batch_id = p_batch_id;
2807
2808 IF l_merge_reason_code = 'DUPLICATE' THEN
2809 --***************************************************************************
2810 -- if reason code is duplicate then allow the party merge to happen without
2811 -- any validations.
2812 --***************************************************************************
2813 null;
2814 ELSE
2815 --***************************************************************************
2816 -- if there are any validations to be done, include it in this section
2817 --***************************************************************************
2818 null;
2819 END IF;
2820
2821 --***************************************************************************
2822 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2823 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2824 --***************************************************************************
2825 if p_from_fk_id = p_to_fk_id then
2826 p_to_id := p_from_id;
2827 return;
2828 end if;
2829
2830 --***************************************************************************
2831 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2832 -- dependent record to the new parent. Before transferring check if a similar
2833 -- dependent record exists on the new parent. If a duplicate exists then do
2834 -- not transfer and return the id of the duplicate record as the Merged To Id
2835 --***************************************************************************
2836
2837 --***************************************************************************
2838 -- Add your own logic if you need to take care of the following cases
2839 -- Check the if record duplicate if change party_id from merge-from
2843 -- customer_id address_id contact_id
2840 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2841 -- situation
2842 --
2844 -- =========== ========== ==========
2845 -- 1200 1100
2846 -- 1300 1400
2847 --
2848 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2849 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2850 -- therefore, if changing 1200 to 1300 (customer_id)
2851 -- and 1100 to 1400 (address_id), then it will cause unique
2852 -- key violation assume that all other fields are the same
2853 -- So, please check if you need to check for record duplication
2854 --***************************************************************************
2855
2856 IF p_from_fk_id <> p_to_fk_id THEN
2857 BEGIN
2858 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2859
2860 OPEN party_cur (p_to_fk_id);
2861 FETCH party_cur into l_temp_num;
2862 IF party_cur%FOUND
2863 THEN
2864 l_party_val_flag := 'Y';
2865 ELSE
2866 l_party_val_flag := 'N';
2867 END IF;
2868 CLOSE party_cur;
2869
2870 IF l_party_val_flag = 'Y'
2871 THEN
2872
2873 DELETE FROM AMS_TCOP_CONTACT_SUMMARY
2874 WHERE party_id = p_from_fk_id;
2875
2876 ELSE
2877 UPDATE AMS_TCOP_CONTACT_SUMMARY
2878 SET party_id = p_to_fk_id,
2879 last_update_date = hz_utility_pub.last_update_date,
2880 last_updated_by = hz_utility_pub.user_id,
2881 last_update_login = hz_utility_pub.last_update_login
2882 WHERE party_id = p_from_fk_id;
2883 END IF;
2884
2885 END IF;
2886 EXCEPTION
2887 WHEN OTHERS THEN
2888 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
2889 x_return_status := FND_API.G_RET_STS_ERROR;
2890 raise;
2891 END;
2892 END IF;
2893
2894
2895 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_CONTACT_SUMM_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2896
2897 END TCOP_CONTACT_SUMM_PARTY_MERGE;
2898
2899
2900 -----------------------------------------------------------------------
2901 -- PROCEDURE
2902 -- TCOP_PRVW_CONTACT_PARTY_MERGE created for (AMS_TCOP_PRVW_CONTACTS)
2903 --
2904 -- HISTORY
2905 -- 02-Jan-2004 mayjain Created
2906 -----------------------------------------------------------------------
2907 PROCEDURE TCOP_PRVW_CONTACT_PARTY_MERGE
2908 ( p_entity_name IN VARCHAR2
2909 ,p_from_id IN NUMBER
2910 ,p_to_id IN OUT NOCOPY NUMBER
2911 ,p_from_fk_id IN NUMBER
2912 ,p_to_fk_id IN NUMBER
2913 ,p_parent_entity_name IN VARCHAR2
2914 ,p_batch_id IN NUMBER
2915 ,p_batch_party_id IN NUMBER
2916 ,x_return_status IN OUT NOCOPY VARCHAR2
2917 )
2918 is
2919 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
2920 l_api_version_number CONSTANT NUMBER := 1.0;
2921 l_merge_reason_code VARCHAR2(30);
2922
2923
2924 BEGIN
2925
2926
2927 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_PRVW_CONTACT_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
2928
2929 x_return_status := FND_API.G_RET_STS_SUCCESS;
2930
2931 select merge_reason_code into l_merge_reason_code
2932 from HZ_MERGE_BATCH
2933 where batch_id = p_batch_id;
2934
2935 IF l_merge_reason_code = 'DUPLICATE' THEN
2936 --***************************************************************************
2937 -- if reason code is duplicate then allow the party merge to happen without
2938 -- any validations.
2939 --***************************************************************************
2940 null;
2941 ELSE
2942 --***************************************************************************
2943 -- if there are any validations to be done, include it in this section
2944 --***************************************************************************
2945 null;
2946 END IF;
2947
2948 --***************************************************************************
2949 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
2950 -- needs to be done. Set Merged To Id is same as Merged From Id and return
2951 --***************************************************************************
2952 if p_from_fk_id = p_to_fk_id then
2953 p_to_id := p_from_id;
2954 return;
2955 end if;
2956
2957 --***************************************************************************
2958 -- If the parent has changed(ie. Parent is getting merged) then transfer the
2959 -- dependent record to the new parent. Before transferring check if a similar
2960 -- dependent record exists on the new parent. If a duplicate exists then do
2961 -- not transfer and return the id of the duplicate record as the Merged To Id
2962 --***************************************************************************
2963
2964 --***************************************************************************
2965 -- Add your own logic if you need to take care of the following cases
2966 -- Check the if record duplicate if change party_id from merge-from
2967 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
2968 -- situation
2969 --
2970 -- customer_id address_id contact_id
2971 -- =========== ========== ==========
2972 -- 1200 1100
2973 -- 1300 1400
2974 --
2975 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
2976 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
2977 -- therefore, if changing 1200 to 1300 (customer_id)
2978 -- and 1100 to 1400 (address_id), then it will cause unique
2979 -- key violation assume that all other fields are the same
2980 -- So, please check if you need to check for record duplication
2981 --***************************************************************************
2982
2983 IF p_from_fk_id <> p_to_fk_id THEN
2984 BEGIN
2985 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
2986
2987
2988
2989 UPDATE AMS_TCOP_PRVW_CONTACTS
2993 last_update_login = hz_utility_pub.last_update_login
2990 SET party_id = p_to_fk_id,
2991 last_update_date = hz_utility_pub.last_update_date,
2992 last_updated_by = hz_utility_pub.user_id,
2994 WHERE party_id = p_from_fk_id;
2995
2996 END IF;
2997 EXCEPTION
2998 WHEN OTHERS THEN
2999 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
3000 x_return_status := FND_API.G_RET_STS_ERROR;
3001 raise;
3002 END;
3003 END IF;
3004
3005
3006 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_PRVW_CONTACT_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
3007
3008 END TCOP_PRVW_CONTACT_PARTY_MERGE;
3009
3010
3011
3012 -----------------------------------------------------------------------
3013 -- PROCEDURE
3014 -- TCOP_PRVW_FTG_DTL_PARTY_MERGE created for (AMS_TCOP_PRVW_FTG_DTLS)
3015 --
3016 -- HISTORY
3017 -- 02-Jan-2004 mayjain Created
3018 -----------------------------------------------------------------------
3019 PROCEDURE TCOP_PRVW_FTG_DTL_PARTY_MERGE
3020 ( p_entity_name IN VARCHAR2
3021 ,p_from_id IN NUMBER
3022 ,p_to_id IN OUT NOCOPY NUMBER
3023 ,p_from_fk_id IN NUMBER
3024 ,p_to_fk_id IN NUMBER
3025 ,p_parent_entity_name IN VARCHAR2
3026 ,p_batch_id IN NUMBER
3027 ,p_batch_party_id IN NUMBER
3028 ,x_return_status IN OUT NOCOPY VARCHAR2
3029 )
3030 is
3031 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MERGE';
3032 l_api_version_number CONSTANT NUMBER := 1.0;
3033 l_merge_reason_code VARCHAR2(30);
3034
3035 BEGIN
3036
3037
3038 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_PRVW_FTG_DTL_PARTY_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
3039
3040 x_return_status := FND_API.G_RET_STS_SUCCESS;
3041
3042 select merge_reason_code into l_merge_reason_code
3043 from HZ_MERGE_BATCH
3044 where batch_id = p_batch_id;
3045
3046 IF l_merge_reason_code = 'DUPLICATE' THEN
3047 --***************************************************************************
3048 -- if reason code is duplicate then allow the party merge to happen without
3049 -- any validations.
3050 --***************************************************************************
3051 null;
3052 ELSE
3053 --***************************************************************************
3054 -- if there are any validations to be done, include it in this section
3055 --***************************************************************************
3056 null;
3057 END IF;
3058
3059 --***************************************************************************
3060 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
3061 -- needs to be done. Set Merged To Id is same as Merged From Id and return
3065 return;
3062 --***************************************************************************
3063 if p_from_fk_id = p_to_fk_id then
3064 p_to_id := p_from_id;
3066 end if;
3067
3068 --***************************************************************************
3069 -- If the parent has changed(ie. Parent is getting merged) then transfer the
3070 -- dependent record to the new parent. Before transferring check if a similar
3071 -- dependent record exists on the new parent. If a duplicate exists then do
3072 -- not transfer and return the id of the duplicate record as the Merged To Id
3073 --***************************************************************************
3074
3075 --***************************************************************************
3076 -- Add your own logic if you need to take care of the following cases
3077 -- Check the if record duplicate if change party_id from merge-from
3078 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
3079 -- situation
3080 --
3081 -- customer_id address_id contact_id
3082 -- =========== ========== ==========
3083 -- 1200 1100
3084 -- 1300 1400
3085 --
3086 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
3087 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
3088 -- therefore, if changing 1200 to 1300 (customer_id)
3089 -- and 1100 to 1400 (address_id), then it will cause unique
3090 -- key violation assume that all other fields are the same
3091 -- So, please check if you need to check for record duplication
3092 --***************************************************************************
3093
3094 IF p_from_fk_id <> p_to_fk_id THEN
3095 BEGIN
3096 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
3097
3098 DELETE FROM AMS_TCOP_PRVW_FTG_DTLS
3099 WHERE party_id = p_from_fk_id
3100 and FATIGUE_DETAIL_ID in
3101 (SELECT f.FATIGUE_DETAIL_ID
3102 FROM AMS_TCOP_PRVW_FTG_DTLS f, AMS_TCOP_PRVW_FTG_DTLS t
3103 WHERE f.party_id = p_from_fk_id
3104 AND t.party_id = p_to_fk_id
3105 AND f.PREVIEW_ID = t.PREVIEW_ID);
3106
3107 UPDATE AMS_TCOP_PRVW_FTG_DTLS
3108 SET party_id = p_to_fk_id,
3109 last_update_date = hz_utility_pub.last_update_date,
3110 last_updated_by = hz_utility_pub.user_id,
3111 last_update_login = hz_utility_pub.last_update_login
3112 where party_id = p_from_fk_id
3113 and FATIGUE_DETAIL_ID not in
3114 (SELECT f.FATIGUE_DETAIL_ID
3115 FROM AMS_TCOP_PRVW_FTG_DTLS f, AMS_TCOP_PRVW_FTG_DTLS t
3116 WHERE f.party_id = p_from_fk_id
3117 AND t.party_id = p_to_fk_id
3118 AND f.PREVIEW_ID = t.PREVIEW_ID);
3119 END IF;
3120 EXCEPTION
3121 WHEN OTHERS THEN
3122 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '|| sqlerrm);
3123 x_return_status := FND_API.G_RET_STS_ERROR;
3124 raise;
3125 END;
3126 END IF;
3127
3128
3129 FND_FILE.PUT_LINE(FND_FILE.LOG,'AMS_PARTY_MERGE_PKG.TCOP_PRVW_FTG_DTL_PARTY_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
3130
3131 END TCOP_PRVW_FTG_DTL_PARTY_MERGE;
3132
3133
3134 END AMS_PARTY_MERGE_PVT;
[Home] [Help]