[Home] [Help]
PACKAGE BODY: APPS.LNS_MERGE_PKG
Source
1 PACKAGE BODY LNS_MERGE_PKG as
2 /* $Header: LNS_MERGE_B.pls 120.0.12010000.2 2009/02/03 15:21:07 mbolli ship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_MERGE_PKG';
9 G_LOG_ENABLED varchar2(5);
10 G_MSG_LEVEL NUMBER;
11
12 /*========================================================================
13 | PRIVATE PROCEDURE LogMessage
14 |
15 | DESCRIPTION
16 | This procedure logs debug messages to db and to CM log
17 |
18 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
19 | MERGE_LOAN_HEADERS_ACC
20 | MERGE_PARTICIPANTS_ACC
21 |
22 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
23 | None
24 |
25 | PARAMETERS
26 | p_msg_level IN Debug msg level
27 | p_msg IN Debug msg itself
28 |
29 | KNOWN ISSUES
30 | None
31 |
32 | NOTES
33 | Any interesting aspect of the code in the package body which needs
34 | to be stated.
35 |
36 | MODIFICATION HISTORY
37 | Date Author Description of Changes
38 | 13-01-2009 mbolli Created
39 |
40 *=======================================================================*/
41 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
42 IS
43 BEGIN
44 if (p_msg_level >= G_MSG_LEVEL) then
45
46 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
47 if FND_GLOBAL.Conc_Request_Id is not null then
48 fnd_file.put_line(FND_FILE.LOG, p_msg);
49 end if;
50
51 end if;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
56 END;
57
58
59 /*========================================================================
60 | PRIVATE PROCEDURE init
61 |
62 | DESCRIPTION
63 | This procedure inits data needed for processing
64 |
65 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
66 | MERGE_LOAN_HEADERS_ACC
67 | MERGE_PARTICIPANTS_ACC
68 |
69 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
70 | LogMessage
71 |
72 | PARAMETERS
73 | None
74 |
75 | KNOWN ISSUES
76 | None
77 |
78 | NOTES
79 | Any interesting aspect of the code in the package body which needs
80 | to be stated.
81 |
82 | MODIFICATION HISTORY
83 | Date Author Description of Changes
84 | 13-01-2009 mbolli Created
85 |
86 *=======================================================================*/
87 Procedure init
88 IS
89 l_api_name CONSTANT VARCHAR2(30) := 'INIT';
90 l_org_status varchar2(1);
91 BEGIN
92
93 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
94
95 /* getting msg logging info */
96 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
97 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
98
99 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
100 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
101
102 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
103
104 END;
105
106
107
108 PROCEDURE MERGE_LOAN_HEADERS(p_entity_name IN VARCHAR2,
109 p_from_id IN NUMBER,
110 p_to_id IN OUT NOCOPY NUMBER,
111 p_from_fk_id IN NUMBER,
112 p_to_fk_id IN NUMBER,
113 p_parent_entity IN VARCHAR2,
114 p_batch_id IN NUMBER,
115 p_batch_party_id IN NUMBER,
116 x_return_status OUT NOCOPY VARCHAR2)
117 IS
118 BEGIN
119
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 /*
123 If the Parent has NOT changed(i.e. Parent getting transferred)
124 then nothing needs to be done. Set Merged To Id is same as Merged From Id
125 and return
126 */
127
128 IF p_from_FK_id = p_to_FK_id THEN
129 p_to_id := p_from_id;
130 RETURN;
131 END IF;
132
133 /*
134 If the Parent has changed(i.e. Parent is getting merged),
135 then transfer the dependent record to the new parent.
136 Before transferring check if a similar dependent record exists on the new parent.
137 If a duplicate exists then do not transfer and return the id of the duplicate record as the Merged To Id.
138 */
139
140 /* updating PRIMARY_BORROWER_ID column */
141 UPDATE LNS_LOAN_HEADERS_ALL
142 SET PRIMARY_BORROWER_ID = p_To_FK_id,
143 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
144 last_updated_by = HZ_UTILITY_V2PUB.user_id,
145 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
146 request_id = HZ_UTILITY_V2PUB.request_id,
147 program_id = HZ_UTILITY_V2PUB.program_id
148 WHERE PRIMARY_BORROWER_ID = p_from_fk_id;
149
150 /* updating CONTACT_PERS_PARTY_ID column */
151 UPDATE LNS_LOAN_HEADERS_ALL
152 SET CONTACT_PERS_PARTY_ID = p_To_FK_id,
153 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
154 last_updated_by = HZ_UTILITY_V2PUB.user_id,
155 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
156 request_id = HZ_UTILITY_V2PUB.request_id,
157 program_id = HZ_UTILITY_V2PUB.program_id
158 WHERE CONTACT_PERS_PARTY_ID = p_from_fk_id;
159
160 /* updating CONTACT_REL_PARTY_ID column */
161 UPDATE LNS_LOAN_HEADERS_ALL
162 SET CONTACT_REL_PARTY_ID = p_To_FK_id,
163 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
164 last_updated_by = HZ_UTILITY_V2PUB.user_id,
165 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
166 request_id = HZ_UTILITY_V2PUB.request_id,
167 program_id = HZ_UTILITY_V2PUB.program_id
168 WHERE CONTACT_REL_PARTY_ID = p_from_fk_id;
169
170 RETURN;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
175 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
176 FND_MSG_PUB.ADD;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178
179 END MERGE_LOAN_HEADERS;
180
181
182
183 PROCEDURE MERGE_PARTICIPANTS(p_entity_name IN VARCHAR2,
184 p_from_id IN NUMBER,
185 p_to_id IN OUT NOCOPY NUMBER,
186 p_from_fk_id IN NUMBER,
187 p_to_fk_id IN NUMBER,
188 p_parent_entity IN VARCHAR2,
189 p_batch_id IN NUMBER,
190 p_batch_party_id IN NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2)
192 IS
193 BEGIN
194
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 /*
198 If the Parent has NOT changed(i.e. Parent getting transferred)
199 then nothing needs to be done. Set Merged To Id is same as Merged From Id
200 and return
201 */
202
203 IF p_from_FK_id = p_to_FK_id THEN
204 p_to_id := p_from_id;
205 RETURN;
206 END IF;
207
208 /*
209 If the Parent has changed(i.e. Parent is getting merged),
210 then transfer the dependent record to the new parent.
211 Before transferring check if a similar dependent record exists on the new parent.
212 If a duplicate exists then do not transfer and return the id of the duplicate record as the Merged To Id.
213 */
214
215 /* updating HZ_PARTY_ID column */
216 UPDATE LNS_PARTICIPANTS
217 SET HZ_PARTY_ID = p_To_FK_id,
218 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
219 last_updated_by = HZ_UTILITY_V2PUB.user_id,
220 last_update_login = HZ_UTILITY_V2PUB.last_update_login
221 WHERE HZ_PARTY_ID = p_from_fk_id;
222
223 /* updating CONTACT_PERS_PARTY_ID column */
224 UPDATE LNS_PARTICIPANTS
225 SET CONTACT_PERS_PARTY_ID = p_To_FK_id,
226 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
227 last_updated_by = HZ_UTILITY_V2PUB.user_id,
228 last_update_login = HZ_UTILITY_V2PUB.last_update_login
229 WHERE CONTACT_PERS_PARTY_ID = p_from_fk_id;
230
231 /* updating CONTACT_REL_PARTY_ID column */
232 UPDATE LNS_PARTICIPANTS
233 SET CONTACT_REL_PARTY_ID = p_To_FK_id,
234 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
235 last_updated_by = HZ_UTILITY_V2PUB.user_id,
236 last_update_login = HZ_UTILITY_V2PUB.last_update_login
237 WHERE CONTACT_REL_PARTY_ID = p_from_fk_id;
238
239 RETURN;
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
244 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
245 FND_MSG_PUB.ADD;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247
248 END MERGE_PARTICIPANTS;
249
250
251
252 PROCEDURE MERGE_LOAN_HEADERS_ACC (
253 req_id NUMBER,
254 set_num NUMBER,
255 process_mode VARCHAR2)
256 IS
257
258 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
259 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
260 INDEX BY BINARY_INTEGER;
261 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
262
263 TYPE LOAN_ID_LIST_TYPE IS TABLE OF
264 LNS_LOAN_HEADERS.LOAN_ID%TYPE
265 INDEX BY BINARY_INTEGER;
266 PRIMARY_KEY_ID1_LIST LOAN_ID_LIST_TYPE;
267
268 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
269 LNS_LOAN_HEADERS.CUST_ACCOUNT_ID%TYPE
270 INDEX BY BINARY_INTEGER;
271
272 TYPE CUST_ACCT_SITE_ID_LIST_TYPE IS TABLE OF
273 LNS_LOAN_HEADERS.BILL_TO_ACCT_SITE_ID%TYPE
274 INDEX BY BINARY_INTEGER;
275
276 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
277 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
278 NUM_COL2_ORIG_LIST CUST_ACCT_SITE_ID_LIST_TYPE;
279 NUM_COL2_NEW_LIST CUST_ACCT_SITE_ID_LIST_TYPE;
280
281 l_profile_val VARCHAR2(30);
282 CURSOR merged_records IS
283 SELECT distinct CUSTOMER_MERGE_HEADER_ID
284 ,LOAN_ID
285 ,CUST_ACCOUNT_ID
286 FROM LNS_LOAN_HEADERS yt, ra_customer_merges m
287 WHERE
288 (yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID)
289 AND m.process_flag = 'N'
290 AND m.request_id = req_id
291 AND m.set_number = set_num;
292
293 CURSOR merged_records2 IS
294 SELECT distinct CUSTOMER_MERGE_HEADER_ID
295 ,LOAN_ID
296 ,CUSTOMER_ADDRESS_ID
297 FROM LNS_LOAN_HEADERS yt, ra_customer_merges m
298 WHERE
299 (yt.BILL_TO_ACCT_SITE_ID = m.DUPLICATE_ADDRESS_ID)
300 AND m.process_flag = 'N'
301 AND m.request_id = req_id
302 AND m.set_number = set_num;
303
304 l_last_fetch BOOLEAN := FALSE;
305 l_count NUMBER;
306 l_api_name CONSTANT VARCHAR2(30) := 'MERGE_LOAN_HEADERS_ACC';
307
308 BEGIN
309
310 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' BEGIN +');
311 LogMessage(FND_LOG.LEVEL_STATEMENT,'Input parameters:');
312 LogMessage(FND_LOG.LEVEL_STATEMENT,'req_id = ' || req_id);
313 LogMessage(FND_LOG.LEVEL_STATEMENT,'set_num = ' || set_num);
314 LogMessage(FND_LOG.LEVEL_STATEMENT,'process_mode = ' || process_mode );
315
316 IF process_mode='LOCK' THEN
317 NULL;
318 ELSE
319
320 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
321 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','LNS_LOAN_HEADERS',FALSE);
322 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
323 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
324
325
326 LogMessage(FND_LOG.LEVEL_STATEMENT,'Searching for ACCOUNT records...');
327
328 /* merging cust_account_id */
329 open merged_records;
330 LOOP
331 FETCH merged_records BULK COLLECT INTO
332 MERGE_HEADER_ID_LIST
333 , PRIMARY_KEY_ID1_LIST
334 , NUM_COL1_ORIG_LIST;
335
336 IF merged_records%NOTFOUND THEN
337 l_last_fetch := TRUE;
338 END IF;
339
340 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
341 LogMessage(FND_LOG.LEVEL_STATEMENT,'Exiting fetch');
342 exit;
343 END IF;
344
345 LogMessage(FND_LOG.LEVEL_PROCEDURE,'Fetched ' || MERGE_HEADER_ID_LIST.COUNT || ' records');
346
347 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
348 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
349 END LOOP;
350
351 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
352 LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting into HZ_CUSTOMER_MERGE_LOG...');
353
354 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
355 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
356 MERGE_LOG_ID,
357 TABLE_NAME,
358 MERGE_HEADER_ID,
359 PRIMARY_KEY_ID1,
360 NUM_COL1_ORIG,
361 NUM_COL1_NEW,
362 ACTION_FLAG,
363 REQUEST_ID,
364 CREATED_BY,
365 CREATION_DATE,
366 LAST_UPDATE_LOGIN,
367 LAST_UPDATE_DATE,
368 LAST_UPDATED_BY)
369 VALUES
370 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
371 'LNS_LOAN_HEADERS',
372 MERGE_HEADER_ID_LIST(I),
373 PRIMARY_KEY_ID1_LIST(I),
374 NUM_COL1_ORIG_LIST(I),
375 NUM_COL1_NEW_LIST(I),
376 'U',
377 req_id,
378 hz_utility_pub.CREATED_BY,
379 hz_utility_pub.CREATION_DATE,
380 hz_utility_pub.LAST_UPDATE_LOGIN,
381 hz_utility_pub.LAST_UPDATE_DATE,
382 hz_utility_pub.LAST_UPDATED_BY);
383
384 END IF;
385
386 LogMessage(FND_LOG.LEVEL_STATEMENT,'Insertion Completed');
387
388 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updating LNS_LOAN_HEADERS Table ...');
389
390 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
391 UPDATE LNS_LOAN_HEADERS yt SET
392 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
393 , LAST_UPDATE_DATE=SYSDATE
394 , last_updated_by=arp_standard.profile.user_id
395 , last_update_login=arp_standard.profile.last_update_login
396 , REQUEST_ID=req_id
397 , PROGRAM_ID=arp_standard.profile.program_id
398 WHERE LOAN_ID=PRIMARY_KEY_ID1_LIST(I);
399
400 l_count := l_count + SQL%ROWCOUNT;
401
402 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updation Completed');
403
404 IF l_last_fetch THEN
405 EXIT;
406 END IF;
407
408 END LOOP;
409 LogMessage(FND_LOG.LEVEL_STATEMENT,'Total processed ' || l_count || ' ACCOUNT records');
410 arp_message.set_name('AR','AR_ROWS_UPDATED');
411 arp_message.set_token('NUM_ROWS',to_char(l_count));
412
413 <<bill_to_acct_site_id>>
414 /* merging CUST_ACCT_SITE_ID */
415
416 MERGE_HEADER_ID_LIST.delete;
417 PRIMARY_KEY_ID1_LIST.delete;
418 l_count := 0;
419 l_last_fetch := FALSE;
420
421 LogMessage(FND_LOG.LEVEL_STATEMENT,'Searching for CUST_ACCT_SITE_ID records...');
422
423 open merged_records2;
424 LOOP
425 FETCH merged_records2 BULK COLLECT INTO
426 MERGE_HEADER_ID_LIST
427 , PRIMARY_KEY_ID1_LIST
428 , NUM_COL2_ORIG_LIST;
429
430 LogMessage(FND_LOG.LEVEL_STATEMENT,'Fetched ' || MERGE_HEADER_ID_LIST.COUNT || ' records');
431
432 IF merged_records2%NOTFOUND THEN
433 l_last_fetch := TRUE;
434 END IF;
435
436 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
437 LogMessage(FND_LOG.LEVEL_STATEMENT,'Exiting fetch of CustActSites');
438 exit;
439 END IF;
440
441 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
442 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
443 END LOOP;
444
445 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
446 LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting CustActSites into HZ_CUSTOMER_MERGE_LOG...');
447
448 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
449 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
450 MERGE_LOG_ID,
451 TABLE_NAME,
452 MERGE_HEADER_ID,
453 PRIMARY_KEY_ID1,
454 NUM_COL1_ORIG,
455 NUM_COL1_NEW,
456 ACTION_FLAG,
457 REQUEST_ID,
458 CREATED_BY,
459 CREATION_DATE,
460 LAST_UPDATE_LOGIN,
461 LAST_UPDATE_DATE,
462 LAST_UPDATED_BY)
463 VALUES
464 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
465 'LNS_LOAN_HEADERS',
466 MERGE_HEADER_ID_LIST(I),
467 PRIMARY_KEY_ID1_LIST(I),
468 NUM_COL2_ORIG_LIST(I),
469 NUM_COL2_NEW_LIST(I),
470 'U',
471 req_id,
472 hz_utility_pub.CREATED_BY,
473 hz_utility_pub.CREATION_DATE,
474 hz_utility_pub.LAST_UPDATE_LOGIN,
475 hz_utility_pub.LAST_UPDATE_DATE,
476 hz_utility_pub.LAST_UPDATED_BY);
477
478 END IF;
479
480 LogMessage(FND_LOG.LEVEL_STATEMENT,'Insertion of custAcctSites Completed');
481
482 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updating custAcctSites in LNS_LOAN_HEADERS Table ...');
483
484 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
485 UPDATE LNS_LOAN_HEADERS yt SET
486 BILL_TO_ACCT_SITE_ID=NUM_COL2_NEW_LIST(I)
487 , LAST_UPDATE_DATE=SYSDATE
488 , last_updated_by=arp_standard.profile.user_id
489 , last_update_login=arp_standard.profile.last_update_login
490 , REQUEST_ID=req_id
491 , PROGRAM_ID=arp_standard.profile.program_id
492 WHERE LOAN_ID=PRIMARY_KEY_ID1_LIST(I);
493
494 l_count := l_count + SQL%ROWCOUNT;
495
496 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updation of custAcctSites Completed');
497
498 IF l_last_fetch THEN
499 EXIT;
500 END IF;
501
502 END LOOP;
503 LogMessage(FND_LOG.LEVEL_STATEMENT,'Total processed ' || l_count || ' CUST_ACCT_SITES records');
504 arp_message.set_name('AR','AR_ROWS_UPDATED');
505 arp_message.set_token('NUM_ROWS',to_char(l_count));
506
507 END IF;
508
509 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' END -');
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' EXCEPTION');
514 arp_message.set_line( 'MERGE_LOAN_HEADERS_ACC');
515 RAISE;
516 END MERGE_LOAN_HEADERS_ACC;
517
518
519 PROCEDURE MERGE_PARTICIPANTS_ACC (
520 req_id NUMBER,
521 set_num NUMBER,
522 process_mode VARCHAR2)
523 IS
524
525 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
526 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
527 INDEX BY BINARY_INTEGER;
528 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
529
530 TYPE PARTICIPANT_ID_LIST_TYPE IS TABLE OF
531 LNS_PARTICIPANTS.PARTICIPANT_ID%TYPE
532 INDEX BY BINARY_INTEGER;
533 PRIMARY_KEY_ID1_LIST PARTICIPANT_ID_LIST_TYPE;
534
535 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
536 LNS_PARTICIPANTS.CUST_ACCOUNT_ID%TYPE
537 INDEX BY BINARY_INTEGER;
538
539 TYPE CUST_ACCT_SITE_ID_LIST_TYPE IS TABLE OF
540 LNS_LOAN_HEADERS.BILL_TO_ACCT_SITE_ID%TYPE
541 INDEX BY BINARY_INTEGER;
542
543 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
544 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
545 NUM_COL2_ORIG_LIST CUST_ACCT_SITE_ID_LIST_TYPE;
546 NUM_COL2_NEW_LIST CUST_ACCT_SITE_ID_LIST_TYPE;
547
548 l_profile_val VARCHAR2(30);
549 CURSOR merged_records IS
550 SELECT distinct CUSTOMER_MERGE_HEADER_ID
551 ,PARTICIPANT_ID
552 ,CUST_ACCOUNT_ID
553 FROM LNS_PARTICIPANTS yt, ra_customer_merges m
554 WHERE (
555 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
556 ) AND m.process_flag = 'N'
557 AND m.request_id = req_id
558 AND m.set_number = set_num;
559
560 CURSOR merged_records2 IS
561 SELECT distinct CUSTOMER_MERGE_HEADER_ID
562 ,PARTICIPANT_ID
563 ,CUSTOMER_ADDRESS_ID
564 FROM LNS_PARTICIPANTS yt, ra_customer_merges m
565 WHERE
566 (yt.BILL_TO_ACCT_SITE_ID = m.DUPLICATE_ADDRESS_ID)
567 AND m.process_flag = 'N'
568 AND m.request_id = req_id
569 AND m.set_number = set_num;
570
571 l_last_fetch BOOLEAN := FALSE;
572 l_count NUMBER;
573 l_api_name CONSTANT VARCHAR2(30) := 'MERGE_PARTICIPANTS_ACC';
574
575 BEGIN
576
577 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' BEGIN +');
578 LogMessage(FND_LOG.LEVEL_STATEMENT,'Input parameters:');
579 LogMessage(FND_LOG.LEVEL_STATEMENT,'req_id = ' || req_id);
580 LogMessage(FND_LOG.LEVEL_STATEMENT,'set_num = ' || set_num);
581 LogMessage(FND_LOG.LEVEL_STATEMENT,'process_mode = ' || process_mode );
582
583 IF process_mode='LOCK' THEN
584 NULL;
585
586 ELSE
587
588 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
589 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','LNS_PARTICIPANTS',FALSE);
590 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
591 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
592
593 LogMessage(FND_LOG.LEVEL_STATEMENT,'Searching for ACCOUNT records...');
594
595 open merged_records;
596 LOOP
597 FETCH merged_records BULK COLLECT INTO
598 MERGE_HEADER_ID_LIST
599 , PRIMARY_KEY_ID1_LIST
600 , NUM_COL1_ORIG_LIST;
601
602 IF merged_records%NOTFOUND THEN
603 l_last_fetch := TRUE;
604 END IF;
605 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
606 LogMessage(FND_LOG.LEVEL_STATEMENT,'Exiting fetch');
607 exit;
608 END IF;
609
610 LogMessage(FND_LOG.LEVEL_PROCEDURE,'Fetched ' || MERGE_HEADER_ID_LIST.COUNT || ' records');
611
612 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
613 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
614 END LOOP;
615
616 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
617 LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting into HZ_CUSTOMER_MERGE_LOG...');
618
619 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
620 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
621 MERGE_LOG_ID,
622 TABLE_NAME,
623 MERGE_HEADER_ID,
624 PRIMARY_KEY_ID1,
625 NUM_COL1_ORIG,
626 NUM_COL1_NEW,
627 ACTION_FLAG,
628 REQUEST_ID,
629 CREATED_BY,
630 CREATION_DATE,
631 LAST_UPDATE_LOGIN,
632 LAST_UPDATE_DATE,
633 LAST_UPDATED_BY
634 ) VALUES (
635 HZ_CUSTOMER_MERGE_LOG_s.nextval,
636 'LNS_PARTICIPANTS',
637 MERGE_HEADER_ID_LIST(I),
638 PRIMARY_KEY_ID1_LIST(I),
639 NUM_COL1_ORIG_LIST(I),
640 NUM_COL1_NEW_LIST(I),
641 'U',
642 req_id,
643 hz_utility_pub.CREATED_BY,
644 hz_utility_pub.CREATION_DATE,
645 hz_utility_pub.LAST_UPDATE_LOGIN,
646 hz_utility_pub.LAST_UPDATE_DATE,
647 hz_utility_pub.LAST_UPDATED_BY
648 );
649
650 END IF;
651
652 LogMessage(FND_LOG.LEVEL_STATEMENT,'Insertion Completed');
653
654 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updating LNS_LOAN_HEADERS Table ...');
655
656
657 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
658 UPDATE LNS_PARTICIPANTS yt SET
659 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
660 , LAST_UPDATE_DATE=SYSDATE
661 , last_updated_by=arp_standard.profile.user_id
662 , last_update_login=arp_standard.profile.last_update_login
663 WHERE PARTICIPANT_ID=PRIMARY_KEY_ID1_LIST(I);
664
665 l_count := l_count + SQL%ROWCOUNT;
666
667 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updation Completed');
668
669 IF l_last_fetch THEN
670 EXIT;
671 END IF;
672
673 END LOOP;
674 LogMessage(FND_LOG.LEVEL_STATEMENT,'Total processed ' || l_count || ' ACCOUNT records');
675 arp_message.set_name('AR','AR_ROWS_UPDATED');
676 arp_message.set_token('NUM_ROWS',to_char(l_count));
677
678
679 <<bill_to_acct_site_id>>
680 /* merging CUST_ACCT_SITE_ID */
681
682 MERGE_HEADER_ID_LIST.delete;
683 PRIMARY_KEY_ID1_LIST.delete;
684 l_count := 0;
685 l_last_fetch := FALSE;
686
687 LogMessage(FND_LOG.LEVEL_STATEMENT,'Searching for CUST_ACCT_SITE_ID records...');
688
689 open merged_records2;
690 LOOP
691 FETCH merged_records2 BULK COLLECT INTO
692 MERGE_HEADER_ID_LIST
693 , PRIMARY_KEY_ID1_LIST
694 , NUM_COL2_ORIG_LIST;
695
696 LogMessage(FND_LOG.LEVEL_STATEMENT,'Fetched ' || MERGE_HEADER_ID_LIST.COUNT || ' records');
697
698 IF merged_records2%NOTFOUND THEN
699 l_last_fetch := TRUE;
700 END IF;
701
702 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
703 LogMessage(FND_LOG.LEVEL_STATEMENT,'Exiting fetch of CustActSites');
704 exit;
705 END IF;
706
707 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
708 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
709 END LOOP;
710
711 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
712 LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting CustActSites into HZ_CUSTOMER_MERGE_LOG...');
713
714 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
715 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
716 MERGE_LOG_ID,
717 TABLE_NAME,
718 MERGE_HEADER_ID,
719 PRIMARY_KEY_ID1,
720 NUM_COL1_ORIG,
721 NUM_COL1_NEW,
722 ACTION_FLAG,
723 REQUEST_ID,
724 CREATED_BY,
725 CREATION_DATE,
726 LAST_UPDATE_LOGIN,
727 LAST_UPDATE_DATE,
728 LAST_UPDATED_BY)
729 VALUES
730 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
731 'LNS_PARTICIPANTS',
732 MERGE_HEADER_ID_LIST(I),
733 PRIMARY_KEY_ID1_LIST(I),
734 NUM_COL2_ORIG_LIST(I),
735 NUM_COL2_NEW_LIST(I),
736 'U',
737 req_id,
738 hz_utility_pub.CREATED_BY,
739 hz_utility_pub.CREATION_DATE,
740 hz_utility_pub.LAST_UPDATE_LOGIN,
741 hz_utility_pub.LAST_UPDATE_DATE,
742 hz_utility_pub.LAST_UPDATED_BY);
743
744 END IF;
745
746 LogMessage(FND_LOG.LEVEL_STATEMENT,'Insertion of custAcctSites Completed');
747
748 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updating custAcctSites in LNS_LOAN_HEADERS Table ...');
749
750 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
751 UPDATE LNS_PARTICIPANTS yt SET
752 BILL_TO_ACCT_SITE_ID=NUM_COL2_NEW_LIST(I)
753 , LAST_UPDATE_DATE=SYSDATE
754 , last_updated_by=arp_standard.profile.user_id
755 , last_update_login=arp_standard.profile.last_update_login
756 WHERE PARTICIPANT_ID=PRIMARY_KEY_ID1_LIST(I);
757
758 l_count := l_count + SQL%ROWCOUNT;
759
760 LogMessage(FND_LOG.LEVEL_STATEMENT,'Updation of custAcctSites Completed');
761
762 IF l_last_fetch THEN
763 EXIT;
764 END IF;
765
766 END LOOP;
767 LogMessage(FND_LOG.LEVEL_STATEMENT,'Total processed ' || l_count || ' CUST_ACCT_SITES records');
768 arp_message.set_name('AR','AR_ROWS_UPDATED');
769 arp_message.set_token('NUM_ROWS',to_char(l_count));
770
771 END IF;
772
773 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' END -');
774
775 EXCEPTION
776 WHEN OTHERS THEN
777 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' EXCEPTION');
778 arp_message.set_line( 'MERGE_PARTICIPANTS_ACC');
779 RAISE;
780 END MERGE_PARTICIPANTS_ACC;
781
782
783 END LNS_MERGE_PKG;