[Home] [Help]
PACKAGE BODY: APPS.OTAP_CMERGE
Source
1 Package Body OTAP_CMERGE as
2 /* $Header: otapcmer.pkb 120.0 2005/05/29 06:58:12 appldev noship $ */
3 --
4 --
5 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
6 g_count NUMBER := 0;
7 --
8 --
9 --
10 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
11 /*-------------------------------------------------------------
12 |
13 | PROCEDURE
14 | OTA_TBD
15 | DESCRIPTION :
16 | Account merge procedure for the table, OTA_BOOKING_DEALS
17 |
18 | NOTES:
19 |--------------------------------------------------------------*/
20 PROCEDURE OTA_TBD (
21 req_id NUMBER,
22 set_num NUMBER,
23 process_mode VARCHAR2) IS
24
25 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
26 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
27 INDEX BY BINARY_INTEGER;
28 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
29
30 TYPE BOOKING_DEAL_ID_LIST_TYPE IS TABLE OF
31 OTA_BOOKING_DEALS.BOOKING_DEAL_ID%TYPE
32 INDEX BY BINARY_INTEGER;
33 PRIMARY_KEY_ID_LIST BOOKING_DEAL_ID_LIST_TYPE;
34
35 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
36 OTA_BOOKING_DEALS.CUSTOMER_ID%TYPE
37 INDEX BY BINARY_INTEGER;
38 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
39 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
40
41 l_profile_val VARCHAR2(30);
42 CURSOR merged_records IS
43 SELECT distinct CUSTOMER_MERGE_HEADER_ID
44 ,BOOKING_DEAL_ID
45 ,yt.CUSTOMER_ID
46 FROM OTA_BOOKING_DEALS yt, ra_customer_merges m
47 WHERE (
48 yt.CUSTOMER_ID = m.DUPLICATE_ID
49 ) AND m.process_flag = 'N'
50 AND m.request_id = req_id
51 AND m.set_number = set_num;
52 l_last_fetch BOOLEAN := FALSE;
53 l_count NUMBER := 0;
54 BEGIN
55 IF process_mode='LOCK' THEN
56 NULL;
57 ELSE
58 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
59 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OTA_BOOKING_DEALS',FALSE);
60 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
61 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
62
63 open merged_records;
64 LOOP
65 FETCH merged_records BULK COLLECT INTO
66 MERGE_HEADER_ID_LIST
67 , PRIMARY_KEY_ID_LIST
68 , NUM_COL1_ORIG_LIST
69 limit 1000;
70 IF merged_records%NOTFOUND THEN
71 l_last_fetch := TRUE;
72 END IF;
73 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
74 exit;
75 END IF;
76 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
77 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
78 END LOOP;
79 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
80 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
81 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
82 MERGE_LOG_ID,
83 TABLE_NAME,
84 MERGE_HEADER_ID,
85 PRIMARY_KEY_ID,
86 NUM_COL1_ORIG,
87 NUM_COL1_NEW,
88 ACTION_FLAG,
89 REQUEST_ID,
90 CREATED_BY,
91 CREATION_DATE,
92 LAST_UPDATE_LOGIN,
93 LAST_UPDATE_DATE,
94 LAST_UPDATED_BY
95 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
96 'OTA_BOOKING_DEALS',
97 MERGE_HEADER_ID_LIST(I),
98 PRIMARY_KEY_ID_LIST(I),
99 NUM_COL1_ORIG_LIST(I),
100 NUM_COL1_NEW_LIST(I),
101 'U',
102 req_id,
103 hz_utility_pub.CREATED_BY,
104 hz_utility_pub.CREATION_DATE,
105 hz_utility_pub.LAST_UPDATE_LOGIN,
106 hz_utility_pub.LAST_UPDATE_DATE,
107 hz_utility_pub.LAST_UPDATED_BY
108 );
109
110 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
111 UPDATE OTA_BOOKING_DEALS yt SET
112 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
113 , LAST_UPDATE_DATE=SYSDATE
114 , last_updated_by=arp_standard.profile.user_id
115 , last_update_login=arp_standard.profile.last_update_login
116 WHERE BOOKING_DEAL_ID=PRIMARY_KEY_ID_LIST(I)
117 ;
118 l_count := l_count + SQL%ROWCOUNT;
119 IF l_last_fetch THEN
120 EXIT;
121 END IF;
122 END LOOP;
123
124 arp_message.set_name('AR','AR_ROWS_UPDATED');
125 arp_message.set_token('NUM_ROWS',to_char(l_count));
126 END IF;
127 EXCEPTION
128 WHEN OTHERS THEN
129 arp_message.set_line( 'OTA_TBD');
130 RAISE;
131 END OTA_TBD;
132
133 --
134 /*-------------------------------------------------------------
135 |
136 | PROCEDURE
137 | OTA_TDB
138 | DESCRIPTION :
139 | Account merge procedure for the table, OTA_DELEGATE_BOOKINGS
140 |
141 | NOTES:
142 |--------------------------------------------------------------*/
143 PROCEDURE OTA_TDB (
144 req_id NUMBER,
145 set_num NUMBER,
146 process_mode VARCHAR2) IS
147
148 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
149 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
150 INDEX BY BINARY_INTEGER;
151 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
152
153 TYPE BOOKING_ID_LIST_TYPE IS TABLE OF
154 OTA_DELEGATE_BOOKINGS.BOOKING_ID%TYPE
155 INDEX BY BINARY_INTEGER;
156 PRIMARY_KEY_ID_LIST BOOKING_ID_LIST_TYPE;
157
158 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
159 OTA_DELEGATE_BOOKINGS.CUSTOMER_ID%TYPE
160 INDEX BY BINARY_INTEGER;
161 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
162 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
163
164 TYPE THIRD_PARTY_CUST_ID_LIST_TYPE IS TABLE OF
165 OTA_DELEGATE_BOOKINGS.THIRD_PARTY_CUSTOMER_ID%TYPE
166 INDEX BY BINARY_INTEGER;
167 NUM_COL2_ORIG_LIST THIRD_PARTY_CUST_ID_LIST_TYPE;
168 NUM_COL2_NEW_LIST THIRD_PARTY_CUST_ID_LIST_TYPE;
169
170 TYPE CONTACT_ADDRESS_ID_LIST_TYPE IS TABLE OF
171 OTA_DELEGATE_BOOKINGS.CONTACT_ADDRESS_ID%TYPE
172 INDEX BY BINARY_INTEGER;
173 NUM_COL3_ORIG_LIST CONTACT_ADDRESS_ID_LIST_TYPE;
174 NUM_COL3_NEW_LIST CONTACT_ADDRESS_ID_LIST_TYPE;
175
176 TYPE THIRD_PARTY_ADDR_ID_LIST_TYPE IS TABLE OF
177 OTA_DELEGATE_BOOKINGS.THIRD_PARTY_ADDRESS_ID%TYPE
178 INDEX BY BINARY_INTEGER;
179 NUM_COL4_ORIG_LIST THIRD_PARTY_ADDR_ID_LIST_TYPE;
180 NUM_COL4_NEW_LIST THIRD_PARTY_ADDR_ID_LIST_TYPE;
181
182 l_profile_val VARCHAR2(30);
183 CURSOR merged_records IS
184 SELECT distinct CUSTOMER_MERGE_HEADER_ID
185 ,BOOKING_ID
186 ,yt.CUSTOMER_ID
187 ,THIRD_PARTY_CUSTOMER_ID
188 ,CONTACT_ADDRESS_ID
189 ,THIRD_PARTY_ADDRESS_ID
190 FROM OTA_DELEGATE_BOOKINGS yt, ra_customer_merges m
191 WHERE (
192 yt.CUSTOMER_ID = m.DUPLICATE_ID
193 OR yt.THIRD_PARTY_CUSTOMER_ID = m.DUPLICATE_ID
194 OR yt.CONTACT_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
195 OR yt.THIRD_PARTY_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
196 ) AND m.process_flag = 'N'
197 AND m.request_id = req_id
198 AND m.set_number = set_num;
199 l_last_fetch BOOLEAN := FALSE;
200 l_count NUMBER := 0;
201 BEGIN
202 IF process_mode='LOCK' THEN
203 NULL;
204 ELSE
205 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
206 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OTA_DELEGATE_BOOKINGS',FALSE);
207 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
208 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
209
210
211 open merged_records;
212 LOOP
213 FETCH merged_records BULK COLLECT INTO
214 MERGE_HEADER_ID_LIST
215 , PRIMARY_KEY_ID_LIST
216 , NUM_COL1_ORIG_LIST
217 , NUM_COL2_ORIG_LIST
218 , NUM_COL3_ORIG_LIST
219 , NUM_COL4_ORIG_LIST
220 limit 1000;
221 IF merged_records%NOTFOUND THEN
222 l_last_fetch := TRUE;
223 END IF;
224 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
225 exit;
226 END IF;
227 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
228 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
229 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
230 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL3_ORIG_LIST(I));
231
232 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL4_ORIG_LIST(I));
233
234 END LOOP;
235 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
236 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
237 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
238 MERGE_LOG_ID,
239 TABLE_NAME,
240 MERGE_HEADER_ID,
241 PRIMARY_KEY_ID,
242 NUM_COL1_ORIG,
243 NUM_COL1_NEW,
244 NUM_COL2_ORIG,
245 NUM_COL2_NEW,
246 NUM_COL3_ORIG,
247 NUM_COL3_NEW,
248 NUM_COL4_ORIG,
249 NUM_COL4_NEW,
250 ACTION_FLAG,
251 REQUEST_ID,
252 CREATED_BY,
253 CREATION_DATE,
254 LAST_UPDATE_LOGIN,
255 LAST_UPDATE_DATE,
256 LAST_UPDATED_BY
257 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
258 'OTA_DELEGATE_BOOKINGS',
259 MERGE_HEADER_ID_LIST(I),
260 PRIMARY_KEY_ID_LIST(I),
261 NUM_COL1_ORIG_LIST(I),
262 NUM_COL1_NEW_LIST(I),
263 NUM_COL2_ORIG_LIST(I),
264 NUM_COL2_NEW_LIST(I),
265 NUM_COL3_ORIG_LIST(I),
266 NUM_COL3_NEW_LIST(I),
267 NUM_COL4_ORIG_LIST(I),
268 NUM_COL4_NEW_LIST(I),
269 'U',
270 req_id,
271 hz_utility_pub.CREATED_BY,
272 hz_utility_pub.CREATION_DATE,
273 hz_utility_pub.LAST_UPDATE_LOGIN,
274 hz_utility_pub.LAST_UPDATE_DATE,
275 hz_utility_pub.LAST_UPDATED_BY
276 );
277
278 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
279 UPDATE OTA_DELEGATE_BOOKINGS yt SET
280 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
281 ,THIRD_PARTY_CUSTOMER_ID=NUM_COL2_NEW_LIST(I)
282 ,CONTACT_ADDRESS_ID=NUM_COL3_NEW_LIST(I)
283 ,THIRD_PARTY_ADDRESS_ID=NUM_COL4_NEW_LIST(I)
284 , LAST_UPDATE_DATE=SYSDATE
285 , last_updated_by=arp_standard.profile.user_id
286 , last_update_login=arp_standard.profile.last_update_login
287 WHERE BOOKING_ID=PRIMARY_KEY_ID_LIST(I)
288 ;
289 l_count := l_count + SQL%ROWCOUNT;
290 IF l_last_fetch THEN
291 EXIT;
292 END IF;
293 END LOOP;
294
295 arp_message.set_name('AR','AR_ROWS_UPDATED');
296 arp_message.set_token('NUM_ROWS',to_char(l_count));
297 END IF;
298 EXCEPTION
299 WHEN OTHERS THEN
300 arp_message.set_line( 'OTA_TDB');
301 RAISE;
302 END OTA_TDB;
303
304 --
305 /*-------------------------------------------------------------
306 |
307 | PROCEDURE
308 | OTA_TFH
309 | DESCRIPTION :
310 | Account merge procedure for the table, OTA_FINANCE_HEADERS
311 |
312 | NOTES:
313 |--------------------------------------------------------------*/
314 PROCEDURE OTA_TFH (
315 req_id NUMBER,
316 set_num NUMBER,
317 process_mode VARCHAR2) IS
318
319 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
320 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
321 INDEX BY BINARY_INTEGER;
322 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
323
324 TYPE FINANCE_HEADER_ID_LIST_TYPE IS TABLE OF
325 OTA_FINANCE_HEADERS.FINANCE_HEADER_ID%TYPE
326 INDEX BY BINARY_INTEGER;
327 PRIMARY_KEY_ID_LIST FINANCE_HEADER_ID_LIST_TYPE;
328
329 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
330 OTA_FINANCE_HEADERS.CUSTOMER_ID%TYPE
331 INDEX BY BINARY_INTEGER;
332 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
333 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
334
335 -- Bug 3590109 Address Merge
336 TYPE ADDRESS_ID_LIST_TYPE IS TABLE OF
337 OTA_FINANCE_HEADERS.ADDRESS_ID%TYPE
338 INDEX BY BINARY_INTEGER;
339 NUM_COL2_ORIG_LIST ADDRESS_ID_LIST_TYPE;
340 NUM_COL2_NEW_LIST ADDRESS_ID_LIST_TYPE;
341
342 TYPE INVOICE_ADDR_STR_LIST_TYPE IS TABLE OF
343 OTA_FINANCE_HEADERS.INVOICE_ADDRESS%TYPE
344 INDEX BY BINARY_INTEGER;
345 VCHAR_COL1_ORIG_LIST INVOICE_ADDR_STR_LIST_TYPE;
346 VCHAR_COL1_NEW_LIST INVOICE_ADDR_STR_LIST_TYPE;
347
348 l_profile_val VARCHAR2(30);
349 CURSOR merged_records IS
350 SELECT distinct CUSTOMER_MERGE_HEADER_ID
351 ,FINANCE_HEADER_ID
352 ,yt.CUSTOMER_ID
353 ,yt.ADDRESS_ID
354 ,yt.INVOICE_ADDRESS
355 FROM OTA_FINANCE_HEADERS yt, ra_customer_merges m
356 WHERE (
357 yt.CUSTOMER_ID = m.DUPLICATE_ID
358 ) AND m.process_flag = 'N'
359 AND m.request_id = req_id
360 AND m.set_number = set_num;
361
362 -- Bug 3590109 Address Merge, Address concat csr
363 CURSOR csr_new_invoice_addr_str(NEW_CUST_ACCT_SITE_ID NUMBER) IS
364 SELECT DISTINCT LOC.ADDRESS1||DECODE(LOC.ADDRESS1,NULL,'',', ')||
365 LOC.ADDRESS2||DECODE(LOC.ADDRESS2,NULL,'',', ')||
366 LOC.ADDRESS3|| DECODE(LOC.ADDRESS3,NULL,'',', ')||
367 LOC.ADDRESS4||DECODE(LOC.ADDRESS4,NULL,'',', ')||
368 LOC.CITY||DECODE(LOC.CITY,NULL, '',', ')||
369 LOC.STATE||DECODE(LOC.STATE,NULL,'',', ')||
370 LOC.PROVINCE||DECODE(LOC.PROVINCE,NULL,'',', ')||
371 LOC.COUNTY||DECODE(LOC.COUNTY,NULL,'',', ')||
372 LOC.POSTAL_CODE||DECODE(LOC.POSTAL_CODE,NULL,'',', ')||
373 LOC.COUNTRY ADDRESS
374 FROM
375 HZ_PARTY_SITES PARTY_SITE,
376 HZ_LOCATIONS LOC,
377 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
378 WHERE
379 LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
380 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
381 AND ACCT_SITE.STATUS = 'A'
382 AND ACCT_SITE.CUST_ACCT_SITE_ID = NEW_CUST_ACCT_SITE_ID;
383
384 l_last_fetch BOOLEAN := FALSE;
385 l_count NUMBER := 0;
386 l_new_invoice_addr_str VARCHAR2(200);
387 BEGIN
388 IF process_mode='LOCK' THEN
389 NULL;
390 ELSE
391 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
392 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OTA_FINANCE_HEADERS',FALSE);
393 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
394 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
395
396 open merged_records;
397 LOOP
398 FETCH merged_records BULK COLLECT INTO
399 MERGE_HEADER_ID_LIST
400 , PRIMARY_KEY_ID_LIST
401 , NUM_COL1_ORIG_LIST
402 , NUM_COL2_ORIG_LIST
403 , VCHAR_COL1_ORIG_LIST
404 limit 1000;
405 IF merged_records%NOTFOUND THEN
406 l_last_fetch := TRUE;
407 END IF;
408 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
409 exit;
410 END IF;
411 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
412 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
413 -- Bug 3590109 Address Merge
414 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
415
416 --Retrieve concat address for NUM_COL2_NEW_LIST(I)
417 OPEN csr_new_invoice_addr_str(NUM_COL2_NEW_LIST(I));
418 FETCH csr_new_invoice_addr_str into l_new_invoice_addr_str;
419 IF csr_new_invoice_addr_str%FOUND THEN
420 VCHAR_COL1_NEW_LIST(I) := l_new_invoice_addr_str;
421 END IF;
422 CLOSE csr_new_invoice_addr_str;
423
424 END LOOP;
425 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
426 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
427 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
428 MERGE_LOG_ID,
429 TABLE_NAME,
430 MERGE_HEADER_ID,
431 PRIMARY_KEY_ID,
432 NUM_COL1_ORIG,
433 NUM_COL1_NEW,
434 --address bug
435 NUM_COL2_ORIG,
436 NUM_COL2_NEW,
437 VCHAR_COL1_ORIG,
438 VCHAR_COL1_NEW,
439 ACTION_FLAG,
440 REQUEST_ID,
441 CREATED_BY,
442 CREATION_DATE,
443 LAST_UPDATE_LOGIN,
444 LAST_UPDATE_DATE,
445 LAST_UPDATED_BY
446 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
447 'OTA_FINANCE_HEADERS',
448 MERGE_HEADER_ID_LIST(I),
449 PRIMARY_KEY_ID_LIST(I),
450 NUM_COL1_ORIG_LIST(I),
451 NUM_COL1_NEW_LIST(I),
452 -- Bug 3590109 Address Merge
453 NUM_COL2_ORIG_LIST(I),
454 NUM_COL2_NEW_LIST(I),
455 VCHAR_COL1_ORIG_LIST(I),
456 VCHAR_COL1_NEW_LIST(I),
457 'U',
458 req_id,
459 hz_utility_pub.CREATED_BY,
460 hz_utility_pub.CREATION_DATE,
461 hz_utility_pub.LAST_UPDATE_LOGIN,
462 hz_utility_pub.LAST_UPDATE_DATE,
463 hz_utility_pub.LAST_UPDATED_BY
464 );
465
466 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
467 UPDATE OTA_FINANCE_HEADERS yt SET
468 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
469 -- Bug 3590109 Address Merge
470 , ADDRESS_ID=NUM_COL2_NEW_LIST(I)
471 , INVOICE_ADDRESS=VCHAR_COL1_NEW_LIST(I)
472 , LAST_UPDATE_DATE=SYSDATE
473 , last_updated_by=arp_standard.profile.user_id
474 , last_update_login=arp_standard.profile.last_update_login
475 WHERE FINANCE_HEADER_ID=PRIMARY_KEY_ID_LIST(I)
476 ;
477 l_count := l_count + SQL%ROWCOUNT;
478 IF l_last_fetch THEN
479 EXIT;
480 END IF;
481 END LOOP;
482
483 arp_message.set_name('AR','AR_ROWS_UPDATED');
484 arp_message.set_token('NUM_ROWS',to_char(l_count));
485 END IF;
486 EXCEPTION
487 WHEN OTHERS THEN
488 arp_message.set_line( 'OTA_TFH');
489 RAISE;
490 END OTA_TFH;
491
492 --
493 /*-------------------------------------------------------------
494 |
495 | PROCEDURE
496 | OTA_TEA
497 | DESCRIPTION :
498 | Account merge procedure for the table, OTA_EVENT_ASSOCIATIONS
499 |
500 | NOTES:
501 |--------------------------------------------------------------*/
502 PROCEDURE OTA_TEA (
503 req_id NUMBER,
504 set_num NUMBER,
505 process_mode VARCHAR2) IS
506
507 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
508 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
509 INDEX BY BINARY_INTEGER;
510 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
511
512 TYPE EVENT_ASSOCIATION_ID_LIST_TYPE IS TABLE OF
513 OTA_EVENT_ASSOCIATIONS.EVENT_ASSOCIATION_ID%TYPE
514 INDEX BY BINARY_INTEGER;
515 PRIMARY_KEY_ID1_LIST EVENT_ASSOCIATION_ID_LIST_TYPE;
516
517 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
518 OTA_EVENT_ASSOCIATIONS.CUSTOMER_ID%TYPE
519 INDEX BY BINARY_INTEGER;
520 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
521 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
522
523 TYPE EVENT_ID_LIST_TYPE IS TABLE OF
524 OTA_EVENT_ASSOCIATIONS.EVENT_ID%TYPE
525 INDEX BY BINARY_INTEGER;
526
527 EVENT_ID_LIST EVENT_ID_LIST_TYPE;
528
529 l_profile_val VARCHAR2(30);
530 CURSOR merged_records IS
531 SELECT distinct CUSTOMER_MERGE_HEADER_ID
532 ,EVENT_ASSOCIATION_ID
533 ,yt.CUSTOMER_ID
534 ,EVENT_ID
535 FROM OTA_EVENT_ASSOCIATIONS yt, ra_customer_merges m
536 WHERE (
537 yt.CUSTOMER_ID = m.DUPLICATE_ID
538 ) AND m.process_flag = 'N'
539 AND m.request_id = req_id
540 AND m.set_number = set_num;
541
542 CURSOR csr_duplicate_record(EVENT_ID NUMBER
543 ,TO_CUSTOMER NUMBER) IS
544 SELECT NULL
545 FROM OTA_EVENT_ASSOCIATIONS tea
546 WHERE tea.event_id = event_id
547 AND tea.CUSTOMER_ID = TO_CUSTOMER;
548
549 CURSOR csr_evt_assoc(P_EVENT_ASSOCIATION_ID NUMBER) IS
550 select *
551 from ota_event_associations
552 where EVENT_ASSOCIATION_ID = P_EVENT_ASSOCIATION_ID;
553
554 l_duplicate_result VARCHAR2(30);
555 l_last_fetch BOOLEAN := FALSE;
556 l_count NUMBER := 0;
557 l_del_count NUMBER := 0;
558 l_evt_assoc_rec csr_evt_assoc%rowtype;
559 BEGIN
560 IF process_mode='LOCK' THEN
561 NULL;
562 ELSE
563 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
564 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OTA_EVENT_ASSOCIATIONS',FALSE);
565 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
566 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
567
568 open merged_records;
569 LOOP
570 FETCH merged_records BULK COLLECT INTO
571 MERGE_HEADER_ID_LIST
572 , PRIMARY_KEY_ID1_LIST
573 , NUM_COL1_ORIG_LIST
574 , EVENT_ID_LIST
575 limit 1000;
576 IF merged_records%NOTFOUND THEN
577 l_last_fetch := TRUE;
578 END IF;
579 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
580 exit;
581 END IF;
582 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
583 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
584 END LOOP;
585
586 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
587 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
588 --Duplicate check
589 OPEN csr_duplicate_record(EVENT_ID_LIST(I), NUM_COL1_NEW_LIST(I));
590 FETCH csr_duplicate_record into l_duplicate_result;
591
592 IF csr_duplicate_record%FOUND THEN
593
594 --Retrieve OTA_EVENT_ASSOCIATIONS data for HZ_CUSTOMER_MERGE_LOG record
595 OPEN csr_evt_assoc(PRIMARY_KEY_ID1_LIST(I));
596 FETCH csr_evt_assoc into l_evt_assoc_rec;
597
598 IF csr_evt_assoc%FOUND THEN
599 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
600 MERGE_LOG_ID,
601 TABLE_NAME,
602 MERGE_HEADER_ID,
603 PRIMARY_KEY_ID1,
604 NUM_COL1_ORIG,
605 NUM_COL1_NEW,
606 ACTION_FLAG,
607 REQUEST_ID,
608 -- tea cols
609 DEL_COL1,
610 DEL_COL2,
611 DEL_COL3,
612 DEL_COL4,
613 DEL_COL5,
614 DEL_COL6,
615 DEL_COL7,
616 DEL_COL8,
617 DEL_COL9,
618 DEL_COL10,
619 DEL_COL11,
620 DEL_COL12,
621 DEL_COL13,
622 DEL_COL14,
623 DEL_COL15,
624 DEL_COL16,
625 DEL_COL17,
626 DEL_COL18,
627 DEL_COL19,
628 DEL_COL20,
629 DEL_COL21,
630 DEL_COL22,
631 DEL_COL23,
632 DEL_COL24,
633 DEL_COL25,
634 DEL_COL26,
635 DEL_COL27,
636 DEL_COL28,
637 DEL_COL29,
638 DEL_COL30,
639 DEL_COL31,
640 DEL_COL32,
641 DEL_COL33,
642 DEL_COL34,
643 DEL_COL35,
644 DEL_COL36,
645 DEL_COL37,
646 DEL_COL38,
647 DEL_COL39,
648 DEL_COL40,
649 DEL_COL41,
650 CREATED_BY,
651 CREATION_DATE,
652 LAST_UPDATE_LOGIN,
653 LAST_UPDATE_DATE,
654 LAST_UPDATED_BY
655 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval
656 , 'OTA_EVENT_ASSOCIATIONS'
657 , MERGE_HEADER_ID_LIST(I)
658 , PRIMARY_KEY_ID1_LIST(I)
659 , NUM_COL1_ORIG_LIST(I)
660 , NUM_COL1_NEW_LIST(I)
661 , 'D'
662 , req_id
663 --tea cols
664 , l_evt_assoc_rec.EVENT_ASSOCIATION_ID
665 , l_evt_assoc_rec.event_id
666 , l_evt_assoc_rec.JOB_ID
667 , l_evt_assoc_rec.POSITION_ID
668 , l_evt_assoc_rec.CUSTOMER_ID
669 , l_evt_assoc_rec.COMMENTS
670 , l_evt_assoc_rec.LAST_UPDATE_DATE
671 , l_evt_assoc_rec.LAST_UPDATED_BY
672 , l_evt_assoc_rec.LAST_UPDATE_LOGIN
673 , l_evt_assoc_rec.CREATED_BY
674 , l_evt_assoc_rec.CREATION_DATE
675 , l_evt_assoc_rec.TEA_INFORMATION_CATEGORY
676 , l_evt_assoc_rec.TEA_INFORMATION1
677 , l_evt_assoc_rec.TEA_INFORMATION2
678 , l_evt_assoc_rec.TEA_INFORMATION3
679 , l_evt_assoc_rec.TEA_INFORMATION4
680 , l_evt_assoc_rec.TEA_INFORMATION5
681 , l_evt_assoc_rec.TEA_INFORMATION6
682 , l_evt_assoc_rec.TEA_INFORMATION7
683 , l_evt_assoc_rec.TEA_INFORMATION8
684 , l_evt_assoc_rec.TEA_INFORMATION9
685 , l_evt_assoc_rec.TEA_INFORMATION10
686 , l_evt_assoc_rec.TEA_INFORMATION11
687 , l_evt_assoc_rec.TEA_INFORMATION12
688 , l_evt_assoc_rec.TEA_INFORMATION13
689 , l_evt_assoc_rec.TEA_INFORMATION14
690 , l_evt_assoc_rec.TEA_INFORMATION15
691 , l_evt_assoc_rec.TEA_INFORMATION16
692 , l_evt_assoc_rec.TEA_INFORMATION17
693 , l_evt_assoc_rec.TEA_INFORMATION18
694 , l_evt_assoc_rec.TEA_INFORMATION19
695 , l_evt_assoc_rec.TEA_INFORMATION20
696 , l_evt_assoc_rec.CATEGORY_USAGE_ID
697 , l_evt_assoc_rec.ACTIVITY_VERSION_ID
698 , l_evt_assoc_rec.OFFERING_ID
699 , l_evt_assoc_rec.SELF_ENROLLMENT_FLAG
700 , l_evt_assoc_rec.MATCH_TYPE
701 , l_evt_assoc_rec.PERSON_ID
702 , l_evt_assoc_rec.PARTY_ID
703 , l_evt_assoc_rec.LEARNING_PATH_ID
704 , l_evt_assoc_rec.ORGANIZATION_ID
705 , hz_utility_pub.CREATED_BY
706 , hz_utility_pub.CREATION_DATE
707 , hz_utility_pub.LAST_UPDATE_LOGIN
708 , hz_utility_pub.LAST_UPDATE_DATE
709 , hz_utility_pub.LAST_UPDATED_BY
710 );
711 --Purge the "Merge From" record
712 DELETE OTA_EVENT_ASSOCIATIONS
713 WHERE EVENT_ASSOCIATION_ID=PRIMARY_KEY_ID1_LIST(I)
714 ;
715 l_del_count := l_del_count + SQL%ROWCOUNT;
716 -- remove table entries
717 NUM_COL1_ORIG_LIST.DELETE(I);
718 NUM_COL1_NEW_LIST.DELETE(I);
719 PRIMARY_KEY_ID1_LIST.DELETE(I);
720 MERGE_HEADER_ID_LIST.DELETE(I);
721 --commit;
722 END IF;
723 CLOSE csr_evt_assoc;
724 ELSE
725 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
726 MERGE_LOG_ID,
727 TABLE_NAME,
728 MERGE_HEADER_ID,
729 PRIMARY_KEY_ID1,
730 NUM_COL1_ORIG,
731 NUM_COL1_NEW,
732 ACTION_FLAG,
733 REQUEST_ID,
734 CREATED_BY,
735 CREATION_DATE,
736 LAST_UPDATE_LOGIN,
737 LAST_UPDATE_DATE,
738 LAST_UPDATED_BY
739 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
740 'OTA_EVENT_ASSOCIATIONS',
741 MERGE_HEADER_ID_LIST(I),
742 PRIMARY_KEY_ID1_LIST(I),
743 NUM_COL1_ORIG_LIST(I),
744 NUM_COL1_NEW_LIST(I),
745 'U',
746 req_id,
747 hz_utility_pub.CREATED_BY,
748 hz_utility_pub.CREATION_DATE,
749 hz_utility_pub.LAST_UPDATE_LOGIN,
750 hz_utility_pub.LAST_UPDATE_DATE,
751 hz_utility_pub.LAST_UPDATED_BY
752 );
753 END IF;
754 CLOSE csr_duplicate_record;
755 END LOOP;
756 -- if audit is not enabled just remove the dupl recs from the pl/sql tables.
757 ELSE
758 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
759 OPEN csr_duplicate_record(EVENT_ID_LIST(I), NUM_COL1_NEW_LIST(I));
760 FETCH csr_duplicate_record into l_duplicate_result;
761
762 IF csr_duplicate_record%FOUND THEN
763 --Purge the "Merge From" record
764 DELETE OTA_EVENT_ASSOCIATIONS
765 WHERE EVENT_ASSOCIATION_ID=PRIMARY_KEY_ID1_LIST(I)
766 ;
767 l_del_count := l_del_count + SQL%ROWCOUNT;
768 -- remove pl/sql table entries
769 NUM_COL1_ORIG_LIST.DELETE(I);
770 NUM_COL1_NEW_LIST.DELETE(I);
771 PRIMARY_KEY_ID1_LIST.DELETE(I);
772 MERGE_HEADER_ID_LIST.DELETE(I);
773 --commit;
774 END IF;
775 CLOSE csr_duplicate_record;
776 END LOOP;
777 END IF;
778
779 arp_message.set_name('AR','AR_ROWS_DELETED');
780 arp_message.set_token('NUM_ROWS',to_char(l_del_count));
781
782 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
783 IF MERGE_HEADER_ID_LIST.COUNT = 0 THEN
784 exit;
785 END IF;
786 UPDATE OTA_EVENT_ASSOCIATIONS yt SET
787 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
788 , LAST_UPDATE_DATE=SYSDATE
789 , last_updated_by=arp_standard.profile.user_id
790 , last_update_login=arp_standard.profile.last_update_login
791 WHERE EVENT_ASSOCIATION_ID=PRIMARY_KEY_ID1_LIST(I)
792 ;
793 l_count := l_count + SQL%ROWCOUNT;
794 IF l_last_fetch THEN
795 EXIT;
796 END IF;
797 END LOOP;
798
799 END LOOP;
800
801 arp_message.set_name('AR','AR_ROWS_UPDATED');
802 arp_message.set_token('NUM_ROWS',to_char(l_count));
803 END IF;
804 EXCEPTION
805 WHEN OTHERS THEN
806 arp_message.set_line( 'OTA_TEA');
807 RAISE;
808 END OTA_TEA;
809
810 --
811 /*-------------------------------------------------------------
812 |
813 | PROCEDURE
814 | OTA_TNH
815 | DESCRIPTION :
816 | Account merge procedure for the table, OTA_NOTRNG_HISTORIES
817 |
818 | NOTES:
819 |--------------------------------------------------------------*/
820 PROCEDURE OTA_TNH (
821 req_id NUMBER,
822 set_num NUMBER,
823 process_mode VARCHAR2) IS
824
825 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
826 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
827 INDEX BY BINARY_INTEGER;
828 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
829
830 TYPE NOTA_HISTORY_ID_LIST_TYPE IS TABLE OF
831 OTA_NOTRNG_HISTORIES.NOTA_HISTORY_ID%TYPE
832 INDEX BY BINARY_INTEGER;
833 PRIMARY_KEY_ID1_LIST NOTA_HISTORY_ID_LIST_TYPE;
834
835 TYPE CUSTOMER_ID_LIST_TYPE IS TABLE OF
836 OTA_NOTRNG_HISTORIES.CUSTOMER_ID%TYPE
837 INDEX BY BINARY_INTEGER;
838 NUM_COL1_ORIG_LIST CUSTOMER_ID_LIST_TYPE;
839 NUM_COL1_NEW_LIST CUSTOMER_ID_LIST_TYPE;
840
841 l_profile_val VARCHAR2(30);
842 CURSOR merged_records IS
843 SELECT distinct CUSTOMER_MERGE_HEADER_ID
844 ,NOTA_HISTORY_ID
845 ,yt.CUSTOMER_ID
846 FROM OTA_NOTRNG_HISTORIES yt, ra_customer_merges m
847 WHERE (
848 yt.CUSTOMER_ID = m.DUPLICATE_ID
849 ) AND m.process_flag = 'N'
850 AND m.request_id = req_id
851 AND m.set_number = set_num;
852 l_last_fetch BOOLEAN := FALSE;
853 l_count NUMBER := 0;
854 BEGIN
855 IF process_mode='LOCK' THEN
856 NULL;
857 ELSE
858 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
859 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OTA_NOTRNG_HISTORIES',FALSE);
860 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
861 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
862
863 open merged_records;
864 LOOP
865 FETCH merged_records BULK COLLECT INTO
866 MERGE_HEADER_ID_LIST
867 , PRIMARY_KEY_ID1_LIST
868 , NUM_COL1_ORIG_LIST
869 limit 1000;
870 IF merged_records%NOTFOUND THEN
871 l_last_fetch := TRUE;
872 END IF;
873 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
874 exit;
875 END IF;
876 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
877 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
878 END LOOP;
879 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
880 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
881 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
882 MERGE_LOG_ID,
883 TABLE_NAME,
884 MERGE_HEADER_ID,
885 PRIMARY_KEY_ID1,
886 NUM_COL1_ORIG,
887 NUM_COL1_NEW,
888 ACTION_FLAG,
889 REQUEST_ID,
890 CREATED_BY,
891 CREATION_DATE,
892 LAST_UPDATE_LOGIN,
893 LAST_UPDATE_DATE,
894 LAST_UPDATED_BY
895 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
896 'OTA_NOTRNG_HISTORIES',
897 MERGE_HEADER_ID_LIST(I),
898 PRIMARY_KEY_ID1_LIST(I),
899 NUM_COL1_ORIG_LIST(I),
900 NUM_COL1_NEW_LIST(I),
901 'U',
902 req_id,
903 hz_utility_pub.CREATED_BY,
904 hz_utility_pub.CREATION_DATE,
905 hz_utility_pub.LAST_UPDATE_LOGIN,
906 hz_utility_pub.LAST_UPDATE_DATE,
907 hz_utility_pub.LAST_UPDATED_BY
908 );
909
910 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
911 UPDATE OTA_NOTRNG_HISTORIES yt SET
912 CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
913 , LAST_UPDATE_DATE=SYSDATE
914 , last_updated_by=arp_standard.profile.user_id
915 , last_update_login=arp_standard.profile.last_update_login
916 WHERE NOTA_HISTORY_ID=PRIMARY_KEY_ID1_LIST(I)
917 ;
918 l_count := l_count + SQL%ROWCOUNT;
919 IF l_last_fetch THEN
920 EXIT;
921 END IF;
922 END LOOP;
923
924 arp_message.set_name('AR','AR_ROWS_UPDATED');
925 arp_message.set_token('NUM_ROWS',to_char(l_count));
926 END IF;
927 EXCEPTION
928 WHEN OTHERS THEN
929 arp_message.set_line( 'OTA_TNH');
930 RAISE;
931 END OTA_TNH;
932
933 --
934 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
935 --
936 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
937 --
938 BEGIN
939 --
940 arp_message.set_line( 'OTAP_CMERGE.MERGE()+' );
941 --
942 OTA_TDB( req_id, set_num, process_mode );
943 OTA_TBD( req_id, set_num, process_mode );
944 -- Bug 3561222
945 -- OTA_TEA( req_id, set_num, process_mode );
946 OTA_TFH( req_id, set_num, process_mode );
947 OTA_TNH( req_id, set_num, process_mode );
948 -- Bug 3561222
949 OTA_TEA( req_id, set_num, process_mode );
950 --
951 arp_message.set_line( 'OTAP_CMERGE.MERGE()-' );
952 --
953 END merge;
954 --
955 --
956 end OTAP_CMERGE;