[Home] [Help]
PACKAGE BODY: APPS.IEX_MERGE_PVT
Source
1 PACKAGE BODY IEX_MERGE_PVT as
2 /* $Header: iexvmrgb.pls 120.9.12020000.2 2012/08/30 05:18:26 snuthala ship $ */
3
4 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
5 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
7 /*-------------------------------------------------------------
8 |
9 | PROCEDURE
10 | SCORE_HISTORY_MERGE
11 | DESCRIPTION :
12 | Account merge procedure for the table, IEX_SCORE_HISTORIES
13 |
14 | NOTES:
15 | ******* Please delete these lines after modifications *******
16 | This account merge procedure was NOT generated using a perl script.
17 |
18 | ******************************
19 |
20 |--------------------------------------------------------------*/
21
22 PROCEDURE SCORE_HISTORY_MERGE (
23 req_id NUMBER,
24 set_num NUMBER,
25 process_mode VARCHAR2) IS
26
27 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
28 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
29 INDEX BY BINARY_INTEGER;
30 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
31
32 TYPE SCORE_HISTORY_ID_LIST_TYPE IS TABLE OF
33 IEX_SCORE_HISTORIES.SCORE_HISTORY_ID%TYPE
34 INDEX BY BINARY_INTEGER;
35 PRIMARY_KEY_ID_LIST SCORE_HISTORY_ID_LIST_TYPE;
36
37 TYPE SCORE_OBJECT_ID_LIST_TYPE IS TABLE OF
38 IEX_SCORE_HISTORIES.SCORE_OBJECT_ID%TYPE
39 INDEX BY BINARY_INTEGER;
40 NUM_COL1_ORIG_LIST SCORE_OBJECT_ID_LIST_TYPE;
41 NUM_COL1_NEW_LIST SCORE_OBJECT_ID_LIST_TYPE;
42 NUM_COL2_ORIG_LIST SCORE_OBJECT_ID_LIST_TYPE;
43 NUM_COL2_NEW_LIST SCORE_OBJECT_ID_LIST_TYPE;
44 NUM_COL3_ORIG_LIST SCORE_OBJECT_ID_LIST_TYPE;
45 NUM_COL3_NEW_LIST SCORE_OBJECT_ID_LIST_TYPE;
46
47 l_profile_val VARCHAR2(30);
48
49 CURSOR merged_records1 IS
50 SELECT distinct CUSTOMER_MERGE_HEADER_ID
51 ,SCORE_HISTORY_ID
52 ,SCORE_OBJECT_ID
53 FROM IEX_SCORE_HISTORIES yt, ra_customer_merges m
54 WHERE yt.SCORE_OBJECT_ID = m.DUPLICATE_ID AND
55 m.process_flag = 'N' AND
56 m.request_id = req_id AND
57 m.set_number = set_num AND
58 yt.SCORE_OBJECT_CODE = 'IEX_ACCOUNT';
59
60 CURSOR merged_records2 IS
61 SELECT distinct CUSTOMER_MERGE_HEADER_ID
62 ,SCORE_HISTORY_ID
63 ,SCORE_OBJECT_ID
64 FROM IEX_SCORE_HISTORIES yt, ra_customer_merges m
65 WHERE yt.SCORE_OBJECT_ID = m.DUPLICATE_ADDRESS_ID AND
66 m.process_flag = 'N' AND
67 m.request_id = req_id AND
68 m.set_number = set_num AND
69 yt.SCORE_OBJECT_CODE = 'IEX_ACCOUNT_SITE';
70
71 CURSOR merged_records3 IS
72 SELECT distinct CUSTOMER_MERGE_HEADER_ID
73 ,SCORE_HISTORY_ID
74 ,SCORE_OBJECT_ID
75 FROM IEX_SCORE_HISTORIES yt, ra_customer_merges m
76 WHERE yt.SCORE_OBJECT_ID = m.DUPLICATE_SITE_ID AND
77 m.process_flag = 'N' AND
78 m.request_id = req_id AND
79 m.set_number = set_num AND
80 yt.SCORE_OBJECT_CODE = 'IEX_BILLTO';
81
82 l_last_fetch BOOLEAN := FALSE;
83 l_count NUMBER;
84 l_acc_status_cnt Number; -- Added for bug 14492050 Snuthala
85
86 BEGIN
87
88 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
89 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.SCORE_HISTORY_MERGE BEGIN');
90 END IF;
91
92 IF process_mode='LOCK' THEN
93 NULL;
94 ELSE
95 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
96 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_SCORE_HISTORIES',FALSE);
97 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
98 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
99
100 /* process IEX_SCORE_HISTORIES.SCORE_OBJECT_ID.OBJECT_CODE='IEX_ACCOUNT' */
101 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
102 IEX_DEBUG_PUB.logMessage('IEX_SCORE_HISTORIES.SCORE_OBJECT_ID.OBJECT_CODE=IEX_ACCOUNT');
103 END IF;
104 open merged_records1;
105 LOOP
106 FETCH merged_records1 BULK COLLECT INTO
107 MERGE_HEADER_ID_LIST
108 , PRIMARY_KEY_ID_LIST
109 , NUM_COL1_ORIG_LIST
110 limit G_Batch_Size;
111 IF merged_records1%NOTFOUND THEN
112 l_last_fetch := TRUE;
113 END IF;
114
115 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
116 goto iex_score_account_site;
117 END IF;
118
119 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
120 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
121 END LOOP;
122 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
123 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
124 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
125 MERGE_LOG_ID,
126 TABLE_NAME,
127 MERGE_HEADER_ID,
128 PRIMARY_KEY_ID,
129 NUM_COL1_ORIG,
130 NUM_COL1_NEW,
131 VCHAR_COL1_ORIG,
132 VCHAR_COL1_NEW,
133 ACTION_FLAG,
134 REQUEST_ID,
135 CREATED_BY,
136 CREATION_DATE,
137 LAST_UPDATE_LOGIN,
138 LAST_UPDATE_DATE,
139 LAST_UPDATED_BY) VALUES
140 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
141 'IEX_SCORE_HISTORIES',
142 MERGE_HEADER_ID_LIST(I),
143 PRIMARY_KEY_ID_LIST(I),
144 NUM_COL1_ORIG_LIST(I),
145 NUM_COL1_NEW_LIST(I),
146 'IEX_ACCOUNT',
147 'IEX_ACCOUNT',
148 'U',
149 req_id,
150 hz_utility_pub.CREATED_BY,
151 hz_utility_pub.CREATION_DATE,
152 hz_utility_pub.LAST_UPDATE_LOGIN,
153 hz_utility_pub.LAST_UPDATE_DATE,
154 hz_utility_pub.LAST_UPDATED_BY);
155 END IF;
156
157 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
158 IEX_DEBUG_PUB.logMessage('Merging ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
159 END IF;
160 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
161 -- Changed for all loop to for loop to fix 14492050 Snuthala
162 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
163
164 select count(CUST_ACCOUNT_ID) into l_acc_status_cnt from hz_cust_accounts where CUST_ACCOUNT_ID = NUM_COL1_ORIG_LIST(I) and status = 'A';
165 if l_acc_status_cnt = 0 then
166
167 UPDATE IEX_SCORE_HISTORIES yt SET
168 SCORE_OBJECT_ID = NUM_COL1_NEW_LIST(I)
169 , LAST_UPDATE_DATE = SYSDATE
170 , last_updated_by = arp_standard.profile.user_id
171 , last_update_login = arp_standard.profile.last_update_login
172 , REQUEST_ID = req_id
173 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
174 , PROGRAM_ID = arp_standard.profile.program_id
175 , PROGRAM_UPDATE_DATE = SYSDATE
176 WHERE SCORE_HISTORY_ID=PRIMARY_KEY_ID_LIST(I);
177 l_count := l_count + SQL%ROWCOUNT;
178 end if;
179 end loop;
180 -- End for fix 14492050 Snuthala
181 IF l_last_fetch THEN
182 goto iex_score_account_site;
183 END IF;
184 END LOOP;
185
186 arp_message.set_name('AR','AR_ROWS_UPDATED');
187 arp_message.set_token('NUM_ROWS',to_char(l_count));
188
189 <<iex_score_account_site>>
190 /* process IEX_SCORE_HISTORIES.SCORE_OBJECT_ID where JTF_OBJECT_TYPE = 'IEX_ACCOUNT_SITE' */
191 MERGE_HEADER_ID_LIST.delete;
192 PRIMARY_KEY_ID_LIST.delete;
193 l_count := 0;
194 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
195 IEX_DEBUG_PUB.logMessage('IEX_SCORE_HISTORIES.SCORE_OBJECT_ID.OBJECT_CODE=IEX_ACCOUNT_SITE');
196 END IF;
197 open merged_records2;
198 LOOP
199 FETCH merged_records2 BULK COLLECT INTO
200 MERGE_HEADER_ID_LIST
201 ,PRIMARY_KEY_ID_LIST
202 ,NUM_COL2_ORIG_LIST
203 limit G_Batch_Size;
204 IF merged_records2%NOTFOUND THEN
205 l_last_fetch := TRUE;
206 END IF;
207
208 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
209 goto iex_score_account_site_use;
210 END IF;
211
212 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
213 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
214 END LOOP;
215
216 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
217 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
218 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
219 MERGE_LOG_ID,
220 TABLE_NAME,
221 MERGE_HEADER_ID,
222 PRIMARY_KEY_ID,
223 NUM_COL1_ORIG,
224 NUM_COL1_NEW,
225 VCHAR_COL1_ORIG,
226 VCHAR_COL1_NEW,
227 ACTION_FLAG,
228 REQUEST_ID,
229 CREATED_BY,
230 CREATION_DATE,
231 LAST_UPDATE_LOGIN,
232 LAST_UPDATE_DATE,
233 LAST_UPDATED_BY) VALUES
234 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
235 'IEX_SCORE_HISTORIES',
236 MERGE_HEADER_ID_LIST(I),
237 PRIMARY_KEY_ID_LIST(I),
238 NUM_COL1_ORIG_LIST(I),
239 NUM_COL1_NEW_LIST(I),
240 'IEX_ACCOUNT_SITE',
241 'IEX_ACCOUNT_SITE',
242 'U',
243 req_id,
244 hz_utility_pub.CREATED_BY,
245 hz_utility_pub.CREATION_DATE,
246 hz_utility_pub.LAST_UPDATE_LOGIN,
247 hz_utility_pub.LAST_UPDATE_DATE,
248 hz_utility_pub.LAST_UPDATED_BY);
249 END IF;
250
251 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
252 IEX_DEBUG_PUB.logMessage('Merging ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
253 END IF;
254 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
255 -- Changed for all loop to for loop to fix 14492050 Snuthala
256 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
257
258 select count(CUST_ACCT_site_id) into l_acc_status_cnt from hz_cust_site_uses_all where cust_acct_site_id = NUM_COL2_ORIG_LIST(I) and status = 'A';
259 if l_acc_status_cnt = 0 then
260 UPDATE IEX_SCORE_HISTORIES yt SET
261 SCORE_OBJECT_ID = NUM_COL2_NEW_LIST(I)
262 , LAST_UPDATE_DATE = SYSDATE
263 , last_updated_by = arp_standard.profile.user_id
264 , last_update_login = arp_standard.profile.last_update_login
265 , REQUEST_ID = req_id
266 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
267 , PROGRAM_ID = arp_standard.profile.program_id
268 , PROGRAM_UPDATE_DATE = SYSDATE
269 WHERE SCORE_HISTORY_ID=PRIMARY_KEY_ID_LIST(I);
270 l_count := l_count + SQL%ROWCOUNT;
271 end if;
272 end loop;
273 -- End for fix 14492050 Snuthala
274 IF l_last_fetch THEN
275 goto iex_score_account_site_use;
276 END IF;
277 END LOOP;
278
279 <<iex_score_account_site_use>>
280 /* process IEX_SCORE_HISTORIES.SCORE_OBJECT_ID where JTF_OBJECT_TYPE = 'IEX_BILLTO' */
281 MERGE_HEADER_ID_LIST.delete;
282 PRIMARY_KEY_ID_LIST.delete;
283 l_count := 0;
284 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
285 IEX_DEBUG_PUB.logMessage('IEX_SCORE_HISTORIES.SCORE_OBJECT_ID.OBJECT_CODE=IEX_BILLTO');
286 END IF;
287 open merged_records3;
288 LOOP
289 FETCH merged_records1 BULK COLLECT INTO
290 MERGE_HEADER_ID_LIST
291 , PRIMARY_KEY_ID_LIST
292 , NUM_COL3_ORIG_LIST
293 limit G_Batch_Size;
294 IF merged_records3%NOTFOUND THEN
295 l_last_fetch := TRUE;
296 END IF;
297
298 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
299 exit;
300 END IF;
301
302 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
303 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
304 END LOOP;
305 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
306 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
307 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
308 MERGE_LOG_ID,
309 TABLE_NAME,
310 MERGE_HEADER_ID,
311 PRIMARY_KEY_ID,
312 NUM_COL1_ORIG,
313 NUM_COL1_NEW,
314 VCHAR_COL1_ORIG,
315 VCHAR_COL1_NEW,
316 ACTION_FLAG,
317 REQUEST_ID,
318 CREATED_BY,
319 CREATION_DATE,
320 LAST_UPDATE_LOGIN,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY) VALUES
323 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
324 'IEX_SCORE_HISTORIES',
325 MERGE_HEADER_ID_LIST(I),
326 PRIMARY_KEY_ID_LIST(I),
327 NUM_COL3_ORIG_LIST(I),
328 NUM_COL3_NEW_LIST(I),
329 'IEX_BILLTO',
330 'IEX_BILLTO',
331 'U',
332 req_id,
333 hz_utility_pub.CREATED_BY,
334 hz_utility_pub.CREATION_DATE,
335 hz_utility_pub.LAST_UPDATE_LOGIN,
336 hz_utility_pub.LAST_UPDATE_DATE,
337 hz_utility_pub.LAST_UPDATED_BY);
338 END IF;
339
340 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
341 IEX_DEBUG_PUB.logMessage('Merging ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
342 END IF;
343 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
344 -- Changed for all loop to for loop to fix 14492050 Snuthala
345 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
346
347 select count(site_use_id) into l_acc_status_cnt from hz_cust_site_uses_all where site_use_id = NUM_COL3_ORIG_LIST(I) and status = 'A';
348 if l_acc_status_cnt = 0 then
349 UPDATE IEX_SCORE_HISTORIES yt SET
350 SCORE_OBJECT_ID = NUM_COL3_NEW_LIST(I)
351 , LAST_UPDATE_DATE = SYSDATE
352 , last_updated_by = arp_standard.profile.user_id
353 , last_update_login = arp_standard.profile.last_update_login
354 , REQUEST_ID = req_id
355 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
356 , PROGRAM_ID = arp_standard.profile.program_id
357 , PROGRAM_UPDATE_DATE = SYSDATE
358 WHERE SCORE_HISTORY_ID=PRIMARY_KEY_ID_LIST(I);
359 l_count := l_count + SQL%ROWCOUNT;
360 end if;
361 end loop;
362 -- End for fix 14492050 Snuthala
363 IF l_last_fetch THEN
364 EXIT;
365 END IF;
366 END LOOP;
367
368 arp_message.set_name('AR','AR_ROWS_UPDATED');
369 arp_message.set_token('NUM_ROWS',to_char(l_count));
370 END IF;
371 EXCEPTION
372 WHEN OTHERS THEN
373 arp_message.set_line('SCORE_HISTORY_MERGE');
374 RAISE;
375 END SCORE_HISTORY_MERGE;
376
377 /*-------------------------------------------------------------
378 |
379 | PROCEDURE
380 | DUNNING_MERGE
381 | DESCRIPTION :
382 | Account merge procedure for the table, IEX_DUNNINGS
383 |
384 | NOTES:
385 | ******* Please delete these lines after modifications *******
386 | This account merge procedure was NOT generated using a perl script.
387 |
388 |--------------------------------------------------------------*/
389 PROCEDURE DUNNING_MERGE (req_id NUMBER,
390 set_num NUMBER,
391 process_mode VARCHAR2) IS
392
393 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
394 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
395 INDEX BY BINARY_INTEGER;
396 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
397
398 TYPE DUNNING_ID_LIST_TYPE IS TABLE OF
399 IEX_DUNNINGS.DUNNING_ID%TYPE
400 INDEX BY BINARY_INTEGER;
401 PRIMARY_KEY_ID_LIST DUNNING_ID_LIST_TYPE;
402
403 TYPE DUNNING_OBJECT_ID_LIST_TYPE IS TABLE OF
404 IEX_DUNNINGS.DUNNING_OBJECT_ID%TYPE
405 INDEX BY BINARY_INTEGER;
406 NUM_COL1_ORIG_LIST DUNNING_OBJECT_ID_LIST_TYPE;
407 NUM_COL1_NEW_LIST DUNNING_OBJECT_ID_LIST_TYPE;
408 NUM_COL2_ORIG_LIST DUNNING_OBJECT_ID_LIST_TYPE;
409 NUM_COL2_NEW_LIST DUNNING_OBJECT_ID_LIST_TYPE;
410
411 l_profile_val VARCHAR2(30);
412
413 /* this cursor is for IEX_DUNNINGS.OBJECT_ID column update if Object is IEX_ACCOUNT */
414 CURSOR merged_records1 IS
415 SELECT distinct CUSTOMER_MERGE_HEADER_ID
416 ,DUNNING_ID
417 ,DUNNING_OBJECT_ID
418 FROM IEX_DUNNINGS yt, ra_customer_merges m
419 WHERE yt.DUNNING_OBJECT_ID = m.DUPLICATE_ID AND
420 m.process_flag = 'N' AND
421 m.request_id = req_id AND
422 m.set_number = set_num AND
423 yt.DUNNING_LEVEL = 'ACCOUNT';
424
425 /* this cursor is for IEX_DUNNINGS.DUNNING_OBJECT_ID column update if Object is 'BILL_TO' */
426 CURSOR merged_records2 IS
427 SELECT distinct CUSTOMER_MERGE_HEADER_ID
428 ,DUNNING_ID
429 ,DUNNING_OBJECT_ID
430 FROM IEX_DUNNINGS yt, ra_customer_merges m
431 WHERE yt.DUNNING_OBJECT_ID = m.DUPLICATE_SITE_ID AND
432 m.process_flag = 'N' AND
433 m.request_id = req_id AND
434 m.set_number = set_num AND
435 yt.DUNNING_LEVEL ='BILL_TO';
436 --yt.object_type = 'BILL_TO'; commented to fix 12746719 on 7/26/2011 by snuthala
437
438 l_last_fetch BOOLEAN := FALSE;
439 l_count NUMBER;
440 l_acc_status_cnt Number; -- Added for bug 14492050 Snuthala
441
442 BEGIN
443 IF process_mode='LOCK' THEN
444 NULL;
445 ELSE
446 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNING_MERGE BEGIN');
447 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
448 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNING_MERGE BEGIN');
449 IEX_DEBUG_PUB.logMessage('Input parameters:');
450 IEX_DEBUG_PUB.logMessage('req_id = ' || req_id);
451 IEX_DEBUG_PUB.logMessage('set_num = ' || set_num);
452 IEX_DEBUG_PUB.logMessage('process_mode = ' || process_mode);
453
454 END IF;
455
456 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
457 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_DUNNING',FALSE);
458 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
459 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
460 IEX_DEBUG_PUB.logMessage('l_profile_val = ' || l_profile_val);
461 /* process IEX_STRATEGIES.CUST_ACCOUNT_ID */
462 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
463 IEX_DEBUG_PUB.logMessage('IEX_DUNNING.DUNNING_OBJECT_ID');
464 END IF;
465 open merged_records1;
466 LOOP
467 FETCH merged_records1 BULK COLLECT INTO
468 MERGE_HEADER_ID_LIST
469 , PRIMARY_KEY_ID_LIST
470 , NUM_COL1_ORIG_LIST
471 limit G_Batch_Size;
472
473 IF merged_records1%NOTFOUND THEN
474
475 l_last_fetch := TRUE;
476
477 END IF;
478
479 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
480 IEX_DEBUG_PUB.logMessage('MERGE_HEADER_ID_LIST.COUNT is 0');
481 goto iex_account;
482 END IF;
483
484 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
485 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
486
487 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNING_MERGE merged_records1 old : '||NUM_COL1_ORIG_LIST(I)||' new : '||NUM_COL1_NEW_LIST(I));
488
489 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNING_MERGE merged_records1 dunning id : '||PRIMARY_KEY_ID_LIST(I));
490
491
492 END LOOP;
493
494 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
495 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
496
497 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
498 MERGE_LOG_ID,
499 TABLE_NAME,
500 MERGE_HEADER_ID,
501 PRIMARY_KEY_ID,
502 NUM_COL1_ORIG,
503 NUM_COL1_NEW,
504 VCHAR_COL1_ORIG,
505 VCHAR_COL1_NEW,
506 ACTION_FLAG,
507 REQUEST_ID,
508 CREATED_BY,
509 CREATION_DATE,
510 LAST_UPDATE_LOGIN,
511 LAST_UPDATE_DATE,
512 LAST_UPDATED_BY)
513 VALUES
514 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
515 'IEX_DUNNINGS',
516 MERGE_HEADER_ID_LIST(I),
517 PRIMARY_KEY_ID_LIST(I),
518 NUM_COL1_ORIG_LIST(I),
519 NUM_COL1_NEW_LIST(I),
520 'ACCOUNT',
521 'ACCOUNT',
522 'U',
523 req_id,
524 hz_utility_pub.CREATED_BY,
525 hz_utility_pub.CREATION_DATE,
526 hz_utility_pub.LAST_UPDATE_LOGIN,
527 hz_utility_pub.LAST_UPDATE_DATE,
528 hz_utility_pub.LAST_UPDATED_BY);
529 END IF;
530
531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
532 IEX_DEBUG_PUB.logMessage('Merging mergerecord1 : ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
533
534
535 END IF;
536
537 -- FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
538 -- Changed for all loop to for loop to fix 14492050 Snuthala
539 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
540
541 select count(CUST_ACCOUNT_ID) into l_acc_status_cnt from hz_cust_accounts where CUST_ACCOUNT_ID = NUM_COL1_ORIG_LIST(I) and status = 'A';
542 if l_acc_status_cnt = 0 then
543 UPDATE IEX_DUNNINGS yt SET
544 DUNNING_OBJECT_ID = NUM_COL1_NEW_LIST(I)
545 , LAST_UPDATE_DATE = SYSDATE
546 , last_updated_by = arp_standard.profile.user_id
547 , last_update_login = arp_standard.profile.last_update_login
548 , REQUEST_ID = req_id
549 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
550 , PROGRAM_ID = arp_standard.profile.program_id
551 , PROGRAM_UPDATE_DATE = SYSDATE
552 WHERE DUNNING_ID=PRIMARY_KEY_ID_LIST(I);
553 -- WHERE DUNNING_OBJECT_ID=PRIMARY_KEY_ID_LIST(I); commented to fix 12746719 on 7/26/2011 by snuthala
554
555 l_count := l_count + SQL%ROWCOUNT;
556 end if;
557 end loop;
558 -- End for fix 14492050 Snuthala
559 IF l_last_fetch THEN
560 goto iex_account;
561 END IF;
562 END LOOP;
563
564
565 <<iex_account>>
566 arp_message.set_name('AR','AR_ROWS_UPDATED');
567 arp_message.set_token('NUM_ROWS',to_char(l_count));
568
569
570 /* process IEX_DUNNINGS.DUNNING_OBJECT_ID where DUNNING_LEVEL = 'BILLTO' */
571 MERGE_HEADER_ID_LIST.delete;
572 PRIMARY_KEY_ID_LIST.delete;
573 l_count := 0;
574 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
575 IEX_DEBUG_PUB.logMessage('IEX_DUNNINGS.DUNNING_OBJECT_ID.TYPE=ACCOUNT END');
576
577 END IF;
578 open merged_records2;
579 LOOP
580 FETCH merged_records2 BULK COLLECT INTO
581 MERGE_HEADER_ID_LIST
582 , PRIMARY_KEY_ID_LIST
583 , NUM_COL2_ORIG_LIST
584 limit G_Batch_Size;
585
586 IF merged_records2%NOTFOUND THEN
587 IEX_DEBUG_PUB.logMessage('MERGE_HEADER_ID_LIST.COUNT for billto is 0');
588 l_last_fetch := TRUE;
589 END IF;
590
591 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
592 EXIT;
593 END IF;
594
595 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
596 --NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
597 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I)); -- 5874874 gnramasa 25-Apr-2007
598
599 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNING_MERGE merged_records2 old : '||NUM_COL2_ORIG_LIST(I)||' new : '||NUM_COL2_NEW_LIST(I));
600
601 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNING_MERGE merged_records2 dunning id : '||PRIMARY_KEY_ID_LIST(I));
602
603 END LOOP;
604 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
605 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
606 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
607 MERGE_LOG_ID,
608 TABLE_NAME,
609 MERGE_HEADER_ID,
610 PRIMARY_KEY_ID,
611 NUM_COL2_ORIG,
612 NUM_COL2_NEW,
613 VCHAR_COL1_ORIG,
614 VCHAR_COL1_NEW,
615 ACTION_FLAG,
616 REQUEST_ID,
617 CREATED_BY,
618 CREATION_DATE,
619 LAST_UPDATE_LOGIN,
620 LAST_UPDATE_DATE,
621 LAST_UPDATED_BY)
622 VALUES
623 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
624 'IEX_DUNNINGS',
625 MERGE_HEADER_ID_LIST(I),
626 PRIMARY_KEY_ID_LIST(I),
627 NUM_COL2_ORIG_LIST(I),
628 NUM_COL2_NEW_LIST(I),
629 'BILL_TO',
630 'BILL_TO',
631 'U',
632 req_id,
633 hz_utility_pub.CREATED_BY,
634 hz_utility_pub.CREATION_DATE,
635 hz_utility_pub.LAST_UPDATE_LOGIN,
636 hz_utility_pub.LAST_UPDATE_DATE,
637 hz_utility_pub.LAST_UPDATED_BY);
638 END IF;
639
640 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
641 IEX_DEBUG_PUB.logMessage('Merging merge_record2' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
642
643 END IF;
644 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
645 -- Changed for all loop to for loop to fix 14492050 Snuthala
646 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
647
648 select count(site_use_id) into l_acc_status_cnt from hz_cust_site_uses_all where site_use_id = NUM_COL2_ORIG_LIST(I) and status = 'A';
649 if l_acc_status_cnt = 0 then
650 UPDATE IEX_DUNNINGS yt SET
651 DUNNING_OBJECT_ID = NUM_COL2_NEW_LIST(I)
652 , LAST_UPDATE_DATE = SYSDATE
653 , last_updated_by = arp_standard.profile.user_id
654 , last_update_login = arp_standard.profile.last_update_login
655 , REQUEST_ID = req_id
656 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
657 , PROGRAM_ID = arp_standard.profile.program_id
658 , PROGRAM_UPDATE_DATE = SYSDATE
659 WHERE DUNNING_ID=PRIMARY_KEY_ID_LIST(I);
660 l_count := l_count + SQL%ROWCOUNT;
661 end if;
662 end loop;
663 -- End for fix 14492050 Snuthala
664 IF l_last_fetch THEN
665 EXIT;
666 END IF;
667 END LOOP;
668
669 arp_message.set_name('AR','AR_ROWS_UPDATED');
670 arp_message.set_token('NUM_ROWS',to_char(l_count));
671 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
672
673 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DUNNNING_MERGE END');
674 END IF;
675
676 END IF;
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 FND_FILE.put_line( FND_FILE.LOG,'IEX_MERGE_PVT.DUNNNING_MERGE EXCEPTION');
681 FND_FILE.PUT_LINE(FND_FILE.LOG,'DUNNNING_MERGE : ' || SQLERRM);
682 arp_message.set_line( 'DUNNNING_MERGE');
683 RAISE;
684 END DUNNING_MERGE;
685
686 /*-------------------------------------------------------------
687 |
688 | PROCEDURE
689 | STRATEGY_MERGE
690 | DESCRIPTION :
691 | Account merge procedure for the table, IEX_STRATEGIES
692 |
693 | NOTES:
694 | ******* Please delete these lines after modifications *******
695 | This account merge procedure was NOT generated using a perl script.
696 |
697 |--------------------------------------------------------------*/
698 PROCEDURE STRATEGY_MERGE (
699 req_id NUMBER,
700 set_num NUMBER,
701 process_mode VARCHAR2) IS
702
703 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
704 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
705 INDEX BY BINARY_INTEGER;
706 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
707
708 TYPE STRATEGY_ID_LIST_TYPE IS TABLE OF
709 IEX_STRATEGIES.STRATEGY_ID%TYPE
710 INDEX BY BINARY_INTEGER;
711 PRIMARY_KEY_ID_LIST STRATEGY_ID_LIST_TYPE;
712
713 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
714 IEX_STRATEGIES.CUST_ACCOUNT_ID%TYPE
715 INDEX BY BINARY_INTEGER;
716 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
717 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
718
719 TYPE JTF_OBJECT_ID_LIST_TYPE IS TABLE OF
720 IEX_STRATEGIES.JTF_OBJECT_ID%TYPE
721 INDEX BY BINARY_INTEGER;
722 NUM_COL2_ORIG_LIST JTF_OBJECT_ID_LIST_TYPE;
723 NUM_COL2_NEW_LIST JTF_OBJECT_ID_LIST_TYPE;
724 NUM_COL3_ORIG_LIST JTF_OBJECT_ID_LIST_TYPE;
725 NUM_COL3_NEW_LIST JTF_OBJECT_ID_LIST_TYPE;
726
727
728 --Added for bug#6974531 by schekuri on 14-Aug-2008
729 TYPE STATUS_CODE_LIST_TYPE IS TABLE OF
730 IEX_STRATEGIES.STATUS_CODE%TYPE
731 INDEX BY BINARY_INTEGER;
732 STATUS_CODE_LIST STATUS_CODE_LIST_TYPE;
733 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
734 IEX_STRATEGIES.PARTY_ID%TYPE
735 INDEX BY BINARY_INTEGER;
736 PARTY_ID_LIST PARTY_ID_LIST_TYPE;
737
738
739 l_profile_val VARCHAR2(30);
740
741 /* this cursor is for IEX_STRATEGIES.CUST_ACCOUNT_ID column update */
742 CURSOR merged_records1 IS
743 SELECT distinct CUSTOMER_MERGE_HEADER_ID
744 ,yt.STRATEGY_ID
745 ,yt.CUST_ACCOUNT_ID
746 ,hca.party_id
747 ,yt.status_code --Added for bug#6974531 by schekuri on 14-Aug-2008
748 FROM IEX_STRATEGIES yt, ra_customer_merges m, hz_cust_accounts hca
749 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID AND
750 hca.cust_account_id = m.customer_id AND
751 m.process_flag = 'N' AND
752 m.request_id = req_id AND
753 m.set_number = set_num;
754
755
756 /* this cursor is for IEX_STRATEGIES.JTF_OBJECT_ID column update if Object is IEX_ACCOUNT */
757 CURSOR merged_records2 IS
758 SELECT distinct CUSTOMER_MERGE_HEADER_ID
759 ,STRATEGY_ID
760 ,JTF_OBJECT_ID
761 ,yt.status_code --Added for bug#6974531 by schekuri on 14-Aug-2008
762 FROM IEX_STRATEGIES yt, ra_customer_merges m
763 WHERE yt.JTF_OBJECT_ID = m.DUPLICATE_ID AND
764 m.process_flag = 'N' AND
765 m.request_id = req_id AND
766 m.set_number = set_num AND
767 yt.jtf_object_type = 'IEX_ACCOUNT';
768
769 /* this cursor is for IEX_STRATEGIES.JTF_OBJECT_ID column update if Object is IEX_BILLTO */
770 CURSOR merged_records3 IS
771 SELECT distinct CUSTOMER_MERGE_HEADER_ID
772 ,STRATEGY_ID
773 ,JTF_OBJECT_ID
774 ,yt.status_code --Added for bug#6974531 by schekuri on 14-Aug-2008
775 FROM IEX_STRATEGIES yt, ra_customer_merges m
776 WHERE yt.JTF_OBJECT_ID = m.DUPLICATE_SITE_ID AND
777 m.process_flag = 'N' AND
778 m.request_id = req_id AND
779 m.set_number = set_num AND
780 yt.jtf_object_type = 'IEX_BILLTO';
781
782 l_last_fetch BOOLEAN := FALSE;
783 l_count NUMBER;
784 l_acc_status_cnt Number; -- Added for bug 14492050 Snuthala
785
786 BEGIN
787 IF process_mode='LOCK' THEN
788 NULL;
789 ELSE
790 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
791 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.STRATEGY_MERGE BEGIN');
792 END IF;
793
794 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
795 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_STRATEGIES',FALSE);
796 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
797 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
798
799 /* process IEX_STRATEGIES.CUST_ACCOUNT_ID */
800 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
801 IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.CUST_ACCOUNT_ID');
802 END IF;
803 open merged_records1;
804 LOOP
805 FETCH merged_records1 BULK COLLECT INTO
806 MERGE_HEADER_ID_LIST
807 , PRIMARY_KEY_ID_LIST
808 , NUM_COL1_ORIG_LIST
809 , PARTY_ID_LIST
810 , STATUS_CODE_LIST --Added for bug#6974531 by schekuri on 14-Aug-2008
811 limit G_Batch_Size;
812
813 IF merged_records1%NOTFOUND THEN
814 l_last_fetch := TRUE;
815 END IF;
816
817 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
818 goto iex_account;
819 END IF;
820
821 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
822 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
823 END LOOP;
824
825 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
826 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
827 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
828 MERGE_LOG_ID,
829 TABLE_NAME,
830 MERGE_HEADER_ID,
831 PRIMARY_KEY_ID,
832 NUM_COL1_ORIG,
833 NUM_COL1_NEW,
834 ACTION_FLAG,
835 REQUEST_ID,
836 CREATED_BY,
837 CREATION_DATE,
838 LAST_UPDATE_LOGIN,
839 LAST_UPDATE_DATE,
840 LAST_UPDATED_BY)
841 VALUES
842 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
843 'IEX_STRATEGIES',
844 MERGE_HEADER_ID_LIST(I),
845 PRIMARY_KEY_ID_LIST(I),
846 NUM_COL1_ORIG_LIST(I),
847 NUM_COL1_NEW_LIST(I),
848 'U',
849 req_id,
850 hz_utility_pub.CREATED_BY,
851 hz_utility_pub.CREATION_DATE,
852 hz_utility_pub.LAST_UPDATE_LOGIN,
853 hz_utility_pub.LAST_UPDATE_DATE,
854 hz_utility_pub.LAST_UPDATED_BY);
855 END IF;
856
857 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
858 IEX_DEBUG_PUB.logMessage('Merging ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
859 END IF;
860 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
861 -- Changed for all loop to for loop to fix 14492050 Snuthala
862 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
863
864 select count(CUST_ACCOUNT_ID) into l_acc_status_cnt from hz_cust_accounts where CUST_ACCOUNT_ID = NUM_COL1_ORIG_LIST(I) and status = 'A';
865 if l_acc_status_cnt = 0 then
866
867 UPDATE IEX_STRATEGIES yt SET
868 CUST_ACCOUNT_ID = NUM_COL1_NEW_LIST(I)
869 , PARTY_ID = PARTY_ID_LIST(I)
870 , LAST_UPDATE_DATE = SYSDATE
871 , last_updated_by = arp_standard.profile.user_id
872 , last_update_login = arp_standard.profile.last_update_login
873 , REQUEST_ID = req_id
874 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
875 , PROGRAM_ID = arp_standard.profile.program_id
876 , PROGRAM_UPDATE_DATE = SYSDATE
877 WHERE STRATEGY_ID=PRIMARY_KEY_ID_LIST(I);
878 l_count := l_count + SQL%ROWCOUNT;
879 end if;
880 end loop;
881 -- End for fix 14492050 Snuthala
882 IF l_last_fetch THEN
883 goto iex_account;
884 END IF;
885 END LOOP;
886
887 arp_message.set_name('AR','AR_ROWS_UPDATED');
888 arp_message.set_token('NUM_ROWS',to_char(l_count));
889
890 <<iex_account>>
891 /* process IEX_STRATEGIES.JTF_OBJECT_ID where JTF_OBJECT_TYPE = 'IEX_ACCOUNT' */
892 MERGE_HEADER_ID_LIST.delete;
893 PRIMARY_KEY_ID_LIST.delete;
894 PARTY_ID_LIST.delete;
895 STATUS_CODE_LIST.delete; --Added for bug#6974531 by schekuri on 14-Aug-2008
896 l_count := 0;
897 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
898 IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.JTF_OBJECT_ID.TYPE=IEX_ACCOUNT');
899 END IF;
900 open merged_records2;
901 LOOP
902 FETCH merged_records2 BULK COLLECT INTO
903 MERGE_HEADER_ID_LIST
904 , PRIMARY_KEY_ID_LIST
905 , NUM_COL2_ORIG_LIST
906 , STATUS_CODE_LIST --Added for bug#6974531 by schekuri on 14-Aug-2008
907 limit G_Batch_Size;
908
909 IF merged_records2%NOTFOUND THEN
910 l_last_fetch := TRUE;
911 END IF;
912
913 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
914 goto iex_billto;
915 END IF;
916
917 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
918 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
919 END LOOP;
920 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
921 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
922 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
923 MERGE_LOG_ID,
924 TABLE_NAME,
925 MERGE_HEADER_ID,
926 PRIMARY_KEY_ID,
927 NUM_COL2_ORIG,
928 NUM_COL2_NEW,
929 VCHAR_COL1_ORIG,
930 VCHAR_COL1_NEW,
931 ACTION_FLAG,
932 REQUEST_ID,
933 CREATED_BY,
934 CREATION_DATE,
935 LAST_UPDATE_LOGIN,
936 LAST_UPDATE_DATE,
937 LAST_UPDATED_BY)
938 VALUES
939 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
940 'IEX_STRATEGIES',
941 MERGE_HEADER_ID_LIST(I),
942 PRIMARY_KEY_ID_LIST(I),
943 NUM_COL2_ORIG_LIST(I),
944 NUM_COL2_NEW_LIST(I),
945 'IEX_ACCOUNT',
946 'IEX_ACCOUNT',
947 'U',
948 req_id,
949 hz_utility_pub.CREATED_BY,
950 hz_utility_pub.CREATION_DATE,
951 hz_utility_pub.LAST_UPDATE_LOGIN,
952 hz_utility_pub.LAST_UPDATE_DATE,
953 hz_utility_pub.LAST_UPDATED_BY);
954 END IF;
955
956 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
957 IEX_DEBUG_PUB.logMessage('Merging ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
958 END IF;
959 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
960 -- Changed for all loop to for loop to fix 14492050 Snuthala
961 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT loop
962
963 select count(CUST_ACCOUNT_ID) into l_acc_status_cnt from hz_cust_accounts where CUST_ACCOUNT_ID = NUM_COL2_ORIG_LIST(I) and status = 'A';
964 if l_acc_status_cnt = 0 then
965
966 UPDATE IEX_STRATEGIES yt SET
967 JTF_OBJECT_ID = NUM_COL2_NEW_LIST(I)
968 , LAST_UPDATE_DATE = SYSDATE
969 , last_updated_by = arp_standard.profile.user_id
970 , last_update_login = arp_standard.profile.last_update_login
971 , REQUEST_ID = req_id
972 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
973 , PROGRAM_ID = arp_standard.profile.program_id
974 , PROGRAM_UPDATE_DATE = SYSDATE
975 WHERE STRATEGY_ID=PRIMARY_KEY_ID_LIST(I);
976 l_count := l_count + SQL%ROWCOUNT;
977 end if;
978 end loop;
979 -- End for fix 14492050 Snuthala
980 -- Begin Bug #6652858 bibeura 11-Dec-2007
981 for I in MERGE_HEADER_ID_LIST.first..MERGE_HEADER_ID_LIST.last loop
982 select count(site_use_id) into l_acc_status_cnt from hz_cust_site_uses_all where site_use_id = NUM_COL2_ORIG_LIST(I) and status = 'A';
983 if l_acc_status_cnt = 0 then
984 --Added filter for bug#8663669 by snuthala on 23-07-2009 to cancel only open and onhold strategies
985 IF STATUS_CODE_LIST(I) in ('OPEN','ONHOLD') then
986 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
987 strategy_id => PRIMARY_KEY_ID_LIST(I),
988 status => 'CANCELLED' ) ;
989 end if;
990 end if;
991 end loop;
992 -- End Bug #6652858 bibeura 11-Dec-2007
993 IF l_last_fetch THEN
994 goto iex_billto;
995 END IF;
996 END LOOP;
997
998 arp_message.set_name('AR','AR_ROWS_UPDATED');
999 arp_message.set_token('NUM_ROWS',to_char(l_count));
1000
1001 <<iex_billto>>
1002
1003 /* process IEX_STRATEGIES.JTF_OBJECT_ID where JTF_OBJECT_TYPE = 'IEX_BILLTO' */
1004 MERGE_HEADER_ID_LIST.delete;
1005 PRIMARY_KEY_ID_LIST.delete;
1006 STATUS_CODE_LIST.delete; --Added for bug#6974531 by schekuri on 14-Aug-2008
1007 l_count := 0;
1008 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1009 IEX_DEBUG_PUB.logMessage('IEX_STRATEGIES.JTF_OBJECT_ID.TYPE=IEX_BILLTO');
1010 END IF;
1011 open merged_records3;
1012 LOOP
1013 FETCH merged_records3 BULK COLLECT INTO
1014 MERGE_HEADER_ID_LIST
1015 , PRIMARY_KEY_ID_LIST
1016 , NUM_COL3_ORIG_LIST
1017 , STATUS_CODE_LIST --Added for bug#6974531 by schekuri on 14-Aug-2008
1018 limit G_Batch_Size;
1019
1020 IF merged_records3%NOTFOUND THEN
1021 l_last_fetch := TRUE;
1022 END IF;
1023
1024 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1025 exit;
1026 END IF;
1027
1028 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1029 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
1030 END LOOP;
1031 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1032 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1033 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1034 MERGE_LOG_ID,
1035 TABLE_NAME,
1036 MERGE_HEADER_ID,
1037 PRIMARY_KEY_ID,
1038 NUM_COL2_ORIG,
1039 NUM_COL2_NEW,
1040 VCHAR_COL1_ORIG,
1041 VCHAR_COL1_NEW,
1042 ACTION_FLAG,
1043 REQUEST_ID,
1044 CREATED_BY,
1045 CREATION_DATE,
1046 LAST_UPDATE_LOGIN,
1047 LAST_UPDATE_DATE,
1048 LAST_UPDATED_BY)
1049 VALUES (
1050 HZ_CUSTOMER_MERGE_LOG_s.nextval,
1051 'IEX_STRATEGIES',
1052 MERGE_HEADER_ID_LIST(I),
1053 PRIMARY_KEY_ID_LIST(I),
1054 NUM_COL3_ORIG_LIST(I),
1055 NUM_COL3_NEW_LIST(I),
1056 'IEX_BILLTO',
1057 'IEX_BILLTO',
1058 'U',
1059 req_id,
1060 hz_utility_pub.CREATED_BY,
1061 hz_utility_pub.CREATION_DATE,
1062 hz_utility_pub.LAST_UPDATE_LOGIN,
1063 hz_utility_pub.LAST_UPDATE_DATE,
1064 hz_utility_pub.LAST_UPDATED_BY);
1065 END IF;
1066
1067 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1068 IEX_DEBUG_PUB.logMessage('Merging ' || MERGE_HEADER_ID_LIST.COUNT || ' Records');
1069 END IF;
1070 --FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1071 -- Changed for all loop to for loop to fix 14492050 Snuthala
1072 for I in MERGE_HEADER_ID_LIST.first..MERGE_HEADER_ID_LIST.last loop
1073 select count(site_use_id) into l_acc_status_cnt from hz_cust_site_uses_all where site_use_id = NUM_COL3_ORIG_LIST(I) and status = 'A';
1074 if l_acc_status_cnt = 0 then
1075 UPDATE IEX_STRATEGIES yt SET
1076 -- CUST_ACCOUNT_ID = NUM_COL3_NEW_LIST(I) Updated for bug#6974531 by schekuri on 14-Aug-2008
1077 JTF_OBJECT_ID = NUM_COL3_NEW_LIST(I)
1078 , CUSTOMER_SITE_USE_ID = NUM_COL3_NEW_LIST(I)
1079 , LAST_UPDATE_DATE = SYSDATE
1080 , last_updated_by = arp_standard.profile.user_id
1081 , last_update_login = arp_standard.profile.last_update_login
1082 , REQUEST_ID = req_id
1083 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
1084 , PROGRAM_ID = arp_standard.profile.program_id
1085 , PROGRAM_UPDATE_DATE = SYSDATE
1086 WHERE STRATEGY_ID = PRIMARY_KEY_ID_LIST(I);
1087 l_count := l_count + SQL%ROWCOUNT;
1088 end if;
1089 end loop;
1090 -- End for fix 14492050 Snuthala
1091 -- Begin Bug #6652858 bibeura 11-Dec-2007
1092 for I in MERGE_HEADER_ID_LIST.first..MERGE_HEADER_ID_LIST.last loop
1093 --Added filter for bug#6974531 by schekuri on 14-Aug-2008 to cancel only open and onhold strategies
1094 IF STATUS_CODE_LIST(I) in ('OPEN','ONHOLD') then
1095 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1096 strategy_id => PRIMARY_KEY_ID_LIST(I),
1097 status => 'CANCELLED' ) ;
1098 end if;
1099 end loop;
1100 -- End Bug #6652858 bibeura 11-Dec-2007
1101 IF l_last_fetch THEN
1102 EXIT;
1103 END IF;
1104 END LOOP;
1105
1106 arp_message.set_name('AR','AR_ROWS_UPDATED');
1107 arp_message.set_token('NUM_ROWS',to_char(l_count));
1108
1109 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1110 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.STRATEGY_MERGE END');
1111 END IF;
1112
1113 END IF;
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 arp_message.set_line( 'STRATEGY_MERGE');
1117 RAISE;
1118 END STRATEGY_MERGE;
1119
1120
1121 PROCEDURE PROMISE_MERGE (
1122 req_id NUMBER,
1123 set_num NUMBER,
1124 process_mode VARCHAR2) IS
1125
1126 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1127 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1128 INDEX BY BINARY_INTEGER;
1129 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1130
1131 TYPE PROMISE_DETAIL_ID_LIST_TYPE IS TABLE OF
1132 IEX_PROMISE_DETAILS.PROMISE_DETAIL_ID%TYPE
1133 INDEX BY BINARY_INTEGER;
1134 PRIMARY_KEY_ID_LIST PROMISE_DETAIL_ID_LIST_TYPE;
1135
1136 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
1137 IEX_PROMISE_DETAILS.CUST_ACCOUNT_ID%TYPE
1138 INDEX BY BINARY_INTEGER;
1139 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1140 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1141
1142 l_profile_val VARCHAR2(30);
1143 -- Changed cusrsor to join with ar_payment_schedules_all ps,iex_delinquencies_all del to fix 14492050 Snuthala
1144 CURSOR merged_records IS
1145 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
1146 ,yt.PROMISE_DETAIL_ID
1147 ,yt.CUST_ACCOUNT_ID
1148 FROM IEX_PROMISE_DETAILS yt, ra_customer_merges m,ar_payment_schedules_all ps,iex_delinquencies_all del
1149 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID AND
1150 m.process_flag = 'N' AND
1151 m.request_id = req_id AND
1152 m.set_number = set_num AND
1153 yt.delinquency_id = del.delinquency_id AND
1154 del.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID AND
1155 yt.CUST_ACCOUNT_ID <> ps.customer_id;
1156
1157 l_last_fetch BOOLEAN := FALSE;
1158 l_count NUMBER;
1159 BEGIN
1160 IF process_mode='LOCK' THEN
1161 NULL;
1162 ELSE
1163 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1164 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_PROMISE_DETAILS',FALSE);
1165 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1166 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1167
1168 open merged_records;
1169 LOOP
1170 FETCH merged_records BULK COLLECT INTO
1171 MERGE_HEADER_ID_LIST
1172 , PRIMARY_KEY_ID_LIST
1173 , NUM_COL1_ORIG_LIST
1174 limit G_Batch_Size;
1175
1176 IF merged_records%NOTFOUND THEN
1177 l_last_fetch := TRUE;
1178 END IF;
1179
1180 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1181 exit;
1182 END IF;
1183
1184 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1185 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1186 END LOOP;
1187
1188 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1189 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1190 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1191 MERGE_LOG_ID,
1192 TABLE_NAME,
1193 MERGE_HEADER_ID,
1194 PRIMARY_KEY_ID,
1195 NUM_COL1_ORIG,
1196 NUM_COL1_NEW,
1197 ACTION_FLAG,
1198 REQUEST_ID,
1199 CREATED_BY,
1200 CREATION_DATE,
1201 LAST_UPDATE_LOGIN,
1202 LAST_UPDATE_DATE,
1203 LAST_UPDATED_BY)
1204 VALUES
1205 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
1206 'IEX_PROMISE_DETAILS',
1207 MERGE_HEADER_ID_LIST(I),
1208 PRIMARY_KEY_ID_LIST(I),
1209 NUM_COL1_ORIG_LIST(I),
1210 NUM_COL1_NEW_LIST(I),
1211 'U',
1212 req_id,
1213 hz_utility_pub.CREATED_BY,
1214 hz_utility_pub.CREATION_DATE,
1215 hz_utility_pub.LAST_UPDATE_LOGIN,
1216 hz_utility_pub.LAST_UPDATE_DATE,
1217 hz_utility_pub.LAST_UPDATED_BY);
1218
1219 END IF;
1220 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1221 UPDATE IEX_PROMISE_DETAILS yt SET
1222 CUST_ACCOUNT_ID = NUM_COL1_NEW_LIST(I)
1223 , LAST_UPDATE_DATE = SYSDATE
1224 , last_updated_by = arp_standard.profile.user_id
1225 , last_update_login = arp_standard.profile.last_update_login
1226 , REQUEST_ID = req_id
1227 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
1228 , PROGRAM_ID = arp_standard.profile.program_id
1229 , PROGRAM_UPDATE_DATE = SYSDATE
1230 WHERE PROMISE_DETAIL_ID=PRIMARY_KEY_ID_LIST(I);
1231 l_count := l_count + SQL%ROWCOUNT;
1232 IF l_last_fetch THEN
1233 EXIT;
1234 END IF;
1235 END LOOP;
1236
1237 arp_message.set_name('AR','AR_ROWS_UPDATED');
1238 arp_message.set_token('NUM_ROWS',to_char(l_count));
1239 END IF;
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 arp_message.set_line( 'PROMISE_MERGE');
1243 RAISE;
1244 END PROMISE_MERGE;
1245
1246 PROCEDURE DELINQUENCY_MERGE (
1247 req_id NUMBER,
1248 set_num NUMBER,
1249 process_mode VARCHAR2) IS
1250
1251 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
1252 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
1253 INDEX BY BINARY_INTEGER;
1254 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
1255
1256 TYPE DELINQUENCY_ID_LIST_TYPE IS TABLE OF
1257 IEX_DELINQUENCIES_ALL.DELINQUENCY_ID%TYPE
1258 INDEX BY BINARY_INTEGER;
1259 PRIMARY_KEY_ID_LIST DELINQUENCY_ID_LIST_TYPE;
1260
1261 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
1262 IEX_DELINQUENCIES_ALL.CUST_ACCOUNT_ID%TYPE
1263 INDEX BY BINARY_INTEGER;
1264 TYPE CUSTOMER_SITE_USE_ID_LIST_TYPE IS TABLE OF
1265 IEX_DELINQUENCIES_ALL.CUSTOMER_SITE_USE_ID%TYPE
1266 INDEX BY BINARY_INTEGER;
1267 TYPE PARTY_ID_LIST_TYPE IS TABLE OF
1268 IEX_DELINQUENCIES_ALL.PARTY_CUST_ID%TYPE
1269 INDEX BY BINARY_INTEGER;
1270
1271 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1272 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
1273 NUM_COL2_ORIG_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
1274 NUM_COL2_NEW_LIST CUSTOMER_SITE_USE_ID_LIST_TYPE;
1275 PARTY_LIST PARTY_ID_LIST_TYPE;
1276
1277 l_profile_val VARCHAR2(30);
1278
1279 /* CURSOR merged_records IS
1280 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1281 ,DELINQUENCY_ID
1282 ,CUST_ACCOUNT_ID
1283 FROM IEX_DELINQUENCIES yt, ra_customer_merges m
1284 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID AND
1285 m.process_flag = 'N' AND
1286 m.request_id = req_id AND
1287 m.set_number = set_num;
1288
1289 CURSOR merged_records1 IS
1290 SELECT distinct CUSTOMER_MERGE_HEADER_ID
1291 ,DELINQUENCY_ID
1292 ,CUSTOMER_SITE_USE_ID
1293 FROM IEX_DELINQUENCIES yt, ra_customer_merges m
1294 WHERE yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_ID AND
1295 m.process_flag = 'N' AND
1296 m.request_id = req_id AND
1297 m.set_number = set_num;
1298 */
1299 -- Changed cusrsor to join with ar_payment_schedules_all ps to fix 14492050 snuthala
1300 CURSOR merged_records IS
1301 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
1302 ,yt.DELINQUENCY_ID
1303 ,yt.CUST_ACCOUNT_ID
1304 ,c.party_id
1305 FROM IEX_DELINQUENCIES_ALL yt, ra_customer_merges m, hz_cust_accounts c, ar_payment_schedules_all ps
1306 WHERE yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID AND
1307 m.process_flag = 'N' AND
1308 m.request_id = req_id AND
1309 m.set_number = set_num and
1310 m.customer_id = c.cust_account_id AND
1311 yt.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID AND
1312 yt.CUST_ACCOUNT_ID <> ps.CUSTOMER_ID ;
1313 -- Changed cusrsor to join with ar_payment_schedules_all ps to fix 14492050 Snuthala
1314 CURSOR merged_records1 IS
1315 SELECT distinct m.CUSTOMER_MERGE_HEADER_ID
1316 ,yt.DELINQUENCY_ID
1317 ,yt.CUSTOMER_SITE_USE_ID
1318 FROM IEX_DELINQUENCIES_ALL yt, ra_customer_merges m,ar_payment_schedules_all ar
1319 WHERE yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID AND
1320 m.process_flag = 'N' AND
1321 m.request_id = req_id AND
1322 m.set_number = set_num AND
1323 yt.PAYMENT_SCHEDULE_ID = ar.PAYMENT_SCHEDULE_ID AND
1324 yt.CUSTOMER_SITE_USE_ID <> ar.CUSTOMER_SITE_USE_ID ;
1325
1326 l_last_fetch BOOLEAN := FALSE;
1327 l_count NUMBER;
1328 BEGIN
1329
1330 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1331 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DELINQUENCY_MERGE BEGIN');
1332 IEX_DEBUG_PUB.logMessage('Input parameters:');
1333 IEX_DEBUG_PUB.logMessage('req_id = ' || req_id);
1334 IEX_DEBUG_PUB.logMessage('set_num = ' || set_num);
1335 IEX_DEBUG_PUB.logMessage('process_mode = ' || process_mode);
1336 END IF;
1337
1338 IF process_mode='LOCK' THEN
1339 NULL;
1340 ELSE
1341 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
1342 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','IEX_DELINQUENCIES',FALSE);
1343 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
1344 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
1345
1346 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1347 IEX_DEBUG_PUB.logMessage('l_profile_val = ' || l_profile_val);
1348 END IF;
1349
1350 l_count := 0;
1351 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1352 IEX_DEBUG_PUB.logMessage('Searching for ACCOUNT records...');
1353 END IF;
1354
1355 /* merging cust_account_id */
1356 open merged_records;
1357 LOOP
1358 FETCH merged_records BULK COLLECT INTO
1359 MERGE_HEADER_ID_LIST
1360 , PRIMARY_KEY_ID_LIST
1361 , NUM_COL1_ORIG_LIST
1362 , PARTY_LIST
1363 limit G_Batch_Size;
1364
1365 IF merged_records%NOTFOUND THEN
1366 l_last_fetch := TRUE;
1367 END IF;
1368
1369 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1370 IEX_DEBUG_PUB.logMessage('Fetched ' || MERGE_HEADER_ID_LIST.COUNT || ' records');
1371 END IF;
1372
1373 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1374 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1375 IEX_DEBUG_PUB.logMessage('Exiting fetch');
1376 END IF;
1377 goto iex_delinquency_acc_site_use;
1378 END IF;
1379
1380 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1381 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
1382 END LOOP;
1383
1384 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1385 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1386 IEX_DEBUG_PUB.logMessage('Inserting into HZ_CUSTOMER_MERGE_LOG...');
1387 END IF;
1388 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1389 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1390 MERGE_LOG_ID,
1391 TABLE_NAME,
1392 MERGE_HEADER_ID,
1393 PRIMARY_KEY_ID,
1394 NUM_COL1_ORIG,
1395 NUM_COL1_NEW,
1396 ACTION_FLAG,
1397 REQUEST_ID,
1398 CREATED_BY,
1399 CREATION_DATE,
1400 LAST_UPDATE_LOGIN,
1401 LAST_UPDATE_DATE,
1402 LAST_UPDATED_BY)
1403 VALUES
1404 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
1405 'IEX_DELINQUENCIES_ALL',
1406 MERGE_HEADER_ID_LIST(I),
1407 PRIMARY_KEY_ID_LIST(I),
1408 NUM_COL1_ORIG_LIST(I),
1409 NUM_COL1_NEW_LIST(I),
1410 'U',
1411 req_id,
1412 hz_utility_pub.CREATED_BY,
1413 hz_utility_pub.CREATION_DATE,
1414 hz_utility_pub.LAST_UPDATE_LOGIN,
1415 hz_utility_pub.LAST_UPDATE_DATE,
1416 hz_utility_pub.LAST_UPDATED_BY);
1417
1418 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1419 IEX_DEBUG_PUB.logMessage('...done');
1420 END IF;
1421
1422 END IF;
1423 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1424 IEX_DEBUG_PUB.logMessage('Updating IEX_DELINQUENCIES_ALL...');
1425 END IF;
1426
1427 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1428 UPDATE IEX_DELINQUENCIES_ALL yt SET
1429 CUST_ACCOUNT_ID = NUM_COL1_NEW_LIST(I)
1430 , PARTY_CUST_ID = PARTY_LIST(I)
1431 , LAST_UPDATE_DATE = SYSDATE
1432 , last_updated_by = arp_standard.profile.user_id
1433 , last_update_login = arp_standard.profile.last_update_login
1434 , REQUEST_ID = req_id
1435 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
1436 , PROGRAM_ID = arp_standard.profile.program_id
1437 , PROGRAM_UPDATE_DATE = SYSDATE
1438 WHERE DELINQUENCY_ID=PRIMARY_KEY_ID_LIST(I);
1439 l_count := l_count + SQL%ROWCOUNT;
1440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1441 IEX_DEBUG_PUB.logMessage('...done');
1442 END IF;
1443
1444 IF l_last_fetch THEN
1445 goto iex_delinquency_acc_site_use;
1446 END IF;
1447 END LOOP;
1448
1449 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1450 IEX_DEBUG_PUB.logMessage('Total processed ' || l_count || ' ACCOUNT records');
1451 END IF;
1452
1453 arp_message.set_name('AR','AR_ROWS_UPDATED');
1454 arp_message.set_token('NUM_ROWS',to_char(l_count));
1455
1456 <<iex_delinquency_acc_site_use>>
1457 /* merging CUSTOMER_SITE_USE_ID */
1458
1459 MERGE_HEADER_ID_LIST.delete;
1460 PRIMARY_KEY_ID_LIST.delete;
1461 l_count := 0;
1462
1463 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1464 IEX_DEBUG_PUB.logMessage('Searching for CUSTOMER_SITE_USE_ID records...');
1465 END IF;
1466
1467 open merged_records1;
1468 LOOP
1469 FETCH merged_records1 BULK COLLECT INTO
1470 MERGE_HEADER_ID_LIST
1471 , PRIMARY_KEY_ID_LIST
1472 , NUM_COL2_ORIG_LIST
1473 limit G_Batch_Size;
1474
1475 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1476 IEX_DEBUG_PUB.logMessage('Fetched ' || MERGE_HEADER_ID_LIST.COUNT || ' records');
1477 END IF;
1478
1479 IF merged_records1%NOTFOUND THEN
1480 l_last_fetch := TRUE;
1481 END IF;
1482
1483 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
1484 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1485 IEX_DEBUG_PUB.logMessage('Exiting fetch');
1486 END IF;
1487 exit;
1488 END IF;
1489
1490 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
1491 --NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL2_ORIG_LIST(I));
1492 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
1493 END LOOP;
1494
1495 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1496
1497 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1498 IEX_DEBUG_PUB.logMessage('Inserting into HZ_CUSTOMER_MERGE_LOG...');
1499 END IF;
1500
1501 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1502 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1503 MERGE_LOG_ID,
1504 TABLE_NAME,
1505 MERGE_HEADER_ID,
1506 PRIMARY_KEY_ID,
1507 NUM_COL2_ORIG,
1508 NUM_COL2_NEW,
1509 ACTION_FLAG,
1510 REQUEST_ID,
1511 CREATED_BY,
1512 CREATION_DATE,
1513 LAST_UPDATE_LOGIN,
1514 LAST_UPDATE_DATE,
1515 LAST_UPDATED_BY)
1516 VALUES
1517 (HZ_CUSTOMER_MERGE_LOG_s.nextval,
1518 'IEX_DELINQUENCIES_ALL',
1519 MERGE_HEADER_ID_LIST(I),
1520 PRIMARY_KEY_ID_LIST(I),
1521 NUM_COL2_ORIG_LIST(I),
1522 NUM_COL2_NEW_LIST(I),
1523 'U',
1524 req_id,
1525 hz_utility_pub.CREATED_BY,
1526 hz_utility_pub.CREATION_DATE,
1527 hz_utility_pub.LAST_UPDATE_LOGIN,
1528 hz_utility_pub.LAST_UPDATE_DATE,
1529 hz_utility_pub.LAST_UPDATED_BY);
1530
1531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1532 IEX_DEBUG_PUB.logMessage('...done');
1533 END IF;
1534
1535 END IF;
1536
1537 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1538 IEX_DEBUG_PUB.logMessage('Updating IEX_DELINQUENCIES_ALL...');
1539 END IF;
1540
1541 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
1542 UPDATE IEX_DELINQUENCIES_ALL yt SET
1543 CUSTOMER_SITE_USE_ID = NUM_COL2_NEW_LIST(I)
1544 , LAST_UPDATE_DATE = SYSDATE
1545 , last_updated_by = arp_standard.profile.user_id
1546 , last_update_login = arp_standard.profile.last_update_login
1547 , REQUEST_ID = req_id
1548 , PROGRAM_APPLICATION_ID = arp_standard.profile.program_application_id
1549 , PROGRAM_ID = arp_standard.profile.program_id
1550 , PROGRAM_UPDATE_DATE = SYSDATE
1551 WHERE DELINQUENCY_ID=PRIMARY_KEY_ID_LIST(I);
1552 l_count := l_count + SQL%ROWCOUNT;
1553 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1554 IEX_DEBUG_PUB.logMessage('...done');
1555 END IF;
1556 IF l_last_fetch THEN
1557 EXIT;
1558 END IF;
1559 END LOOP;
1560
1561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1562 IEX_DEBUG_PUB.logMessage('Total processed ' || l_count || ' CUSTOMER_SITE_USE_ID records');
1563 END IF;
1564
1565 arp_message.set_name('AR','AR_ROWS_UPDATED');
1566 arp_message.set_token('NUM_ROWS',to_char(l_count));
1567
1568 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1569 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.DELINQUENCY_MERGE END');
1570 END IF;
1571
1572 END IF;
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1576 IEX_DEBUG_PUB.logMessage('In IEX_MERGE_PVT.DELINQUENCY_MERGE exception');
1577 END IF;
1578 arp_message.set_line('DELINQUENCY_MERGE');
1579 RAISE;
1580 END DELINQUENCY_MERGE;
1581
1582 PROCEDURE MERGE_DELINQUENCY_PARTIES(p_entity_name IN VARCHAR2,
1583 p_from_id IN NUMBER,
1584 p_to_id IN OUT NOCOPY NUMBER,
1585 p_from_fk_id IN NUMBER,
1586 p_to_fk_id IN NUMBER,
1587 p_parent_entity IN VARCHAR2,
1588 p_batch_id IN NUMBER,
1589 p_batch_party_id IN NUMBER,
1590 x_return_status OUT NOCOPY VARCHAR2)
1591 IS
1592
1593 v_merged_to_id NUMBER;
1594 l_num_records NUMBER;
1595 l_merge_reason VARCHAR2(25);
1596
1597 -- Begin - 10/12/2005 - Andre Araujo - Add exception handling
1598 e_NullParameters EXCEPTION;
1599 -- End - 10/12/2005 - Andre Araujo - Add exception handling
1600
1601 BEGIN
1602 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1603 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: Begin');
1604 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_from_id: ' || p_from_id );
1605 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_to_id: ' || p_to_id );
1606 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_from_fk_id: ' || p_from_fk_id );
1607 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_to_fk_id: ' || p_to_fk_id );
1608 END IF;
1609
1610 x_return_status := FND_API.G_RET_STS_SUCCESS;
1611
1612 /* 1. Do all Validations */
1613
1614 /* Check the Merge reason. If Merge Reason is Duplicate Record then no validation is performed.
1615 Otherwise check if the resource is being used somewhere
1616 */
1617 SELECT merge_reason_code into l_merge_reason
1618 FROM hz_merge_batch
1619 WHERE batch_id = p_batch_id;
1620
1621 IF l_merge_reason = 'DUPLICATE' THEN
1622 NULL;
1623 ELSE
1624 NULL;
1625
1626 -- Begin - 10/12/2005 - Andre Araujo - Check if we received all the required parameters
1627 IF p_from_FK_id is null or p_to_fk_id is null THEN
1628 raise e_NullParameters;
1629 END IF;
1630 -- End - 10/12/2005 - Andre Araujo - Check if we received all the required parameters
1631
1632 /* Check if the delinquency is being used some where. If so, do not allow Merge */
1633 /*
1634 SELECT count(1) INTO l_num_records
1635 FROM IEX_DELINQUENCIES_ALL
1636 WHERE delinquency_id = p_from_id;
1637 IF l_num_records >= 1 THEN
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 FND_MESSAGE.SET_NAME('JTF','JTF_MERGE_NOTALLOWED');
1640 FND_MSG_PUB.ADD;
1641 RETURN;
1642 END IF;
1643 */
1644 END IF;
1645
1646
1647 /* 2. Perform the Merge Operation. */
1648
1649 /* If the Parent has NOT changed(i.e. Parent getting transferred)
1650 then nothing needs to be done. Set Merged To Id is same as Merged From Id
1651 and return
1652 */
1653 IF p_from_FK_id = p_to_FK_id THEN
1654 p_to_id := p_from_id;
1655 RETURN;
1656 END IF;
1657
1658 /* If the Parent has changed(i.e. Parent is getting merged),
1659 then transfer the dependent record to the new parent.
1660 Before transferring check if a similar dependent record exists on the new parent.
1661 If a duplicate exists then do not transfer and return the id of the duplicate record as the Merged To Id.
1662 */
1663
1664 /* begin raverma 07242001
1665 */
1666 -- do we really care if something is being "merged" or transferred?? i think not
1667 -- lets just update the table to reflect the new party_id
1668 UPDATE IEX_DELINQUENCIES_ALL
1669 SET party_cust_id = p_To_FK_id,
1670 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1671 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1672 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1673 request_id = HZ_UTILITY_V2PUB.request_id,
1674 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1675 program_id = HZ_UTILITY_V2PUB.program_id,
1676 program_update_date = sysdate
1677 WHERE party_cust_id = p_from_fk_id;
1678
1679 -- begin raverma 10232001 -- add this to update promise table
1680 UPDATE IEX_PROMISE_DETAILS
1681 SET Promise_Made_By = p_To_FK_ID,
1682 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1683 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1684 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1685 request_id = HZ_UTILITY_V2PUB.request_id,
1686 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1687 program_id = HZ_UTILITY_V2PUB.program_id,
1688 program_update_date = sysdate
1689 WHERE promise_made_by = p_from_fk_id;
1690
1691 /* Begin raverma 02032003 add new party_merge entities
1692 IEX_REPOSSESIONS
1693 iex_del_third_parties
1694 iex_case_contacts
1695 iex_cases_all_b
1696 iex_writeoffs
1697 iex_bankruptcies
1698 iex_litigations
1699 -- 02182002 add IEX_SCORE_HISTORIES
1700 IEX_STRATEGIES
1701 */
1702 UPDATE IEX_STRATEGIES
1703 SET JTF_OBJECT_ID = p_To_FK_ID,
1704 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1705 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1706 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1707 request_id = HZ_UTILITY_V2PUB.request_id,
1708 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1709 program_id = HZ_UTILITY_V2PUB.program_id,
1710 program_update_date = sysdate,
1711 -- Begin - 10/12/2005 - Andre Araujo - Need to update party_id also
1712 PARTY_ID = p_To_FK_ID
1713 -- End - 10/12/2005 - Andre Araujo - Need to update party_id also
1714 WHERE JTF_OBJECT_ID = p_from_fk_id AND
1715 JTF_OBJECT_TYPE = 'PARTY';
1716
1717 -- Begin - 10/12/2005 - Andre Araujo - Need to update party_id also
1718 UPDATE IEX_STRATEGIES
1719 SET last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1720 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1721 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1722 request_id = HZ_UTILITY_V2PUB.request_id,
1723 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1724 program_id = HZ_UTILITY_V2PUB.program_id,
1725 program_update_date = sysdate,
1726 PARTY_ID = p_To_FK_ID
1727 WHERE PARTY_ID = p_from_fk_id;
1728 -- End - 10/12/2005 - Andre Araujo - Need to update party_id also
1729
1730
1731 UPDATE IEX_SCORE_HISTORIES
1732 SET SCORE_OBJECT_ID = p_To_FK_ID,
1733 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1734 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1735 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1736 request_id = HZ_UTILITY_V2PUB.request_id,
1737 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1738 program_id = HZ_UTILITY_V2PUB.program_id,
1739 program_update_date = sysdate
1740 WHERE SCORE_OBJECT_ID = p_from_fk_id AND
1741 SCORE_OBJECT_CODE = 'PARTY';
1742
1743 UPDATE IEX_REPOSSESSIONS
1744 SET PARTY_ID = p_To_FK_ID,
1745 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1746 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1747 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1748 request_id = HZ_UTILITY_V2PUB.request_id,
1749 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1750 program_id = HZ_UTILITY_V2PUB.program_id,
1751 program_update_date = sysdate
1752 WHERE PARTY_ID = p_from_fk_id;
1753
1754 UPDATE IEX_REPOSSESSIONS
1755 SET REPLEVIN_ATTORNEY = p_To_FK_ID,
1756 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1757 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1758 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1759 request_id = HZ_UTILITY_V2PUB.request_id,
1760 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1761 program_id = HZ_UTILITY_V2PUB.program_id,
1762 program_update_date = sysdate
1763 WHERE REPLEVIN_ATTORNEY = p_from_fk_id;
1764
1765 UPDATE IEX_DEL_THIRD_PARTIES
1766 SET THIRD_PARTY_ID = p_To_FK_ID,
1767 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1768 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1769 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1770 request_id = HZ_UTILITY_V2PUB.request_id,
1771 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1772 program_id = HZ_UTILITY_V2PUB.program_id,
1773 program_update_date = sysdate
1774 WHERE THIRD_PARTY_ID = p_from_fk_id;
1775
1776 UPDATE IEX_CASE_CONTACTS
1777 SET CONTACT_PARTY_ID = p_To_FK_ID,
1778 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1779 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1780 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1781 request_id = HZ_UTILITY_V2PUB.request_id,
1782 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1783 program_id = HZ_UTILITY_V2PUB.program_id,
1784 program_update_date = sysdate
1785 WHERE CONTACT_PARTY_ID = p_from_fk_id;
1786
1787 UPDATE IEX_CASES_ALL_B
1788 SET PARTY_ID = p_To_FK_ID,
1789 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1790 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1791 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1792 request_id = HZ_UTILITY_V2PUB.request_id,
1793 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1794 program_id = HZ_UTILITY_V2PUB.program_id,
1795 program_update_date = sysdate
1796 WHERE PARTY_ID = p_from_fk_id;
1797
1798 UPDATE IEX_WRITEOFFS
1799 SET PARTY_ID = p_To_FK_ID,
1800 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1801 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1802 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1803 request_id = HZ_UTILITY_V2PUB.request_id,
1804 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1805 program_id = HZ_UTILITY_V2PUB.program_id,
1806 program_update_date = sysdate
1807 WHERE PARTY_ID = p_from_fk_id;
1808
1809 UPDATE IEX_BANKRUPTCIES
1810 SET PARTY_ID = p_To_FK_ID,
1811 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1812 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1813 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1814 request_id = HZ_UTILITY_V2PUB.request_id,
1815 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1816 program_id = HZ_UTILITY_V2PUB.program_id,
1817 program_update_date = sysdate
1818 WHERE PARTY_ID = p_from_fk_id;
1819
1820 UPDATE IEX_LITIGATIONS
1821 SET PARTY_ID = p_To_FK_ID,
1822 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
1823 last_updated_by = HZ_UTILITY_V2PUB.user_id,
1824 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
1825 request_id = HZ_UTILITY_V2PUB.request_id,
1826 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
1827 program_id = HZ_UTILITY_V2PUB.program_id,
1828 program_update_date = sysdate
1829 WHERE PARTY_ID = p_from_fk_id;
1830
1831 /* end raverma 02032003 */
1832
1833 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1834 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: End success!');
1835 END IF;
1836
1837 RETURN;
1838
1839 /*
1840 IF p_from_FK_id <> p_to_FK_id THEN
1841 BEGIN
1842 SELECT party_cust_id
1843 INTO v_merged_to_id
1844 FROM IEX_DELINQUENCIES_ALL
1845 WHERE party_cust_id = p_To_FK_id
1846 --and category = p_parent_entity_name
1847 --and resource_name = (select resource_name
1848 -- from JTF_RS_RESOURCE_EXTNS
1849 -- where resource_id = p_from_id)
1850 and rownum =1;
1851 EXCEPTION
1852 WHEN no_data_found THEN
1853 v_merged_to_id := NULL;
1854 END;
1855 END IF;
1856
1857 IF v_merged_to_id IS NULL THEN
1858 -- Duplicate Does Not exist. Therefore transfer
1859 UPDATE IEX_DELINQUENCIES_ALL
1860 SET party_cust_id = p_To_FK_id,
1861 last_update_date = hz_utility_pub.last_update_date,
1862 last_updated_by = hz_utility_pub.user_id,
1863 last_update_login = hz_utility_pub.last_update_login,
1864 --request_id = hz_utility_pub.request_id,
1865 --program_application_id = hz_utility_pub.program_application_id,
1866 program_id = hz_utility_pub.program_id
1867 --program_update_date = sysdate
1868 WHERE delinquency_id = p_from_id;
1869 RETURN;
1870
1871 END IF;
1872
1873 IF v_merged_to_id IS NOT NULL THEN
1874 /* Duplicate Exists. Therefore Merge */
1875 /*
1876 UPDATE IEX_DELINQUENCIES_ALL
1877 SET STATUS = 'CLOSED',
1878 last_update_date = hz_utility_pub.last_update_date,
1879 last_updated_by = hz_utility_pub.user_id,
1880 last_update_login = hz_utility_pub.last_update_login,
1881 --request_id = hz_utility_pub.request_id,
1882 --program_application_id = hz_utility_pub.program_application_id,
1883 program_id = hz_utility_pub.program_id
1884 --program_update_date = sysdate
1885 WHERE delinquency_id = p_from_id;
1886 p_to_id := v_merged_to_id;
1887
1888 RETURN;
1889 END IF;
1890 */
1891
1892 EXCEPTION
1893 -- Begin - 10/12/2005 - Andre Araujo - Check if we received all the required parameters
1894 When e_NullParameters THEN
1895 FND_MESSAGE.SET_NAME('IEX', 'IEX_API_ALL_NULL_PARAMETER');
1896 FND_MESSAGE.SET_TOKEN('API_NAME', 'MERGE_DELINQUENCY_PARTIES');
1897 FND_MESSAGE.SET_TOKEN('NULL_PARAM', null);
1898 FND_MSG_PUB.ADD;
1899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1900
1901 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: Null party_ids received!!!');
1902 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_from_id: ' || p_from_id );
1903 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_to_id: ' || p_to_id );
1904 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_from_fk_id: ' || p_from_fk_id );
1905 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: p_to_fk_id: ' || p_to_fk_id );
1906
1907 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: Null party_ids received!!!');
1908 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: p_from_id: ' || p_from_id );
1909 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: p_to_id: ' || p_to_id );
1910 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: p_from_fk_id: ' || p_from_fk_id );
1911 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: p_to_fk_id: ' || p_to_fk_id );
1912
1913
1914 -- End - 10/12/2005 - Andre Araujo - Check if we received all the required parameters
1915
1916 WHEN OTHERS THEN
1917 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1918 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1919 FND_MSG_PUB.ADD;
1920
1921 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: EXCEPTION!!!');
1922 IEX_DEBUG_PUB.LOGMESSAGE('MERGE_DELINQUENCY_PARTIES: ' || SQLERRM);
1923
1924 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: EXCEPTION!!!');
1925 FND_FILE.PUT_LINE(FND_FILE.LOG,'MERGE_DELINQUENCY_PARTIES: ' || SQLERRM);
1926
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1928
1929 END MERGE_DELINQUENCY_PARTIES;
1930
1931 /* Begin - Andre Araujo - 05/04/03 - Add Contact points and Address Merge */
1932
1933 PROCEDURE CASE_CONTACT_MERGE
1934 ( p_entity_name IN VARCHAR2
1935 ,p_from_id IN NUMBER
1936 ,p_to_id IN OUT NOCOPY NUMBER
1937 ,p_from_fk_id IN NUMBER
1938 ,p_to_fk_id IN NUMBER
1939 ,p_parent_entity_name IN VARCHAR2
1940 ,p_batch_id IN NUMBER
1941 ,p_batch_party_id IN NUMBER
1942 ,x_return_status IN OUT NOCOPY VARCHAR2
1943 ) is
1944 l_api_name CONSTANT VARCHAR2(30) := 'CASE_CONTACT_MERGE';
1945 l_api_version_number CONSTANT NUMBER := 1.0;
1946 l_merge_reason_code VARCHAR2(30);
1947 BEGIN
1948 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1949 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.CASE_CONTACT_MERGE BEGIN');
1950 END IF;
1951 x_return_status := FND_API.G_RET_STS_SUCCESS;
1952
1953 select merge_reason_code into l_merge_reason_code
1954 from HZ_MERGE_BATCH
1955 where batch_id = p_batch_id;
1956
1957 IF l_merge_reason_code = 'DUPLICATE' THEN
1958 -- ***************************************************************************
1959 -- if reason code is duplicate then allow the party merge to happen without
1960 -- any validations.
1961 -- ***************************************************************************
1962 null;
1963 ELSE
1964 -- ***************************************************************************
1965 -- if there are any validations to be done, include it in this section
1966 -- ***************************************************************************
1967 null;
1968 END IF;
1969
1970 -- ***************************************************************************
1971 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
1972 -- needs to be done. Set Merged To Id is same as Merged From Id and return
1973 -- ***************************************************************************
1974 if p_from_fk_id = p_to_fk_id then
1975 p_to_id := p_from_id;
1976 return;
1977 end if;
1978
1979 -- ***************************************************************************
1980 -- If the parent has changed(ie. Parent is getting merged) then transfer the
1981 -- dependent record to the new parent. Before transferring check if a similar
1982 -- dependent record exists on the new parent. If a duplicate exists then do
1983 -- not transfer and return the id of the duplicate record as the Merged To Id
1984 -- ***************************************************************************
1985
1986 -- ***************************************************************************
1987 -- Add your own logic if you need to take care of the following cases
1988 -- Check the if record duplicate if change party_id from merge-from
1989 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
1990 -- situation
1991 --
1992 -- customer_id address_id contact_id
1993 -- =========== ========== ==========
1994 -- 1200 1100
1995 -- 1300 1400
1996 --
1997 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
1998 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
1999 -- therefore, if changing 1200 to 1300 (customer_id)
2000 -- and 1100 to 1400 (address_id), then it will cause unique
2001 -- key violation assume that all other fields are the same
2002 -- So, please check if you need to check for record duplication
2003 -- ***************************************************************************
2004
2005 IF p_from_fk_id <> p_to_fk_id THEN
2006 BEGIN
2007 IF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
2008 UPDATE IEX_CASE_CONTACTS
2009 set address_id = p_to_fk_id,
2010 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
2011 last_updated_by = HZ_UTILITY_V2PUB.user_id,
2012 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
2013 request_id = HZ_UTILITY_V2PUB.request_id,
2014 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
2015 program_id = HZ_UTILITY_V2PUB.program_id,
2016 program_update_date = sysdate
2017 where address_id = p_from_fk_id;
2018 ELSIF p_parent_entity_name = 'HZ_CONTACT_POINTS' THEN -- merge contact_points
2019 UPDATE IEX_CASE_CONTACTS
2020 set phone_id = p_to_fk_id,
2021 last_update_date = HZ_UTILITY_V2PUB.last_update_date,
2022 last_updated_by = HZ_UTILITY_V2PUB.user_id,
2023 last_update_login = HZ_UTILITY_V2PUB.last_update_login,
2024 request_id = HZ_UTILITY_V2PUB.request_id,
2025 program_application_id = HZ_UTILITY_V2PUB.program_application_id,
2026 program_id = HZ_UTILITY_V2PUB.program_id,
2027 program_update_date = sysdate
2028 where phone_id = p_from_fk_id;
2029 END IF;
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2033 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.CASE_CONTACT_MERGE EXCEPTION:');
2034 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT' || '.' || l_api_name || ': ' || sqlerrm);
2035 END IF;
2036 arp_message.set_line('IEX_MERGE_PVT' || '.' || l_api_name || ': ' || sqlerrm);
2037 x_return_status := FND_API.G_RET_STS_ERROR;
2038 raise;
2039 END;
2040 END IF;
2041
2042 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2043 IEX_DEBUG_PUB.logMessage('IEX_MERGE_PVT.CASE_CONTACT_MERGE END');
2044 END IF;
2045
2046 END CASE_CONTACT_MERGE;
2047
2048 /* End - Andre Araujo - 05/04/03 - Add Contact points and Address Merge */
2049
2050
2051 END IEX_MERGE_PVT;