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