[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARTRX
Source
1 PACKAGE BODY ARP_CMERGE_ARTRX as
2 /* $Header: ARPLTRXB.pls 120.19.12020000.2 2012/07/26 03:26:31 riqi ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10 PROCEDURE ar_cr (
11 req_id NUMBER,
12 set_num NUMBER,
13 process_mode VARCHAR2) IS
14
15 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
16 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
17 INDEX BY BINARY_INTEGER;
18 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
19
20 TYPE cash_receipt_id_LIST_TYPE IS TABLE OF
21 AR_CASH_RECEIPTS.cash_receipt_id%TYPE
22 INDEX BY BINARY_INTEGER;
23 PRIMARY_KEY_ID_LIST cash_receipt_id_LIST_TYPE;
24
25 TYPE pay_from_customer_LIST_TYPE IS TABLE OF
26 AR_CASH_RECEIPTS.pay_from_customer%TYPE
27 INDEX BY BINARY_INTEGER;
28 NUM_COL1_ORIG_LIST pay_from_customer_LIST_TYPE;
29 NUM_COL1_NEW_LIST pay_from_customer_LIST_TYPE;
30
31 TYPE customer_site_use_id_LIST_TYPE IS TABLE OF
32 AR_CASH_RECEIPTS.customer_site_use_id%TYPE
33 INDEX BY BINARY_INTEGER;
34 NUM_COL2_ORIG_LIST customer_site_use_id_LIST_TYPE;
35 NUM_COL2_NEW_LIST customer_site_use_id_LIST_TYPE;
36
37 l_profile_val VARCHAR2(30);
38 CURSOR merged_records IS
39 SELECT distinct CUSTOMER_MERGE_HEADER_ID
40 ,cash_receipt_id
41 ,pay_from_customer
42 ,customer_site_use_id
43 FROM AR_CASH_RECEIPTS yt, ra_customer_merges m
44 WHERE ( (yt.pay_from_customer = m.DUPLICATE_ID AND
45 nvl(yt.customer_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
46 AND m.process_flag = 'N'
47 AND m.request_id = req_id
48 AND m.set_number = set_num;
49 l_last_fetch BOOLEAN := FALSE;
50 l_count NUMBER := 0;
51 BEGIN
52 IF process_mode='LOCK' THEN
53 NULL;
54 ELSE
55 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
56 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CASH_RECEIPTS',FALSE);
57 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
58 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
59
60 open merged_records;
61 LOOP
62 FETCH merged_records BULK COLLECT INTO
63 MERGE_HEADER_ID_LIST
64 , PRIMARY_KEY_ID_LIST
65 , NUM_COL1_ORIG_LIST
66 , NUM_COL2_ORIG_LIST
67 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
68 IF merged_records%NOTFOUND THEN
69 l_last_fetch := TRUE;
70 END IF;
71 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
72 exit;
73 END IF;
74 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
75 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
76 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
77 END LOOP;
78 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
79 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
80 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
81 MERGE_LOG_ID,
82 TABLE_NAME,
83 MERGE_HEADER_ID,
84 PRIMARY_KEY_ID,
85 NUM_COL1_ORIG,
86 NUM_COL1_NEW,
87 NUM_COL2_ORIG,
88 NUM_COL2_NEW,
89 ACTION_FLAG,
90 REQUEST_ID,
91 CREATED_BY,
92 CREATION_DATE,
93 LAST_UPDATE_LOGIN,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY
96 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
97 'AR_CASH_RECEIPTS',
98 MERGE_HEADER_ID_LIST(I),
99 PRIMARY_KEY_ID_LIST(I),
100 NUM_COL1_ORIG_LIST(I),
101 NUM_COL1_NEW_LIST(I),
102 NUM_COL2_ORIG_LIST(I),
103 NUM_COL2_NEW_LIST(I),
104 'U',
105 req_id,
106 hz_utility_pub.CREATED_BY,
107 hz_utility_pub.CREATION_DATE,
108 hz_utility_pub.LAST_UPDATE_LOGIN,
109 hz_utility_pub.LAST_UPDATE_DATE,
110 hz_utility_pub.LAST_UPDATED_BY
111 );
112
113 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
114 UPDATE AR_CASH_RECEIPTS yt SET
115 pay_from_customer=NUM_COL1_NEW_LIST(I)
116 ,customer_site_use_id=NUM_COL2_NEW_LIST(I)
117 , LAST_UPDATE_DATE=SYSDATE
118 , last_updated_by=arp_standard.profile.user_id
119 , last_update_login=arp_standard.profile.last_update_login
120 , REQUEST_ID=req_id
121 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
122 , PROGRAM_ID=arp_standard.profile.program_id
123 , PROGRAM_UPDATE_DATE=SYSDATE
124 WHERE cash_receipt_id=PRIMARY_KEY_ID_LIST(I)
125 ;
126 l_count := l_count + SQL%ROWCOUNT;
127 IF l_last_fetch THEN
128 EXIT;
129 END IF;
130 END LOOP;
131
132 arp_message.set_name('AR','AR_ROWS_UPDATED');
133 arp_message.set_token('NUM_ROWS',to_char(l_count));
134 END IF;
135 EXCEPTION
136 WHEN OTHERS THEN
137 arp_message.set_line( 'ar_cr');
138 RAISE;
139 END ar_cr;
140
141 PROCEDURE ar_ps (
142 req_id NUMBER,
143 set_num NUMBER,
144 process_mode VARCHAR2) IS
145
146 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
147 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
148 INDEX BY BINARY_INTEGER;
149 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
150
151 TYPE payment_schedule_id_LIST_TYPE IS TABLE OF
152 AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE
153 INDEX BY BINARY_INTEGER;
154 PRIMARY_KEY_ID_LIST payment_schedule_id_LIST_TYPE;
155
156 TYPE customer_id_LIST_TYPE IS TABLE OF
157 AR_PAYMENT_SCHEDULES.customer_id%TYPE
158 INDEX BY BINARY_INTEGER;
159 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
160 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
161
162 TYPE customer_site_use_id_LIST_TYPE IS TABLE OF
163 AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE
164 INDEX BY BINARY_INTEGER;
165 NUM_COL2_ORIG_LIST customer_site_use_id_LIST_TYPE;
166 NUM_COL2_NEW_LIST customer_site_use_id_LIST_TYPE;
167
168 l_profile_val VARCHAR2(30);
169 CURSOR merged_records IS
170 SELECT distinct CUSTOMER_MERGE_HEADER_ID
171 ,payment_schedule_id
172 ,yt.customer_id
173 ,customer_site_use_id
174 FROM AR_PAYMENT_SCHEDULES yt, ra_customer_merges m
175 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
176 nvl(yt.customer_site_use_id, m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
177 AND m.process_flag = 'N'
178 AND m.request_id = req_id
179 AND m.set_number = set_num;
180 l_last_fetch BOOLEAN := FALSE;
181 l_count NUMBER := 0;
182 BEGIN
183 IF process_mode='LOCK' THEN
184 NULL;
185 ELSE
186 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
187 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_PAYMENT_SCHEDULES',FALSE);
188 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
189 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
190
191 open merged_records;
192 LOOP
193 FETCH merged_records BULK COLLECT INTO
194 MERGE_HEADER_ID_LIST
195 , PRIMARY_KEY_ID_LIST
196 , NUM_COL1_ORIG_LIST
197 , NUM_COL2_ORIG_LIST
198 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
199 IF merged_records%NOTFOUND THEN
200 l_last_fetch := TRUE;
201 END IF;
202 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
203 exit;
204 END IF;
205 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
206 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
207 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
208 END LOOP;
209 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
210 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
211 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
212 MERGE_LOG_ID,
213 TABLE_NAME,
214 MERGE_HEADER_ID,
215 PRIMARY_KEY_ID,
216 NUM_COL1_ORIG,
217 NUM_COL1_NEW,
218 NUM_COL2_ORIG,
219 NUM_COL2_NEW,
220 ACTION_FLAG,
221 REQUEST_ID,
222 CREATED_BY,
223 CREATION_DATE,
224 LAST_UPDATE_LOGIN,
225 LAST_UPDATE_DATE,
226 LAST_UPDATED_BY
227 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
228 'AR_PAYMENT_SCHEDULES',
229 MERGE_HEADER_ID_LIST(I),
230 PRIMARY_KEY_ID_LIST(I),
231 NUM_COL1_ORIG_LIST(I),
232 NUM_COL1_NEW_LIST(I),
233 NUM_COL2_ORIG_LIST(I),
234 NUM_COL2_NEW_LIST(I),
235 'U',
236 req_id,
237 hz_utility_pub.CREATED_BY,
238 hz_utility_pub.CREATION_DATE,
239 hz_utility_pub.LAST_UPDATE_LOGIN,
240 hz_utility_pub.LAST_UPDATE_DATE,
241 hz_utility_pub.LAST_UPDATED_BY
242 );
243
244 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
245 UPDATE AR_PAYMENT_SCHEDULES yt SET
246 customer_id=NUM_COL1_NEW_LIST(I)
247 ,customer_site_use_id=NUM_COL2_NEW_LIST(I)
248 , LAST_UPDATE_DATE=SYSDATE
249 , last_updated_by=arp_standard.profile.user_id
250 , last_update_login=arp_standard.profile.last_update_login
251 , REQUEST_ID=req_id
252 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
253 , PROGRAM_ID=arp_standard.profile.program_id
254 , PROGRAM_UPDATE_DATE=SYSDATE
255 WHERE payment_schedule_id=PRIMARY_KEY_ID_LIST(I)
256 ;
257 l_count := l_count + SQL%ROWCOUNT;
258 IF l_last_fetch THEN
259 EXIT;
260 END IF;
261 END LOOP;
262
263 arp_message.set_name('AR','AR_ROWS_UPDATED');
264 arp_message.set_token('NUM_ROWS',to_char(l_count));
265 END IF;
266 EXCEPTION
267 WHEN OTHERS THEN
268 arp_message.set_line( 'ar_ps');
269 RAISE;
270 END ar_ps;
271
272
273 PROCEDURE ra_ct (
274 req_id NUMBER,
275 set_num NUMBER,
276 process_mode VARCHAR2) IS
277
278 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
279 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
280 INDEX BY BINARY_INTEGER;
281 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
282
283 TYPE customer_trx_id_LIST_TYPE IS TABLE OF
284 RA_CUSTOMER_TRX.customer_trx_id%TYPE
285 INDEX BY BINARY_INTEGER;
286 PRIMARY_KEY_ID_LIST customer_trx_id_LIST_TYPE;
287
288 TYPE bill_to_customer_id_LIST_TYPE IS TABLE OF
289 RA_CUSTOMER_TRX.bill_to_customer_id%TYPE
290 INDEX BY BINARY_INTEGER;
291 NUM_COL1_ORIG_LIST bill_to_customer_id_LIST_TYPE;
292 NUM_COL1_NEW_LIST bill_to_customer_id_LIST_TYPE;
293
294 TYPE bill_to_site_use_id_LIST_TYPE IS TABLE OF
295 RA_CUSTOMER_TRX.bill_to_site_use_id%TYPE
296 INDEX BY BINARY_INTEGER;
297 NUM_COL2_ORIG_LIST bill_to_site_use_id_LIST_TYPE;
298 NUM_COL2_NEW_LIST bill_to_site_use_id_LIST_TYPE;
299
300 TYPE paying_customer_id_LIST_TYPE IS TABLE OF
301 RA_CUSTOMER_TRX.paying_customer_id%TYPE
302 INDEX BY BINARY_INTEGER;
303 NUM_COL3_ORIG_LIST paying_customer_id_LIST_TYPE;
304 NUM_COL3_NEW_LIST paying_customer_id_LIST_TYPE;
305
306 TYPE paying_site_use_id_LIST_TYPE IS TABLE OF
307 RA_CUSTOMER_TRX.paying_site_use_id%TYPE
308 INDEX BY BINARY_INTEGER;
309 NUM_COL4_ORIG_LIST paying_site_use_id_LIST_TYPE;
310 NUM_COL4_NEW_LIST paying_site_use_id_LIST_TYPE;
311
312 TYPE ship_to_customer_id_LIST_TYPE IS TABLE OF
313 RA_CUSTOMER_TRX.ship_to_customer_id%TYPE
314 INDEX BY BINARY_INTEGER;
315 NUM_COL5_ORIG_LIST ship_to_customer_id_LIST_TYPE;
316 NUM_COL5_NEW_LIST ship_to_customer_id_LIST_TYPE;
317
318 TYPE ship_to_site_use_id_LIST_TYPE IS TABLE OF
319 RA_CUSTOMER_TRX.ship_to_site_use_id%TYPE
320 INDEX BY BINARY_INTEGER;
321 NUM_COL6_ORIG_LIST ship_to_site_use_id_LIST_TYPE;
322 NUM_COL6_NEW_LIST ship_to_site_use_id_LIST_TYPE;
323
324 TYPE sold_to_customer_id_LIST_TYPE IS TABLE OF
325 RA_CUSTOMER_TRX.sold_to_customer_id%TYPE
326 INDEX BY BINARY_INTEGER;
327 NUM_COL7_ORIG_LIST sold_to_customer_id_LIST_TYPE;
328 NUM_COL7_NEW_LIST sold_to_customer_id_LIST_TYPE;
329
330 TYPE sold_to_site_use_id_LIST_TYPE IS TABLE OF
331 RA_CUSTOMER_TRX.sold_to_site_use_id%TYPE
332 INDEX BY BINARY_INTEGER;
333 NUM_COL8_ORIG_LIST sold_to_site_use_id_LIST_TYPE;
334 NUM_COL8_NEW_LIST sold_to_site_use_id_LIST_TYPE;
335
336 /* Bug 10030466 */
337 TYPE drawee_id_LIST_TYPE IS TABLE OF
338 RA_CUSTOMER_TRX.drawee_id%TYPE
339 INDEX BY BINARY_INTEGER;
340 NUM_COL9_ORIG_LIST drawee_id_LIST_TYPE;
341 NUM_COL9_NEW_LIST drawee_id_LIST_TYPE;
342
343 TYPE drawee_site_use_id_LIST_TYPE IS TABLE OF
344 RA_CUSTOMER_TRX.drawee_site_use_id%TYPE
345 INDEX BY BINARY_INTEGER;
346 NUM_COL10_ORIG_LIST drawee_site_use_id_LIST_TYPE;
347 NUM_COL10_NEW_LIST drawee_site_use_id_LIST_TYPE;
348
349 l_profile_val VARCHAR2(30);
350 CURSOR merged_records IS
351 SELECT distinct CUSTOMER_MERGE_HEADER_ID
352 ,customer_trx_id
353 ,bill_to_customer_id
354 ,bill_to_site_use_id
355 ,paying_customer_id
356 ,paying_site_use_id
357 ,ship_to_customer_id
358 ,ship_to_site_use_id
359 ,sold_to_customer_id
360 ,sold_to_site_use_id
361 ,DRAWEE_ID
362 ,DRAWEE_SITE_USE_ID
363 FROM RA_CUSTOMER_TRX yt, ra_customer_merges m
364 WHERE ( (yt.bill_to_customer_id = m.DUPLICATE_ID AND
365 yt.bill_to_site_use_id = m.DUPLICATE_SITE_ID)
366 OR (yt.paying_customer_id = m.DUPLICATE_ID AND
367 yt.paying_site_use_id = m.DUPLICATE_SITE_ID)
368 OR (yt.ship_to_customer_id = m.DUPLICATE_ID AND
369 nvl(yt.ship_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
370 OR (yt.sold_to_customer_id = m.DUPLICATE_ID AND
371 nvl(yt.sold_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
372 OR (yt.drawee_id = m.DUPLICATE_ID AND
373 nvl(yt.drawee_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
374 AND m.process_flag = 'N'
375 AND m.request_id = req_id
376 AND m.set_number = set_num;
377 l_last_fetch BOOLEAN := FALSE;
378 l_count NUMBER := 0;
379 BEGIN
380 IF process_mode='LOCK' THEN
381 NULL;
382 ELSE
383 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
384 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RA_CUSTOMER_TRX',FALSE);
385 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
386 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
387
388 open merged_records;
389 LOOP
390 FETCH merged_records BULK COLLECT INTO
391 MERGE_HEADER_ID_LIST
392 , PRIMARY_KEY_ID_LIST
393 , NUM_COL1_ORIG_LIST
394 , NUM_COL2_ORIG_LIST
395 , NUM_COL3_ORIG_LIST
396 , NUM_COL4_ORIG_LIST
397 , NUM_COL5_ORIG_LIST
398 , NUM_COL6_ORIG_LIST
399 , NUM_COL7_ORIG_LIST
400 , NUM_COL8_ORIG_LIST
401 , NUM_COL9_ORIG_LIST
402 , NUM_COL10_ORIG_LIST
403 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
404 IF merged_records%NOTFOUND THEN
405 l_last_fetch := TRUE;
406 END IF;
407 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
408 exit;
409 END IF;
410 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
411 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
412 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
413 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL3_ORIG_LIST(I));
414 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL4_ORIG_LIST(I));
415 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL5_ORIG_LIST(I));
416 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL6_ORIG_LIST(I));
417 NUM_COL7_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL7_ORIG_LIST(I));
418 NUM_COL8_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL8_ORIG_LIST(I));
419 NUM_COL9_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL9_ORIG_LIST(I));
420 NUM_COL10_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL10_ORIG_LIST(I));
421 END LOOP;
422 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
423 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
424 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
425 MERGE_LOG_ID,
426 TABLE_NAME,
427 MERGE_HEADER_ID,
428 PRIMARY_KEY_ID,
429 NUM_COL1_ORIG,
430 NUM_COL1_NEW,
431 NUM_COL2_ORIG,
432 NUM_COL2_NEW,
433 NUM_COL3_ORIG,
434 NUM_COL3_NEW,
435 NUM_COL4_ORIG,
436 NUM_COL4_NEW,
437 NUM_COL5_ORIG,
438 NUM_COL5_NEW,
439 NUM_COL6_ORIG,
440 NUM_COL6_NEW,
441 NUM_COL7_ORIG,
442 NUM_COL7_NEW,
443 NUM_COL8_ORIG,
444 NUM_COL8_NEW,
445 VCHAR_COL1_ORIG,
446 VCHAR_COL1_NEW,
447 VCHAR_COL2_ORIG,
448 VCHAR_COL2_NEW,
449 ACTION_FLAG,
450 REQUEST_ID,
451 CREATED_BY,
452 CREATION_DATE,
453 LAST_UPDATE_LOGIN,
454 LAST_UPDATE_DATE,
455 LAST_UPDATED_BY
456 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
457 'RA_CUSTOMER_TRX',
458 MERGE_HEADER_ID_LIST(I),
459 PRIMARY_KEY_ID_LIST(I),
460 NUM_COL1_ORIG_LIST(I),
461 NUM_COL1_NEW_LIST(I),
462 NUM_COL2_ORIG_LIST(I),
463 NUM_COL2_NEW_LIST(I),
464 NUM_COL3_ORIG_LIST(I),
465 NUM_COL3_NEW_LIST(I),
466 NUM_COL4_ORIG_LIST(I),
467 NUM_COL4_NEW_LIST(I),
468 NUM_COL5_ORIG_LIST(I),
469 NUM_COL5_NEW_LIST(I),
470 NUM_COL6_ORIG_LIST(I),
471 NUM_COL6_NEW_LIST(I),
472 NUM_COL7_ORIG_LIST(I),
473 NUM_COL7_NEW_LIST(I),
474 NUM_COL8_ORIG_LIST(I),
475 NUM_COL8_NEW_LIST(I),
476 NUM_COL9_ORIG_LIST(I),
477 NUM_COL9_NEW_LIST(I),
478 NUM_COL10_ORIG_LIST(I),
479 NUM_COL10_NEW_LIST(I),
480 'U',
481 req_id,
482 hz_utility_pub.CREATED_BY,
483 hz_utility_pub.CREATION_DATE,
484 hz_utility_pub.LAST_UPDATE_LOGIN,
485 hz_utility_pub.LAST_UPDATE_DATE,
486 hz_utility_pub.LAST_UPDATED_BY
487 );
488 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
489 UPDATE RA_CUSTOMER_TRX yt SET
490 bill_to_customer_id=NUM_COL1_NEW_LIST(I)
491 ,bill_to_site_use_id=NUM_COL2_NEW_LIST(I)
492 ,paying_customer_id=NUM_COL3_NEW_LIST(I)
493 ,paying_site_use_id=NUM_COL4_NEW_LIST(I)
494 ,ship_to_customer_id=NUM_COL5_NEW_LIST(I)
495 ,ship_to_site_use_id=NUM_COL6_NEW_LIST(I)
496 ,sold_to_customer_id=NUM_COL7_NEW_LIST(I)
497 ,sold_to_site_use_id=NUM_COL8_NEW_LIST(I)
498 ,drawee_id=NUM_COL9_NEW_LIST(I)
499 ,drawee_site_use_id=NUM_COL10_NEW_LIST(I)
500 , LAST_UPDATE_DATE=SYSDATE
501 , last_updated_by=arp_standard.profile.user_id
502 , last_update_login=arp_standard.profile.last_update_login
503 , REQUEST_ID=req_id
504 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
505 , PROGRAM_ID=arp_standard.profile.program_id
506 , PROGRAM_UPDATE_DATE=SYSDATE
507 WHERE customer_trx_id=PRIMARY_KEY_ID_LIST(I)
508 ;
509 l_count := l_count + SQL%ROWCOUNT;
510 IF l_last_fetch THEN
511 EXIT;
512 END IF;
513 END LOOP;
514
515 arp_message.set_name('AR','AR_ROWS_UPDATED');
516 arp_message.set_token('NUM_ROWS',to_char(l_count));
517 END IF;
518 EXCEPTION
519 WHEN OTHERS THEN
520 arp_message.set_line( 'ra_ct');
521 RAISE;
522 END ra_ct;
523
524 -- bug9095566
525
526 PROCEDURE ra_ctl (
527 req_id NUMBER,
528 set_num NUMBER,
529 process_mode VARCHAR2) IS
530
531 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
532 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
533 INDEX BY BINARY_INTEGER;
534 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
535
536 TYPE customer_trx_line_id_LIST_TYPE IS TABLE OF
537 RA_CUSTOMER_TRX_LINES.customer_trx_line_id%TYPE
538 INDEX BY BINARY_INTEGER;
539 PRIMARY_KEY_ID_LIST customer_trx_line_id_LIST_TYPE;
540
541 TYPE ship_to_customer_id_LIST_TYPE IS TABLE OF
542 RA_CUSTOMER_TRX_LINES.ship_to_customer_id%TYPE
543 INDEX BY BINARY_INTEGER;
544 NUM_COL1_ORIG_LIST ship_to_customer_id_LIST_TYPE;
545 NUM_COL1_NEW_LIST ship_to_customer_id_LIST_TYPE;
546
547 TYPE ship_to_site_use_id_LIST_TYPE IS TABLE OF
548 RA_CUSTOMER_TRX_LINES.ship_to_site_use_id%TYPE
549 INDEX BY BINARY_INTEGER;
550 NUM_COL2_ORIG_LIST ship_to_site_use_id_LIST_TYPE;
551 NUM_COL2_NEW_LIST ship_to_site_use_id_LIST_TYPE;
552
553
554 l_profile_val VARCHAR2(30);
555 CURSOR merged_records IS
556 SELECT distinct CUSTOMER_MERGE_HEADER_ID
557 ,customer_trx_line_id
558 ,ship_to_customer_id
559 ,ship_to_site_use_id
560
561 FROM RA_CUSTOMER_TRX_LINES yt , ra_customer_merges m
562 WHERE
563 yt.ship_to_customer_id = m.DUPLICATE_ID AND
564 nvl(yt.ship_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID
565
566 AND m.process_flag = 'N'
567 AND m.request_id = req_id
568 AND m.set_number = set_num;
569 l_last_fetch BOOLEAN := FALSE;
570 l_count NUMBER := 0;
571 BEGIN
572 IF process_mode='LOCK' THEN
573 NULL;
574 ELSE
575 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
576 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RA_CUSTOMER_TRX_LINES',FALSE);
577 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
578 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
579
580 open merged_records;
581 LOOP
582 FETCH merged_records BULK COLLECT INTO
583 MERGE_HEADER_ID_LIST
584 , PRIMARY_KEY_ID_LIST
585 , NUM_COL1_ORIG_LIST
586 , NUM_COL2_ORIG_LIST
587
588 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
589 IF merged_records%NOTFOUND THEN
590 l_last_fetch := TRUE;
591 END IF;
592 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
593 exit;
594 END IF;
595 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
596
597 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
598 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
599
600 END LOOP;
601 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
602 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
603 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
604 MERGE_LOG_ID,
605 TABLE_NAME,
606 MERGE_HEADER_ID,
607 PRIMARY_KEY_ID,
608 NUM_COL1_ORIG,
609 NUM_COL1_NEW,
610 NUM_COL2_ORIG,
611 NUM_COL2_NEW,
612 ACTION_FLAG,
613 REQUEST_ID,
614 CREATED_BY,
615 CREATION_DATE,
616 LAST_UPDATE_LOGIN,
617 LAST_UPDATE_DATE,
618 LAST_UPDATED_BY
619 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
620 'RA_CUSTOMER_TRX_LINES',
621 MERGE_HEADER_ID_LIST(I),
622 PRIMARY_KEY_ID_LIST(I),
623 NUM_COL1_ORIG_LIST(I),
624 NUM_COL1_NEW_LIST(I),
625 NUM_COL2_ORIG_LIST(I),
626 NUM_COL2_NEW_LIST(I),
627 'U',
628 req_id,
629 hz_utility_pub.CREATED_BY,
630 hz_utility_pub.CREATION_DATE,
631 hz_utility_pub.LAST_UPDATE_LOGIN,
632 hz_utility_pub.LAST_UPDATE_DATE,
633 hz_utility_pub.LAST_UPDATED_BY
634 );
635 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
636 UPDATE RA_CUSTOMER_TRX_LINES yt SET
637
638 ship_to_customer_id=NUM_COL1_NEW_LIST(I)
639 ,ship_to_site_use_id=NUM_COL2_NEW_LIST(I)
640 , LAST_UPDATE_DATE=SYSDATE
641 , last_updated_by=arp_standard.profile.user_id
642 , last_update_login=arp_standard.profile.last_update_login
643 , REQUEST_ID=req_id
644 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
645 , PROGRAM_ID=arp_standard.profile.program_id
646 , PROGRAM_UPDATE_DATE=SYSDATE
647 WHERE customer_trx_line_id=PRIMARY_KEY_ID_LIST(I)
648 ;
649 l_count := l_count + SQL%ROWCOUNT;
650 IF l_last_fetch THEN
651 EXIT;
652 END IF;
653 END LOOP;
654
655 arp_message.set_name('AR','AR_ROWS_UPDATED');
656 arp_message.set_token('NUM_ROWS',to_char(l_count));
657 END IF;
658 EXCEPTION
659 WHEN OTHERS THEN
660 arp_message.set_line( 'ra_ctl');
661 RAISE;
662 END ra_ctl;
663
664
665 -- Bug 9155869 Added this procudure to update ar_receivable_applications table.
666 PROCEDURE ar_ra (
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 ra_id_LIST_TYPE IS TABLE OF
677 ar_receivable_applications.receivable_application_id%TYPE
678 INDEX BY BINARY_INTEGER;
679 PRIMARY_KEY_ID_LIST ra_id_LIST_TYPE;
680
681 TYPE cust_id_LIST_TYPE IS TABLE OF
682 ar_receivable_applications.on_acct_cust_id%TYPE
683 INDEX BY BINARY_INTEGER;
684 NUM_COL1_ORIG_LIST cust_id_LIST_TYPE;
685 NUM_COL1_NEW_LIST cust_id_LIST_TYPE;
686
687 TYPE cust_site_use_id_LIST_TYPE IS TABLE OF
688 ar_receivable_applications.on_acct_cust_site_use_id%TYPE
689 INDEX BY BINARY_INTEGER;
690 NUM_COL2_ORIG_LIST cust_site_use_id_LIST_TYPE;
691 NUM_COL2_NEW_LIST cust_site_use_id_LIST_TYPE;
692
693 l_profile_val VARCHAR2(30);
694
695 CURSOR merged_records IS
696 SELECT distinct CUSTOMER_MERGE_HEADER_ID
697 ,receivable_application_id
698 ,on_acct_cust_id
699 ,on_acct_cust_site_use_id
700 FROM ar_receivable_applications yt, ra_customer_merges m
701 WHERE (yt.on_acct_cust_id = m.DUPLICATE_ID AND
702 nvl(yt.on_acct_cust_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
703 AND m.process_flag = 'N'
704 AND m.request_id = req_id
705 AND m.set_number = set_num;
706
707 l_last_fetch BOOLEAN := FALSE;
708 l_count NUMBER :=0;
709 BEGIN
710 IF process_mode='LOCK' THEN
711 NULL;
712 ELSE
713 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
714 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ar_receivable_applications',FALSE);
715 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
716 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
717
718 open merged_records;
719 LOOP
720 FETCH merged_records BULK COLLECT INTO
721 MERGE_HEADER_ID_LIST
722 , PRIMARY_KEY_ID_LIST
723 , NUM_COL1_ORIG_LIST
724 , NUM_COL2_ORIG_LIST
725 LIMIT ARP_CMERGE.max_array_size;
726 IF merged_records%NOTFOUND THEN
727 l_last_fetch := TRUE;
728 END IF;
729 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
730 exit;
731 END IF;
732 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
733 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
734 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
735 END LOOP;
736 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
737 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
738 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
739 MERGE_LOG_ID,
740 TABLE_NAME,
741 MERGE_HEADER_ID,
742 PRIMARY_KEY_ID,
743 NUM_COL1_ORIG,
744 NUM_COL1_NEW,
745 NUM_COL2_ORIG,
746 NUM_COL2_NEW,
747 ACTION_FLAG,
748 REQUEST_ID,
749 CREATED_BY,
750 CREATION_DATE,
751 LAST_UPDATE_LOGIN,
752 LAST_UPDATE_DATE,
753 LAST_UPDATED_BY
754 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
755 'ar_receivable_applications',
756 MERGE_HEADER_ID_LIST(I),
757 PRIMARY_KEY_ID_LIST(I),
758 NUM_COL1_ORIG_LIST(I),
759 NUM_COL1_NEW_LIST(I),
760 NUM_COL2_ORIG_LIST(I),
761 NUM_COL2_NEW_LIST(I),
762 'U',
763 req_id,
764 hz_utility_pub.CREATED_BY,
765 hz_utility_pub.CREATION_DATE,
766 hz_utility_pub.LAST_UPDATE_LOGIN,
767 hz_utility_pub.LAST_UPDATE_DATE,
768 hz_utility_pub.LAST_UPDATED_BY
769 );
770 END IF;
771
772 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
773 UPDATE ar_receivable_applications yt SET
774 on_acct_cust_id=NUM_COL1_NEW_LIST(I)
775 ,on_acct_cust_site_use_id=NUM_COL2_NEW_LIST(I)
776 , LAST_UPDATE_DATE=SYSDATE
777 , last_updated_by=arp_standard.profile.user_id
778 , last_update_login=arp_standard.profile.last_update_login
779 , REQUEST_ID=req_id
780 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
781 , PROGRAM_ID=arp_standard.profile.program_id
782 , PROGRAM_UPDATE_DATE=SYSDATE
783 WHERE receivable_application_id=PRIMARY_KEY_ID_LIST(I)
784 ;
785 l_count := l_count + SQL%ROWCOUNT;
786 IF l_last_fetch THEN
787 EXIT;
788 END IF;
789 END LOOP;
790
791 arp_message.set_name('AR','AR_ROWS_UPDATED');
792 arp_message.set_token('NUM_ROWS',to_char(l_count));
793 END IF;
794 EXCEPTION
795 WHEN OTHERS THEN
796 arp_message.set_line( 'ar_ra');
797 RAISE;
798 END ar_ra;
799
800 PROCEDURE RA_INT (
801 req_id NUMBER,
802 set_num NUMBER,
803 process_mode VARCHAR2) IS
804
805 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
806 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
807 INDEX BY BINARY_INTEGER;
808 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
809
810 TYPE ROWID_LIST_TYPE IS TABLE OF
811 VARCHAR2(25)
812 INDEX BY BINARY_INTEGER;
813 PRIMARY_KEY1_LIST ROWID_LIST_TYPE;
814
815 TYPE ORIG_BILL_CUST_ID_LIST_TYPE IS TABLE OF
816 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_CUSTOMER_ID%TYPE
817 INDEX BY BINARY_INTEGER;
818 NUM_COL1_ORIG_LIST ORIG_BILL_CUST_ID_LIST_TYPE;
819 NUM_COL1_NEW_LIST ORIG_BILL_CUST_ID_LIST_TYPE;
820
821 TYPE ORIG_SHIP_CUST_ID_LIST_TYPE IS TABLE OF
822 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_CUSTOMER_ID%TYPE
823 INDEX BY BINARY_INTEGER;
824 NUM_COL2_ORIG_LIST ORIG_SHIP_CUST_ID_LIST_TYPE;
825 NUM_COL2_NEW_LIST ORIG_SHIP_CUST_ID_LIST_TYPE;
826
827 TYPE ORIG_SOLD_CUST_ID_LIST_TYPE IS TABLE OF
828 RA_INTERFACE_LINES.ORIG_SYSTEM_SOLD_CUSTOMER_ID%TYPE
829 INDEX BY BINARY_INTEGER;
830 NUM_COL3_ORIG_LIST ORIG_SOLD_CUST_ID_LIST_TYPE;
831 NUM_COL3_NEW_LIST ORIG_SOLD_CUST_ID_LIST_TYPE;
832
833 TYPE ORIG_BILL_ADD_ID_LIST_TYPE IS TABLE OF
834 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_ADDRESS_ID%TYPE
835 INDEX BY BINARY_INTEGER;
836 NUM_COL4_ORIG_LIST ORIG_BILL_ADD_ID_LIST_TYPE;
837 NUM_COL4_NEW_LIST ORIG_BILL_ADD_ID_LIST_TYPE;
838
839 TYPE ORIG_SHIP_ADD_ID_LIST_TYPE IS TABLE OF
840 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_ADDRESS_ID%TYPE
841 INDEX BY BINARY_INTEGER;
842 NUM_COL5_ORIG_LIST ORIG_SHIP_ADD_ID_LIST_TYPE;
843 NUM_COL5_NEW_LIST ORIG_SHIP_ADD_ID_LIST_TYPE;
844
845 TYPE ORIG_BILL_CUST_REF_LIST_TYPE IS TABLE OF
846 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_CUSTOMER_REF%TYPE
847 INDEX BY BINARY_INTEGER;
848 VCHAR_COL1_ORIG_LIST ORIG_BILL_CUST_REF_LIST_TYPE;
849 VCHAR_COL1_NEW_LIST ORIG_BILL_CUST_REF_LIST_TYPE;
850
851 TYPE ORIG_SHIP_CUST_REF_LIST_TYPE IS TABLE OF
852 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_CUSTOMER_REF%TYPE
853 INDEX BY BINARY_INTEGER;
854 VCHAR_COL2_ORIG_LIST ORIG_SHIP_CUST_REF_LIST_TYPE;
855 VCHAR_COL2_NEW_LIST ORIG_SHIP_CUST_REF_LIST_TYPE;
856
857 TYPE ORIG_SOLD_CUST_REF_LIST_TYPE IS TABLE OF
858 RA_INTERFACE_LINES.ORIG_SYSTEM_SOLD_CUSTOMER_REF%TYPE
859 INDEX BY BINARY_INTEGER;
860 VCHAR_COL3_ORIG_LIST ORIG_SOLD_CUST_REF_LIST_TYPE;
861 VCHAR_COL3_NEW_LIST ORIG_SOLD_CUST_REF_LIST_TYPE;
862
863 TYPE ORIG_BILL_ADD_REF_LIST_TYPE IS TABLE OF
864 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_ADDRESS_REF%TYPE
865 INDEX BY BINARY_INTEGER;
866 VCHAR_COL4_ORIG_LIST ORIG_BILL_ADD_REF_LIST_TYPE;
867 VCHAR_COL4_NEW_LIST ORIG_BILL_ADD_REF_LIST_TYPE;
868
869 TYPE ORIG_SHIP_ADD_REF_LIST_TYPE IS TABLE OF
870 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_ADDRESS_REF%TYPE
871 INDEX BY BINARY_INTEGER;
872 VCHAR_COL5_ORIG_LIST ORIG_SHIP_ADD_REF_LIST_TYPE;
873 VCHAR_COL5_NEW_LIST ORIG_SHIP_ADD_REF_LIST_TYPE;
874
875 /* Bug3500125 : Added following table type and added CUSTOMER_REF to CURSOR merged_records*/
876
877 TYPE CUSTOMER_REF_LIST_TYPE IS TABLE OF
878 RA_CUSTOMER_MERGES.CUSTOMER_REF%TYPE
879 INDEX BY BINARY_INTEGER;
880 VCHAR_COL6_ORIG_LIST CUSTOMER_REF_LIST_TYPE;
881 VCHAR_COL6_NEW_LIST CUSTOMER_REF_LIST_TYPE;
882
883 /*Additional change for ra_interface_lines under bug2447449*/
884 /* bug3667197: Modified the where clause of cursor merged_records to avoid
885 FTS on table ra_customer_merges */
886 /* bug4075234: Replaced 'exists' clause in CURSOR merged_records with 'IN' for performance
887 improvement */
888 l_profile_val VARCHAR2(30);
889
890 /*Bug 13808128 For better performance changed the query from "or" to "union" */
891
892 CURSOR merged_records IS
893 SELECT distinct CUSTOMER_MERGE_HEADER_ID
894 ,rai.ROWID
895 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
896 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
897 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
898 ,ORIG_SYSTEM_BILL_ADDRESS_ID
899 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
900 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
901 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
902 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
903 ,ORIG_SYSTEM_BILL_ADDRESS_REF
904 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
905 ,m.CUSTOMER_REF
906 from ra_interface_lines rai,
907 ra_customer_merges m
908 where nvl(rai.interface_status,'N') <> 'P'
909 and m.process_flag = 'N'
910 and m.request_id = req_id
911 and m.set_number = set_num
912 and m.duplicate_id = rai.orig_system_bill_customer_id
913 union
914 SELECT distinct CUSTOMER_MERGE_HEADER_ID
915 ,rai.ROWID
916 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
917 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
918 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
919 ,ORIG_SYSTEM_BILL_ADDRESS_ID
920 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
921 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
922 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
923 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
924 ,ORIG_SYSTEM_BILL_ADDRESS_REF
925 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
926 ,m.CUSTOMER_REF
927 from ra_interface_lines rai,
928 ra_customer_merges m
929 where nvl(rai.interface_status,'N') <> 'P'
930 and m.process_flag = 'N'
931 and m.request_id = req_id
932 and m.set_number = set_num
933 and m.duplicate_ref = rai.orig_system_bill_customer_ref
934 union
935 SELECT distinct CUSTOMER_MERGE_HEADER_ID
936 ,rai.ROWID
937 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
938 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
939 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
940 ,ORIG_SYSTEM_BILL_ADDRESS_ID
941 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
942 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
943 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
944 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
945 ,ORIG_SYSTEM_BILL_ADDRESS_REF
946 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
947 ,m.CUSTOMER_REF
948 from ra_interface_lines rai,
949 ra_customer_merges m
950 where nvl(rai.interface_status,'N') <> 'P'
951 and m.process_flag = 'N'
952 and m.request_id = req_id
953 and m.set_number = set_num
954 and m.duplicate_address_id = rai.orig_system_bill_address_id
955 union
956 SELECT distinct CUSTOMER_MERGE_HEADER_ID
957 ,rai.ROWID
958 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
959 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
960 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
961 ,ORIG_SYSTEM_BILL_ADDRESS_ID
962 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
963 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
964 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
965 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
966 ,ORIG_SYSTEM_BILL_ADDRESS_REF
967 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
968 ,m.CUSTOMER_REF
969 from ra_interface_lines rai,
970 ra_customer_merges m
971 where nvl(rai.interface_status,'N') <> 'P'
972 and m.process_flag = 'N'
973 and m.request_id = req_id
974 and m.set_number = set_num
975 and m.duplicate_id = rai.orig_system_ship_customer_id
976 union
977 SELECT distinct CUSTOMER_MERGE_HEADER_ID
978 ,rai.ROWID
979 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
980 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
981 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
982 ,ORIG_SYSTEM_BILL_ADDRESS_ID
983 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
984 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
985 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
986 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
987 ,ORIG_SYSTEM_BILL_ADDRESS_REF
988 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
989 ,m.CUSTOMER_REF
990 from ra_interface_lines rai,
991 ra_customer_merges m
992 where nvl(rai.interface_status,'N') <> 'P'
993 and m.process_flag = 'N'
994 and m.request_id = req_id
995 and m.set_number = set_num
996 and m.duplicate_ref = rai.orig_system_ship_customer_ref
997 union
998 SELECT distinct CUSTOMER_MERGE_HEADER_ID
999 ,rai.ROWID
1000 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
1001 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
1002 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
1003 ,ORIG_SYSTEM_BILL_ADDRESS_ID
1004 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
1005 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
1006 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
1007 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
1008 ,ORIG_SYSTEM_BILL_ADDRESS_REF
1009 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1010 ,m.CUSTOMER_REF
1011 from ra_interface_lines rai,
1012 ra_customer_merges m
1013 where nvl(rai.interface_status,'N') <> 'P'
1014 and m.process_flag = 'N'
1015 and m.request_id = req_id
1016 and m.set_number = set_num
1017 and m.duplicate_address_id = rai.orig_system_ship_address_id
1018 union
1019 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1020 ,rai.ROWID
1021 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
1022 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
1023 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
1024 ,ORIG_SYSTEM_BILL_ADDRESS_ID
1025 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
1026 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
1027 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
1028 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
1029 ,ORIG_SYSTEM_BILL_ADDRESS_REF
1030 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1031 ,m.CUSTOMER_REF
1032 from ra_interface_lines rai,
1033 ra_customer_merges m
1034 where nvl(rai.interface_status,'N') <> 'P'
1035 and m.process_flag = 'N'
1036 and m.request_id = req_id
1037 and m.set_number = set_num
1038 and m.duplicate_id = rai.orig_system_sold_customer_id
1039 union
1040 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1041 ,rai.ROWID
1042 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
1043 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
1044 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
1045 ,ORIG_SYSTEM_BILL_ADDRESS_ID
1046 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
1047 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
1048 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
1049 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
1050 ,ORIG_SYSTEM_BILL_ADDRESS_REF
1051 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1052 ,m.CUSTOMER_REF
1053 from ra_interface_lines rai,
1054 ra_customer_merges m
1055 where nvl(rai.interface_status,'N') <> 'P'
1056 and m.process_flag = 'N'
1057 and m.request_id = req_id
1058 and m.set_number = set_num
1059 and m.duplicate_ref = rai.orig_system_sold_customer_ref
1060 union
1061 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1062 ,rai.ROWID
1063 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
1064 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
1065 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
1066 ,ORIG_SYSTEM_BILL_ADDRESS_ID
1067 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
1068 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
1069 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
1070 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
1071 ,ORIG_SYSTEM_BILL_ADDRESS_REF
1072 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1073 ,m.CUSTOMER_REF
1074 from ra_interface_lines rai,
1075 ra_customer_merges m
1076 where nvl(rai.interface_status,'N') <> 'P'
1077 and m.process_flag = 'N'
1078 and m.request_id = req_id
1079 and m.set_number = set_num
1080 and rai.orig_system_bill_address_ref IN ( select
1081 ra.orig_system_reference
1082 from hz_cust_acct_sites ra
1083 where m.duplicate_address_id = ra.cust_acct_site_id)
1084 union
1085 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1086 ,rai.ROWID
1087 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
1088 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
1089 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
1090 ,ORIG_SYSTEM_BILL_ADDRESS_ID
1091 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
1092 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
1093 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
1094 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
1095 ,ORIG_SYSTEM_BILL_ADDRESS_REF
1096 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1097 ,m.CUSTOMER_REF
1098 from ra_interface_lines rai,
1099 ra_customer_merges m
1100 where nvl(rai.interface_status,'N') <> 'P'
1101 and m.process_flag = 'N'
1102 and m.request_id = req_id
1103 and m.set_number = set_num
1104 and rai.orig_system_ship_address_ref IN (select
1105 ra.orig_system_reference
1106 from hz_cust_acct_sites ra
1107 where m.duplicate_address_id = ra.cust_acct_site_id)
1108 ;
1109
1110 l_last_fetch BOOLEAN := FALSE;
1111 l_count NUMBER := 0;
1112
1113
1114 BEGIN
1115 IF process_mode='LOCK' THEN
1116 NULL;
1117 ELSE
1118 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1119 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RA_INTERFACE_LINES',FALSE);
1120 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1121 /*Additional change for ra_interface_lines under bug2447449*/
1122 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1123 open merged_records;
1124 LOOP
1125 FETCH merged_records BULK COLLECT INTO
1126 MERGE_HEADER_ID_LIST
1127 , PRIMARY_KEY1_LIST
1128 , NUM_COL1_ORIG_LIST
1129 , NUM_COL2_ORIG_LIST
1130 , NUM_COL3_ORIG_LIST
1131 , NUM_COL4_ORIG_LIST
1132 , NUM_COL5_ORIG_LIST
1133 , VCHAR_COL1_ORIG_LIST
1134 , VCHAR_COL2_ORIG_LIST
1135 , VCHAR_COL3_ORIG_LIST
1136 , VCHAR_COL4_ORIG_LIST
1137 , VCHAR_COL5_ORIG_LIST
1138 , VCHAR_COL6_NEW_LIST
1139 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
1140 IF merged_records%NOTFOUND THEN
1141 l_last_fetch := TRUE;
1142 END IF;
1143 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1144 exit;
1145 END IF;
1146 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1147
1148 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1149 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
1150 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL3_ORIG_LIST(I));
1151 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL4_ORIG_LIST(I));
1152 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL5_ORIG_LIST(I));
1153
1154 /* Bug3500125:Modified code to assign values to original references with new reference values*/
1155 IF VCHAR_COL1_ORIG_LIST(I) IS NOT NULL THEN
1156 VCHAR_COL1_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
1157 ELSE
1158 VCHAR_COL1_NEW_LIST(I) := NULL;
1159 END IF;
1160
1161 IF VCHAR_COL2_ORIG_LIST(I) IS NOT NULL THEN
1162 VCHAR_COL2_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
1163 ELSE
1164 VCHAR_COL2_NEW_LIST(I) := NULL;
1165 END IF;
1166
1167 IF VCHAR_COL3_ORIG_LIST(I) IS NOT NULL THEN
1168 VCHAR_COL3_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
1169 ELSE
1170 VCHAR_COL3_NEW_LIST(I) := NULL;
1171 END IF;
1172
1173 IF VCHAR_COL4_ORIG_LIST(I) IS NOT NULL THEN
1174 VCHAR_COL4_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
1175 ELSE
1176 VCHAR_COL4_NEW_LIST(I) := NULL;
1177 END IF;
1178
1179 IF VCHAR_COL5_ORIG_LIST(I) IS NOT NULL THEN
1180 VCHAR_COL5_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
1181 ELSE
1182 VCHAR_COL5_NEW_LIST(I) := NULL;
1183 END IF;
1184
1185 END LOOP;
1186
1187 /*Additional change for ra_interface_lines under bug2447449*/
1188 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1189 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1190 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1191 MERGE_LOG_ID,
1192 TABLE_NAME,
1193 MERGE_HEADER_ID,
1194 PRIMARY_KEY1,
1195 NUM_COL1_ORIG,
1196 NUM_COL1_NEW,
1197 NUM_COL2_ORIG,
1198 NUM_COL2_NEW,
1199 NUM_COL3_ORIG,
1200 NUM_COL3_NEW,
1201 NUM_COL4_ORIG,
1202 NUM_COL4_NEW,
1203 NUM_COL5_ORIG,
1204 NUM_COL5_NEW,
1205 VCHAR_COL1_ORIG,
1206 VCHAR_COL1_NEW,
1207 VCHAR_COL2_ORIG,
1208 VCHAR_COL2_NEW,
1209 VCHAR_COL3_ORIG,
1210 VCHAR_COL3_NEW,
1211 VCHAR_COL4_ORIG,
1212 VCHAR_COL4_NEW,
1213 VCHAR_COL5_ORIG,
1214 VCHAR_COL5_NEW,
1215 ACTION_FLAG,
1216 REQUEST_ID,
1217 CREATED_BY,
1218 CREATION_DATE,
1219 LAST_UPDATE_LOGIN,
1220 LAST_UPDATE_DATE,
1221 LAST_UPDATED_BY
1222 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1223 'RA_INTERFACE_LINES',
1224 MERGE_HEADER_ID_LIST(I),
1225 PRIMARY_KEY1_LIST(I),
1226 NUM_COL1_ORIG_LIST(I),
1227 NUM_COL1_NEW_LIST(I),
1228 NUM_COL2_ORIG_LIST(I),
1229 NUM_COL2_NEW_LIST(I),
1230 NUM_COL3_ORIG_LIST(I),
1231 NUM_COL3_NEW_LIST(I),
1232 NUM_COL4_ORIG_LIST(I),
1233 NUM_COL4_NEW_LIST(I),
1234 NUM_COL5_ORIG_LIST(I),
1235 NUM_COL5_NEW_LIST(I),
1236 VCHAR_COL1_ORIG_LIST(I),
1237 VCHAR_COL1_NEW_LIST(I),
1238 VCHAR_COL2_ORIG_LIST(I),
1239 VCHAR_COL2_NEW_LIST(I),
1240 VCHAR_COL3_ORIG_LIST(I),
1241 VCHAR_COL3_NEW_LIST(I),
1242 VCHAR_COL4_ORIG_LIST(I),
1243 VCHAR_COL4_NEW_LIST(I),
1244 VCHAR_COL5_ORIG_LIST(I),
1245 VCHAR_COL5_NEW_LIST(I),
1246 'U',
1247 req_id,
1248 hz_utility_pub.CREATED_BY,
1249 hz_utility_pub.CREATION_DATE,
1250 hz_utility_pub.LAST_UPDATE_LOGIN,
1251 hz_utility_pub.LAST_UPDATE_DATE,
1252 hz_utility_pub.LAST_UPDATED_BY
1253 );
1254
1255 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1256 UPDATE RA_INTERFACE_LINES yt SET
1257 ORIG_SYSTEM_BILL_CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
1258 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID=NUM_COL2_NEW_LIST(I)
1259 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID=NUM_COL3_NEW_LIST(I)
1260 ,ORIG_SYSTEM_BILL_ADDRESS_ID=NUM_COL4_NEW_LIST(I)
1261 ,ORIG_SYSTEM_SHIP_ADDRESS_ID=NUM_COL5_NEW_LIST(I)
1262 ,ORIG_SYSTEM_BILL_CUSTOMER_REF=VCHAR_COL1_NEW_LIST(I)
1263 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF=VCHAR_COL2_NEW_LIST(I)
1264 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF=VCHAR_COL3_NEW_LIST(I)
1265 ,ORIG_SYSTEM_BILL_ADDRESS_REF=VCHAR_COL4_NEW_LIST(I)
1266 ,ORIG_SYSTEM_SHIP_ADDRESS_REF=VCHAR_COL5_NEW_LIST(I)
1267 , LAST_UPDATE_DATE=SYSDATE
1268 , last_updated_by=arp_standard.profile.user_id
1269 , last_update_login=arp_standard.profile.last_update_login
1270 WHERE ROWID=PRIMARY_KEY1_LIST(I)
1271 ;
1272 l_count := l_count + SQL%ROWCOUNT;
1273 IF l_last_fetch THEN
1274 EXIT;
1275 END IF;
1276 END LOOP;
1277
1278 arp_message.set_name('AR','AR_ROWS_UPDATED');
1279 arp_message.set_token('NUM_ROWS',to_char(l_count));
1280 END IF;
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 arp_message.set_line( 'RA_INT');
1284 RAISE;
1285 END RA_INT;
1286
1287 PROCEDURE ar_ard (
1288 req_id NUMBER,
1289 set_num NUMBER,
1290 process_mode VARCHAR2) IS
1291
1292 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1293 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1294 INDEX BY BINARY_INTEGER;
1295 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1296
1297 TYPE line_id_LIST_TYPE IS TABLE OF
1298 AR_DISTRIBUTIONS.line_id%TYPE
1299 INDEX BY BINARY_INTEGER;
1300 PRIMARY_KEY_ID_LIST line_id_LIST_TYPE;
1301
1302 TYPE third_party_id_LIST_TYPE IS TABLE OF
1303 AR_DISTRIBUTIONS.third_party_id%TYPE
1304 INDEX BY BINARY_INTEGER;
1305 NUM_COL1_ORIG_LIST third_party_id_LIST_TYPE;
1306 NUM_COL1_NEW_LIST third_party_id_LIST_TYPE;
1307
1308 TYPE third_party_sub_id_LIST_TYPE IS TABLE OF
1309 AR_DISTRIBUTIONS.third_party_sub_id%TYPE
1310 INDEX BY BINARY_INTEGER;
1311 NUM_COL2_ORIG_LIST third_party_sub_id_LIST_TYPE;
1312 NUM_COL2_NEW_LIST third_party_sub_id_LIST_TYPE;
1313
1314 l_profile_val VARCHAR2(30);
1315 CURSOR merged_records IS
1316 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1317 ,line_id
1318 ,third_party_id
1319 ,third_party_sub_id
1320 FROM AR_DISTRIBUTIONS yt, ra_customer_merges m
1321 WHERE ( (yt.third_party_id = m.DUPLICATE_ID AND
1322 nvl(yt.third_party_sub_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
1323 AND m.process_flag = 'N'
1324 AND m.request_id = req_id
1325 AND m.set_number = set_num;
1326 l_last_fetch BOOLEAN := FALSE;
1327 l_count NUMBER := 0;
1328 BEGIN
1329 IF process_mode='LOCK' THEN
1330 NULL;
1331 ELSE
1332 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1333 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_DISTRIBUTIONS',FALSE);
1334 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1335 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1336
1337 open merged_records;
1338 LOOP
1339 FETCH merged_records BULK COLLECT INTO
1340 MERGE_HEADER_ID_LIST
1341 , PRIMARY_KEY_ID_LIST
1342 , NUM_COL1_ORIG_LIST
1343 , NUM_COL2_ORIG_LIST
1344 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
1345 IF merged_records%NOTFOUND THEN
1346 l_last_fetch := TRUE;
1347 END IF;
1348 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1349 exit;
1350 END IF;
1351 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1352 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1353 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1354 END LOOP;
1355 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1356 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1357 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1358 MERGE_LOG_ID,
1359 TABLE_NAME,
1360 MERGE_HEADER_ID,
1361 PRIMARY_KEY_ID,
1362 NUM_COL1_ORIG,
1363 NUM_COL1_NEW,
1364 NUM_COL2_ORIG,
1365 NUM_COL2_NEW,
1366 ACTION_FLAG,
1367 REQUEST_ID,
1368 CREATED_BY,
1369 CREATION_DATE,
1370 LAST_UPDATE_LOGIN,
1371 LAST_UPDATE_DATE,
1372 LAST_UPDATED_BY
1373 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1374 'AR_DISTRIBUTIONS',
1375 MERGE_HEADER_ID_LIST(I),
1376 PRIMARY_KEY_ID_LIST(I),
1377 NUM_COL1_ORIG_LIST(I),
1378 NUM_COL1_NEW_LIST(I),
1379 NUM_COL2_ORIG_LIST(I),
1380 NUM_COL2_NEW_LIST(I),
1381 'U',
1382 req_id,
1383 hz_utility_pub.CREATED_BY,
1384 hz_utility_pub.CREATION_DATE,
1385 hz_utility_pub.LAST_UPDATE_LOGIN,
1386 hz_utility_pub.LAST_UPDATE_DATE,
1387 hz_utility_pub.LAST_UPDATED_BY
1388 );
1389
1390 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1391 UPDATE AR_DISTRIBUTIONS yt SET
1392 third_party_id=NUM_COL1_NEW_LIST(I)
1393 ,third_party_sub_id=NUM_COL2_NEW_LIST(I)
1394 , LAST_UPDATE_DATE=SYSDATE
1395 , last_updated_by=arp_standard.profile.user_id
1396 , last_update_login=arp_standard.profile.last_update_login
1397 WHERE line_id=PRIMARY_KEY_ID_LIST(I)
1398 ;
1399 l_count := l_count + SQL%ROWCOUNT;
1400 IF l_last_fetch THEN
1401 EXIT;
1402 END IF;
1403 END LOOP;
1404
1405 arp_message.set_name('AR','AR_ROWS_UPDATED');
1406 arp_message.set_token('NUM_ROWS',to_char(l_count));
1407 END IF;
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 arp_message.set_line( 'ar_ard');
1411 RAISE;
1412 END ar_ard;
1413
1414
1415 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
1416 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
1417 BEGIN
1418
1419 arp_message.set_line( 'ARP_CMERGE_ARTRX.MERGE()+' );
1420
1421 ar_cr( req_id, set_num, process_mode );
1422 ar_ps( req_id, set_num, process_mode );
1423 ra_ct( req_id, set_num, process_mode );
1424 ra_ctl ( req_id, set_num, process_mode );
1425 ra_int(req_id, set_num, process_mode );
1426 ar_ard(req_id, set_num, process_mode );
1427 ar_ra(req_id, set_num, process_mode ); -- Bug 9155869
1428
1429 arp_message.set_line( 'ARP_CMERGE_ARTRX.MERGE()-' );
1430
1431 EXCEPTION
1432 when others then
1433 raise;
1434
1435 END merge;
1436
1437 END ARP_CMERGE_ARTRX;