DBA Data[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;