[Home] [Help]
PACKAGE BODY: APPS.FII_AR_ACCOUNT_MERGE_PKG
Source
1 PACKAGE BODY FII_AR_ACCOUNT_MERGE_PKG AS
2 /* $Header: FIIAR21B.pls 120.0.12000000.1 2007/02/23 02:27:35 applrt ship $ */
3
4 PROCEDURE MERGE_ACCOUNTS
5 (
6 req_id NUMBER,
7 set_num NUMBER,
8 process_mode VARCHAR2) IS
9 BEGIN
10 MERGE_FACT_ACCOUNTS (req_id, set_num, process_mode);
11 MERGE_COLLECTOR_ACCOUNTS (req_id, set_num, process_mode);
12 MERGE_CUSTOMER_ACCOUNTS (req_id, set_num, process_mode);
13 END MERGE_ACCOUNTS;
14
15
16 PROCEDURE MERGE_FACT_ACCOUNTS
17 (
18 req_id NUMBER,
19 set_num NUMBER,
20 process_mode VARCHAR2) IS
21
22 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
23 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
24 INDEX BY BINARY_INTEGER;
25 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
26
27 TYPE PAYMENT_SCHEDULE_ID_LIST_TYPE IS TABLE OF FII_AR_PMT_SCHEDULES_F.PAYMENT_SCHEDULE_ID%TYPE
28 INDEX BY BINARY_INTEGER;
29 PRIMARY_KEY_ID_LIST PAYMENT_SCHEDULE_ID_LIST_TYPE;
30
31 TYPE BILL_TO_CUSTOMER_ID_LIST_TYPE IS TABLE OF FII_AR_PMT_SCHEDULES_F.BILL_TO_CUSTOMER_ID%TYPE
32 INDEX BY BINARY_INTEGER;
33 NUM_COL1_ORIG_LIST BILL_TO_CUSTOMER_ID_LIST_TYPE;
34 NUM_COL1_NEW_LIST BILL_TO_CUSTOMER_ID_LIST_TYPE;
35
36 TYPE BILL_TO_SITE_USE_ID_LIST_TYPE IS TABLE OF FII_AR_PMT_SCHEDULES_F.BILL_TO_SITE_USE_ID%TYPE
37 INDEX BY BINARY_INTEGER;
38 NUM_COL2_ORIG_LIST BILL_TO_SITE_USE_ID_LIST_TYPE;
39 NUM_COL2_NEW_LIST BILL_TO_SITE_USE_ID_LIST_TYPE;
40
41 l_profile_val VARCHAR2(30);
42 CURSOR merged_records IS
43 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
44 yt.PAYMENT_SCHEDULE_ID,
45 yt.BILL_TO_CUSTOMER_ID,
46 yt.BILL_TO_SITE_USE_ID,
47 m.CUSTOMER_ID,
48 m.customer_site_id
49 FROM FII_AR_PMT_SCHEDULES_F yt,
50 ra_customer_merges m
51 WHERE (yt.BILL_TO_CUSTOMER_ID = m.duplicate_id
52 AND yt.BILL_TO_SITE_USE_ID = m.duplicate_site_id)
53 AND m.process_flag = 'N'
54 AND m.request_id = req_id
55 AND m.set_number = set_num;
56
57 l_last_fetch BOOLEAN := FALSE;
58 l_count NUMBER;
59
60 BEGIN
61 IF process_mode='LOCK' THEN
62 NULL;
63 ELSE
64 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
65 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','FII_AR_PMT_SCHEDULES_F',FALSE);
66 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
67 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
68
69 open merged_records;
70 LOOP
71
72 FETCH merged_records BULK COLLECT INTO
73 MERGE_HEADER_ID_LIST,
74 PRIMARY_KEY_ID_LIST,
75 NUM_COL1_ORIG_LIST,
76 NUM_COL2_ORIG_LIST,
77 NUM_COL1_NEW_LIST,
78 NUM_COL2_NEW_LIST;
79
80 IF merged_records%NOTFOUND THEN
81 l_last_fetch := TRUE;
82 END IF;
83
84 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
85 exit;
86 END IF;
87
88
89 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
90 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
91 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
92 MERGE_LOG_ID,
93 TABLE_NAME,
94 MERGE_HEADER_ID,
95 PRIMARY_KEY_ID,
96 NUM_COL1_ORIG, NUM_COL1_NEW,
97 NUM_COL2_ORIG, NUM_COL2_NEW,
98 ACTION_FLAG,
99 REQUEST_ID,
100 CREATED_BY,
101 CREATION_DATE,
102 LAST_UPDATE_LOGIN,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY
105 ) VALUES (HZ_CUSTOMER_MERGE_LOG_s.nextval,
106 'FII_AR_PMT_SCHEDULES_F',
107 MERGE_HEADER_ID_LIST(I),
108 PRIMARY_KEY_ID_LIST(I),
109 NUM_COL1_ORIG_LIST(I), NUM_COL1_NEW_LIST(I),
110 NUM_COL2_ORIG_LIST(I), NUM_COL2_NEW_LIST(I),
111 'U',
112 req_id,
113 hz_utility_pub.CREATED_BY,
114 hz_utility_pub.CREATION_DATE,
115 hz_utility_pub.LAST_UPDATE_LOGIN,
116 hz_utility_pub.LAST_UPDATE_DATE,
117 hz_utility_pub.LAST_UPDATED_BY
118 );
119
120 END IF;
121
122 --FII_AR_PAYMENT_SCHEDULES_F
123 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
124 UPDATE FII_AR_PMT_SCHEDULES_F yt
125 SET BILL_TO_CUSTOMER_ID=NUM_COL1_NEW_LIST(I),
126 BILL_TO_SITE_USE_ID=NUM_COL2_NEW_LIST(I)
127 WHERE PAYMENT_SCHEDULE_ID=PRIMARY_KEY_ID_LIST(I);
128
129 --FII_AR_TRANSACTIONS_F
130 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
131 UPDATE FII_AR_TRANSACTIONS_F yt
132 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
133 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
134 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
135 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
136
137 --FII_AR_RECEIPTS_F
138 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
139 UPDATE FII_AR_RECEIPTS_F yt
140 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
141 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
142 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
143 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
144
145 --FII_AR_RECEIPTS_F (Collector_bill_to)
146 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
147 UPDATE FII_AR_RECEIPTS_F yt
148 SET COLLECTOR_BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
149 COLLECTOR_BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
150 WHERE COLLECTOR_BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
151 AND COLLECTOR_BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
152
153 --FII_AR_ADJUSTMENTS_F
154 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
155 UPDATE FII_AR_ADJUSTMENTS_F yt
156 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
157 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
158 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
159 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
160
161 --FII_AR_DISPUTE_HISTORY_F
162 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
163 UPDATE FII_AR_DISPUTE_HISTORY_F yt
164 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
165 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
166 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
167 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
168
169 --FII_AR_AGING_RECEIVABLES
170 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
171 UPDATE FII_AR_AGING_RECEIVABLES yt
172 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
173 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
174 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
175 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
176
177 --FII_AR_AGING_RECEIPTS
178 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
179 UPDATE FII_AR_AGING_RECEIPTS yt
180 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
181 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
182 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
183 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
184
185 --FII_AR_AGING_DISPUTES
186 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
187 UPDATE FII_AR_AGING_DISPUTES yt
188 SET BILL_TO_CUSTOMER_ID = NUM_COL1_NEW_LIST(I),
189 BILL_TO_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
190 WHERE BILL_TO_CUSTOMER_ID = NUM_COL1_ORIG_LIST(I)
191 AND BILL_TO_SITE_USE_ID = NUM_COL2_ORIG_LIST(I);
192
193 l_count := l_count + SQL%ROWCOUNT;
194 IF l_last_fetch THEN
195 EXIT;
196 END IF;
197 END LOOP;
198
199 arp_message.set_name('AR','AR_ROWS_UPDATED');
200 arp_message.set_token('NUM_ROWS',to_char(l_count));
201 END IF;
202
203 EXCEPTION
204
205 WHEN OTHERS THEN
206 arp_message.set_line('MERGE_FACT_ACCOUNTS');
207 RAISE;
208 END MERGE_FACT_ACCOUNTS;
209
210 PROCEDURE MERGE_COLLECTOR_ACCOUNTS
211 (
212 req_id NUMBER,
213 set_num NUMBER,
214 process_mode VARCHAR2) IS
215
216 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
217 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
218 INDEX BY BINARY_INTEGER;
219 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
220
221 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF FII_COLLECTORS.CUST_ACCOUNT_ID%TYPE
222 INDEX BY BINARY_INTEGER;
223 PRIMARY_KEY_ID1_LIST CUST_ACCOUNT_ID_LIST_TYPE;
224
225 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF FII_COLLECTORS.SITE_USE_ID%TYPE
226 INDEX BY BINARY_INTEGER;
227 PRIMARY_KEY_ID2_LIST SITE_USE_ID_LIST_TYPE;
228
229 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
230 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
231
232 NUM_COL2_ORIG_LIST SITE_USE_ID_LIST_TYPE;
233 NUM_COL2_NEW_LIST SITE_USE_ID_LIST_TYPE;
234
235 TYPE PARTY_ID_LIST_TYPE IS TABLE OF FII_COLLECTORS.PARTY_ID%TYPE
236 INDEX BY BINARY_INTEGER;
237 PARTY_ID_LIST PARTY_ID_LIST_TYPE;
238
239 TYPE COLLECTOR_ID_LIST_TYPE IS TABLE OF FII_COLLECTORS.COLLECTOR_ID%TYPE
240 INDEX BY BINARY_INTEGER;
241 COLLECTOR_ID_LIST COLLECTOR_ID_LIST_TYPE;
242
243 l_profile_val VARCHAR2(30);
244 CURSOR merged_records IS
245 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
246 yt.cust_account_id,
247 yt.site_use_id,
248 yt.cust_account_id,
249 yt.site_use_id,
250 m.CUSTOMER_ID,
251 m.customer_site_id
252 FROM FII_COLLECTORS yt,
253 ra_customer_merges m
254 WHERE (yt.cust_account_id = m.duplicate_id
255 AND yt.site_use_id = m.duplicate_site_id)
256 AND (m.customer_id, m.customer_site_id) not in
257 (select cust_account_id, site_use_id
258 from fii_collectors)
259 AND m.process_flag = 'N'
260 AND m.request_id = req_id
261 AND m.set_number = set_num;
262
263 CURSOR deleted_records IS
264 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
265 yt.cust_account_id,
266 yt.site_use_id,
267 yt.party_id,
268 yt.collector_id
269 FROM FII_COLLECTORS yt,
270 ra_customer_merges m
271 WHERE yt.cust_account_id = m.duplicate_id
272 AND m.customer_id in
273 (select cust_account_id
274 from fii_collectors)
275 AND m.process_flag = 'N'
276 AND m.request_id = req_id
277 AND m.set_number = set_num;
278
279 l_last_fetch BOOLEAN := FALSE;
280 l_count NUMBER;
281
282 BEGIN
283 IF process_mode='LOCK' THEN
284 NULL;
285 ELSE
286 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
287 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','FII_COLLECTORS',FALSE);
288 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
289 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
290
291 /*
292 The following code will update records, which when updated with the surviving account/site_use
293 will not result in a primary key violation, since the surviving account/site_use combination
294 is new to fii_collectors.
295 */
296 open merged_records;
297 LOOP
298
299 FETCH merged_records BULK COLLECT INTO
300 MERGE_HEADER_ID_LIST,
301 PRIMARY_KEY_ID1_LIST,
302 PRIMARY_KEY_ID2_LIST,
303 NUM_COL1_ORIG_LIST,
304 NUM_COL2_ORIG_LIST,
305 NUM_COL1_NEW_LIST,
306 NUM_COL2_NEW_LIST;
307
308 IF merged_records%NOTFOUND THEN
309 l_last_fetch := TRUE;
310 END IF;
311
312 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
313 exit;
314 END IF;
315
316 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
317 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
318 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
319 MERGE_LOG_ID,
320 TABLE_NAME,
321 MERGE_HEADER_ID,
322 PRIMARY_KEY_ID,
323 PRIMARY_KEY_ID1, PRIMARY_KEY_ID2,
324 NUM_COL1_ORIG, NUM_COL1_NEW,
325 NUM_COL2_ORIG, NUM_COL2_NEW,
326 ACTION_FLAG,
327 REQUEST_ID,
328 CREATED_BY,
329 CREATION_DATE,
330 LAST_UPDATE_LOGIN,
331 LAST_UPDATE_DATE,
332 LAST_UPDATED_BY
333 ) VALUES (HZ_CUSTOMER_MERGE_LOG_s.nextval,
334 'FII_COLLECTORS',
335 MERGE_HEADER_ID_LIST(I),
336 null,
337 PRIMARY_KEY_ID1_LIST(I), PRIMARY_KEY_ID2_LIST(I),
338 NUM_COL1_ORIG_LIST(I), NUM_COL1_NEW_LIST(I),
339 NUM_COL2_ORIG_LIST(I), NUM_COL2_NEW_LIST(I),
340 'U',
341 req_id,
342 hz_utility_pub.CREATED_BY,
343 hz_utility_pub.CREATION_DATE,
344 hz_utility_pub.LAST_UPDATE_LOGIN,
345 hz_utility_pub.LAST_UPDATE_DATE,
346 hz_utility_pub.LAST_UPDATED_BY
347 );
348
349 END IF;
350
351 --FII_COLLECTORS
352 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
353 UPDATE FII_COLLECTORS yt
354 SET CUST_ACCOUNT_ID = NUM_COL1_NEW_LIST(I),
355 SITE_USE_ID = NUM_COL2_NEW_LIST(I)
356 WHERE CUST_ACCOUNT_ID = PRIMARY_KEY_ID1_LIST(I)
357 AND SITE_USE_ID = PRIMARY_KEY_ID2_LIST(I);
358
359
360 l_count := l_count + SQL%ROWCOUNT;
361 IF l_last_fetch THEN
362 EXIT;
363 END IF;
364 END LOOP;
365
366 arp_message.set_name('AR','AR_ROWS_UPDATED');
367 arp_message.set_token('NUM_ROWS',to_char(l_count));
368
369
370 /*
371 The following code will delete records, which if updated with the surviving account/site_use
372 would have resulted in a primary key violation, since the surviving account/site_use combination
373 already exists in fii_collectors.
374 */
375 l_last_fetch := FALSE;
376
377 open deleted_records;
378 LOOP
379
380 FETCH deleted_records BULK COLLECT INTO
381 MERGE_HEADER_ID_LIST,
382 PRIMARY_KEY_ID1_LIST,
383 PRIMARY_KEY_ID2_LIST,
384 PARTY_ID_LIST,
385 COLLECTOR_ID_LIST;
386
387 IF deleted_records%NOTFOUND THEN
388 l_last_fetch := TRUE;
389 END IF;
390
391 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
392 exit;
393 END IF;
394
395 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
396 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
397 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
398 MERGE_LOG_ID,
399 TABLE_NAME,
400 MERGE_HEADER_ID,
401 PRIMARY_KEY_ID1, PRIMARY_KEY_ID2,
402 DEL_COL1,
403 DEL_COL2,
404 ACTION_FLAG,
405 REQUEST_ID,
406 CREATED_BY,
407 CREATION_DATE,
408 LAST_UPDATE_LOGIN,
409 LAST_UPDATE_DATE,
410 LAST_UPDATED_BY
411 ) VALUES (HZ_CUSTOMER_MERGE_LOG_s.nextval,
412 'FII_COLLECTORS',
413 MERGE_HEADER_ID_LIST(I),
414 PRIMARY_KEY_ID1_LIST(I), PRIMARY_KEY_ID2_LIST(I),
415 PARTY_ID_LIST(I),
416 COLLECTOR_ID_LIST(I),
417 'D',
418 req_id,
419 hz_utility_pub.CREATED_BY,
420 hz_utility_pub.CREATION_DATE,
421 hz_utility_pub.LAST_UPDATE_LOGIN,
422 hz_utility_pub.LAST_UPDATE_DATE,
423 hz_utility_pub.LAST_UPDATED_BY
424 );
425
426 END IF;
427
428 --FII_COLLECTORS
429 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
430 DELETE FROM FII_COLLECTORS
431 WHERE CUST_ACCOUNT_ID = PRIMARY_KEY_ID1_LIST(I);
432
433
434 l_count := l_count + SQL%ROWCOUNT;
435 IF l_last_fetch THEN
436 EXIT;
437 END IF;
438 END LOOP;
439
440 --arp_message.set_name('AR','AR_ROWS_UPDATED');
441 --arp_message.set_token('NUM_ROWS',to_char(l_count));
442
443 END IF;
444
445 EXCEPTION
446
447 WHEN OTHERS THEN
448 arp_message.set_line('MERGE_COLLECTOR_ACCOUNTS');
449 RAISE;
450
451 END MERGE_COLLECTOR_ACCOUNTS;
452
453
454
455 -- ******************************************************************
456 -- This procedure maintains FII_Cust_Accounts after an Account Merge.
457 -- ******************************************************************
458
459 PROCEDURE MERGE_CUSTOMER_ACCOUNTS (req_id NUMBER,
460 set_num NUMBER,
461 process_mode VARCHAR2) IS
462
463 TYPE Merge_Header_ID_Type IS
464 TABLE OF RA_CUSTOMER_MERGES.CUSTOMER_MERGE_HEADER_ID%TYPE
465 INDEX BY BINARY_INTEGER;
466
467 TYPE Cust_Account_ID_Type IS TABLE OF FII_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE
468 INDEX BY BINARY_INTEGER;
469
470 TYPE Party_ID_Type IS TABLE OF HZ_PARTIES.PARTY_ID%TYPE
471 INDEX BY BINARY_INTEGER;
472
473 TYPE PARTY_ID_LIST_TYPE IS TABLE OF FII_CUST_ACCOUNTS.PARENT_PARTY_ID%TYPE
474 INDEX BY BINARY_INTEGER;
475
476 Merge_Header_ID_List Merge_Header_ID_Type;
477 Cust_Account_ID_List Cust_Account_ID_Type;
478 Account_Owner_Party_ID_List Party_ID_Type;
479 Parent_Party_ID_List Party_ID_Type;
480
481 l_profile_val VARCHAR2(30);
482 l_last_fetch BOOLEAN := FALSE;
483
484 CURSOR Account_Merge_Records IS
485 SELECT M.Customer_Merge_Header_ID,
486 CA.Cust_Account_ID,
487 CA.Account_Owner_Party_ID,
488 CA.Parent_Party_ID
489 FROM FII_Cust_Accounts CA,
490 RA_Customer_Merges M
491 WHERE CA.Cust_Account_ID = M.Duplicate_ID
492 AND M.Process_Flag = 'N'
493 AND M.Request_ID = Req_ID
494 AND M.Set_Number = Set_Num
495 AND M.Delete_Duplicate_Flag = 'Y';
496
497
498 BEGIN
499
500 IF Process_Mode <> 'LOCK' THEN --Process_Mode = 'UPDATE'
501
502 ARP_MESSAGE.SET_NAME('FII','FII_DELETING_TABLE');
503 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','FII_CUST_ACCOUNTS',FALSE);
504 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
505
506 OPEN Account_Merge_Records;
507 LOOP
508
509 FETCH Account_Merge_Records
510 BULK COLLECT INTO Merge_Header_ID_List,
511 Cust_Account_ID_List,
512 Account_Owner_Party_ID_List,
513 Parent_Party_ID_List;
514
515 IF Account_Merge_Records%NOTFOUND THEN
516 l_last_fetch := TRUE;
517 END IF;
518
519 IF Merge_Header_ID_List.COUNT = 0 and l_last_fetch THEN
520 EXIT;
521 END IF;
522
523 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
524 FORALL i in 1..Merge_Header_ID_List.Count
525 INSERT INTO HZ_Customer_Merge_Log(
526 MERGE_LOG_ID,
527 TABLE_NAME,
528 MERGE_HEADER_ID,
529 PRIMARY_KEY_ID1,
530 PRIMARY_KEY_ID2,
531 PRIMARY_KEY_ID3,
532 DEL_COL1,
533 DEL_COL2,
534 DEL_COL3,
535 ACTION_FLAG,
536 REQUEST_ID,
537 CREATED_BY,
538 CREATION_DATE,
539 LAST_UPDATE_LOGIN,
540 LAST_UPDATE_DATE,
541 LAST_UPDATED_BY)
542 VALUES (
543 HZ_Customer_Merge_Log_S.nextval,
544 'FII_CUST_ACCOUNTS',
545 Merge_Header_ID_List(i),
546 Cust_Account_ID_List(i),
547 Account_Owner_Party_ID_List(i),
548 Parent_Party_ID_List(i),
549 Cust_Account_ID_List(i),
550 Account_Owner_Party_ID_List(i),
551 Parent_Party_ID_List(i),
552 'D',
553 Req_ID,
554 HZ_Utility_Pub.CREATED_BY,
555 HZ_Utility_Pub.CREATION_DATE,
556 HZ_Utility_Pub.LAST_UPDATE_LOGIN,
557 HZ_Utility_Pub.LAST_UPDATE_DATE,
558 HZ_Utility_Pub.LAST_UPDATED_BY);
559 END IF;
560
561 FORALL i in 1..Merge_Header_ID_List.Count
562 DELETE FROM FII_Cust_Accounts
563 WHERE Cust_Account_ID = Cust_Account_ID_List(i)
564 AND Account_Owner_Party_ID = Account_Owner_Party_ID_List(i)
565 AND Parent_Party_ID = Parent_Party_ID_List(i);
566
567 IF l_last_fetch THEN
568 EXIT;
569 END IF;
570
571 END LOOP;
572
573 ARP_MESSAGE.SET_NAME('FII','FII_ROWS_DELETED');
574 ARP_MESSAGE.SET_TOKEN('NUM_ROWS', To_Char(Merge_Header_ID_List.Count),FALSE);
575
576 END IF;
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 ARP_MESSAGE.SET_LINE('MERGE_CUSTOMER_ACCOUNTS');
581 RAISE;
582 END MERGE_CUSTOMER_ACCOUNTS;
583
584
585 End FII_AR_ACCOUNT_MERGE_PKG;