[Home] [Help]
PACKAGE BODY: APPS.ASO_MERGE_PVT
Source
1 Package Body ASO_MERGE_PVT As
2 /* $Header: asovmrgb.pls 120.1 2010/12/03 08:18:11 vidsrini ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | |
6 | DESCRIPTION |
7 | This package contains APIs for customer merge and party |
8 | merge for Order Capture. |
9 | REQUIRES |
10 | |
11 | |
12 | EXCEPTIONS RAISED |
13 | |
14 | KNOWN BUGS |
15 | |
16 | NOTES |
17 | |
18 | HISTORY |
19 | Harish Ekkirala Created 03/27/2001. |
20 | Veeru Tarikere 07/18/2002 Rewrote Customer_merge, update_quote_lines |
21 | and update_shipments.Removed Globals |
22 | |
23 *----------------------------------------------------------------------------*/
24
25
26 /*----------------------------------------------------------------------------*
27 | PUBLIC PROCEDURE |
28 | CUSTOMER_MERGE |
29 | DESCRIPTION |
30 | This API should be called from TCA customer merge concurrent |
31 | program and will merge records in Order Capture tables for |
32 | customers that being merged. |
33 | REQUIRES |
34 | |
35 | |
36 | EXCEPTIONS RAISED |
37 | DIFFERENT_PARTIES -- Raises an exception when the owner |
38 | parties are different for the cust |
39 | accounts that are being merged. |
40 | removed (vtariker) |
41 | KNOWN BUGS |
42 | |
43 | NOTES |
44 | |
45 | HISTORY |
46 | Harish Ekkirala Created 03/27/2001. |
47 | Vtariker 07/18/2002 Rewrote Customer_Merge |
48 | |
49 *----------------------------------------------------------------------------*/
50
51 PROCEDURE CUSTOMER_MERGE(
52 req_id NUMBER,
53 set_num NUMBER,
54 process_mode VARCHAR2
55 )
56 IS
57
58
59 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
60 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
61 INDEX BY BINARY_INTEGER;
62 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
63
64 TYPE QUOTE_HEADER_ID_LIST_TYPE IS TABLE OF
65 ASO_QUOTE_HEADERS.QUOTE_HEADER_ID%TYPE
66 INDEX BY BINARY_INTEGER;
67 PRIMARY_KEY_ID_LIST QUOTE_HEADER_ID_LIST_TYPE;
68
69 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
70 ASO_QUOTE_HEADERS.CUST_ACCOUNT_ID%TYPE
71 INDEX BY BINARY_INTEGER;
72 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
73 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
74
75 TYPE INV_TO_CUST_ACCT_ID_LIST_TYPE IS TABLE OF
76 ASO_QUOTE_HEADERS.INVOICE_TO_CUST_ACCOUNT_ID%TYPE
77 INDEX BY BINARY_INTEGER;
78 NUM_COL2_ORIG_LIST INV_TO_CUST_ACCT_ID_LIST_TYPE;
79 NUM_COL2_NEW_LIST INV_TO_CUST_ACCT_ID_LIST_TYPE;
80
81 TYPE END_CUST_ACCT_ID_LIST_TYPE IS TABLE OF
82 ASO_QUOTE_HEADERS.END_CUSTOMER_CUST_ACCOUNT_ID%TYPE
83 INDEX BY BINARY_INTEGER;
84 NUM_COL3_ORIG_LIST END_CUST_ACCT_ID_LIST_TYPE;
85 NUM_COL3_NEW_LIST END_CUST_ACCT_ID_LIST_TYPE;
86
87 l_profile_val VARCHAR2(30);
88 CURSOR merged_records IS
89 SELECT /*+ leading(M) use_nl(M,YT) USE_CONCAT */
90 distinct CUSTOMER_MERGE_HEADER_ID
91 ,QUOTE_HEADER_ID
92 ,CUST_ACCOUNT_ID
93 ,INVOICE_TO_CUST_ACCOUNT_ID
94 ,END_CUSTOMER_CUST_ACCOUNT_ID
95 FROM ASO_QUOTE_HEADERS yt, ra_customer_merges m
96 WHERE (
97 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
98 OR yt.INVOICE_TO_CUST_ACCOUNT_ID = m.DUPLICATE_ID
99 OR yt.END_CUSTOMER_CUST_ACCOUNT_ID = m.DUPLICATE_ID
100 ) AND m.process_flag = 'N'
101 AND m.request_id = req_id
105
102 AND m.set_number = set_num;
103 l_last_fetch BOOLEAN := FALSE;
104 l_count NUMBER;
106 BEGIN
107
108 IF process_mode='LOCK' THEN
109
110 NULL;
111
112 ELSE
113
114 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
115 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ASO_QUOTE_HEADERS',FALSE);
116 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
117 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
118
119 open merged_records;
120
121 LOOP
122 FETCH merged_records BULK COLLECT INTO
123 MERGE_HEADER_ID_LIST
124 , PRIMARY_KEY_ID_LIST
125 , NUM_COL1_ORIG_LIST
126 , NUM_COL2_ORIG_LIST
127 , NUM_COL3_ORIG_LIST
128 limit 1000;
129 IF merged_records%NOTFOUND THEN
130 l_last_fetch := TRUE;
131 END IF;
132
133 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
134 exit;
135 END IF;
136
137 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
138 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
139 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
140 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL3_ORIG_LIST(I));
141 END LOOP;
142
143 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
144 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
145 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
146 MERGE_LOG_ID,
147 TABLE_NAME,
148 MERGE_HEADER_ID,
149 PRIMARY_KEY_ID,
150 NUM_COL1_ORIG,
151 NUM_COL1_NEW,
152 NUM_COL2_ORIG,
153 NUM_COL2_NEW,
154 NUM_COL3_ORIG,
155 NUM_COL3_NEW,
156 ACTION_FLAG,
157 REQUEST_ID,
158 CREATED_BY,
159 CREATION_DATE,
160 LAST_UPDATE_LOGIN,
161 LAST_UPDATE_DATE,
162 LAST_UPDATED_BY
163 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
164 'ASO_QUOTE_HEADERS',
165 MERGE_HEADER_ID_LIST(I),
166 PRIMARY_KEY_ID_LIST(I),
167 NUM_COL1_ORIG_LIST(I),
168 NUM_COL1_NEW_LIST(I),
169 NUM_COL2_ORIG_LIST(I),
170 NUM_COL2_NEW_LIST(I),
171 NUM_COL3_ORIG_LIST(I),
172 NUM_COL3_NEW_LIST(I),
173 'U',
174 req_id,
175 hz_utility_pub.CREATED_BY,
176 hz_utility_pub.CREATION_DATE,
177 hz_utility_pub.LAST_UPDATE_LOGIN,
178 hz_utility_pub.LAST_UPDATE_DATE,
179 hz_utility_pub.LAST_UPDATED_BY
180 );
181
182 END IF;
183
184 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
185 UPDATE ASO_QUOTE_HEADERS yt SET
186 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
187 ,INVOICE_TO_CUST_ACCOUNT_ID=NUM_COL2_NEW_LIST(I)
188 ,END_CUSTOMER_CUST_ACCOUNT_ID=NUM_COL3_NEW_LIST(I)
189 , LAST_UPDATE_DATE=SYSDATE
190 , last_updated_by=arp_standard.profile.user_id
191 , last_update_login=arp_standard.profile.last_update_login
192 , REQUEST_ID=req_id
193 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
194 , PROGRAM_ID=arp_standard.profile.program_id
195 , PROGRAM_UPDATE_DATE=SYSDATE
196 WHERE QUOTE_HEADER_ID=PRIMARY_KEY_ID_LIST(I)
197 ;
198
199 l_count := l_count + SQL%ROWCOUNT;
200
201 IF l_last_fetch THEN
202 EXIT;
203 END IF;
204
205 END LOOP;
206
207 arp_message.set_name('AR','AR_ROWS_UPDATED');
208 arp_message.set_token('NUM_ROWS',to_char(l_count));
209
210 END IF;
211
212 ASO_MERGE_PVT.UPDATE_QUOTE_LINES(
213 req_id => req_id,
214 set_num => set_num,
215 process_mode => process_mode
216 );
217
218 ASO_MERGE_PVT.UPDATE_SHIPMENTS(
219 req_id => req_id,
220 set_num => set_num,
221 process_mode => process_mode
222 );
223
224
225 EXCEPTION
226
227 WHEN OTHERS THEN
228
229 arp_message.set_line( 'CUSTOMER_MERGE');
230 RAISE;
231
232 END CUSTOMER_MERGE;
233
234
235
236 /*----------------------------------------------------------------------------*
237 | PRIVATE PROCEDURE |
238 | UPDATE_QUOTE_LINES |
239 | DESCRIPTION |
240 | This is a private procedure to update ASO_QUOTE_LINES_ALL |
241 | table with merged to cust account id. When two cust accounts |
242 | are merged. |
243 | REQUIRES |
244 | |
245 | |
246 | EXCEPTIONS RAISED |
247 | |
248 | KNOWN BUGS |
249 | |
250 | NOTES |
254 | Vtariker 07/18/2002 Rewrote Update_Quote_lines |
251 | |
252 | HISTORY |
253 | Harish Ekkirala Created 03/27/2001. |
255 | |
256 *----------------------------------------------------------------------------*/
257 PROCEDURE UPDATE_QUOTE_LINES(
258 req_id NUMBER,
259 set_num NUMBER,
260 process_mode VARCHAR2
261 ) IS
262
263 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
264 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
265 INDEX BY BINARY_INTEGER;
266 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
267
268 TYPE QUOTE_LINE_ID_LIST_TYPE IS TABLE OF
269 ASO_QUOTE_LINES.QUOTE_LINE_ID%TYPE
270 INDEX BY BINARY_INTEGER;
271 PRIMARY_KEY_ID_LIST QUOTE_LINE_ID_LIST_TYPE;
272
273 TYPE INV_TO_CUST_ACCT_ID_LIST_TYPE IS TABLE OF
274 ASO_QUOTE_LINES.INVOICE_TO_CUST_ACCOUNT_ID%TYPE
275 INDEX BY BINARY_INTEGER;
276 NUM_COL1_ORIG_LIST INV_TO_CUST_ACCT_ID_LIST_TYPE;
277 NUM_COL1_NEW_LIST INV_TO_CUST_ACCT_ID_LIST_TYPE;
278
279 TYPE END_CUST_ACCT_ID_LIST_TYPE IS TABLE OF
280 ASO_QUOTE_LINES.END_CUSTOMER_CUST_ACCOUNT_ID%TYPE
281 INDEX BY BINARY_INTEGER;
282 NUM_COL2_ORIG_LIST END_CUST_ACCT_ID_LIST_TYPE;
283 NUM_COL2_NEW_LIST END_CUST_ACCT_ID_LIST_TYPE;
284
285 l_profile_val VARCHAR2(30);
286
287 CURSOR merged_records IS
288 SELECT distinct CUSTOMER_MERGE_HEADER_ID
289 ,QUOTE_LINE_ID
290 ,INVOICE_TO_CUST_ACCOUNT_ID
291 ,END_CUSTOMER_CUST_ACCOUNT_ID
292 FROM ASO_QUOTE_LINES yt, ra_customer_merges m
293 WHERE (
294 yt.INVOICE_TO_CUST_ACCOUNT_ID = m.DUPLICATE_ID
295 OR yt.END_CUSTOMER_CUST_ACCOUNT_ID = m.DUPLICATE_ID
296 ) AND m.process_flag = 'N'
297 AND m.request_id = req_id
298 AND m.set_number = set_num;
299
300 l_last_fetch BOOLEAN := FALSE;
301 l_count NUMBER;
302
303 BEGIN
304
305 IF process_mode='LOCK' THEN
306 NULL;
307 ELSE
308
309 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
310 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ASO_QUOTE_LINES',FALSE);
311 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
312 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
313
314 open merged_records;
315
316 LOOP
317
318 FETCH merged_records BULK COLLECT INTO
319 MERGE_HEADER_ID_LIST
320 , PRIMARY_KEY_ID_LIST
321 , NUM_COL1_ORIG_LIST
322 , NUM_COL2_ORIG_LIST
323 limit 1000;
324
325 IF merged_records%NOTFOUND THEN
326 l_last_fetch := TRUE;
327 END IF;
328
329 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
330 exit;
331 END IF;
332
333 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
334 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
335 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
336 END LOOP;
337
338 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
339 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
340 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
341 MERGE_LOG_ID,
342 TABLE_NAME,
343 MERGE_HEADER_ID,
344 PRIMARY_KEY_ID,
345 NUM_COL1_ORIG,
346 NUM_COL1_NEW,
347 NUM_COL2_ORIG,
348 NUM_COL2_NEW,
349 ACTION_FLAG,
350 REQUEST_ID,
351 CREATED_BY,
352 CREATION_DATE,
353 LAST_UPDATE_LOGIN,
354 LAST_UPDATE_DATE,
355 LAST_UPDATED_BY
356 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
357 'ASO_QUOTE_LINES',
358 MERGE_HEADER_ID_LIST(I),
359 PRIMARY_KEY_ID_LIST(I),
360 NUM_COL1_ORIG_LIST(I),
361 NUM_COL1_NEW_LIST(I),
362 NUM_COL2_ORIG_LIST(I),
363 NUM_COL2_NEW_LIST(I),
364 'U',
365 req_id,
366 hz_utility_pub.CREATED_BY,
367 hz_utility_pub.CREATION_DATE,
368 hz_utility_pub.LAST_UPDATE_LOGIN,
369 hz_utility_pub.LAST_UPDATE_DATE,
370 hz_utility_pub.LAST_UPDATED_BY
371 );
372
373 END IF;
374
375 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
376 UPDATE ASO_QUOTE_LINES yt SET
377 INVOICE_TO_CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
378 , END_CUSTOMER_CUST_ACCOUNT_ID=NUM_COL2_NEW_LIST(I)
379 , LAST_UPDATE_DATE=SYSDATE
380 , last_updated_by=arp_standard.profile.user_id
381 , last_update_login=arp_standard.profile.last_update_login
382 , REQUEST_ID=req_id
383 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
384 , PROGRAM_ID=arp_standard.profile.program_id
385 , PROGRAM_UPDATE_DATE=SYSDATE
386 WHERE QUOTE_LINE_ID=PRIMARY_KEY_ID_LIST(I)
387 ;
388
389 l_count := l_count + SQL%ROWCOUNT;
390
391 IF l_last_fetch THEN
392 EXIT;
393 END IF;
394
395 END LOOP;
396
397 arp_message.set_name('AR','AR_ROWS_UPDATED');
398 arp_message.set_token('NUM_ROWS',to_char(l_count));
399
400 END IF;
401
405
402 EXCEPTION
403
404 WHEN OTHERS THEN
406 arp_message.set_line( 'UPDATE_QUOTE_LINES');
407 RAISE;
408
409 END UPDATE_QUOTE_LINES;
410
411
412
413 /*----------------------------------------------------------------------------*
414 | PRIVATE PROCEDURE |
415 | UPDATE_SHIPMENTS |
416 | DESCRIPTION |
417 | This is a private procedure to update ASO_SHIPMENTS |
418 | table with merged to cust account id. When two cust accounts |
419 | are merged. |
420 | REQUIRES |
421 | |
422 | |
423 | EXCEPTIONS RAISED |
424 | |
425 | KNOWN BUGS |
426 | |
427 | NOTES |
428 | |
429 | HISTORY |
430 | Harish Ekkirala Created 03/27/2001. |
431 | Vtariker 07/18/2002 Rewrote Update_Shipments |
432 | |
433 *----------------------------------------------------------------------------*/
434 PROCEDURE UPDATE_SHIPMENTS(
435 req_id NUMBER,
436 set_num NUMBER,
437 process_mode VARCHAR2
438 ) IS
439
440 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
441 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
442 INDEX BY BINARY_INTEGER;
443 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
444
445 TYPE SHIPMENT_ID_LIST_TYPE IS TABLE OF
446 ASO_SHIPMENTS.SHIPMENT_ID%TYPE
447 INDEX BY BINARY_INTEGER;
448 PRIMARY_KEY_ID_LIST SHIPMENT_ID_LIST_TYPE;
449
450 TYPE SHIP_TO_CUST_ACCT_ID_LIST_TYPE IS TABLE OF
451 ASO_SHIPMENTS.SHIP_TO_CUST_ACCOUNT_ID%TYPE
452 INDEX BY BINARY_INTEGER;
453 NUM_COL1_ORIG_LIST SHIP_TO_CUST_ACCT_ID_LIST_TYPE;
454 NUM_COL1_NEW_LIST SHIP_TO_CUST_ACCT_ID_LIST_TYPE;
455
456 l_profile_val VARCHAR2(30);
457
458 CURSOR merged_records IS
459 SELECT distinct CUSTOMER_MERGE_HEADER_ID
460 ,SHIPMENT_ID
461 ,SHIP_TO_CUST_ACCOUNT_ID
462 FROM ASO_SHIPMENTS yt, ra_customer_merges m
463 WHERE (
464 yt.SHIP_TO_CUST_ACCOUNT_ID = m.DUPLICATE_ID
465 ) AND m.process_flag = 'N'
466 AND m.request_id = req_id
467 AND m.set_number = set_num;
468
469 l_last_fetch BOOLEAN := FALSE;
470 l_count NUMBER;
471
472 BEGIN
473
474 IF process_mode='LOCK' THEN
475 NULL;
476 ELSE
477 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
478 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ASO_SHIPMENTS',FALSE);
479 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
480 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
481
482 open merged_records;
483
484 LOOP
485 FETCH merged_records BULK COLLECT INTO
486 MERGE_HEADER_ID_LIST
487 , PRIMARY_KEY_ID_LIST
488 , NUM_COL1_ORIG_LIST
489 limit 1000;
490
491 IF merged_records%NOTFOUND THEN
492 l_last_fetch := TRUE;
493 END IF;
494
495 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
496 exit;
497 END IF;
498
499 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
500 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
501 END LOOP;
502
503 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
504 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
505 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
506 MERGE_LOG_ID,
507 TABLE_NAME,
508 MERGE_HEADER_ID,
509 PRIMARY_KEY_ID,
510 NUM_COL1_ORIG,
511 NUM_COL1_NEW,
512 ACTION_FLAG,
513 REQUEST_ID,
514 CREATED_BY,
515 CREATION_DATE,
516 LAST_UPDATE_LOGIN,
517 LAST_UPDATE_DATE,
518 LAST_UPDATED_BY
519 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
520 'ASO_SHIPMENTS',
521 MERGE_HEADER_ID_LIST(I),
522 PRIMARY_KEY_ID_LIST(I),
523 NUM_COL1_ORIG_LIST(I),
524 NUM_COL1_NEW_LIST(I),
525 'U',
526 req_id,
527 hz_utility_pub.CREATED_BY,
528 hz_utility_pub.CREATION_DATE,
529 hz_utility_pub.LAST_UPDATE_LOGIN,
530 hz_utility_pub.LAST_UPDATE_DATE,
531 hz_utility_pub.LAST_UPDATED_BY
532 );
533
534
535 END IF;
536
537 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
538 UPDATE ASO_SHIPMENTS yt SET
539 SHIP_TO_CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
540 , LAST_UPDATE_DATE=SYSDATE
541 , last_updated_by=arp_standard.profile.user_id
542 , last_update_login=arp_standard.profile.last_update_login
543 , REQUEST_ID=req_id
544 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
545 , PROGRAM_ID=arp_standard.profile.program_id
546 , PROGRAM_UPDATE_DATE=SYSDATE
547 WHERE SHIPMENT_ID=PRIMARY_KEY_ID_LIST(I)
548 ;
549 l_count := l_count + SQL%ROWCOUNT;
550
551 IF l_last_fetch THEN
552 EXIT;
553 END IF;
554
555 END LOOP;
556
557 arp_message.set_name('AR','AR_ROWS_UPDATED');
558 arp_message.set_token('NUM_ROWS',to_char(l_count));
559
560 END IF;
561
562 EXCEPTION
563
564 WHEN OTHERS THEN
565
566 arp_message.set_line( 'UPDATE_SHIPMENTS');
567 RAISE;
568
569 END UPDATE_SHIPMENTS;
570
571
572
573 /*----------------------------------------------------------------------------*
574 | PUBLIC PROCEDURES |
575 | MERGE_QUOTE_HEADERS -- |
576 | When in ERP Parties are merged the |
577 | The Foriegn keys to party_id and other columns |
578 | should also be updated in iStore tables. |
579 | This procedure will update ASO_QUOTE_HEADERS_ALL table |
580 | and will be called from party Merge concurrent program. |
581 | DESCRIPTION |
582 | |
583 | REQUIRES |
584 | |
585 | |
586 | EXCEPTIONS RAISED |
587 | |
588 | KNOWN BUGS |
589 | |
590 | NOTES |
591 | |
592 | HISTORY |
593 | |
594 *----------------------------------------------------------------------------*/
595
596 PROCEDURE MERGE_QUOTE_HEADERS(
597 P_entity_name IN VARCHAR2,
598 P_from_id IN NUMBER,
599 X_to_id OUT NOCOPY NUMBER,
600 P_from_fk_id IN NUMBER,
601 P_to_fk_id IN NUMBER,
602 P_parent_entity_name IN VARCHAR2,
603 P_batch_id IN NUMBER,
604 P_batch_party_id IN NUMBER,
605 X_return_status OUT NOCOPY VARCHAR2
606 ) IS
607
608 l_merge_reason_code VARCHAR2(30);
609 l_count NUMBER(10) := 0;
610
611 RESOURCE_BUSY EXCEPTION;
612 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
613
614 BEGIN
615
616 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_HEADERS()+');
617
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619
620 /* Perform the merge operation */
621
622 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
623 as Merged from id and return */
624
625 if p_from_fk_id = p_to_fk_id then
626 x_to_id := p_from_id;
627 return;
628 End If;
629
630
631 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent. */
632
633 if p_from_fk_id <> p_to_fk_id Then
634
635 IF p_parent_entity_name = 'HZ_PARTIES' Then
636
637 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
638 arp_message.set_token('TABLE_NAME','ASO_QUOTE_HEADERS_ALL', FALSE);
639
640 UPDATE ASO_QUOTE_HEADERS_ALL SET
641 party_id = DECODE(party_id,p_from_fk_id,p_to_fk_id,party_id),
642 invoice_to_party_id = DECODE(invoice_to_party_id,p_from_fk_id,p_to_fk_id,invoice_to_party_id),
643 cust_party_id = DECODE(cust_party_id,p_from_fk_id,p_to_fk_id,cust_party_id),
644 invoice_to_cust_party_id = DECODE(invoice_to_cust_party_id,p_from_fk_id,p_to_fk_id,invoice_to_cust_party_id),
645 End_Customer_party_id = DECODE(End_Customer_party_id,p_from_fk_id,p_to_fk_id,End_Customer_party_id),
646 End_Customer_cust_party_id = DECODE(End_Customer_cust_party_id,p_from_fk_id,p_to_fk_id,End_Customer_cust_party_id),
647 last_update_date = hz_utility_pub.last_update_date,
648 last_updated_by = hz_utility_pub.user_id,
649 last_update_login = hz_utility_pub.last_update_login,
650 request_id = hz_utility_pub.request_id,
651 program_application_id = hz_utility_pub.program_application_id,
652 program_id = hz_utility_pub.program_id,
653 program_update_date = sysdate
654 Where party_id = p_from_fk_id
655 OR invoice_to_party_id = p_from_fk_id
656 OR cust_party_id = p_from_fk_id
657 OR invoice_to_cust_party_id = p_from_fk_id;
658
659 l_count := sql%rowcount;
660
661 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
662 arp_message.set_token('NUM_ROWS', to_char(l_count) );
663
664 return;
665
666 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN
667
668 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
669 arp_message.set_token('TABLE_NAME','ASO_QUOTE_HEADERS_ALL', FALSE);
670
671 UPDATE ASO_QUOTE_HEADERS_ALL SET
672 invoice_to_party_site_id = DECODE(invoice_to_party_site_id,p_from_fk_id,p_to_fk_id,invoice_to_party_site_id),
673 End_Customer_party_site_id = DECODE(End_Customer_party_site_id,p_from_fk_id,p_to_fk_id,End_Customer_party_site_id),
674 sold_to_party_site_id = DECODE(sold_to_party_site_id,p_from_fk_id,p_to_fk_id,sold_to_party_site_id),
675 last_update_date = hz_utility_pub.last_update_date,
676 last_updated_by = hz_utility_pub.user_id,
677 last_update_login = hz_utility_pub.last_update_login,
678 request_id = hz_utility_pub.request_id,
679 program_application_id = hz_utility_pub.program_application_id,
680 program_id = hz_utility_pub.program_id,
681 program_update_date = sysdate
682 Where invoice_to_party_site_id = p_from_fk_id
683 OR End_Customer_party_site_id = p_from_fk_id
684 OR sold_to_party_site_id = p_from_fk_id;
685
686 l_count := sql%rowcount;
687
688 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
689 arp_message.set_token('NUM_ROWS', to_char(l_count) );
690
691 return;
692
693 ELSIF p_parent_entity_name = 'HZ_ORG_CONTACTS' THEN
694
695 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
696 arp_message.set_token('TABLE_NAME','ASO_QUOTE_HEADERS_ALL', FALSE);
697
698 UPDATE ASO_QUOTE_HEADERS_ALL SET
699 org_contact_id = p_to_fk_id,
700 last_update_date = hz_utility_pub.last_update_date,
701 last_updated_by = hz_utility_pub.user_id,
702 last_update_login = hz_utility_pub.last_update_login,
703 request_id = hz_utility_pub.request_id,
704 program_application_id = hz_utility_pub.program_application_id,
705 program_id = hz_utility_pub.program_id,
706 program_update_date = sysdate
707 Where org_contact_id = p_from_fk_id;
708
709 l_count := sql%rowcount;
710
711 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
712 arp_message.set_token('NUM_ROWS', to_char(l_count) );
713
714 return;
715
716
717 END IF;
718
719 End If;
720
721 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_HEADERS()-');
722
723 Exception
724 When RESOURCE_BUSY Then
725 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_HEADERS; Could not obtain lock'||
729 raise;
726 'on table ASO_QUOTE_HEADERS_ALL');
727
728 x_return_status := FND_API.G_RET_STS_ERROR;
730 When Others Then
731 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_HEADERS'||sqlerrm);
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 raise;
734 END MERGE_QUOTE_HEADERS;
735
736 /*----------------------------------------------------------------------------*
737 | PUBLIC PROCEDURES |
738 | MERGE_QUOTE_LINES -- |
739 | When in ERP Parties are merged the |
740 | The Foriegn keys to party_id and other columns |
741 | should also be updated in iStore tables. |
742 | This procedure will update ASO_QUOTE_LINES_ALL table |
743 | and will be called from party Merge concurrent program. |
744 | DESCRIPTION |
745 | |
746 | REQUIRES |
747 | |
748 | |
749 | EXCEPTIONS RAISED |
750 | |
751 | KNOWN BUGS |
752 | |
753 | NOTES |
754 | |
755 | HISTORY |
756 | |
757 *----------------------------------------------------------------------------*/
758
759 PROCEDURE MERGE_QUOTE_LINES(
760 P_entity_name IN VARCHAR2,
761 P_from_id IN NUMBER,
762 X_to_id OUT NOCOPY NUMBER,
763 P_from_fk_id IN NUMBER,
764 P_to_fk_id IN NUMBER,
765 P_parent_entity_name IN VARCHAR2,
766 P_batch_id IN NUMBER,
767 P_batch_party_id IN NUMBER,
768 X_return_status OUT NOCOPY VARCHAR2
769 ) IS
770
771 l_merge_reason_code VARCHAR2(30);
772 l_count NUMBER(10) := 0;
773
774 RESOURCE_BUSY EXCEPTION;
775 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
776
777 BEGIN
778
779 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_LINES()+');
780
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 /* Perform the merge operation */
784
785 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
786 as Merged from id and return */
787
788 if p_from_fk_id = p_to_fk_id then
789 x_to_id := p_from_id;
790 return;
791 End If;
792
793
794 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent. */
795
796 if p_from_fk_id <> p_to_fk_id Then
797
798 If p_parent_entity_name = 'HZ_PARTIES' Then
799
800 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
801 arp_message.set_token('TABLE_NAME','ASO_QUOTE_LINES_ALL', FALSE);
802
803 UPDATE ASO_QUOTE_LINES_ALL SET
804 invoice_to_party_id = DECODE(invoice_to_party_id,p_from_fk_id,p_to_fk_id,invoice_to_party_id),
805 invoice_to_cust_party_id = DECODE(invoice_to_cust_party_id,p_from_fk_id,p_to_fk_id,invoice_to_cust_party_id),
806 End_Customer_party_id = DECODE(End_Customer_party_id,p_from_fk_id,p_to_fk_id,End_Customer_party_id),
807 End_Customer_cust_party_id = DECODE(End_Customer_cust_party_id,p_from_fk_id,p_to_fk_id,End_Customer_cust_party_id),
808 last_update_date = hz_utility_pub.last_update_date,
809 last_updated_by = hz_utility_pub.user_id,
810 last_update_login = hz_utility_pub.last_update_login,
811 request_id = hz_utility_pub.request_id,
812 program_application_id = hz_utility_pub.program_application_id,
813 program_id = hz_utility_pub.program_id,
814 program_update_date = sysdate
815 Where invoice_to_party_id = p_from_fk_id
816 OR invoice_to_cust_party_id = p_from_fk_id
817 OR End_Customer_cust_party_id = p_from_fk_id;
818
819 l_count := sql%rowcount;
820
821 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
822 arp_message.set_token('NUM_ROWS', to_char(l_count) );
823
824 return;
825
826 Elsif p_parent_entity_name = 'HZ_PARTY_SITES' Then
827
828 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
829 arp_message.set_token('TABLE_NAME','ASO_QUOTE_LINES_ALL', FALSE);
830
831 UPDATE ASO_QUOTE_LINES_ALL SET
832 invoice_to_party_site_id = p_to_fk_id,
833 End_Customer_party_site_id = p_to_fk_id,
834 last_update_date = hz_utility_pub.last_update_date,
835 last_updated_by = hz_utility_pub.user_id,
836 last_update_login = hz_utility_pub.last_update_login,
837 request_id = hz_utility_pub.request_id,
838 program_application_id = hz_utility_pub.program_application_id,
839 program_id = hz_utility_pub.program_id,
840 program_update_date = sysdate
841 Where invoice_to_party_site_id = p_from_fk_id
842 OR End_Customer_party_site_id = p_from_fk_id;
843
844 l_count := sql%rowcount;
845
846 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
847 arp_message.set_token('NUM_ROWS', to_char(l_count) );
848
849 return;
850
851 End If;
852
853 End If;
854
858 When RESOURCE_BUSY Then
855 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_LINES()-');
856
857 Exception
859 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_LINES; Could not obtain lock'||
860 'on table ASO_QUOTE_LINES_ALL');
861
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 raise;
864 When Others Then
865 arp_message.set_line('ASO_MERGE_PVT.MERGE_QUOTE_LINES'||sqlerrm);
866 x_return_status := FND_API.G_RET_STS_ERROR;
867 raise;
868
869 END MERGE_QUOTE_LINES;
870
871 /*----------------------------------------------------------------------------*
872 | PUBLIC PROCEDURES |
873 | MERGE_SHIPMENTS-- |
874 | When in ERP Parties are merged the |
875 | The Foriegn keys to party_id and other columns |
876 | should also be updated in iStore tables. |
877 | This procedure will update ASO_SHIPMENTS table |
878 | and will be called from party Merge concurrent program. |
879 | DESCRIPTION |
880 | |
881 | REQUIRES |
882 | |
883 | |
884 | EXCEPTIONS RAISED |
885 | |
886 | KNOWN BUGS |
887 | |
888 | NOTES |
889 | |
890 | HISTORY |
891 | |
892 *----------------------------------------------------------------------------*/
893
894 PROCEDURE MERGE_SHIPMENTS(
895 P_entity_name IN VARCHAR2,
896 P_from_id IN NUMBER,
897 X_to_id OUT NOCOPY NUMBER,
898 P_from_fk_id IN NUMBER,
899 P_to_fk_id IN NUMBER,
900 P_parent_entity_name IN VARCHAR2,
901 P_batch_id IN NUMBER,
902 P_batch_party_id IN NUMBER,
903 X_return_status OUT NOCOPY VARCHAR2
904 ) IS
905
906 l_merge_reason_code VARCHAR2(30);
907 l_count NUMBER(10) := 0;
908
909 RESOURCE_BUSY EXCEPTION;
910 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
911
912 BEGIN
913
914 arp_message.set_line('ASO_MERGE_PVT.MERGE_SHIPMENTS()+');
915
916 x_return_status := FND_API.G_RET_STS_SUCCESS;
917
918 /* Perform the merge operation */
919
920 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
921 as Merged from id and return */
922
923 if p_from_fk_id = p_to_fk_id then
924 x_to_id := p_from_id;
925 return;
926 End If;
927
928
929 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent. */
930
931 if p_from_fk_id <> p_to_fk_id Then
932
933 If p_parent_entity_name = 'HZ_PARTIES' Then
934
935 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
936 arp_message.set_token('TABLE_NAME','ASO_SHIPMENTS', FALSE);
937
938 UPDATE ASO_SHIPMENTS SET
939 ship_to_party_id = DECODE(ship_to_party_id,p_from_fk_id,p_to_fk_id,ship_to_party_id),
940 ship_to_cust_party_id = DECODE(ship_to_cust_party_id,p_from_fk_id,p_to_fk_id,ship_to_cust_party_id),
941 last_update_date = hz_utility_pub.last_update_date,
942 last_updated_by = hz_utility_pub.user_id,
943 last_update_login = hz_utility_pub.last_update_login,
944 request_id = hz_utility_pub.request_id,
945 program_application_id = hz_utility_pub.program_application_id,
946 program_id = hz_utility_pub.program_id,
947 program_update_date = sysdate
948 Where ship_to_party_id = p_from_fk_id
949 OR ship_to_cust_party_id = p_from_fk_id;
950
951 l_count := sql%rowcount;
952
953 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
954 arp_message.set_token('NUM_ROWS', to_char(l_count) );
955
956 return;
957
958 Elsif p_parent_entity_name = 'HZ_PARTY_SITES' Then
959
960 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
961 arp_message.set_token('TABLE_NAME','ASO_SHIPMENTS', FALSE);
962
963 UPDATE ASO_SHIPMENTS SET
964 ship_to_party_site_id = p_to_fk_id,
965 last_update_date = hz_utility_pub.last_update_date,
966 last_updated_by = hz_utility_pub.user_id,
967 last_update_login = hz_utility_pub.last_update_login,
968 request_id = hz_utility_pub.request_id,
969 program_application_id = hz_utility_pub.program_application_id,
970 program_id = hz_utility_pub.program_id,
971 program_update_date = sysdate
972 Where ship_to_party_site_id = p_from_fk_id;
973
974 l_count := sql%rowcount;
975
976 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
977 arp_message.set_token('NUM_ROWS', to_char(l_count) );
978
979 return;
980
981 End If;
982
983 End If;
984
985 arp_message.set_line('ASO_MERGE_PVT.MERGE_SHIPMENTS()-');
986
987 Exception
988 When RESOURCE_BUSY Then
989 arp_message.set_line('ASO_MERGE_PVT.MERGE_SHIPMENTS; Could not obtain lock'||
993 raise;
990 'on table ASO_SHIPMENTS');
991
992 x_return_status := FND_API.G_RET_STS_ERROR;
994 When Others Then
995 arp_message.set_line('ASO_MERGE_PVT.MERGE_SHIPMENTS'||sqlerrm);
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 raise;
998
999 END MERGE_SHIPMENTS;
1000
1001 END ASO_MERGE_PVT;