[Home] [Help]
PACKAGE BODY: APPS.IBE_MERGE_PVT
Source
1 Package Body IBE_MERGE_PVT As
2 /* $Header: IBEVMRGB.pls 120.0 2005/05/30 02:40:49 appldev noship $ */
3
4 G_FETCH_LIMIT CONSTANT NUMBER := 1000;
5 /*----------------------------------------------------------------------------*
6 | PRIVATE FUNCTION: |
7 | Allow_merge |
8 | DESCRIPTION |
9 | This function takes customer_id and duplicate_id as inputs |
10 | returns 'Y' if the cust accounts belongs to same parties. |
11 | REQUIRES |
12 | |
13 | |
14 | EXCEPTIONS RAISED |
15 | |
16 | KNOWN BUGS |
17 | |
18 | NOTES |
19 | |
20 | HISTORY |
21 | Harish Ekkirala Created 03/27/2001. |
22 | |
23 *----------------------------------------------------------------------------*/
24 Function Allow_merge(p_customer_id NUMBER,
25 p_duplicate_id NUMBER) Return Varchar2
26 IS
27 l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
28 l_party_id HZ_PARTIES.PARTY_ID%TYPE;
29 l_dup_party_id HZ_PARTIES.PARTY_ID%TYPE;
30 l_rel_party_id HZ_PARTIES.PARTY_ID%TYPE;
31 l_user_id FND_USER.USER_ID%TYPE;
32
33 CURSOR party_rel(p_party_id NUMBER) IS
34 Select party_id
35 From HZ_RELATIONSHIPS
36 Where object_id = p_party_id
37 and subject_type='PERSON' and object_type='ORGANIZATION';
38
39 Begin
40
41 Select party_type,party_id into l_party_type,l_party_id
42 From hz_parties
43 Where party_id in (Select party_id from hz_cust_accounts
44 Where cust_account_id = p_customer_id);
45
46 Select party_id into l_dup_party_id
47 From hz_parties
48 Where party_id in (Select party_id from hz_cust_accounts
49 Where cust_account_id = p_duplicate_id);
50
51
52 If ((l_Party_type = 'ORGANIZATION') AND (l_party_id <> l_dup_party_id)) Then
53
54 Open party_rel(l_party_id);
55 Loop
56 Fetch party_rel into l_rel_party_id;
57 EXIT When party_rel%NOTFOUND;
58
59 Begin
60
61 Select user_id into l_user_id
62 From fnd_user_resp_groups
63 Where user_id in (Select user_id from fnd_user
64 Where customer_id = l_rel_party_id)
65 And responsibility_application_id = 671;
66 Exception
67 When NO_DATA_FOUND Then
68 l_user_id := Null;
69 End;
70
71 If l_user_id is NOT NULL Then
72 return ('N');
73 End IF;
74 End Loop;
75 Close party_rel;
76 End If;
77
78 Return('Y');
79 End Allow_merge;
80
81
82 FUNCTION find_party (
83 p_account_id NUMBER) RETURN NUMBER IS
84 l_party_id NUMBER :=0 ;
85 BEGIN
86 select PARTY_ID INTO l_party_id
87 from hz_cust_accounts
88 where cust_account_id=p_account_id;
89 IF l_party_id IS NULL THEN
90 RETURN l_party_id;
91 END IF;
92 RETURN l_party_id;
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 return l_party_id;
96 END;
97
98 /*-------------------------------------------------------------
99 |
100 | PROCEDURE
101 | acc_merge_oneclick
102 | DESCRIPTION :
103 | Account merge procedure for the table, IBE_ORD_ONECLICK_ALL
104 |
105 |
106 |--------------------------------------------------------------*/
107 procedure acc_merge_oneclick (
108 req_id NUMBER,
109 set_num NUMBER,
110 Process_MODE VARCHAR2)
111 IS
112 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
113 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
114 INDEX BY BINARY_INTEGER;
115 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
116
117 TYPE ORD_ONECLICK_ID_LIST_TYPE IS TABLE OF
118 IBE_ORD_ONECLICK_ALL.ORD_ONECLICK_ID%TYPE
119 INDEX BY BINARY_INTEGER;
120 PRIMARY_KEY_ID_LIST ORD_ONECLICK_ID_LIST_TYPE;
121
122 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
123 IBE_ORD_ONECLICK_ALL.CUST_ACCOUNT_ID%TYPE
124 INDEX BY BINARY_INTEGER;
125 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
126 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
127
128 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
129 IBE_ORD_ONECLICK_ALL.PARTY_ID%TYPE
130 INDEX BY BINARY_INTEGER;
131 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
132 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
133 l_acct_id number;
134 l_profile_val VARCHAR2(30);
135 l_ord_oneclick_id IBE_ORD_ONECLICK_ALL.ORD_ONECLICK_ID%TYPE;
136
137 --cursor to get <merge to> party, account ID and primary key for shopping lists
138 CURSOR merged_records IS
139 SELECT distinct CUSTOMER_MERGE_HEADER_ID
140 ,yt.ord_oneclick_id
141 ,yt.CUST_ACCOUNT_ID
142 ,yt.party_id
143 FROM IBE_ORD_ONECLICK_ALL yt, ra_customer_merges m
144 WHERE
145 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
146 AND m.process_flag = 'N'
147 AND m.request_id = req_id
148 AND m.set_number = set_num;
149 l_last_fetch BOOLEAN := FALSE;
150 l_count NUMBER;
151 BEGIN
152 IF process_mode='LOCK' THEN
153 NULL;
154 ELSE
155 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
156 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IBE_SH_SHP_LISTS',FALSE);
157 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
158 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
159 --cursor to get <merge to> party and account ID
160 open merged_records;
161 LOOP
162 FETCH merged_records BULK COLLECT INTO
163 MERGE_HEADER_ID_LIST
164 , PRIMARY_KEY_ID_LIST
165 , NUM_COL1_ORIG_LIST
166 , NUM_COL2_ORIG_LIST
167 limit G_FETCH_LIMIT;
168 IF merged_records%NOTFOUND THEN
169 l_last_fetch := TRUE;
170 END IF;
171 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
172 exit;
173 END IF;
174
175 --fix 2899235: do not transfer exp chkout setting
176 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
177 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
178 insert into HZ_CUSTOMER_MERGE_LOG (MERGE_LOG_ID, TABLE_NAME,
179 MERGE_HEADER_ID,request_id,PRIMARY_KEY_ID,DEL_COL1,DEL_COL2,DEL_COL3,
180 DEL_COL4,DEL_COL5,DEL_COL6,DEL_COL7,DEL_COL8,DEL_COL9,DEL_COL10,DEL_COL11,DEL_COL12,
181 DEL_COL13,DEL_COL14,DEL_COL15,DEL_COL16,DEL_COL17,DEL_COL18,DEL_COL19,DEL_COL20,DEL_COL21,
182 DEL_COL22,DEL_COL23,DEL_COL24,DEL_COL25,DEL_COL26,DEL_COL27,DEL_COL28,DEL_COL29,DEL_COL30,
183 DEL_COL31,DEL_COL32,DEL_COL33,DEL_COL34,DEL_COL35,ACTION_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY)
184 select HZ_CUSTOMER_MERGE_LOG_s.nextval,'IBE_ORD_ONECLICK_ALL',MERGE_HEADER_ID_LIST(I)
185 ,req_id,ORD_ONECLICK_ID,OBJECT_VERSION_NUMBER, CUST_ACCOUNT_ID, PARTY_ID, CREATED_BY, CREATION_DATE,
186 LAST_UPDATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ENABLED_FLAG, FREIGHT_CODE, PAYMENT_ID,
187 BILL_TO_PTY_SITE_ID, SHIP_TO_PTY_SITE_ID,ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
188 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
189 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, SECURITY_GROUP_ID, REQUEST_ID, PROGRAM_ID,
190 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, ORG_ID, 'D',hz_utility_pub.CREATED_BY,
191 hz_utility_pub.CREATION_DATE, hz_utility_pub.LAST_UPDATE_LOGIN, hz_utility_pub.LAST_UPDATE_DATE,
192 hz_utility_pub.LAST_UPDATED_BY
193 from IBE_ORD_ONECLICK_ALL where ORD_ONECLICK_ID=PRIMARY_KEY_ID_LIST(I);
194 end if;
195 delete IBE_ORD_ONECLICK_ALL
196 where ORD_ONECLICK_ID=PRIMARY_KEY_ID_LIST(I);
197
198 END LOOP;
199 --fix 2899235: do not transfer exp chkout setting
200 l_count := l_count + SQL%ROWCOUNT;
201 IF l_last_fetch THEN
202 EXIT;
203 END IF;
204 END LOOP;
205 close merged_records;
206 arp_message.set_name('AR','AR_ROWS_UPDATED');
207 arp_message.set_token('NUM_ROWS',to_char(l_count));
208 END IF;
209 EXCEPTION
210 WHEN OTHERS THEN
211 arp_message.set_line( 'acc_merge_oneclick');
212 RAISE;
213 END acc_merge_oneclick;
214
215
216
217 /*-------------------------------------------------------------
218 |
219 | PROCEDURE
220 | acc_merge_shp_lists
221 | DESCRIPTION :
222 | Account merge procedure for the table, IBE_SH_SHP_LISTS_ALL
223 |
224 |
225 |--------------------------------------------------------------*/
226 procedure acc_merge_shp_lists (
227 req_id NUMBER,
228 set_num NUMBER,
229 Process_MODE VARCHAR2,
230 customer_type VARCHAR2)
231 IS
232
233 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
234 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
235 INDEX BY BINARY_INTEGER;
236 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
237
238 TYPE SHP_LIST_ID_LIST_TYPE IS TABLE OF
239 IBE_SH_SHP_LISTS_ALL.SHP_LIST_ID%TYPE
240 INDEX BY BINARY_INTEGER;
241 PRIMARY_KEY_ID_LIST SHP_LIST_ID_LIST_TYPE;
242
243 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
244 IBE_SH_SHP_LISTS_ALL.CUST_ACCOUNT_ID%TYPE
245 INDEX BY BINARY_INTEGER;
246 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
247 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
248
249 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
250 IBE_SH_SHP_LISTS_ALL.PARTY_ID%TYPE
251 INDEX BY BINARY_INTEGER;
252 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
253 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
254 l_acct_id number;
255 l_profile_val VARCHAR2(30);
256
257 --cursor to get <merge to> party, account ID and primary key for shopping lists
258 CURSOR merged_records IS
259 SELECT distinct CUSTOMER_MERGE_HEADER_ID
260 ,yt.SHP_LIST_ID
261 ,yt.CUST_ACCOUNT_ID
262 ,yt.party_id
263 FROM IBE_SH_SHP_LISTS_ALL yt, ra_customer_merges m
264 WHERE
265 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
266 AND m.process_flag = 'N'
267 AND m.request_id = req_id
268 AND m.set_number = set_num;
269 l_last_fetch BOOLEAN := FALSE;
270 l_count NUMBER;
271 BEGIN
272 IF process_mode='LOCK' THEN
273 NULL;
274 ELSE
275 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
276 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IBE_SH_SHP_LISTS',FALSE);
277 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
278 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
279 --cursor to get <merge to> party and account ID
280 open merged_records;
281 LOOP
282 FETCH merged_records BULK COLLECT INTO
283 MERGE_HEADER_ID_LIST
284 , PRIMARY_KEY_ID_LIST
285 , NUM_COL1_ORIG_LIST
286 , NUM_COL2_ORIG_LIST
287 limit G_FETCH_LIMIT;
288 IF merged_records%NOTFOUND THEN
289 l_last_fetch := TRUE;
290 END IF;
291 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
292 exit;
293 END IF;
294 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
295 --get <merge to> party and account ID
296 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
297 --fix for 2935845
298 if customer_type = 'CUSTOMER_ORG' then
299 --B2B, partyID stays the same
300 NUM_COL2_NEW_LIST(I) := NUM_COL2_ORIG_LIST(I);
301 else
302 --B2C, partyID changes to what's tied to accountID
303 NUM_COL2_NEW_LIST(I) := find_party(NUM_COL1_NEW_LIST(I));
304 end if;
305 UPDATE IBE_SH_SHP_LISTS_ALL yt SET
306 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
307 ,PARTY_ID=NUM_COL2_NEW_LIST(I)
308 , LAST_UPDATE_DATE=SYSDATE
309 , last_updated_by=arp_standard.profile.user_id
310 , last_update_login=arp_standard.profile.last_update_login
311 , REQUEST_ID=request_id
312 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
313 , PROGRAM_ID=arp_standard.profile.program_id
314 , PROGRAM_UPDATE_DATE=SYSDATE
315 WHERE SHP_LIST_ID=PRIMARY_KEY_ID_LIST(I);
316
317 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
318 --if logging profile is ON, log data
319 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
320 MERGE_LOG_ID,
321 TABLE_NAME,
322 MERGE_HEADER_ID,
323 PRIMARY_KEY_ID,
324 NUM_COL1_ORIG,
325 NUM_COL1_NEW,
326 NUM_COL2_ORIG,
327 NUM_COL2_NEW,
328 ACTION_FLAG,
329 REQUEST_ID,
330 CREATED_BY,
331 CREATION_DATE,
332 LAST_UPDATE_LOGIN,
333 LAST_UPDATE_DATE,
334 LAST_UPDATED_BY
335 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
336 'IBE_SH_SHP_LISTS_ALL',
337 MERGE_HEADER_ID_LIST(I),
338 PRIMARY_KEY_ID_LIST(I),
339 NUM_COL1_ORIG_LIST(I),
340 NUM_COL1_NEW_LIST(I),
341 NUM_COL2_ORIG_LIST(I),
342 NUM_COL2_NEW_LIST(I),
343 'U',
344 req_id,
345 hz_utility_pub.CREATED_BY,
346 hz_utility_pub.CREATION_DATE,
347 hz_utility_pub.LAST_UPDATE_LOGIN,
348 hz_utility_pub.LAST_UPDATE_DATE,
349 hz_utility_pub.LAST_UPDATED_BY
350 );
351
352
353 END IF;
354 END LOOP;
355 l_count := l_count + SQL%ROWCOUNT;
356 IF l_last_fetch THEN
357 EXIT;
358 END IF;
359 END LOOP;
360 close merged_records;
361 arp_message.set_name('AR','AR_ROWS_UPDATED');
362 arp_message.set_token('NUM_ROWS',to_char(l_count));
363 END IF;
364 EXCEPTION
365 WHEN OTHERS THEN
366 arp_message.set_line( 'acc_merge_shp_lists');
367 RAISE;
368 END acc_merge_shp_lists;
369
370
371
372
373 /*-------------------------------------------------------------
374 |
375 | PROCEDURE
376 | acc_merge_active_quotes
377 | DESCRIPTION :
378 | Account merge procedure for the table, IBE_ACTIVE_QUOTES_ALL
379 |
380 |--------------------------------------------------------------*/
381
382 PROCEDURE acc_merge_active_quotes (
383 req_id NUMBER,
384 set_num NUMBER,
385 process_mode VARCHAR2) IS
386
387 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
388 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
389 INDEX BY BINARY_INTEGER;
390 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
391
392 TYPE ACTIVE_QUOTE_ID_LIST_TYPE IS TABLE OF
393 IBE_ACTIVE_QUOTES_ALL.ACTIVE_QUOTE_ID%TYPE
394 INDEX BY BINARY_INTEGER;
395 PRIMARY_KEY_ID_LIST ACTIVE_QUOTE_ID_LIST_TYPE;
396
397 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
398 IBE_ACTIVE_QUOTES_ALL.CUST_ACCOUNT_ID%TYPE
399 INDEX BY BINARY_INTEGER;
400 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
401 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
402
403 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
404 IBE_ACTIVE_QUOTES_ALL.PARTY_ID%TYPE
405 INDEX BY BINARY_INTEGER;
406 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
407 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
408
409 l_profile_val VARCHAR2(30);
410 l_from_quote_id NUMBER;
411 l_from_party_id NUMBER;
412 l_from_acct_id NUMBER;
413 l_to_acct_id NUMBER;
414 l_from_quote_name VARCHAR2(80);
415 l_customer_merge_id number;
416
417 --cursor to get <merge from> account and party IDs, quote name and quote_header_id
418 Cursor C_ACTIVE_QUOTE_FROM is
419 Select a.quote_header_id, a.cust_account_id, a.party_id, b.quote_name, racm.customer_merge_id
420 from IBE_ACTIVE_QUOTES_ALL a, ASO_QUOTE_HEADERS_ALL b, RA_CUSTOMER_MERGES RACM
421 Where a.quote_header_id = b.quote_header_id (+)
422 and a.party_id = b.party_id (+)
423 and a.cust_account_id = b.cust_account_id (+)
424 and a.cust_account_id = racm.duplicate_id
425 and a.record_type = 'CART'
426 and RACM.PROCESS_FLAG='N' AND RACM.REQUEST_ID = req_id
427 and RACM.SET_NUMBER = set_num;
428
429 l_last_fetch BOOLEAN := FALSE;
430 l_count NUMBER;
431 BEGIN
432 IF process_mode='LOCK' THEN
433 NULL;
434 ELSE
435 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
436 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IBE_ACTIVE_QUOTES',FALSE);
437 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
438 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
439
440 open C_ACTIVE_QUOTE_FROM;
441 loop
442 --get active quote for <merge from> account
443 Fetch C_ACTIVE_QUOTE_FROM into l_from_quote_id, l_from_acct_id,
444 l_from_party_id, l_from_quote_name, l_customer_merge_id;
445 EXIT When C_ACTIVE_QUOTE_FROM%NOTFOUND;
446 Begin
447 --2967340
448 --if <merge from> has an unnamed cart, update it to be default
449 update ASO_QUOTE_HEADERS_ALL
450 set QUOTE_NAME = 'IBE_PRMT_SC_DEFAULTNAMED'
451 where quote_header_id = l_from_quote_id
452 AND quote_name = 'IBE_PRMT_SC_UNNAMED';
453 --check profile, log when it's ON
454 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
455 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
456 MERGE_LOG_ID,TABLE_NAME,MERGE_HEADER_ID,PRIMARY_KEY_ID,
457 VCHAR_COL1_ORIG,VCHAR_COL1_NEW,ACTION_FLAG,REQUEST_ID,CREATED_BY,
458 CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY
459 ) VALUES (
460 HZ_CUSTOMER_MERGE_LOG_s.nextval,'ASO_QUOTE_HEADERS_ALL',
461 l_customer_merge_id,l_from_quote_id,'IBE_PRMT_SC_UNNAMED','IBE_PRMT_SC_DEFAULTNAMED',
462 'U',req_id,hz_utility_pub.CREATED_BY,
463 hz_utility_pub.CREATION_DATE,hz_utility_pub.LAST_UPDATE_LOGIN,
464 hz_utility_pub.LAST_UPDATE_DATE,hz_utility_pub.LAST_UPDATED_BY
465 );
466 end if;
467
468 --log data when audit profile is On
469 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
470 insert into HZ_CUSTOMER_MERGE_LOG (MERGE_LOG_ID, TABLE_NAME,
471 MERGE_HEADER_ID,request_id,PRIMARY_KEY_ID,DEL_COL1,DEL_COL2,DEL_COL3,
472 DEL_COL4,DEL_COL5,DEL_COL6,DEL_COL7,DEL_COL8,DEL_COL9,DEL_COL10,DEL_COL11,ACTION_FLAG,
473 CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY)
474 select HZ_CUSTOMER_MERGE_LOG_s.nextval,'IBE_ACTIVE_QUOTES_ALL',l_customer_merge_id
475 ,req_id,ACTIVE_QUOTE_ID,PARTY_ID,CUST_ACCOUNT_ID,ORG_ID,CREATED_BY,CREATION_DATE
476 ,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_VERSION_NUMBER,LAST_UPDATE_LOGIN
477 ,SECURITY_GROUP_ID,QUOTE_HEADER_ID,'D',hz_utility_pub.CREATED_BY,hz_utility_pub.CREATION_DATE,
478 hz_utility_pub.LAST_UPDATE_LOGIN, hz_utility_pub.LAST_UPDATE_DATE,hz_utility_pub.LAST_UPDATED_BY
479 from ibe_active_quotes_all where quote_header_id=l_from_quote_id
480 and cust_account_id = l_from_acct_id and party_id=l_from_party_id;
481
482 end if;
483 --delete active quote row for <merge from>
484 delete ibe_active_quotes_all
485 where quote_header_id = l_from_quote_id
486 and cust_account_id = l_from_acct_id
487 and party_id=l_from_party_id;
488 End;
489
490 END LOOP;
491 close C_ACTIVE_QUOTE_FROM;
492 arp_message.set_name('AR','AR_ROWS_UPDATED');
493 arp_message.set_token('NUM_ROWS',to_char(l_count));
494 END IF;
495 EXCEPTION
496 WHEN OTHERS THEN
497 arp_message.set_line( 'acc_merge_active_quotes');
498 RAISE;
499 END acc_merge_active_quotes;
500
501
502 /*-------------------------------------------------------------
503 |
504 | PROCEDURE
505 | acc_merge_shared_quote
506 | DESCRIPTION :
507 | Account merge procedure for the table, IBE_SH_QUOTE_ACCESS
508 |
509 |--------------------------------------------------------------*/
510
511 PROCEDURE acc_merge_shared_quote (
512 req_id NUMBER,
513 set_num NUMBER,
514 process_mode VARCHAR2,
515 customer_type VARCHAR2) IS
516
517 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
518 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
519 INDEX BY BINARY_INTEGER;
520 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
521
522 TYPE QUOTE_SHAREE_ID_LIST_TYPE IS TABLE OF
523 IBE_SH_QUOTE_ACCESS.QUOTE_SHAREE_ID%TYPE
524 INDEX BY BINARY_INTEGER;
525 PRIMARY_KEY_ID_LIST QUOTE_SHAREE_ID_LIST_TYPE;
526
527 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
528 IBE_SH_QUOTE_ACCESS.CUST_ACCOUNT_ID%TYPE
529 INDEX BY BINARY_INTEGER;
530 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
531 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
532
533 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
534 IBE_SH_QUOTE_ACCESS.PARTY_ID%TYPE
535 INDEX BY BINARY_INTEGER;
536 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
537 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
538
539 l_profile_val VARCHAR2(30);
540 l_customer_merge_header_id number;
541 l_quote_sharee_number number(15,0);
542 l_request_id number;
543 l_program_application_id number;
544 l_program_id number;
545 l_program_update_date date;
546 l_object_version_number number(9,0);
547 l_created_by number;
548 l_creation_date date;
549 l_last_updated_by number;
550 l_last_update_date date;
551 l_last_update_login number;
552 l_quote_header_id number(15,0);
553 l_to_quote_sharee_id number(15,0);
554 l_from_quote_sharee_id number(15,0);
555 l_update_privilege_type_code varchar2(30);
556 l_security_group_id number;
557 l_party_id number;
558 l_cust_account_id number;
559 l_start_date_active date;
560 l_end_date_active date;
561 l_recipient_name varchar2(2000);
562 l_contact_point_id number;
563 l_from_quote_id number;
564 l_from_party_id number;
565 l_from_acct_id number;
566 l_to_acct_id number;
567 l_to_party_id number;
568 l_delete_flag boolean:=TRUE;
569 /*retrive <merge from> account shared carts*/
570 Cursor C_SHARED_QUOTE_FROM is
571 Select distinct customer_merge_header_id,quote_header_id,
572 i.party_id, RACM.DUPLICATE_ID, RACM.CUSTOMER_ID,quote_sharee_id
573 from IBE_SH_QUOTE_ACCESS i, RA_CUSTOMER_MERGES RACM
574 Where i.cust_account_id = RACM.DUPLICATE_ID
575 AND RACM.PROCESS_FLAG='N'
576 AND RACM.REQUEST_ID = req_id
577 AND RACM.SET_NUMBER = set_num;
578
579
580 l_last_fetch BOOLEAN := FALSE;
581 l_count NUMBER;
582 BEGIN
583 IF process_mode='LOCK' THEN
584 NULL;
585 ELSE
586 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
587 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IBE_SH_QUOTE_ACCESS',FALSE);
588 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
589 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
590
591 --retrieve <merge from> shared carts
592 Open C_SHARED_QUOTE_FROM;
593 Loop
594 Fetch C_SHARED_QUOTE_FROM into l_customer_merge_header_id,l_from_quote_id,
595 l_from_party_id, l_from_acct_id, l_to_acct_id,l_from_quote_sharee_id;
596 EXIT When C_SHARED_QUOTE_FROM%NOTFOUND;
597 Begin
598 l_delete_flag:=TRUE;
599 --check if <merge to> has same share cart as merge from
600 Select quote_sharee_id, request_id,program_application_id,program_id,program_update_date,
601 object_version_number,created_by,creation_date,last_updated_by,last_update_date,
602 last_update_login,quote_header_id,quote_sharee_number,update_privilege_type_code,
603 security_group_id,party_id,cust_account_id,start_date_active,end_date_active,recipient_name,
604 contact_point_id
605 into l_to_quote_sharee_id,l_request_id,l_program_application_id,l_program_id,
606 l_program_update_date,l_object_version_number,l_created_by,l_creation_date,
607 l_last_updated_by,l_last_update_date,l_last_update_login,l_quote_header_id,
608 l_quote_sharee_number,l_update_privilege_type_code,l_security_group_id,l_party_id,
609 l_cust_account_id,l_start_date_active,l_end_date_active,l_recipient_name,l_contact_point_id
610 From IBE_SH_QUOTE_ACCESS
611 Where quote_header_id = l_from_quote_id
612 and cust_account_id = l_to_acct_id
613 --if multiple rows exist for with same quote header ID and account ID
614 and rownum=1
615 ;
616 EXCEPTION
617 When NO_DATA_FOUND Then
618 l_delete_flag:=FALSE;
619 END;
620
621 /* Delete/end_date since it's a duplicate row in shared cart table
622 If both has same shared cart, delete <merge from> row.
623 Log delete info.*/
624 --debug: need TCA profile for test
625 --actual delete
626 if l_delete_flag then
627
628 delete IBE_SH_QUOTE_ACCESS
629 where quote_header_id = l_from_quote_id
630 and cust_account_id = l_from_acct_id;
631 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
632 insert into HZ_CUSTOMER_MERGE_LOG (
633 MERGE_LOG_ID, TABLE_NAME,MERGE_HEADER_ID,PRIMARY_KEY_ID,
634 DEL_COL1,DEL_COL2,DEL_COL3,DEL_COL4,DEL_COL5,DEL_COL6,DEL_COL7,DEL_COL8,
635 DEL_COL9,DEL_COL10,DEL_COL11,DEL_COL12,DEL_COL13,DEL_COL14,DEL_COL15,
636 DEL_COL16,DEL_COL17,DEL_COL18,DEL_COL19,DEL_COL20,ACTION_FLAG,
637 REQUEST_ID,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
638 LAST_UPDATED_BY)
639 values(
640 HZ_CUSTOMER_MERGE_LOG_s.nextval,'IBE_SH_QUOTE_ACCESS',l_customer_merge_header_id,
641 l_from_quote_sharee_id,l_request_id,l_program_application_id,l_program_id,
642 l_program_update_date,l_object_version_number,l_created_by,l_creation_date,
643 l_last_updated_by,l_last_update_date,l_last_update_login,l_quote_header_id,
644 l_quote_sharee_number,l_update_privilege_type_code,l_security_group_id,l_party_id,
645 l_cust_account_id,l_start_date_active,l_end_date_active,l_recipient_name,
646 l_contact_point_id,'D',req_id,hz_utility_pub.CREATED_BY, hz_utility_pub.CREATION_DATE,
647 hz_utility_pub.LAST_UPDATE_LOGIN, hz_utility_pub.LAST_UPDATE_DATE,
648 hz_utility_pub.LAST_UPDATED_BY );
649 end if;
650 else
651 --if <merge from> shared cart not a duplicate of <merge to>, update party/account ID to merge to
652 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
653 arp_message.set_token('TABLE_NAME', 'IBE_SH_QUOTE_ACCESS', FALSE);
654 --fix for 2940366
655 if customer_type = 'CUSTOMER_ORG' then
656 --B2B, partyID stays the same
657 l_to_party_id := l_from_party_id;
658 else
659 --B2C, partyID changes to what's tied to accountID
660 l_to_party_id := find_party(l_to_acct_id);
661 end if;
662 --debug: need TCA profile for test
663 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
664
665 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
666 MERGE_LOG_ID,TABLE_NAME,MERGE_HEADER_ID,PRIMARY_KEY_ID,NUM_COL1_ORIG,NUM_COL1_NEW,
667 NUM_COL2_ORIG,NUM_COL2_NEW,ACTION_FLAG,REQUEST_ID,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,
668 LAST_UPDATE_DATE,LAST_UPDATED_BY
669 ) VALUES (
670 HZ_CUSTOMER_MERGE_LOG_s.nextval,
671 'IBE_SH_QUOTE_ACCESS',l_customer_merge_header_id,l_from_quote_sharee_id,l_from_acct_id,
672 l_to_acct_id, l_from_party_id, l_to_party_id,'U',req_id,hz_utility_pub.CREATED_BY,
673 hz_utility_pub.CREATION_DATE,hz_utility_pub.LAST_UPDATE_LOGIN,hz_utility_pub.LAST_UPDATE_DATE,
674 hz_utility_pub.LAST_UPDATED_BY
675 );
676 end if;
677 --update shared cart
678
679 UPDATE IBE_SH_QUOTE_ACCESS ISQ SET
680 party_id = l_to_party_id,
681 cust_account_id = l_to_acct_id,
682 last_update_date = hz_utility_pub.last_update_date,
683 last_updated_by = hz_utility_pub.user_id,
684 last_update_login = hz_utility_pub.last_update_login,
685 request_id = hz_utility_pub.request_id,
686 program_application_id = hz_utility_pub.program_application_id,
687 program_id = hz_utility_pub.program_id,
688 program_update_date = sysdate
689 Where cust_account_id = l_from_acct_id
690 And party_id = l_from_party_id
691 And quote_header_id = l_from_quote_id;
692
693 end if;
694 END LOOP;
695 CLOSE C_SHARED_QUOTE_FROM;
696 END if;
697 END acc_merge_shared_quote;
698
699
700 /*----------------------------------------------------------------------------*
701 | PUBLIC PROCEDURES |
702 | CUSTOMER_MERGE -- When in ERP Customers are merged the |
703 | The Foriegn keys to cust_account_id should also be updated|
704 | in iStore tables. This procedure will be invoked by |
705 | Customer Merge concurrent program. |
706 | DESCRIPTION |
707 | |
708 | REQUIRES |
709 | |
710 | |
711 | EXCEPTIONS RAISED |
712 | |
713 | KNOWN BUGS |
714 | |
715 | NOTES |
716 | |
717 | HISTORY |
718 | Harish Ekkirala Created 11/06/2000. |
719 | |
720 *----------------------------------------------------------------------------*/
721
722 PROCEDURE CUSTOMER_MERGE(
723 Request_id NUMBER,
724 Set_Number NUMBER,
725 Process_MODE VARCHAR2
726 )
727 IS
728
729 g_count NUMBER;
730 p_request_id NUMBER;
731 p_customer_id RA_CUSTOMER_MERGES.CUSTOMER_ID%TYPE;
732 p_duplicate_id RA_CUSTOMER_MERGES.DUPLICATE_ID%TYPE;
733 p_allow_merge VARCHAR2(1) := 'Y';
734 p_customer_type RA_CUSTOMER_MERGES.CUSTOMER_TYPE%TYPE;
735 l_from_quote_id NUMBER;
736 l_from_party_id NUMBER;
737 l_from_acct_id NUMBER;
738 l_to_acct_id NUMBER;
739 l_sharee_id NUMBER;
740 l_from_quote_name VARCHAR2(80);
741 l_request_id NUMBER := request_id;
742 l_set_number NUMBER := set_number;
743 MERGE_NOT_ALLOWED EXCEPTION;
744
745 CURSOR C is
746 SELECT 'X' from IBE_SH_SHP_LISTS_ALL ISA
747 WHERE ISA.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
748 FROM RA_CUSTOMER_MERGES RACM
749 WHERE RACM.PROCESS_FLAG='N'
750 AND RACM.REQUEST_ID = request_id
751 AND RACM.SET_NUMBER = set_number)
752 FOR UPDATE NOWAIT;
753
754 CURSOR C1 is
755 SELECT 'X' from IBE_ORD_ONECLICK_ALL IOO
756 WHERE IOO.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
757 FROM RA_CUSTOMER_MERGES RACM
758 WHERE RACM.PROCESS_FLAG='N'
759 AND RACM.REQUEST_ID = request_id
760 AND RACM.SET_NUMBER = set_number)
761 FOR UPDATE NOWAIT;
762
763 CURSOR C2 is
764 SELECT 'X' from IBE_SH_QUOTE_ACCESS ISQ
765 WHERE ISQ.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
766 FROM RA_CUSTOMER_MERGES RACM
767 WHERE RACM.PROCESS_FLAG='N'
768 AND RACM.REQUEST_ID = request_id
769 AND RACM.SET_NUMBER = set_number)
770 FOR UPDATE NOWAIT;
771
772 CURSOR C3 is
773 SELECT 'X' from IBE_ACTIVE_QUOTES_ALL IAQ
774 WHERE IAQ.CUST_ACCOUNT_ID IN (SELECT RACM.DUPLICATE_ID
775 FROM RA_CUSTOMER_MERGES RACM
776 WHERE RACM.PROCESS_FLAG='N'
777 AND RACM.REQUEST_ID = Request_id
778 AND RACM.SET_NUMBER = Set_Number)
779 FOR UPDATE NOWAIT;
780
781 --2940366 add customer type, change requestID
782 CURSOR C_CUST (req_id NUMBER) is
783 SELECT RACM.CUSTOMER_ID,RACM.DUPLICATE_ID,RACM.CUSTOMER_TYPE
784 FROM RA_CUSTOMER_MERGES RACM
785 WHERE RACM.PROCESS_FLAG='N'
786 AND RACM.REQUEST_ID = req_id
787 AND RACM.SET_NUMBER = set_number;
788
789 BEGIN
790
791 arp_message.set_line('IBE_MERGE_PVT.CUSTOMER_MERGE()+');
792
793 p_request_id := request_id;
794
795 /* Check to See if you can allow the customer merge to happen */
796
797 Open C_CUST(p_request_id);
798 Loop
799 --2940366
800 Fetch C_CUST into p_customer_id,p_duplicate_id,p_customer_type;
801 Exit When C_CUST%NOTFOUND;
802
803 p_allow_merge := allow_merge(p_customer_id,p_duplicate_id);
804
805 If p_allow_merge = 'N' Then
806 Close c_cust;
807 Raise MERGE_NOT_ALLOWED;
808 End IF;
809
810 End Loop;
811 Close C_CUST;
812
813 /*obsolete code after consulting with TCA, lock mode not used*/
814 /*
815 If process_mode = 'LOCK' then
816
817 arp_message.set_name('AR','AR_LOCKING_TABLE');
818 arp_message.set_token('TABLE_NAME','IBE_SH_SHP_LISTS_ALL',FALSE);
819
820 open C;
821 close C;
822
823 arp_message.set_name('AR','AR_LOCKING_TABLE');
824 arp_message.set_token('TABLE_NAME','IBE_ORD_ONECLICK_ALL',FALSE);
825
826 open C1;
827 close C1;
828
829 arp_message.set_name('AR','AR_LOCKING_TABLE');
830 arp_message.set_token('TABLE_NAME','IBE_SH_QUOTE_ACCESS',FALSE);
831
832 open C2;
833 close C2;
834
835 End If; */
836
837 arp_message.set_name('AR','AR_UPDATING_TABLE');
838 arp_message.set_token('TABLE_NAME','IBE_SH_SHP_LISTS_ALL',FALSE);
839
840 /* For updating IBE_SH_SHP_LISTS_ALL table*/
841 --2940366
842 acc_merge_shp_lists(request_id,set_number,process_mode,p_customer_type);
843 g_count := sql%rowcount;
844 arp_message.set_name('AR','AR_ROWS_UPDATED');
845 arp_message.set_token('NUM_ROWS',to_char(g_count));
846
847 /* For updating IBE_ORD_ONECLICK_ALL Table */
848 /* 4/8/02
849 If oneclick table already has entry for the merge to account, ignore and do nothing
850 */
851 acc_merge_oneclick(request_id,set_number,process_mode);
852 arp_message.set_name('AR','AR_UPDATING_TABLE');
853 arp_message.set_token('TABLE_NAME','IBE_ORD_ONECLICK_ALL',FALSE);
854 g_count := sql%rowcount;
855
856
857 /* account merge for shared quote
858 12/18/02
859 */
860 --2940366
861 acc_merge_shared_quote(request_id,set_number,process_mode,p_customer_type);
862 arp_message.set_name('AR','AR_UPDATING_TABLE');
863 arp_message.set_token('TABLE_NAME','IBE_SH_QUOTE_ACCESS',FALSE);
864 arp_message.set_name('AR','AR_ROWS_UPDATED');
865 arp_message.set_token('NUM_ROWS',to_char(g_count));
866
867
868 /*merge active cart
869 12/18/02
870 */
871 acc_merge_active_quotes(request_id,set_number,process_mode);
872 arp_message.set_name('AR','AR_UPDATING_TABLE');
873 arp_message.set_token('TABLE_NAME','IBE_ACTIVE_QUOTES_ALL',FALSE);
874 arp_message.set_name('AR','AR_ROWS_UPDATED');
875 arp_message.set_token('NUM_ROWS',to_char(g_count));
876
877
878 arp_message.set_line('IBE_MERGE_PVT.CUSTOMER_MERGE()-');
879
880 EXCEPTION
881
882 WHEN MERGE_NOT_ALLOWED THEN
883 arp_message.set_name('IBE','IBE_MERGE_NOT_ALLOWED');
884 arp_message.set_error('IBE_MERGE_PVT.CUSTOMER_MERGE');
885 raise;
886
887 WHEN OTHERS THEN
888 arp_message.set_error('IBE_MERGE_PVT.CUSTOMER_MERGE');
889 raise;
890
891 End Customer_Merge;
892
893 /*----------------------------------------------------------------------------*
894 | PUBLIC PROCEDURES |
895 | MERGE_SHP_LISTS -- |
896 | When in ERP Parties are merged the |
897 | The Foriegn keys to party_id and other columns |
898 | should also be updated in iStore tables. |
899 | This procedure will update IBE_SH_SHP_LISTS_ALL table |
900 | and will be called from party Merge concurrent program. |
901 | DESCRIPTION |
902 | |
903 | REQUIRES |
904 | |
905 | |
906 | EXCEPTIONS RAISED |
907 | |
908 | KNOWN BUGS |
909 | |
910 | NOTES |
911 | |
912 | HISTORY |
913 | Harish Ekkirala Created 02/12/2001. |
914 | |
915 *----------------------------------------------------------------------------*/
916
917 PROCEDURE MERGE_SHIP_LISTS(
918 P_entity_name IN VARCHAR2,
919 P_from_id IN NUMBER,
920 X_to_id OUT NOCOPY NUMBER,
921 P_from_fk_id IN NUMBER,
922 P_to_fk_id IN NUMBER,
923 P_parent_entity_name IN VARCHAR2,
924 P_batch_id IN NUMBER,
925 P_batch_party_id IN NUMBER,
926 X_return_status OUT NOCOPY VARCHAR2
927 )
928 IS
929
930 Cursor C1 is
931 Select 'X' from
932 IBE_SH_SHP_LISTS_ALL
933 Where party_id = p_from_fk_id
934 for update nowait;
935
936 l_merge_reason_code VARCHAR2(30);
937 l_count NUMBER(10) := 0;
938
939 RESOURCE_BUSY EXCEPTION;
940 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
941
942
943 Begin
944
945 arp_message.set_line('IBE_MERGE_PVT.MERGE_SHIP_LISTS()+');
946
947 x_return_status := FND_API.G_RET_STS_SUCCESS;
948
949
950
951 --Do All Validations
952
953 --Check the Merge Reason code. If the merge reason is duplicate record, then no validation is required.
954 -- Otherwise do the required validations.
955
956 -- Commenting this section for now as we are not doing any validations, if the reason is not 'Duplicate Record'.
957 -- In future if we need any validations we can un comment this sections and add validations.
958 /*
959 Select merge_reason_code
960 Into l_merge_reason_code
961 From hz_merge_batch
962 Where batch_id = p_batch_id;
963
964 If l_merge_reason_code = 'DUPLICATE' Then
965 null;
966 Else
967 null;
968 End If;
969 */
970
971 /* Perform the merge operation */
972
973 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
974 as Merged from id and return */
975
976 if p_from_fk_id = p_to_fk_id then
977 x_to_id := p_from_id;
978 return;
979 End If;
980
981 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent. */
982
983 if p_from_fk_id <> p_to_fk_id Then
984
985 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
986 arp_message.set_token('TABLE_NAME', 'IBE_SH_SHP_LISTS_ALL', FALSE);
987
988 Open C1;
989 Close C1;
990
991 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
992 arp_message.set_token('TABLE_NAME', 'IBE_SH_SHP_LISTS_ALL', FALSE);
993
994 UPDATE IBE_SH_SHP_LISTS_ALL isl SET
995 party_id = p_to_fk_id,
996 last_update_date = hz_utility_pub.last_update_date,
997 last_updated_by = hz_utility_pub.user_id,
998 last_update_login = hz_utility_pub.last_update_login,
999 request_id = hz_utility_pub.request_id,
1000 program_application_id = hz_utility_pub.program_application_id,
1001 program_id = hz_utility_pub.program_id,
1002 program_update_date = sysdate
1003 Where party_id = p_from_fk_id;
1004
1005
1006 l_count := sql%rowcount;
1007
1008 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1009 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1010
1011 return;
1012
1013 End If;
1014
1015 arp_message.set_line('IBE_MERGE_PVT.MERGE_SHIP_LISTS()-');
1016
1017
1018 Exception
1019 When RESOURCE_BUSY Then
1020 arp_message.set_line('IBE_MERGE_PVT.MERGE_SHIP_LISTS; Could not obtain lock'||
1021 'on table IBE_SH_SHP_LISTS_ALL');
1022
1023 x_return_status := FND_API.G_RET_STS_ERROR;
1024 raise;
1025 When Others Then
1026
1027 arp_message.set_line('IBE_MERGE_PVT.MERGE_SHIP_LISTS'||sqlerrm);
1028
1029 x_return_status := FND_API.G_RET_STS_ERROR;
1030 raise;
1031
1032 End MERGE_SHIP_LISTS;
1033
1034 /*----------------------------------------------------------------------------*
1035 | PUBLIC PROCEDURES |
1036 | MERRGE_ONECLICK -- |
1037 | When in ERP Parties are merged the |
1038 | The Foriegn keys to party_id and other columns |
1039 | should also be updated in iStore tables. |
1040 | This procedure will update |
1041 | IBE_ORD_ONECLICK_ALL table and will be called from party |
1042 | Merge concurrent program. |
1043 | DESCRIPTION |
1044 | |
1045 | REQUIRES |
1046 | |
1047 | |
1048 | EXCEPTIONS RAISED |
1049 | |
1050 | KNOWN BUGS |
1051 | |
1052 | NOTES |
1053 | |
1054 | HISTORY |
1055 | Harish Ekkirala Created 02/12/2001. |
1056 | |
1057 *----------------------------------------------------------------------------*/
1058 PROCEDURE MERGE_ONECLICK(
1059 P_entity_name IN VARCHAR2,
1060 P_from_id IN NUMBER,
1061 X_to_id OUT NOCOPY NUMBER,
1062 P_from_fk_id IN NUMBER,
1063 P_to_fk_id IN NUMBER,
1064 P_parent_entity_name IN VARCHAR2,
1065 P_batch_id IN NUMBER,
1066 P_batch_party_id IN NUMBER,
1067 X_return_status OUT NOCOPY VARCHAR2
1068 )
1069 IS
1070
1071 Cursor C1 is
1072 Select 'X' from
1073 IBE_ORD_ONECLICK_ALL
1074 where party_id = p_from_fk_id
1075 for update nowait;
1076
1077 Cursor C2 is
1078 Select 'X' from
1079 IBE_ORD_ONECLICK_ALL
1080 Where bill_to_pty_site_id = p_from_fk_id
1081 Or ship_to_pty_site_id = p_from_fk_id
1082 for update nowait;
1083
1084 l_ord_oneclick_id IBE_ORD_ONECLICK_ALL.ORD_ONECLICK_ID%TYPE;
1085 l_merge_reason_code VARCHAR2(30);
1086 l_count NUMBER(10) := 0;
1087 RESOURCE_BUSY EXCEPTION;
1088 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1089
1090 Begin
1091
1092 arp_message.set_line('IBE_MERGE_PVT.MERGE_ONECLICK()+');
1093
1094 x_return_status := FND_API.G_RET_STS_SUCCESS;
1095
1096 --Do All Validations
1097
1098 --Check the Merge Reason code. If the merge reason is duplicate record, then no validation is required.
1099 -- Otherwise do the required validations.
1100
1101 -- Commenting this section for now as we are not doing any validations, if the reason is not 'Duplicate Record'.
1102 -- In future if we need any validations we can un comment this sections and add validations.
1103
1104 /*
1105 Select merge_reason_code
1106 Into l_merge_reason_code
1107 From hz_merge_batch
1108 Where batch_id = p_batch_id;
1109
1110 If l_merge_reason_code = 'DUPLICATE' Then
1111 null;
1112 Else
1113 null;
1114 End If;
1115 */
1116
1117
1118 /* Perform the merge operation */
1119
1120 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
1121 as Merged from id and return */
1122
1123
1124 if p_from_fk_id = p_to_fk_id then
1125
1126 x_to_id := p_from_id;
1127
1128 return;
1129 End If;
1130
1131
1132 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent.
1133 Before transferring check if similar dependent record exists on the new parent. If the duplicate exists then do not
1134 transfer and and return the id of the duplicate record as the merged to id. */
1135
1136
1137 if p_from_fk_id <> p_to_fk_id Then
1138
1139 if p_parent_entity_name = 'HZ_PARTIES' Then
1140
1141 --fix 2899235: delete <merge from> only, don't change <merge to>
1142 delete IBE_ORD_ONECLICK_ALL
1143 where party_id = p_from_fk_id;
1144
1145
1146 /* Begin
1147 select ord_oneclick_id
1148 into l_ord_oneclick_id
1149 from ibe_ord_oneclick_all
1150 where party_id = p_to_fk_id
1151 and rownum = 1;
1152 exception
1153
1154 When no_data_found Then
1155 l_ord_oneclick_id := null;
1156 end;
1157
1158 If l_ord_oneclick_id is null Then
1159
1160 -- Lock the table and update the record(s).
1161
1162 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1163 arp_message.set_token('TABLE_NAME', 'IBE_ORD_ONECLICK_ALL', FALSE);
1164
1165 Open C1;
1166 Close C1;
1167
1168 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
1169 arp_message.set_token('TABLE_NAME', 'IBE_ORD_ONECLICK_ALL', FALSE);
1170
1171
1172 -- 4/8/02
1173 -- If updating party_id results in uniqueness violation, means oneclick already has
1174 -- setting for express checkout, do nothing.
1175
1176 BEGIN
1177 UPDATE IBE_ORD_ONECLICK_ALL SET
1178 party_id = p_to_fk_id,
1179 last_update_date = hz_utility_pub.last_update_date,
1180 last_updated_by = hz_utility_pub.user_id,
1181 last_update_login = hz_utility_pub.last_update_login
1182 Where party_id = p_from_fk_id;
1183 EXCEPTION WHEN OTHERS THEN
1184 NULL;
1185 END;
1186 l_count := sql%rowcount;
1187
1188 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1189 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1190
1191 Return;
1192
1193 Else
1194 --fix 2781213
1195 delete IBE_ORD_ONECLICK_ALL
1196 where party_id = p_from_fk_id;
1197
1198 return;
1199
1200 End IF;
1201 */
1202 --fix 2899235: don't update bill/ship party site ID ever
1203 /* Elsif p_parent_entity_name = 'HZ_PARTY_SITES' Then
1204
1205 -- Lock the table and update the record(s).
1206
1207 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1208 arp_message.set_token('TABLE_NAME', 'IBE_ORD_ONECLICK_ALL', FALSE);
1209
1210 Open C1;
1211 Close C1;
1212
1213 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
1214 arp_message.set_token('TABLE_NAME', 'IBE_ORD_ONECLICK_ALL', FALSE);
1215
1216 UPDATE IBE_ORD_ONECLICK_ALL SET
1217 bill_to_pty_site_id = decode(bill_to_pty_site_id,p_from_fk_id,p_to_fk_id,bill_to_pty_site_id),
1218 ship_to_pty_site_id = decode(ship_to_pty_site_id,p_from_fk_id,p_to_fk_id,ship_to_pty_site_id),
1219 last_update_date = hz_utility_pub.last_update_date,
1220 last_updated_by = hz_utility_pub.user_id,
1221 last_update_login = hz_utility_pub.last_update_login
1222 Where bill_to_pty_site_id= p_from_fk_id
1223 Or ship_to_pty_site_id = p_from_fk_id;
1224
1225 l_count := sql%rowcount;
1226
1227 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1228 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1229
1230 Return;
1231 */
1232 End If;
1233
1234 End If;
1235
1236 arp_message.set_line('IBE_MERGE_PVT.MERGE_ONECLICK()-');
1237
1238 Exception
1239 When RESOURCE_BUSY Then
1240 arp_message.set_line('IBE_MERGE_PVT.MERGE_ONECLICK; Could not obtain lock'||
1241 'on table IBE_ORD_ONECLICK_ALL');
1242 x_return_status := FND_API.G_RET_STS_ERROR;
1243 raise;
1244 When Others Then
1245 arp_message.set_line('IBE_MERGE_PVT.MERGE_ONECLICK'||sqlerrm);
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 raise;
1248
1249
1250 END MERGE_ONECLICK;
1251
1252
1253
1254
1255 /*----------------------------------------------------------------------------*
1256 | PUBLIC PROCEDURES |
1257 | MERGE_MSITE_PARTY_ACCESS -- |
1258 | When in ERP Parties are merged the |
1259 | The Foriegn keys to party_id and other columns |
1260 | should also be updated in iStore tables. |
1261 | This procedure will update |
1262 | IBE_MSITE_PRTY_ACCSS table and will be called from party |
1263 | Merge concurrent program. |
1264 | DESCRIPTION |
1265 | |
1266 | REQUIRES |
1267 | |
1268 | |
1269 | EXCEPTIONS RAISED |
1270 | |
1271 | KNOWN BUGS |
1272 | |
1273 | NOTES |
1274 | |
1275 | HISTORY |
1276 | Harish Ekkirala Created 02/12/2001. |
1277 | |
1278 *----------------------------------------------------------------------------*/
1279
1280 PROCEDURE MERGE_MSITE_PARTY_ACCESS(
1281 P_entity_name IN VARCHAR2,
1282 P_from_id IN NUMBER,
1283 X_to_id OUT NOCOPY NUMBER,
1284 P_from_fk_id IN NUMBER,
1285 P_to_fk_id IN NUMBER,
1286 P_parent_entity_name IN VARCHAR2,
1287 P_batch_id IN NUMBER,
1288 P_batch_party_id IN NUMBER,
1289 X_return_status OUT NOCOPY VARCHAR2
1290 )
1291 IS
1292
1293 Cursor C1 is
1294 Select 'X' from
1295 IBE_MSITE_PRTY_ACCSS
1296 where party_id = p_from_fk_id
1297 for update nowait;
1298
1299 CURSOR merge_records(p_party_id NUMBER) IS
1300 Select a.msite_id, b.party_access_code
1301 From ibe_msite_prty_accss a, ibe_msites_b b
1302 Where party_id = p_party_id and a.msite_id=b.msite_id and b.site_type = 'I';
1303
1304
1305 l_msite_prty_accss_id IBE_MSITE_PRTY_ACCSS.MSITE_PRTY_ACCSS_ID%TYPE;
1306 l_merge_reason_code VARCHAR2(30);
1307 l_count NUMBER(10) := 0;
1308 RESOURCE_BUSY EXCEPTION;
1309 l_msite_id IBE_MSITE_PRTY_ACCSS.MSITE_ID%TYPE;
1310 l_party_access_code IBE_MSITES_B.PARTY_ACCESS_CODE%TYPE;
1311 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1312
1313 Begin
1314
1315 arp_message.set_line('IBE_MERGE_PVT.MERGE_MSITE_PARTY_ACCESS()+');
1316
1317 x_return_status := FND_API.G_RET_STS_SUCCESS;
1318
1319
1320
1321 /* Perform the merge operation */
1322
1323 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
1324 as Merged from id and return */
1325
1326
1327 if p_from_fk_id = p_to_fk_id then
1328
1329 x_to_id := p_from_id;
1330
1331 return;
1332 End If;
1333
1334
1335 /* If the Parent has changed(i.e. Parent is getting merged), then transfer the dependent record to the new parent.
1336 Before transferring check if similar dependent record exists on the new parent. If the duplicate exists then do not
1337 transfer and and return the id of the duplicate record as the merged to id. */
1338
1339
1340 if p_from_fk_id <> p_to_fk_id Then
1341
1342 if p_parent_entity_name = 'HZ_PARTIES' Then
1343
1344 --iterate each <merge from> record in the site access table
1345 open merge_records(p_from_fk_id);
1346 loop
1347 fetch merge_records into l_msite_id, l_party_access_code;
1348 EXIT When merge_records%NOTFOUND;
1349 Begin
1350 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
1351 arp_message.set_token('TABLE_NAME', 'IBE_MSITE_PRTY_ACCSS', FALSE);
1352 arp_message.set_line('IBE_MERGE_PVT.MERGE_MSITE_PARTY_ACCESS; '||
1353 ' merging msite:'||l_msite_id||' both <merge from> & <merge to> are BOTH on');
1354
1355 -- only merge when both <merge from> & <merge to> are BOTH on
1356 -- and <merge to> doesn't have restrictions on the <merge from> minisite
1357 UPDATE IBE_MSITE_PRTY_ACCSS
1358 SET party_id = p_to_fk_id,
1359 last_update_date = hz_utility_pub.last_update_date,
1360 last_updated_by = hz_utility_pub.user_id,
1361 last_update_login = hz_utility_pub.last_update_login
1362 Where party_id = p_from_fk_id and exists (
1363 select 1 from IBE_MSITE_PRTY_ACCSS a, IBE_MSITES_B b
1364 where party_id=p_to_fk_id and a.msite_id<>l_msite_id
1365 and a.msite_id = b.msite_id and b.party_access_code = l_party_access_code
1366 and b.site_type = 'I'
1367 );
1368 l_count := sql%rowcount;
1369 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1370 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1371
1372
1373 -- for a given msite, if <merge from> has party access and <merge to> doesn't
1374 -- then end_date <merge from> to prevent dangling party layer data
1375 if (SQL%NOTFOUND) then
1376 update IBE_MSITE_PRTY_ACCSS
1377 set END_DATE_ACTIVE = trunc(sysdate)
1378 where party_id = p_from_fk_id and msite_id=l_msite_id;
1379 end if;
1380
1381 Exception
1382 When Others Then
1383 arp_message.set_line('IBE_MERGE_PVT.MERGE_MSITE_PARTY_ACCESS'||sqlerrm);
1384 x_return_status := FND_API.G_RET_STS_ERROR;
1385 raise;
1386 end;
1387 end loop;
1388 close merge_records;
1389
1390 End If;
1391 End If;
1392
1393 arp_message.set_line('IBE_MERGE_PVT.MERGE_MSITE_PARTY_ACCESS()-');
1394
1395 Exception
1396 When RESOURCE_BUSY Then
1397 arp_message.set_line('IBE_MERGE_PVT.MERGE_MSITE_PARTY_ACCESS; Could not obtain lock'||
1398 'on table IBE_MSITE_PRTY_ACCSS');
1399 x_return_status := FND_API.G_RET_STS_ERROR;
1400 raise;
1401 When Others Then
1402 arp_message.set_line('IBE_MERGE_PVT.MERGE_MSITE_PARTY_ACCESS'||sqlerrm);
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 raise;
1405
1406
1407 END MERGE_MSITE_PARTY_ACCESS;
1408
1409 /*----------------------------------------------------------------------------*
1410 | PUBLIC PROCEDURES |
1411 | MERGE_SHARED_QUOTE -- |
1412 | When in ERP Parties are merged the |
1413 | The Foriegn keys to party_id and other columns |
1414 | should also be updated in iStore tables. |
1415 | This procedure will update IBE_SH_QUOTE table |
1416 | and will be called from party Merge concurrent program. |
1417 | DESCRIPTION |
1418 | |
1419 | REQUIRES |
1420 | |
1421 | |
1422 | EXCEPTIONS RAISED |
1423 | |
1424 | KNOWN BUGS |
1425 | |
1426 | NOTES |
1427 | |
1428 | HISTORY |
1429 | Adam Wu Created 12/05/2002. |
1430 | |
1431 *----------------------------------------------------------------------------*/
1432
1433 PROCEDURE MERGE_SHARED_QUOTE(
1434 P_entity_name IN VARCHAR2,
1435 P_from_id IN NUMBER,
1436 X_to_id OUT NOCOPY NUMBER,
1437 P_from_fk_id IN NUMBER,
1438 P_to_fk_id IN NUMBER,
1439 P_parent_entity_name IN VARCHAR2,
1440 P_batch_id IN NUMBER,
1441 P_batch_party_id IN NUMBER,
1442 X_return_status OUT NOCOPY VARCHAR2
1443 )
1444 IS
1445
1446 l_dummy VARCHAR2(1);
1447 Cursor MERGE_FROM_SH is
1448 Select quote_header_id, cust_account_id from
1449 IBE_SH_QUOTE_ACCESS
1450 Where party_id = p_from_fk_id
1451 for update nowait;
1452
1453
1454 cursor find_account(p_party_id number) is
1455 select cust_account_id
1456 from hz_cust_accounts
1457 where party_id=p_party_id and rownum=1
1458 for update nowait;
1459
1460 l_merge_reason_code VARCHAR2(30);
1461 l_count NUMBER(10) := 0;
1462 l_quote_header_id NUMBER;
1463 l_party_id NUMBER;
1464 l_from_cust_account_id NUMBER;
1465 l_to_cust_account_id NUMBER;
1466
1467 RESOURCE_BUSY EXCEPTION;
1468 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1469
1470 Begin
1471
1472 arp_message.set_line('IBE_MERGE_PVT.MERGE_SHARED_QUOTE()+');
1473
1474 x_return_status := FND_API.G_RET_STS_SUCCESS;
1475
1476 /* Perform the merge operation */
1477
1478 /* If the parent has NOT Changed(i.e Parent is getting transfered), then nothing needs to be done. Set Merge To id same
1479 as Merged from id and return */
1480
1481 if p_from_fk_id = p_to_fk_id then
1482 x_to_id := p_from_id;
1483 return;
1484 End If;
1485
1486
1487
1488 if p_from_fk_id <> p_to_fk_id Then
1489 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1490 arp_message.set_token('TABLE_NAME', 'IBE_SH_QUOTE_ACCESS', FALSE);
1491 open find_account(p_to_fk_id);
1492 fetch find_account into l_to_cust_account_id ;
1493 if p_parent_entity_name = 'HZ_PARTIES' Then
1494 Open MERGE_FROM_SH;
1495 Loop
1496 Fetch MERGE_FROM_SH into l_quote_header_id, l_from_cust_account_id;
1497 EXIT When merge_from_sh%NOTFOUND;
1498 Begin
1499 Select party_id into l_party_id
1500 From IBE_SH_QUOTE_ACCESS
1501 Where party_id = p_to_fk_id
1502 and cust_account_id = l_to_cust_account_id
1503 And quote_header_id = l_quote_header_id;
1504 -- delete/end_date since it's a duplicate row in quotes table
1505
1506 delete IBE_SH_QUOTE_ACCESS
1507 where quote_header_id = l_quote_header_id
1508 and party_id = p_from_fk_id
1509 and cust_account_id = l_from_cust_account_id;
1510 Exception
1511 --update party ID to merge to if not a duplicate for merge to party
1512 When NO_DATA_FOUND Then
1513 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
1514 arp_message.set_token('TABLE_NAME', 'IBE_SH_QUOTE_ACCESS', FALSE);
1515
1516 UPDATE IBE_SH_QUOTE_ACCESS SET
1517 party_id = p_to_fk_id,
1518 last_update_date = hz_utility_pub.last_update_date,
1519 last_updated_by = hz_utility_pub.user_id,
1520 last_update_login = hz_utility_pub.last_update_login,
1521 request_id = hz_utility_pub.request_id,
1522 program_application_id = hz_utility_pub.program_application_id,
1523 program_id = hz_utility_pub.program_id,
1524 program_update_date = sysdate
1525 Where party_id = p_from_fk_id
1526 And cust_account_id = l_from_cust_account_id
1527 And quote_header_id = l_quote_header_id;
1528 END;
1529 END LOOP;
1530 CLOSE MERGE_FROM_SH;
1531 --fix 2889340
1532 --fix 2920475
1533 elsif p_parent_entity_name = 'HZ_CONTACT_POINTS' Then
1534 BEGIN
1535 select 1
1536 into l_dummy
1537 from hz_contact_points
1538 where contact_point_id=p_from_fk_id and owner_table_name<>'IBE_SH_QUOTE_ACCESS';
1539 UPDATE IBE_SH_QUOTE_ACCESS SET
1540 contact_point_id = p_to_fk_id,
1541 last_update_date = hz_utility_pub.last_update_date,
1542 last_updated_by = hz_utility_pub.user_id,
1543 last_update_login = hz_utility_pub.last_update_login,
1544 request_id = hz_utility_pub.request_id,
1545 program_application_id = hz_utility_pub.program_application_id,
1546 program_id = hz_utility_pub.program_id,
1547 program_update_date = sysdate
1548 Where contact_point_id = p_from_fk_id;
1549
1550 EXCEPTION
1551 WHEN NO_DATA_FOUND THEN
1552 NULL;
1553 END;
1554 end if; --end if p_parent_entity_name = 'HZ_CONTACT_POINTS' Then
1555
1556 l_count := sql%rowcount;
1557
1558 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1559 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1560 return;
1561
1562
1563 End If; --end if p_from_fk_id <> p_to_fk_id Then
1564
1565 arp_message.set_line('IBE_MERGE_PVT.MERGE_SHIP_LISTS()-');
1566
1567
1568 Exception
1569 When RESOURCE_BUSY Then
1570 arp_message.set_line('IBE_MERGE_PVT.MERGE_SH_QUOTE_ACCESS; Could not obtain lock'||
1571 'on table IBE_SH_QUOTE_ACCESS');
1572 x_return_status := FND_API.G_RET_STS_ERROR;
1573 raise;
1574 When Others Then
1575 arp_message.set_line('IBE_MERGE_PVT.MERGE_SH_QUOTE_ACCESS'||sqlerrm);
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 raise;
1578 End MERGE_SHARED_QUOTE;
1579
1580 /*----------------------------------------------------------------------------*
1581 | PUBLIC PROCEDURES |
1582 | MERGE_ACTIVE_QUOTE -- |
1583 | When in ERP Parties are merged the |
1584 | The Foriegn keys to party_id and other columns |
1585 | should also be updated in iStore tables. |
1586 | This procedure will update IBE_ACTIVE_QUOTES_ALL table |
1587 | and will be called from party Merge concurrent program. |
1588 | DESCRIPTION |
1589 | |
1590 | REQUIRES |
1591 | |
1592 | |
1593 | EXCEPTIONS RAISED |
1594 | |
1595 | KNOWN BUGS |
1596 | |
1597 | NOTES |
1598 | |
1599 | HISTORY |
1600 | Adam Wu Created 12/05/2002. |
1601 | |
1602 *----------------------------------------------------------------------------*/
1603 procedure MERGE_ACTIVE_QUOTE(
1604 P_entity_name IN VARCHAR2,
1605 P_from_id IN NUMBER,
1606 X_to_id OUT NOCOPY NUMBER,
1607 P_from_fk_id IN NUMBER,
1608 P_to_fk_id IN NUMBER,
1609 P_parent_entity_name IN VARCHAR2,
1610 P_batch_id IN NUMBER,
1611 P_batch_party_id IN NUMBER,
1612 X_return_status OUT NOCOPY VARCHAR2
1613 )
1614 IS
1615
1616 l_merge_reason_code VARCHAR2(30);
1617 l_count NUMBER(10) := 0;
1618 l_quote_header_id NUMBER;
1619 l_party_id NUMBER;
1620 l_cust_account_id NUMBER;
1621 l_quote_name VARCHAR2(80);
1622
1623 RESOURCE_BUSY EXCEPTION;
1624 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
1625
1626 Cursor MERGE_FROM_ACTIVE is
1627 Select a.quote_header_id, a.cust_account_id, b.quote_name
1628 from IBE_ACTIVE_QUOTES_ALL a, ASO_QUOTE_HEADERS_ALL b
1629 Where a.quote_header_id = b.quote_header_id (+) and a.party_id = b.party_id (+)
1630 and a.cust_account_id=b.cust_account_id (+) and a.party_id=P_from_fk_id
1631 for update nowait;
1632
1633
1634
1635 BEGIN
1636
1637 arp_message.set_line('IBE_MERGE_PVT.MERGE_ACTIVE_QUOTE()+');
1638
1639 x_return_status := FND_API.G_RET_STS_SUCCESS;
1640
1641
1642 if p_from_fk_id = p_to_fk_id then
1643 x_to_id := p_from_id;
1644 return;
1645 end If;
1646
1647
1648
1649 if p_from_fk_id <> p_to_fk_id Then
1650 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
1651 arp_message.set_token('TABLE_NAME', 'IBE_SH_SHP_LISTS_ALL', FALSE);
1652 open merge_from_active;
1653 loop
1654 Fetch merge_from_active into l_quote_header_id, l_cust_account_id, l_quote_name;
1655 EXIT When merge_from_active%NOTFOUND;
1656 Begin
1657 --2967430
1658 update ASO_QUOTE_HEADERS_ALL
1659 set QUOTE_NAME = 'IBE_PRMT_SC_DEFAULTNAMED'
1660 where quote_header_id = l_quote_header_id
1661 AND quote_name = 'IBE_PRMT_SC_UNNAMED';
1662
1663 delete ibe_active_quotes_all
1664 where quote_header_id = l_quote_header_id
1665 and cust_account_id = l_cust_account_id
1666 and party_id=P_from_fk_id;
1667 /* Exception
1668 null;
1669 */
1670 End;
1671
1672 END LOOP;
1673 CLOSE MERGE_FROM_ACTIVE;
1674 l_count := sql%rowcount;
1675 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1676 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1677
1678 return;
1679
1680 END IF;
1681
1682 arp_message.set_line('IBE_MERGE_PVT.MERGE_ACTIVE_QUOTE()-');
1683
1684
1685 Exception
1686 When RESOURCE_BUSY Then
1687 arp_message.set_line('IBE_MERGE_PVT.MERGE_ACTIVE_QUOTE; Could not obtain lock'||
1688 'on table IBE_ACTIVE_QUOTES_ALL');
1689 x_return_status := FND_API.G_RET_STS_ERROR;
1690 raise;
1691 When Others Then
1692 arp_message.set_line('IBE_MERGE_PVT.MERGE_ACTIVE_QUOTE'||sqlerrm);
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 raise;
1695 END MERGE_ACTIVE_QUOTE;
1696
1697 End IBE_MERGE_PVT;
1698