[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARTRX
Source
1 PACKAGE BODY ARP_CMERGE_ARTRX as
2 /* $Header: ARPLTRXB.pls 120.15.12010000.2 2008/09/19 11:47:01 tthangav 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 l_profile_val VARCHAR2(30);
336 CURSOR merged_records IS
337 SELECT distinct CUSTOMER_MERGE_HEADER_ID
338 ,customer_trx_id
339 ,bill_to_customer_id
340 ,bill_to_site_use_id
341 ,paying_customer_id
342 ,paying_site_use_id
343 ,ship_to_customer_id
344 ,ship_to_site_use_id
345 ,sold_to_customer_id
346 ,sold_to_site_use_id
347 FROM RA_CUSTOMER_TRX yt, ra_customer_merges m
348 WHERE ( (yt.bill_to_customer_id = m.DUPLICATE_ID AND
349 yt.bill_to_site_use_id = m.DUPLICATE_SITE_ID)
350 OR (yt.paying_customer_id = m.DUPLICATE_ID AND
351 yt.paying_site_use_id = m.DUPLICATE_SITE_ID)
352 OR (yt.ship_to_customer_id = m.DUPLICATE_ID AND
353 nvl(yt.ship_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
354 OR (yt.sold_to_customer_id = m.DUPLICATE_ID AND
355 nvl(yt.sold_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
356 AND m.process_flag = 'N'
357 AND m.request_id = req_id
358 AND m.set_number = set_num;
359 l_last_fetch BOOLEAN := FALSE;
360 l_count NUMBER := 0;
361 BEGIN
362 IF process_mode='LOCK' THEN
363 NULL;
364 ELSE
365 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
366 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RA_CUSTOMER_TRX',FALSE);
367 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
368 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
369
370 open merged_records;
371 LOOP
372 FETCH merged_records BULK COLLECT INTO
373 MERGE_HEADER_ID_LIST
374 , PRIMARY_KEY_ID_LIST
375 , NUM_COL1_ORIG_LIST
376 , NUM_COL2_ORIG_LIST
377 , NUM_COL3_ORIG_LIST
378 , NUM_COL4_ORIG_LIST
379 , NUM_COL5_ORIG_LIST
380 , NUM_COL6_ORIG_LIST
381 , NUM_COL7_ORIG_LIST
382 , NUM_COL8_ORIG_LIST
383 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
384 IF merged_records%NOTFOUND THEN
385 l_last_fetch := TRUE;
386 END IF;
387 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
388 exit;
389 END IF;
390 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
391 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
392 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
393 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL3_ORIG_LIST(I));
394 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL4_ORIG_LIST(I));
395 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL5_ORIG_LIST(I));
396 NUM_COL6_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL6_ORIG_LIST(I));
397 NUM_COL7_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL7_ORIG_LIST(I));
398 NUM_COL8_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL8_ORIG_LIST(I));
399 END LOOP;
400 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
401 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
402 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
403 MERGE_LOG_ID,
404 TABLE_NAME,
405 MERGE_HEADER_ID,
406 PRIMARY_KEY_ID,
407 NUM_COL1_ORIG,
408 NUM_COL1_NEW,
409 NUM_COL2_ORIG,
410 NUM_COL2_NEW,
411 NUM_COL3_ORIG,
412 NUM_COL3_NEW,
413 NUM_COL4_ORIG,
414 NUM_COL4_NEW,
415 NUM_COL5_ORIG,
416 NUM_COL5_NEW,
417 NUM_COL6_ORIG,
418 NUM_COL6_NEW,
419 NUM_COL7_ORIG,
420 NUM_COL7_NEW,
421 NUM_COL8_ORIG,
422 NUM_COL8_NEW,
423 ACTION_FLAG,
424 REQUEST_ID,
425 CREATED_BY,
426 CREATION_DATE,
427 LAST_UPDATE_LOGIN,
428 LAST_UPDATE_DATE,
429 LAST_UPDATED_BY
430 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
431 'RA_CUSTOMER_TRX',
432 MERGE_HEADER_ID_LIST(I),
433 PRIMARY_KEY_ID_LIST(I),
434 NUM_COL1_ORIG_LIST(I),
435 NUM_COL1_NEW_LIST(I),
436 NUM_COL2_ORIG_LIST(I),
437 NUM_COL2_NEW_LIST(I),
438 NUM_COL3_ORIG_LIST(I),
439 NUM_COL3_NEW_LIST(I),
440 NUM_COL4_ORIG_LIST(I),
441 NUM_COL4_NEW_LIST(I),
442 NUM_COL5_ORIG_LIST(I),
443 NUM_COL5_NEW_LIST(I),
444 NUM_COL6_ORIG_LIST(I),
445 NUM_COL6_NEW_LIST(I),
446 NUM_COL7_ORIG_LIST(I),
447 NUM_COL7_NEW_LIST(I),
448 NUM_COL8_ORIG_LIST(I),
449 NUM_COL8_NEW_LIST(I),
450 'U',
451 req_id,
452 hz_utility_pub.CREATED_BY,
453 hz_utility_pub.CREATION_DATE,
454 hz_utility_pub.LAST_UPDATE_LOGIN,
455 hz_utility_pub.LAST_UPDATE_DATE,
456 hz_utility_pub.LAST_UPDATED_BY
457 );
458 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
459 UPDATE RA_CUSTOMER_TRX yt SET
460 bill_to_customer_id=NUM_COL1_NEW_LIST(I)
461 ,bill_to_site_use_id=NUM_COL2_NEW_LIST(I)
462 ,paying_customer_id=NUM_COL3_NEW_LIST(I)
463 ,paying_site_use_id=NUM_COL4_NEW_LIST(I)
464 ,ship_to_customer_id=NUM_COL5_NEW_LIST(I)
465 ,ship_to_site_use_id=NUM_COL6_NEW_LIST(I)
466 ,sold_to_customer_id=NUM_COL7_NEW_LIST(I)
467 ,sold_to_site_use_id=NUM_COL8_NEW_LIST(I)
468 , LAST_UPDATE_DATE=SYSDATE
469 , last_updated_by=arp_standard.profile.user_id
470 , last_update_login=arp_standard.profile.last_update_login
471 , REQUEST_ID=req_id
472 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
473 , PROGRAM_ID=arp_standard.profile.program_id
474 , PROGRAM_UPDATE_DATE=SYSDATE
475 WHERE customer_trx_id=PRIMARY_KEY_ID_LIST(I)
476 ;
477 l_count := l_count + SQL%ROWCOUNT;
478 IF l_last_fetch THEN
479 EXIT;
480 END IF;
481 END LOOP;
482
483 arp_message.set_name('AR','AR_ROWS_UPDATED');
484 arp_message.set_token('NUM_ROWS',to_char(l_count));
485 END IF;
486 EXCEPTION
487 WHEN OTHERS THEN
488 arp_message.set_line( 'ra_ct');
489 RAISE;
490 END ra_ct;
491
492 PROCEDURE RA_INT (
493 req_id NUMBER,
494 set_num NUMBER,
495 process_mode VARCHAR2) IS
496
497 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
498 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
499 INDEX BY BINARY_INTEGER;
500 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
501
502 TYPE ROWID_LIST_TYPE IS TABLE OF
503 VARCHAR2(25)
504 INDEX BY BINARY_INTEGER;
505 PRIMARY_KEY1_LIST ROWID_LIST_TYPE;
506
507 TYPE ORIG_BILL_CUST_ID_LIST_TYPE IS TABLE OF
508 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_CUSTOMER_ID%TYPE
509 INDEX BY BINARY_INTEGER;
510 NUM_COL1_ORIG_LIST ORIG_BILL_CUST_ID_LIST_TYPE;
511 NUM_COL1_NEW_LIST ORIG_BILL_CUST_ID_LIST_TYPE;
512
513 TYPE ORIG_SHIP_CUST_ID_LIST_TYPE IS TABLE OF
514 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_CUSTOMER_ID%TYPE
515 INDEX BY BINARY_INTEGER;
516 NUM_COL2_ORIG_LIST ORIG_SHIP_CUST_ID_LIST_TYPE;
517 NUM_COL2_NEW_LIST ORIG_SHIP_CUST_ID_LIST_TYPE;
518
519 TYPE ORIG_SOLD_CUST_ID_LIST_TYPE IS TABLE OF
520 RA_INTERFACE_LINES.ORIG_SYSTEM_SOLD_CUSTOMER_ID%TYPE
521 INDEX BY BINARY_INTEGER;
522 NUM_COL3_ORIG_LIST ORIG_SOLD_CUST_ID_LIST_TYPE;
523 NUM_COL3_NEW_LIST ORIG_SOLD_CUST_ID_LIST_TYPE;
524
525 TYPE ORIG_BILL_ADD_ID_LIST_TYPE IS TABLE OF
526 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_ADDRESS_ID%TYPE
527 INDEX BY BINARY_INTEGER;
528 NUM_COL4_ORIG_LIST ORIG_BILL_ADD_ID_LIST_TYPE;
529 NUM_COL4_NEW_LIST ORIG_BILL_ADD_ID_LIST_TYPE;
530
531 TYPE ORIG_SHIP_ADD_ID_LIST_TYPE IS TABLE OF
532 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_ADDRESS_ID%TYPE
533 INDEX BY BINARY_INTEGER;
534 NUM_COL5_ORIG_LIST ORIG_SHIP_ADD_ID_LIST_TYPE;
535 NUM_COL5_NEW_LIST ORIG_SHIP_ADD_ID_LIST_TYPE;
536
537 TYPE ORIG_BILL_CUST_REF_LIST_TYPE IS TABLE OF
538 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_CUSTOMER_REF%TYPE
539 INDEX BY BINARY_INTEGER;
540 VCHAR_COL1_ORIG_LIST ORIG_BILL_CUST_REF_LIST_TYPE;
541 VCHAR_COL1_NEW_LIST ORIG_BILL_CUST_REF_LIST_TYPE;
542
543 TYPE ORIG_SHIP_CUST_REF_LIST_TYPE IS TABLE OF
544 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_CUSTOMER_REF%TYPE
545 INDEX BY BINARY_INTEGER;
546 VCHAR_COL2_ORIG_LIST ORIG_SHIP_CUST_REF_LIST_TYPE;
547 VCHAR_COL2_NEW_LIST ORIG_SHIP_CUST_REF_LIST_TYPE;
548
549 TYPE ORIG_SOLD_CUST_REF_LIST_TYPE IS TABLE OF
550 RA_INTERFACE_LINES.ORIG_SYSTEM_SOLD_CUSTOMER_REF%TYPE
551 INDEX BY BINARY_INTEGER;
552 VCHAR_COL3_ORIG_LIST ORIG_SOLD_CUST_REF_LIST_TYPE;
553 VCHAR_COL3_NEW_LIST ORIG_SOLD_CUST_REF_LIST_TYPE;
554
555 TYPE ORIG_BILL_ADD_REF_LIST_TYPE IS TABLE OF
556 RA_INTERFACE_LINES.ORIG_SYSTEM_BILL_ADDRESS_REF%TYPE
557 INDEX BY BINARY_INTEGER;
558 VCHAR_COL4_ORIG_LIST ORIG_BILL_ADD_REF_LIST_TYPE;
559 VCHAR_COL4_NEW_LIST ORIG_BILL_ADD_REF_LIST_TYPE;
560
561 TYPE ORIG_SHIP_ADD_REF_LIST_TYPE IS TABLE OF
562 RA_INTERFACE_LINES.ORIG_SYSTEM_SHIP_ADDRESS_REF%TYPE
563 INDEX BY BINARY_INTEGER;
564 VCHAR_COL5_ORIG_LIST ORIG_SHIP_ADD_REF_LIST_TYPE;
565 VCHAR_COL5_NEW_LIST ORIG_SHIP_ADD_REF_LIST_TYPE;
566
567 /* Bug3500125 : Added following table type and added CUSTOMER_REF to CURSOR merged_records*/
568
569 TYPE CUSTOMER_REF_LIST_TYPE IS TABLE OF
570 RA_CUSTOMER_MERGES.CUSTOMER_REF%TYPE
571 INDEX BY BINARY_INTEGER;
572 VCHAR_COL6_ORIG_LIST CUSTOMER_REF_LIST_TYPE;
573 VCHAR_COL6_NEW_LIST CUSTOMER_REF_LIST_TYPE;
574
575 /*Additional change for ra_interface_lines under bug2447449*/
576 /* bug3667197: Modified the where clause of cursor merged_records to avoid
577 FTS on table ra_customer_merges */
578 /* bug4075234: Replaced 'exists' clause in CURSOR merged_records with 'IN' for performance
579 improvement */
580 l_profile_val VARCHAR2(30);
581
582 CURSOR merged_records IS
583 SELECT distinct CUSTOMER_MERGE_HEADER_ID
584 ,rai.ROWID
585 ,ORIG_SYSTEM_BILL_CUSTOMER_ID
586 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID
587 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID
588 ,ORIG_SYSTEM_BILL_ADDRESS_ID
589 ,ORIG_SYSTEM_SHIP_ADDRESS_ID
590 ,ORIG_SYSTEM_BILL_CUSTOMER_REF
591 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF
592 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF
593 ,ORIG_SYSTEM_BILL_ADDRESS_REF
594 ,ORIG_SYSTEM_SHIP_ADDRESS_REF
595 ,m.CUSTOMER_REF
596 from ra_interface_lines rai,
597 ra_customer_merges m
598 where nvl(rai.interface_status,'N') <> 'P' /* bug 1611619 : check interface_status */
599 and (
600 m.duplicate_id = rai.orig_system_bill_customer_id
601 or (m.duplicate_ref = rai.orig_system_bill_customer_ref)
602 or (m.duplicate_address_id = rai.orig_system_bill_address_id)
603 or (m.duplicate_id = rai.orig_system_ship_customer_id)
604 or (m.duplicate_ref = rai.orig_system_ship_customer_ref)
605 or (m.duplicate_address_id = rai.orig_system_ship_address_id)
606 or (m.duplicate_id = rai.orig_system_sold_customer_id)
607 or (m.duplicate_ref = rai.orig_system_sold_customer_ref)
608 or (rai.orig_system_bill_address_ref IN ( select
609 ra.orig_system_reference
610 from hz_cust_acct_sites ra
611 where m.duplicate_address_id = ra.cust_acct_site_id)
612 )
613 or (rai.orig_system_ship_address_ref IN (select
614 ra.orig_system_reference
615 from hz_cust_acct_sites ra
616 where m.duplicate_address_id = ra.cust_acct_site_id)
617 )
618 )
619 and m.process_flag = 'N'
620 and m.request_id = req_id
621 and m.set_number = set_num ;
622
623
624 l_last_fetch BOOLEAN := FALSE;
625 l_count NUMBER := 0;
626
627
628 BEGIN
629 IF process_mode='LOCK' THEN
630 NULL;
631 ELSE
632 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
633 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','RA_INTERFACE_LINES',FALSE);
634 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
635 /*Additional change for ra_interface_lines under bug2447449*/
636 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
637 open merged_records;
638 LOOP
639 FETCH merged_records BULK COLLECT INTO
640 MERGE_HEADER_ID_LIST
641 , PRIMARY_KEY1_LIST
642 , NUM_COL1_ORIG_LIST
643 , NUM_COL2_ORIG_LIST
644 , NUM_COL3_ORIG_LIST
645 , NUM_COL4_ORIG_LIST
646 , NUM_COL5_ORIG_LIST
647 , VCHAR_COL1_ORIG_LIST
648 , VCHAR_COL2_ORIG_LIST
649 , VCHAR_COL3_ORIG_LIST
650 , VCHAR_COL4_ORIG_LIST
651 , VCHAR_COL5_ORIG_LIST
652 , VCHAR_COL6_NEW_LIST
653 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
654 IF merged_records%NOTFOUND THEN
655 l_last_fetch := TRUE;
656 END IF;
657 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
658 exit;
659 END IF;
660 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
661
662 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
663 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
664 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL3_ORIG_LIST(I));
665 NUM_COL4_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL4_ORIG_LIST(I));
666 NUM_COL5_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL5_ORIG_LIST(I));
667
668 /* Bug3500125:Modified code to assign values to original references with new reference values*/
669 IF VCHAR_COL1_ORIG_LIST(I) IS NOT NULL THEN
670 VCHAR_COL1_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
671 ELSE
672 VCHAR_COL1_NEW_LIST(I) := NULL;
673 END IF;
674
675 IF VCHAR_COL2_ORIG_LIST(I) IS NOT NULL THEN
676 VCHAR_COL2_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
677 ELSE
678 VCHAR_COL2_NEW_LIST(I) := NULL;
679 END IF;
680
681 IF VCHAR_COL3_ORIG_LIST(I) IS NOT NULL THEN
682 VCHAR_COL3_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
683 ELSE
684 VCHAR_COL3_NEW_LIST(I) := NULL;
685 END IF;
686
687 IF VCHAR_COL4_ORIG_LIST(I) IS NOT NULL THEN
688 VCHAR_COL4_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
689 ELSE
690 VCHAR_COL4_NEW_LIST(I) := NULL;
691 END IF;
692
693 IF VCHAR_COL5_ORIG_LIST(I) IS NOT NULL THEN
694 VCHAR_COL5_NEW_LIST(I) := VCHAR_COL6_NEW_LIST(I);
695 ELSE
696 VCHAR_COL5_NEW_LIST(I) := NULL;
697 END IF;
698
699 END LOOP;
700
701 /*Additional change for ra_interface_lines under bug2447449*/
702 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
703 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
704 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
705 MERGE_LOG_ID,
706 TABLE_NAME,
707 MERGE_HEADER_ID,
708 PRIMARY_KEY1,
709 NUM_COL1_ORIG,
710 NUM_COL1_NEW,
711 NUM_COL2_ORIG,
712 NUM_COL2_NEW,
713 NUM_COL3_ORIG,
714 NUM_COL3_NEW,
715 NUM_COL4_ORIG,
716 NUM_COL4_NEW,
717 NUM_COL5_ORIG,
718 NUM_COL5_NEW,
719 VCHAR_COL1_ORIG,
720 VCHAR_COL1_NEW,
721 VCHAR_COL2_ORIG,
722 VCHAR_COL2_NEW,
723 VCHAR_COL3_ORIG,
724 VCHAR_COL3_NEW,
725 VCHAR_COL4_ORIG,
726 VCHAR_COL4_NEW,
727 VCHAR_COL5_ORIG,
728 VCHAR_COL5_NEW,
729 ACTION_FLAG,
730 REQUEST_ID,
731 CREATED_BY,
732 CREATION_DATE,
733 LAST_UPDATE_LOGIN,
734 LAST_UPDATE_DATE,
735 LAST_UPDATED_BY
736 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
737 'RA_INTERFACE_LINES',
738 MERGE_HEADER_ID_LIST(I),
739 PRIMARY_KEY1_LIST(I),
740 NUM_COL1_ORIG_LIST(I),
741 NUM_COL1_NEW_LIST(I),
742 NUM_COL2_ORIG_LIST(I),
743 NUM_COL2_NEW_LIST(I),
744 NUM_COL3_ORIG_LIST(I),
745 NUM_COL3_NEW_LIST(I),
746 NUM_COL4_ORIG_LIST(I),
747 NUM_COL4_NEW_LIST(I),
748 NUM_COL5_ORIG_LIST(I),
749 NUM_COL5_NEW_LIST(I),
750 VCHAR_COL1_ORIG_LIST(I),
751 VCHAR_COL1_NEW_LIST(I),
752 VCHAR_COL2_ORIG_LIST(I),
753 VCHAR_COL2_NEW_LIST(I),
754 VCHAR_COL3_ORIG_LIST(I),
755 VCHAR_COL3_NEW_LIST(I),
756 VCHAR_COL4_ORIG_LIST(I),
757 VCHAR_COL4_NEW_LIST(I),
758 VCHAR_COL5_ORIG_LIST(I),
759 VCHAR_COL5_NEW_LIST(I),
760 'U',
761 req_id,
762 hz_utility_pub.CREATED_BY,
763 hz_utility_pub.CREATION_DATE,
764 hz_utility_pub.LAST_UPDATE_LOGIN,
765 hz_utility_pub.LAST_UPDATE_DATE,
766 hz_utility_pub.LAST_UPDATED_BY
767 );
768
769 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
770 UPDATE RA_INTERFACE_LINES yt SET
771 ORIG_SYSTEM_BILL_CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
772 ,ORIG_SYSTEM_SHIP_CUSTOMER_ID=NUM_COL2_NEW_LIST(I)
773 ,ORIG_SYSTEM_SOLD_CUSTOMER_ID=NUM_COL3_NEW_LIST(I)
774 ,ORIG_SYSTEM_BILL_ADDRESS_ID=NUM_COL4_NEW_LIST(I)
775 ,ORIG_SYSTEM_SHIP_ADDRESS_ID=NUM_COL5_NEW_LIST(I)
776 ,ORIG_SYSTEM_BILL_CUSTOMER_REF=VCHAR_COL1_NEW_LIST(I)
777 ,ORIG_SYSTEM_SHIP_CUSTOMER_REF=VCHAR_COL2_NEW_LIST(I)
778 ,ORIG_SYSTEM_SOLD_CUSTOMER_REF=VCHAR_COL3_NEW_LIST(I)
779 ,ORIG_SYSTEM_BILL_ADDRESS_REF=VCHAR_COL4_NEW_LIST(I)
780 ,ORIG_SYSTEM_SHIP_ADDRESS_REF=VCHAR_COL5_NEW_LIST(I)
781 , LAST_UPDATE_DATE=SYSDATE
782 , last_updated_by=arp_standard.profile.user_id
783 , last_update_login=arp_standard.profile.last_update_login
784 WHERE ROWID=PRIMARY_KEY1_LIST(I)
785 ;
786 l_count := l_count + SQL%ROWCOUNT;
787 IF l_last_fetch THEN
788 EXIT;
789 END IF;
790 END LOOP;
791
792 arp_message.set_name('AR','AR_ROWS_UPDATED');
793 arp_message.set_token('NUM_ROWS',to_char(l_count));
794 END IF;
795 EXCEPTION
796 WHEN OTHERS THEN
797 arp_message.set_line( 'RA_INT');
798 RAISE;
799 END RA_INT;
800
801 PROCEDURE ar_ard (
802 req_id NUMBER,
803 set_num NUMBER,
804 process_mode VARCHAR2) IS
805
806 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
807 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
808 INDEX BY BINARY_INTEGER;
809 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
810
811 TYPE line_id_LIST_TYPE IS TABLE OF
812 AR_DISTRIBUTIONS.line_id%TYPE
813 INDEX BY BINARY_INTEGER;
814 PRIMARY_KEY_ID_LIST line_id_LIST_TYPE;
815
816 TYPE third_party_id_LIST_TYPE IS TABLE OF
817 AR_DISTRIBUTIONS.third_party_id%TYPE
818 INDEX BY BINARY_INTEGER;
819 NUM_COL1_ORIG_LIST third_party_id_LIST_TYPE;
820 NUM_COL1_NEW_LIST third_party_id_LIST_TYPE;
821
822 TYPE third_party_sub_id_LIST_TYPE IS TABLE OF
823 AR_DISTRIBUTIONS.third_party_sub_id%TYPE
824 INDEX BY BINARY_INTEGER;
825 NUM_COL2_ORIG_LIST third_party_sub_id_LIST_TYPE;
826 NUM_COL2_NEW_LIST third_party_sub_id_LIST_TYPE;
827
828 l_profile_val VARCHAR2(30);
829 CURSOR merged_records IS
830 SELECT distinct CUSTOMER_MERGE_HEADER_ID
831 ,line_id
832 ,third_party_id
833 ,third_party_sub_id
834 FROM AR_DISTRIBUTIONS yt, ra_customer_merges m
835 WHERE ( (yt.third_party_id = m.DUPLICATE_ID AND
836 nvl(yt.third_party_sub_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
837 AND m.process_flag = 'N'
838 AND m.request_id = req_id
839 AND m.set_number = set_num;
840 l_last_fetch BOOLEAN := FALSE;
841 l_count NUMBER := 0;
842 BEGIN
843 IF process_mode='LOCK' THEN
844 NULL;
845 ELSE
846 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
847 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_DISTRIBUTIONS',FALSE);
848 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
849 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
850
851 open merged_records;
852 LOOP
853 FETCH merged_records BULK COLLECT INTO
854 MERGE_HEADER_ID_LIST
855 , PRIMARY_KEY_ID_LIST
856 , NUM_COL1_ORIG_LIST
857 , NUM_COL2_ORIG_LIST
858 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
859 IF merged_records%NOTFOUND THEN
860 l_last_fetch := TRUE;
861 END IF;
862 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
863 exit;
864 END IF;
865 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
866 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
867 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
868 END LOOP;
869 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
870 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
871 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
872 MERGE_LOG_ID,
873 TABLE_NAME,
874 MERGE_HEADER_ID,
875 PRIMARY_KEY_ID,
876 NUM_COL1_ORIG,
877 NUM_COL1_NEW,
878 NUM_COL2_ORIG,
879 NUM_COL2_NEW,
880 ACTION_FLAG,
881 REQUEST_ID,
882 CREATED_BY,
883 CREATION_DATE,
884 LAST_UPDATE_LOGIN,
885 LAST_UPDATE_DATE,
886 LAST_UPDATED_BY
887 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
888 'AR_DISTRIBUTIONS',
889 MERGE_HEADER_ID_LIST(I),
890 PRIMARY_KEY_ID_LIST(I),
891 NUM_COL1_ORIG_LIST(I),
892 NUM_COL1_NEW_LIST(I),
893 NUM_COL2_ORIG_LIST(I),
894 NUM_COL2_NEW_LIST(I),
895 'U',
896 req_id,
897 hz_utility_pub.CREATED_BY,
898 hz_utility_pub.CREATION_DATE,
899 hz_utility_pub.LAST_UPDATE_LOGIN,
900 hz_utility_pub.LAST_UPDATE_DATE,
901 hz_utility_pub.LAST_UPDATED_BY
902 );
903
904 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
905 UPDATE AR_DISTRIBUTIONS yt SET
906 third_party_id=NUM_COL1_NEW_LIST(I)
907 ,third_party_sub_id=NUM_COL2_NEW_LIST(I)
908 , LAST_UPDATE_DATE=SYSDATE
909 , last_updated_by=arp_standard.profile.user_id
910 , last_update_login=arp_standard.profile.last_update_login
911 WHERE line_id=PRIMARY_KEY_ID_LIST(I)
912 ;
913 l_count := l_count + SQL%ROWCOUNT;
914 IF l_last_fetch THEN
915 EXIT;
916 END IF;
917 END LOOP;
918
919 arp_message.set_name('AR','AR_ROWS_UPDATED');
920 arp_message.set_token('NUM_ROWS',to_char(l_count));
921 END IF;
922 EXCEPTION
923 WHEN OTHERS THEN
924 arp_message.set_line( 'ar_ard');
925 RAISE;
926 END ar_ard;
927
928
929 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
930 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
931 BEGIN
932
933 arp_message.set_line( 'ARP_CMERGE_ARTRX.MERGE()+' );
934
935 ar_cr( req_id, set_num, process_mode );
936 ar_ps( req_id, set_num, process_mode );
937 ra_ct( req_id, set_num, process_mode );
938 ra_int(req_id, set_num, process_mode );
939 ar_ard(req_id, set_num, process_mode );
940
941 arp_message.set_line( 'ARP_CMERGE_ARTRX.MERGE()-' );
942
943 EXCEPTION
944 when others then
945 raise;
946
947 END merge;
948
949 END ARP_CMERGE_ARTRX;