[Home] [Help]
PACKAGE BODY: APPS.CSI_PARTY_MERGE_PKG
Source
1 PACKAGE BODY CSI_PARTY_MERGE_PKG AS
2 /* $Header: csipymgb.pls 120.1 2008/02/26 18:56:46 fli ship $ */
3
4 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CSI_PARTY_MERGE_PKG';
5 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
6 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
7
8 PROCEDURE CSI_ITEM_INSTANCES_MERGE(
9 p_entity_name IN VARCHAR2,
10 p_from_id IN NUMBER,
11 x_to_id OUT NOCOPY NUMBER,
12 p_from_fk_id IN NUMBER,
13 p_to_fk_id IN NUMBER,
14 p_parent_entity_name IN VARCHAR2,
15 p_batch_id IN NUMBER,
16 p_batch_party_id IN NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2)
18 IS
19 v_location_type_code VARCHAR2(30) := 'HZ_PARTY_SITES';
20 v_install_location_type_code VARCHAR2(30) := 'HZ_PARTY_SITES';
21 v_owner_party_source_table VARCHAR2(30) := 'HZ_PARTIES';
22 v_source_transaction_type VARCHAR2(30) := 'PARTY_MERGE';
23
24 cursor c1 is
25 select 1
26 from csi_item_instances
27 where owner_party_id = p_from_fk_id
28 and owner_party_source_table = v_owner_party_source_table
29 for update nowait;
30
31 cursor c2 is
32 select 1
33 from csi_item_instances
34 where ( install_location_type_code = v_install_location_type_code and install_location_id = p_from_fk_id ) or
35 ( location_type_code = v_install_location_type_code and location_id = p_from_fk_id )
36 for update nowait;
37
38 l_merge_reason_code VARCHAR2(30);
39 l_api_name VARCHAR2(30) := 'CSI_ITEM_INSTANCES_MERGE';
40 l_column_name VARCHAR2(30);
41 l_count NUMBER(10) := 0;
42 l_cp_audit_id NUMBER;
43 v_transaction_type_id NUMBER;
44 v_transaction_id NUMBER;
45 v_no_of_rows NUMBER;
46 v_error_message varchar2(255);
47 v_internal_party_message varchar2(255);
48 v_txn_type_not_found_msg varchar2(255);
49 v_instance_history_id NUMBER;
50 v_internal_party_id NUMBER;
51 internal_party_error EXCEPTION;
52 txn_type_not_found_error EXCEPTION;
53 RESOURCE_BUSY EXCEPTION;
54 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
55 BEGIN
56 arp_message.set_line('CSI_PARTY_MERGE_PKG.CSI_ITEM_INSTANCES_MERGE()+');
57
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 begin
61 select internal_party_id
62 into v_internal_party_id
63 from csi_install_parameters;
64 exception
65 when no_data_found then
66 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. Data exists in Installed Base, but Install Parameters are not defined';
67 raise internal_party_error;
68 when others then
69 arp_message.set_line(g_proc_name || '.' ||l_api_name || ': ' ||sqlerrm);
70 x_return_status := FND_API.G_RET_STS_ERROR;
71 raise;
72 end;
73
74 if v_internal_party_id = p_from_fk_id or
75 v_internal_party_id = p_to_fk_id
76 then
77 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. One of the party ids is defined as internal party in CSI-Installed Base';
78 raise internal_party_error;
79 end if;
80
81 select merge_reason_code
82 into l_merge_reason_code
83 from hz_merge_batch
84 where batch_id = p_batch_id;
85
86 if l_merge_reason_code = 'DUPLICATE' then
87 -- if reason code is duplicate then allow the party merge to
88 -- happen without any validations.
89 null;
90 else
91 -- if there are any validations to be done, include it in this section
92 null;
93 end if;
94
95 if p_from_fk_id = p_to_fk_id then
96 x_to_id := p_from_id;
97 return;
98 end if;
99
100 IF p_from_fk_id <> p_to_fk_id then
101 BEGIN
102 If p_parent_entity_name = 'HZ_PARTIES' Then
103
104 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
105 arp_message.set_token('TABLE_NAME', 'CSI_ITEM_INSTANCES', FALSE);
106
107 l_column_name := 'owner_party_id';
108
109 open c1;
110 close c1;
111
112 update csi_item_instances
113 set owner_party_id = p_to_fk_id,
114 last_update_date = SYSDATE,
115 last_updated_by = G_USER_ID,
116 last_update_login = G_LOGIN_ID
117 where owner_party_id = p_from_fk_id
118 and owner_party_source_table = v_owner_party_source_table;
119
120 l_count := sql%rowcount;
121
122 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
123 arp_message.set_token('NUM_ROWS', to_char(l_count) );
124
125 Elsif p_parent_entity_name = 'HZ_PARTY_SITES' Then
126
127 /* insert record into transaction table */
128 v_no_of_rows := 0;
129
130 Begin
131
132 Begin
133 SELECT transaction_type_id
134 INTO v_transaction_type_id
135 FROM csi_txn_types
136 WHERE source_transaction_type = v_source_transaction_type;
137 Exception
138 when no_data_found then
139 v_txn_type_not_found_msg := 'Invalid Transaction Type.';
140 raise txn_type_not_found_error;
141 End;
142
143 SELECT transaction_id
144 INTO v_transaction_id
145 FROM csi_transactions
146 WHERE source_line_ref_id = p_batch_id AND
147 transaction_type_id = v_transaction_type_id;
148
149 Exception
150
151 When no_data_found Then
152
153 Begin
154
155 Begin
156
157 SELECT CSI_TRANSACTIONS_S.nextval
158 INTO v_transaction_id
159 FROM dual;
160
161 End;
162
163 INSERT INTO csi_transactions(
164 transaction_id
165 ,transaction_date
166 ,source_transaction_date
167 ,transaction_type_id
168 ,source_line_ref_id
169 ,created_by
170 ,creation_date
171 ,last_updated_by
172 ,last_update_date
173 ,last_update_login
174 ,object_version_number
175 )
176 VALUES(
177 v_transaction_id
178 ,sysdate
179 ,sysdate
180 ,v_transaction_type_id
181 ,p_batch_id
182 ,arp_standard.profile.user_id
183 ,sysdate
184 ,arp_standard.profile.user_id
185 ,sysdate
186 ,arp_standard.profile.user_id
187 ,1
188 );
189
190 End;
191
192 End;
193
194 /* insert record into history table */
195 --
196 INSERT INTO CSI_ITEM_INSTANCES_H
197 (
198 INSTANCE_HISTORY_ID
199 ,INSTANCE_ID
200 ,TRANSACTION_ID
201 ,OLD_LOCATION_ID
202 ,NEW_LOCATION_ID
203 ,FULL_DUMP_FLAG
204 ,CREATED_BY
205 ,CREATION_DATE
206 ,LAST_UPDATED_BY
207 ,LAST_UPDATE_DATE
208 ,LAST_UPDATE_LOGIN
209 ,OBJECT_VERSION_NUMBER
210 ,OLD_INST_LOC_ID
211 ,NEW_INST_LOC_ID
212 )
213 SELECT
214 CSI_ITEM_INSTANCES_H_S.nextval
215 ,cii.INSTANCE_ID
216 ,v_transaction_id
217 ,decode( cii.location_id, p_from_fk_id, cii.location_id, null )
218 ,decode( cii.location_id, p_from_fk_id, p_to_fk_id, null )
219 ,'N'
220 ,arp_standard.profile.user_id
221 ,sysdate
222 ,arp_standard.profile.user_id
223 ,sysdate
224 ,arp_standard.profile.user_id
225 ,1
226 ,decode( cii.install_location_id, p_from_fk_id, cii.install_location_id, null )
227 ,decode( cii.install_location_id, p_from_fk_id, p_to_fk_id, null )
228 FROM csi_item_instances cii
229 WHERE ( install_location_type_code = v_install_location_type_code and
230 install_location_id = p_from_fk_id ) or
231 ( location_type_code = v_install_location_type_code and
232 location_id = p_from_fk_id );
233
234
235 arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
236
237 v_no_of_rows := sql%rowcount;
238 arp_message.set_token('NUM_ROWS',to_char(v_no_of_rows));
239 v_error_message := 'Done with the insert of item instance history';
240 arp_message.set_line(v_error_message);
241
242 /*
243 After inserting into the history tables for the location(s) update,
244 now update the install_location_id and location_id, if applicable
245 */
246
247 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
248 arp_message.set_token('TABLE_NAME', 'CSI_ITEM_INSTANCES', FALSE);
249
250 l_column_name := 'location_id';
251
252 open c2;
253 close c2;
254
255 /* Modified udpate statement to retain the install_location_id & location_id instead of nulling them out - Bug#6848272*/
256 update csi_item_instances
257 set install_location_id = decode( install_location_id, p_from_fk_id, p_to_fk_id, install_location_id ),
258 location_id = decode( location_id, p_from_fk_id, p_to_fk_id, location_id ),
259 last_update_date = SYSDATE,
260 last_updated_by = G_USER_ID,
261 last_update_login = G_LOGIN_ID
262 where ( install_location_type_code = v_install_location_type_code and
263 install_location_id = p_from_fk_id ) or
264 ( location_type_code = v_install_location_type_code and
265 location_id = p_from_fk_id );
266
267 l_count := sql%rowcount;
268
269 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
270 arp_message.set_token('NUM_ROWS', to_char(l_count) );
271
272 End If;
273
274 EXCEPTION
275 when internal_party_error then
276 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || v_internal_party_message);
277 x_return_status := FND_API.G_RET_STS_ERROR;
278 raise;
279 when txn_type_not_found_error then
280 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || v_txn_type_not_found_msg);
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 raise;
283 when resource_busy then
284 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CSI_ITEM_INSTANCES for '||l_column_name ||' = '|| p_from_fk_id );
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 raise;
287 when others then
288 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 raise;
291 END;
292 END IF;
293 END CSI_ITEM_INSTANCES_MERGE;
294
295
296 PROCEDURE CSI_I_PARTIES_MERGE(
297 p_entity_name IN VARCHAR2,
298 p_from_id IN NUMBER,
299 x_to_id OUT NOCOPY NUMBER,
300 p_from_fk_id IN NUMBER,
301 p_to_fk_id IN NUMBER,
302 p_parent_entity_name IN VARCHAR2,
303 p_batch_id IN NUMBER,
304 p_batch_party_id IN NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2)
306 IS
307 v_source_transaction_type VARCHAR2(30) := 'PARTY_MERGE';
308 v_party_source_table VARCHAR2(30) := 'HZ_PARTIES';
309
310 cursor c1 is
311 select 1
312 from csi_i_parties
313 where party_id = p_from_fk_id
314 and party_source_table = v_party_source_table
315 for update nowait;
316
317 l_merge_reason_code VARCHAR2(30);
318 l_api_name VARCHAR2(30) := 'CSI_I_PARTIES_MERGE';
319 l_column_name VARCHAR2(30);
320 l_count NUMBER(10) := 0;
321 l_cp_audit_id NUMBER;
322 v_transaction_type_id NUMBER;
323 v_transaction_id NUMBER;
324 v_no_of_rows NUMBER;
325 v_error_message varchar2(255);
326 v_internal_party_message varchar2(255);
327 v_txn_type_not_found_msg varchar2(255);
328 v_instance_party_history_id NUMBER;
329 v_internal_party_id NUMBER;
330 internal_party_error EXCEPTION;
331 txn_type_not_found_error EXCEPTION;
332
333 RESOURCE_BUSY EXCEPTION;
334 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
335 BEGIN
336 arp_message.set_line('CSI_PARTY_MERGE_PKG.CSI_I_PARTIES_MERGE()+');
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 begin
341 select internal_party_id
342 into v_internal_party_id
343 from csi_install_parameters;
344 exception
345 when no_data_found then
346 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. Data exists in Installed Base, but Install Parameters are not defined';
347 raise internal_party_error;
348 when others then
349 arp_message.set_line(g_proc_name || '.' ||l_api_name || ': ' ||sqlerrm);
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 raise;
352 end;
353
354 if v_internal_party_id = p_from_fk_id or
355 v_internal_party_id = p_to_fk_id
356 then
357 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. One of the party ids is defined as internal party in CSI-Installed Base application.';
358 raise internal_party_error;
359 end if;
360
361 select merge_reason_code
362 into l_merge_reason_code
363 from hz_merge_batch
364 where batch_id = p_batch_id;
365
366 if l_merge_reason_code = 'DUPLICATE' then
367 -- if reason code is duplicate then allow the party merge to
368 -- happen without any validations.
369 null;
370 else
371 -- if there are any validations to be done, include it in this section
372 null;
373 end if;
374
375 if p_from_fk_id = p_to_fk_id then
376 x_to_id := p_from_id;
377 return;
378 end if;
379
380 IF p_from_fk_id <> p_to_fk_id then
381 BEGIN
382 /* insert record into transaction table */
383 v_no_of_rows := 0;
384
385 Begin
386 Begin
387 SELECT transaction_type_id
388 INTO v_transaction_type_id
389 FROM csi_txn_types
390 WHERE source_transaction_type = v_source_transaction_type;
394 raise txn_type_not_found_error;
391 Exception
392 when no_data_found then
393 v_txn_type_not_found_msg := 'Invalid Transaction Type..';
395 End;
396 SELECT transaction_id
397 INTO v_transaction_id
398 FROM csi_transactions
399 WHERE source_line_ref_id = p_batch_id
400 AND transaction_type_id = v_transaction_type_id;
401 Exception
402 When no_data_found Then
403 Begin
404 Begin
405 SELECT CSI_TRANSACTIONS_S.nextval
406 INTO v_transaction_id
407 FROM dual;
408 End;
409 INSERT INTO csi_transactions
410 (
411 transaction_id
412 ,transaction_date
413 ,source_transaction_date
414 ,transaction_type_id
415 ,source_line_ref_id
416 ,created_by
417 ,creation_date
418 ,last_updated_by
419 ,last_update_date
420 ,last_update_login
421 ,object_version_number
422 )
423 VALUES
424 (
425 v_transaction_id
426 ,sysdate
427 ,sysdate
428 ,v_transaction_type_id
429 ,p_batch_id
430 ,arp_standard.profile.user_id
431 ,sysdate
432 ,arp_standard.profile.user_id
433 ,sysdate
434 ,arp_standard.profile.user_id
435 ,1
436 );
437 End;
438 End;
439
440 /* insert record into history table */
441
442 INSERT INTO csi_i_parties_h
443 (
444 instance_party_history_id,
445 instance_party_id,
446 transaction_id,
447 old_party_source_table,
448 new_party_source_table,
449 old_party_id,
450 new_party_id,
451 full_dump_flag,
452 created_by,
453 creation_date,
454 last_updated_by,
455 last_update_date,
456 last_update_login,
457 object_version_number
458 )
459 SELECT csi_i_parties_h_s.nextval,
460 cip.instance_party_id,
461 v_transaction_id,
462 v_party_source_table,
463 v_party_source_table,
464 p_from_fk_id,
465 p_to_fk_id,
466 'N',
467 arp_standard.profile.user_id,
468 sysdate,
469 arp_standard.profile.user_id,
470 sysdate,
471 arp_standard.profile.user_id,
472 1
473 FROM csi_i_parties cip
474 WHERE cip.party_source_table = v_party_source_table
475 AND cip.party_id = p_from_fk_id;
476
477 arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
478
479 v_no_of_rows := sql%rowcount;
480 arp_message.set_token('NUM_ROWS',to_char(v_no_of_rows));
481 v_error_message := 'Done with the insert of party history';
482 arp_message.set_line(v_error_message);
483
484 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
485 arp_message.set_token('TABLE_NAME', 'CSI_I_PARTIES', FALSE);
486
487 l_column_name := 'party_id';
488
489 open c1;
490 close c1;
491
492 update csi_i_parties
493 set party_id = p_to_fk_id,
494 last_update_date = SYSDATE,
495 last_updated_by = G_USER_ID,
496 last_update_login = G_LOGIN_ID
497 where party_id = p_from_fk_id
498 and party_source_table = v_party_source_table;
499
500 l_count := sql%rowcount;
501
502 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
503 arp_message.set_token('NUM_ROWS', to_char(l_count) );
504
505 EXCEPTION
506 when internal_party_error then
507 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' ||v_internal_party_message);
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 raise;
510 when txn_type_not_found_error then
511 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || v_txn_type_not_found_msg);
512 x_return_status := FND_API.G_RET_STS_ERROR;
513 raise;
514 when resource_busy then
515 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CSI_I_PARTIES for '||l_column_name ||' = '|| p_from_fk_id );
516 x_return_status := FND_API.G_RET_STS_ERROR;
517 raise;
518 when others then
519 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 raise;
522 END;
523 END IF;
524 END CSI_I_PARTIES_MERGE;
525
529 x_to_id OUT NOCOPY NUMBER,
526 PROCEDURE CSI_SYSTEMS_B_MERGE(
527 p_entity_name IN VARCHAR2,
528 p_from_id IN NUMBER,
530 p_from_fk_id IN NUMBER,
531 p_to_fk_id IN NUMBER,
532 p_parent_entity_name IN VARCHAR2,
533 p_batch_id IN NUMBER,
534 p_batch_party_id IN NUMBER,
535 x_return_status OUT NOCOPY VARCHAR2)
536 IS
537 cursor c1 is
538 select 1
539 from csi_systems_b
540 where install_site_use_id = p_from_fk_id
541 for update nowait;
542
543 cursor c2 is
544 select 1
545 from csi_systems_b
546 where ship_to_contact_id = p_from_fk_id
547 or bill_to_contact_id = p_from_fk_id
548 or technical_contact_id = p_from_fk_id
549 or service_admin_contact_id = p_from_fk_id
550 for update nowait;
551
552 v_source_transaction_type VARCHAR2(30) := 'PARTY_MERGE';
553 l_merge_reason_code VARCHAR2(30);
554 l_api_name VARCHAR2(30) := 'CSI_SYSTEMS_B_MERGE';
555 l_count NUMBER(10) := 0;
556 l_system_audit_id NUMBER;
557 l_column_name VARCHAR2(30);
558 v_transaction_type_id NUMBER;
559 v_transaction_id NUMBER;
560 v_no_of_rows NUMBER;
561 v_error_message varchar2(255);
562 v_internal_party_message varchar2(255);
563 v_txn_type_not_found_msg varchar2(255);
564 v_system_history_id NUMBER;
565 v_internal_party_id NUMBER;
566 internal_party_error EXCEPTION;
567 txn_type_not_found_error EXCEPTION;
568
569 RESOURCE_BUSY EXCEPTION;
570 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
571
572 BEGIN
573 arp_message.set_line('CSI_PARTY_MERGE_PKG.CSI_SYSTEMS_B_MERGE()+');
574
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576
577 begin
578 select internal_party_id
579 into v_internal_party_id
580 from csi_install_parameters;
581 exception
582 when no_data_found then
583 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. Data exists in Installed Base, but Install Parameters are not defined';
584 raise internal_party_error;
585 when others then
586 arp_message.set_line(g_proc_name || '.' ||l_api_name || ': ' ||sqlerrm);
587 x_return_status := FND_API.G_RET_STS_ERROR;
588 raise;
589 end;
590
591 if v_internal_party_id = p_from_fk_id or
592 v_internal_party_id = p_to_fk_id
593 then
594 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. One of the party ids is defined as internal party in CSI-Installed Base';
595 raise internal_party_error;
596 end if;
597
598 select merge_reason_code
599 into l_merge_reason_code
600 from hz_merge_batch
601 where batch_id = p_batch_id;
602
603 if l_merge_reason_code = 'DUPLICATE' then
604 null;
605 else
606 null;
607 end if;
608
609 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
610 -- needs to be done. Set Merged To Id is same as Merged From Id and return
611
612 If p_from_fk_id = p_to_fk_id Then
613 x_to_id := p_from_id;
614 return;
615 End If;
616
617 If p_from_fk_id <> p_to_fk_id Then
618
619 Begin
620 BEGIN
621
622 Begin
623 SELECT transaction_type_id
624 INTO v_transaction_type_id
625 FROM csi_txn_types
626 WHERE source_transaction_type = v_source_transaction_type;
627 Exception
628 when no_data_found then
629 v_txn_type_not_found_msg := 'Invalid Transaction Type...';
630 raise txn_type_not_found_error;
631 End;
632
633 SELECT transaction_id
634 INTO v_transaction_id
635 FROM csi_transactions
636 WHERE source_line_ref_id = p_batch_id
637 AND transaction_type_id = v_transaction_type_id;
638
639 EXCEPTION
640 When no_data_found Then
641
642 Begin
643
644 Begin
645 SELECT CSI_TRANSACTIONS_S.nextval
646 INTO v_transaction_id
647 FROM dual;
648 End;
649
650 INSERT INTO csi_transactions(
651 transaction_id
652 ,transaction_date
653 ,source_transaction_date
654 ,transaction_type_id
655 ,source_line_ref_id
656 ,created_by
657 ,creation_date
658 ,last_updated_by
659 ,last_update_date
660 ,last_update_login
661 ,object_version_number
662 )
666 ,sysdate
663 VALUES(
664 v_transaction_id
665 ,sysdate
667 ,v_transaction_type_id
668 ,p_batch_id
669 ,arp_standard.profile.user_id
670 ,sysdate
671 ,arp_standard.profile.user_id
672 ,sysdate
673 ,arp_standard.profile.user_id
674 ,1
675 );
676 End;
677 END;
678
679 If p_parent_entity_name = 'HZ_PARTY_SITES' Then
680 v_no_of_rows := 0;
681
682 INSERT INTO csi_systems_h
683 (system_history_id,
684 system_id,
685 transaction_id,
686 old_install_site_use_id,
687 new_install_site_use_id,
688 full_dump_flag,
689 created_by,
690 creation_date,
691 last_updated_by,
692 last_update_date,
693 last_update_login,
694 object_version_number
695 )
696 SELECT csi_systems_h_s.nextval,
697 csb.system_id,
698 v_transaction_id,
699 p_from_fk_id,
700 p_to_fk_id,
701 'N',
702 arp_standard.profile.user_id,
703 sysdate,
704 arp_standard.profile.user_id,
705 sysdate,
706 arp_standard.profile.user_id,
707 1
708 FROM csi_systems_b csb
709 WHERE csb.install_site_use_id = p_from_fk_id;
710
711 arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
712
713 v_no_of_rows := sql%rowcount;
714 arp_message.set_token('NUM_ROWS',to_char(v_no_of_rows));
715 v_error_message := 'Done with the insert of systems history';
716 arp_message.set_line(v_error_message);
717
718 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
719 arp_message.set_token('TABLE_NAME', 'CSI_SYSTEMS_B', FALSE);
720
721 l_column_name := 'install_site_use_id';
722
723 open c1;
724 close c1;
725
726 update csi_systems_b
727 set install_site_use_id = p_to_fk_id,
728 last_update_date = SYSDATE,
729 last_updated_by = G_USER_ID,
730 last_update_login = G_LOGIN_ID
731 where install_site_use_id = p_from_fk_id;
732
733 l_count := sql%rowcount;
734
735 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
736 arp_message.set_token('NUM_ROWS', to_char(l_count) );
737
738 Elsif p_parent_entity_name = 'HZ_PARTIES' Then
739
740 v_no_of_rows := 0;
741
742 INSERT INTO csi_systems_h
743 (
744 system_history_id,
745 system_id,
746 transaction_id,
747 old_ship_to_contact_id,
748 new_ship_to_contact_id,
749 old_bill_to_contact_id,
750 new_bill_to_contact_id,
751 old_technical_contact_id,
752 new_technical_contact_id,
753 old_service_admin_contact_id,
754 new_service_admin_contact_id,
755 full_dump_flag,
756 created_by,
757 creation_date,
758 last_updated_by,
759 last_update_date,
760 last_update_login,
761 object_version_number
762 )
763 SELECT csi_systems_h_s.nextval,
764 csb.system_id,
765 v_transaction_id,
766 decode( csb.ship_to_contact_id, p_from_fk_id, p_from_fk_id, null ),
767 decode( csb.ship_to_contact_id, p_from_fk_id, p_to_fk_id, null ),
768 decode( csb.bill_to_contact_id, p_from_fk_id, p_from_fk_id, null ),
769 decode( csb.bill_to_contact_id, p_from_fk_id, p_to_fk_id, null ),
770 decode( csb.technical_contact_id, p_from_fk_id, p_from_fk_id, null ),
771 decode( csb.technical_contact_id, p_from_fk_id, p_to_fk_id, null ),
772 decode( csb.service_admin_contact_id, p_from_fk_id, p_from_fk_id, null ),
773 decode( csb.service_admin_contact_id, p_from_fk_id, p_to_fk_id, null ),
774 'N',
775 arp_standard.profile.user_id,
776 sysdate,
777 arp_standard.profile.user_id,
778 sysdate,
779 arp_standard.profile.user_id,
780 1
781 FROM csi_systems_b csb
782 WHERE ship_to_contact_id = p_from_fk_id
783 OR bill_to_contact_id = p_from_fk_id
784 OR technical_contact_id = p_from_fk_id
785 OR service_admin_contact_id = p_from_fk_id ;
786
787 arp_message.set_Name('CSI', 'CSI_ROWS_INSERTED');
788
789 v_no_of_rows := sql%rowcount;
793
790 arp_message.set_token('NUM_ROWS',to_char(v_no_of_rows));
791 v_error_message := 'Done with the insert of systems history';
792 arp_message.set_line(v_error_message);
794 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
795 arp_message.set_token('TABLE_NAME', 'CSI_SYSTEMS_B', FALSE);
796
797 l_column_name := 'contact_ids';
798
799 open c2;
800 close c2;
801
802 /* Modified udpate statement to retain : ship_to_contact_id, bill_to_contact_id, technical_contact_id &
803 * service_admin_contact_id instead of nulling them out - Bug#6848272
804 */
805 update csi_systems_b
806 set ship_to_contact_id = decode( ship_to_contact_id, p_from_fk_id, p_to_fk_id, ship_to_contact_id ),
807 bill_to_contact_id = decode( bill_to_contact_id, p_from_fk_id, p_to_fk_id, bill_to_contact_id ),
808 technical_contact_id = decode( technical_contact_id, p_from_fk_id, p_to_fk_id, technical_contact_id ),
809 service_admin_contact_id = decode( service_admin_contact_id, p_from_fk_id, p_to_fk_id, service_admin_contact_id ),
810 last_update_date = SYSDATE,
811 last_updated_by = G_USER_ID,
812 last_update_login = G_LOGIN_ID
813 where ship_to_contact_id = p_from_fk_id
814 or bill_to_contact_id = p_from_fk_id
815 or technical_contact_id = p_from_fk_id
816 or service_admin_contact_id = p_from_fk_id ;
817
818 l_count := sql%rowcount;
819
820 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
821 arp_message.set_token('NUM_ROWS', to_char(l_count) );
822
823 End If;
824
825 EXCEPTION
826 when internal_party_error then
827 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' ||v_internal_party_message);
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 raise;
830 when txn_type_not_found_error then
831 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || v_txn_type_not_found_msg);
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 raise;
834 when resource_busy then
835 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CSI_SYSTEMS_B for '||l_column_name ||' = '|| p_from_fk_id );
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 raise;
838
839 when others then
840 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
841 x_return_status := FND_API.G_RET_STS_ERROR;
842 raise;
843 END;
844 end if;
845 END CSI_SYSTEMS_B_MERGE;
846
847 PROCEDURE CSI_T_TXN_SYSTEMS_MERGE(
848 p_entity_name IN VARCHAR2,
849 p_from_id IN NUMBER,
850 x_to_id OUT NOCOPY NUMBER,
851 p_from_fk_id IN NUMBER,
852 p_to_fk_id IN NUMBER,
853 p_parent_entity_name IN VARCHAR2,
854 p_batch_id IN NUMBER,
855 p_batch_party_id IN NUMBER,
856 x_return_status OUT NOCOPY VARCHAR2)
857 IS
858
859 cursor c1 is
860 select 1
861 from csi_t_txn_systems
862 where install_site_use_id = p_from_fk_id
863 for update nowait;
864
865 cursor c2 is
866 select 1
867 from csi_t_txn_systems
868 where ship_to_contact_id = p_from_fk_id
869 or bill_to_contact_id = p_from_fk_id
870 or technical_contact_id = p_from_fk_id
871 or service_admin_contact_id = p_from_fk_id
872 for update nowait;
873
874 l_merge_reason_code VARCHAR2(30);
875 l_api_name VARCHAR2(30) := 'CSI_T_TXN_SYSTEMS_MERGE';
876 l_count NUMBER(10) := 0;
877 l_system_audit_id NUMBER;
878 l_column_name VARCHAR2(30);
879 v_no_of_rows NUMBER;
880 v_error_message varchar2(255);
881 v_internal_party_message varchar2(255);
882 v_internal_party_id NUMBER;
883 internal_party_error EXCEPTION;
884
885 RESOURCE_BUSY EXCEPTION;
886 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
887
888 BEGIN
889 arp_message.set_line('CSI_PARTY_MERGE_PKG.CSI_T_TXN_SYSTEMS_MERGE()+');
890
891 x_return_status := FND_API.G_RET_STS_SUCCESS;
892
893 begin
894 select internal_party_id
895 into v_internal_party_id
896 from csi_install_parameters;
897 exception
898 when no_data_found then
899 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. Data exists in Installed Base, but Install Parameters are not defined';
900 raise internal_party_error;
901 when others then
902 arp_message.set_line(g_proc_name || '.' ||l_api_name || ': ' ||sqlerrm);
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 raise;
905 end;
906
907 if v_internal_party_id = p_from_fk_id or
908 v_internal_party_id = p_to_fk_id
909 then
913
910 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. One of the party ids is defined as internal party in CSI-Installed Base';
911 raise internal_party_error;
912 end if;
914 select merge_reason_code
915 into l_merge_reason_code
916 from hz_merge_batch
917 where batch_id = p_batch_id;
918
919 if l_merge_reason_code = 'DUPLICATE' then
920 null;
921 else
922 null;
923 end if;
924
925 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
926 -- needs to be done. Set Merged To Id is same as Merged From Id and return
927
928 if p_from_fk_id = p_to_fk_id then
929 x_to_id := p_from_id;
930 return;
931 end if;
932
933 if p_from_fk_id <> p_to_fk_id then
934 begin
935
936 If p_parent_entity_name = 'HZ_PARTY_SITES' Then
937
938 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
939 arp_message.set_token('TABLE_NAME', 'CSI_T_TXN_SYSTEMS', FALSE);
940
941 l_column_name := 'install_site_use_id';
942
943 open c1;
944 close c1;
945
946 update csi_t_txn_systems
947 set install_site_use_id = p_to_fk_id,
948 last_update_date = SYSDATE,
949 last_updated_by = G_USER_ID,
950 last_update_login = G_LOGIN_ID
951 where install_site_use_id = p_from_fk_id;
952
953 l_count := sql%rowcount;
954
955 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
956 arp_message.set_token('NUM_ROWS', to_char(l_count) );
957
958 Elsif p_parent_entity_name = 'HZ_PARTIES' Then
959
960 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
961 arp_message.set_token('TABLE_NAME', 'CSI_T_TXN_SYSTEMS', FALSE);
962
963 l_column_name := 'contact_ids';
964
965 open c2;
966 close c2;
967
968 update csi_t_txn_systems
969 set bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id, p_to_fk_id, bill_to_contact_id ),
970 ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id, p_to_fk_id, ship_to_contact_id ),
971 technical_contact_id = decode(technical_contact_id, p_from_fk_id, p_to_fk_id, technical_contact_id ),
972 service_admin_contact_id = decode(service_admin_contact_id,
973 p_from_fk_id, p_to_fk_id, service_admin_contact_id ),
974 last_update_date = SYSDATE,
975 last_updated_by = G_USER_ID,
976 last_update_login = G_LOGIN_ID
977 where ship_to_contact_id = p_from_fk_id
978 or bill_to_contact_id = p_from_fk_id
979 or technical_contact_id = p_from_fk_id
980 or service_admin_contact_id = p_from_fk_id;
981
982 l_count := sql%rowcount;
983
984 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
985 arp_message.set_token('NUM_ROWS', to_char(l_count) );
986
987 End If;
988
989 EXCEPTION
990 when internal_party_error then
991 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' ||v_internal_party_message);
992 x_return_status := FND_API.G_RET_STS_ERROR;
993 raise;
994 when resource_busy then
995 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CSI_T_TXN_SYSTEMS for '||l_column_name ||' = '|| p_from_fk_id );
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 raise;
998 when others then
999 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 raise;
1002 END;
1003
1004 end if;
1005
1006 END CSI_T_TXN_SYSTEMS_MERGE;
1007
1008 PROCEDURE CSI_T_PARTY_DETAILS_MERGE(
1009 p_entity_name IN VARCHAR2,
1010 p_from_id IN NUMBER,
1011 x_to_id OUT NOCOPY NUMBER,
1012 p_from_fk_id IN NUMBER,
1013 p_to_fk_id IN NUMBER,
1014 p_parent_entity_name IN VARCHAR2,
1015 p_batch_id IN NUMBER,
1016 p_batch_party_id IN NUMBER,
1017 x_return_status OUT NOCOPY VARCHAR2)
1018 IS
1019 v_party_source_table VARCHAR2(30) := 'HZ_PARTIES';
1020
1021 cursor c1 is
1022 select 1
1023 from csi_t_party_details
1024 where party_source_id = p_from_fk_id
1025 and party_source_table = v_party_source_table
1026 for update nowait;
1027
1028 l_merge_reason_code VARCHAR2(30);
1029 l_api_name VARCHAR2(30) := 'CSI_T_PARTY_DETAILS_MERGE';
1030 l_column_name VARCHAR2(30);
1031 l_count NUMBER(10) := 0;
1032 l_cp_audit_id NUMBER;
1033 v_no_of_rows NUMBER;
1034 v_error_message varchar2(255);
1035 v_internal_party_message varchar2(255);
1036 v_internal_party_id NUMBER;
1037 internal_party_error EXCEPTION;
1041 BEGIN
1038
1039 RESOURCE_BUSY EXCEPTION;
1040 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1042 arp_message.set_line('CSI_PARTY_MERGE_PKG.CSI_T_PARTY_DETAILS_MERGE()+');
1043
1044 x_return_status := FND_API.G_RET_STS_SUCCESS;
1045
1046 begin
1047 select internal_party_id
1048 into v_internal_party_id
1049 from csi_install_parameters;
1050 exception
1051 when no_data_found then
1052 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. Data exists in Installed Base, but Install Parameters are not defined';
1053 raise internal_party_error;
1054 when others then
1055 arp_message.set_line(g_proc_name || '.' ||l_api_name || ': ' ||sqlerrm);
1056 x_return_status := FND_API.G_RET_STS_ERROR;
1057 raise;
1058 end;
1059
1060 if v_internal_party_id = p_from_fk_id or
1061 v_internal_party_id = p_to_fk_id
1062 then
1063 v_internal_party_message := 'Cannot merge party id '||to_char(p_from_fk_id)||' '||'to party id '||to_char(p_to_fk_id)||'. One of the party ids is defined as internal party in CSI-Installed Base';
1064 raise internal_party_error;
1065 end if;
1066
1067 select merge_reason_code
1068 into l_merge_reason_code
1069 from hz_merge_batch
1070 where batch_id = p_batch_id;
1071
1072 if l_merge_reason_code = 'DUPLICATE' then
1073 -- if reason code is duplicate then allow the party merge to
1074 -- happen without any validations.
1075 null;
1076 else
1077 -- if there are any validations to be done, include it in this section
1078 null;
1079 end if;
1080
1081 if p_from_fk_id = p_to_fk_id then
1082 x_to_id := p_from_id;
1083 return;
1084 end if;
1085
1086 IF p_from_fk_id <> p_to_fk_id then
1087
1088 BEGIN
1089
1090 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1091 arp_message.set_token('TABLE_NAME', 'CSI_T_PARTY_DETAILS', FALSE);
1092 l_column_name := 'party_source_id';
1093
1094 open c1;
1095 close c1;
1096
1097 update csi_t_party_details
1098 set party_source_id = p_to_fk_id,
1099 last_update_date = SYSDATE,
1100 last_updated_by = G_USER_ID,
1101 last_update_login = G_LOGIN_ID
1102 where party_source_id = p_from_fk_id
1103 and party_source_table = v_party_source_table;
1104
1105 l_count := sql%rowcount;
1106
1107 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1108 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1109
1110 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1111 arp_message.set_token('TABLE_NAME', 'CSI_T_PARTY_DETAILS', FALSE);
1112
1113 l_column_name := 'contact_party_id';
1114
1115 EXCEPTION
1116 when internal_party_error then
1117 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' ||v_internal_party_message);
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119 raise;
1120 when resource_busy then
1121 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CSI_T_PARTY_DETAILS for '||l_column_name ||' = '|| p_from_fk_id );
1122 x_return_status := FND_API.G_RET_STS_ERROR;
1123 raise;
1124 when others then
1125 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
1126 x_return_status := FND_API.G_RET_STS_ERROR;
1127 raise;
1128 END;
1129 END IF;
1130 END CSI_T_PARTY_DETAILS_MERGE;
1131
1132 PROCEDURE CSI_T_TXN_LINE_DETAILS_MERGE(
1133 p_entity_name IN VARCHAR2,
1134 p_from_id IN NUMBER,
1135 x_to_id OUT NOCOPY NUMBER,
1136 p_from_fk_id IN NUMBER,
1137 p_to_fk_id IN NUMBER,
1138 p_parent_entity_name IN VARCHAR2,
1139 p_batch_id IN NUMBER,
1140 p_batch_party_id IN NUMBER,
1141 x_return_status OUT NOCOPY VARCHAR2)
1142 IS
1143 v_location_type_code VARCHAR2(30) := 'HZ_PARTY_SITES';
1144 v_install_location_type_code VARCHAR2(30) := 'HZ_PARTY_SITES';
1145
1146 cursor c1 is
1147 select 1
1148 from csi_t_txn_line_details
1149 where ( location_id = p_from_fk_id and location_type_code = v_location_type_code )
1150 or ( install_location_id = p_from_fk_id and install_location_type_code = v_location_type_code )
1151 for update nowait;
1152
1153 l_merge_reason_code VARCHAR2(30);
1154 l_api_name VARCHAR2(30) := 'CSI_T_TXN_LINE_DETAILS_MERGE';
1155 l_column_name VARCHAR2(30);
1156 l_count NUMBER(10) := 0;
1157 l_cp_audit_id NUMBER;
1158 v_transaction_type_id NUMBER;
1159 v_transaction_id NUMBER;
1160 v_no_of_rows NUMBER;
1161 v_error_message varchar2(255);
1162 v_internal_party_message varchar2(255);
1163 v_internal_party_id NUMBER;
1164 internal_party_error EXCEPTION;
1165
1166 RESOURCE_BUSY EXCEPTION;
1167 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1168 BEGIN
1169 arp_message.set_line('CSI_PARTY_MERGE_PKG.CSI_T_TXN_LINE_DETAILS_MERGE()+');
1170
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 select merge_reason_code
1174 into l_merge_reason_code
1175 from hz_merge_batch
1176 where batch_id = p_batch_id;
1177
1178 if l_merge_reason_code = 'DUPLICATE' then
1179 -- if reason code is duplicate then allow the party merge to
1180 -- happen without any validations.
1181 null;
1182 else
1183 -- if there are any validations to be done, include it in this section
1184 null;
1185 end if;
1186
1187 if p_from_fk_id = p_to_fk_id then
1188 x_to_id := p_from_id;
1189 return;
1190 end if;
1191
1192 IF p_from_fk_id <> p_to_fk_id then
1193 BEGIN
1194 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1195 arp_message.set_token('TABLE_NAME', 'CSI_T_TXN_LINE_DETAILS', FALSE);
1196 l_column_name := 'location_id';
1197
1198 open c1;
1199 close c1;
1200
1201 update csi_t_txn_line_details
1202 set location_id = decode( location_id, p_from_fk_id, p_to_fk_id, location_id ),
1203 install_location_id = decode( install_location_id, p_from_fk_id, p_to_fk_id, install_location_id ),
1204 last_update_date = SYSDATE,
1205 last_updated_by = G_USER_ID,
1206 last_update_login = G_LOGIN_ID
1207 where ( location_id = p_from_fk_id and location_type_code = v_location_type_code )
1208 or ( install_location_id = p_from_fk_id and install_location_type_code = v_location_type_code );
1209
1210 l_count := sql%rowcount;
1211
1212 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1213 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1214
1215 EXCEPTION
1216 when internal_party_error then
1217 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || v_internal_party_message);
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219 raise;
1220 when resource_busy then
1221 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CSI_T_TXN_LINE_DETAILS for '||l_column_name ||' = '|| p_from_fk_id );
1222 x_return_status := FND_API.G_RET_STS_ERROR;
1223 raise;
1224 when others then
1225 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
1226 x_return_status := FND_API.G_RET_STS_ERROR;
1227 raise;
1228 END;
1229 END IF;
1230 END CSI_T_TXN_LINE_DETAILS_MERGE;
1231
1232 END CSI_PARTY_MERGE_PKG;