[Home] [Help]
PACKAGE BODY: APPS.OZF_ACCOUNT_MERGE_PKG
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_ACCOUNT_MERGE_PKG';
1 PACKAGE BODY OZF_ACCOUNT_MERGE_PKG AS
2 /* $Header: ozfvcmrb.pls 115.6 2004/05/07 05:23:06 samaresh ship $ */
3
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvcmrb.pls';
6 ------------------------------------------------------------------------------
7
8 /*-------------------------------------------------------------
9 |
10 | PROCEDURE
11 | merge_acct_alloc
12 | DESCRIPTION :
13 | Account merge procedure for the table, ozf_account_allocations
14 |
15 | NOTES:
16 |
17 |--------------------------------------------------------------*/
18
19 PROCEDURE merge_acct_alloc (
20 req_id NUMBER,
21 set_num NUMBER,
22 process_mode VARCHAR2) IS
23
24 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
25 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
26 INDEX BY BINARY_INTEGER;
27 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
28
29 TYPE account_alloc_id_LIST_TYPE IS TABLE OF
30 ozf_account_allocations.account_allocation_id%TYPE
31 INDEX BY BINARY_INTEGER;
32 PRIMARY_KEY_ID1_LIST account_alloc_id_LIST_TYPE;
33
34 TYPE cust_account_id_LIST_TYPE IS TABLE OF
35 ozf_account_allocations.cust_account_id%TYPE
36 INDEX BY BINARY_INTEGER;
37 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
38 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
39
40 TYPE bill_to_site_use_id_LIST_TYPE IS TABLE OF
41 ozf_account_allocations.bill_to_site_use_id%TYPE
42 INDEX BY BINARY_INTEGER;
43 NUM_COL2_ORIG_LIST bill_to_site_use_id_LIST_TYPE;
44 NUM_COL2_NEW_LIST bill_to_site_use_id_LIST_TYPE;
45
46 TYPE site_use_id_LIST_TYPE IS TABLE OF
47 ozf_account_allocations.site_use_id%TYPE
48 INDEX BY BINARY_INTEGER;
49 NUM_COL3_ORIG_LIST site_use_id_LIST_TYPE;
50 NUM_COL3_NEW_LIST site_use_id_LIST_TYPE;
51
52 l_profile_val VARCHAR2(30);
53 CURSOR merged_records IS
54 SELECT distinct CUSTOMER_MERGE_HEADER_ID
55 ,account_allocation_id
56 ,cust_account_id
57 ,bill_to_site_use_id
58 ,site_use_id
59 FROM ozf_account_allocations yt, ra_customer_merges m
60 WHERE (
61 yt.cust_account_id = m.DUPLICATE_ADDRESS_ID
62 OR yt.bill_to_site_use_id = m.DUPLICATE_SITE_ID
63 OR yt.site_use_id = m.DUPLICATE_SITE_ID
64 ) AND m.process_flag = 'N'
65 AND m.request_id = req_id
66 AND m.set_number = set_num;
67 l_last_fetch BOOLEAN := FALSE;
68 l_count NUMBER;
69 BEGIN
70 IF process_mode='LOCK' THEN
71 NULL;
72 ELSE
73 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
74 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_account_allocations',FALSE);
75 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
76 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
77
78 open merged_records;
79 LOOP
80 FETCH merged_records BULK COLLECT INTO
81 MERGE_HEADER_ID_LIST
82 , PRIMARY_KEY_ID1_LIST
83 , NUM_COL1_ORIG_LIST
84 , NUM_COL2_ORIG_LIST
85 , NUM_COL3_ORIG_LIST
86 ;
90 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
87 IF merged_records%NOTFOUND THEN
88 l_last_fetch := TRUE;
89 END IF;
91 exit;
92 END IF;
93 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
94 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
95
96 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
97 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
98 END LOOP;
99 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
100 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
101 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
102 MERGE_LOG_ID,
103 TABLE_NAME,
104 MERGE_HEADER_ID,
105 PRIMARY_KEY_ID1,
106 NUM_COL1_ORIG,
107 NUM_COL1_NEW,
108 NUM_COL2_ORIG,
109 NUM_COL2_NEW,
110 NUM_COL3_ORIG,
111 NUM_COL3_NEW,
112 ACTION_FLAG,
113 REQUEST_ID,
114 CREATED_BY,
115 CREATION_DATE,
116 LAST_UPDATE_LOGIN,
117 LAST_UPDATE_DATE,
118 LAST_UPDATED_BY
119 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
120 'ozf_account_allocations',
121 MERGE_HEADER_ID_LIST(I),
122 PRIMARY_KEY_ID1_LIST(I),
123 NUM_COL1_ORIG_LIST(I),
124 NUM_COL1_NEW_LIST(I),
125 NUM_COL2_ORIG_LIST(I),
126 NUM_COL2_NEW_LIST(I),
127 NUM_COL3_ORIG_LIST(I),
128 NUM_COL3_NEW_LIST(I),
129 'U',
130 req_id,
131 hz_utility_pub.CREATED_BY,
132 hz_utility_pub.CREATION_DATE,
133 hz_utility_pub.LAST_UPDATE_LOGIN,
134 hz_utility_pub.LAST_UPDATE_DATE,
135 hz_utility_pub.LAST_UPDATED_BY
136 );
137
138 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
139 UPDATE ozf_account_allocations yt SET
140 cust_account_id=NUM_COL1_NEW_LIST(I)
141 ,bill_to_site_use_id=NUM_COL2_NEW_LIST(I)
142 ,site_use_id=NUM_COL3_NEW_LIST(I)
143 , LAST_UPDATE_DATE=SYSDATE
144 , last_updated_by=arp_standard.profile.user_id
145 , last_update_login=arp_standard.profile.last_update_login
146 WHERE account_allocation_id=PRIMARY_KEY_ID1_LIST(I)
147 ;
148 l_count := l_count + SQL%ROWCOUNT;
149 IF l_last_fetch THEN
150 EXIT;
151 END IF;
152 END LOOP;
153
154 arp_message.set_name('AR','AR_ROWS_UPDATED');
155 arp_message.set_token('NUM_ROWS',to_char(l_count));
156 END IF;
157 EXCEPTION
158 WHEN OTHERS THEN
159 arp_message.set_line( 'merge_acct_alloc');
160 RAISE;
161 END merge_acct_alloc;
162
163 /*-------------------------------------------------------------
164 |
165 | PROCEDURE
166 | merge_claim_lines
167 | DESCRIPTION :
168 | Account merge procedure for the table, ozf_claim_lines
169 |
170 | NOTES:
171 |
172 |--------------------------------------------------------------*/
173
174 PROCEDURE merge_claim_lines (
175 req_id NUMBER,
176 set_num NUMBER,
177 process_mode VARCHAR2) IS
178
179 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
180 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
181 INDEX BY BINARY_INTEGER;
182 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
183
184 TYPE claim_line_id_LIST_TYPE IS TABLE OF
185 ozf_claim_lines.claim_line_id%TYPE
186 INDEX BY BINARY_INTEGER;
187 PRIMARY_KEY_ID1_LIST claim_line_id_LIST_TYPE;
188
189 TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
190 ozf_claim_lines.related_cust_account_id%TYPE
191 INDEX BY BINARY_INTEGER;
192 NUM_COL1_ORIG_LIST rel_cust_account_id_LIST_TYPE;
193 NUM_COL1_NEW_LIST rel_cust_account_id_LIST_TYPE;
194
195 TYPE buy_grp_cust_acct_id_LIST_TYPE IS TABLE OF
196 ozf_claim_lines.buy_group_cust_account_id%TYPE
197 INDEX BY BINARY_INTEGER;
198 NUM_COL2_ORIG_LIST buy_grp_cust_acct_id_LIST_TYPE;
199 NUM_COL2_NEW_LIST buy_grp_cust_acct_id_LIST_TYPE;
200
201 l_profile_val VARCHAR2(30);
202 CURSOR merged_records IS
203 SELECT distinct CUSTOMER_MERGE_HEADER_ID
204 ,claim_line_id
205 ,related_cust_account_id
206 ,buy_group_cust_account_id
207 FROM ozf_claim_lines yt, ra_customer_merges m
208 WHERE (
209 yt.related_cust_account_id = m.DUPLICATE_ID
210 OR yt.buy_group_cust_account_id = m.DUPLICATE_ID
211 ) AND m.process_flag = 'N'
212 AND m.request_id = req_id
213 AND m.set_number = set_num;
214 l_last_fetch BOOLEAN := FALSE;
215 l_count NUMBER;
216 BEGIN
217 IF process_mode='LOCK' THEN
218 NULL;
219 ELSE
220 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
221 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_claim_lines',FALSE);
222 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
223 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
224
225 open merged_records;
226 LOOP
227 FETCH merged_records BULK COLLECT INTO
228 MERGE_HEADER_ID_LIST
229 , PRIMARY_KEY_ID1_LIST
230 , NUM_COL1_ORIG_LIST
231 , NUM_COL2_ORIG_LIST
232 ;
233 IF merged_records%NOTFOUND THEN
234 l_last_fetch := TRUE;
235 END IF;
236 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
237 exit;
238 END IF;
239 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
240 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
241 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
242 END LOOP;
243 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
244 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
245 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
246 MERGE_LOG_ID,
247 TABLE_NAME,
248 MERGE_HEADER_ID,
249 PRIMARY_KEY_ID1,
250 NUM_COL1_ORIG,
251 NUM_COL1_NEW,
252 NUM_COL2_ORIG,
253 NUM_COL2_NEW,
254 ACTION_FLAG,
255 REQUEST_ID,
256 CREATED_BY,
257 CREATION_DATE,
258 LAST_UPDATE_LOGIN,
259 LAST_UPDATE_DATE,
260 LAST_UPDATED_BY
261 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
262 'ozf_claim_lines',
263 MERGE_HEADER_ID_LIST(I),
264 PRIMARY_KEY_ID1_LIST(I),
265 NUM_COL1_ORIG_LIST(I),
266 NUM_COL1_NEW_LIST(I),
267 NUM_COL2_ORIG_LIST(I),
268 NUM_COL2_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 ozf_claim_lines yt SET
280 related_cust_account_id=NUM_COL1_NEW_LIST(I)
281 ,buy_group_cust_account_id=NUM_COL2_NEW_LIST(I)
282 , LAST_UPDATE_DATE=SYSDATE
283 , last_updated_by=arp_standard.profile.user_id
284 , last_update_login=arp_standard.profile.last_update_login
285 , REQUEST_ID=req_id
286 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
287 , PROGRAM_ID=arp_standard.profile.program_id
288 , PROGRAM_UPDATE_DATE=SYSDATE
289 WHERE claim_line_id=PRIMARY_KEY_ID1_LIST(I)
290 ;
291 l_count := l_count + SQL%ROWCOUNT;
292 IF l_last_fetch THEN
293 EXIT;
294 END IF;
295 END LOOP;
296
297 arp_message.set_name('AR','AR_ROWS_UPDATED');
298 arp_message.set_token('NUM_ROWS',to_char(l_count));
299 END IF;
300 EXCEPTION
301 WHEN OTHERS THEN
302 arp_message.set_line( 'merge_claim_lines');
303 RAISE;
304 END merge_claim_lines;
305
306
307 /*-------------------------------------------------------------
308 |
309 | PROCEDURE
310 | merge_claim_lines_hist
311 | DESCRIPTION :
312 | Account merge procedure for the table, ozf_claim_lines_hist
313 |
314 | NOTES:
315 |
316 |--------------------------------------------------------------*/
317
318 PROCEDURE merge_claim_lines_hist (
319 req_id NUMBER,
320 set_num NUMBER,
321 process_mode VARCHAR2) IS
322
323 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
324 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
325 INDEX BY BINARY_INTEGER;
326 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
327
328 TYPE claim_line_hist_id_LIST_TYPE IS TABLE OF
329 ozf_claim_lines_hist.claim_line_history_id%TYPE
330 INDEX BY BINARY_INTEGER;
331 PRIMARY_KEY_ID1_LIST claim_line_hist_id_LIST_TYPE;
332
333 TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
334 ozf_claim_lines_hist.related_cust_account_id%TYPE
335 INDEX BY BINARY_INTEGER;
336 NUM_COL1_ORIG_LIST rel_cust_account_id_LIST_TYPE;
337 NUM_COL1_NEW_LIST rel_cust_account_id_LIST_TYPE;
338
339 TYPE buy_grp_cust_acct_id_LIST_TYPE IS TABLE OF
340 ozf_claim_lines_hist.buy_group_cust_account_id%TYPE
341 INDEX BY BINARY_INTEGER;
342 NUM_COL2_ORIG_LIST buy_grp_cust_acct_id_LIST_TYPE;
343 NUM_COL2_NEW_LIST buy_grp_cust_acct_id_LIST_TYPE;
344
345 l_profile_val VARCHAR2(30);
346 CURSOR merged_records IS
350 ,buy_group_cust_account_id
347 SELECT distinct CUSTOMER_MERGE_HEADER_ID
348 ,claim_line_history_id
349 ,related_cust_account_id
351 FROM ozf_claim_lines_hist yt, ra_customer_merges m
352 WHERE (
353 yt.related_cust_account_id = m.DUPLICATE_ID
354 OR yt.buy_group_cust_account_id = m.DUPLICATE_ID
355 ) AND m.process_flag = 'N'
356 AND m.request_id = req_id
357 AND m.set_number = set_num;
358 l_last_fetch BOOLEAN := FALSE;
359 l_count NUMBER;
360 BEGIN
361 IF process_mode='LOCK' THEN
362 NULL;
363 ELSE
364 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
365 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_claim_lines_hist',FALSE);
366 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
367 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
368
369 open merged_records;
370 LOOP
371 FETCH merged_records BULK COLLECT INTO
372 MERGE_HEADER_ID_LIST
373 , PRIMARY_KEY_ID1_LIST
374 , NUM_COL1_ORIG_LIST
375 , NUM_COL2_ORIG_LIST
376 ;
377 IF merged_records%NOTFOUND THEN
378 l_last_fetch := TRUE;
379 END IF;
380 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
381 exit;
382 END IF;
383 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
384 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
385 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
386 END LOOP;
387 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
388 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
389 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
390 MERGE_LOG_ID,
391 TABLE_NAME,
392 MERGE_HEADER_ID,
393 PRIMARY_KEY_ID1,
394 NUM_COL1_ORIG,
395 NUM_COL1_NEW,
396 NUM_COL2_ORIG,
397 NUM_COL2_NEW,
398 ACTION_FLAG,
399 REQUEST_ID,
400 CREATED_BY,
401 CREATION_DATE,
402 LAST_UPDATE_LOGIN,
403 LAST_UPDATE_DATE,
404 LAST_UPDATED_BY
405 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
406 'ozf_claim_lines_hist',
407 MERGE_HEADER_ID_LIST(I),
408 PRIMARY_KEY_ID1_LIST(I),
409 NUM_COL1_ORIG_LIST(I),
410 NUM_COL1_NEW_LIST(I),
411 NUM_COL2_ORIG_LIST(I),
412 NUM_COL2_NEW_LIST(I),
413 'U',
414 req_id,
415 hz_utility_pub.CREATED_BY,
416 hz_utility_pub.CREATION_DATE,
417 hz_utility_pub.LAST_UPDATE_LOGIN,
418 hz_utility_pub.LAST_UPDATE_DATE,
419 hz_utility_pub.LAST_UPDATED_BY
420 );
421
422 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
423 UPDATE ozf_claim_lines_hist yt SET
424 related_cust_account_id=NUM_COL1_NEW_LIST(I)
425 ,buy_group_cust_account_id=NUM_COL2_NEW_LIST(I)
426 , LAST_UPDATE_DATE=SYSDATE
427 , last_updated_by=arp_standard.profile.user_id
428 , last_update_login=arp_standard.profile.last_update_login
429 , REQUEST_ID=req_id
430 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
431 , PROGRAM_ID=arp_standard.profile.program_id
432 , PROGRAM_UPDATE_DATE=SYSDATE
433 WHERE claim_line_history_id=PRIMARY_KEY_ID1_LIST(I)
434 ;
435 l_count := l_count + SQL%ROWCOUNT;
436 IF l_last_fetch THEN
437 EXIT;
438 END IF;
439 END LOOP;
440
441 arp_message.set_name('AR','AR_ROWS_UPDATED');
442 arp_message.set_token('NUM_ROWS',to_char(l_count));
443 END IF;
444 EXCEPTION
445 WHEN OTHERS THEN
446 arp_message.set_line( 'merge_claim_lines_hist');
447 RAISE;
448 END merge_claim_lines_hist;
449
450
451 /*-------------------------------------------------------------
452 |
453 | PROCEDURE
454 | merge_claims
455 | DESCRIPTION :
456 | Account merge procedure for the table, ozf_claims
457 |
458 | NOTES:
459 |
460 |--------------------------------------------------------------*/
461
462 PROCEDURE merge_claims (
463 req_id NUMBER,
464 set_num NUMBER,
465 process_mode VARCHAR2) IS
466
467 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
468 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
469 INDEX BY BINARY_INTEGER;
470 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
471
472 TYPE claim_id_LIST_TYPE IS TABLE OF
473 ozf_claims.claim_id%TYPE
474 INDEX BY BINARY_INTEGER;
475 PRIMARY_KEY_ID1_LIST claim_id_LIST_TYPE;
476
477 TYPE cust_account_id_LIST_TYPE IS TABLE OF
478 ozf_claims.cust_account_id%TYPE
479 INDEX BY BINARY_INTEGER;
480 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
481 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
482
483 TYPE ship_to_cust_acct_id_LIST_TYPE IS TABLE OF
484 ozf_claims.ship_to_cust_account_id%TYPE
485 INDEX BY BINARY_INTEGER;
486 NUM_COL2_ORIG_LIST ship_to_cust_acct_id_LIST_TYPE;
487 NUM_COL2_NEW_LIST ship_to_cust_acct_id_LIST_TYPE;
488
489 TYPE cb_acct_site_id_LIST_TYPE IS TABLE OF
490 ozf_claims.cust_billto_acct_site_id%TYPE
491 INDEX BY BINARY_INTEGER;
492 NUM_COL3_ORIG_LIST cb_acct_site_id_LIST_TYPE;
493 NUM_COL3_NEW_LIST cb_acct_site_id_LIST_TYPE;
494
495 TYPE cs_acct_site_id_LIST_TYPE IS TABLE OF
496 ozf_claims.cust_shipto_acct_site_id%TYPE
497 INDEX BY BINARY_INTEGER;
498 NUM_COL4_ORIG_LIST cs_acct_site_id_LIST_TYPE;
499 NUM_COL4_NEW_LIST cs_acct_site_id_LIST_TYPE;
500
501 TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
502 ozf_claims.related_cust_account_id%TYPE
503 INDEX BY BINARY_INTEGER;
504 NUM_COL5_ORIG_LIST rel_cust_account_id_LIST_TYPE;
505 NUM_COL5_NEW_LIST rel_cust_account_id_LIST_TYPE;
506
507 TYPE related_site_use_id_LIST_TYPE IS TABLE OF
508 ozf_claims.related_site_use_id%TYPE
509 INDEX BY BINARY_INTEGER;
510 NUM_COL6_ORIG_LIST related_site_use_id_LIST_TYPE;
511 NUM_COL6_NEW_LIST related_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 ,claim_id
517 ,cust_account_id
518 ,ship_to_cust_account_id
519 ,cust_billto_acct_site_id
520 ,cust_shipto_acct_site_id
521 ,related_cust_account_id
522 ,related_site_use_id
523 FROM ozf_claims yt, ra_customer_merges m
524 WHERE (
525 yt.cust_account_id = m.DUPLICATE_ID
526 OR yt.ship_to_cust_account_id = m.DUPLICATE_ID
527 OR yt.cust_billto_acct_site_id = m.DUPLICATE_SITE_ID
528 OR yt.cust_shipto_acct_site_id = m.DUPLICATE_SITE_ID
529 OR yt.related_cust_account_id = m.DUPLICATE_ID
530 OR yt.related_site_use_id = m.DUPLICATE_SITE_ID
531 ) AND m.process_flag = 'N'
532 AND m.request_id = req_id
533 AND m.set_number = set_num;
534 l_last_fetch BOOLEAN := FALSE;
535 l_count NUMBER;
536 BEGIN
537 IF process_mode='LOCK' THEN
538 NULL;
539 ELSE
540 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
541 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_claims',FALSE);
542 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
543 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
544
545 open merged_records;
546 LOOP
547 FETCH merged_records BULK COLLECT INTO
548 MERGE_HEADER_ID_LIST
549 , PRIMARY_KEY_ID1_LIST
550 , NUM_COL1_ORIG_LIST
551 , NUM_COL2_ORIG_LIST
552 , NUM_COL3_ORIG_LIST
553 , NUM_COL4_ORIG_LIST
554 , NUM_COL5_ORIG_LIST
555 , NUM_COL6_ORIG_LIST
556 ;
557 IF merged_records%NOTFOUND THEN
558 l_last_fetch := TRUE;
559 END IF;
560 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
561 exit;
562 END IF;
563 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
564 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
565 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
566 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
567 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL4_ORIG_LIST(I));
568 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL5_ORIG_LIST(I));
569 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL6_ORIG_LIST(I));
570 END LOOP;
571 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
572 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
573 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
574 MERGE_LOG_ID,
575 TABLE_NAME,
576 MERGE_HEADER_ID,
577 PRIMARY_KEY_ID1,
578 NUM_COL1_ORIG,
579 NUM_COL1_NEW,
580 NUM_COL2_ORIG,
581 NUM_COL2_NEW,
582 NUM_COL3_ORIG,
583 NUM_COL3_NEW,
584 NUM_COL4_ORIG,
585 NUM_COL4_NEW,
586 NUM_COL5_ORIG,
587 NUM_COL5_NEW,
588 NUM_COL6_ORIG,
589 NUM_COL6_NEW,
590 ACTION_FLAG,
591 REQUEST_ID,
592 CREATED_BY,
593 CREATION_DATE,
594 LAST_UPDATE_LOGIN,
595 LAST_UPDATE_DATE,
596 LAST_UPDATED_BY
597 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
598 'ozf_claims',
599 MERGE_HEADER_ID_LIST(I),
600 PRIMARY_KEY_ID1_LIST(I),
601 NUM_COL1_ORIG_LIST(I),
602 NUM_COL1_NEW_LIST(I),
603 NUM_COL2_ORIG_LIST(I),
604 NUM_COL2_NEW_LIST(I),
605 NUM_COL3_ORIG_LIST(I),
606 NUM_COL3_NEW_LIST(I),
607 NUM_COL4_ORIG_LIST(I),
608 NUM_COL4_NEW_LIST(I),
609 NUM_COL5_ORIG_LIST(I),
610 NUM_COL5_NEW_LIST(I),
611 NUM_COL6_ORIG_LIST(I),
612 NUM_COL6_NEW_LIST(I),
613 'U',
614 req_id,
615 hz_utility_pub.CREATED_BY,
616 hz_utility_pub.CREATION_DATE,
617 hz_utility_pub.LAST_UPDATE_LOGIN,
618 hz_utility_pub.LAST_UPDATE_DATE,
619 hz_utility_pub.LAST_UPDATED_BY
620 );
621
622 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
623 UPDATE ozf_claims yt SET
624 cust_account_id=NUM_COL1_NEW_LIST(I)
625 ,ship_to_cust_account_id=NUM_COL2_NEW_LIST(I)
626 ,cust_billto_acct_site_id=NUM_COL3_NEW_LIST(I)
627 ,cust_shipto_acct_site_id=NUM_COL4_NEW_LIST(I)
628 ,related_cust_account_id=NUM_COL5_NEW_LIST(I)
629 ,related_site_use_id=NUM_COL6_NEW_LIST(I)
630 , LAST_UPDATE_DATE=SYSDATE
631 , last_updated_by=arp_standard.profile.user_id
632 , last_update_login=arp_standard.profile.last_update_login
633 , REQUEST_ID=req_id
634 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
635 , PROGRAM_ID=arp_standard.profile.program_id
636 , PROGRAM_UPDATE_DATE=SYSDATE
637 WHERE claim_id=PRIMARY_KEY_ID1_LIST(I)
638 ;
639 l_count := l_count + SQL%ROWCOUNT;
640 IF l_last_fetch THEN
641 EXIT;
642 END IF;
643 END LOOP;
644
645 arp_message.set_name('AR','AR_ROWS_UPDATED');
646 arp_message.set_token('NUM_ROWS',to_char(l_count));
647 END IF;
648 EXCEPTION
649 WHEN OTHERS THEN
650 arp_message.set_line( 'merge_claims');
651 RAISE;
652 END merge_claims;
653
654
655 /*-------------------------------------------------------------
656 |
657 | PROCEDURE
658 | merge_claims_history
659 | DESCRIPTION :
660 | Account merge procedure for the table, ozf_claims_history
661 |
662 | NOTES:
663 |
664 |--------------------------------------------------------------*/
665
666 PROCEDURE merge_claims_history (
667 req_id NUMBER,
668 set_num NUMBER,
669 process_mode VARCHAR2) IS
670
671 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
672 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
673 INDEX BY BINARY_INTEGER;
674 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
675
676 TYPE claim_history_id_LIST_TYPE IS TABLE OF
677 ozf_claims_history.claim_history_id%TYPE
678 INDEX BY BINARY_INTEGER;
679 PRIMARY_KEY_ID1_LIST claim_history_id_LIST_TYPE;
680
681 TYPE cust_account_id_LIST_TYPE IS TABLE OF
682 ozf_claims_history.cust_account_id%TYPE
683 INDEX BY BINARY_INTEGER;
684 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
685 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
686
687 TYPE ship_to_cust_acct_id_LIST_TYPE IS TABLE OF
688 ozf_claims_history.ship_to_cust_account_id%TYPE
689 INDEX BY BINARY_INTEGER;
690 NUM_COL2_ORIG_LIST ship_to_cust_acct_id_LIST_TYPE;
691 NUM_COL2_NEW_LIST ship_to_cust_acct_id_LIST_TYPE;
692
693 TYPE cb_acct_site_id_LIST_TYPE IS TABLE OF
694 ozf_claims_history.cust_billto_acct_site_id%TYPE
695 INDEX BY BINARY_INTEGER;
696 NUM_COL3_ORIG_LIST cb_acct_site_id_LIST_TYPE;
697 NUM_COL3_NEW_LIST cb_acct_site_id_LIST_TYPE;
698
699 TYPE cs_acct_site_id_LIST_TYPE IS TABLE OF
700 ozf_claims_history.cust_shipto_acct_site_id%TYPE
701 INDEX BY BINARY_INTEGER;
702 NUM_COL4_ORIG_LIST cs_acct_site_id_LIST_TYPE;
703 NUM_COL4_NEW_LIST cs_acct_site_id_LIST_TYPE;
704
705 TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
706 ozf_claims_history.related_cust_account_id%TYPE
707 INDEX BY BINARY_INTEGER;
708 NUM_COL5_ORIG_LIST rel_cust_account_id_LIST_TYPE;
709 NUM_COL5_NEW_LIST rel_cust_account_id_LIST_TYPE;
710
711 TYPE related_site_use_id_LIST_TYPE IS TABLE OF
712 ozf_claims_history.related_site_use_id%TYPE
713 INDEX BY BINARY_INTEGER;
714 NUM_COL6_ORIG_LIST related_site_use_id_LIST_TYPE;
715 NUM_COL6_NEW_LIST related_site_use_id_LIST_TYPE;
716
717 l_profile_val VARCHAR2(30);
718 CURSOR merged_records IS
719 SELECT distinct CUSTOMER_MERGE_HEADER_ID
720 ,claim_history_id
724 ,cust_shipto_acct_site_id
721 ,cust_account_id
722 ,ship_to_cust_account_id
723 ,cust_billto_acct_site_id
725 ,related_cust_account_id
726 ,related_site_use_id
727 FROM ozf_claims_history yt, ra_customer_merges m
728 WHERE (
729 yt.cust_account_id = m.DUPLICATE_ID
730 OR yt.ship_to_cust_account_id = m.DUPLICATE_ID
731 OR yt.cust_billto_acct_site_id = m.DUPLICATE_SITE_ID
732 OR yt.cust_shipto_acct_site_id = m.DUPLICATE_SITE_ID
733 OR yt.related_cust_account_id = m.DUPLICATE_ID
734 OR yt.related_site_use_id = m.DUPLICATE_SITE_ID
735 ) AND m.process_flag = 'N'
736 AND m.request_id = req_id
737 AND m.set_number = set_num;
738 l_last_fetch BOOLEAN := FALSE;
739 l_count NUMBER;
740 BEGIN
741 IF process_mode='LOCK' THEN
742 NULL;
743 ELSE
744 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
745 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_claims_history',FALSE);
746 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
747 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
748
749 open merged_records;
750 LOOP
751 FETCH merged_records BULK COLLECT INTO
752 MERGE_HEADER_ID_LIST
753 , PRIMARY_KEY_ID1_LIST
754 , NUM_COL1_ORIG_LIST
755 , NUM_COL2_ORIG_LIST
756 , NUM_COL3_ORIG_LIST
757 , NUM_COL4_ORIG_LIST
758 , NUM_COL5_ORIG_LIST
759 , NUM_COL6_ORIG_LIST
760 ;
761 IF merged_records%NOTFOUND THEN
762 l_last_fetch := TRUE;
763 END IF;
764 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
765 exit;
766 END IF;
767 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
768 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
769 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
770 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
771 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL4_ORIG_LIST(I));
772 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL5_ORIG_LIST(I));
773 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL6_ORIG_LIST(I));
774 END LOOP;
775 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
776 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
777 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
778 MERGE_LOG_ID,
779 TABLE_NAME,
780 MERGE_HEADER_ID,
781 PRIMARY_KEY_ID1,
782 NUM_COL1_ORIG,
783 NUM_COL1_NEW,
784 NUM_COL2_ORIG,
785 NUM_COL2_NEW,
786 NUM_COL3_ORIG,
787 NUM_COL3_NEW,
788 NUM_COL4_ORIG,
789 NUM_COL4_NEW,
790 NUM_COL5_ORIG,
791 NUM_COL5_NEW,
792 NUM_COL6_ORIG,
793 NUM_COL6_NEW,
794 ACTION_FLAG,
795 REQUEST_ID,
796 CREATED_BY,
797 CREATION_DATE,
798 LAST_UPDATE_LOGIN,
799 LAST_UPDATE_DATE,
800 LAST_UPDATED_BY
801 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
802 'ozf_claims_history',
803 MERGE_HEADER_ID_LIST(I),
804 PRIMARY_KEY_ID1_LIST(I),
805 NUM_COL1_ORIG_LIST(I),
806 NUM_COL1_NEW_LIST(I),
807 NUM_COL2_ORIG_LIST(I),
808 NUM_COL2_NEW_LIST(I),
809 NUM_COL3_ORIG_LIST(I),
810 NUM_COL3_NEW_LIST(I),
811 NUM_COL4_ORIG_LIST(I),
812 NUM_COL4_NEW_LIST(I),
813 NUM_COL5_ORIG_LIST(I),
814 NUM_COL5_NEW_LIST(I),
815 NUM_COL6_ORIG_LIST(I),
816 NUM_COL6_NEW_LIST(I),
817 'U',
818 req_id,
819 hz_utility_pub.CREATED_BY,
820 hz_utility_pub.CREATION_DATE,
821 hz_utility_pub.LAST_UPDATE_LOGIN,
822 hz_utility_pub.LAST_UPDATE_DATE,
823 hz_utility_pub.LAST_UPDATED_BY
824 );
825
826 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
827 UPDATE ozf_claims_history yt SET
828 cust_account_id=NUM_COL1_NEW_LIST(I)
829 ,ship_to_cust_account_id=NUM_COL2_NEW_LIST(I)
830 ,cust_billto_acct_site_id=NUM_COL3_NEW_LIST(I)
831 ,cust_shipto_acct_site_id=NUM_COL4_NEW_LIST(I)
832 ,related_cust_account_id=NUM_COL5_NEW_LIST(I)
833 ,related_site_use_id=NUM_COL6_NEW_LIST(I)
834 , LAST_UPDATE_DATE=SYSDATE
835 , last_updated_by=arp_standard.profile.user_id
836 , last_update_login=arp_standard.profile.last_update_login
837 , REQUEST_ID=req_id
838 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
839 , PROGRAM_ID=arp_standard.profile.program_id
840 , PROGRAM_UPDATE_DATE=SYSDATE
841 WHERE claim_history_id=PRIMARY_KEY_ID1_LIST(I)
842 ;
843 l_count := l_count + SQL%ROWCOUNT;
844 IF l_last_fetch THEN
845 EXIT;
846 END IF;
847 END LOOP;
848
849 arp_message.set_name('AR','AR_ROWS_UPDATED');
850 arp_message.set_token('NUM_ROWS',to_char(l_count));
851 END IF;
852 EXCEPTION
853 WHEN OTHERS THEN
854 arp_message.set_line( 'merge_claims_history');
855 RAISE;
856 END merge_claims_history;
857
861 | PROCEDURE
858
859 /*-------------------------------------------------------------
860 |
862 | merge_code_conversions
863 | DESCRIPTION :
864 | Account merge procedure for the table, ozf_code_conversions
865 |
866 | NOTES:
867 |
868 |--------------------------------------------------------------*/
869
870 PROCEDURE merge_code_conversions (
871 req_id NUMBER,
872 set_num NUMBER,
873 process_mode VARCHAR2) IS
874
875 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
876 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
877 INDEX BY BINARY_INTEGER;
878 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
879
880 TYPE code_conversion_id_LIST_TYPE IS TABLE OF
881 ozf_code_conversions.code_conversion_id%TYPE
882 INDEX BY BINARY_INTEGER;
883 PRIMARY_KEY_ID1_LIST code_conversion_id_LIST_TYPE;
884
885 TYPE cust_account_id_LIST_TYPE IS TABLE OF
886 ozf_code_conversions.cust_account_id%TYPE
887 INDEX BY BINARY_INTEGER;
888 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
889 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
890
891 l_profile_val VARCHAR2(30);
892 CURSOR merged_records IS
893 SELECT distinct CUSTOMER_MERGE_HEADER_ID
894 ,code_conversion_id
895 ,cust_account_id
896 FROM ozf_code_conversions yt, ra_customer_merges m
897 WHERE (
898 yt.cust_account_id = m.DUPLICATE_ID
899 ) AND m.process_flag = 'N'
900 AND m.request_id = req_id
901 AND m.set_number = set_num;
902 l_last_fetch BOOLEAN := FALSE;
903 l_count NUMBER;
904 BEGIN
905 IF process_mode='LOCK' THEN
906 NULL;
907 ELSE
908 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
909 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_code_conversions',FALSE);
910 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
911 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
912
913 open merged_records;
914 LOOP
915 FETCH merged_records BULK COLLECT INTO
916 MERGE_HEADER_ID_LIST
917 , PRIMARY_KEY_ID1_LIST
918 , NUM_COL1_ORIG_LIST
919 ;
920 IF merged_records%NOTFOUND THEN
921 l_last_fetch := TRUE;
922 END IF;
923 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
924 exit;
925 END IF;
926 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
927 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
928 END LOOP;
929 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
930 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
931 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
932 MERGE_LOG_ID,
933 TABLE_NAME,
934 MERGE_HEADER_ID,
935 PRIMARY_KEY_ID1,
936 NUM_COL1_ORIG,
937 NUM_COL1_NEW,
938 ACTION_FLAG,
939 REQUEST_ID,
940 CREATED_BY,
941 CREATION_DATE,
942 LAST_UPDATE_LOGIN,
943 LAST_UPDATE_DATE,
944 LAST_UPDATED_BY
945 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
946 'ozf_code_conversions',
947 MERGE_HEADER_ID_LIST(I),
948 PRIMARY_KEY_ID1_LIST(I),
949 NUM_COL1_ORIG_LIST(I),
950 NUM_COL1_NEW_LIST(I),
951 'U',
952 req_id,
953 hz_utility_pub.CREATED_BY,
954 hz_utility_pub.CREATION_DATE,
955 hz_utility_pub.LAST_UPDATE_LOGIN,
956 hz_utility_pub.LAST_UPDATE_DATE,
957 hz_utility_pub.LAST_UPDATED_BY
958 );
959
960 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
961 UPDATE ozf_code_conversions yt SET
962 cust_account_id=NUM_COL1_NEW_LIST(I)
963 , LAST_UPDATE_DATE=SYSDATE
964 , last_updated_by=arp_standard.profile.user_id
965 , last_update_login=arp_standard.profile.last_update_login
966 WHERE code_conversion_id=PRIMARY_KEY_ID1_LIST(I)
967 ;
968 l_count := l_count + SQL%ROWCOUNT;
969 IF l_last_fetch THEN
970 EXIT;
971 END IF;
972 END LOOP;
973
974 arp_message.set_name('AR','AR_ROWS_UPDATED');
975 arp_message.set_token('NUM_ROWS',to_char(l_count));
976 END IF;
977 EXCEPTION
978 WHEN OTHERS THEN
979 arp_message.set_line( 'merge_code_conversions');
980 RAISE;
981 END merge_code_conversions;
982
983 /*-------------------------------------------------------------
984 |
985 | PROCEDURE
986 | merge_cust_daily_facts
987 | DESCRIPTION :
988 | Account merge procedure for the table, ozf_cust_daily_facts
989 |
990 | NOTES:
991 |
992 |--------------------------------------------------------------*/
993
994 PROCEDURE merge_cust_daily_facts (
995 req_id NUMBER,
996 set_num NUMBER,
997 process_mode VARCHAR2) IS
998
999 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1000 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1001 INDEX BY BINARY_INTEGER;
1002 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1003
1004 TYPE cust_daily_fact_id_LIST_TYPE IS TABLE OF
1005 ozf_cust_daily_facts.cust_daily_fact_id%TYPE
1006 INDEX BY BINARY_INTEGER;
1007 PRIMARY_KEY_ID1_LIST cust_daily_fact_id_LIST_TYPE;
1008
1009 TYPE cust_account_id_LIST_TYPE IS TABLE OF
1010 ozf_cust_daily_facts.cust_account_id%TYPE
1011 INDEX BY BINARY_INTEGER;
1012 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
1013 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
1014
1015 TYPE bill_to_site_use_id_LIST_TYPE IS TABLE OF
1016 ozf_cust_daily_facts.bill_to_site_use_id%TYPE
1017 INDEX BY BINARY_INTEGER;
1018 NUM_COL2_ORIG_LIST bill_to_site_use_id_LIST_TYPE;
1019 NUM_COL2_NEW_LIST bill_to_site_use_id_LIST_TYPE;
1020
1021 TYPE ship_to_site_use_id_LIST_TYPE IS TABLE OF
1022 ozf_cust_daily_facts.ship_to_site_use_id%TYPE
1023 INDEX BY BINARY_INTEGER;
1024 NUM_COL3_ORIG_LIST ship_to_site_use_id_LIST_TYPE;
1025 NUM_COL3_NEW_LIST ship_to_site_use_id_LIST_TYPE;
1026
1027 l_profile_val VARCHAR2(30);
1028 CURSOR merged_records IS
1029 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1030 ,cust_daily_fact_id
1031 ,cust_account_id
1032 ,bill_to_site_use_id
1033 ,ship_to_site_use_id
1034 FROM ozf_cust_daily_facts yt, ra_customer_merges m
1035 WHERE (
1036 yt.cust_account_id = m.DUPLICATE_ADDRESS_ID
1037 OR yt.bill_to_site_use_id = m.DUPLICATE_SITE_ID
1038 OR yt.ship_to_site_use_id = m.DUPLICATE_SITE_ID
1039 ) AND m.process_flag = 'N'
1040 AND m.request_id = req_id
1041 AND m.set_number = set_num;
1042 l_last_fetch BOOLEAN := FALSE;
1043 l_count NUMBER;
1044 BEGIN
1045 IF process_mode='LOCK' THEN
1046 NULL;
1047 ELSE
1048 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1049 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_cust_daily_facts',FALSE);
1050 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1051 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1052
1053 open merged_records;
1054 LOOP
1055 FETCH merged_records BULK COLLECT INTO
1056 MERGE_HEADER_ID_LIST
1057 , PRIMARY_KEY_ID1_LIST
1058 , NUM_COL1_ORIG_LIST
1059 , NUM_COL2_ORIG_LIST
1060 , NUM_COL3_ORIG_LIST
1061 ;
1062 IF merged_records%NOTFOUND THEN
1063 l_last_fetch := TRUE;
1064 END IF;
1065 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1066 exit;
1067 END IF;
1068 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1069 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
1070
1071 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1072 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1073 END LOOP;
1074 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1075 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1076 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1077 MERGE_LOG_ID,
1078 TABLE_NAME,
1079 MERGE_HEADER_ID,
1080 PRIMARY_KEY_ID1,
1081 NUM_COL1_ORIG,
1082 NUM_COL1_NEW,
1083 NUM_COL2_ORIG,
1084 NUM_COL2_NEW,
1085 NUM_COL3_ORIG,
1086 NUM_COL3_NEW,
1087 ACTION_FLAG,
1088 REQUEST_ID,
1089 CREATED_BY,
1090 CREATION_DATE,
1091 LAST_UPDATE_LOGIN,
1092 LAST_UPDATE_DATE,
1093 LAST_UPDATED_BY
1094 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1095 'ozf_cust_daily_facts',
1096 MERGE_HEADER_ID_LIST(I),
1097 PRIMARY_KEY_ID1_LIST(I),
1098 NUM_COL1_ORIG_LIST(I),
1099 NUM_COL1_NEW_LIST(I),
1100 NUM_COL2_ORIG_LIST(I),
1101 NUM_COL2_NEW_LIST(I),
1102 NUM_COL3_ORIG_LIST(I),
1103 NUM_COL3_NEW_LIST(I),
1104 'U',
1105 req_id,
1106 hz_utility_pub.CREATED_BY,
1107 hz_utility_pub.CREATION_DATE,
1108 hz_utility_pub.LAST_UPDATE_LOGIN,
1109 hz_utility_pub.LAST_UPDATE_DATE,
1110 hz_utility_pub.LAST_UPDATED_BY
1111 );
1112
1113 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1114 UPDATE ozf_cust_daily_facts yt SET
1115 cust_account_id=NUM_COL1_NEW_LIST(I)
1116 ,bill_to_site_use_id=NUM_COL2_NEW_LIST(I)
1117 ,ship_to_site_use_id=NUM_COL3_NEW_LIST(I)
1118 , LAST_UPDATE_DATE=SYSDATE
1119 , last_updated_by=arp_standard.profile.user_id
1120 , last_update_login=arp_standard.profile.last_update_login
1121 WHERE cust_daily_fact_id=PRIMARY_KEY_ID1_LIST(I)
1122 ;
1123 l_count := l_count + SQL%ROWCOUNT;
1124 IF l_last_fetch THEN
1125 EXIT;
1126 END IF;
1127 END LOOP;
1128
1129 arp_message.set_name('AR','AR_ROWS_UPDATED');
1130 arp_message.set_token('NUM_ROWS',to_char(l_count));
1131 END IF;
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 arp_message.set_line( 'merge_cust_daily_facts');
1135 RAISE;
1136 END merge_cust_daily_facts;
1137
1138 /*-------------------------------------------------------------
1139 |
1140 | PROCEDURE
1141 | merge_fund_utilization
1142 | DESCRIPTION :
1143 | Account merge procedure for the table, ozf_funds_utilized_all_b
1144 |
1145 | NOTES:
1146 |
1147 |--------------------------------------------------------------*/
1148
1149 PROCEDURE merge_fund_utilization (
1150 req_id NUMBER,
1151 set_num NUMBER,
1152 process_mode VARCHAR2) IS
1153
1154 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1155 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1156 INDEX BY BINARY_INTEGER;
1157 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1158
1159 TYPE utilization_id_LIST_TYPE IS TABLE OF
1160 ozf_funds_utilized_all_b.utilization_id%TYPE
1161 INDEX BY BINARY_INTEGER;
1162 PRIMARY_KEY_ID1_LIST utilization_id_LIST_TYPE;
1163
1164 TYPE cust_account_id_LIST_TYPE IS TABLE OF
1165 ozf_funds_utilized_all_b.cust_account_id%TYPE
1166 INDEX BY BINARY_INTEGER;
1167 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
1168 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
1169
1170 TYPE bc_account_id_LIST_TYPE IS TABLE OF
1171 ozf_funds_utilized_all_b.billto_cust_account_id%TYPE
1172 INDEX BY BINARY_INTEGER;
1173 NUM_COL2_ORIG_LIST bc_account_id_LIST_TYPE;
1174 NUM_COL2_NEW_LIST bc_account_id_LIST_TYPE;
1175
1176 l_profile_val VARCHAR2(30);
1177 CURSOR merged_records IS
1178 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1179 ,utilization_id
1180 ,cust_account_id
1181 ,billto_cust_account_id
1182 FROM ozf_funds_utilized_all_b yt, ra_customer_merges m
1183 WHERE (
1184 yt.cust_account_id = m.DUPLICATE_ID
1185 OR yt.billto_cust_account_id = m.DUPLICATE_ID
1186 ) AND m.process_flag = 'N'
1187 AND m.request_id = req_id
1188 AND m.set_number = set_num;
1189 l_last_fetch BOOLEAN := FALSE;
1190 l_count NUMBER;
1191 BEGIN
1192 IF process_mode='LOCK' THEN
1193 NULL;
1194 ELSE
1195 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1196 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_funds_utilized_all_b',FALSE);
1197 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1198 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1199
1200 open merged_records;
1201 LOOP
1202 FETCH merged_records BULK COLLECT INTO
1203 MERGE_HEADER_ID_LIST
1204 , PRIMARY_KEY_ID1_LIST
1205 , NUM_COL1_ORIG_LIST
1206 , NUM_COL2_ORIG_LIST
1207 ;
1208 IF merged_records%NOTFOUND THEN
1209 l_last_fetch := TRUE;
1210 END IF;
1211 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1212 exit;
1213 END IF;
1214 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1215 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1216 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
1217 END LOOP;
1218 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1219 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1220 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1221 MERGE_LOG_ID,
1222 TABLE_NAME,
1223 MERGE_HEADER_ID,
1224 PRIMARY_KEY_ID1,
1225 NUM_COL1_ORIG,
1226 NUM_COL1_NEW,
1227 NUM_COL2_ORIG,
1228 NUM_COL2_NEW,
1229 ACTION_FLAG,
1230 REQUEST_ID,
1231 CREATED_BY,
1232 CREATION_DATE,
1233 LAST_UPDATE_LOGIN,
1234 LAST_UPDATE_DATE,
1235 LAST_UPDATED_BY
1236 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1237 'ozf_funds_utilized_all_b',
1238 MERGE_HEADER_ID_LIST(I),
1239 PRIMARY_KEY_ID1_LIST(I),
1240 NUM_COL1_ORIG_LIST(I),
1241 NUM_COL1_NEW_LIST(I),
1242 NUM_COL2_ORIG_LIST(I),
1243 NUM_COL2_NEW_LIST(I),
1244 'U',
1245 req_id,
1246 hz_utility_pub.CREATED_BY,
1247 hz_utility_pub.CREATION_DATE,
1248 hz_utility_pub.LAST_UPDATE_LOGIN,
1249 hz_utility_pub.LAST_UPDATE_DATE,
1250 hz_utility_pub.LAST_UPDATED_BY
1251 );
1252
1253 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1254 UPDATE ozf_funds_utilized_all_b yt SET
1255 cust_account_id=NUM_COL1_NEW_LIST(I)
1256 ,billto_cust_account_id=NUM_COL2_NEW_LIST(I)
1257 , LAST_UPDATE_DATE=SYSDATE
1258 , last_updated_by=arp_standard.profile.user_id
1259 , last_update_login=arp_standard.profile.last_update_login
1260 , REQUEST_ID=req_id
1261 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
1262 , PROGRAM_ID=arp_standard.profile.program_id
1263 , PROGRAM_UPDATE_DATE=SYSDATE
1264 WHERE utilization_id=PRIMARY_KEY_ID1_LIST(I)
1265 ;
1266 l_count := l_count + SQL%ROWCOUNT;
1267 IF l_last_fetch THEN
1268 EXIT;
1269 END IF;
1270 END LOOP;
1271
1272 arp_message.set_name('AR','AR_ROWS_UPDATED');
1273 arp_message.set_token('NUM_ROWS',to_char(l_count));
1274 END IF;
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 arp_message.set_line( 'merge_fund_utilization');
1278 RAISE;
1279 END merge_fund_utilization;
1280
1281
1282 /*-------------------------------------------------------------
1283 |
1284 | PROCEDURE
1285 | merge_offer_denorm
1286 | DESCRIPTION :
1287 | Account merge procedure for the table, ozf_activity_customers
1288 |
1289 | NOTES:
1290 |
1291 |--------------------------------------------------------------*/
1292
1293 PROCEDURE merge_offer_denorm (
1294 req_id NUMBER,
1295 set_num NUMBER,
1296 process_mode VARCHAR2) IS
1297
1298 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1299 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1300 INDEX BY BINARY_INTEGER;
1301 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1302
1303 TYPE object_class_LIST_TYPE IS TABLE OF
1304 ozf_activity_customers.object_class%TYPE
1305 INDEX BY BINARY_INTEGER;
1306 PRIMARY_KEY1_LIST object_class_LIST_TYPE;
1307
1308 TYPE object_id_LIST_TYPE IS TABLE OF
1309 ozf_activity_customers.object_id%TYPE
1310 INDEX BY BINARY_INTEGER;
1311 PRIMARY_KEY2_LIST object_id_LIST_TYPE;
1312
1313 TYPE cust_account_id_LIST_TYPE IS TABLE OF
1314 ozf_activity_customers.cust_account_id%TYPE
1315 INDEX BY BINARY_INTEGER;
1316 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
1317 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
1318
1319 TYPE site_use_id_LIST_TYPE IS TABLE OF
1320 ozf_activity_customers.site_use_id%TYPE
1321 INDEX BY BINARY_INTEGER;
1322 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
1323 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
1324
1325 l_profile_val VARCHAR2(30);
1326 CURSOR merged_records IS
1327 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1328 ,object_class
1329 ,object_id
1330 ,cust_account_id
1331 ,site_use_id
1332 FROM ozf_activity_customers yt, ra_customer_merges m
1333 WHERE (
1334 yt.cust_account_id = m.DUPLICATE_ADDRESS_ID
1335 OR yt.site_use_id = m.DUPLICATE_SITE_ID
1336 ) AND m.process_flag = 'N'
1337 AND m.request_id = req_id
1338 AND m.set_number = set_num;
1339 l_last_fetch BOOLEAN := FALSE;
1340 l_count NUMBER;
1341 BEGIN
1342 IF process_mode='LOCK' THEN
1343 NULL;
1344 ELSE
1345 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1346 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_activity_customers',FALSE);
1347 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1348 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1349
1350 open merged_records;
1351 LOOP
1352 FETCH merged_records BULK COLLECT INTO
1353 MERGE_HEADER_ID_LIST
1354 , PRIMARY_KEY1_LIST
1355 , PRIMARY_KEY2_LIST
1356 , NUM_COL1_ORIG_LIST
1357 , NUM_COL2_ORIG_LIST
1358 ;
1359 IF merged_records%NOTFOUND THEN
1360 l_last_fetch := TRUE;
1361 END IF;
1362 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1363 exit;
1364 END IF;
1365 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1366 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
1367
1368 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1369 END LOOP;
1370 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1371 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1372 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1373 MERGE_LOG_ID,
1374 TABLE_NAME,
1375 MERGE_HEADER_ID,
1376 PRIMARY_KEY1,
1377 PRIMARY_KEY2,
1378 NUM_COL1_ORIG,
1379 NUM_COL1_NEW,
1380 NUM_COL2_ORIG,
1381 NUM_COL2_NEW,
1382 ACTION_FLAG,
1383 REQUEST_ID,
1384 CREATED_BY,
1385 CREATION_DATE,
1386 LAST_UPDATE_LOGIN,
1387 LAST_UPDATE_DATE,
1388 LAST_UPDATED_BY
1389 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1390 'ozf_activity_customers',
1391 MERGE_HEADER_ID_LIST(I),
1392 PRIMARY_KEY1_LIST(I),
1393 PRIMARY_KEY2_LIST(I),
1394 NUM_COL1_ORIG_LIST(I),
1395 NUM_COL1_NEW_LIST(I),
1396 NUM_COL2_ORIG_LIST(I),
1397 NUM_COL2_NEW_LIST(I),
1398 'U',
1399 req_id,
1400 hz_utility_pub.CREATED_BY,
1401 hz_utility_pub.CREATION_DATE,
1402 hz_utility_pub.LAST_UPDATE_LOGIN,
1403 hz_utility_pub.LAST_UPDATE_DATE,
1404 hz_utility_pub.LAST_UPDATED_BY
1405 );
1406
1407 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1408 UPDATE ozf_activity_customers yt SET
1409 cust_account_id=NUM_COL1_NEW_LIST(I)
1410 ,site_use_id=NUM_COL2_NEW_LIST(I)
1411 , LAST_UPDATE_DATE=SYSDATE
1412 , last_updated_by=arp_standard.profile.user_id
1413 , last_update_login=arp_standard.profile.last_update_login
1414 WHERE object_class=PRIMARY_KEY1_LIST(I)
1415 AND object_id=PRIMARY_KEY2_LIST(I)
1416 ;
1417 l_count := l_count + SQL%ROWCOUNT;
1418 IF l_last_fetch THEN
1419 EXIT;
1420 END IF;
1421 END LOOP;
1422
1423 arp_message.set_name('AR','AR_ROWS_UPDATED');
1424 arp_message.set_token('NUM_ROWS',to_char(l_count));
1425 END IF;
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 arp_message.set_line( 'merge_offer_denorm');
1429 RAISE;
1430 END merge_offer_denorm;
1431
1432
1436 | merge_offer_header
1433 /*-------------------------------------------------------------
1434 |
1435 | PROCEDURE
1437 | DESCRIPTION :
1438 | Account merge procedure for the table, ozf_offers
1439 |
1440 | NOTES:
1441 |
1442 |--------------------------------------------------------------*/
1443
1444 PROCEDURE merge_offer_header (
1445 req_id NUMBER,
1446 set_num NUMBER,
1447 process_mode VARCHAR2) IS
1448
1449 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1450 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1451 INDEX BY BINARY_INTEGER;
1452 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1453
1454 TYPE qp_list_header_id_LIST_TYPE IS TABLE OF
1455 ozf_offers.qp_list_header_id%TYPE
1456 INDEX BY BINARY_INTEGER;
1457 PRIMARY_KEY_ID1_LIST qp_list_header_id_LIST_TYPE;
1458
1459 TYPE ben_account_id_LIST_TYPE IS TABLE OF
1460 ozf_offers.beneficiary_account_id%TYPE
1461 INDEX BY BINARY_INTEGER;
1462 NUM_COL1_ORIG_LIST ben_account_id_LIST_TYPE;
1463 NUM_COL1_NEW_LIST ben_account_id_LIST_TYPE;
1464
1465 l_profile_val VARCHAR2(30);
1466 CURSOR merged_records IS
1467 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1468 ,qp_list_header_id
1469 ,beneficiary_account_id
1470 FROM ozf_offers yt, ra_customer_merges m
1471 WHERE (
1472 yt.beneficiary_account_id = m.DUPLICATE_ADDRESS_ID
1473 ) AND m.process_flag = 'N'
1474 AND m.request_id = req_id
1475 AND m.set_number = set_num;
1476 l_last_fetch BOOLEAN := FALSE;
1477 l_count NUMBER;
1478 BEGIN
1479 IF process_mode='LOCK' THEN
1480 NULL;
1481 ELSE
1482 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1483 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_offers',FALSE);
1484 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1485 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1486
1487 open merged_records;
1488 LOOP
1489 FETCH merged_records BULK COLLECT INTO
1490 MERGE_HEADER_ID_LIST
1491 , PRIMARY_KEY_ID1_LIST
1492 , NUM_COL1_ORIG_LIST
1493 ;
1494 IF merged_records%NOTFOUND THEN
1495 l_last_fetch := TRUE;
1496 END IF;
1497 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1498 exit;
1499 END IF;
1500 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1501 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
1502
1503 END LOOP;
1504 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1505 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1506 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1507 MERGE_LOG_ID,
1508 TABLE_NAME,
1509 MERGE_HEADER_ID,
1510 PRIMARY_KEY_ID1,
1511 NUM_COL1_ORIG,
1512 NUM_COL1_NEW,
1513 ACTION_FLAG,
1514 REQUEST_ID,
1515 CREATED_BY,
1516 CREATION_DATE,
1517 LAST_UPDATE_LOGIN,
1518 LAST_UPDATE_DATE,
1519 LAST_UPDATED_BY
1520 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1521 'ozf_offers',
1522 MERGE_HEADER_ID_LIST(I),
1523 PRIMARY_KEY_ID1_LIST(I),
1524 NUM_COL1_ORIG_LIST(I),
1525 NUM_COL1_NEW_LIST(I),
1526 'U',
1527 req_id,
1528 hz_utility_pub.CREATED_BY,
1529 hz_utility_pub.CREATION_DATE,
1530 hz_utility_pub.LAST_UPDATE_LOGIN,
1531 hz_utility_pub.LAST_UPDATE_DATE,
1532 hz_utility_pub.LAST_UPDATED_BY
1533 );
1534
1535 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1536 UPDATE ozf_offers yt SET
1537 beneficiary_account_id=NUM_COL1_NEW_LIST(I)
1538 , LAST_UPDATE_DATE=SYSDATE
1539 , last_updated_by=arp_standard.profile.user_id
1540 , last_update_login=arp_standard.profile.last_update_login
1541 WHERE qp_list_header_id=PRIMARY_KEY_ID1_LIST(I)
1542 ;
1543 l_count := l_count + SQL%ROWCOUNT;
1544 IF l_last_fetch THEN
1545 EXIT;
1546 END IF;
1547 END LOOP;
1548
1549 arp_message.set_name('AR','AR_ROWS_UPDATED');
1550 arp_message.set_token('NUM_ROWS',to_char(l_count));
1551 END IF;
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 arp_message.set_line( 'merge_offer_header');
1555 RAISE;
1556 END merge_offer_header;
1557
1558
1559 /*-------------------------------------------------------------
1560 |
1561 | PROCEDURE
1562 | merge_request_header
1563 | DESCRIPTION :
1564 | Account merge procedure for the table, ozf_request_headers_all_b
1565 |
1566 | NOTES:
1567 |
1568 |--------------------------------------------------------------*/
1569
1570 PROCEDURE merge_request_header (
1571 req_id NUMBER,
1572 set_num NUMBER,
1573 process_mode VARCHAR2) IS
1574
1575 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1576 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1577 INDEX BY BINARY_INTEGER;
1578 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1579
1580 TYPE request_header_id_LIST_TYPE IS TABLE OF
1581 ozf_request_headers_all_b.request_header_id%TYPE
1582 INDEX BY BINARY_INTEGER;
1583 PRIMARY_KEY_ID1_LIST request_header_id_LIST_TYPE;
1584
1585 TYPE reseller_site_use_id_LIST_TYPE IS TABLE OF
1586 ozf_request_headers_all_b.reseller_site_use_id%TYPE
1587 INDEX BY BINARY_INTEGER;
1588 NUM_COL1_ORIG_LIST reseller_site_use_id_LIST_TYPE;
1589 NUM_COL1_NEW_LIST reseller_site_use_id_LIST_TYPE;
1590
1591 TYPE end_cust_site_use_id_LIST_TYPE IS TABLE OF
1592 ozf_request_headers_all_b.end_cust_site_use_id%TYPE
1593 INDEX BY BINARY_INTEGER;
1594 NUM_COL2_ORIG_LIST end_cust_site_use_id_LIST_TYPE;
1595 NUM_COL2_NEW_LIST end_cust_site_use_id_LIST_TYPE;
1596
1597 TYPE partner_site_use_id_LIST_TYPE IS TABLE OF
1598 ozf_request_headers_all_b.partner_site_use_id%TYPE
1599 INDEX BY BINARY_INTEGER;
1600 NUM_COL3_ORIG_LIST partner_site_use_id_LIST_TYPE;
1601 NUM_COL3_NEW_LIST partner_site_use_id_LIST_TYPE;
1602
1603 l_profile_val VARCHAR2(30);
1604 CURSOR merged_records IS
1605 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1606 ,request_header_id
1607 ,reseller_site_use_id
1608 ,end_cust_site_use_id
1609 ,partner_site_use_id
1610 FROM ozf_request_headers_all_b yt, ra_customer_merges m
1611 WHERE (
1612 yt.reseller_site_use_id = m.DUPLICATE_SITE_ID
1613 OR yt.end_cust_site_use_id = m.DUPLICATE_SITE_ID
1614 OR yt.partner_site_use_id = m.DUPLICATE_SITE_ID
1615 ) AND m.process_flag = 'N'
1616 AND m.request_id = req_id
1617 AND m.set_number = set_num;
1618 l_last_fetch BOOLEAN := FALSE;
1619 l_count NUMBER;
1620 BEGIN
1621 IF process_mode='LOCK' THEN
1622 NULL;
1623 ELSE
1624 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1625 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_request_headers_all_b',FALSE);
1626 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1627 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1628
1629 open merged_records;
1630 LOOP
1631 FETCH merged_records BULK COLLECT INTO
1632 MERGE_HEADER_ID_LIST
1633 , PRIMARY_KEY_ID1_LIST
1634 , NUM_COL1_ORIG_LIST
1635 , NUM_COL2_ORIG_LIST
1636 , NUM_COL3_ORIG_LIST
1637 ;
1638 IF merged_records%NOTFOUND THEN
1639 l_last_fetch := TRUE;
1640 END IF;
1641 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1642 exit;
1643 END IF;
1644 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1645 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST(I));
1646 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1647 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1648 END LOOP;
1649 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1650 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1651 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1652 MERGE_LOG_ID,
1653 TABLE_NAME,
1654 MERGE_HEADER_ID,
1655 PRIMARY_KEY_ID1,
1656 NUM_COL1_ORIG,
1657 NUM_COL1_NEW,
1658 NUM_COL2_ORIG,
1659 NUM_COL2_NEW,
1660 NUM_COL3_ORIG,
1661 NUM_COL3_NEW,
1662 ACTION_FLAG,
1663 REQUEST_ID,
1664 CREATED_BY,
1665 CREATION_DATE,
1666 LAST_UPDATE_LOGIN,
1667 LAST_UPDATE_DATE,
1668 LAST_UPDATED_BY
1669 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1670 'ozf_request_headers_all_b',
1671 MERGE_HEADER_ID_LIST(I),
1672 PRIMARY_KEY_ID1_LIST(I),
1673 NUM_COL1_ORIG_LIST(I),
1674 NUM_COL1_NEW_LIST(I),
1675 NUM_COL2_ORIG_LIST(I),
1676 NUM_COL2_NEW_LIST(I),
1677 NUM_COL3_ORIG_LIST(I),
1678 NUM_COL3_NEW_LIST(I),
1679 'U',
1680 req_id,
1681 hz_utility_pub.CREATED_BY,
1682 hz_utility_pub.CREATION_DATE,
1683 hz_utility_pub.LAST_UPDATE_LOGIN,
1684 hz_utility_pub.LAST_UPDATE_DATE,
1685 hz_utility_pub.LAST_UPDATED_BY
1686 );
1687
1688 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1689 UPDATE ozf_request_headers_all_b yt SET
1690 reseller_site_use_id=NUM_COL1_NEW_LIST(I)
1691 ,end_cust_site_use_id=NUM_COL2_NEW_LIST(I)
1692 ,partner_site_use_id=NUM_COL3_NEW_LIST(I)
1693 , LAST_UPDATE_DATE=SYSDATE
1694 , last_updated_by=arp_standard.profile.user_id
1695 , last_update_login=arp_standard.profile.last_update_login
1696 , REQUEST_ID=req_id
1697 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
1698 , PROGRAM_ID=arp_standard.profile.program_id
1699 , PROGRAM_UPDATE_DATE=SYSDATE
1700 WHERE request_header_id=PRIMARY_KEY_ID1_LIST(I)
1701 ;
1702 l_count := l_count + SQL%ROWCOUNT;
1703 IF l_last_fetch THEN
1704 EXIT;
1705 END IF;
1706 END LOOP;
1707
1708 arp_message.set_name('AR','AR_ROWS_UPDATED');
1709 arp_message.set_token('NUM_ROWS',to_char(l_count));
1710 END IF;
1711 EXCEPTION
1712 WHEN OTHERS THEN
1713 arp_message.set_line( 'merge_request_header');
1714 RAISE;
1715 END merge_request_header;
1716
1717
1718 /*-------------------------------------------------------------
1719 |
1720 | PROCEDURE
1721 | merge_retail_price_points
1722 | DESCRIPTION :
1723 | Account merge procedure for the table, ozf_retail_price_points
1724 |
1725 | NOTES:
1726 |
1727 |--------------------------------------------------------------*/
1728
1729 PROCEDURE merge_retail_price_points (
1730 req_id NUMBER,
1731 set_num NUMBER,
1732 process_mode VARCHAR2) IS
1733
1734 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1735 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1736 INDEX BY BINARY_INTEGER;
1737 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1738
1739 TYPE rp_point_id_LIST_TYPE IS TABLE OF
1740 ozf_retail_price_points.retail_price_point_id%TYPE
1741 INDEX BY BINARY_INTEGER;
1742 PRIMARY_KEY_ID1_LIST rp_point_id_LIST_TYPE;
1743
1744 TYPE cust_account_id_LIST_TYPE IS TABLE OF
1745 ozf_retail_price_points.cust_account_id%TYPE
1746 INDEX BY BINARY_INTEGER;
1747 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
1748 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
1749
1750 TYPE site_use_id_LIST_TYPE IS TABLE OF
1751 ozf_retail_price_points.site_use_id%TYPE
1752 INDEX BY BINARY_INTEGER;
1753 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
1754 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
1755
1756 l_profile_val VARCHAR2(30);
1757 CURSOR merged_records IS
1758 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1759 ,retail_price_point_id
1760 ,cust_account_id
1761 ,site_use_id
1762 FROM ozf_retail_price_points yt, ra_customer_merges m
1763 WHERE (
1764 yt.cust_account_id = m.DUPLICATE_ADDRESS_ID
1765 OR yt.site_use_id = m.DUPLICATE_SITE_ID
1766 ) AND m.process_flag = 'N'
1767 AND m.request_id = req_id
1768 AND m.set_number = set_num;
1769 l_last_fetch BOOLEAN := FALSE;
1770 l_count NUMBER;
1771 BEGIN
1772 IF process_mode='LOCK' THEN
1773 NULL;
1774 ELSE
1775 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1776 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_retail_price_points',FALSE);
1777 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1778 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1779
1780 open merged_records;
1781 LOOP
1782 FETCH merged_records BULK COLLECT INTO
1783 MERGE_HEADER_ID_LIST
1784 , PRIMARY_KEY_ID1_LIST
1785 , NUM_COL1_ORIG_LIST
1786 , NUM_COL2_ORIG_LIST
1787 ;
1788 IF merged_records%NOTFOUND THEN
1789 l_last_fetch := TRUE;
1790 END IF;
1791 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1792 exit;
1793 END IF;
1794 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1795 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST(I));
1796
1797 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1798 END LOOP;
1799 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1800 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1801 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1802 MERGE_LOG_ID,
1803 TABLE_NAME,
1804 MERGE_HEADER_ID,
1805 PRIMARY_KEY_ID1,
1806 NUM_COL1_ORIG,
1807 NUM_COL1_NEW,
1808 NUM_COL2_ORIG,
1809 NUM_COL2_NEW,
1810 ACTION_FLAG,
1811 REQUEST_ID,
1812 CREATED_BY,
1813 CREATION_DATE,
1814 LAST_UPDATE_LOGIN,
1815 LAST_UPDATE_DATE,
1816 LAST_UPDATED_BY
1817 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1818 'ozf_retail_price_points',
1819 MERGE_HEADER_ID_LIST(I),
1820 PRIMARY_KEY_ID1_LIST(I),
1821 NUM_COL1_ORIG_LIST(I),
1822 NUM_COL1_NEW_LIST(I),
1823 NUM_COL2_ORIG_LIST(I),
1824 NUM_COL2_NEW_LIST(I),
1825 'U',
1826 req_id,
1827 hz_utility_pub.CREATED_BY,
1828 hz_utility_pub.CREATION_DATE,
1829 hz_utility_pub.LAST_UPDATE_LOGIN,
1830 hz_utility_pub.LAST_UPDATE_DATE,
1831 hz_utility_pub.LAST_UPDATED_BY
1832 );
1833
1834 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1835 UPDATE ozf_retail_price_points yt SET
1836 cust_account_id=NUM_COL1_NEW_LIST(I)
1837 ,site_use_id=NUM_COL2_NEW_LIST(I)
1838 , LAST_UPDATE_DATE=SYSDATE
1839 , last_updated_by=arp_standard.profile.user_id
1840 , last_update_login=arp_standard.profile.last_update_login
1841 WHERE retail_price_point_id=PRIMARY_KEY_ID1_LIST(I)
1842 ;
1843 l_count := l_count + SQL%ROWCOUNT;
1844 IF l_last_fetch THEN
1845 EXIT;
1846 END IF;
1847 END LOOP;
1848
1849 arp_message.set_name('AR','AR_ROWS_UPDATED');
1850 arp_message.set_token('NUM_ROWS',to_char(l_count));
1851 END IF;
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 arp_message.set_line( 'merge_retail_price_points');
1855 RAISE;
1856 END merge_retail_price_points;
1857
1858
1859 /*-------------------------------------------------------------
1860 |
1861 | PROCEDURE
1862 | merge_trade_profiles
1863 | DESCRIPTION :
1864 | Account merge procedure for the table, ozf_cust_trd_prfls
1865 |
1866 | NOTES:
1867 |
1868 |--------------------------------------------------------------*/
1869
1870 PROCEDURE merge_trade_profiles (
1871 req_id NUMBER,
1872 set_num NUMBER,
1873 process_mode VARCHAR2) IS
1874
1875 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1876 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1877 INDEX BY BINARY_INTEGER;
1878 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1879
1880 TYPE trade_profile_id_LIST_TYPE IS TABLE OF
1881 ozf_cust_trd_prfls.trade_profile_id%TYPE
1882 INDEX BY BINARY_INTEGER;
1883 PRIMARY_KEY_ID1_LIST trade_profile_id_LIST_TYPE;
1884
1885 TYPE cust_account_id_LIST_TYPE IS TABLE OF
1886 ozf_cust_trd_prfls.cust_account_id%TYPE
1887 INDEX BY BINARY_INTEGER;
1888 NUM_COL1_ORIG_LIST cust_account_id_LIST_TYPE;
1889 NUM_COL1_NEW_LIST cust_account_id_LIST_TYPE;
1890
1891 TYPE cust_acct_site_id_LIST_TYPE IS TABLE OF
1892 ozf_cust_trd_prfls.cust_acct_site_id%TYPE
1893 INDEX BY BINARY_INTEGER;
1894 NUM_COL2_ORIG_LIST cust_acct_site_id_LIST_TYPE;
1895 NUM_COL2_NEW_LIST cust_acct_site_id_LIST_TYPE;
1896
1897 l_profile_val VARCHAR2(30);
1898 CURSOR merged_records IS
1899 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1900 ,trade_profile_id
1901 ,cust_account_id
1902 ,cust_acct_site_id
1903 FROM ozf_cust_trd_prfls yt, ra_customer_merges m
1904 WHERE (
1905 yt.cust_account_id = m.DUPLICATE_ID
1906 OR yt.cust_acct_site_id = m.DUPLICATE_SITE_ID
1907 ) AND m.process_flag = 'N'
1908 AND m.request_id = req_id
1909 AND m.set_number = set_num;
1910 l_last_fetch BOOLEAN := FALSE;
1911 l_count NUMBER;
1912 BEGIN
1913 IF process_mode='LOCK' THEN
1914 NULL;
1915 ELSE
1916 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1917 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_cust_trd_prfls',FALSE);
1918 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1919 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1920
1921 open merged_records;
1922 LOOP
1923 FETCH merged_records BULK COLLECT INTO
1924 MERGE_HEADER_ID_LIST
1925 , PRIMARY_KEY_ID1_LIST
1926 , NUM_COL1_ORIG_LIST
1927 , NUM_COL2_ORIG_LIST
1928 ;
1929 IF merged_records%NOTFOUND THEN
1930 l_last_fetch := TRUE;
1931 END IF;
1932 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1933 exit;
1934 END IF;
1935 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1936 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1937 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1938 END LOOP;
1939 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1940 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1941 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1942 MERGE_LOG_ID,
1943 TABLE_NAME,
1944 MERGE_HEADER_ID,
1945 PRIMARY_KEY_ID1,
1946 NUM_COL1_ORIG,
1947 NUM_COL1_NEW,
1948 NUM_COL2_ORIG,
1949 NUM_COL2_NEW,
1950 ACTION_FLAG,
1951 REQUEST_ID,
1952 CREATED_BY,
1953 CREATION_DATE,
1954 LAST_UPDATE_LOGIN,
1955 LAST_UPDATE_DATE,
1956 LAST_UPDATED_BY
1957 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1958 'ozf_cust_trd_prfls',
1959 MERGE_HEADER_ID_LIST(I),
1960 PRIMARY_KEY_ID1_LIST(I),
1961 NUM_COL1_ORIG_LIST(I),
1962 NUM_COL1_NEW_LIST(I),
1963 NUM_COL2_ORIG_LIST(I),
1964 NUM_COL2_NEW_LIST(I),
1965 'U',
1966 req_id,
1967 hz_utility_pub.CREATED_BY,
1968 hz_utility_pub.CREATION_DATE,
1969 hz_utility_pub.LAST_UPDATE_LOGIN,
1970 hz_utility_pub.LAST_UPDATE_DATE,
1971 hz_utility_pub.LAST_UPDATED_BY
1972 );
1973
1974 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1975 UPDATE ozf_cust_trd_prfls yt SET
1976 cust_account_id=NUM_COL1_NEW_LIST(I)
1977 ,cust_acct_site_id=NUM_COL2_NEW_LIST(I)
1978 , LAST_UPDATE_DATE=SYSDATE
1979 , last_updated_by=arp_standard.profile.user_id
1980 , last_update_login=arp_standard.profile.last_update_login
1981 , REQUEST_ID=req_id
1982 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
1983 , PROGRAM_ID=arp_standard.profile.program_id
1984 , PROGRAM_UPDATE_DATE=SYSDATE
1985 WHERE trade_profile_id=PRIMARY_KEY_ID1_LIST(I)
1986 ;
1987 l_count := l_count + SQL%ROWCOUNT;
1988 IF l_last_fetch THEN
1989 EXIT;
1990 END IF;
1991 END LOOP;
1992
1993 arp_message.set_name('AR','AR_ROWS_UPDATED');
1994 arp_message.set_token('NUM_ROWS',to_char(l_count));
1995 END IF;
1996 EXCEPTION
1997 WHEN OTHERS THEN
1998 arp_message.set_line( 'merge_trade_profiles');
1999 RAISE;
2000 END merge_trade_profiles;
2001
2002 END OZF_ACCOUNT_MERGE_PKG;