1 PACKAGE BODY JTF_TASK_PARTY_MERGE_PKG as
2 /* $Header: jtftkpmb.pls 120.2 2005/12/23 02:15:35 sbarat ship $ */
3 --/**==================================================================*
4 -- Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --+====================================================================+
7 -- Package body for JTF_TASK_PARTY_MERGE_PKG package
8 --
9 -- Version : 1.0
10 -- Performs a Party Merge for TASKS module.
11 -------------------------------------------------------------------------------------------
12 -- History
13 -------------------------------------------------------------------------------------------
14 -- 16-FEB-01 tivanov Created.
15 -- 30-APR-02 sanjeev changed. added object_version_number clause in the
16 -- updates for bug 2272073
17 -- 17-SEP-03 tsinghal Modified for bug # 3138768 , added jtf_task_utl.check_truncation
18 -- 04-NOV-03 tsinghal Modified for bug # 3138768 , added jtf_task_utl.check_truncation to some more apis
19 -- 23-DEC-05 sbarat Fixed SQL Literal issue for bug# 4614088
20 ---------------------------------------------------------------------------------
21 -- End of comments
22 ------------------------------------------------------------------------------------------
23 -- Procedure: TASK_MERGE_PARTY - Performs party ids merge in JTF_TASKS_B table.
24 -- Columns: CUSTOMER_ID
25 ------------------------------------------------------------------------------------------
26
27 PROCEDURE TASK_MERGE_PARTY(
28 p_entity_name IN VARCHAR2,
29 p_from_id IN NUMBER,
30 x_to_id OUT NOCOPY NUMBER,
31 p_from_fk_id IN NUMBER,
32 p_to_fk_id IN NUMBER,
33 p_parent_entity_name IN VARCHAR2,
34 p_batch_id IN NUMBER,
35 p_batch_party_id IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2) IS
37
38 l_merge_reason_code VARCHAR2(30);
39
40 Cursor c_duplicate Is
41 select merge_reason_code
42 from hz_merge_batch
43 where batch_id = p_batch_id;
44
45
46 BEGIN
47
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 open c_duplicate;
51 fetch c_duplicate into l_merge_reason_code;
52 close c_duplicate;
53
54 if l_merge_reason_code <> 'DUPLICATE' then
55
56 -- if there are any validations to be done, include it in this section
57 -- if reason code is duplicate then allow the party merge to happen without
58 -- any validations.
59
60 null;
61
62 end if;
63
64 -- perform the merge operation
65
66 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
67 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
68
69 if p_from_fk_id = p_to_fk_id then
70
71 x_to_id := p_from_id;
72 return;
73
74 end if;
75
76
77 -- If the parent has changed(ie. Parent is getting merged) then transfer the
78 -- dependent record to the new parent.
79 -- For JTF_TASKS_B table, if party_id 1000 got merged to party_id 2000
80 -- then, we have to update all records with customer_id = 1000 to 2000
81
82 if p_from_fk_id <> p_to_fk_id then
83
84 UPDATE jtf_tasks_b
85 SET customer_id = p_to_fk_id,
86 last_update_date = hz_utility_pub.last_update_date,
87 last_updated_by = hz_utility_pub.user_id,
88 last_update_login = hz_utility_pub.last_update_login,
89 object_version_number = object_version_number + 1
90 WHERE customer_id = p_from_fk_id; -- just to make sure it is the right one
91 end if;
92
93 exception
94 when others then
95
96 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
97 fnd_message.set_token('ERROR' ,SQLERRM);
98 fnd_msg_pub.add;
99 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
100
101
102 END TASK_MERGE_PARTY;
103
104 ------------------------------------------------------------------------------------------
105 -- Procedure: TASK_AUDIT_MERGE_NEW_CUSTOMER
106 -- Performs party ids merge in JTF_TASK_AUDITS_B table.
107 -- Columns: NEW_CUSTOMER_ID
108 ------------------------------------------------------------------------------------------
109
110
111 PROCEDURE TASK_AUDIT_MERGE_NEW_CUSTOMER(
112 p_entity_name IN VARCHAR2,
113 p_from_id IN NUMBER,
114 x_to_id OUT NOCOPY NUMBER,
115 p_from_fk_id IN NUMBER,
116 p_to_fk_id IN NUMBER,
117 p_parent_entity_name IN VARCHAR2,
118 p_batch_id IN NUMBER,
119 p_batch_party_id IN NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2) Is
121
122 l_merge_reason_code VARCHAR2(30);
123
124 Cursor c_duplicate Is
125 select merge_reason_code
126 from hz_merge_batch
127 where batch_id = p_batch_id;
128
129
130 BEGIN
131
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 open c_duplicate;
135 fetch c_duplicate into l_merge_reason_code;
136 close c_duplicate;
137
138 if l_merge_reason_code <> 'DUPLICATE' then
139
140 -- if there are any validations to be done, include it in this section
141 -- if reason code is duplicate then allow the party merge to happen without
142 -- any validations.
143
144 null;
145
146 end if;
147
148 -- perform the merge operation
149
150 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
151 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
152
153 if p_from_fk_id = p_to_fk_id then
154
155 x_to_id := p_from_id;
156 return;
157
158 end if;
159
160
161 -- If the parent has changed(ie. Parent is getting merged) then transfer the
162 -- dependent record to the new parent.
163 -- For JTF_TASK_AUDITS_B table, if party_id 1000 got merged to party_id 2000
164 -- then, we have to update all records with new_customer_id = 1000 to 2000
165
166 if p_from_fk_id <> p_to_fk_id then
167
168 UPDATE jtf_task_audits_b
169 SET new_customer_id = p_to_fk_id,
170 last_update_date = hz_utility_pub.last_update_date,
171 last_updated_by = hz_utility_pub.user_id,
172 last_update_login = hz_utility_pub.last_update_login,
173 object_version_number = object_version_number + 1
174 WHERE new_customer_id = p_from_fk_id; -- just to make sure it is the right one
175 end if;
176
177 exception
178 when others then
179
180 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
181 fnd_message.set_token('ERROR' ,SQLERRM);
182 fnd_msg_pub.add;
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184
185
186 END TASK_AUDIT_MERGE_NEW_CUSTOMER;
187
188 ------------------------------------------------------------------------------------------
189 -- Procedure: TASK_AUDIT_MERGE_OLD_CUSTOMER
190 -- Performs party ids merge in JTF_TASK_AUDITS_B table.
191 -- Columns: OLD_CUSTOMER_ID
192 ------------------------------------------------------------------------------------------
193
194 PROCEDURE TASK_AUDIT_MERGE_OLD_CUSTOMER(
195 p_entity_name IN VARCHAR2,
196 p_from_id IN NUMBER,
197 x_to_id OUT NOCOPY NUMBER,
198 p_from_fk_id IN NUMBER,
199 p_to_fk_id IN NUMBER,
200 p_parent_entity_name IN VARCHAR2,
201 p_batch_id IN NUMBER,
202 p_batch_party_id IN NUMBER,
203 x_return_status OUT NOCOPY VARCHAR2) Is
204
205 l_merge_reason_code VARCHAR2(30);
206
207 Cursor c_duplicate Is
208 select merge_reason_code
209 from hz_merge_batch
210 where batch_id = p_batch_id;
211
212
213 BEGIN
214
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 open c_duplicate;
218 fetch c_duplicate into l_merge_reason_code;
219 close c_duplicate;
220
221 if l_merge_reason_code <> 'DUPLICATE' then
222
223 -- if there are any validations to be done, include it in this section
224 -- if reason code is duplicate then allow the party merge to happen without
225 -- any validations.
226
227 null;
228
229 end if;
230
231 -- perform the merge operation
232
233 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
234 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
235
236 if p_from_fk_id = p_to_fk_id then
237
238 x_to_id := p_from_id;
239 return;
240
241 end if;
242
243
244 -- If the parent has changed(ie. Parent is getting merged) then transfer the
245 -- dependent record to the new parent.
246 -- For JTF_TASK_AUDITS_B table, if party_id 1000 got merged to party_id 2000
247 -- then, we have to update all records with old_customer_id = 1000 to 2000
248
249 if p_from_fk_id <> p_to_fk_id then
250
251 UPDATE jtf_task_audits_b
252 SET old_customer_id = p_to_fk_id,
253 last_update_date = hz_utility_pub.last_update_date,
254 last_updated_by = hz_utility_pub.user_id,
255 last_update_login = hz_utility_pub.last_update_login,
256 object_version_number = object_version_number + 1
257 WHERE old_customer_id = p_from_fk_id; -- just to make sure it is the right one
258 end if;
259
260 exception
261 when others then
262
263 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
264 fnd_message.set_token('ERROR' ,SQLERRM);
265 fnd_msg_pub.add;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267
268 END TASK_AUDIT_MERGE_OLD_CUSTOMER;
269
270 ------------------------------------------------------------------------------------------
271 -- Procedure: TASK_MERGE_SOURCE_OBJECT - Performs party ids merge in JTF_TASKS_B table.
272 -- Columns: SOURCE_OBJECT_ID , SOURCE_OBJECT_NAME
273 ------------------------------------------------------------------------------------------
274
275 PROCEDURE TASK_MERGE_SOURCE_OBJECT(
276 p_entity_name IN VARCHAR2,
277 p_from_id IN NUMBER,
278 x_to_id OUT NOCOPY NUMBER,
279 p_from_fk_id IN NUMBER,
280 p_to_fk_id IN NUMBER,
281 p_parent_entity_name IN VARCHAR2,
282 p_batch_id IN NUMBER,
283 p_batch_party_id IN NUMBER,
284 x_return_status OUT NOCOPY VARCHAR2) IS
285
286 l_merge_reason_code VARCHAR2(30);
287
288 Cursor c_duplicate Is
289 select merge_reason_code
290 from hz_merge_batch
291 where batch_id = p_batch_id;
292
293 --Cursor c_name Is
294 --select o.select_name
295 --from jtf_objects_vl o,
296 -- jtf_tasks_b t
297 --where t.task_id = p_from_id
298 --and t.source_object_type_code = o.object_code
299 --and nvl(start_date_active, sysdate) <= sysdate
300 --and nvl(end_date_active, sysdate) >= sysdate;
301
302 Cursor c_name Is
303 select select_name
304 from jtf_objects_vl
305 where object_code = 'PARTY'
306 and nvl(start_date_active, sysdate) <= sysdate
307 and nvl(end_date_active, sysdate) >= sysdate;
308
309 l_name jtf_objects_vl.select_name%TYPE;
310 l_select_stat varchar2(1000);
311
312
313 BEGIN
314
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316
317 open c_duplicate;
318 fetch c_duplicate into l_merge_reason_code;
319 close c_duplicate;
320
321 if l_merge_reason_code <> 'DUPLICATE' then
322
323 -- if there are any validations to be done, include it in this section
324 -- if reason code is duplicate then allow the party merge to happen without
325 -- any validations.
326
327 null;
328
329 end if;
330
331 -- perform the merge operation
332
333 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
334 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
335
336 if p_from_fk_id = p_to_fk_id then
337
338 x_to_id := p_from_id;
339 return;
340
341 end if;
342
343
344 -- If the parent has changed(ie. Parent is getting merged) then transfer the
345 -- dependent record to the new parent.
346 -- For JTF_TASKS_B table, if party_id 1000 got merged to party_id 2000
347 -- then, we have to update all records with source_object_id = 1000 to 2000
348 -- and also update the source_object_name where source_object_type_code = 'PARTY'
349
350 open c_name;
351 fetch c_name into l_name;
352 close c_name;
353
354 if l_name is not NULL then
355
356 -- Modified by TSINGHAL for bug # 3138768 dt 17/sept/2003 start
357 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
358 l_select_stat :=
359 'UPDATE jtf_tasks_b'
360 || ' SET source_object_id = :1'
361 || ' , source_object_name = (SELECT jtf_task_utl.check_truncation(:2) FROM hz_parties WHERE party_id = :3)'
362 || ' , last_update_date = hz_utility_pub.last_update_date'
363 || ' , last_updated_by = hz_utility_pub.user_id'
364 || ' , last_update_login = hz_utility_pub.last_update_login'
365 || ' , object_version_number = object_version_number + 1'
366 || ' WHERE source_object_id = :4'
367 || ' AND source_object_type_code = ''PARTY''';
368
369 -- Modified by TSINGHAL for bug # 3138768 dt 17/sept/2003 End
370 end if;
371
372 if l_select_stat is not NULL then
373 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
374 EXECUTE IMMEDIATE l_select_stat
375 USING to_char(p_to_fk_id) ,
376 l_name ,
377 to_char(p_to_fk_id) ,
378 to_char(p_from_fk_id);
379 else
380 fnd_message.set_name('JTF', 'JTF_TASK_DYNAMYC_SELECT');
381 fnd_msg_pub.add;
382 raise fnd_api.g_exc_error;
383 end if;
384
385 exception
386 when others then
387
388 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
389 fnd_message.set_token('ERROR' ,SQLERRM);
390 fnd_msg_pub.add;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392
393 END TASK_MERGE_SOURCE_OBJECT;
394
395 ------------------------------------------------------------------------------------------
396 -- Procedure: TASK_AUDIT_MERGE_NEW_S_OBJECT
397 -- Performs party ids merge in JTF_TASK_AUDITS_B table.
398 -- Columns: NEW_SOURCE_OBJECT_ID , NEW_SOURCE_OBJECT_NAME
399 ------------------------------------------------------------------------------------------
400
401 PROCEDURE TASK_AUDIT_MERGE_NEW_S_OBJECT(
402 p_entity_name IN VARCHAR2,
403 p_from_id IN NUMBER,
404 x_to_id OUT NOCOPY NUMBER,
405 p_from_fk_id IN NUMBER,
406 p_to_fk_id IN NUMBER,
407 p_parent_entity_name IN VARCHAR2,
408 p_batch_id IN NUMBER,
409 p_batch_party_id IN NUMBER,
410 x_return_status OUT NOCOPY VARCHAR2) IS
411 l_merge_reason_code VARCHAR2(30);
412
413 Cursor c_duplicate Is
414 select merge_reason_code
415 from hz_merge_batch
416 where batch_id = p_batch_id;
417
418 --Cursor c_name Is
419 --select o.select_name
420 --from jtf_objects_vl o,
421 -- jtf_task_audits_b a
422 --where a.task_audit_id = p_from_id
426
423 --and a.new_source_object_type_code = o.object_code
424 --and nvl(start_date_active, sysdate) <= sysdate
425 --and nvl(end_date_active, sysdate) >= sysdate;
427 Cursor c_name Is
428 select select_name
429 from jtf_objects_vl
430 where object_code = 'PARTY'
431 and nvl(start_date_active, sysdate) <= sysdate
432 and nvl(end_date_active, sysdate) >= sysdate;
433
434 l_name jtf_objects_vl.select_name%TYPE;
435 l_select_stat varchar2(1000);
436
437 BEGIN
438
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 open c_duplicate;
442 fetch c_duplicate into l_merge_reason_code;
443 close c_duplicate;
444
445 if l_merge_reason_code <> 'DUPLICATE' then
446
447 -- if there are any validations to be done, include it in this section
448 -- if reason code is duplicate then allow the party merge to happen without
449 -- any validations.
450
451 null;
452
453 end if;
454
455 -- perform the merge operation
456
457 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
458 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
459
460 if p_from_fk_id = p_to_fk_id then
461
462 x_to_id := p_from_id;
463 return;
464
465 end if;
466
467
468 -- If the parent has changed(ie. Parent is getting merged) then transfer the
469 -- dependent record to the new parent.
470 -- For JTF_TASK_AUDITS_B table, if party_id 1000 got merged to party_id 2000
471 -- then, we have to update all records with new_source_object_id = 1000 to 2000
472 -- and also update the new_source_object_name where new_source_object_type_code = 'PARTY'
473
474 open c_name;
475 fetch c_name into l_name;
476 close c_name;
477
478 if l_name is not NULL then
479 -- Modified by TSINGHAL for bug # 3138768 dt 17/sept/2003 start
480 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
481 l_select_stat := 'UPDATE jtf_task_audits_b '
482 || 'SET new_source_object_id = :1'
483 || ' , new_source_object_name = (SELECT jtf_task_utl.check_truncation(:2) FROM hz_parties WHERE party_id = :3)'
484 || ' , last_update_date = hz_utility_pub.last_update_date'
485 || ' , last_updated_by = hz_utility_pub.user_id'
486 || ' , last_update_login = hz_utility_pub.last_update_login'
487 || ' , object_version_number = object_version_number + 1'
488 || ' WHERE new_source_object_id = :4'
489 || ' AND new_source_object_type_code = ''PARTY''';
490 end if;
491 -- Modified by TSINGHAL for bug # 3138768 dt 17/sept/2003 End
492 if l_select_stat is not NULL then
493 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
494 EXECUTE IMMEDIATE l_select_stat
495 USING to_char(p_to_fk_id) ,
496 l_name ,
497 to_char(p_to_fk_id) ,
498 to_char(p_from_fk_id);
499 else
500 fnd_message.set_name('JTF', 'JTF_TASK_DYNAMYC_SELECT');
501 fnd_msg_pub.add;
502 raise fnd_api.g_exc_error;
503 end if;
504
505 exception
506 when others then
507
508 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
509 fnd_message.set_token('ERROR' ,SQLERRM);
510 fnd_msg_pub.add;
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512
513
514 END TASK_AUDIT_MERGE_NEW_S_OBJECT;
515
516 ------------------------------------------------------------------------------------------
517 -- Procedure: TASK_AUDIT_MERGE_OLD_S_OBJECT
518 -- Performs party ids merge in JTF_TASK_AUDITS_B table.
519 -- Columns: OLD_SOURCE_OBJECT_ID , OLD_SOURCE_OBJECT_NAME
520 ------------------------------------------------------------------------------------------
521
522 PROCEDURE TASK_AUDIT_MERGE_OLD_S_OBJECT(
523 p_entity_name IN VARCHAR2,
524 p_from_id IN NUMBER,
525 x_to_id OUT NOCOPY NUMBER,
526 p_from_fk_id IN NUMBER,
527 p_to_fk_id IN NUMBER,
528 p_parent_entity_name IN VARCHAR2,
529 p_batch_id IN NUMBER,
530 p_batch_party_id IN NUMBER,
531 x_return_status OUT NOCOPY VARCHAR2) IS
532
533 l_merge_reason_code VARCHAR2(30);
534
535 Cursor c_duplicate Is
536 select merge_reason_code
537 from hz_merge_batch
538 where batch_id = p_batch_id;
539
540 --Cursor c_name Is
541 --select o.select_name
542 --from jtf_objects_vl o,
543 -- jtf_task_audits_b a
544 --where a.task_audit_id = p_from_id
545 --and a.old_source_object_type_code = o.object_code
546 --and nvl(start_date_active, sysdate) <= sysdate
547 --and nvl(end_date_active, sysdate) >= sysdate;
548
549 Cursor c_name Is
550 select select_name
551 from jtf_objects_vl
552 where object_code = 'PARTY'
553 and nvl(start_date_active, sysdate) <= sysdate
554 and nvl(end_date_active, sysdate) >= sysdate;
555
556 l_name jtf_objects_vl.select_name%TYPE;
557 l_select_stat varchar2(1000);
558
559 BEGIN
560
564 fetch c_duplicate into l_merge_reason_code;
561 x_return_status := FND_API.G_RET_STS_SUCCESS;
562
563 open c_duplicate;
565 close c_duplicate;
566
567 if l_merge_reason_code <> 'DUPLICATE' then
568
569 -- if there are any validations to be done, include it in this section
570 -- if reason code is duplicate then allow the party merge to happen without
571 -- any validations.
572
573 null;
574
575 end if;
576
577 -- perform the merge operation
578
579 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
580 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
581
582 if p_from_fk_id = p_to_fk_id then
583
584 x_to_id := p_from_id;
585 return;
586
587 end if;
588
589
590 -- If the parent has changed(ie. Parent is getting merged) then transfer the
591 -- dependent record to the new parent.
592 -- For JTF_TASK_AUDITS_B table, if party_id 1000 got merged to party_id 2000
593 -- then, we have to update all records with old_source_object_id = 1000 to 2000
594 -- and also update the old_source_object_name where old_source_object_type_code = 'PARTY'
595
596
597 open c_name;
598 fetch c_name into l_name;
599 close c_name;
600
601 if l_name is not NULL then
602 -- Modified by TSINGHAL for bug # 3138768 dt 01/Oct/2003 Start
603 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
604 l_select_stat := 'UPDATE jtf_task_audits_b'
605 || ' SET old_source_object_id = :1'
606 || ' , old_source_object_name = (SELECT jtf_task_utl.check_truncation(:2) FROM hz_parties WHERE party_id = :3 )'
607 || ' , last_update_date = hz_utility_pub.last_update_date'
608 || ' , last_updated_by = hz_utility_pub.user_id'
609 || ' , last_update_login = hz_utility_pub.last_update_login'
610 || ' , object_version_number = object_version_number + 1'
611 || ' WHERE old_source_object_id = :4'
612 || ' AND old_source_object_type_code = ''PARTY''';
613 -- Modified by TSINGHAL for bug # 3138768 dt 01/Oct/2003 End
614 end if;
615
616 if l_select_stat is not NULL then
617 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
618 EXECUTE IMMEDIATE l_select_stat
619 USING to_char(p_to_fk_id) ,
620 l_name ,
621 to_char(p_to_fk_id) ,
622 to_char(p_from_fk_id);
623 else
624 fnd_message.set_name('JTF', 'JTF_TASK_DYNAMYC_SELECT');
625 fnd_msg_pub.add;
626 raise fnd_api.g_exc_error;
627 end if;
628
629 exception
630 when others then
631
632 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
633 fnd_message.set_token('ERROR' ,SQLERRM);
634 fnd_msg_pub.add;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636
637
638 END TASK_AUDIT_MERGE_OLD_S_OBJECT;
639
640
641 ------------------------------------------------------------------------------------------
642 -- Procedure: TASK_REF_MERGE_PARTY_OBJECT
643 -- Performs party ids merge in JTF_TASK_REFERENCES_B table for objects of type -- 'PARTY'.
644 -- Columns: OBJECT_ID , OBJECT_NAME
645 ------------------------------------------------------------------------------------------
646
647
648 PROCEDURE TASK_REF_MERGE_PARTY_OBJECT(
649 p_entity_name IN VARCHAR2,
650 p_from_id IN NUMBER,
651 x_to_id OUT NOCOPY NUMBER,
652 p_from_fk_id IN NUMBER,
653 p_to_fk_id IN NUMBER,
654 p_parent_entity_name IN VARCHAR2,
655 p_batch_id IN NUMBER,
656 p_batch_party_id IN NUMBER,
657 x_return_status OUT NOCOPY VARCHAR2) IS
658 l_merge_reason_code VARCHAR2(30);
659
660 Cursor c_duplicate Is
661 select merge_reason_code
662 from hz_merge_batch
663 where batch_id = p_batch_id;
664
665 --Cursor c_name Is
666 --select o.select_name
667 --from jtf_objects_vl o,
668 -- jtf_task_references_b r
669 --where r.task_reference_id = p_from_id
670 --and r.object_type_code = o.object_code
671 --and nvl(start_date_active, sysdate) <= sysdate
672 --and nvl(end_date_active, sysdate) >= sysdate;
673
674 Cursor c_name Is
675 select select_name
676 from jtf_objects_vl
677 where object_code = 'PARTY'
678 and nvl(start_date_active, sysdate) <= sysdate
679 and nvl(end_date_active, sysdate) >= sysdate;
680
681 l_name jtf_objects_vl.select_name%TYPE;
682 l_select_stat varchar2(1000);
683
684
685 BEGIN
686
687 x_return_status := FND_API.G_RET_STS_SUCCESS;
688
689 open c_duplicate;
690 fetch c_duplicate into l_merge_reason_code;
691 close c_duplicate;
692
693 if l_merge_reason_code <> 'DUPLICATE' then
694
695 -- if there are any validations to be done, include it in this section
696 -- if reason code is duplicate then allow the party merge to happen without
697 -- any validations.
698
699 null;
700
701 end if;
702
706 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
703 -- perform the merge operation
704
705 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
707
708 if p_from_fk_id = p_to_fk_id then
709
710 x_to_id := p_from_id;
711 return;
712
713 end if;
714
715
716 -- If the parent has changed(ie. Parent is getting merged) then transfer the
717 -- dependent record to the new parent.
718 -- For JTF_TASK_REFERENCES_B table, if party_id 1000 got merged to party_id 2000
719 -- then, we have to update all records with object_id = 1000 to 2000 ,
720 -- and also update the object_name where object_type_code = 'PARTY'
721
722
723 open c_name;
724 fetch c_name into l_name;
725 close c_name;
726
727 if l_name is not NULL then
728 /* Modified by tsinghal 4th Nov '03 bug # 3138768 Start */
729 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
730 l_select_stat := 'UPDATE jtf_task_references_b'
731 || ' SET object_id = :1'
732 || ' , object_name = (SELECT jtf_task_utl.check_truncation(:2) FROM hz_parties WHERE party_id = :3 )'
733 || ' , last_update_date = hz_utility_pub.last_update_date'
734 || ' , last_updated_by = hz_utility_pub.user_id'
735 || ' , last_update_login = hz_utility_pub.last_update_login'
736 || ' , object_version_number = object_version_number + 1'
737 || ' WHERE object_id = :4'
738 || ' AND object_type_code = ''PARTY''';
739 end if;
740 /* Modified by tsinghal 4th Nov '03 bug # 3138768 End */
741 if l_select_stat is not NULL then
742 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
743 EXECUTE IMMEDIATE l_select_stat
744 USING to_char(p_to_fk_id) ,
745 l_name ,
746 to_char(p_to_fk_id) ,
747 to_char(p_from_fk_id);
748
749 else
750 fnd_message.set_name('JTF', 'JTF_TASK_DYNAMYC_SELECT');
751 fnd_msg_pub.add;
752 raise fnd_api.g_exc_error;
753 end if;
754
755 exception
756 when others then
757
758 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
759 fnd_message.set_token('ERROR' ,SQLERRM);
760 fnd_msg_pub.add;
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
762
763 END TASK_REF_MERGE_PARTY_OBJECT;
764
765 ------------------------------------------------------------------------------------------
766 -- Procedure: TASK_REF_MERGE_PSITE_OBJECT
767 -- Performs party ids merge in JTF_TASK_REFERENCES_B table for objects of type -- 'PARTY_SITE'.
768 -- Columns: OBJECT_ID , OBJECT_NAME
769 ------------------------------------------------------------------------------------------
770
771
772 PROCEDURE TASK_REF_MERGE_PSITE_OBJECT(
773 p_entity_name IN VARCHAR2,
774 p_from_id IN NUMBER,
775 x_to_id OUT NOCOPY NUMBER,
776 p_from_fk_id IN NUMBER,
777 p_to_fk_id IN NUMBER,
778 p_parent_entity_name IN VARCHAR2,
779 p_batch_id IN NUMBER,
780 p_batch_party_id IN NUMBER,
781 x_return_status OUT NOCOPY VARCHAR2) IS
782 l_merge_reason_code VARCHAR2(30);
783
784 Cursor c_duplicate Is
785 select merge_reason_code
786 from hz_merge_batch
787 where batch_id = p_batch_id;
788
789 --Cursor c_name Is
790 --select o.select_name
791 --from jtf_objects_vl o,
792 -- jtf_task_references_b r
793 --where r.task_reference_id = p_from_id
794 --and r.object_type_code = o.object_code
795 --and nvl(start_date_active, sysdate) <= sysdate
796 --and nvl(end_date_active, sysdate) >= sysdate;
797
798 Cursor c_name Is
799 select select_name
800 from jtf_objects_vl
801 where object_code = 'SITE'
802 and nvl(start_date_active, sysdate) <= sysdate
803 and nvl(end_date_active, sysdate) >= sysdate;
804
805 l_name jtf_objects_vl.select_name%TYPE;
806 l_select_stat varchar2(1000);
807
808
809 BEGIN
810
811 x_return_status := FND_API.G_RET_STS_SUCCESS;
812
813 open c_duplicate;
814 fetch c_duplicate into l_merge_reason_code;
815 close c_duplicate;
816
817
818 if l_merge_reason_code <> 'DUPLICATE' then
819
820 -- if there are any validations to be done, include it in this section
821 -- if reason code is duplicate then allow the party merge to happen without
822 -- any validations.
823
824 null;
825
826 end if;
827
828 -- perform the merge operation
829
830 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
831 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
832
833
834 if p_from_fk_id = p_to_fk_id then
835
836 x_to_id := p_from_id;
837 return;
838
839 end if;
840
841
842 -- If the parent has changed(ie. Parent is getting merged) then transfer the
843 -- dependent record to the new parent.
847
844 -- For JTF_TASK_REFERENCES_B table, if party_id 1000 got merged to party_id 2000
845 -- then, we have to update all records with object_id = 1000 to 2000 ,
846 -- and also update the object_name where object_type_code = 'PARTY'
848
849
850 open c_name;
851 fetch c_name into l_name;
852 close c_name;
853
854 if l_name is not NULL then
855 /* Modified by tsinghal 4th Nov '03 bug # 3138768 Start */
856 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
857 l_select_stat := 'UPDATE jtf_task_references_b'
858 || ' SET object_id = :1'
859 || ' , object_name = (SELECT jtf_task_utl.check_truncation(:2) FROM hz_party_sites WHERE party_site_id = :3 )'
860 || ' , last_update_date = hz_utility_pub.last_update_date'
861 || ' , last_updated_by = hz_utility_pub.user_id'
862 || ' , last_update_login = hz_utility_pub.last_update_login'
863 || ' , object_version_number = object_version_number + 1'
864 || ' WHERE object_id = :4'
865 || ' AND object_type_code = ''SITE''';
866 end if;
867 /* Modified by tsinghal 4th Nov '03 bug # 3138768 End */
868 if l_select_stat is not NULL then
869 -- Modified by SBARAT on 23/12/2005 for SQL Literal issue, bug# 4614088
870 EXECUTE IMMEDIATE l_select_stat
871 USING to_char(p_to_fk_id) ,
872 l_name ,
873 to_char(p_to_fk_id) ,
874 to_char(p_from_fk_id);
875 else
876 fnd_message.set_name('JTF', 'JTF_TASK_DYNAMYC_SELECT');
877 fnd_msg_pub.add;
878 raise fnd_api.g_exc_error;
879 end if;
880
881 exception
882 when others then
883
884 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
885 fnd_message.set_token('ERROR' ,SQLERRM);
886 fnd_msg_pub.add;
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888
889 END TASK_REF_MERGE_PSITE_OBJECT;
890
891
892
893 ------------------------------------------------------------------------------------------
894 -- Procedure: TASK_MERGE_ADDRESS
895 -- Performs party_site merge in JTF_TASKS_B table.
896 -- Columns: ADDRESS_ID
897 ------------------------------------------------------------------------------------------
898
899 PROCEDURE TASK_MERGE_ADDRESS(
900 p_entity_name IN VARCHAR2,
901 p_from_id IN NUMBER,
902 x_to_id OUT NOCOPY NUMBER,
903 p_from_fk_id IN NUMBER,
904 p_to_fk_id IN NUMBER,
905 p_parent_entity_name IN VARCHAR2,
906 p_batch_id IN NUMBER,
907 p_batch_party_id IN NUMBER,
908 x_return_status OUT NOCOPY VARCHAR2) IS
909
910 l_merge_reason_code VARCHAR2(30);
911
912 Cursor c_duplicate Is
913 select merge_reason_code
914 from hz_merge_batch
915 where batch_id = p_batch_id;
916
917
918 BEGIN
919
920 x_return_status := FND_API.G_RET_STS_SUCCESS;
921
922 open c_duplicate;
923 fetch c_duplicate into l_merge_reason_code;
924 close c_duplicate;
925
926 if l_merge_reason_code <> 'DUPLICATE' then
927
928 -- if there are any validations to be done, include it in this section
929 -- if reason code is duplicate then allow the party merge to happen without
930 -- any validations.
931
932 null;
933
934 end if;
935
936 -- perform the merge operation
937
938 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
939 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
940 -- If the party_site has been transferred then nothing should be done.
941
942 if p_from_fk_id = p_to_fk_id then
943
944 x_to_id := p_from_id;
945 return;
946
947 end if;
948
949
950 -- If the parent has changed(ie. Parent is getting merged) then transfer the
951 -- dependent record to the new parent.
952 -- For JTF_TASKS_B table, if party_site_id 1111 got merged to party_site_id 2222
953 -- then, we have to update all records with address_id = 1111 to 2222
954
955 if p_from_fk_id <> p_to_fk_id then
956
957 UPDATE jtf_tasks_b
958 SET address_id = p_to_fk_id,
959 last_update_date = hz_utility_pub.last_update_date,
960 last_updated_by = hz_utility_pub.user_id,
961 last_update_login = hz_utility_pub.last_update_login,
962 object_version_number = object_version_number + 1
963 WHERE --task_id = p_from_id
964 --AND
965 address_id = p_from_fk_id; -- just to make sure it is the right one
966 end if;
967
968 exception
969 when others then
970
971 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
972 fnd_message.set_token('ERROR' ,SQLERRM);
973 fnd_msg_pub.add;
974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
975
976 END TASK_MERGE_ADDRESS;
977
978 ------------------------------------------------------------------------------------------
979 -- Procedure: TASK_AUDIT_MERGE_NEW_ADDRESS
980 -- Performs party_site merge in JTF_TASK_AUDITS_B table.
981 -- Columns: NEW_ADDRESS_ID
985 p_entity_name IN VARCHAR2,
982 ------------------------------------------------------------------------------------------
983
984 PROCEDURE TASK_AUDIT_MERGE_NEW_ADDRESS(
986 p_from_id IN NUMBER,
987 x_to_id OUT NOCOPY NUMBER,
988 p_from_fk_id IN NUMBER,
989 p_to_fk_id IN NUMBER,
990 p_parent_entity_name IN VARCHAR2,
991 p_batch_id IN NUMBER,
992 p_batch_party_id IN NUMBER,
993 x_return_status OUT NOCOPY VARCHAR2) IS
994
995 l_merge_reason_code VARCHAR2(30);
996
997 Cursor c_duplicate Is
998 select merge_reason_code
999 from hz_merge_batch
1000 where batch_id = p_batch_id;
1001
1002
1003 BEGIN
1004
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006
1007 open c_duplicate;
1008 fetch c_duplicate into l_merge_reason_code;
1009 close c_duplicate;
1010
1011 if l_merge_reason_code <> 'DUPLICATE' then
1012
1013 -- if there are any validations to be done, include it in this section
1014 -- if reason code is duplicate then allow the party merge to happen without
1015 -- any validations.
1016
1017 null;
1018
1019 end if;
1020
1021 -- perform the merge operation
1022
1023 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1024 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1025 -- If the party_site has been transferred then nothing should be done.
1026
1027 if p_from_fk_id = p_to_fk_id then
1028
1029 x_to_id := p_from_id;
1030 return;
1031
1032 end if;
1033
1034
1035 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1036 -- dependent record to the new parent.
1037 -- For JTF_TASK_AUDITS_B table, if party_site_id 1111 got merged to party_site_id 2222
1038 -- then, we have to update all records with new_address_id = 1111 to 2222
1039
1040 if p_from_fk_id <> p_to_fk_id then
1041
1042 UPDATE jtf_task_audits_b
1043 SET new_address_id = p_to_fk_id,
1044 last_update_date = hz_utility_pub.last_update_date,
1045 last_updated_by = hz_utility_pub.user_id,
1046 last_update_login = hz_utility_pub.last_update_login,
1047 object_version_number = object_version_number + 1
1048 WHERE --task_audit_id = p_from_id
1049 --AND
1050 new_address_id = p_from_fk_id; -- just to make sure it is the right one
1051 end if;
1052
1053 exception
1054 when others then
1055
1056 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1057 fnd_message.set_token('ERROR' ,SQLERRM);
1058 fnd_msg_pub.add;
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060
1061 END TASK_AUDIT_MERGE_NEW_ADDRESS;
1062
1063 ------------------------------------------------------------------------------------------
1064 -- Procedure: TASK_AUDIT_MERGE_OLD_ADDRESS
1065 -- Performs party_site merge in JTF_TASK_AUDITS_B table.
1066 -- Columns: OLD_ADDRESS_ID
1067 ------------------------------------------------------------------------------------------
1068
1069 PROCEDURE TASK_AUDIT_MERGE_OLD_ADDRESS(
1070 p_entity_name IN VARCHAR2,
1071 p_from_id IN NUMBER,
1072 x_to_id OUT NOCOPY NUMBER,
1073 p_from_fk_id IN NUMBER,
1074 p_to_fk_id IN NUMBER,
1075 p_parent_entity_name IN VARCHAR2,
1076 p_batch_id IN NUMBER,
1077 p_batch_party_id IN NUMBER,
1078 x_return_status OUT NOCOPY VARCHAR2) IS
1079
1080 l_merge_reason_code VARCHAR2(30);
1081
1082 Cursor c_duplicate Is
1083 select merge_reason_code
1084 from hz_merge_batch
1085 where batch_id = p_batch_id;
1086
1087
1088 BEGIN
1089
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091
1092 open c_duplicate;
1093 fetch c_duplicate into l_merge_reason_code;
1094 close c_duplicate;
1095
1096 if l_merge_reason_code <> 'DUPLICATE' then
1097
1098 -- if there are any validations to be done, include it in this section
1099 -- if reason code is duplicate then allow the party merge to happen without
1100 -- any validations.
1101
1102 null;
1103
1104 end if;
1105
1106 -- perform the merge operation
1107
1108 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1109 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1110 -- If the party_site has been transferred then nothing should be done.
1111
1112 if p_from_fk_id = p_to_fk_id then
1113
1114 x_to_id := p_from_id;
1115 return;
1116
1117 end if;
1118
1119
1120 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1121 -- dependent record to the new parent.
1122 -- For JTF_TASK_AUDITS_B table, if party_site_id 1111 got merged to party_site_id 2222
1123 -- then, we have to update all records with old_address_id = 1111 to 2222
1124
1125 if p_from_fk_id <> p_to_fk_id then
1126
1127 UPDATE jtf_task_audits_b
1128 SET old_address_id = p_to_fk_id,
1132 object_version_number = object_version_number + 1
1129 last_update_date = hz_utility_pub.last_update_date,
1130 last_updated_by = hz_utility_pub.user_id,
1131 last_update_login = hz_utility_pub.last_update_login,
1133 WHERE --task_audit_id = p_from_id
1134 --AND
1135 old_address_id = p_from_fk_id; -- just to make sure it is the right one
1136 end if;
1137
1138 exception
1139 when others then
1140
1141 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1142 fnd_message.set_token('ERROR' ,SQLERRM);
1143 fnd_msg_pub.add;
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1145
1146 END TASK_AUDIT_MERGE_OLD_ADDRESS;
1147
1148 ------------------------------------------------------------------------------------------
1149 -- Procedure: TASK_MERGE_CONTACTS
1150 -- Performs party_id merge in JTF_TASK_CONTACTS table.
1151 -- Columns: CONTACT_ID where CONTACT_TYPE_CODE = 'CUST'
1152 ------------------------------------------------------------------------------------------
1153
1154 PROCEDURE TASK_MERGE_CONTACTS(
1155 p_entity_name IN VARCHAR2,
1156 p_from_id IN NUMBER,
1157 x_to_id OUT NOCOPY NUMBER,
1158 p_from_fk_id IN NUMBER,
1159 p_to_fk_id IN NUMBER,
1160 p_parent_entity_name IN VARCHAR2,
1161 p_batch_id IN NUMBER,
1162 p_batch_party_id IN NUMBER,
1163 x_return_status OUT NOCOPY VARCHAR2) Is
1164
1165 l_merge_reason_code VARCHAR2(30);
1166
1167 Cursor c_duplicate Is
1168 select merge_reason_code
1169 from hz_merge_batch
1170 where batch_id = p_batch_id;
1171
1172 BEGIN
1173
1174 x_return_status := FND_API.G_RET_STS_SUCCESS;
1175
1176 open c_duplicate;
1177 fetch c_duplicate into l_merge_reason_code;
1178 close c_duplicate;
1179
1180 if l_merge_reason_code <> 'DUPLICATE' then
1181
1182 -- if there are any validations to be done, include it in this section
1183 -- if reason code is duplicate then allow the party merge to happen without
1184 -- any validations.
1185
1186 null;
1187
1188 end if;
1189
1190 -- perform the merge operation
1191
1192 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1193 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1194
1195 if p_from_fk_id = p_to_fk_id then
1196
1197 x_to_id := p_from_id;
1198 return;
1199
1200 end if;
1201
1202
1203 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1204 -- dependent record to the new parent.
1205 -- For JTF_TASK_CONTACTS table, if party_id 1000 got merged to party_id 2000
1206 -- then, we have to update all records with contact_id = 1000 to 2000
1207 -- for contacts of type 'Customer' - contact_type_code = 'CUST'
1208
1209 if p_from_fk_id <> p_to_fk_id then
1210
1211 UPDATE jtf_task_contacts
1212 SET contact_id = p_to_fk_id,
1213 last_update_date = hz_utility_pub.last_update_date,
1214 last_updated_by = hz_utility_pub.user_id,
1215 last_update_login = hz_utility_pub.last_update_login,
1216 object_version_number = object_version_number + 1
1217 WHERE --task_contact_id = p_from_id
1218 contact_type_code = 'CUST'
1219 AND contact_id = p_from_fk_id; --just to make sure it is the right one
1220
1221 end if;
1222
1223 exception
1224 when others then
1225
1226 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1227 fnd_message.set_token('ERROR' ,SQLERRM);
1228 fnd_msg_pub.add;
1229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1230
1231 END TASK_MERGE_CONTACTS;
1232
1233
1234 ------------------------------------------------------------------------------------------
1235 -- Procedure: TASK_MERGE_CONTACT_POINTS
1236 -- Performs contact_point_id merge in JTF_TASK_PHONES table.
1237 -- Columns: PHONE_ID
1238 ------------------------------------------------------------------------------------------
1239
1240 PROCEDURE TASK_MERGE_CONTACT_POINTS(
1241 p_entity_name IN VARCHAR2,
1242 p_from_id IN NUMBER,
1243 x_to_id OUT NOCOPY NUMBER,
1244 p_from_fk_id IN NUMBER,
1245 p_to_fk_id IN NUMBER,
1246 p_parent_entity_name IN VARCHAR2,
1247 p_batch_id IN NUMBER,
1248 p_batch_party_id IN NUMBER,
1249 x_return_status OUT NOCOPY VARCHAR2) Is
1250
1251 l_merge_reason_code VARCHAR2(30);
1252
1253 Cursor c_duplicate Is
1254 select merge_reason_code
1255 from hz_merge_batch
1256 where batch_id = p_batch_id;
1257
1258 BEGIN
1259
1260 x_return_status := FND_API.G_RET_STS_SUCCESS;
1261
1262 open c_duplicate;
1263 fetch c_duplicate into l_merge_reason_code;
1264 close c_duplicate;
1265
1266 if l_merge_reason_code <> 'DUPLICATE' then
1267
1268 -- if there are any validations to be done, include it in this section
1269 -- if reason code is duplicate then allow the party merge to happen without
1270 -- any validations.
1271
1272 null;
1273
1277
1274 end if;
1275
1276 -- perform the merge operation
1278 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1279 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1280
1281 if p_from_fk_id = p_to_fk_id then
1282
1283 x_to_id := p_from_id;
1284 return;
1285
1286 end if;
1287
1288
1289 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1290 -- dependent record to the new parent.
1291 -- For JTF_TASK_PHONES table, if contact_point_id 1000 got merged to contact_point_id
1292 -- 2000 then, we have to update all records with phone_id = 1000 to 2000
1293
1294 if p_from_fk_id <> p_to_fk_id then
1295
1296 UPDATE jtf_task_phones
1297 SET phone_id = p_to_fk_id,
1298 last_update_date = hz_utility_pub.last_update_date,
1299 last_updated_by = hz_utility_pub.user_id,
1300 last_update_login = hz_utility_pub.last_update_login,
1301 object_version_number = object_version_number + 1
1302 WHERE --task_phone_id = p_from_id
1303 --AND
1304 phone_id = p_from_fk_id; --just to make sure it is the right one
1305
1306 end if;
1307
1308
1309 exception
1310 when others then
1311
1312 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1313 fnd_message.set_token('ERROR' ,SQLERRM);
1314 fnd_msg_pub.add;
1315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1316
1317 END TASK_MERGE_CONTACT_POINTS;
1318
1319
1320 ------------------------------------------------------------------------------------------
1321 -- Procedure: SEARCH_MERGE_NUMBER_PARTY_ID - Performs party ids merge in JTF_PERZ_QUERY_PARAM table for Customer Number saved searches.
1322 -- Columns: Updates PARAMETER_VALUE where PARAMETER_NAME='CUSTOMER_ID'
1323 -- Parameters: p_from_id = jtf_perz_query_param.query_param_id
1324 ------------------------------------------------------------------------------------------
1325
1326 PROCEDURE SEARCH_MERGE_NUMBER_PARTY_ID(
1327 p_entity_name IN VARCHAR2,
1328 p_from_id IN NUMBER,
1329 x_to_id OUT NOCOPY NUMBER,
1330 p_from_fk_id IN NUMBER,
1331 p_to_fk_id IN NUMBER,
1332 p_parent_entity_name IN VARCHAR2,
1333 p_batch_id IN NUMBER,
1334 p_batch_party_id IN NUMBER,
1335 x_return_status OUT NOCOPY VARCHAR2) IS
1336
1337 l_merge_reason_code VARCHAR2(30);
1338
1339 Cursor c_duplicate Is
1340 select merge_reason_code
1341 from hz_merge_batch
1342 where batch_id = p_batch_id;
1343
1344
1345
1346 -- get CUSTOMER_NAME's query_param_id
1347
1348 --cursor get_query_customer_name(p_from_id NUMBER) Is
1349 --SELECT p2.query_param_id
1350 --FROM jtf_perz_query_param p1,
1351 -- jtf_perz_query_param p2
1352 --WHERE p1.query_param_id = p_from_id
1353 --AND p1.query_id = p2.query_id
1354 --AND p2.parameter_name = 'CUSTOMER_NAME';
1355
1356 -- get Customer new Name - using the p_to_fk_id
1357
1358 cursor get_new_customer_number(p_to_fk_id NUMBER) Is
1359 SELECT party_number
1360 FROM hz_parties
1361 WHERE party_id = p_to_fk_id;
1362
1363 --l_query_customer_name_id jtf_perz_query_param.query_param_id%TYPE;
1364 l_customer_new_number hz_parties.party_number%TYPE;
1365
1366 BEGIN
1367
1368 x_return_status := FND_API.G_RET_STS_SUCCESS;
1369
1370 open c_duplicate;
1371 fetch c_duplicate into l_merge_reason_code;
1372 close c_duplicate;
1373
1374 if l_merge_reason_code <> 'DUPLICATE' then
1375
1376 -- if there are any validations to be done, include it in this section
1377 -- if reason code is duplicate then allow the party merge to happen without
1378 -- any validations.
1379
1380 null;
1381
1382 end if;
1383
1384 -- perform the merge operation
1385
1386 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1387 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1388
1389 if p_from_fk_id = p_to_fk_id then
1390
1391 x_to_id := p_from_id;
1392 return;
1393
1394 end if;
1395
1396
1397 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1398 -- dependent record to the new parent.
1399
1400 if p_from_fk_id <> p_to_fk_id then
1401
1402 --open get_query_customer_name(p_from_id);
1403 --fetch get_query_customer_name into l_query_customer_name_id;
1404 --close get_query_customer_name;
1405
1406 --open get_new_customer_name(p_to_fk_id);
1407 open get_new_customer_number(p_to_fk_id);
1408 fetch get_new_customer_number into l_customer_new_number;
1409 close get_new_customer_number;
1410
1411 if --(SQL%ROWCOUNT > 0)
1412 --and
1413 (l_customer_new_number is not NULL)
1414 --and (l_query_customer_name_id is not NULL)
1415 then
1416
1417 UPDATE jtf_perz_query_param
1418 -- Fix bug 3738509
1419 SET parameter_value = l_customer_new_number,
1420 last_update_date = hz_utility_pub.last_update_date,
1421 last_updated_by = hz_utility_pub.user_id,
1425 FROM jtf_perz_query_param param
1422 last_update_login = hz_utility_pub.last_update_login
1423 WHERE --query_param_id = l_query_customer_name_id
1424 query_param_id IN (SELECT param.query_param_id
1426 , (SELECT q.query_id
1427 FROM jtf_perz_query q
1428 , jtf_perz_query_param p
1429 WHERE q.query_type = 'JTF_TASK'
1430 AND q.application_id = 690
1431 AND p.query_id = q.query_id
1432 AND p.parameter_name = 'CUSTOMER'
1433 AND p.parameter_value = 'NUMBER') query
1434 WHERE param.query_id = query.query_id
1435 AND param.parameter_name = 'CUSTOMER_NAME'
1436 AND EXISTS (SELECT 1
1437 FROM jtf_perz_query_param pm
1438 WHERE pm.query_id = param.query_id
1439 AND pm.parameter_name = 'CUSTOMER_ID'
1440 AND pm.parameter_value = to_char(p_from_fk_id))
1441 );
1442
1443 end if;
1444
1445 UPDATE jtf_perz_query_param p
1446 SET parameter_value = to_char(p_to_fk_id),
1447 last_update_date = hz_utility_pub.last_update_date,
1448 last_updated_by = hz_utility_pub.user_id,
1449 last_update_login = hz_utility_pub.last_update_login
1450 WHERE --query_param_id = p_from_id
1451 query_param_id IN (SELECT param.query_param_id
1452 FROM jtf_perz_query_param param
1453 , (SELECT q.query_id
1454 FROM jtf_perz_query q
1455 , jtf_perz_query_param p
1456 WHERE q.query_type = 'JTF_TASK'
1457 AND q.application_id = 690
1458 AND p.query_id = q.query_id
1459 AND p.parameter_name = 'CUSTOMER'
1460 AND p.parameter_value = 'NUMBER') query
1461 WHERE param.query_id = query.query_id
1462 AND param.parameter_name = 'CUSTOMER_ID'
1463 AND param.parameter_value = to_char(p_from_fk_id));
1464
1465
1466 end if;
1467
1468 exception
1469 when others then
1470
1471 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1472 fnd_message.set_token('ERROR' ,SQLERRM);
1473 fnd_msg_pub.add;
1474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1475
1476
1477 END SEARCH_MERGE_NUMBER_PARTY_ID;
1478
1479
1480 ------------------------------------------------------------------------------------------
1481 -- Procedure: SEARCH_MERGE_NAME_PARTY_ID - Performs party ids merge in JTF_PERZ_QUERY_PARAM table for Customer Name saved searches.
1482 -- Columns: Updates PARAMETER_VALUE where PARAMETER_NAME='CUSTOMER_ID'
1483 -- Parameters: p_from_id = jtf_perz_query_param.query_param_id
1484 ------------------------------------------------------------------------------------------
1485
1486 PROCEDURE SEARCH_MERGE_NAME_PARTY_ID(
1487 p_entity_name IN VARCHAR2,
1488 p_from_id IN NUMBER,
1489 x_to_id OUT NOCOPY NUMBER,
1490 p_from_fk_id IN NUMBER,
1491 p_to_fk_id IN NUMBER,
1492 p_parent_entity_name IN VARCHAR2,
1493 p_batch_id IN NUMBER,
1494 p_batch_party_id IN NUMBER,
1495 x_return_status OUT NOCOPY VARCHAR2) IS
1496
1497 l_merge_reason_code VARCHAR2(30);
1498
1499 Cursor c_duplicate Is
1500 select merge_reason_code
1501 from hz_merge_batch
1502 where batch_id = p_batch_id;
1503
1504
1505
1506 -- get CUSTOMER_NAME's query_param_id
1507
1508 --cursor get_query_customer_name(p_from_id NUMBER) Is
1509 --SELECT p2.query_param_id
1510 --FROM jtf_perz_query_param p1,
1511 -- jtf_perz_query_param p2
1512 --WHERE p1.query_param_id = p_from_id
1513 --AND p1.query_id = p2.query_id
1514 --AND p2.parameter_name = 'CUSTOMER_NAME';
1515
1516 -- get Customer new Name - using the p_to_fk_id
1517
1518 cursor get_new_customer_name(p_to_fk_id NUMBER) Is
1519 SELECT party_name
1520 FROM hz_parties
1521 WHERE party_id = p_to_fk_id;
1522
1523 --l_query_customer_name_id jtf_perz_query_param.query_param_id%TYPE;
1524 l_customer_new_name hz_parties.party_name%TYPE;
1525
1526 BEGIN
1527
1528 x_return_status := FND_API.G_RET_STS_SUCCESS;
1529
1530 open c_duplicate;
1531 fetch c_duplicate into l_merge_reason_code;
1532 close c_duplicate;
1533
1534 if l_merge_reason_code <> 'DUPLICATE' then
1535
1536 -- if there are any validations to be done, include it in this section
1537 -- if reason code is duplicate then allow the party merge to happen without
1538 -- any validations.
1539
1540 null;
1541
1542 end if;
1543
1544 -- perform the merge operation
1545
1549 if p_from_fk_id = p_to_fk_id then
1546 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1547 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1548
1550
1551 x_to_id := p_from_id;
1552 return;
1553
1554 end if;
1555
1556
1557 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1558 -- dependent record to the new parent.
1559
1560
1561 if p_from_fk_id <> p_to_fk_id then
1562
1563 --open get_query_customer_name(p_from_id);
1564 --fetch get_query_customer_name into l_query_customer_name_id;
1565 --close get_query_customer_name;
1566
1567 open get_new_customer_name(p_to_fk_id);
1568 fetch get_new_customer_name into l_customer_new_name;
1569 close get_new_customer_name;
1570
1571 -- UPDATE jtf_perz_query_param
1572 -- SET parameter_value = to_char(p_to_fk_id),
1573 -- last_update_date = hz_utility_pub.last_update_date,
1574 -- last_updated_by = hz_utility_pub.user_id,
1575 -- last_update_login = hz_utility_pub.last_update_login
1576 -- WHERE query_param_id = p_from_id
1577 -- AND parameter_value = to_char(p_from_fk_id);
1578
1579
1580 if --(SQL%ROWCOUNT > 0)
1581 -- and
1582 (l_customer_new_name is not NULL)
1583 -- and (l_query_customer_name_id is not NULL)
1584 then
1585
1586 -- UPDATE jtf_perz_query_param
1587 -- SET parameter_value = l_customer_new_name,
1588 -- last_update_date = hz_utility_pub.last_update_date,
1589 -- last_updated_by = hz_utility_pub.user_id,
1590 -- last_update_login = hz_utility_pub.last_update_login
1591 -- WHERE query_param_id = l_query_customer_name_id;
1592
1593 -- end if;
1594
1595 UPDATE jtf_perz_query_param
1596 SET parameter_value = l_customer_new_name,
1597 last_update_date = hz_utility_pub.last_update_date,
1598 last_updated_by = hz_utility_pub.user_id,
1599 last_update_login = hz_utility_pub.last_update_login
1600 WHERE --query_param_id = l_query_customer_name_id
1601 query_param_id IN (SELECT param.query_param_id
1602 FROM jtf_perz_query_param param
1603 , (SELECT q.query_id
1604 FROM jtf_perz_query q
1605 , jtf_perz_query_param p
1606 WHERE q.query_type = 'JTF_TASK'
1607 AND q.application_id = 690
1608 AND p.query_id = q.query_id
1609 AND p.parameter_name = 'CUSTOMER'
1610 AND p.parameter_value = 'NAME') query
1611 WHERE param.query_id = query.query_id
1612 AND param.parameter_name = 'CUSTOMER_NAME'
1613 AND EXISTS (SELECT 1
1614 FROM jtf_perz_query_param pm
1615 WHERE pm.query_id = param.query_id
1616 AND pm.parameter_name = 'CUSTOMER_ID'
1617 AND pm.parameter_value = to_char(p_from_fk_id))
1618 );
1619
1620 end if;
1621
1622 UPDATE jtf_perz_query_param p
1623 SET parameter_value = to_char(p_to_fk_id),
1624 last_update_date = hz_utility_pub.last_update_date,
1625 last_updated_by = hz_utility_pub.user_id,
1626 last_update_login = hz_utility_pub.last_update_login
1627 WHERE --query_param_id = p_from_id
1628 query_param_id IN (SELECT param.query_param_id
1629 FROM jtf_perz_query_param param
1630 , (SELECT q.query_id
1631 FROM jtf_perz_query q
1632 , jtf_perz_query_param p
1633 WHERE q.query_type = 'JTF_TASK'
1634 AND q.application_id = 690
1635 AND p.query_id = q.query_id
1636 AND p.parameter_name = 'CUSTOMER'
1637 AND p.parameter_value = 'NAME') query
1638 WHERE param.query_id = query.query_id
1639 AND param.parameter_name = 'CUSTOMER_ID'
1640 AND param.parameter_value = to_char(p_from_fk_id));
1641
1642 end if;
1643
1644 exception
1645 when others then
1646
1647 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1648 fnd_message.set_token('ERROR' ,SQLERRM);
1649 fnd_msg_pub.add;
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651
1652
1653 END SEARCH_MERGE_NAME_PARTY_ID;
1654
1655
1656 ------------------------------------------------------------------------------------------
1657 -- Procedure: TASK_ASSIGNMENTS_MERGE - Performs party ids merge in
1658 -- JTF_TASK_ASSIGNMENTS table.
1659 -- Columns: Updates RESOURCE_ID where RESOURCE_TYPE is of party type
1660 ------------------------------------------------------------------------------------------
1661
1662 PROCEDURE TASK_ASSIGNMENTS_MERGE(
1663 p_entity_name IN VARCHAR2,
1664 p_from_id IN NUMBER,
1665 x_to_id OUT NOCOPY NUMBER,
1666 p_from_fk_id IN NUMBER,
1667 p_to_fk_id IN NUMBER,
1668 p_parent_entity_name IN VARCHAR2,
1669 p_batch_id IN NUMBER,
1670 p_batch_party_id IN NUMBER,
1671 x_return_status OUT NOCOPY VARCHAR2) IS
1672
1673 l_merge_reason_code VARCHAR2(30);
1674
1675 Cursor c_duplicate Is
1676 select merge_reason_code
1677 from hz_merge_batch
1678 where batch_id = p_batch_id;
1679
1680 CURSOR c_party IS
1681 SELECT party_type
1682 FROM hz_parties
1683 WHERE party_id = p_from_fk_id;
1684
1685 l_party_type hz_parties.party_type%TYPE;
1686
1687 BEGIN
1688
1689 x_return_status := FND_API.G_RET_STS_SUCCESS;
1690
1691 open c_duplicate;
1692 fetch c_duplicate into l_merge_reason_code;
1693 close c_duplicate;
1694
1695 if l_merge_reason_code <> 'DUPLICATE' then
1696
1697 -- if there are any validations to be done, include it in this section
1698 -- if reason code is duplicate then allow the party merge to happen without
1699 -- any validations.
1700
1701 null;
1702
1703 end if;
1704
1705 -- perform the merge operation
1706
1707 -- if the parent has NOT changed(i.e. parent getting transferred) then nothing
1708 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
1709
1710 if p_from_fk_id = p_to_fk_id then
1711
1712 x_to_id := p_from_id;
1713 return;
1714
1715 end if;
1716
1717
1718 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1719 -- dependent record to the new parent.
1720
1721 if p_from_fk_id <> p_to_fk_id then
1722
1723 UPDATE jtf_task_assignments
1724 SET resource_id = p_to_fk_id,
1725 last_update_date = hz_utility_pub.last_update_date,
1726 last_updated_by = hz_utility_pub.user_id,
1727 last_update_login = hz_utility_pub.last_update_login,
1728 object_version_number = object_version_number + 1
1729 WHERE resource_id = p_from_fk_id
1730 AND resource_type_code IN (SELECT object_code
1731 FROM jtf_objects_b
1732 WHERE LTRIM(RTRIM(UPPER(from_table))) = 'HZ_PARTIES'
1733 AND RTRIM(LTRIM(UPPER(select_id))) = 'PARTY_ID');
1734
1735 end if;
1736
1737 exception
1738 when others then
1739
1740 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1741 fnd_message.set_token('ERROR' ,SQLERRM);
1742 fnd_msg_pub.add;
1743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1744
1745 END TASK_ASSIGNMENTS_MERGE;
1746
1747
1748 END JTF_TASK_PARTY_MERGE_PKG;
[Home] [Help]