[Home] [Help]
PACKAGE BODY: APPS.GMS_CMERGE
Source
4
1 PACKAGE BODY GMS_CMERGE AS
2 -- $Header: gmscmrgb.pls 120.2 2006/04/03 23:48:04 lveerubh ship $
3
5 /*-------------------------------------------------------------
6 |
7 | PROCEDURE
8 | MERGE_AWARDS
9 | DESCRIPTION :
10 | Account merge procedure for the table, GMS_AWARDS
11 |
12 |--------------------------------------------------------------*/
13
14 PROCEDURE MERGE_AWARDS (
15 req_id NUMBER,
16 set_num NUMBER,
17 process_mode VARCHAR2) IS
18
19 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
20 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
24 TYPE AWARD_ID_LIST_TYPE IS TABLE OF
21 INDEX BY BINARY_INTEGER;
22 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
23
25 GMS_AWARDS.AWARD_ID%TYPE
26 INDEX BY BINARY_INTEGER;
27 PRIMARY_KEY_ID_LIST AWARD_ID_LIST_TYPE;
28
29 TYPE BILL_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
30 GMS_AWARDS.BILL_TO_ADDRESS_ID%TYPE
31 INDEX BY BINARY_INTEGER;
32 NUM_COL1_ORIG_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
33 NUM_COL1_NEW_LIST BILL_TO_ADDRESS_ID_LIST_TYPE;
34
35 TYPE SHIP_TO_ADDRESS_ID_LIST_TYPE IS TABLE OF
36 GMS_AWARDS.SHIP_TO_ADDRESS_ID%TYPE
37 INDEX BY BINARY_INTEGER;
38 NUM_COL2_ORIG_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
39 NUM_COL2_NEW_LIST SHIP_TO_ADDRESS_ID_LIST_TYPE;
40
41 TYPE LOC_BILL_TO_ADD_ID_LIST_TYPE IS TABLE OF
42 GMS_AWARDS.LOC_BILL_TO_ADDRESS_ID%TYPE
43 INDEX BY BINARY_INTEGER;
44 NUM_COL3_ORIG_LIST LOC_BILL_TO_ADD_ID_LIST_TYPE;
45 NUM_COL3_NEW_LIST LOC_BILL_TO_ADD_ID_LIST_TYPE;
46
47 TYPE LOC_SHIP_TO_ADD_ID_LIST_TYPE IS TABLE OF
48 GMS_AWARDS.LOC_SHIP_TO_ADDRESS_ID%TYPE
49 INDEX BY BINARY_INTEGER;
50 NUM_COL4_ORIG_LIST LOC_SHIP_TO_ADD_ID_LIST_TYPE;
51 NUM_COL4_NEW_LIST LOC_SHIP_TO_ADD_ID_LIST_TYPE;
52
53 TYPE BILL_TO_CUSTOMER_ID_LIST_TYPE IS TABLE OF
54 GMS_AWARDS.BILL_TO_CUSTOMER_ID%TYPE
55 INDEX BY BINARY_INTEGER;
56 NUM_COL5_ORIG_LIST BILL_TO_CUSTOMER_ID_LIST_TYPE;
57 NUM_COL5_NEW_LIST BILL_TO_CUSTOMER_ID_LIST_TYPE;
58
59 TYPE FUNDING_SOURCE_ID_LIST_TYPE IS TABLE OF
60 GMS_AWARDS.FUNDING_SOURCE_ID%TYPE
61 INDEX BY BINARY_INTEGER;
62 NUM_COL6_ORIG_LIST FUNDING_SOURCE_ID_LIST_TYPE;
63 NUM_COL6_NEW_LIST FUNDING_SOURCE_ID_LIST_TYPE;
64
65 l_profile_val VARCHAR2(30);
66 CURSOR merged_records IS
67 SELECT distinct CUSTOMER_MERGE_HEADER_ID
68 ,AWARD_ID
69 ,BILL_TO_ADDRESS_ID
70 ,SHIP_TO_ADDRESS_ID
71 ,LOC_BILL_TO_ADDRESS_ID
72 ,LOC_SHIP_TO_ADDRESS_ID
73 ,BILL_TO_CUSTOMER_ID
74 ,FUNDING_SOURCE_ID
75 FROM GMS_AWARDS yt, ra_customer_merges m
76 WHERE (
77 yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
78 OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
79 OR yt.LOC_BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
80 OR yt.LOC_SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
81 OR yt.BILL_TO_CUSTOMER_ID = m.DUPLICATE_ID
82 OR yt.FUNDING_SOURCE_ID = m.DUPLICATE_ID
83 ) AND m.process_flag = 'N'
84 AND m.request_id = req_id
85 AND m.set_number = set_num;
86 l_last_fetch BOOLEAN := FALSE;
87 l_count NUMBER;
88 BEGIN
89 IF process_mode='LOCK' THEN
90 NULL;
91 ELSE
92 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
93 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','GMS_AWARDS',FALSE);
94 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
95 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
96
97 open merged_records;
98 LOOP
99 FETCH merged_records BULK COLLECT INTO
100 MERGE_HEADER_ID_LIST
101 , PRIMARY_KEY_ID_LIST
102 , NUM_COL1_ORIG_LIST
103 , NUM_COL2_ORIG_LIST
104 , NUM_COL3_ORIG_LIST
105 , NUM_COL4_ORIG_LIST
106 , NUM_COL5_ORIG_LIST
107 , NUM_COL6_ORIG_LIST
108 LIMIT 1000;
109 IF merged_records%NOTFOUND THEN
110 l_last_fetch := TRUE;
111 CLOSE merged_records; --Bug 4710433
112 END IF;
113 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
114 exit;
115 END IF;
116 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
117 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
118
119 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
120
121 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL3_ORIG_LIST(I));
122
123 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL4_ORIG_LIST(I));
124
125 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL5_ORIG_LIST(I));
126 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL6_ORIG_LIST(I));
127 END LOOP;
128 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
129 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
130 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
131 MERGE_LOG_ID,
132 TABLE_NAME,
133 MERGE_HEADER_ID,
134 PRIMARY_KEY_ID,
135 NUM_COL1_ORIG,
136 NUM_COL1_NEW,
137 NUM_COL2_ORIG,
138 NUM_COL2_NEW,
139 NUM_COL3_ORIG,
140 NUM_COL3_NEW,
141 NUM_COL4_ORIG,
142 NUM_COL4_NEW,
143 NUM_COL5_ORIG,
144 NUM_COL5_NEW,
145 NUM_COL6_ORIG,
146 NUM_COL6_NEW,
147 ACTION_FLAG,
148 REQUEST_ID,
149 CREATED_BY,
150 CREATION_DATE,
151 LAST_UPDATE_LOGIN,
152 LAST_UPDATE_DATE,
153 LAST_UPDATED_BY
154 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
155 'GMS_AWARDS',
156 MERGE_HEADER_ID_LIST(I),
157 PRIMARY_KEY_ID_LIST(I),
158 NUM_COL1_ORIG_LIST(I),
159 NUM_COL1_NEW_LIST(I),
160 NUM_COL2_ORIG_LIST(I),
161 NUM_COL2_NEW_LIST(I),
162 NUM_COL3_ORIG_LIST(I),
163 NUM_COL3_NEW_LIST(I),
164 NUM_COL4_ORIG_LIST(I),
165 NUM_COL4_NEW_LIST(I),
169 NUM_COL6_NEW_LIST(I),
166 NUM_COL5_ORIG_LIST(I),
167 NUM_COL5_NEW_LIST(I),
168 NUM_COL6_ORIG_LIST(I),
170 'U',
171 req_id,
172 hz_utility_pub.CREATED_BY,
173 hz_utility_pub.CREATION_DATE,
174 hz_utility_pub.LAST_UPDATE_LOGIN,
175 hz_utility_pub.LAST_UPDATE_DATE,
176 hz_utility_pub.LAST_UPDATED_BY
177 );
178
179 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
180 UPDATE GMS_AWARDS yt SET
181 BILL_TO_ADDRESS_ID=NUM_COL1_NEW_LIST(I)
182 ,SHIP_TO_ADDRESS_ID=NUM_COL2_NEW_LIST(I)
183 ,LOC_BILL_TO_ADDRESS_ID=NUM_COL3_NEW_LIST(I)
184 ,LOC_SHIP_TO_ADDRESS_ID=NUM_COL4_NEW_LIST(I)
185 ,BILL_TO_CUSTOMER_ID=NUM_COL5_NEW_LIST(I)
186 ,FUNDING_SOURCE_ID=NUM_COL6_NEW_LIST(I)
187 , LAST_UPDATE_DATE=SYSDATE
188 , last_updated_by=arp_standard.profile.user_id
189 , last_update_login=arp_standard.profile.last_update_login
190 WHERE AWARD_ID=PRIMARY_KEY_ID_LIST(I)
191 ;
192 l_count := l_count + SQL%ROWCOUNT;
193 IF l_last_fetch THEN
194 EXIT;
195 END IF;
196 END LOOP;
197
198 arp_message.set_name('AR','AR_ROWS_UPDATED');
199 arp_message.set_token('NUM_ROWS',to_char(l_count));
200 END IF;
201 EXCEPTION
202 WHEN OTHERS THEN
203 arp_message.set_line( 'MERGE_AWARDS');
204 RAISE;
205 END MERGE_AWARDS;
206
207
208 /*-------------------------------------------------------------
209 |
210 | PROCEDURE
211 | MERGE_CONTACTS
212 | DESCRIPTION :
213 | Account merge procedure for the table, GMS_AWARDS_CONTACTS
214 |
215 |--------------------------------------------------------------*/
216
217 PROCEDURE MERGE_CONTACTS (
218 req_id NUMBER,
219 set_num NUMBER,
220 process_mode VARCHAR2) IS
221
222 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
223 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
224 INDEX BY BINARY_INTEGER;
225 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
226
227 TYPE AWARD_ID_LIST_TYPE IS TABLE OF
228 GMS_AWARDS_CONTACTS.AWARD_ID%TYPE
229 INDEX BY BINARY_INTEGER;
230 PRIMARY_KEY1_LIST AWARD_ID_LIST_TYPE;
231
232 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
233 GMS_AWARDS_CONTACTS.CUSTOMER_ID%TYPE
234 INDEX BY BINARY_INTEGER;
235 PRIMARY_KEY2_LIST CUSTOMER_ID_LIST_TYPE;
236
237 TYPE CONTACT_ID_LIST_TYPE IS TABLE OF
238 GMS_AWARDS_CONTACTS.CONTACT_ID%TYPE
239 INDEX BY BINARY_INTEGER;
240 PRIMARY_KEY3_LIST CONTACT_ID_LIST_TYPE;
241
242 TYPE USAGE_CODE_LIST_TYPE IS TABLE OF
243 GMS_AWARDS_CONTACTS.USAGE_CODE%TYPE
244 INDEX BY BINARY_INTEGER;
245 PRIMARY_KEY4_LIST USAGE_CODE_LIST_TYPE;
246
247 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
248 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
249
250 l_profile_val VARCHAR2(30);
251 CURSOR merged_records IS
252 SELECT distinct CUSTOMER_MERGE_HEADER_ID
253 ,yt.AWARD_ID
254 ,yt.CUSTOMER_ID
255 ,yt.CONTACT_ID
256 ,yt.USAGE_CODE
257 ,yt.CUSTOMER_ID
258 FROM GMS_AWARDS_CONTACTS yt, ra_customer_merges m
259 WHERE (
260 yt.CUSTOMER_ID = m.DUPLICATE_ID
261 ) AND m.process_flag = 'N'
262 AND m.request_id = req_id
263 AND m.set_number = set_num;
264 l_last_fetch BOOLEAN := FALSE;
265 l_count NUMBER;
266 BEGIN
267 IF process_mode='LOCK' THEN
268 NULL;
269 ELSE
270 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
271 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','GMS_AWARDS_CONTACTS',FALSE);
272 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
273 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
274
275 open merged_records;
276 LOOP
277 FETCH merged_records BULK COLLECT INTO
278 MERGE_HEADER_ID_LIST
279 , PRIMARY_KEY1_LIST
280 , PRIMARY_KEY2_LIST
281 , PRIMARY_KEY3_LIST
282 , PRIMARY_KEY4_LIST
283 , NUM_COL1_ORIG_LIST
284 LIMIT 1000;
285 IF merged_records%NOTFOUND THEN
286 l_last_fetch := TRUE;
287 CLOSE merged_records; --Bug 4710433
288 END IF;
289 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
290 exit;
291 END IF;
292 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
293 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
294 END LOOP;
295 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
296 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
297 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
298 MERGE_LOG_ID,
299 TABLE_NAME,
300 MERGE_HEADER_ID,
301 PRIMARY_KEY1,
302 PRIMARY_KEY2,
303 PRIMARY_KEY3,
304 PRIMARY_KEY4,
305 NUM_COL1_ORIG,
306 NUM_COL1_NEW,
307 ACTION_FLAG,
308 REQUEST_ID,
309 CREATED_BY,
310 CREATION_DATE,
311 LAST_UPDATE_LOGIN,
312 LAST_UPDATE_DATE,
313 LAST_UPDATED_BY
314 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
315 'GMS_AWARDS_CONTACTS',
316 MERGE_HEADER_ID_LIST(I),
317 PRIMARY_KEY1_LIST(I),
318 PRIMARY_KEY2_LIST(I),
319 PRIMARY_KEY3_LIST(I),
323 'U',
320 PRIMARY_KEY4_LIST(I),
321 NUM_COL1_ORIG_LIST(I),
322 NUM_COL1_NEW_LIST(I),
324 req_id,
325 hz_utility_pub.CREATED_BY,
326 hz_utility_pub.CREATION_DATE,
327 hz_utility_pub.LAST_UPDATE_LOGIN,
328 hz_utility_pub.LAST_UPDATE_DATE,
329 hz_utility_pub.LAST_UPDATED_BY
330 );
331
332 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
333 UPDATE GMS_AWARDS_CONTACTS yt SET
334 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
335 , LAST_UPDATE_DATE=SYSDATE
336 , last_updated_by=arp_standard.profile.user_id
337 , last_update_login=arp_standard.profile.last_update_login
338 WHERE AWARD_ID=PRIMARY_KEY1_LIST(I)
339 AND CUSTOMER_ID=PRIMARY_KEY2_LIST(I)
340 AND CONTACT_ID=PRIMARY_KEY3_LIST(I)
341 AND USAGE_CODE=PRIMARY_KEY4_LIST(I)
342 ;
343 l_count := l_count + SQL%ROWCOUNT;
344 IF l_last_fetch THEN
345 EXIT;
346 END IF;
347 END LOOP;
348
349 arp_message.set_name('AR','AR_ROWS_UPDATED');
350 arp_message.set_token('NUM_ROWS',to_char(l_count));
351 END IF;
352 EXCEPTION
353 WHEN OTHERS THEN
354 arp_message.set_line( 'MERGE_CONTACTS');
355 RAISE;
356 END MERGE_CONTACTS;
357
358
359 /*-------------------------------------------------------------
360 |
361 | PROCEDURE
362 | MERGE_REPORTS
363 | DESCRIPTION :
364 | Account merge procedure for the table, GMS_REPORTS
365 |
366 |--------------------------------------------------------------*/
367
368 PROCEDURE MERGE_REPORTS (
369 req_id NUMBER,
370 set_num NUMBER,
371 process_mode VARCHAR2) IS
372
373 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
374 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
375 INDEX BY BINARY_INTEGER;
376 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
377
378 TYPE REPORT_ID_LIST_TYPE IS TABLE OF
379 GMS_REPORTS.REPORT_ID%TYPE
380 INDEX BY BINARY_INTEGER;
381 PRIMARY_KEY_ID_LIST REPORT_ID_LIST_TYPE;
382
383 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
384 GMS_REPORTS.SITE_USE_ID%TYPE
385 INDEX BY BINARY_INTEGER;
386 NUM_COL1_ORIG_LIST SITE_USE_ID_LIST_TYPE;
387 NUM_COL1_NEW_LIST SITE_USE_ID_LIST_TYPE;
388
389 l_profile_val VARCHAR2(30);
390 CURSOR merged_records IS
391 SELECT distinct CUSTOMER_MERGE_HEADER_ID
392 ,REPORT_ID
393 ,SITE_USE_ID
394 FROM GMS_REPORTS yt, ra_customer_merges m
395 WHERE (
396 yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
397 ) AND m.process_flag = 'N'
398 AND m.request_id = req_id
399 AND m.set_number = set_num;
400 l_last_fetch BOOLEAN := FALSE;
401 l_count NUMBER;
402 BEGIN
403 IF process_mode='LOCK' THEN
404 NULL;
405 ELSE
406 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
407 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','GMS_REPORTS',FALSE);
408 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
409 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
410
411 open merged_records;
412 LOOP
413 FETCH merged_records BULK COLLECT INTO
414 MERGE_HEADER_ID_LIST
415 , PRIMARY_KEY_ID_LIST
416 , NUM_COL1_ORIG_LIST
417 LIMIT 1000;
418 IF merged_records%NOTFOUND THEN
419 l_last_fetch := TRUE;
420 CLOSE merged_records; --Bug 4710433
421 END IF;
422 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
423 exit;
424 END IF;
425 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
426 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST(I));
427 END LOOP;
428 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
429 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
430 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
431 MERGE_LOG_ID,
432 TABLE_NAME,
433 MERGE_HEADER_ID,
434 PRIMARY_KEY_ID,
435 NUM_COL1_ORIG,
436 NUM_COL1_NEW,
437 ACTION_FLAG,
438 REQUEST_ID,
439 CREATED_BY,
440 CREATION_DATE,
441 LAST_UPDATE_LOGIN,
442 LAST_UPDATE_DATE,
443 LAST_UPDATED_BY
444 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
445 'GMS_REPORTS',
446 MERGE_HEADER_ID_LIST(I),
447 PRIMARY_KEY_ID_LIST(I),
448 NUM_COL1_ORIG_LIST(I),
449 NUM_COL1_NEW_LIST(I),
450 'U',
451 req_id,
452 hz_utility_pub.CREATED_BY,
453 hz_utility_pub.CREATION_DATE,
454 hz_utility_pub.LAST_UPDATE_LOGIN,
455 hz_utility_pub.LAST_UPDATE_DATE,
456 hz_utility_pub.LAST_UPDATED_BY
457 );
458
459 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
460 UPDATE GMS_REPORTS yt SET
461 SITE_USE_ID=NUM_COL1_NEW_LIST(I)
462 , LAST_UPDATE_DATE=SYSDATE
463 , last_updated_by=arp_standard.profile.user_id
464 , last_update_login=arp_standard.profile.last_update_login
465 WHERE REPORT_ID=PRIMARY_KEY_ID_LIST(I)
466 ;
467 l_count := l_count + SQL%ROWCOUNT;
468 IF l_last_fetch THEN
469 EXIT;
470 END IF;
471 END LOOP;
472
473 arp_message.set_name('AR','AR_ROWS_UPDATED');
474 arp_message.set_token('NUM_ROWS',to_char(l_count));
478 arp_message.set_line( 'MERGE_REPORTS');
475 END IF;
476 EXCEPTION
477 WHEN OTHERS THEN
479 RAISE;
480 END MERGE_REPORTS;
481
482
483 /*-------------------------------------------------------------
484 |
485 | PROCEDURE
486 | MERGE_DEFAULT_REPORTS
487 | DESCRIPTION :
488 | Account merge procedure for the table, GMS_DEFAULT_REPORTS
489 |
490 |--------------------------------------------------------------*/
491
492 PROCEDURE MERGE_DEFAULT_REPORTS (
493 req_id NUMBER,
494 set_num NUMBER,
495 process_mode VARCHAR2) IS
496
497 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
498 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
499 INDEX BY BINARY_INTEGER;
500 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
501
502 TYPE DEFAULT_REPORT_ID_LIST_TYPE IS TABLE OF
503 GMS_DEFAULT_REPORTS.DEFAULT_REPORT_ID%TYPE
504 INDEX BY BINARY_INTEGER;
505 PRIMARY_KEY_ID_LIST DEFAULT_REPORT_ID_LIST_TYPE;
506
507 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
508 GMS_DEFAULT_REPORTS.SITE_USE_ID%TYPE
509 INDEX BY BINARY_INTEGER;
510 NUM_COL1_ORIG_LIST SITE_USE_ID_LIST_TYPE;
511 NUM_COL1_NEW_LIST SITE_USE_ID_LIST_TYPE;
512
513 l_profile_val VARCHAR2(30);
514 CURSOR merged_records IS
515 SELECT distinct CUSTOMER_MERGE_HEADER_ID
516 ,DEFAULT_REPORT_ID
517 ,SITE_USE_ID
518 FROM GMS_DEFAULT_REPORTS yt, ra_customer_merges m
519 WHERE (
520 yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
521 ) AND m.process_flag = 'N'
522 AND m.request_id = req_id
523 AND m.set_number = set_num;
524 l_last_fetch BOOLEAN := FALSE;
525 l_count NUMBER;
526 BEGIN
527 IF process_mode='LOCK' THEN
528 NULL;
529 ELSE
530 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
531 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','GMS_DEFAULT_REPORTS',FALSE);
532 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
533 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
534
535 open merged_records;
536 LOOP
537 FETCH merged_records BULK COLLECT INTO
538 MERGE_HEADER_ID_LIST
539 , PRIMARY_KEY_ID_LIST
540 , NUM_COL1_ORIG_LIST
541 LIMIT 1000;
542 IF merged_records%NOTFOUND THEN
543 l_last_fetch := TRUE;
544 CLOSE merged_records; --Bug 4710433
545 END IF;
546 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
547 exit;
548 END IF;
549 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
550 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST(I));
551 END LOOP;
552 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
553 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
554 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
555 MERGE_LOG_ID,
556 TABLE_NAME,
557 MERGE_HEADER_ID,
558 PRIMARY_KEY_ID,
559 NUM_COL1_ORIG,
560 NUM_COL1_NEW,
561 ACTION_FLAG,
562 REQUEST_ID,
563 CREATED_BY,
564 CREATION_DATE,
565 LAST_UPDATE_LOGIN,
566 LAST_UPDATE_DATE,
567 LAST_UPDATED_BY
568 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
569 'GMS_DEFAULT_REPORTS',
570 MERGE_HEADER_ID_LIST(I),
571 PRIMARY_KEY_ID_LIST(I),
572 NUM_COL1_ORIG_LIST(I),
573 NUM_COL1_NEW_LIST(I),
574 'U',
575 req_id,
576 hz_utility_pub.CREATED_BY,
577 hz_utility_pub.CREATION_DATE,
578 hz_utility_pub.LAST_UPDATE_LOGIN,
579 hz_utility_pub.LAST_UPDATE_DATE,
580 hz_utility_pub.LAST_UPDATED_BY
581 );
582
583 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
584 UPDATE GMS_DEFAULT_REPORTS yt SET
585 SITE_USE_ID=NUM_COL1_NEW_LIST(I)
586 , LAST_UPDATE_DATE=SYSDATE
587 , last_updated_by=arp_standard.profile.user_id
588 , last_update_login=arp_standard.profile.last_update_login
589 WHERE DEFAULT_REPORT_ID=PRIMARY_KEY_ID_LIST(I)
590 ;
591 l_count := l_count + SQL%ROWCOUNT;
592 IF l_last_fetch THEN
593 EXIT;
594 END IF;
595 END LOOP;
596
597 arp_message.set_name('AR','AR_ROWS_UPDATED');
598 arp_message.set_token('NUM_ROWS',to_char(l_count));
599 END IF;
600 EXCEPTION
601 WHEN OTHERS THEN
602 arp_message.set_line( 'MERGE_DEFAULT_REPORTS');
603 RAISE;
604 END MERGE_DEFAULT_REPORTS;
605
606
607
608
609 PROCEDURE MERGE ( req_id IN NUMBER, set_no IN NUMBER, process_mode IN VARCHAR2 ) IS
610 --
611 -- Calling the above procedures to update the tables with customer related data.
612 --
613
614 BEGIN
615
616 MERGE_AWARDS(req_id => req_id,
617 set_num => set_no,
618 process_mode => process_mode);
619
620 MERGE_CONTACTS(req_id => req_id,
621 set_num => set_no,
622 process_mode => process_mode);
623
624 MERGE_REPORTS(req_id => req_id,
625 set_num => set_no,
626 process_mode => process_mode);
627
628 MERGE_DEFAULT_REPORTS(req_id => req_id,
629 set_num => set_no,
630 process_mode => process_mode);
631
632 END MERGE;
633
634 END GMS_CMERGE;