[Home] [Help]
PACKAGE BODY: APPS.CSC_ACCOUNT_MERGE_PKG
Source
1 PACKAGE BODY CSC_ACCOUNT_MERGE_PKG AS
2 /* $Header: cscvmacb.pls 115.4 2003/06/26 14:50:23 bhroy ship $ */
3 -- Start of Comments
4 -- Package name : CSC_ACCOUNT_MERGE
5 -- Purpose : Merges duplicate customer accounts in the Customer
6 -- Care tables.
7 --
8 -- History
9 -- MM-DD-YYYY NAME MODIFICATIONS
10 -- 10-06-2000 dejoseph Created.
11 -- 02-02-2001 dejoseph Modified update stmt. to update the new columns that
12 -- were added. ie. request_id, program_application_id,
13 -- program_id, program_update_date.
14 -- 12-23-2002 bhroy All procedures body changed using the auto generated Perl script.
15 -- 02-12-2003 bhroy LAST_UPDATE_DATE, Last_updated_by, Last_update_login commented for CSC_CUSTOMIZED_PLANS table
16 -- 02-25-2003 bhroy l_count initialized, CSC_CUST_PLANS update where clause changed, delete redundant record
17 -- 04-28-2003 bhroy TCA sripts are inserting same record for ORIG and NEW columns, modified merge cursor
21 PROCEDURE CSC_MERGE_ALL_ACCOUNTS (
18 -- 06-26-2003 bhroy Fixed cross party merge, Bug# 2930337
19 --
20 -- End of Comments
22 req_id IN NUMBER,
23 set_num IN NUMBER,
24 process_mode IN VARCHAR2 := 'LOCK' )
25 IS
26 BEGIN
27 CSC_CUSTOMERS_MERGE(
28 req_id => req_id,
29 set_num => set_num,
30 process_mode => process_mode );
31
32 CSC_CUSTOMERS_AUDIT_HIST_MERGE(
33 req_id => req_id,
34 set_num => set_num,
35 process_mode => process_mode );
36
37 CSC_CUSTOMIZED_PLANS_MERGE(
38 req_id => req_id,
39 set_num => set_num,
40 process_mode => process_mode );
41
42 CSC_CUST_PLANS_MERGE(
43 req_id => req_id,
44 set_num => set_num,
45 process_mode => process_mode );
46
47 CSC_CUST_PLANS_AUDIT_MERGE(
48 req_id => req_id,
49 set_num => set_num,
50 process_mode => process_mode );
51
52 END CSC_MERGE_ALL_ACCOUNTS;
53
54 PROCEDURE CSC_CUSTOMERS_MERGE (
55 req_id NUMBER,
56 set_num NUMBER,
57 process_mode VARCHAR2) IS
58
59 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
60 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
61 INDEX BY BINARY_INTEGER;
62 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
63
64 TYPE cust_account_id_LIST_TYPE IS TABLE OF
65 CSC_CUSTOMERS.cust_account_id%TYPE
66 INDEX BY BINARY_INTEGER;
67 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
68 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
69
70 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
71 CSC_CUSTOMERS.PARTY_ID%TYPE
72 INDEX BY BINARY_INTEGER;
73 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
74 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
75
76 l_profile_val VARCHAR2(30);
77 CURSOR merged_records IS
78 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
79 ,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
80 FROM CSC_CUSTOMERS yt, ra_customer_merges m, hz_cust_accounts hzca
81 WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
82 AND m.process_flag = 'N'
83 AND m.request_id = req_id
84 AND m.set_number = set_num;
85 l_last_fetch BOOLEAN := FALSE;
86 l_count NUMBER := 0;
87 BEGIN
88 IF process_mode='LOCK' THEN
89 NULL;
90 ELSE
91 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
92 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSC_CUSTOMERS',FALSE);
93 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
94 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
95
96 open merged_records;
97 LOOP
98 FETCH merged_records BULK COLLECT INTO
99 MERGE_HEADER_ID_LIST
100 , NUM_COL1_NEW_LIST
101 , NUM_COL1_ORIG_LIST
102 , NUM_COL2_NEW_LIST
103 , NUM_COL2_ORIG_LIST
104 limit 1000;
105 IF merged_records%NOTFOUND THEN
106 l_last_fetch := TRUE;
107 END IF;
108 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
109 exit;
110 END IF;
111 -- FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
112 -- NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
113 -- END LOOP;
114 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
115 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
116 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
117 MERGE_LOG_ID,
118 TABLE_NAME,
119 MERGE_HEADER_ID,
120 NUM_COL1_ORIG,
121 NUM_COL1_NEW,
122 NUM_COL2_ORIG,
123 NUM_COL2_NEW,
124 ACTION_FLAG,
125 REQUEST_ID,
126 CREATED_BY,
127 CREATION_DATE,
128 LAST_UPDATE_LOGIN,
129 LAST_UPDATE_DATE,
130 LAST_UPDATED_BY
131 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
132 'CSC_CUSTOMERS',
133 MERGE_HEADER_ID_LIST(I),
134 NUM_COL1_ORIG_LIST(I),
135 NUM_COL1_NEW_LIST(I),
136 NUM_COL2_ORIG_LIST(I),
137 NUM_COL2_NEW_LIST(I),
138 'U',
139 req_id,
140 hz_utility_pub.CREATED_BY,
141 hz_utility_pub.CREATION_DATE,
142 hz_utility_pub.LAST_UPDATE_LOGIN,
143 hz_utility_pub.LAST_UPDATE_DATE,
144 hz_utility_pub.LAST_UPDATED_BY
145 );
146
147 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
148 UPDATE CSC_CUSTOMERS yt SET
149 cust_account_id=NUM_COL1_NEW_LIST(I)
150 , party_id=NUM_COL2_NEW_LIST(I)
151 , LAST_UPDATE_DATE=SYSDATE
152 , last_updated_by=arp_standard.profile.user_id
153 , last_update_login=arp_standard.profile.last_update_login
154 , REQUEST_ID=req_id
155 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
156 , PROGRAM_ID=arp_standard.profile.program_id
157 , PROGRAM_UPDATE_DATE=SYSDATE
158 WHERE cust_account_id in ( SELECT m.duplicate_id FROM
159 ra_customer_merges m WHERE
160 m.process_flag = 'N'
161 AND m.request_id = req_id
162 AND m.set_number = set_num )
163 ;
167 END IF;
164 l_count := l_count + SQL%ROWCOUNT;
165 IF l_last_fetch THEN
166 EXIT;
168 END LOOP;
169
170 arp_message.set_name('AR','AR_ROWS_UPDATED');
171 arp_message.set_token('NUM_ROWS',to_char(l_count));
172 END IF;
173 EXCEPTION
174 WHEN OTHERS THEN
175 arp_message.set_line( 'CSC_CUSTOMERS_MERGE');
176 RAISE;
177 END CSC_CUSTOMERS_MERGE;
178
179 PROCEDURE CSC_CUSTOMERS_AUDIT_HIST_MERGE (
180 req_id NUMBER,
181 set_num NUMBER,
182 process_mode VARCHAR2) IS
183
184 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
185 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
186 INDEX BY BINARY_INTEGER;
187 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
188
189 TYPE cust_account_id_LIST_TYPE IS TABLE OF
190 CSC_CUSTOMERS_AUDIT_HIST.cust_account_id%TYPE
191 INDEX BY BINARY_INTEGER;
192 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
193 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
194
195 l_profile_val VARCHAR2(30);
196 CURSOR merged_records IS
197 SELECT distinct CUSTOMER_MERGE_HEADER_ID
198 ,m.customer_id, m.duplicate_id
199 FROM CSC_CUSTOMERS_AUDIT_HIST yt, ra_customer_merges m , hz_cust_accounts hzca
200 WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
201 AND m.process_flag = 'N'
202 AND m.request_id = req_id
203 AND m.set_number = set_num;
204 l_last_fetch BOOLEAN := FALSE;
205 l_count NUMBER := 0;
206 BEGIN
207 IF process_mode='LOCK' THEN
208 NULL;
209 ELSE
210 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
211 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSC_CUSTOMERS_AUDIT_HIST',FALSE);
212 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
213 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
214
215 open merged_records;
216 LOOP
217 FETCH merged_records BULK COLLECT INTO
218 MERGE_HEADER_ID_LIST
219 , NUM_COL1_NEW_LIST
220 , NUM_COL1_ORIG_LIST
221 limit 1000;
222 IF merged_records%NOTFOUND THEN
223 l_last_fetch := TRUE;
224 END IF;
225 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
226 exit;
227 END IF;
228 -- FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
229 -- NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
230 -- END LOOP;
231 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
232 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
233 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
234 MERGE_LOG_ID,
235 TABLE_NAME,
236 MERGE_HEADER_ID,
237 NUM_COL1_ORIG,
238 NUM_COL1_NEW,
239 ACTION_FLAG,
240 REQUEST_ID,
241 CREATED_BY,
242 CREATION_DATE,
243 LAST_UPDATE_LOGIN,
244 LAST_UPDATE_DATE,
245 LAST_UPDATED_BY
246 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
247 'CSC_CUSTOMERS_AUDIT_HIST',
248 MERGE_HEADER_ID_LIST(I),
249 NUM_COL1_ORIG_LIST(I),
250 NUM_COL1_NEW_LIST(I),
251 'U',
252 req_id,
253 hz_utility_pub.CREATED_BY,
254 hz_utility_pub.CREATION_DATE,
255 hz_utility_pub.LAST_UPDATE_LOGIN,
256 hz_utility_pub.LAST_UPDATE_DATE,
257 hz_utility_pub.LAST_UPDATED_BY
258 );
259
260 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
261 UPDATE CSC_CUSTOMERS_AUDIT_HIST yt SET
262 cust_account_id=NUM_COL1_NEW_LIST(I)
263 , LAST_UPDATE_DATE=SYSDATE
264 , last_updated_by=arp_standard.profile.user_id
265 , last_update_login=arp_standard.profile.last_update_login
266 , REQUEST_ID=req_id
267 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
268 , PROGRAM_ID=arp_standard.profile.program_id
269 , PROGRAM_UPDATE_DATE=SYSDATE
270 WHERE cust_account_id in ( SELECT m.duplicate_id FROM
271 ra_customer_merges m WHERE
272 m.process_flag = 'N'
273 AND m.request_id = req_id
274 AND m.set_number = set_num )
275 ;
276 l_count := l_count + SQL%ROWCOUNT;
277 IF l_last_fetch THEN
278 EXIT;
279 END IF;
280 END LOOP;
281
282 arp_message.set_name('AR','AR_ROWS_UPDATED');
283 arp_message.set_token('NUM_ROWS',to_char(l_count));
284 END IF;
285 EXCEPTION
286 WHEN OTHERS THEN
287 arp_message.set_line( 'CSC_CUSTOMERS_AUDIT_HIST_MERGE');
288 RAISE;
289 END CSC_CUSTOMERS_AUDIT_HIST_MERGE;
290
291 PROCEDURE CSC_CUSTOMIZED_PLANS_MERGE (
292 req_id NUMBER,
293 set_num NUMBER,
294 process_mode VARCHAR2) IS
295
296 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
297 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
298 INDEX BY BINARY_INTEGER;
299 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
300
301 TYPE cust_account_id_LIST_TYPE IS TABLE OF
302 CSC_CUSTOMIZED_PLANS.cust_account_id%TYPE
303 INDEX BY BINARY_INTEGER;
304 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
305 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
306
307 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
308 CSC_CUSTOMIZED_PLANS.PARTY_ID%TYPE
309 INDEX BY BINARY_INTEGER;
310 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
311 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
312
316 ,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
313 l_profile_val VARCHAR2(30);
314 CURSOR merged_records IS
315 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
317 FROM CSC_CUSTOMIZED_PLANS yt, ra_customer_merges m , hz_cust_accounts hzca
318 WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
319 AND m.process_flag = 'N'
320 AND m.request_id = req_id
321 AND m.set_number = set_num;
322 l_last_fetch BOOLEAN := FALSE;
323 l_count NUMBER := 0;
324 BEGIN
325 IF process_mode='LOCK' THEN
326 NULL;
327 ELSE
328 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
329 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSC_CUSTOMIZED_PLANS',FALSE);
330 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
331 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
332
333 open merged_records;
334 LOOP
335 FETCH merged_records BULK COLLECT INTO
336 MERGE_HEADER_ID_LIST
337 , NUM_COL1_NEW_LIST
338 , NUM_COL1_ORIG_LIST
339 , NUM_COL2_NEW_LIST
340 , NUM_COL2_ORIG_LIST
341 limit 1000;
342 IF merged_records%NOTFOUND THEN
343 l_last_fetch := TRUE;
344 END IF;
345 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
346 exit;
347 END IF;
348 -- FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
349 -- NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
350 -- END LOOP;
351 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
352 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
353 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
354 MERGE_LOG_ID,
355 TABLE_NAME,
356 MERGE_HEADER_ID,
357 NUM_COL1_ORIG,
358 NUM_COL1_NEW,
359 NUM_COL2_ORIG,
360 NUM_COL2_NEW,
361 ACTION_FLAG,
362 REQUEST_ID,
363 CREATED_BY,
364 CREATION_DATE,
365 LAST_UPDATE_LOGIN,
366 LAST_UPDATE_DATE,
367 LAST_UPDATED_BY
368 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
369 'CSC_CUSTOMIZED_PLANS',
370 MERGE_HEADER_ID_LIST(I),
371 NUM_COL1_ORIG_LIST(I),
372 NUM_COL1_NEW_LIST(I),
373 NUM_COL2_ORIG_LIST(I),
374 NUM_COL2_NEW_LIST(I),
375 'U',
376 req_id,
377 hz_utility_pub.CREATED_BY,
378 hz_utility_pub.CREATION_DATE,
379 hz_utility_pub.LAST_UPDATE_LOGIN,
380 hz_utility_pub.LAST_UPDATE_DATE,
381 hz_utility_pub.LAST_UPDATED_BY
382 );
383
384 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
385 UPDATE CSC_CUSTOMIZED_PLANS yt SET
386 cust_account_id=NUM_COL1_NEW_LIST(I)
387 , party_id=NUM_COL2_NEW_LIST(I)
388 -- , LAST_UPDATE_DATE=SYSDATE
389 -- , last_updated_by=arp_standard.profile.user_id
390 -- , last_update_login=arp_standard.profile.last_update_login
391 , REQUEST_ID=req_id
392 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
393 , PROGRAM_ID=arp_standard.profile.program_id
394 , PROGRAM_UPDATE_DATE=SYSDATE
395 WHERE cust_account_id in ( SELECT m.duplicate_id FROM
396 ra_customer_merges m WHERE
397 m.process_flag = 'N'
398 AND m.request_id = req_id
399 AND m.set_number = set_num )
400 ;
401 l_count := l_count + SQL%ROWCOUNT;
402 IF l_last_fetch THEN
403 EXIT;
404 END IF;
405 END LOOP;
406
407 arp_message.set_name('AR','AR_ROWS_UPDATED');
408 arp_message.set_token('NUM_ROWS',to_char(l_count));
409 END IF;
410 EXCEPTION
411 WHEN OTHERS THEN
412 arp_message.set_line( 'CSC_CUSTOMIZED_PLANS_MERGE');
413 RAISE;
414 END CSC_CUSTOMIZED_PLANS_MERGE;
415
416 PROCEDURE CSC_CUST_PLANS_MERGE (
417 req_id NUMBER,
418 set_num NUMBER,
419 process_mode VARCHAR2) IS
420
421 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
422 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
423 INDEX BY BINARY_INTEGER;
424 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
425
426 TYPE CUST_PLAN_ID_LIST_TYPE IS TABLE OF
427 CSC_CUST_PLANS.CUST_PLAN_ID%TYPE
428 INDEX BY BINARY_INTEGER;
429 PRIMARY_KEY_ID_LIST CUST_PLAN_ID_LIST_TYPE;
430
431 TYPE cust_account_id_LIST_TYPE IS TABLE OF
432 CSC_CUST_PLANS.cust_account_id%TYPE
433 INDEX BY BINARY_INTEGER;
434 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
435 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
436
437 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
438 CSC_CUST_PLANS.PARTY_ID%TYPE
439 INDEX BY BINARY_INTEGER;
440 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
441 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
442
443 l_profile_val VARCHAR2(30);
444 CURSOR merged_records IS
445 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
446 ,yt.cust_plan_id
447 ,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
448 FROM CSC_CUST_PLANS yt, ra_customer_merges m , hz_cust_accounts hzca
449 WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
450 AND m.process_flag = 'N'
451 AND m.request_id = req_id
452 AND m.set_number = set_num;
453 l_last_fetch BOOLEAN := FALSE;
454 l_count NUMBER := 0;
455 BEGIN
456 IF process_mode='LOCK' THEN
457 NULL;
461 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
458 ELSE
459 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
460 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSC_CUST_PLANS',FALSE);
462 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
463
464 open merged_records;
465 LOOP
466 FETCH merged_records BULK COLLECT INTO
467 MERGE_HEADER_ID_LIST
468 , PRIMARY_KEY_ID_LIST
469 , NUM_COL1_NEW_LIST
470 , NUM_COL1_ORIG_LIST
471 , NUM_COL2_NEW_LIST
472 , NUM_COL2_ORIG_LIST
473 limit 1000;
474 IF merged_records%NOTFOUND THEN
475 l_last_fetch := TRUE;
476 END IF;
477 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
478 exit;
479 END IF;
480 -- FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
481 -- NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
482 -- END LOOP;
483 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
484 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
485 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
486 MERGE_LOG_ID,
487 TABLE_NAME,
488 MERGE_HEADER_ID,
489 PRIMARY_KEY_ID,
490 NUM_COL1_ORIG,
491 NUM_COL1_NEW,
492 NUM_COL2_ORIG,
493 NUM_COL2_NEW,
494 ACTION_FLAG,
495 REQUEST_ID,
496 CREATED_BY,
497 CREATION_DATE,
498 LAST_UPDATE_LOGIN,
499 LAST_UPDATE_DATE,
500 LAST_UPDATED_BY
501 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
502 'CSC_CUST_PLANS',
503 MERGE_HEADER_ID_LIST(I),
504 PRIMARY_KEY_ID_LIST(I),
505 NUM_COL1_ORIG_LIST(I),
506 NUM_COL1_NEW_LIST(I),
507 NUM_COL2_ORIG_LIST(I),
508 NUM_COL2_NEW_LIST(I),
509 'U',
510 req_id,
511 hz_utility_pub.CREATED_BY,
512 hz_utility_pub.CREATION_DATE,
513 hz_utility_pub.LAST_UPDATE_LOGIN,
514 hz_utility_pub.LAST_UPDATE_DATE,
515 hz_utility_pub.LAST_UPDATED_BY
516 );
517
518 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
519 UPDATE CSC_CUST_PLANS yt SET
520 cust_account_id=NUM_COL1_NEW_LIST(I)
521 , party_id=NUM_COL2_NEW_LIST(I)
522 , LAST_UPDATE_DATE=SYSDATE
523 , last_updated_by=arp_standard.profile.user_id
524 , last_update_login=arp_standard.profile.last_update_login
525 , REQUEST_ID=req_id
526 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
527 , PROGRAM_ID=arp_standard.profile.program_id
528 , PROGRAM_UPDATE_DATE=SYSDATE
529 WHERE cust_account_id in ( SELECT m.duplicate_id FROM
530 ra_customer_merges m WHERE
531 m.process_flag = 'N'
532 AND m.request_id = req_id
533 AND m.set_number = set_num )
534 AND plan_id not in ( SELECT yts.plan_id FROM
535 csc_cust_plans yts, ra_customer_merges m WHERE
536 yts.cust_account_id = m.customer_id
537 AND m.process_flag = 'N'
538 AND m.request_id = req_id
539 AND m.set_number = set_num )
540 ;
541 l_count := l_count + SQL%ROWCOUNT;
542
543 DELETE FROM CSC_CUST_PLANS
544 WHERE cust_account_id in ( SELECT m.duplicate_id FROM
545 ra_customer_merges m WHERE
546 m.process_flag = 'N'
547 AND m.request_id = req_id
548 AND m.set_number = set_num )
549 AND plan_id in ( SELECT yts.plan_id FROM
550 csc_cust_plans yts, ra_customer_merges m WHERE
551 yts.cust_account_id = m.customer_id
552 AND m.process_flag = 'N'
553 AND m.request_id = req_id
554 AND m.set_number = set_num )
555 ;
556
557 l_count := l_count + SQL%ROWCOUNT;
558 IF l_last_fetch THEN
559 EXIT;
560 END IF;
561 END LOOP;
562
563 arp_message.set_name('AR','AR_ROWS_UPDATED');
564 arp_message.set_token('NUM_ROWS',to_char(l_count));
565 END IF;
566 EXCEPTION
567 WHEN OTHERS THEN
568 arp_message.set_line( 'CSC_CUST_PLANS_MERGE');
569 RAISE;
570 END CSC_CUST_PLANS_MERGE;
571
572 PROCEDURE CSC_CUST_PLANS_AUDIT_MERGE (
573 req_id NUMBER,
574 set_num NUMBER,
575 process_mode VARCHAR2) IS
576
577 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
578 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
579 INDEX BY BINARY_INTEGER;
580 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
581
582 TYPE PLAN_AUDIT_ID_LIST_TYPE IS TABLE OF
583 CSC_CUST_PLANS_AUDIT.PLAN_AUDIT_ID%TYPE
584 INDEX BY BINARY_INTEGER;
585 PRIMARY_KEY_ID_LIST PLAN_AUDIT_ID_LIST_TYPE;
586
587 TYPE cust_account_id_LIST_TYPE IS TABLE OF
588 CSC_CUST_PLANS_AUDIT.cust_account_id%TYPE
589 INDEX BY BINARY_INTEGER;
590 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
591 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
592
593 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
594 CSC_CUST_PLANS.PARTY_ID%TYPE
595 INDEX BY BINARY_INTEGER;
596 NUM_COL2_ORIG_LIST PARTY_ID_LIST_TYPE;
597 NUM_COL2_NEW_LIST PARTY_ID_LIST_TYPE;
598
599 l_profile_val VARCHAR2(30);
600 CURSOR merged_records IS
601 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
602 ,yt.plan_audit_id
603 ,m.customer_id, m.duplicate_id, hzca.party_id, yt.party_id
604 FROM CSC_CUST_PLANS_AUDIT yt, ra_customer_merges m , hz_cust_accounts hzca
608 AND m.set_number = set_num;
605 WHERE ( yt.cust_account_id = m.duplicate_id AND m.customer_id = hzca.cust_account_id )
606 AND m.process_flag = 'N'
607 AND m.request_id = req_id
609 l_last_fetch BOOLEAN := FALSE;
610 l_count NUMBER := 0;
611 BEGIN
612 IF process_mode='LOCK' THEN
613 NULL;
614 ELSE
615 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
616 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','CSC_CUST_PLANS_AUDIT',FALSE);
617 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
618 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
619
620 open merged_records;
621 LOOP
622 FETCH merged_records BULK COLLECT INTO
623 MERGE_HEADER_ID_LIST
624 , PRIMARY_KEY_ID_LIST
625 , NUM_COL1_NEW_LIST
626 , NUM_COL1_ORIG_LIST
627 , NUM_COL2_NEW_LIST
628 , NUM_COL2_ORIG_LIST
629 limit 1000;
630 IF merged_records%NOTFOUND THEN
631 l_last_fetch := TRUE;
632 END IF;
633 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
634 exit;
635 END IF;
636 -- FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
637 -- NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
638 -- END LOOP;
639 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
640 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
641 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
642 MERGE_LOG_ID,
643 TABLE_NAME,
644 MERGE_HEADER_ID,
645 PRIMARY_KEY_ID,
646 NUM_COL1_ORIG,
647 NUM_COL1_NEW,
648 NUM_COL2_ORIG,
649 NUM_COL2_NEW,
650 ACTION_FLAG,
651 REQUEST_ID,
652 CREATED_BY,
653 CREATION_DATE,
654 LAST_UPDATE_LOGIN,
655 LAST_UPDATE_DATE,
656 LAST_UPDATED_BY
657 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
658 'CSC_CUST_PLANS_AUDIT',
659 MERGE_HEADER_ID_LIST(I),
660 PRIMARY_KEY_ID_LIST(I),
661 NUM_COL1_ORIG_LIST(I),
662 NUM_COL1_NEW_LIST(I),
663 NUM_COL2_ORIG_LIST(I),
664 NUM_COL2_NEW_LIST(I),
665 'U',
666 req_id,
667 hz_utility_pub.CREATED_BY,
668 hz_utility_pub.CREATION_DATE,
669 hz_utility_pub.LAST_UPDATE_LOGIN,
670 hz_utility_pub.LAST_UPDATE_DATE,
671 hz_utility_pub.LAST_UPDATED_BY
672 );
673
674 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
675 UPDATE CSC_CUST_PLANS_AUDIT yt SET
676 cust_account_id=NUM_COL1_NEW_LIST(I)
677 , party_id=NUM_COL2_NEW_LIST(I)
678 , LAST_UPDATE_DATE=SYSDATE
679 , last_updated_by=arp_standard.profile.user_id
680 , last_update_login=arp_standard.profile.last_update_login
681 , REQUEST_ID=req_id
682 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
683 , PROGRAM_ID=arp_standard.profile.program_id
684 , PROGRAM_UPDATE_DATE=SYSDATE
685 WHERE cust_account_id in ( SELECT m.duplicate_id FROM
686 ra_customer_merges m WHERE
687 m.process_flag = 'N'
688 AND m.request_id = req_id
689 AND m.set_number = set_num )
690 AND plan_id not in ( SELECT yts.plan_id FROM
691 csc_cust_plans_audit yts, ra_customer_merges m WHERE
692 yts.cust_account_id = m.customer_id
693 AND m.process_flag = 'N'
694 AND m.request_id = req_id
695 AND m.set_number = set_num )
696 ;
697 l_count := l_count + SQL%ROWCOUNT;
698 IF l_last_fetch THEN
699 EXIT;
700 END IF;
701 END LOOP;
702
703 arp_message.set_name('AR','AR_ROWS_UPDATED');
704 arp_message.set_token('NUM_ROWS',to_char(l_count));
705 END IF;
706 EXCEPTION
707 WHEN OTHERS THEN
708 arp_message.set_line( 'CSC_CUST_PLANS_AUDIT_MERGE');
709 RAISE;
710 END CSC_CUST_PLANS_AUDIT_MERGE;
711
712 END CSC_ACCOUNT_MERGE_PKG;