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