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