[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_ACCOUNT_MERGE
Source
1 Package BODY AR_CMGT_ACCOUNT_MERGE AS
2 /* $Header: ARCMGAMB.pls 120.4.12000000.3 2007/07/23 10:26:38 cuddagir ship $ */
3 /*-------------------------------------------------------------
4 |
5 | PROCEDURE
6 | CASE_FOLDER_ACCOUNT_MERGE
7 | DESCRIPTION :
8 | Account merge procedure for the table, AR_CMGT_CASE_FOLDERS
9 |
10 |--------------------------------------------------------------*/
11
12 PROCEDURE CASE_FOLDER_ACCOUNT_MERGE (
13 req_id NUMBER,
14 set_num NUMBER,
15 process_mode VARCHAR2) IS
16
17 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
18 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
19 INDEX BY BINARY_INTEGER;
20 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
21
22 TYPE CASE_FOLDER_ID_LIST_TYPE IS TABLE OF
23 AR_CMGT_CASE_FOLDERS.CASE_FOLDER_ID%TYPE
24 INDEX BY BINARY_INTEGER;
25 PRIMARY_KEY_ID_LIST CASE_FOLDER_ID_LIST_TYPE;
26
27 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
28 AR_CMGT_CASE_FOLDERS.CUST_ACCOUNT_ID%TYPE
29 INDEX BY BINARY_INTEGER;
30 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
31 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
32
33 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
34 AR_CMGT_CASE_FOLDERS.SITE_USE_ID%TYPE
35 INDEX BY BINARY_INTEGER;
36 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
37 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
38
39 l_profile_val VARCHAR2(30);
40 CURSOR merged_records IS
41 SELECT distinct CUSTOMER_MERGE_HEADER_ID
42 ,CASE_FOLDER_ID
43 ,CUST_ACCOUNT_ID
44 ,SITE_USE_ID
45 FROM ar_cmgt_case_folders yt,
46 ra_customer_merges m
47 WHERE yt.cust_account_id = m.duplicate_id
48 AND DECODE( yt.site_use_id , -99, m.duplicate_site_id,
49 yt.site_use_id ) = m.duplicate_site_id
50 AND m.process_flag = 'N'
51 AND m.request_id = req_id
52 AND m.set_number = set_num;
53
54 l_last_fetch BOOLEAN := FALSE;
55 l_count NUMBER;
56 BEGIN
57 IF process_mode='LOCK' THEN
58 NULL;
59 ELSE
60 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
61 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CMGT_CASE_FOLDERS',FALSE);
62 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
63 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
64
65 open merged_records;
66 LOOP
67 FETCH merged_records BULK COLLECT INTO
68 MERGE_HEADER_ID_LIST
69 , PRIMARY_KEY_ID_LIST
70 , NUM_COL1_ORIG_LIST
71 , NUM_COL2_ORIG_LIST
72 limit 1000;
73 IF merged_records%NOTFOUND THEN
74 l_last_fetch := TRUE;
75 END IF;
76 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
77 exit;
78 END IF;
79 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
80 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
81 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
82 END LOOP;
83 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
84 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
85 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
86 MERGE_LOG_ID,
87 TABLE_NAME,
88 MERGE_HEADER_ID,
89 PRIMARY_KEY_ID,
90 NUM_COL1_ORIG,
91 NUM_COL1_NEW,
92 NUM_COL2_ORIG,
93 NUM_COL2_NEW,
94 ACTION_FLAG,
95 REQUEST_ID,
96 CREATED_BY,
97 CREATION_DATE,
98 LAST_UPDATE_LOGIN,
99 LAST_UPDATE_DATE,
100 LAST_UPDATED_BY
101 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
102 'AR_CMGT_CASE_FOLDERS',
103 MERGE_HEADER_ID_LIST(I),
104 PRIMARY_KEY_ID_LIST(I),
105 NUM_COL1_ORIG_LIST(I),
106 NUM_COL1_NEW_LIST(I),
107 NUM_COL2_ORIG_LIST(I),
108 NUM_COL2_NEW_LIST(I),
109 'U',
110 req_id,
111 hz_utility_pub.CREATED_BY,
112 hz_utility_pub.CREATION_DATE,
113 hz_utility_pub.LAST_UPDATE_LOGIN,
114 hz_utility_pub.LAST_UPDATE_DATE,
115 hz_utility_pub.LAST_UPDATED_BY
116 );
117
118 END IF;
119 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
120 DELETE FROM ar_cmgt_cf_dtls
121 WHERE case_folder_id in (
122 SELECT case_folder_id
123 FROM ar_cmgt_case_folders
124 WHERE case_folder_id = PRIMARY_KEY_ID_LIST(I)
125 AND type = 'DATA');
126
127 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
128 DELETE FROM ar_cmgt_case_folders
129 WHERE case_folder_id = PRIMARY_KEY_ID_LIST(I)
130 AND type = 'DATA';
131
132 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
133 UPDATE AR_CMGT_CASE_FOLDERS yt SET
134 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
135 ,SITE_USE_ID=DECODE( SITE_USE_ID, -99,-99,NUM_COL2_NEW_LIST(I))
136 , LAST_UPDATE_DATE=SYSDATE
137 , last_updated_by=arp_standard.profile.user_id
138 , last_update_login=arp_standard.profile.last_update_login
139 WHERE CASE_FOLDER_ID=PRIMARY_KEY_ID_LIST(I)
140 ;
141 l_count := l_count + SQL%ROWCOUNT;
142 IF l_last_fetch THEN
143 EXIT;
144 END IF;
145 END LOOP;
146
147 arp_message.set_name('AR','AR_ROWS_UPDATED');
148 arp_message.set_token('NUM_ROWS',to_char(l_count));
149 END IF;
150 EXCEPTION
151 WHEN OTHERS THEN
152 arp_message.set_line( 'CASE_FOLDER_ACCOUNT_MERGE');
153 RAISE;
154 END CASE_FOLDER_ACCOUNT_MERGE;
155
156 /*-------------------------------------------------------------
157 |
158 | PROCEDURE
159 | CREDIT_REQUEST_ACCOUNT_MERGE
160 | DESCRIPTION :
161 | Account merge procedure for the table, AR_CMGT_CREDIT_REQUESTS
162
163 |--------------------------------------------------------------*/
164
165 PROCEDURE CREDIT_REQUEST_ACCOUNT_MERGE (
166 req_id NUMBER,
167 set_num NUMBER,
168 process_mode VARCHAR2) IS
169
170 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
171 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
172 INDEX BY BINARY_INTEGER;
173 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
174
175 TYPE CREDIT_REQUEST_ID_LIST_TYPE IS TABLE OF
176 AR_CMGT_CREDIT_REQUESTS.CREDIT_REQUEST_ID%TYPE
177 INDEX BY BINARY_INTEGER;
178 PRIMARY_KEY_ID_LIST CREDIT_REQUEST_ID_LIST_TYPE;
179
180 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
181 AR_CMGT_CREDIT_REQUESTS.CUST_ACCOUNT_ID%TYPE
182 INDEX BY BINARY_INTEGER;
183 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
184 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
185
186 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
187 AR_CMGT_CREDIT_REQUESTS.SITE_USE_ID%TYPE
188 INDEX BY BINARY_INTEGER;
189 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
190 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
191
192 l_profile_val VARCHAR2(30);
193 CURSOR merged_records IS
194 SELECT distinct CUSTOMER_MERGE_HEADER_ID
195 ,CREDIT_REQUEST_ID
196 ,CUST_ACCOUNT_ID
197 ,SITE_USE_ID
198 FROM AR_CMGT_CREDIT_REQUESTS yt,
199 ra_customer_merges m
200 WHERE yt.cust_account_id = m.DUPLICATE_ID
201 AND DECODE( yt.site_use_id , -99, m.duplicate_site_id,
202 yt.site_use_id ) = m.duplicate_site_id
203 AND m.process_flag = 'N'
204 AND m.request_id = req_id
205 AND m.set_number = set_num;
206 l_last_fetch BOOLEAN := FALSE;
207 l_count NUMBER;
208 BEGIN
209 IF process_mode='LOCK' THEN
210 NULL;
211 ELSE
212 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
213 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CMGT_CREDIT_REQUESTS',FALSE);
214 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
215 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
216
217 open merged_records;
218 LOOP
219 FETCH merged_records BULK COLLECT INTO
220 MERGE_HEADER_ID_LIST
221 , PRIMARY_KEY_ID_LIST
222 , NUM_COL1_ORIG_LIST
223 , NUM_COL2_ORIG_LIST
224 limit 1000;
225 IF merged_records%NOTFOUND THEN
226 l_last_fetch := TRUE;
227 END IF;
228 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
229 exit;
230 END IF;
231 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
232 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
233 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
234 END LOOP;
235 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
236 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
237 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
238 MERGE_LOG_ID,
239 TABLE_NAME,
240 MERGE_HEADER_ID,
241 PRIMARY_KEY_ID,
242 NUM_COL1_ORIG,
243 NUM_COL1_NEW,
244 NUM_COL2_ORIG,
245 NUM_COL2_NEW,
246 ACTION_FLAG,
247 REQUEST_ID,
248 CREATED_BY,
249 CREATION_DATE,
250 LAST_UPDATE_LOGIN,
251 LAST_UPDATE_DATE,
252 LAST_UPDATED_BY
253 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
254 'AR_CMGT_CREDIT_REQUESTS',
255 MERGE_HEADER_ID_LIST(I),
256 PRIMARY_KEY_ID_LIST(I),
257 NUM_COL1_ORIG_LIST(I),
258 NUM_COL1_NEW_LIST(I),
259 NUM_COL2_ORIG_LIST(I),
260 NUM_COL2_NEW_LIST(I),
261 'U',
262 req_id,
263 hz_utility_pub.CREATED_BY,
264 hz_utility_pub.CREATION_DATE,
265 hz_utility_pub.LAST_UPDATE_LOGIN,
266 hz_utility_pub.LAST_UPDATE_DATE,
267 hz_utility_pub.LAST_UPDATED_BY
268 );
269
270 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
271 UPDATE AR_CMGT_CREDIT_REQUESTS yt SET
272 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
273 ,SITE_USE_ID=DECODE(SITE_USE_ID,-99,-99, NUM_COL2_NEW_LIST(I))
274 , LAST_UPDATE_DATE=SYSDATE
275 , last_updated_by=arp_standard.profile.user_id
276 , last_update_login=arp_standard.profile.last_update_login
277 WHERE CREDIT_REQUEST_ID=PRIMARY_KEY_ID_LIST(I)
278 ;
279 l_count := l_count + SQL%ROWCOUNT;
280 IF l_last_fetch THEN
281 EXIT;
282 END IF;
283 END LOOP;
284
285 arp_message.set_name('AR','AR_ROWS_UPDATED');
286 arp_message.set_token('NUM_ROWS',to_char(l_count));
287 END IF;
288 EXCEPTION
289 WHEN OTHERS THEN
290 arp_message.set_line( 'CREDIT_REQUEST_ACCOUNT_MERGE');
291 RAISE;
292 END CREDIT_REQUEST_ACCOUNT_MERGE;
293
294 /*-------------------------------------------------------------
295 |
296 | PROCEDURE
297 | TRX_BAL_SUMMARY_ACCOUNT_MERGE
298 | DESCRIPTION :
299 | Account merge procedure for the table, AR_TRX_BAL_SUMMARY
300 |
301 |
302 |--------------------------------------------------------------*/
303
304 PROCEDURE TRX_BAL_SUMMARY_ACCOUNT_MERGE (
305 req_id NUMBER,
306 set_num NUMBER,
307 process_mode VARCHAR2) IS
308
309 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
310 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
311 INDEX BY BINARY_INTEGER;
312 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
313
314 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
315 AR_TRX_BAL_SUMMARY.CUST_ACCOUNT_ID%TYPE
316 INDEX BY BINARY_INTEGER;
317 PRIMARY_KEY1_LIST CUST_ACCOUNT_ID_LIST_TYPE;
318
319 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
320 AR_TRX_BAL_SUMMARY.SITE_USE_ID%TYPE
321 INDEX BY BINARY_INTEGER;
322 PRIMARY_KEY2_LIST SITE_USE_ID_LIST_TYPE;
323
324 TYPE CURRENCY_LIST_TYPE IS TABLE OF
325 AR_TRX_BAL_SUMMARY.CURRENCY%TYPE
326 INDEX BY BINARY_INTEGER;
327 PRIMARY_KEY3_LIST CURRENCY_LIST_TYPE;
328
329 TYPE ORG_ID_LIST_TYPE IS TABLE OF
330 AR_TRX_BAL_SUMMARY.ORG_ID%TYPE
331 INDEX BY BINARY_INTEGER;
332 PRIMARY_KEY4_LIST ORG_ID_LIST_TYPE;
333
334 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
335 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
336
337
338 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
339 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
340
341 TYPE DATE_LIST_TYPE IS TABLE OF DATE
342 INDEX BY BINARY_INTEGER;
343
344 TYPE PAYMENT_NUMBER_LIST_TYPE IS TABLE OF
345 AR_TRX_BAL_SUMMARY.LAST_PAYMENT_NUMBER%type
346 INDEX BY BINARY_INTEGER;
347
348 TYPE NUMBER_LIST_TYPE IS TABLE OF NUMBER
349 INDEX BY BINARY_INTEGER;
350
351 DEL_BEST_CURRENT_RECEIVABLES NUMBER_LIST_TYPE;
352 DEL_TOTAL_DSO_DAYS_CREDIT NUMBER_LIST_TYPE;
353 DEL_OP_INVOICES_VALUE NUMBER_LIST_TYPE;
354 DEL_OP_INVOICES_COUNT NUMBER_LIST_TYPE;
355 DEL_OP_DEBIT_MEMOS_VALUE NUMBER_LIST_TYPE;
356 DEL_OP_DEBIT_MEMOS_COUNT NUMBER_LIST_TYPE;
357 DEL_OP_DEPOSITS_VALUE NUMBER_LIST_TYPE;
358 DEL_OP_DEPOSITS_COUNT NUMBER_LIST_TYPE;
359 DEL_OP_BILLS_RECEIVABLES_VALUE NUMBER_LIST_TYPE;
360 DEL_OP_BILLS_RECEIVABLES_COUNT NUMBER_LIST_TYPE;
361 DEL_OP_CHARGEBACK_VALUE NUMBER_LIST_TYPE;
362 DEL_OP_CHARGEBACK_COUNT NUMBER_LIST_TYPE;
363 DEL_OP_CREDIT_MEMOS_VALUE NUMBER_LIST_TYPE;
364 DEL_OP_CREDIT_MEMOS_COUNT NUMBER_LIST_TYPE;
365 DEL_UNRESOLVED_CASH_VALUE NUMBER_LIST_TYPE;
366 DEL_UNRESOLVED_CASH_COUNT NUMBER_LIST_TYPE;
367 DEL_RECEIPTS_AT_RISK_VALUE NUMBER_LIST_TYPE;
368 DEL_INV_AMT_IN_DISPUTE NUMBER_LIST_TYPE;
369 DEL_DISPUTED_INV_COUNT NUMBER_LIST_TYPE;
370 DEL_PENDING_ADJ_VALUE NUMBER_LIST_TYPE;
371 DEL_LAST_DUNNING_DATE DATE_LIST_TYPE;
372 DEL_DUNNING_COUNT NUMBER_LIST_TYPE;
373 DEL_PAST_DUE_INV_VALUE NUMBER_LIST_TYPE;
374 DEL_PAST_DUE_INV_INST_COUNT NUMBER_LIST_TYPE;
375 DEL_LAST_PAYMENT_AMOUNT NUMBER_LIST_TYPE;
376 DEL_LAST_PAYMENT_DATE DATE_LIST_TYPE;
377 DEL_LAST_PAYMENT_NUMBER PAYMENT_NUMBER_LIST_TYPE;
378
379
380 l_profile_val VARCHAR2(30);
381 /* bug4727614: Modified cursor to prevent ORA errors */
382 CURSOR merged_records IS
383 SELECT distinct CUSTOMER_MERGE_HEADER_ID
384 ,CUST_ACCOUNT_ID
385 ,SITE_USE_ID
386 ,CURRENCY
387 ,yt.ORG_ID
388 ,CUST_ACCOUNT_ID
392 ,OP_INVOICES_VALUE
389 ,SITE_USE_ID
390 ,BEST_CURRENT_RECEIVABLES
391 ,TOTAL_DSO_DAYS_CREDIT
393 ,OP_INVOICES_COUNT
394 ,OP_DEBIT_MEMOS_VALUE
395 ,OP_DEBIT_MEMOS_COUNT
396 ,OP_DEPOSITS_VALUE
397 ,OP_DEPOSITS_COUNT
398 ,OP_BILLS_RECEIVABLES_VALUE
399 ,OP_BILLS_RECEIVABLES_COUNT
400 ,OP_CHARGEBACK_VALUE
401 ,OP_CHARGEBACK_COUNT
402 ,OP_CREDIT_MEMOS_VALUE
403 ,OP_CREDIT_MEMOS_COUNT
404 ,UNRESOLVED_CASH_VALUE
405 ,UNRESOLVED_CASH_COUNT
406 ,RECEIPTS_AT_RISK_VALUE
407 ,INV_AMT_IN_DISPUTE
408 ,DISPUTED_INV_COUNT
409 ,PENDING_ADJ_VALUE
410 ,LAST_DUNNING_DATE
411 ,DUNNING_COUNT
412 ,PAST_DUE_INV_VALUE
413 ,PAST_DUE_INV_INST_COUNT
414 ,LAST_PAYMENT_AMOUNT
415 ,LAST_PAYMENT_DATE
416 ,LAST_PAYMENT_NUMBER
417 FROM AR_TRX_BAL_SUMMARY yt, ra_customer_merges m
418 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
419 AND DECODE(yt.SITE_USE_ID , -99, m.DUPLICATE_SITE_ID,
420 yt.SITE_USE_ID) = m.DUPLICATE_SITE_ID
421 AND m.process_flag = 'N'
422 AND m.request_id = req_id
423 AND m.set_number = set_num
424 AND m.DUPLICATE_ID <> m.CUSTOMER_ID
425 UNION
426 SELECT distinct CUSTOMER_MERGE_HEADER_ID
427 ,CUST_ACCOUNT_ID
428 ,SITE_USE_ID
429 ,CURRENCY
430 ,yt.ORG_ID
431 ,CUST_ACCOUNT_ID
432 ,SITE_USE_ID
433 ,BEST_CURRENT_RECEIVABLES
434 ,TOTAL_DSO_DAYS_CREDIT
435 ,OP_INVOICES_VALUE
436 ,OP_INVOICES_COUNT
437 ,OP_DEBIT_MEMOS_VALUE
438 ,OP_DEBIT_MEMOS_COUNT
439 ,OP_DEPOSITS_VALUE
440 ,OP_DEPOSITS_COUNT
441 ,OP_BILLS_RECEIVABLES_VALUE
442 ,OP_BILLS_RECEIVABLES_COUNT
443 ,OP_CHARGEBACK_VALUE
444 ,OP_CHARGEBACK_COUNT
445 ,OP_CREDIT_MEMOS_VALUE
446 ,OP_CREDIT_MEMOS_COUNT
447 ,UNRESOLVED_CASH_VALUE
448 ,UNRESOLVED_CASH_COUNT
449 ,RECEIPTS_AT_RISK_VALUE
450 ,INV_AMT_IN_DISPUTE
451 ,DISPUTED_INV_COUNT
452 ,PENDING_ADJ_VALUE
453 ,LAST_DUNNING_DATE
454 ,DUNNING_COUNT
455 ,PAST_DUE_INV_VALUE
456 ,PAST_DUE_INV_INST_COUNT
457 ,LAST_PAYMENT_AMOUNT
458 ,LAST_PAYMENT_DATE
459 ,LAST_PAYMENT_NUMBER
460 FROM AR_TRX_BAL_SUMMARY yt, ra_customer_merges m
461 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
462 AND yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
463 AND m.process_flag = 'N'
464 AND m.request_id = req_id
465 AND m.set_number = set_num
466 AND m.DUPLICATE_ID = m.CUSTOMER_ID;
467 l_last_fetch BOOLEAN := FALSE;
468 l_count NUMBER;
469 BEGIN
470 IF process_mode='LOCK' THEN
471 NULL;
472 ELSE
473 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
474 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_TRX_BAL_SUMMARY',FALSE);
475 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
476 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
477
478 open merged_records;
479 LOOP
480 FETCH merged_records BULK COLLECT INTO
481 MERGE_HEADER_ID_LIST
482 , PRIMARY_KEY1_LIST
483 , PRIMARY_KEY2_LIST
484 , PRIMARY_KEY3_LIST
485 , PRIMARY_KEY4_LIST
486 , NUM_COL1_ORIG_LIST
487 , NUM_COL2_ORIG_LIST
488 ,DEL_BEST_CURRENT_RECEIVABLES
489 ,DEL_TOTAL_DSO_DAYS_CREDIT
490 ,DEL_OP_INVOICES_VALUE
491 ,DEL_OP_INVOICES_COUNT
492 ,DEL_OP_DEBIT_MEMOS_VALUE
493 ,DEL_OP_DEBIT_MEMOS_COUNT
494 ,DEL_OP_DEPOSITS_VALUE
495 ,DEL_OP_DEPOSITS_COUNT
496 ,DEL_OP_BILLS_RECEIVABLES_VALUE
497 ,DEL_OP_BILLS_RECEIVABLES_COUNT
498 ,DEL_OP_CHARGEBACK_VALUE
499 ,DEL_OP_CHARGEBACK_COUNT
500 ,DEL_OP_CREDIT_MEMOS_VALUE
501 ,DEL_OP_CREDIT_MEMOS_COUNT
502 ,DEL_UNRESOLVED_CASH_VALUE
503 ,DEL_UNRESOLVED_CASH_COUNT
504 ,DEL_RECEIPTS_AT_RISK_VALUE
505 ,DEL_INV_AMT_IN_DISPUTE
506 ,DEL_DISPUTED_INV_COUNT
507 ,DEL_PENDING_ADJ_VALUE
508 ,DEL_LAST_DUNNING_DATE
509 ,DEL_DUNNING_COUNT
510 ,DEL_PAST_DUE_INV_VALUE
511 ,DEL_PAST_DUE_INV_INST_COUNT
512 ,DEL_LAST_PAYMENT_AMOUNT
513 ,DEL_LAST_PAYMENT_DATE
514 ,DEL_LAST_PAYMENT_NUMBER
515 limit 1000;
516 IF merged_records%NOTFOUND THEN
517 l_last_fetch := TRUE;
518 END IF;
519 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
520 exit;
521 END IF;
522 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
523 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
524 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
525 arp_message.set_line( 'Request Id ='||req_id);
526 arp_message.set_line( 'NUM_COL1_NEW_LIST(I)='||NUM_COL1_NEW_LIST(I));
527 arp_message.set_line( 'NUM_COL2_NEW_LIST(I)='||NUM_COL2_NEW_LIST(I));
528 END LOOP;
529 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
530 arp_message.set_line( 'Inserting into HZ_CUSTOMER_MERGE_LOG');
534 TABLE_NAME,
531 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
532 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
533 MERGE_LOG_ID,
535 MERGE_HEADER_ID,
536 PRIMARY_KEY1,
537 PRIMARY_KEY2,
538 PRIMARY_KEY3,
539 PRIMARY_KEY4,
540 NUM_COL1_ORIG,
541 NUM_COL1_NEW,
542 NUM_COL2_ORIG,
543 NUM_COL2_NEW,
544 ACTION_FLAG,
545 REQUEST_ID,
546 DEL_COL1,
547 DEL_COL2,
548 DEL_COL3,
549 DEL_COL4,
550 DEL_COL5,
551 DEL_COL6,
552 DEL_COL7,
553 DEL_COL8,
554 DEL_COL9,
555 DEL_COL10,
556 DEL_COL11,
557 DEL_COL12,
558 DEL_COL13,
559 DEL_COL14,
560 DEL_COL15,
561 DEL_COL16,
562 DEL_COL17,
563 DEL_COL18,
564 DEL_COL19,
565 DEL_COL20,
566 DEL_COL21,
567 DEL_COL22,
568 DEL_COL23,
569 DEL_COL24,
570 DEL_COL25,
571 DEL_COL26,
572 DEL_COL27,
573 CREATED_BY,
574 CREATION_DATE,
575 LAST_UPDATE_LOGIN,
576 LAST_UPDATE_DATE,
577 LAST_UPDATED_BY
578 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
579 'AR_TRX_BAL_SUMMARY',
580 MERGE_HEADER_ID_LIST(I),
581 PRIMARY_KEY1_LIST(I),
582 PRIMARY_KEY2_LIST(I),
583 PRIMARY_KEY3_LIST(I),
584 PRIMARY_KEY4_LIST(I),
585 NUM_COL1_ORIG_LIST(I),
586 NUM_COL1_NEW_LIST(I),
587 NUM_COL2_ORIG_LIST(I),
588 NUM_COL2_NEW_LIST(I),
589 'D',
590 req_id,
591 DEL_BEST_CURRENT_RECEIVABLES(I)
592 ,DEL_TOTAL_DSO_DAYS_CREDIT(I)
593 ,DEL_OP_INVOICES_VALUE(I)
594 ,DEL_OP_INVOICES_COUNT(I)
595 ,DEL_OP_DEBIT_MEMOS_VALUE(I)
596 ,DEL_OP_DEBIT_MEMOS_COUNT(I)
597 ,DEL_OP_DEPOSITS_VALUE(I)
598 ,DEL_OP_DEPOSITS_COUNT(I)
599 ,DEL_OP_BILLS_RECEIVABLES_VALUE(I)
600 ,DEL_OP_BILLS_RECEIVABLES_COUNT(I)
601 ,DEL_OP_CHARGEBACK_VALUE(I)
602 ,DEL_OP_CHARGEBACK_COUNT(I)
603 ,DEL_OP_CREDIT_MEMOS_VALUE(I)
604 ,DEL_OP_CREDIT_MEMOS_COUNT(I)
605 ,DEL_UNRESOLVED_CASH_VALUE(I)
606 ,DEL_UNRESOLVED_CASH_COUNT(I)
607 ,DEL_RECEIPTS_AT_RISK_VALUE(I)
608 ,DEL_INV_AMT_IN_DISPUTE(I)
609 ,DEL_DISPUTED_INV_COUNT(I)
610 ,DEL_PENDING_ADJ_VALUE(I)
611 ,DEL_LAST_DUNNING_DATE(I)
612 ,DEL_DUNNING_COUNT(I)
613 ,DEL_PAST_DUE_INV_VALUE(I)
614 ,DEL_PAST_DUE_INV_INST_COUNT(I)
615 ,DEL_LAST_PAYMENT_AMOUNT(I)
616 ,DEL_LAST_PAYMENT_DATE(I)
617 ,DEL_LAST_PAYMENT_NUMBER(I),
618 hz_utility_pub.CREATED_BY,
619 hz_utility_pub.CREATION_DATE,
620 hz_utility_pub.LAST_UPDATE_LOGIN,
621 hz_utility_pub.LAST_UPDATE_DATE,
622 hz_utility_pub.LAST_UPDATED_BY
623 );
624
625 arp_message.set_line( 'after Insert into HZ_CUSTOMER_MERGE_LOG');
626 END IF;
627 arp_message.set_line( 'before UPDATE AR_TRX_BAL_SUMMARY ');
628 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
629 UPDATE AR_TRX_BAL_SUMMARY yt SET
630 (BEST_CURRENT_RECEIVABLES
631 ,TOTAL_DSO_DAYS_CREDIT
632 ,OP_INVOICES_VALUE
633 ,OP_INVOICES_COUNT
634 ,OP_DEBIT_MEMOS_VALUE
635 ,OP_DEBIT_MEMOS_COUNT
636 ,OP_DEPOSITS_VALUE
637 ,OP_DEPOSITS_COUNT
638 ,OP_BILLS_RECEIVABLES_VALUE
639 ,OP_BILLS_RECEIVABLES_COUNT
640 ,OP_CHARGEBACK_VALUE
641 ,OP_CHARGEBACK_COUNT
642 ,OP_CREDIT_MEMOS_VALUE
643 ,OP_CREDIT_MEMOS_COUNT
644 ,UNRESOLVED_CASH_VALUE
645 ,UNRESOLVED_CASH_COUNT
646 ,RECEIPTS_AT_RISK_VALUE
647 ,INV_AMT_IN_DISPUTE
648 ,DISPUTED_INV_COUNT
649 ,PENDING_ADJ_VALUE
650 ,PAST_DUE_INV_VALUE
651 ,PAST_DUE_INV_INST_COUNT
652 ,LAST_PAYMENT_AMOUNT
653 ,LAST_PAYMENT_DATE
654 ,LAST_PAYMENT_NUMBER
655 ,LAST_UPDATE_DATE
656 ,LAST_UPDATED_BY
657 ,LAST_UPDATE_LOGIN
658 ) =
659 ( SELECT DECODE( yt.BEST_CURRENT_RECEIVABLES, null,
660 DECODE(yt1.BEST_CURRENT_RECEIVABLES, null, null,
661 nvl(yt.BEST_CURRENT_RECEIVABLES,0) +
662 nvl(yt1.BEST_CURRENT_RECEIVABLES,0)),
663 nvl(yt.BEST_CURRENT_RECEIVABLES,0) +
664 nvl(yt1.BEST_CURRENT_RECEIVABLES,0)) ,
665 DECODE( yt.TOTAL_DSO_DAYS_CREDIT, null,
666 DECODE(yt1.TOTAL_DSO_DAYS_CREDIT, null, null,
667 nvl(yt.TOTAL_DSO_DAYS_CREDIT,0) +
668 nvl(yt1.TOTAL_DSO_DAYS_CREDIT,0)),
669 nvl(yt.TOTAL_DSO_DAYS_CREDIT,0) +
670 nvl(yt1.TOTAL_DSO_DAYS_CREDIT,0)) ,
671 DECODE( yt.OP_INVOICES_VALUE, null,
672 DECODE(yt1.OP_INVOICES_VALUE, null, null,
673 nvl(yt.OP_INVOICES_VALUE,0) +
674 nvl(yt1.OP_INVOICES_VALUE,0)),
675 nvl(yt.OP_INVOICES_VALUE,0) +
676 nvl(yt1.OP_INVOICES_VALUE,0)) ,
677 DECODE( yt.OP_INVOICES_COUNT, null,
678 DECODE(yt1.OP_INVOICES_COUNT, null, null,
679 nvl(yt.OP_INVOICES_COUNT,0) +
680 nvl(yt1.OP_INVOICES_COUNT,0)),
681 nvl(yt.OP_INVOICES_COUNT,0) +
682 nvl(yt1.OP_INVOICES_COUNT,0)) ,
683 DECODE( yt.OP_DEBIT_MEMOS_VALUE, null,
684 DECODE(yt1.OP_DEBIT_MEMOS_VALUE, null, null,
685 nvl(yt.OP_DEBIT_MEMOS_VALUE,0) +
686 nvl(yt1.OP_DEBIT_MEMOS_VALUE,0)),
687 nvl(yt.OP_DEBIT_MEMOS_VALUE,0) +
688 nvl(yt1.OP_DEBIT_MEMOS_VALUE,0)) ,
689 DECODE( yt.OP_DEBIT_MEMOS_COUNT, null,
690 DECODE(yt1.OP_DEBIT_MEMOS_COUNT, null, null,
691 nvl(yt.OP_DEBIT_MEMOS_COUNT,0) +
692 nvl(yt1.OP_DEBIT_MEMOS_COUNT,0)),
693 nvl(yt.OP_DEBIT_MEMOS_COUNT,0) +
694 nvl(yt1.OP_DEBIT_MEMOS_COUNT,0)) ,
695 DECODE( yt.OP_DEPOSITS_VALUE, null,
696 DECODE(yt1.OP_DEPOSITS_VALUE, null, null,
697 nvl(yt.OP_DEPOSITS_VALUE,0) +
698 nvl(yt1.OP_DEPOSITS_VALUE,0)),
699 nvl(yt.OP_DEPOSITS_VALUE,0) +
700 nvl(yt1.OP_DEPOSITS_VALUE,0)) ,
701 DECODE( yt.OP_DEPOSITS_COUNT, null,
702 DECODE(yt1.OP_DEPOSITS_COUNT, null, null,
703 nvl(yt.OP_DEPOSITS_COUNT,0) +
704 nvl(yt1.OP_DEPOSITS_COUNT,0)),
705 nvl(yt.OP_DEPOSITS_COUNT,0) +
706 nvl(yt1.OP_DEPOSITS_COUNT,0)) ,
707 DECODE( yt.OP_BILLS_RECEIVABLES_VALUE, null,
708 DECODE(yt1.OP_BILLS_RECEIVABLES_VALUE, null, null,
709 nvl(yt.OP_BILLS_RECEIVABLES_VALUE,0) +
710 nvl(yt1.OP_BILLS_RECEIVABLES_VALUE,0)),
711 nvl(yt.OP_BILLS_RECEIVABLES_VALUE,0) +
712 nvl(yt1.OP_BILLS_RECEIVABLES_VALUE,0)) ,
713 DECODE( yt.OP_BILLS_RECEIVABLES_COUNT, null,
714 DECODE(yt1.OP_BILLS_RECEIVABLES_COUNT, null, null,
715 nvl(yt.OP_BILLS_RECEIVABLES_COUNT,0) +
716 nvl(yt1.OP_BILLS_RECEIVABLES_COUNT,0)),
717 nvl(yt.OP_BILLS_RECEIVABLES_COUNT,0) +
718 nvl(yt1.OP_BILLS_RECEIVABLES_COUNT,0)) ,
719 DECODE( yt.OP_CHARGEBACK_VALUE, null,
720 DECODE(yt1.OP_CHARGEBACK_VALUE, null, null,
721 nvl(yt.OP_CHARGEBACK_VALUE,0) +
722 nvl(yt1.OP_CHARGEBACK_VALUE,0)),
723 nvl(yt.OP_CHARGEBACK_VALUE,0) +
724 nvl(yt1.OP_CHARGEBACK_VALUE,0)) ,
725 DECODE( yt.OP_CHARGEBACK_COUNT, null,
726 DECODE(yt1.OP_CHARGEBACK_COUNT, null, null,
727 nvl(yt.OP_CHARGEBACK_COUNT,0) +
728 nvl(yt1.OP_CHARGEBACK_COUNT,0)),
729 nvl(yt.OP_CHARGEBACK_COUNT,0) +
730 nvl(yt1.OP_CHARGEBACK_COUNT,0)) ,
731 DECODE( yt.OP_CREDIT_MEMOS_VALUE, null,
732 DECODE(yt1.OP_CREDIT_MEMOS_VALUE, null, null,
733 nvl(yt.OP_CREDIT_MEMOS_VALUE,0) +
734 nvl(yt1.OP_CREDIT_MEMOS_VALUE,0)),
735 nvl(yt.OP_CREDIT_MEMOS_VALUE,0) +
736 nvl(yt1.OP_CREDIT_MEMOS_VALUE,0)) ,
737 DECODE( yt.OP_CREDIT_MEMOS_COUNT, null,
741 nvl(yt.OP_CREDIT_MEMOS_COUNT,0) +
738 DECODE(yt1.OP_CREDIT_MEMOS_COUNT, null, null,
739 nvl(yt.OP_CREDIT_MEMOS_COUNT,0) +
740 nvl(yt1.OP_CREDIT_MEMOS_COUNT,0)),
742 nvl(yt1.OP_CREDIT_MEMOS_COUNT,0)) ,
743 DECODE( yt.UNRESOLVED_CASH_VALUE, null,
744 DECODE(yt1.UNRESOLVED_CASH_VALUE, null, null,
745 nvl(yt.UNRESOLVED_CASH_VALUE,0) +
746 nvl(yt1.UNRESOLVED_CASH_VALUE,0)),
747 nvl(yt.UNRESOLVED_CASH_VALUE,0) +
748 nvl(yt1.UNRESOLVED_CASH_VALUE,0)) ,
749 DECODE( yt.UNRESOLVED_CASH_VALUE, null,
750 DECODE(yt1.UNRESOLVED_CASH_COUNT, null, null,
751 nvl(yt.UNRESOLVED_CASH_COUNT,0) +
752 nvl(yt1.UNRESOLVED_CASH_COUNT,0)),
753 nvl(yt.UNRESOLVED_CASH_COUNT,0) +
754 nvl(yt1.UNRESOLVED_CASH_COUNT,0)) ,
755 DECODE( yt.RECEIPTS_AT_RISK_VALUE, null,
756 DECODE(yt1.RECEIPTS_AT_RISK_VALUE, null, null,
757 nvl(yt.RECEIPTS_AT_RISK_VALUE,0) +
758 nvl(yt1.RECEIPTS_AT_RISK_VALUE,0)),
759 nvl(yt.RECEIPTS_AT_RISK_VALUE,0) +
760 nvl(yt1.RECEIPTS_AT_RISK_VALUE,0)) ,
761 DECODE( yt.INV_AMT_IN_DISPUTE, null,
762 DECODE(yt1.INV_AMT_IN_DISPUTE, null, null,
763 nvl(yt.INV_AMT_IN_DISPUTE,0) +
764 nvl(yt1.INV_AMT_IN_DISPUTE,0)),
765 nvl(yt.INV_AMT_IN_DISPUTE,0) +
766 nvl(yt1.INV_AMT_IN_DISPUTE,0)) ,
767 DECODE( yt.DISPUTED_INV_COUNT, null,
768 DECODE(yt1.DISPUTED_INV_COUNT, null, null,
769 nvl(yt.DISPUTED_INV_COUNT,0) +
770 nvl(yt1.DISPUTED_INV_COUNT,0)),
771 nvl(yt.DISPUTED_INV_COUNT,0) +
772 nvl(yt1.DISPUTED_INV_COUNT,0)) ,
773 DECODE( yt.PENDING_ADJ_VALUE, null,
774 DECODE(yt1.PENDING_ADJ_VALUE, null, null,
775 nvl(yt.PENDING_ADJ_VALUE,0) +
776 nvl(yt1.PENDING_ADJ_VALUE,0)),
777 nvl(yt.PENDING_ADJ_VALUE,0) +
778 nvl(yt1.PENDING_ADJ_VALUE,0)) ,
779 DECODE( yt.PAST_DUE_INV_VALUE, null,
780 DECODE(yt1.PAST_DUE_INV_VALUE, null, null,
781 nvl(yt.PAST_DUE_INV_VALUE,0) +
782 nvl(yt1.PAST_DUE_INV_VALUE,0)),
783 nvl(yt.PAST_DUE_INV_VALUE,0) +
784 nvl(yt1.PAST_DUE_INV_VALUE,0)) ,
785 DECODE( yt.PAST_DUE_INV_INST_COUNT, null,
786 DECODE(yt1.PAST_DUE_INV_INST_COUNT, null, null,
787 nvl(yt.PAST_DUE_INV_INST_COUNT,0) +
788 nvl(yt1.PAST_DUE_INV_INST_COUNT,0)),
789 nvl(yt.PAST_DUE_INV_INST_COUNT,0) +
790 nvl(yt1.PAST_DUE_INV_INST_COUNT,0)) ,
791 DECODE(GREATEST(nvl(yt.LAST_PAYMENT_DATE,yt1.last_payment_date),
792 nvl(yt1.LAST_PAYMENT_DATE, yt.last_payment_date)),
793 yt.LAST_PAYMENT_DATE, yt.LAST_PAYMENT_AMOUNT,
794 yt1.LAST_PAYMENT_AMOUNT),
795 GREATEST(nvl(yt.LAST_PAYMENT_DATE,yt1.last_payment_date),
796 nvl(yt1.LAST_PAYMENT_DATE, yt.last_payment_date)),
797 DECODE(GREATEST(nvl(yt.LAST_PAYMENT_DATE,yt1.last_payment_date),
798 nvl(yt1.LAST_PAYMENT_DATE, yt.last_payment_date)),
799 yt.LAST_PAYMENT_DATE, yt.LAST_PAYMENT_NUMBER,
800 yt1.LAST_PAYMENT_NUMBER),
801 sysdate,
802 FND_GLOBAL.user_id,
803 FND_GLOBAL.login_id
804 FROM ar_trx_bal_summary yt1
805 WHERE yt1.cust_account_id = PRIMARY_KEY1_LIST(I)
806 AND yt1.SITE_USE_ID=PRIMARY_KEY2_LIST(I)
807 AND yt1.CURRENCY=PRIMARY_KEY3_LIST(I)
808 AND yt1.ORG_ID=PRIMARY_KEY4_LIST(I)
812 AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
809 AND EXISTS ( SELECT 'X'
810 FROM AR_TRX_BAL_SUMMARY yt2
811 WHERE yt2.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
813 AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
814 AND yt2.ORG_ID=PRIMARY_KEY4_LIST(I) ))
815 WHERE yt.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
816 AND yt.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
817 AND yt.CURRENCY=PRIMARY_KEY3_LIST(I)
818 AND yt.ORG_ID=PRIMARY_KEY4_LIST(I) ;
819
820 arp_message.set_line( 'after UPDATE AR_TRX_BAL_SUMMARY ');
821 arp_message.set_line( 'before DELETE AR_TRX_BAL_SUMMARY');
822 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
823 DELETE AR_TRX_BAL_SUMMARY yt
824 WHERE yt.cust_account_id = PRIMARY_KEY1_LIST(I)
825 AND yt.SITE_USE_ID=PRIMARY_KEY2_LIST(I)
826 AND yt.CURRENCY=PRIMARY_KEY3_LIST(I)
827 AND yt.ORG_ID=PRIMARY_KEY4_LIST(I)
828 AND EXISTS ( SELECT 'X'
829 FROM AR_TRX_BAL_SUMMARY yt2
830 WHERE yt2.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
831 AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
832 AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
833 AND yt2.ORG_ID=PRIMARY_KEY4_LIST(I) );
834
835 arp_message.set_line( 'after DELETE AR_TRX_BAL_SUMMARY');
836
837 arp_message.set_line( 'before UPDATE AR_TRX_BAL_SUMMARY again');
838 /* bug4727614: Added not exists clause to prevent unique index error */
839 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
840 UPDATE AR_TRX_BAL_SUMMARY yt SET
841 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
842 ,SITE_USE_ID=DECODE(SITE_USE_ID, -99, -99, NUM_COL2_NEW_LIST(I))
843 , LAST_UPDATE_DATE=SYSDATE
844 , last_updated_by=arp_standard.profile.user_id
845 , last_update_login=arp_standard.profile.last_update_login
846 WHERE CUST_ACCOUNT_ID=PRIMARY_KEY1_LIST(I)
847 AND SITE_USE_ID=PRIMARY_KEY2_LIST(I)
848 AND CURRENCY=PRIMARY_KEY3_LIST(I)
849 AND ORG_ID=PRIMARY_KEY4_LIST(I)
850 and not exists ( SELECT 'X'
851 FROM AR_TRX_BAL_SUMMARY yt2
852 WHERE yt2.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
853 AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
854 AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
855 AND yt2.ORG_ID=PRIMARY_KEY4_LIST(I) );
856
857 arp_message.set_line( 'after UPDATE AR_TRX_BAL_SUMMARY again');
858 l_count := l_count + SQL%ROWCOUNT;
859 IF l_last_fetch THEN
860 EXIT;
861 END IF;
862 END LOOP;
863
864 arp_message.set_name('AR','AR_ROWS_UPDATED');
865 arp_message.set_token('NUM_ROWS',to_char(l_count));
866 END IF;
867 EXCEPTION
868 WHEN OTHERS THEN
869 arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE');
870 arp_message.set_line( 'TRX_BAL_SUMMARY_ACCOUNT_MERGE: SQLERRM : ' || SQLERRM);
871 RAISE;
872 END TRX_BAL_SUMMARY_ACCOUNT_MERGE;
873
874 /*-------------------------------------------------------------
875 |
876 | PROCEDURE
877 | TRX_SUMMARY_ACCOUNT_MERGE
878 | DESCRIPTION :
879 | Account merge procedure for the table, AR_TRX_SUMMARY
880 | Bug4502961: Modified cursor merged_records to avoid error
881 |
882 |--------------------------------------------------------------*/
883
884 PROCEDURE TRX_SUMMARY_ACCOUNT_MERGE (
885 req_id NUMBER,
886 set_num NUMBER,
887 process_mode VARCHAR2) IS
888
889 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
890 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
891 INDEX BY BINARY_INTEGER;
892 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
893
894 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
895 AR_TRX_SUMMARY.CUST_ACCOUNT_ID%TYPE
896 INDEX BY BINARY_INTEGER;
897 PRIMARY_KEY1_LIST CUST_ACCOUNT_ID_LIST_TYPE;
898
899 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
900 AR_TRX_SUMMARY.SITE_USE_ID%TYPE
901 INDEX BY BINARY_INTEGER;
902 PRIMARY_KEY2_LIST SITE_USE_ID_LIST_TYPE;
903
904 TYPE CURRENCY_LIST_TYPE IS TABLE OF
905 AR_TRX_SUMMARY.CURRENCY%TYPE
906 INDEX BY BINARY_INTEGER;
907 PRIMARY_KEY3_LIST CURRENCY_LIST_TYPE;
908
909 TYPE AS_OF_DATE_LIST_TYPE IS TABLE OF
910 AR_TRX_SUMMARY.AS_OF_DATE%TYPE
911 INDEX BY BINARY_INTEGER;
912 PRIMARY_KEY4_LIST AS_OF_DATE_LIST_TYPE;
913
914 TYPE ORG_ID_LIST_TYPE IS TABLE OF
915 AR_TRX_SUMMARY.ORG_ID%TYPE
916 INDEX BY BINARY_INTEGER;
917 PRIMARY_KEY5_LIST ORG_ID_LIST_TYPE;
918
919 TYPE NUMBER_LIST_TYPE IS TABLE OF NUMBER
920 INDEX BY BINARY_INTEGER;
921
922
923
924 DEL_OP_BAL_HIGH_WATERMARK NUMBER_LIST_TYPE;
925 DEL_TOTAL_CASH_RECEIPTS_VALUE NUMBER_LIST_TYPE;
926 DEL_TOTAL_CASH_RECEIPTS_COUNT NUMBER_LIST_TYPE;
930 DEL_INV_INST_PMT_DAYS_SUM NUMBER_LIST_TYPE;
927 DEL_TOTAL_INVOICES_VALUE NUMBER_LIST_TYPE;
928 DEL_TOTAL_INVOICES_COUNT NUMBER_LIST_TYPE;
929 DEL_INV_PAID_AMOUNT NUMBER_LIST_TYPE;
931 DEL_TOTAL_BILLS_REC_VALUE NUMBER_LIST_TYPE;
932 DEL_TOTAL_BILLS_REC_COUNT NUMBER_LIST_TYPE;
933 DEL_TOTAL_CREDIT_MEMOS_VALUE NUMBER_LIST_TYPE;
934 DEL_TOTAL_CREDIT_MEMOS_COUNT NUMBER_LIST_TYPE;
935 DEL_TOTAL_DEBIT_MEMOS_VALUE NUMBER_LIST_TYPE;
936 DEL_TOTAL_DEBIT_MEMOS_COUNT NUMBER_LIST_TYPE;
937 DEL_TOTAL_CHARGEBACK_VALUE NUMBER_LIST_TYPE;
938 DEL_TOTAL_CHARGEBACK_COUNT NUMBER_LIST_TYPE;
939 DEL_TOTAL_EARNED_DISC_VALUE NUMBER_LIST_TYPE;
940 DEL_TOTAL_EARNED_DISC_COUNT NUMBER_LIST_TYPE;
941 DEL_TOTAL_UNEARNED_DISC_VALUE NUMBER_LIST_TYPE;
942 DEL_TOTAL_UNEARNED_DISC_COUNT NUMBER_LIST_TYPE;
943 DEL_TOTAL_ADJUSTMENTS_VALUE NUMBER_LIST_TYPE;
944 DEL_TOTAL_ADJUSTMENTS_COUNT NUMBER_LIST_TYPE;
945 DEL_TOTAL_DEPOSITS_VALUE NUMBER_LIST_TYPE;
946 DEL_TOTAL_DEPOSITS_COUNT NUMBER_LIST_TYPE;
947 DEL_SUM_APP_AMT_DAYS_LATE NUMBER_LIST_TYPE;
948 DEL_SUM_APP_AMT NUMBER_LIST_TYPE;
949 DEL_COUNT_OF_TOT_INV_INST_PAID NUMBER_LIST_TYPE;
950 DEL_CNT_OF_INV_INST_PAID_LATE NUMBER_LIST_TYPE;
951 DEL_COUNT_OF_DISC_INV_INST NUMBER_LIST_TYPE;
952 DEL_LARGEST_INV_AMOUNT NUMBER_LIST_TYPE;
953 DEL_LARGEST_INV_DATE AS_OF_DATE_LIST_TYPE;
954 DEL_LARGEST_INV_CUST_TRX_ID NUMBER_LIST_TYPE;
955 DEL_DAYS_CREDIT_GRANTED_SUM NUMBER_LIST_TYPE;
956 DEL_NSF_STOP_PAYMENT_COUNT NUMBER_LIST_TYPE;
957 DEL_NSF_STOP_PAYMENT_AMOUNT NUMBER_LIST_TYPE;
958
959
960 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
961 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
962
963 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
964 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
965
966 l_profile_val VARCHAR2(30);
967 CURSOR merged_records IS
968 SELECT distinct CUSTOMER_MERGE_HEADER_ID
969 ,CUST_ACCOUNT_ID
970 ,SITE_USE_ID
971 ,CURRENCY
972 ,AS_OF_DATE
973 ,yt.ORG_ID
974 ,CUST_ACCOUNT_ID
975 ,SITE_USE_ID
976 ,OP_BAL_HIGH_WATERMARK
977 ,TOTAL_CASH_RECEIPTS_VALUE
978 ,TOTAL_CASH_RECEIPTS_COUNT
979 ,TOTAL_INVOICES_VALUE
980 ,TOTAL_INVOICES_COUNT
981 ,INV_PAID_AMOUNT
982 ,INV_INST_PMT_DAYS_SUM
983 ,TOTAL_BILLS_RECEIVABLES_VALUE
984 ,TOTAL_BILLS_RECEIVABLES_COUNT
985 ,TOTAL_CREDIT_MEMOS_VALUE
986 ,TOTAL_CREDIT_MEMOS_COUNT
987 ,TOTAL_DEBIT_MEMOS_VALUE
988 ,TOTAL_DEBIT_MEMOS_COUNT
989 ,TOTAL_CHARGEBACK_VALUE
990 ,TOTAL_CHARGEBACK_COUNT
991 ,TOTAL_EARNED_DISC_VALUE
992 ,TOTAL_EARNED_DISC_COUNT
993 ,TOTAL_UNEARNED_DISC_VALUE
994 ,TOTAL_UNEARNED_DISC_COUNT
995 ,TOTAL_ADJUSTMENTS_VALUE
996 ,TOTAL_ADJUSTMENTS_COUNT
997 ,TOTAL_DEPOSITS_VALUE
998 ,TOTAL_DEPOSITS_COUNT
999 ,SUM_APP_AMT_DAYS_LATE
1000 ,SUM_APP_AMT
1001 ,COUNT_OF_TOT_INV_INST_PAID
1002 ,COUNT_OF_INV_INST_PAID_LATE
1003 ,COUNT_OF_DISC_INV_INST
1004 ,LARGEST_INV_AMOUNT
1005 ,LARGEST_INV_DATE
1006 ,LARGEST_INV_CUST_TRX_ID
1007 ,DAYS_CREDIT_GRANTED_SUM
1008 ,NSF_STOP_PAYMENT_COUNT
1009 ,NSF_STOP_PAYMENT_AMOUNT
1010 FROM AR_TRX_SUMMARY yt, ra_customer_merges m
1011 WHERE yt.cust_account_id = m.duplicate_id
1012 AND DECODE( yt.site_use_id , -99, m.duplicate_site_id,
1013 yt.site_use_id ) = m.duplicate_site_id
1014 AND m.process_flag = 'N'
1015 AND m.request_id = req_id
1016 AND m.set_number = set_num;
1017 l_last_fetch BOOLEAN := FALSE;
1018 l_count NUMBER;
1019
1020
1021 BEGIN
1022 IF process_mode='LOCK' THEN
1023 NULL;
1024 ELSE
1025 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1026 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_TRX_SUMMARY',FALSE);
1027 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1028 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1029
1030 open merged_records;
1031 LOOP
1032 FETCH merged_records BULK COLLECT INTO
1033 MERGE_HEADER_ID_LIST
1034 , PRIMARY_KEY1_LIST
1035 , PRIMARY_KEY2_LIST
1036 , PRIMARY_KEY3_LIST
1037 , PRIMARY_KEY4_LIST
1038 , PRIMARY_KEY5_LIST
1039 , NUM_COL1_ORIG_LIST
1040 , NUM_COL2_ORIG_LIST
1041 , DEL_OP_BAL_HIGH_WATERMARK
1042 ,DEL_TOTAL_CASH_RECEIPTS_VALUE
1043 ,DEL_TOTAL_CASH_RECEIPTS_COUNT
1044 ,DEL_TOTAL_INVOICES_VALUE
1045 ,DEL_TOTAL_INVOICES_COUNT
1046 ,DEL_INV_PAID_AMOUNT
1047 ,DEL_INV_INST_PMT_DAYS_SUM
1048 ,DEL_TOTAL_BILLS_REC_VALUE
1052 ,DEL_TOTAL_DEBIT_MEMOS_VALUE
1049 ,DEL_TOTAL_BILLS_REC_COUNT
1050 ,DEL_TOTAL_CREDIT_MEMOS_VALUE
1051 ,DEL_TOTAL_CREDIT_MEMOS_COUNT
1053 ,DEL_TOTAL_DEBIT_MEMOS_COUNT
1054 ,DEL_TOTAL_CHARGEBACK_VALUE
1055 ,DEL_TOTAL_CHARGEBACK_COUNT
1056 ,DEL_TOTAL_EARNED_DISC_VALUE
1057 ,DEL_TOTAL_EARNED_DISC_COUNT
1058 ,DEL_TOTAL_UNEARNED_DISC_VALUE
1059 ,DEL_TOTAL_UNEARNED_DISC_COUNT
1060 ,DEL_TOTAL_ADJUSTMENTS_VALUE
1061 ,DEL_TOTAL_ADJUSTMENTS_COUNT
1062 ,DEL_TOTAL_DEPOSITS_VALUE
1063 ,DEL_TOTAL_DEPOSITS_COUNT
1064 ,DEL_SUM_APP_AMT_DAYS_LATE
1065 ,DEL_SUM_APP_AMT
1066 ,DEL_COUNT_OF_TOT_INV_INST_PAID
1067 ,DEL_CNT_OF_INV_INST_PAID_LATE
1068 ,DEL_COUNT_OF_DISC_INV_INST
1069 ,DEL_LARGEST_INV_AMOUNT
1070 ,DEL_LARGEST_INV_DATE
1071 ,DEL_LARGEST_INV_CUST_TRX_ID
1072 ,DEL_DAYS_CREDIT_GRANTED_SUM
1073 ,DEL_NSF_STOP_PAYMENT_COUNT
1074 ,DEL_NSF_STOP_PAYMENT_AMOUNT
1075 limit 1000;
1076 IF merged_records%NOTFOUND THEN
1077 l_last_fetch := TRUE;
1078 END IF;
1079 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1080 exit;
1081 END IF;
1082 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1083 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1084 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1085
1086 END LOOP;
1087 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1088 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1089 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1090 MERGE_LOG_ID,
1091 TABLE_NAME,
1092 MERGE_HEADER_ID,
1093 PRIMARY_KEY1,
1094 PRIMARY_KEY2,
1095 PRIMARY_KEY3,
1096 PRIMARY_KEY4,
1097 PRIMARY_KEY5,
1098 NUM_COL1_ORIG,
1099 NUM_COL1_NEW,
1100 NUM_COL2_ORIG,
1101 NUM_COL2_NEW,
1102 ACTION_FLAG,
1103 REQUEST_ID,
1104 DEL_COL1,
1105 DEL_COL2,
1106 DEL_COL3,
1107 DEL_COL4,
1108 DEL_COL5,
1109 DEL_COL6,
1110 DEL_COL7,
1111 DEL_COL8,
1112 DEL_COL9,
1113 DEL_COL10,
1114 DEL_COL11,
1115 DEL_COL12,
1116 DEL_COL13,
1117 DEL_COL14,
1118 DEL_COL15,
1119 DEL_COL16,
1120 DEL_COL17,
1121 DEL_COL18,
1122 DEL_COL19,
1123 DEL_COL20,
1124 DEL_COL21,
1125 DEL_COL22,
1126 DEL_COL23,
1127 DEL_COL24,
1128 DEL_COL25,
1129 DEL_COL26,
1130 DEL_COL27,
1131 DEL_COL28,
1132 DEL_COL29,
1133 DEL_COL30,
1134 DEL_COL31,
1135 DEL_COL32,
1136 DEL_COL33,
1137 DEL_COL34,
1138 CREATED_BY,
1139 CREATION_DATE,
1140 LAST_UPDATE_LOGIN,
1141 LAST_UPDATE_DATE,
1142 LAST_UPDATED_BY
1143 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
1144 'AR_TRX_SUMMARY',
1145 MERGE_HEADER_ID_LIST(I),
1146 PRIMARY_KEY1_LIST(I),
1147 PRIMARY_KEY2_LIST(I),
1148 PRIMARY_KEY3_LIST(I),
1149 PRIMARY_KEY4_LIST(I),
1150 PRIMARY_KEY5_LIST(I),
1151 NUM_COL1_ORIG_LIST(I),
1152 NUM_COL1_NEW_LIST(I),
1153 NUM_COL2_ORIG_LIST(I),
1154 NUM_COL2_NEW_LIST(I),
1155 'D',
1156 req_id,
1157 DEL_OP_BAL_HIGH_WATERMARK(I)
1158 ,DEL_TOTAL_CASH_RECEIPTS_VALUE(I)
1159 ,DEL_TOTAL_CASH_RECEIPTS_COUNT(I)
1160 ,DEL_TOTAL_INVOICES_VALUE(I)
1161 ,DEL_TOTAL_INVOICES_COUNT(I)
1162 ,DEL_INV_PAID_AMOUNT(I)
1163 ,DEL_INV_INST_PMT_DAYS_SUM(I)
1164 ,DEL_TOTAL_BILLS_REC_VALUE(I)
1165 ,DEL_TOTAL_BILLS_REC_COUNT(I)
1166 ,DEL_TOTAL_CREDIT_MEMOS_VALUE(I)
1167 ,DEL_TOTAL_CREDIT_MEMOS_COUNT(I)
1168 ,DEL_TOTAL_DEBIT_MEMOS_VALUE(I)
1169 ,DEL_TOTAL_DEBIT_MEMOS_COUNT(I)
1170 ,DEL_TOTAL_CHARGEBACK_VALUE(I)
1171 ,DEL_TOTAL_CHARGEBACK_COUNT(I)
1172 ,DEL_TOTAL_EARNED_DISC_VALUE(I)
1173 ,DEL_TOTAL_EARNED_DISC_COUNT(I)
1174 ,DEL_TOTAL_UNEARNED_DISC_VALUE(I)
1175 ,DEL_TOTAL_UNEARNED_DISC_COUNT(I)
1176 ,DEL_TOTAL_ADJUSTMENTS_VALUE(I)
1177 ,DEL_TOTAL_ADJUSTMENTS_COUNT(I)
1178 ,DEL_TOTAL_DEPOSITS_VALUE(I)
1179 ,DEL_TOTAL_DEPOSITS_COUNT(I)
1180 ,DEL_SUM_APP_AMT_DAYS_LATE(I)
1181 ,DEL_SUM_APP_AMT(I)
1182 ,DEL_COUNT_OF_TOT_INV_INST_PAID(I)
1183 ,DEL_CNT_OF_INV_INST_PAID_LATE(I)
1184 ,DEL_COUNT_OF_DISC_INV_INST(I)
1188 ,DEL_DAYS_CREDIT_GRANTED_SUM(I)
1185 ,DEL_LARGEST_INV_AMOUNT(I)
1186 ,DEL_LARGEST_INV_DATE(I)
1187 ,DEL_LARGEST_INV_CUST_TRX_ID(I)
1189 ,DEL_NSF_STOP_PAYMENT_COUNT(I)
1190 ,DEL_NSF_STOP_PAYMENT_AMOUNT(I)
1191 ,hz_utility_pub.CREATED_BY,
1192 hz_utility_pub.CREATION_DATE,
1193 hz_utility_pub.LAST_UPDATE_LOGIN,
1194 hz_utility_pub.LAST_UPDATE_DATE,
1195 hz_utility_pub.LAST_UPDATED_BY
1196 );
1197 END IF;
1198 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1199 UPDATE AR_TRX_SUMMARY yt
1200 SET ( OP_BAL_HIGH_WATERMARK
1201 ,TOTAL_CASH_RECEIPTS_VALUE
1202 ,TOTAL_CASH_RECEIPTS_COUNT
1203 ,TOTAL_INVOICES_VALUE
1204 ,TOTAL_INVOICES_COUNT
1205 ,INV_PAID_AMOUNT
1206 ,INV_INST_PMT_DAYS_SUM
1207 ,TOTAL_BILLS_RECEIVABLES_VALUE
1208 ,TOTAL_BILLS_RECEIVABLES_COUNT
1209 ,TOTAL_CREDIT_MEMOS_VALUE
1210 ,TOTAL_CREDIT_MEMOS_COUNT
1211 ,TOTAL_DEBIT_MEMOS_VALUE
1212 ,TOTAL_DEBIT_MEMOS_COUNT
1213 ,TOTAL_CHARGEBACK_VALUE
1214 ,TOTAL_CHARGEBACK_COUNT
1215 ,TOTAL_EARNED_DISC_VALUE
1216 ,TOTAL_EARNED_DISC_COUNT
1217 ,TOTAL_UNEARNED_DISC_VALUE
1218 ,TOTAL_UNEARNED_DISC_COUNT
1219 ,TOTAL_ADJUSTMENTS_VALUE
1220 ,TOTAL_ADJUSTMENTS_COUNT
1221 ,TOTAL_DEPOSITS_VALUE
1222 ,TOTAL_DEPOSITS_COUNT
1223 ,SUM_APP_AMT_DAYS_LATE
1224 ,SUM_APP_AMT
1225 ,COUNT_OF_TOT_INV_INST_PAID
1226 ,COUNT_OF_INV_INST_PAID_LATE
1227 ,COUNT_OF_DISC_INV_INST
1228 ,LARGEST_INV_AMOUNT
1229 ,LARGEST_INV_DATE
1230 ,LARGEST_INV_CUST_TRX_ID
1231 ,DAYS_CREDIT_GRANTED_SUM
1232 ,NSF_STOP_PAYMENT_COUNT
1233 ,NSF_STOP_PAYMENT_AMOUNT
1234 ,LAST_UPDATE_DATE
1235 ,LAST_UPDATED_BY
1236 ,LAST_UPDATE_LOGIN) =
1237 ( SELECT DECODE( yt.OP_BAL_HIGH_WATERMARK, null,
1238 DECODE(yt1.OP_BAL_HIGH_WATERMARK, null, null,
1239 nvl(yt.OP_BAL_HIGH_WATERMARK,0) +
1240 nvl(yt1.OP_BAL_HIGH_WATERMARK,0)),
1241 nvl(yt.OP_BAL_HIGH_WATERMARK,0) +
1242 nvl(yt1.OP_BAL_HIGH_WATERMARK,0)) ,
1243 DECODE( yt.TOTAL_CASH_RECEIPTS_VALUE, null,
1244 DECODE(yt1.TOTAL_CASH_RECEIPTS_VALUE, null, null,
1245 nvl(yt.TOTAL_CASH_RECEIPTS_VALUE,0) +
1246 nvl(yt1.TOTAL_CASH_RECEIPTS_VALUE,0)),
1247 nvl(yt.TOTAL_CASH_RECEIPTS_VALUE,0) +
1248 nvl(yt1.TOTAL_CASH_RECEIPTS_VALUE,0)),
1249 DECODE( yt.TOTAL_CASH_RECEIPTS_COUNT, null,
1250 DECODE(yt1.TOTAL_CASH_RECEIPTS_COUNT, null, null,
1251 nvl(yt.TOTAL_CASH_RECEIPTS_COUNT,0) +
1252 nvl(yt1.TOTAL_CASH_RECEIPTS_COUNT,0)),
1253 nvl(yt.TOTAL_CASH_RECEIPTS_COUNT,0) +
1254 nvl(yt1.TOTAL_CASH_RECEIPTS_COUNT,0)),
1255 DECODE( yt.TOTAL_INVOICES_VALUE, null,
1256 DECODE(yt1.TOTAL_INVOICES_VALUE, null, null,
1257 nvl(yt.TOTAL_INVOICES_VALUE,0) +
1258 nvl(yt1.TOTAL_INVOICES_VALUE,0)),
1259 nvl(yt.TOTAL_INVOICES_VALUE,0) +
1260 nvl(yt1.TOTAL_INVOICES_VALUE,0)),
1261 DECODE( yt.TOTAL_INVOICES_COUNT, null,
1262 DECODE(yt1.TOTAL_INVOICES_COUNT, null, null,
1263 nvl(yt.TOTAL_INVOICES_COUNT,0) +
1264 nvl(yt1.TOTAL_INVOICES_COUNT,0)),
1265 nvl(yt.TOTAL_INVOICES_COUNT,0) +
1266 nvl(yt1.TOTAL_INVOICES_COUNT,0)),
1267 DECODE( yt.INV_PAID_AMOUNT, null,
1268 DECODE(yt1.INV_PAID_AMOUNT, null, null,
1269 nvl(yt.INV_PAID_AMOUNT,0) +
1270 nvl(yt1.INV_PAID_AMOUNT,0)),
1271 nvl(yt.INV_PAID_AMOUNT,0) +
1272 nvl(yt1.INV_PAID_AMOUNT,0)),
1273 DECODE( yt.INV_INST_PMT_DAYS_SUM, null,
1274 DECODE(yt1.INV_INST_PMT_DAYS_SUM, null, null,
1275 nvl(yt.INV_INST_PMT_DAYS_SUM,0) +
1276 nvl(yt1.INV_INST_PMT_DAYS_SUM,0)),
1277 nvl(yt.INV_INST_PMT_DAYS_SUM,0) +
1281 nvl(yt.TOTAL_BILLS_RECEIVABLES_VALUE,0) +
1278 nvl(yt1.INV_INST_PMT_DAYS_SUM,0)),
1279 DECODE( yt.TOTAL_BILLS_RECEIVABLES_VALUE, null,
1280 DECODE(yt1.TOTAL_BILLS_RECEIVABLES_VALUE, null, null,
1282 nvl(yt1.TOTAL_BILLS_RECEIVABLES_VALUE,0)),
1283 nvl(yt.TOTAL_BILLS_RECEIVABLES_VALUE,0) +
1284 nvl(yt1.TOTAL_BILLS_RECEIVABLES_VALUE,0)),
1285 DECODE( yt.TOTAL_BILLS_RECEIVABLES_COUNT, null,
1286 DECODE(yt1.TOTAL_BILLS_RECEIVABLES_COUNT, null, null,
1287 nvl(yt.TOTAL_BILLS_RECEIVABLES_COUNT,0) +
1288 nvl(yt1.TOTAL_BILLS_RECEIVABLES_COUNT,0)),
1289 nvl(yt.TOTAL_BILLS_RECEIVABLES_COUNT,0) +
1290 nvl(yt1.TOTAL_BILLS_RECEIVABLES_COUNT,0)),
1291 DECODE( yt.TOTAL_CREDIT_MEMOS_VALUE, null,
1292 DECODE(yt1.TOTAL_CREDIT_MEMOS_VALUE, null, null,
1293 nvl(yt.TOTAL_CREDIT_MEMOS_VALUE,0) +
1294 nvl(yt1.TOTAL_CREDIT_MEMOS_VALUE,0)),
1295 nvl(yt.TOTAL_CREDIT_MEMOS_VALUE,0) +
1296 nvl(yt1.TOTAL_CREDIT_MEMOS_VALUE,0)),
1297 DECODE( yt.TOTAL_CREDIT_MEMOS_COUNT, null,
1298 DECODE(yt1.TOTAL_CREDIT_MEMOS_COUNT, null, null,
1299 nvl(yt.TOTAL_CREDIT_MEMOS_COUNT,0) +
1300 nvl(yt1.TOTAL_CREDIT_MEMOS_COUNT,0)),
1301 nvl(yt.TOTAL_CREDIT_MEMOS_COUNT,0) +
1302 nvl(yt1.TOTAL_CREDIT_MEMOS_COUNT,0)),
1303 DECODE( yt.TOTAL_DEBIT_MEMOS_VALUE, null,
1304 DECODE(yt1.TOTAL_DEBIT_MEMOS_VALUE, null, null,
1305 nvl(yt.TOTAL_DEBIT_MEMOS_VALUE,0) +
1306 nvl(yt1.TOTAL_DEBIT_MEMOS_VALUE,0)),
1307 nvl(yt.TOTAL_DEBIT_MEMOS_VALUE,0) +
1308 nvl(yt1.TOTAL_DEBIT_MEMOS_VALUE,0)),
1309 DECODE( yt.TOTAL_DEBIT_MEMOS_COUNT, null,
1310 DECODE(yt1.TOTAL_DEBIT_MEMOS_COUNT, null, null,
1311 nvl(yt.TOTAL_DEBIT_MEMOS_COUNT,0) +
1312 nvl(yt1.TOTAL_DEBIT_MEMOS_COUNT,0)),
1313 nvl(yt.TOTAL_DEBIT_MEMOS_COUNT,0) +
1314 nvl(yt1.TOTAL_DEBIT_MEMOS_COUNT,0)),
1315 DECODE( yt.TOTAL_CHARGEBACK_VALUE, null,
1316 DECODE(yt1.TOTAL_CHARGEBACK_VALUE, null, null,
1317 nvl(yt.TOTAL_CHARGEBACK_VALUE,0) +
1318 nvl(yt1.TOTAL_CHARGEBACK_VALUE,0)),
1319 nvl(yt.TOTAL_CHARGEBACK_VALUE,0) +
1320 nvl(yt1.TOTAL_CHARGEBACK_VALUE,0)),
1321 DECODE( yt.TOTAL_CHARGEBACK_COUNT, null,
1322 DECODE(yt1.TOTAL_CHARGEBACK_COUNT, null, null,
1323 nvl(yt.TOTAL_CHARGEBACK_COUNT,0) +
1324 nvl(yt1.TOTAL_CHARGEBACK_COUNT,0)),
1325 nvl(yt.TOTAL_CHARGEBACK_COUNT,0) +
1326 nvl(yt1.TOTAL_CHARGEBACK_COUNT,0)),
1327 DECODE( yt.TOTAL_EARNED_DISC_VALUE, null,
1328 DECODE(yt1.TOTAL_EARNED_DISC_VALUE, null, null,
1329 nvl(yt.TOTAL_EARNED_DISC_VALUE,0) +
1330 nvl(yt1.TOTAL_EARNED_DISC_VALUE,0)),
1331 nvl(yt.TOTAL_EARNED_DISC_VALUE,0) +
1332 nvl(yt1.TOTAL_EARNED_DISC_VALUE,0)),
1333 DECODE( yt.TOTAL_EARNED_DISC_COUNT, null,
1334 DECODE(yt1.TOTAL_EARNED_DISC_COUNT, null, null,
1335 nvl(yt.TOTAL_EARNED_DISC_COUNT,0) +
1336 nvl(yt1.TOTAL_EARNED_DISC_COUNT,0)),
1337 nvl(yt.TOTAL_EARNED_DISC_COUNT,0) +
1338 nvl(yt1.TOTAL_EARNED_DISC_COUNT,0)),
1339 DECODE( yt.TOTAL_UNEARNED_DISC_VALUE, null,
1340 DECODE(yt1.TOTAL_UNEARNED_DISC_VALUE, null, null,
1341 nvl(yt.TOTAL_UNEARNED_DISC_VALUE,0) +
1342 nvl(yt1.TOTAL_UNEARNED_DISC_VALUE,0)),
1343 nvl(yt.TOTAL_UNEARNED_DISC_VALUE,0) +
1344 nvl(yt1.TOTAL_UNEARNED_DISC_VALUE,0)),
1345 DECODE( yt.TOTAL_UNEARNED_DISC_COUNT, null,
1349 nvl(yt.TOTAL_UNEARNED_DISC_COUNT,0) +
1346 DECODE(yt1.TOTAL_UNEARNED_DISC_COUNT, null, null,
1347 nvl(yt.TOTAL_UNEARNED_DISC_COUNT,0) +
1348 nvl(yt1.TOTAL_UNEARNED_DISC_COUNT,0)),
1350 nvl(yt1.TOTAL_UNEARNED_DISC_COUNT,0)),
1351 DECODE( yt.TOTAL_ADJUSTMENTS_VALUE, null,
1352 DECODE(yt1.TOTAL_ADJUSTMENTS_VALUE, null, null,
1353 nvl(yt.TOTAL_ADJUSTMENTS_VALUE,0) +
1354 nvl(yt1.TOTAL_ADJUSTMENTS_VALUE,0)),
1355 nvl(yt.TOTAL_ADJUSTMENTS_VALUE,0) +
1356 nvl(yt1.TOTAL_ADJUSTMENTS_VALUE,0)),
1357 DECODE( yt.TOTAL_ADJUSTMENTS_COUNT, null,
1358 DECODE(yt1.TOTAL_ADJUSTMENTS_COUNT, null, null,
1359 nvl(yt.TOTAL_ADJUSTMENTS_COUNT,0) +
1360 nvl(yt1.TOTAL_ADJUSTMENTS_COUNT,0)),
1361 nvl(yt.TOTAL_ADJUSTMENTS_COUNT,0) +
1362 nvl(yt1.TOTAL_ADJUSTMENTS_COUNT,0)),
1363 DECODE( yt.TOTAL_DEPOSITS_VALUE, null,
1364 DECODE(yt1.TOTAL_DEPOSITS_VALUE, null, null,
1365 nvl(yt.TOTAL_DEPOSITS_VALUE,0) +
1366 nvl(yt1.TOTAL_DEPOSITS_VALUE,0)),
1367 nvl(yt.TOTAL_DEPOSITS_VALUE,0) +
1368 nvl(yt1.TOTAL_DEPOSITS_VALUE,0)),
1369 DECODE( yt.TOTAL_DEPOSITS_COUNT, null,
1370 DECODE(yt1.TOTAL_DEPOSITS_COUNT, null, null,
1371 nvl(yt.TOTAL_DEPOSITS_COUNT,0) +
1372 nvl(yt1.TOTAL_DEPOSITS_COUNT,0)),
1373 nvl(yt.TOTAL_DEPOSITS_COUNT,0) +
1374 nvl(yt1.TOTAL_DEPOSITS_COUNT,0)),
1375 DECODE( yt.SUM_APP_AMT_DAYS_LATE, null,
1376 DECODE(yt1.SUM_APP_AMT_DAYS_LATE, null, null,
1377 nvl(yt.SUM_APP_AMT_DAYS_LATE,0) +
1378 nvl(yt1.SUM_APP_AMT_DAYS_LATE,0)),
1379 nvl(yt.SUM_APP_AMT_DAYS_LATE,0) +
1380 nvl(yt1.SUM_APP_AMT_DAYS_LATE,0)),
1381 DECODE( yt.SUM_APP_AMT, null,
1382 DECODE(yt1.SUM_APP_AMT, null, null,
1383 nvl(yt.SUM_APP_AMT,0) +
1384 nvl(yt1.SUM_APP_AMT,0)),
1385 nvl(yt.SUM_APP_AMT,0) +
1386 nvl(yt1.SUM_APP_AMT,0)),
1387 DECODE( yt.COUNT_OF_TOT_INV_INST_PAID, null,
1388 DECODE(yt1.COUNT_OF_TOT_INV_INST_PAID, null, null,
1389 nvl(yt.COUNT_OF_TOT_INV_INST_PAID,0) +
1390 nvl(yt1.COUNT_OF_TOT_INV_INST_PAID,0)),
1391 nvl(yt.COUNT_OF_TOT_INV_INST_PAID,0) +
1392 nvl(yt1.COUNT_OF_TOT_INV_INST_PAID,0)),
1393 DECODE( yt.COUNT_OF_INV_INST_PAID_LATE, null,
1394 DECODE(yt1.COUNT_OF_INV_INST_PAID_LATE, null, null,
1395 nvl(yt.COUNT_OF_INV_INST_PAID_LATE,0) +
1396 nvl(yt1.COUNT_OF_INV_INST_PAID_LATE,0)),
1397 nvl(yt.COUNT_OF_INV_INST_PAID_LATE,0) +
1398 nvl(yt1.COUNT_OF_INV_INST_PAID_LATE,0)),
1399 DECODE( yt.COUNT_OF_DISC_INV_INST, null,
1400 DECODE(yt1.COUNT_OF_DISC_INV_INST, null, null,
1401 nvl(yt.COUNT_OF_DISC_INV_INST,0) +
1402 nvl(yt1.COUNT_OF_DISC_INV_INST,0)),
1403 nvl(yt.COUNT_OF_DISC_INV_INST,0) +
1404 nvl(yt1.COUNT_OF_DISC_INV_INST,0)),
1405 DECODE(GREATEST(nvl(yt.LARGEST_INV_DATE,yt1.LARGEST_INV_DATE),
1406 nvl(yt1.LARGEST_INV_DATE, yt.LARGEST_INV_DATE)),
1407 yt.LARGEST_INV_DATE, yt.LARGEST_INV_AMOUNT,
1408 yt1.LARGEST_INV_AMOUNT),
1409 GREATEST(nvl(yt.LARGEST_INV_DATE,yt1.LARGEST_INV_DATE),
1410 nvl(yt1.LARGEST_INV_DATE, yt.LARGEST_INV_DATE)),
1411 DECODE(GREATEST(nvl(yt.LARGEST_INV_DATE,yt1.LARGEST_INV_DATE),
1412 nvl(yt1.LARGEST_INV_DATE, yt.LARGEST_INV_DATE)),
1413 yt.LARGEST_INV_DATE, yt.LARGEST_INV_CUST_TRX_ID,
1417 nvl(yt.DAYS_CREDIT_GRANTED_SUM,0) +
1414 yt1.LARGEST_INV_CUST_TRX_ID),
1415 DECODE( yt.DAYS_CREDIT_GRANTED_SUM, null,
1416 DECODE(yt1.DAYS_CREDIT_GRANTED_SUM, null, null,
1418 nvl(yt1.DAYS_CREDIT_GRANTED_SUM,0)),
1419 nvl(yt.DAYS_CREDIT_GRANTED_SUM,0) +
1420 nvl(yt1.DAYS_CREDIT_GRANTED_SUM,0)),
1421 DECODE( yt.NSF_STOP_PAYMENT_COUNT, null,
1422 DECODE(yt1.NSF_STOP_PAYMENT_COUNT, null, null,
1423 nvl(yt.NSF_STOP_PAYMENT_COUNT,0) +
1424 nvl(yt1.NSF_STOP_PAYMENT_COUNT,0)),
1425 nvl(yt.NSF_STOP_PAYMENT_COUNT,0) +
1426 nvl(yt1.NSF_STOP_PAYMENT_COUNT,0)),
1427 DECODE( yt.NSF_STOP_PAYMENT_AMOUNT, null,
1428 DECODE(yt1.NSF_STOP_PAYMENT_AMOUNT, null, null,
1429 nvl(yt.NSF_STOP_PAYMENT_AMOUNT,0) +
1430 nvl(yt1.NSF_STOP_PAYMENT_AMOUNT,0)),
1431 nvl(yt.NSF_STOP_PAYMENT_AMOUNT,0) +
1432 nvl(yt1.NSF_STOP_PAYMENT_AMOUNT,0)),
1433 sysdate,
1434 FND_GLOBAL.user_id,
1435 FND_GLOBAL.login_id
1436 FROM ar_trx_summary yt1
1437 WHERE yt1.cust_account_id = PRIMARY_KEY1_LIST(I)
1438 AND yt1.SITE_USE_ID=PRIMARY_KEY2_LIST(I)
1439 AND yt1.CURRENCY=PRIMARY_KEY3_LIST(I)
1440 AND yt1.AS_OF_DATE=PRIMARY_KEY4_LIST(I)
1441 AND yt1.ORG_ID=PRIMARY_KEY5_LIST(I)
1442 AND EXISTS ( SELECT 'X'
1443 FROM AR_TRX_SUMMARY yt2
1444 WHERE yt2.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1445 AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
1446 AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
1447 AND yt2.AS_OF_DATE=PRIMARY_KEY4_LIST(I)
1448 AND yt2.ORG_ID=PRIMARY_KEY5_LIST(I) ))
1449 WHERE yt.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1450 AND yt.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
1451 AND yt.CURRENCY=PRIMARY_KEY3_LIST(I)
1452 AND yt.AS_OF_DATE=PRIMARY_KEY4_LIST(I)
1453 AND yt.ORG_ID=PRIMARY_KEY5_LIST(I) ;
1454
1455 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1456 DELETE AR_TRX_SUMMARY yt
1457 WHERE yt.cust_account_id = PRIMARY_KEY1_LIST(I)
1458 AND yt.SITE_USE_ID=PRIMARY_KEY2_LIST(I)
1459 AND yt.CURRENCY=PRIMARY_KEY3_LIST(I)
1460 AND yt.AS_OF_DATE=PRIMARY_KEY4_LIST(I)
1461 AND yt.ORG_ID=PRIMARY_KEY5_LIST(I)
1462 AND EXISTS ( SELECT 'X'
1463 FROM AR_TRX_SUMMARY yt2
1464 WHERE yt2.CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1465 AND yt2.SITE_USE_ID=NUM_COL2_NEW_LIST(I)
1466 AND yt2.CURRENCY=PRIMARY_KEY3_LIST(I)
1467 AND yt2.AS_OF_DATE=PRIMARY_KEY4_LIST(I)
1468 AND yt2.ORG_ID=PRIMARY_KEY5_LIST(I) );
1469
1470
1471 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1472 UPDATE AR_TRX_SUMMARY yt SET
1473 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
1474 ,SITE_USE_ID=NUM_COL2_NEW_LIST(I)
1475 , LAST_UPDATE_DATE=SYSDATE
1476 , last_updated_by=arp_standard.profile.user_id
1477 , last_update_login=arp_standard.profile.last_update_login
1478 WHERE CUST_ACCOUNT_ID=PRIMARY_KEY1_LIST(I)
1479 AND SITE_USE_ID=PRIMARY_KEY2_LIST(I)
1480 AND CURRENCY=PRIMARY_KEY3_LIST(I)
1481 AND AS_OF_DATE=PRIMARY_KEY4_LIST(I)
1482 AND ORG_ID=PRIMARY_KEY5_LIST(I) ;
1483
1484
1485 l_count := l_count + SQL%ROWCOUNT;
1486 IF l_last_fetch THEN
1487 EXIT;
1488 END IF;
1489 END LOOP;
1490
1491 arp_message.set_name('AR','AR_ROWS_UPDATED');
1492 arp_message.set_token('NUM_ROWS',to_char(l_count));
1493 END IF;
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 arp_message.set_line( 'TRX_SUMMARY_ACCOUNT_MERGE');
1497 RAISE;
1498 END TRX_SUMMARY_ACCOUNT_MERGE;
1499
1500 END AR_CMGT_ACCOUNT_MERGE;