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