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