[Home] [Help]
PACKAGE BODY: APPS.HZ_ACCOUNT_MERGE_PUB
Source
4 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1 PACKAGE BODY HZ_ACCOUNT_MERGE_PUB AS
2 /*$Header: ARHCMRQB.pls 120.0.12020000.6 2013/03/01 07:11:25 vsegu noship $ */
3
5 TYPE CharList IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
6 TYPE PCharList IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
7
8 g_location_id NumberList;
9 g_site_use_location CharList;
10 g_primary_flag PCharList;
11 l_debug_prefix VARCHAR2(30) := 'AccountMerge';
12
13 FUNCTION is_ss_provided(
14 p_os IN VARCHAR2,
15 p_osr IN VARCHAR2
16 ) RETURN VARCHAR2 IS
17 BEGIN
18 IF((p_os is null or p_os = fnd_api.g_miss_char)
19 and (p_osr is null or p_osr = fnd_api.g_miss_char))THEN
20 RETURN 'N';
21 ELSE
22 RETURN 'Y';
23 END IF;
24 END is_ss_provided;
25
26 PROCEDURE validate_account(
27 p_account_id IN OUT NOCOPY NUMBER,
28 p_account_number IN OUT NOCOPY VARCHAR2,
29 p_account_os IN OUT NOCOPY VARCHAR2,
30 p_account_osr IN OUT NOCOPY VARCHAR2,
31 x_account_status OUT NOCOPY VARCHAR2,
32 x_party_id OUT NOCOPY NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2
36 ) IS
37
38
39 l_account_id NUMBER;
40 l_account_number VARCHAR2(30);
41 l_account_osr VARCHAR2(255);
42 l_ss_flag VARCHAR2(1);
43 l_owner_table_id NUMBER;
44 l_count NUMBER;
45
46 CURSOR get_account(c_account_id NUMBER) IS
47 SELECT cust_account_id, party_id, account_number, orig_system_reference, status
48 FROM HZ_CUST_ACCOUNTS
49 WHERE cust_account_id = c_account_id
50 AND status in ('A','I');
51
52
53 BEGIN
54
55 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
56 hz_utility_v2pub.debug(p_message=>'validate_account(+)',
57 p_prefix=>l_debug_prefix,
58 p_msg_level=>fnd_log.level_procedure);
59 END IF;
60
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 /* Validate account_id */
64
65 IF p_account_id is not null THEN
66
67 OPEN get_account(p_account_id);
68 FETCH get_account INTO l_account_id, x_party_id,l_account_number, l_account_osr, x_account_status;
69 CLOSE get_account;
70
71 IF l_account_id IS NULL THEN
72 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_ACCT_ID');
73 FND_MESSAGE.SET_TOKEN('ACCOUNT_ID', p_account_id);
74 FND_MSG_PUB.ADD();
75 RAISE fnd_api.g_exc_error;
76 END IF;
77 END IF;
78
79 /* Validate from account_number*/
80
81 IF p_account_number is not null THEN
82
83 IF l_account_number IS NULL THEN
84
85 BEGIN
86 /* Fetch account details using account_number */
87
88 SELECT cust_account_id, party_id, account_number, orig_system_reference , status
89 INTO l_account_id, x_party_id,l_account_number, l_account_osr, x_account_status
90 FROM HZ_CUST_ACCOUNTS
91 WHERE account_number = p_account_number
92 AND status in ('A','I');
93
94 EXCEPTION
95
96 WHEN OTHERS THEN
97
98 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_ACCT_NUM');
99 FND_MESSAGE.SET_TOKEN('ACCOUNT_NUMBER', p_account_number);
100 FND_MSG_PUB.ADD();
101 RAISE fnd_api.g_exc_error;
102
103 END;
104
105 /*Check if account_number is in sync*/
106
107 ELSIF l_account_number <> p_account_number THEN
108
109 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_ACCT_NUM');
110 FND_MESSAGE.SET_TOKEN('ACCOUNT_NUMBER', p_account_number);
111 FND_MSG_PUB.ADD();
112 RAISE fnd_api.g_exc_error;
113
114 END IF;
115
116 END IF;
117
118 /* Validate OS and OSR */
119
120 l_ss_flag := is_ss_provided(p_os => p_account_os,
121 p_osr =>p_account_osr);
122
123 IF (l_ss_flag = 'Y') THEN
124
125 -- Get how many rows return
126 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
127 p_orig_system => p_account_os,
128 p_orig_system_reference => p_account_osr,
129 p_owner_table_name => 'HZ_CUST_ACCOUNTS');
130
131 IF (l_count = 0) THEN
132
133 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
134 FND_MESSAGE.SET_TOKEN('OSOSR', p_account_os||','||p_account_osr);
135 FND_MSG_PUB.ADD();
136 RAISE fnd_api.g_exc_error;
137
138 ELSIF(l_count = 1) THEN
139 -- Get owner_table_id
140 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
141 p_orig_system => p_account_os,
142 p_orig_system_reference => p_account_osr,
143 p_owner_table_name => 'HZ_CUST_ACCOUNTS',
144 x_owner_table_id => l_owner_table_id,
145 x_return_status => x_return_status);
146
147 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
148 RAISE fnd_api.g_exc_error;
149 END IF;
150
151 IF l_account_id IS NOT NULL AND l_owner_table_id <> l_account_id THEN
152
153 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
154 FND_MESSAGE.SET_TOKEN('OSOSR', p_account_os||','||p_account_osr);
155 FND_MSG_PUB.ADD();
156 RAISE fnd_api.g_exc_error;
157
158 ELSIF l_account_id IS NULL THEN
159
160 OPEN get_account(l_owner_table_id);
161 FETCH get_account INTO l_account_id, x_party_id, l_account_number, l_account_osr, x_account_status;
162 CLOSE get_account;
163
164 IF l_account_id IS NULL THEN
165
166 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
167 FND_MESSAGE.SET_TOKEN('OSOSR', p_account_os||','||p_account_osr);
168 FND_MSG_PUB.ADD();
169 RAISE fnd_api.g_exc_error;
170 END IF;
171 END IF;
172
173 END IF; /*l_count */
174
175 END IF;/* l_ss_flag */
176
177 IF p_account_id IS NULL THEN
178
179 p_account_id := l_account_id;
180
181 END IF;
182
186
183 IF p_account_number IS NULL THEN
184
185 p_account_number := l_account_number;
187 END IF;
188
192
189 IF p_account_osr IS NULL THEN
190
191 p_account_osr := l_account_osr;
193 END IF;
194
195 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
196 hz_utility_v2pub.debug(p_message=>'validate_account(-)',
197 p_prefix=>l_debug_prefix,
198 p_msg_level=>fnd_log.level_procedure);
199 END IF;
200
201 EXCEPTION
202 WHEN fnd_api.g_exc_error THEN
203 x_return_status := fnd_api.g_ret_sts_error;
204
205 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
206 p_count => x_msg_count,
207 p_data => x_msg_data);
208
209 -- Debug info.
210 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
211 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
212 p_msg_data=>x_msg_data,
213 p_msg_type=>'ERROR',
214 p_msg_level=>fnd_log.level_error);
215 END IF;
216 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
217 hz_utility_v2pub.debug(p_message=>'validate_account(-)',
218 p_prefix=>l_debug_prefix,
219 p_msg_level=>fnd_log.level_procedure);
220 END IF;
221 WHEN fnd_api.g_exc_unexpected_error THEN
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223
224 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
225 p_count => x_msg_count,
226 p_data => x_msg_data);
227
228 -- Debug info.
229 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
230 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
231 p_msg_data=>x_msg_data,
232 p_msg_type=>'UNEXPECTED ERROR',
233 p_msg_level=>fnd_log.level_error);
234 END IF;
235 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
236 hz_utility_v2pub.debug(p_message=>'validate_account(-)',
237 p_prefix=>l_debug_prefix,
238 p_msg_level=>fnd_log.level_procedure);
239 END IF;
240 WHEN OTHERS THEN
241 x_return_status := fnd_api.g_ret_sts_unexp_error;
242
243 FND_MESSAGE.set_name('AR', 'HZ_API_OTHERS_EXCEP');
244 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
245 fnd_msg_pub.add;
246
247 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
248 p_count => x_msg_count,
252 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
249 p_data => x_msg_data);
250
251 -- Debug info.
253 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
254 p_msg_data=>x_msg_data,
255 p_msg_type=>'SQL ERROR',
256 p_msg_level=>fnd_log.level_error);
257 END IF;
258 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
259 hz_utility_v2pub.debug(p_message=>'validate_account(-)',
260 p_prefix=>l_debug_prefix,
261 p_msg_level=>fnd_log.level_procedure);
262 END IF;
263 END validate_account;
264
265 PROCEDURE validate_account_site(
266 p_account_id IN NUMBER,
267 p_account_site_id IN OUT NOCOPY NUMBER,
268 p_site_number IN OUT NOCOPY VARCHAR2,
269 p_site_os IN OUT NOCOPY VARCHAR2,
270 p_site_osr IN OUT NOCOPY VARCHAR2,
271 p_org_id IN OUT NOCOPY VARCHAR2,
272 x_location_id IN OUT NOCOPY NUMBER,
273 x_acct_site_status OUT NOCOPY VARCHAR2,
274 x_return_status OUT NOCOPY VARCHAR2,
275 x_msg_count OUT NOCOPY NUMBER,
276 x_msg_data OUT NOCOPY VARCHAR2
277 ) IS
278
279 CURSOR get_account_site (c_acct_site_id NUMBER) IS
280 SELECT cust_acct_site_id, cust_account_id, party_site_id, status, org_id, orig_system_reference
281 FROM HZ_CUST_ACCT_SITES_ALL
282 WHERE cust_acct_site_id = c_acct_site_id;
283
284 l_cust_acct_site_id NUMBER;
285 l_cust_account_id NUMBER;
286 l_party_site_id NUMBER;
287 l_ss_flag VARCHAR2(1);
288 l_owner_table_id NUMBER;
289 l_org_id NUMBER;
290 l_party_site_number VARCHAR2(30);
291 l_count NUMBER;
292 l_osr VARCHAR2(240);
293
294 BEGIN
295
296 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
297 hz_utility_v2pub.debug(p_message=>'validate_account_site(+)',
298 p_prefix=>l_debug_prefix,
299 p_msg_level=>fnd_log.level_procedure);
300 END IF;
301
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 /* Validate p_account_site_id */
305
306 IF p_account_site_id IS NOT NULL THEN
307
308 OPEN get_account_site(p_account_site_id);
309 FETCH get_account_site INTO l_cust_acct_site_id, l_cust_account_id, l_party_site_id, x_acct_site_status, l_org_id, l_osr;
310 CLOSE get_account_site;
311
312 -- Check the existence of cust_acct_site_id
313
314 IF l_cust_acct_site_id IS NULL THEN
315
316 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_ID');
317 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', p_account_site_id);
318 FND_MSG_PUB.ADD();
319 RAISE fnd_api.g_exc_error;
320 END IF;
321
322 -- Validate if acct site belongs to correct account
323
324 IF l_cust_account_id <> p_account_id THEN
325 FND_MESSAGE.SET_NAME('AR','HZ_CM_SITE_MISMATCH');
326 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', p_account_site_id);
327 FND_MESSAGE.SET_TOKEN('ACCOUNT_ID', p_account_id);
328 FND_MSG_PUB.ADD();
329 RAISE fnd_api.g_exc_error;
330 END IF;
331
332 END IF;
333
334 /* Validate OS and OSR */
335
336 l_ss_flag := is_ss_provided(p_os => p_site_os,
337 p_osr =>p_site_osr);
338
339 IF (l_ss_flag = 'Y')THEN
340
341 -- Get how many rows return
342 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
343 p_orig_system => p_site_os,
344 p_orig_system_reference => p_site_osr,
345 p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL');
346
347 IF (l_count = 0) THEN
348
352 RAISE fnd_api.g_exc_error;
349 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
350 FND_MESSAGE.SET_TOKEN('OSOSR', p_site_os||','||p_site_osr);
351 FND_MSG_PUB.ADD();
353
354 ELSIF(l_count = 1) THEN
355 -- Get owner_table_id
356 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
357 p_orig_system => p_site_os,
358 p_orig_system_reference => p_site_osr,
359 p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
360 x_owner_table_id => l_owner_table_id,
361 x_return_status => x_return_status);
362
363 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
364 RAISE fnd_api.g_exc_error;
365 END IF;
366
367 IF l_cust_acct_site_id IS NOT NULL AND l_owner_table_id <> l_cust_acct_site_id THEN
368
369 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
370 FND_MESSAGE.SET_TOKEN('OSOSR', p_site_os||','||p_site_osr);
371 FND_MSG_PUB.ADD();
372 RAISE fnd_api.g_exc_error;
373
374 ELSIF l_cust_acct_site_id IS NULL THEN
375
376 OPEN get_account_site(l_owner_table_id);
377 FETCH get_account_site INTO l_cust_acct_site_id, l_cust_account_id, l_party_site_id, x_acct_site_status, l_org_id, l_osr;
378 CLOSE get_account_site;
379
380 IF l_cust_acct_site_id IS NULL THEN
381
382 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
383 FND_MESSAGE.SET_TOKEN('OSOSR', p_site_os||','||p_site_osr);
384 FND_MSG_PUB.ADD();
385 RAISE fnd_api.g_exc_error;
386 END IF;
387 END IF;
388
389 END IF; /*l_count */
390
391 END IF;/* l_ss_flag */
392
393 /* Validate if acct_site exists in p_org_id */
394
395 IF p_org_id <> l_org_id THEN
396
397 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_OU');
398 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', l_cust_acct_site_id);
399 FND_MESSAGE.SET_TOKEN('ORG_ID', p_org_id);
400 FND_MSG_PUB.ADD();
401 RAISE fnd_api.g_exc_error;
402
403 END IF;
404
405 IF l_party_site_id IS NULL THEN
406
407 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_ID');
408 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', p_account_site_id);
409 FND_MSG_PUB.ADD();
410 RAISE fnd_api.g_exc_error;
411
412 ELSE
413
414 SELECT party_site_number, location_id INTo l_party_site_number, x_location_id
415 FROM hz_party_sites
416 WHERE party_site_id = l_party_site_id;
417
418 END IF;
419
420 /* Validate site_number */
421
422 IF p_site_number IS NULL THEN
423
424 p_site_number := l_party_site_number;
425
426 ELSIF p_site_number <> l_party_site_number THEN
427
428 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_NUM');
429 FND_MESSAGE.SET_TOKEN('SITE_NUMBER', l_party_site_number);
430 FND_MESSAGE.SET_TOKEN('ACCT_SITE', l_cust_acct_site_id);
431 FND_MSG_PUB.ADD();
432 RAISE fnd_api.g_exc_error;
433
434 END IF;
435
436 IF p_account_site_id IS NULL THEN
437
438 p_account_site_id := l_cust_acct_site_id;
439
440 END IF;
441
442 p_site_osr := l_osr;
443
444 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
445 hz_utility_v2pub.debug(p_message=>'validate_account_site(-)',
446 p_prefix=>l_debug_prefix,
447 p_msg_level=>fnd_log.level_procedure);
448 END IF;
449
450 EXCEPTION
451 WHEN fnd_api.g_exc_error THEN
452 x_return_status := fnd_api.g_ret_sts_error;
453
454 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
455 p_count => x_msg_count,
456 p_data => x_msg_data);
457
458 -- Debug info.
459 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
460 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
461 p_msg_data=>x_msg_data,
462 p_msg_type=>'ERROR',
463 p_msg_level=>fnd_log.level_error);
464 END IF;
465 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
466 hz_utility_v2pub.debug(p_message=>'validate_account_site(-)',
467 p_prefix=>l_debug_prefix,
468 p_msg_level=>fnd_log.level_procedure);
469 END IF;
470 WHEN fnd_api.g_exc_unexpected_error THEN
471 x_return_status := fnd_api.g_ret_sts_unexp_error;
472
473 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
474 p_count => x_msg_count,
475 p_data => x_msg_data);
476
477 -- Debug info.
478 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
479 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
480 p_msg_data=>x_msg_data,
481 p_msg_type=>'UNEXPECTED ERROR',
482 p_msg_level=>fnd_log.level_error);
483 END IF;
484 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
485 hz_utility_v2pub.debug(p_message=>'validate_account_site(-)',
486 p_prefix=>l_debug_prefix,
487 p_msg_level=>fnd_log.level_procedure);
488 END IF;
489 WHEN OTHERS THEN
490 x_return_status := fnd_api.g_ret_sts_unexp_error;
491
492 FND_MESSAGE.set_name('AR', 'HZ_API_OTHERS_EXCEP');
493 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
494 fnd_msg_pub.add;
495
496 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
497 p_count => x_msg_count,
498 p_data => x_msg_data);
499
500 -- Debug info.
501 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
502 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
503 p_msg_data=>x_msg_data,
504 p_msg_type=>'SQL ERROR',
505 p_msg_level=>fnd_log.level_error);
506 END IF;
510 p_msg_level=>fnd_log.level_procedure);
507 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
508 hz_utility_v2pub.debug(p_message=>'validate_account_site(-)',
509 p_prefix=>l_debug_prefix,
511 END IF;
512
513 END validate_account_site;
514
515 PROCEDURE validate_account_site_use(
519 p_site_use_osr IN OUT NOCOPY VARCHAR2,
516 p_account_site_id IN NUMBER,
517 p_site_use_id IN OUT NOCOPY NUMBER,
518 p_site_use_os IN OUT NOCOPY VARCHAR2,
520 p_site_usage IN OUT NOCOPY VARCHAR2,
521 x_site_use_status OUT NOCOPY VARCHAR2,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2
525 ) IS
526
527 CURSOR get_site_use_details(c_site_use_id NUMBER) IS
528 SELECT site_use_id, cust_acct_site_id, site_use_code, status, location, primary_flag
529 FROM hz_cust_site_uses_all
530 WHERE site_use_id = c_site_use_id;
531
532 l_site_use_id NUMBER;
533 l_account_site_id NUMBER;
534 l_site_use_code VARCHAR2(40);
535 l_location VARCHAR2(40);
536 l_primary_flag VARCHAR2(1);
537 l_count NUMBER;
538 l_ss_flag VARCHAR2(1);
539 l_owner_table_id NUMBER;
540
541 BEGIN
542
543 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
544 hz_utility_v2pub.debug(p_message=>'validate_account_site_use(+)',
545 p_prefix=>l_debug_prefix,
546 p_msg_level=>fnd_log.level_procedure);
547 END IF;
548
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 /*Validate p_site_use_id */
552
553 IF p_site_use_id IS NOT NULL THEN
554
555 OPEN get_site_use_details(p_site_use_id);
556 FETCH get_site_use_details INTO l_site_use_id, l_account_site_id , l_site_use_code, x_site_use_status, l_location, l_primary_flag;
557 CLOSE get_site_use_details;
558
559 IF l_site_use_id IS NULL THEN
560
561 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_ID');
562 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', p_site_use_id);
563 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', P_account_site_id);
564 FND_MSG_PUB.ADD();
565 RAISE fnd_api.g_exc_error;
566
567 END IF;
568
569 IF l_account_site_id <> p_account_site_id THEN
570
571 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_ID');
572 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', p_site_use_id);
573 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', P_account_site_id);
574 FND_MSG_PUB.ADD();
575 RAISE fnd_api.g_exc_error;
576
577 END IF;
578
579
580 END IF;
581
582 /* Validate OS and OSR */
583
584 l_ss_flag := is_ss_provided(p_os => p_site_use_os,
585 p_osr =>p_site_use_osr);
586
587 IF (l_ss_flag = 'Y')THEN
588
589 -- Get how many rows return
590 l_count := HZ_MOSR_VALIDATE_PKG.get_orig_system_ref_count(
591 p_orig_system => p_site_use_os,
592 p_orig_system_reference => p_site_use_osr,
593 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL');
594
595 IF (l_count = 0) THEN
596
597 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
598 FND_MESSAGE.SET_TOKEN('OSOSR', p_site_use_os||','||p_site_use_osr);
599 FND_MSG_PUB.ADD();
600 RAISE fnd_api.g_exc_error;
601
602 ELSIF(l_count = 1) THEN
603 -- Get owner_table_id
604 HZ_ORIG_SYSTEM_REF_PUB.get_owner_table_id(
605 p_orig_system => p_site_use_os,
606 p_orig_system_reference => p_site_use_osr,
610
607 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL',
608 x_owner_table_id => l_owner_table_id,
609 x_return_status => x_return_status);
611 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
612 RAISE fnd_api.g_exc_error;
613 END IF;
614
615 IF l_site_use_id IS NOT NULL AND l_owner_table_id <> l_site_use_id THEN
616
617 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
618 FND_MESSAGE.SET_TOKEN('OSOSR', p_site_use_os||','||p_site_use_osr);
619 FND_MSG_PUB.ADD();
620 RAISE fnd_api.g_exc_error;
621
622 ELSIF l_site_use_id IS NULL THEN
623
624 OPEN get_site_use_details(l_owner_table_id);
625 FETCH get_site_use_details INTO l_site_use_id, l_account_site_id, l_site_use_code, x_site_use_status, l_location, l_primary_flag;
626 CLOSE get_site_use_details;
627
628
629 IF l_site_use_id IS NULL THEN
630
631 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_OSOSR');
632 FND_MESSAGE.SET_TOKEN('OSOSR', p_site_use_os||','||p_site_use_osr);
633 FND_MSG_PUB.ADD();
634 RAISE fnd_api.g_exc_error;
635 END IF;
636 END IF;
637
638 END IF; /*l_count */
639
640 END IF;/* l_ss_flag */
641
642 IF p_site_use_id IS NULL THEN
643
644 p_site_use_id := l_site_use_id;
645
646 END IF;
647
648 IF p_site_use_id IS NULL THEN
649
650 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_ID');
651 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', p_site_use_id);
652 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', P_account_site_id);
653 FND_MSG_PUB.ADD();
654 RAISE fnd_api.g_exc_error;
655 END IF;
656
657 IF p_site_usage IS NULL THEN
658
659 p_site_usage := l_site_use_code;
660
661 ELSIF l_site_use_code <> p_site_usage THEN
662
663 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_TYPE');
664 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', p_site_use_id);
665 FND_MSG_PUB.ADD();
666 RAISE fnd_api.g_exc_error;
667 END IF;
668
669 g_site_use_location(p_site_use_id) := l_location;
670 g_primary_flag(p_site_use_id) := l_primary_flag;
671
672 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
673 hz_utility_v2pub.debug(p_message=>'validate_account_site_use(-)',
674 p_prefix=>l_debug_prefix,
675 p_msg_level=>fnd_log.level_procedure);
676 END IF;
677
678 EXCEPTION
679 WHEN fnd_api.g_exc_error THEN
680 x_return_status := fnd_api.g_ret_sts_error;
681
682 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
683 p_count => x_msg_count,
684 p_data => x_msg_data);
685
686 -- Debug info.
687 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
688 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
689 p_msg_data=>x_msg_data,
690 p_msg_type=>'ERROR',
694 hz_utility_v2pub.debug(p_message=>'validate_account_site_use(-)',
691 p_msg_level=>fnd_log.level_error);
692 END IF;
693 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
695 p_prefix=>l_debug_prefix,
696 p_msg_level=>fnd_log.level_procedure);
697 END IF;
698 WHEN fnd_api.g_exc_unexpected_error THEN
699 x_return_status := fnd_api.g_ret_sts_unexp_error;
700
701 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
702 p_count => x_msg_count,
703 p_data => x_msg_data);
704
705 -- Debug info.
706 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
707 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
708 p_msg_data=>x_msg_data,
709 p_msg_type=>'UNEXPECTED ERROR',
710 p_msg_level=>fnd_log.level_error);
711 END IF;
712 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
713 hz_utility_v2pub.debug(p_message=>'validate_account_site_use(-)',
714 p_prefix=>l_debug_prefix,
715 p_msg_level=>fnd_log.level_procedure);
716 END IF;
717 WHEN OTHERS THEN
718 x_return_status := fnd_api.g_ret_sts_unexp_error;
719
720 FND_MESSAGE.set_name('AR', 'HZ_API_OTHERS_EXCEP');
721 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
722 fnd_msg_pub.add;
723
724 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
725 p_count => x_msg_count,
726 p_data => x_msg_data);
727
728 -- Debug info.
729 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
730 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
731 p_msg_data=>x_msg_data,
732 p_msg_type=>'SQL ERROR',
733 p_msg_level=>fnd_log.level_error);
734 END IF;
735 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
736 hz_utility_v2pub.debug(p_message=>'validate_account_site_use(-)',
737 p_prefix=>l_debug_prefix,
738 p_msg_level=>fnd_log.level_procedure);
739 END IF;
740
741 END validate_account_site_use;
742
743 -- PROCEDURE create_account_merge_request
744 --
745 -- DESCRIPTION
746 -- Create merge request for duplicate accounts
747 --
748 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
749 --
750 -- ARGUMENTS
751 -- IN:
752 -- p_init_msg_list Standard IN parameter to initialize message stack.
753 -- p_submit_request Indicates whether the merge should be submitted or not.
754 -- p_account_merge_request_obj An object of account merge details.
755 --
756 -- OUT:
757 -- x_return_status Return status after the call. The status can
758 -- be fnd_api.g_ret_sts_success (success),
759 -- fnd_api.g_ret_sts_error (error),
760 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
761 -- x_msg_count Number of messages in message stack.
762 -- x_msg_data Message text if x_msg_count is 1.
763 -- x_account_merge_result
764 --
765 -- NOTES
766 --
767 -- MODIFICATION HISTORY
768 --
769 -- 11-DEC-2012 S V Sowjanya o Created.
770
771 PROCEDURE create_account_merge_request(
772 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
773 p_submit_request IN VARCHAR2 := 'N',
774 p_account_merge_request_obj IN HZ_ACCOUNT_MERGE_REQUEST_OBJ,
775 x_customer_merge_header_id OUT NOCOPY NUMBER,
776 x_account_merge_request_id OUT NOCOPY NUMBER,
777 x_return_status OUT NOCOPY VARCHAR2,
778 x_msg_count OUT NOCOPY NUMBER,
779 x_msg_data OUT NOCOPY VARCHAR2) IS
780
781 l_account_merge_request_obj HZ_ACCOUNT_MERGE_REQUEST_OBJ;
782 l_from_account_status VARCHAR2(1);
783 l_from_party_id NUMBER;
784 l_to_account_status VARCHAR2(1);
785 l_to_party_id NUMBER;
786 l_from_customer_name VARCHAR2 (360);
787 l_to_customer_name VARCHAR2 (360);
788 l_from_customer_type VARCHAR2 (30);
789 l_to_customer_type VARCHAR2 (30);
790 l_from_cust_first_name VARCHAR2 (150);
791 l_from_cust_last_name VARCHAR2 (150);
792 l_to_cust_first_name VARCHAR2 (150);
793 l_to_cust_last_name VARCHAR2 (150);
794 l_header_id NUMBER(15);
795 l_from_location_id NUMBER(15);
796 l_to_location_id NUMBER(15);
797 l_merge_yn VARCHAR2(2);
798 l_from_site_status VARCHAR2(1);
799 l_to_site_status VARCHAR2(1);
800 l_from_site_use_status VARCHAR2(1);
801 l_to_site_use_status VARCHAR2(1);
802 l_from_address VARCHAR2(360);
803 l_to_address VARCHAR2(360);
804 l_from_site_use_location VARCHAR2(40);
805 l_to_site_use_location VARCHAR2(40);
806 l_from_party_osr VARCHAR2(240);
807 l_to_party_osr VARCHAR2(240);
808 l_exist VARCHAR2(1);
809 tmp_duplicate_number VARCHAR2(30);
810 tmp_customer_number VARCHAR2(30);
811 l_site_cnt NUMBER;
812 l_site_use_exists VARCHAR2(1);
813 l_ou_name VARCHAR2(240);
814
815 CURSOR c_get_party_details(c_party_id NUMBER) IS
816 SELECT party_name, party_type, person_first_name, person_last_name, orig_system_reference
817 FROM hz_parties
818 WHERE party_id = c_party_id;
819
820 CURSOR c_site_use(c_acct_site_id NUMBER) IS
821 SELECT 'Y'
822 FROM hz_cust_site_uses_all
823 WHERE cust_acct_site_id = c_acct_site_id
824 AND rownum = 1;
825
826
827 BEGIN
828
829 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
830 hz_utility_v2pub.debug(p_message=>'create_account_merge_request(+)',
831 p_prefix=>l_debug_prefix,
832 p_msg_level=>fnd_log.level_procedure);
833 END IF;
834
838 FND_MSG_PUB.initialize;
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836
837 IF FND_API.to_Boolean(p_init_msg_list) THEN
839 END IF;
840
841 --Assign p_account_merge_request_obj to local variable
842
843 l_account_merge_request_obj := p_account_merge_request_obj;
844 --Validate p_submit_request
845
846 IF p_submit_request not in ('Y', 'N') THEN
850 END IF;
847 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SUBMIT_PARAM');
848 FND_MSG_PUB.ADD();
849 RAISE fnd_api.g_exc_error;
851
852 g_location_id.DELETE;
853 g_site_use_location.DELETE;
854
855 --Begin Validate merge-from account
856
857 IF l_account_merge_request_obj.merge_from_account IS NULL THEN
858
859 FND_MESSAGE.SET_NAME('AR','HZ_CM_FROM_ACCT_MISSING');
860 FND_MSG_PUB.ADD();
861 RAISE fnd_api.g_exc_error;
862 END IF;
863
864
865 validate_account(p_account_id => l_account_merge_request_obj.merge_from_account.account_id,
866 p_account_number => l_account_merge_request_obj.merge_from_account.account_number,
867 p_account_os => l_account_merge_request_obj.merge_from_account.account_orig_system,
868 p_account_osr => l_account_merge_request_obj.merge_from_account.account_orig_sys_ref,
869 x_account_status => l_from_account_status,
870 x_party_id => l_from_party_id,
871 x_return_status => x_return_status,
872 x_msg_count => x_msg_count,
873 x_msg_data => x_msg_data);
874
875 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
876 RAISE fnd_api.g_exc_error;
877 END IF;
878
879 IF l_account_merge_request_obj.merge_from_account.account_id IS NULL THEN
880
881 FND_MESSAGE.SET_NAME('AR','HZ_CM_FROM_ACCT_MISSING');
882 FND_MSG_PUB.ADD();
883 RAISE fnd_api.g_exc_error;
884 END IF;
885
886 --End Validate merge-from account
887
888 --Begin Validate merge-to account
889
890 IF l_account_merge_request_obj.merge_to_account IS NULL THEN
891 FND_MESSAGE.SET_NAME('AR','HZ_CM_TO_ACCT_MISSING');
892 FND_MSG_PUB.ADD();
893 RAISE fnd_api.g_exc_error;
894 END IF;
895
896 validate_account(p_account_id => l_account_merge_request_obj.merge_to_account.account_id,
897 p_account_number => l_account_merge_request_obj.merge_to_account.account_number,
898 p_account_os => l_account_merge_request_obj.merge_to_account.account_orig_system,
899 p_account_osr => l_account_merge_request_obj.merge_to_account.account_orig_sys_ref,
900 x_account_status => l_to_account_status,
901 x_party_id => l_to_party_id,
902 x_return_status => x_return_status,
903 x_msg_count => x_msg_count,
904 x_msg_data => x_msg_data);
905
906 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
907 RAISE fnd_api.g_exc_error;
908 END IF;
909
910 IF l_account_merge_request_obj.merge_to_account.account_id IS NULL THEN
911
912 FND_MESSAGE.SET_NAME('AR','HZ_CM_TO_ACCT_MISSING');
913 FND_MSG_PUB.ADD();
914 RAISE fnd_api.g_exc_error;
915 END IF;
916
917
918 -- END Validate merge-to account
919
920 /* Test if there are other current users are merging same customers
921 * You can not continue merging process if situations below occur:
922 *
923 * User One: A->B While User Two: B->C
924 * User One: A->B While User Two: A->C
925 * User One: A->B While User Two: C->A
926 */
927
928 IF l_account_merge_request_obj.merge_from_account.account_number <> l_account_merge_request_obj.merge_to_account.account_number THEN
929
930 BEGIN
931 l_exist := 'N';
932
933 select 'Y' , cmh.duplicate_number, cmh.customer_number
934 into l_exist, tmp_duplicate_number, tmp_customer_number
935 from ra_customer_merges cm, ra_customer_merge_headers cmh , hz_cust_acct_sites_all cas
936 where cas.cust_account_id = cm.duplicate_id
937 and (cmh.process_flag NOT IN ('FAILED' ,'Y','DISCARD') )
938 and cmh.customer_merge_header_id = cm.customer_merge_header_id
939 and cas.cust_account_id = l_account_merge_request_obj.merge_from_account.account_id
940 and cas.org_id = cm.org_id
941 and ( (cas.cust_acct_site_id = cm.duplicate_address_id) OR
942 (cas.cust_acct_site_id = cm.customer_address_id) )
943 and ROWNUM = 1;
944
945
946
947 EXCEPTION
948 WHEN NO_DATA_FOUND THEN
949 l_exist := 'N';
950 END;
951
952 if l_exist = 'Y' then
956 FND_MESSAGE.SET_TOKEN('TACCT_NUM', tmp_customer_number);
953 FND_MESSAGE.set_name ('AR', 'HZ_PROMPT_SELECTED_ACCOUNT');
954 FND_MESSAGE.SET_TOKEN('ACCT_NUM', l_account_merge_request_obj.merge_from_account.account_number);
955 FND_MESSAGE.SET_TOKEN('FACCT_NUM', tmp_duplicate_number);
957 FND_MSG_PUB.ADD();
958 RAISE fnd_api.g_exc_error;
959 end if;
960
961 END IF;
962
963
964 IF l_from_account_status = 'A' AND l_to_account_status = 'I' THEN
965
966 FND_MESSAGE.SET_NAME('AR','HZ_MERGING_INVALID_ACCOUNT');
967 FND_MSG_PUB.ADD();
968 RAISE fnd_api.g_exc_error;
969 END IF;
970
971 --Validate header org_id
972
973 IF l_account_merge_request_obj.org_id IS NULL THEN
974
975 FND_MESSAGE.SET_NAME('AR','HZ_CM_HDR_NULL_OU');
976 FND_MSG_PUB.ADD();
977 RAISE fnd_api.g_exc_error;
978 END IF;
979
980 If l_account_merge_request_obj.org_id <> -1 THEN
981
982 BEGIN
983 MO_GLOBAL.validate_orgid_pub_api(l_account_merge_request_obj.org_id,'N',x_return_status);
984 EXCEPTION
985 WHEN OTHERS
986 THEN
987 RAISE FND_API.G_EXC_ERROR;
988 END;
989
990 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
991 RAISE fnd_api.g_exc_error;
992 END IF;
993
994 END IF;
995
996 --Get party details
997
998 OPEN c_get_party_details(l_from_party_id);
999 FETCH c_get_party_details INTO l_from_customer_name, l_from_customer_type, l_from_cust_first_name, l_from_cust_last_name, l_from_party_osr;
1000 CLOSE c_get_party_details;
1001
1002
1003
1004 OPEN c_get_party_details(l_to_party_id);
1005 FETCH c_get_party_details INTO l_to_customer_name, l_to_customer_type, l_to_cust_first_name, l_to_cust_last_name, l_to_party_osr;
1006 CLOSE c_get_party_details;
1007
1008 IF l_from_customer_type <> l_to_customer_type THEN
1009
1010 FND_MESSAGE.SET_NAME('AR','HZ_INVALID_MERGE_PARTIES');
1011 FND_MSG_PUB.ADD();
1012 RAISE fnd_api.g_exc_error;
1013
1014 END IF;
1015
1016 IF l_from_customer_type = 'ORGANIZATION' THEN
1017
1018 l_from_customer_type := 'DUPLICATE_ORG';
1019 l_to_customer_type := 'CUSTOMER_ORG';
1020
1021 ELSE
1022
1026 END IF;
1023 l_from_customer_type := 'DUPLICATE_PERSON';
1024 l_to_customer_type := 'CUSTOMER_PERSON';
1025
1027
1028
1029 --Validate site details
1030
1031 IF l_account_merge_request_obj.merge_from_account.account_id = l_account_merge_request_obj.merge_to_account.account_id
1032 AND (l_account_merge_request_obj.site_merge_details IS NULL OR l_account_merge_request_obj.site_merge_details.count = 0) THEN
1033
1034 FND_MESSAGE.SET_NAME('AR','HZ_CM_SAME_CUST_MERGE');
1035 FND_MSG_PUB.ADD();
1036 RAISE fnd_api.g_exc_error;
1037 END IF;
1038
1039 IF (l_account_merge_request_obj.site_merge_details IS NOT NULL AND l_account_merge_request_obj.site_merge_details.count > 0) THEN
1040
1041 FOR i IN 1..l_account_merge_request_obj.site_merge_details.count LOOP
1042
1043 --Validate site level org_id
1044
1045 IF l_account_merge_request_obj.site_merge_details(i).org_id IS NULL THEN
1046
1047 FND_MESSAGE.SET_NAME('AR','HZ_CM_SITE_NULL_OU');
1048 FND_MESSAGE.SET_TOKEN('FROM_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1049 FND_MSG_PUB.ADD();
1050 RAISE fnd_api.g_exc_error;
1051 END IF;
1052
1053 BEGIN
1054 MO_GLOBAL.validate_orgid_pub_api(l_account_merge_request_obj.site_merge_details(i).org_id,'N',x_return_status);
1055 EXCEPTION
1056 WHEN OTHERS
1057 THEN
1058 RAISE FND_API.G_EXC_ERROR;
1059 END;
1060 l_from_location_id := null;
1061 l_to_location_id := null;
1062
1063 /*validate merge from account site obj*/
1064 validate_account_site(
1065 p_account_id => l_account_merge_request_obj.merge_from_account.account_id,
1066 p_account_site_id => l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id,
1067 p_site_number => l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.site_number,
1068 p_site_os => l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_orig_system,
1069 p_site_osr => l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_orig_sys_ref,
1070 p_org_id => l_account_merge_request_obj.site_merge_details(i).org_id,
1071 x_location_id => l_from_location_id,
1072 x_acct_site_status=> l_from_site_status,
1073 x_return_status => x_return_status,
1074 x_msg_count => x_msg_count,
1075 x_msg_data => x_msg_data
1076 );
1077
1078 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1079 RAISE fnd_api.g_exc_error;
1080 END IF;
1081
1082 g_location_id(l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id) := l_from_location_id;
1083
1084 /* validate merge to account site obj */
1085
1086 IF (l_account_merge_request_obj.merge_from_account.account_id = l_account_merge_request_obj.merge_to_account.account_id
1087 AND l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj IS NULL) THEN
1088
1089 FND_MESSAGE.SET_NAME('AR','HZ_CM_NULL_TO_SITE');
1090 FND_MSG_PUB.ADD();
1091 RAISE fnd_api.g_exc_error;
1092 END IF;
1093
1094 IF l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj IS NOT NULL THEN
1095
1096 validate_account_site(
1097 p_account_id => l_account_merge_request_obj.merge_to_account.account_id,
1098 p_account_site_id => l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id,
1099 p_site_number => l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.site_number,
1100 p_site_os => l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_system,
1101 p_site_osr => l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref,
1102 p_org_id => l_account_merge_request_obj.site_merge_details(i).org_id,
1103 x_location_id => l_to_location_id,
1104 x_acct_site_status=> l_to_site_status,
1105 x_return_status => x_return_status,
1106 x_msg_count => x_msg_count,
1107 x_msg_data => x_msg_data
1108 );
1109
1110 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1111 RAISE fnd_api.g_exc_error;
1112 END IF;
1113
1117 FND_MESSAGE.SET_NAME('AR','HZ_CM_SAME_SITES');
1114 IF (l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id =
1115 l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id) THEN
1116
1118 FND_MESSAGE.SET_TOKEN('FROM_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1119 FND_MESSAGE.SET_TOKEN('TO_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id);
1120 FND_MSG_PUB.ADD();
1121 RAISE fnd_api.g_exc_error;
1122
1123 END IF;
1124
1125
1126 IF l_to_site_status = 'I' AND l_from_site_status = 'A' THEN
1127
1128 FND_MESSAGE.SET_NAME('AR','HZ_CM_SITE_MASTER_ERR');
1129 FND_MESSAGE.SET_TOKEN('FROM_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1130 FND_MESSAGE.SET_TOKEN('TO_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id);
1131 FND_MSG_PUB.ADD();
1132 RAISE fnd_api.g_exc_error;
1133
1134 END IF;
1135
1136
1137 /*Perform tax validation*/
1138
1139 ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
1140 l_to_location_id,
1141 FND_API.G_FALSE,
1142 l_merge_yn,
1143 x_return_status,
1144 x_msg_count,
1145 x_msg_data);
1146
1147 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1148 RAISE fnd_api.g_exc_error;
1149 END IF;
1150
1151 IF l_merge_yn = 'N' THEN
1152
1153 FND_MESSAGE.SET_NAME('AR','AR_CUST_ADDR_LOC_MISMATCH');
1154 FND_MSG_PUB.ADD();
1155 RAISE fnd_api.g_exc_error;
1156 END IF;
1157
1158 g_location_id(l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id) := l_to_location_id;
1159
1160 END IF;
1161
1162
1163
1164 IF (l_account_merge_request_obj.site_merge_details(i).site_use_merge_details IS NULL
1165 OR l_account_merge_request_obj.site_merge_details(i).site_use_merge_details.count = 0)
1166 AND l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id IS NOT NULL THEN
1167
1171 FETCH c_site_use INTO l_site_use_exists;
1168 l_site_use_exists := 'N';
1169
1170 OPEN c_site_use(l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1172 CLOSE c_site_use;
1173
1174
1175 IF l_site_use_exists = 'Y' THEN
1176 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_ID');
1177 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', NULL);
1178 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1179 FND_MSG_PUB.ADD();
1180 RAISE fnd_api.g_exc_error;
1181 END IF;
1182
1183 END IF;
1184
1185
1186 IF (l_account_merge_request_obj.site_merge_details(i).site_use_merge_details IS NOT NULL AND
1187 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details.count > 0) THEN
1188
1189 FOR j IN 1..l_account_merge_request_obj.site_merge_details(i).site_use_merge_details.count LOOP
1190
1191
1192 IF l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj IS NULL
1193 AND l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id IS NOT NULL THEN
1194
1195 l_site_use_exists := 'N';
1196
1200
1197 OPEN c_site_use(l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1198 FETCH c_site_use INTO l_site_use_exists;
1199 CLOSE c_site_use;
1201 IF l_site_use_exists = 'Y' THEN
1202
1203 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_ID');
1204 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', NULL);
1205 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1206 FND_MSG_PUB.ADD();
1207 RAISE fnd_api.g_exc_error;
1208 END IF;
1209
1210 END IF;
1211
1212 /*validate merge from account site use obj*/
1213
1214 validate_account_site_use(
1215 p_account_site_id => l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id,
1216 p_site_use_id => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id,
1217 p_site_use_os => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_orig_system,
1218 p_site_use_osr => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_orig_sys_ref,
1219 p_site_usage => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_type,
1220 x_site_use_status => l_from_site_use_status,
1221 x_return_status => x_return_status,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data
1224 );
1225
1226 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1227 RAISE fnd_api.g_exc_error;
1228 END IF;
1229
1230
1231 IF l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj IS NULL
1232 AND l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id IS NOT NULL THEN
1233
1234
1235 l_site_use_exists := 'N';
1236
1237 OPEN c_site_use(l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id);
1238 FETCH c_site_use INTO l_site_use_exists;
1239 CLOSE c_site_use;
1240
1241 IF l_site_use_exists = 'Y' THEN
1242
1243 FND_MESSAGE.SET_NAME('AR','HZ_CM_INV_SITE_USE_ID');
1244 FND_MESSAGE.SET_TOKEN('SITE_USE_ID', NULL);
1245 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id);
1246 FND_MSG_PUB.ADD();
1247 RAISE fnd_api.g_exc_error;
1248
1249 END IF;
1250
1251 END IF;
1252
1253 IF l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj IS NOT NULL THEN
1254 /*validate merge to account site use obj*/
1255
1256 validate_account_site_use(
1257 p_account_site_id => l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id,
1258 p_site_use_id => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id,
1259 p_site_use_os => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_orig_system,
1260 p_site_use_osr => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_orig_sys_ref,
1261 p_site_usage => l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_type,
1262 x_site_use_status => l_to_site_use_status,
1263 x_return_status => x_return_status,
1264 x_msg_count => x_msg_count,
1265 x_msg_data => x_msg_data
1266 );
1267
1268 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1269 RAISE fnd_api.g_exc_error;
1270 END IF;
1271 IF l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_type <>
1272 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_type THEN
1273
1274 FND_MESSAGE.SET_NAME('AR','HZ_CM_SITE_USE_MISMATCH');
1275 FND_MESSAGE.SET_TOKEN('FROM_SITE_USE_ID',
1276 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id);
1277 FND_MESSAGE.SET_TOKEN('TO_SITE_USE_ID',
1278 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id);
1279 FND_MSG_PUB.ADD();
1280 RAISE fnd_api.g_exc_error;
1281
1282 END IF;
1283
1284 IF l_to_site_use_status = 'I' AND l_to_site_use_status = 'A' THEN
1285
1286 FND_MESSAGE.SET_NAME('AR','HZ_CM_SITE_USE_MASTER_ERR');
1287 FND_MESSAGE.SET_TOKEN('FROM_SITE_USE_ID',
1288 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id);
1289 FND_MESSAGE.SET_TOKEN('TO_SITE_USE_ID',
1290 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id);
1291 FND_MSG_PUB.ADD();
1292 RAISE fnd_api.g_exc_error;
1293
1294 END IF;
1295
1296 END IF; --merge_to_site_use_obj
1297
1298 END LOOP; --site use
1299
1300 END IF;
1301
1302
1303 END LOOP; --site_merge_details
1304
1305 END IF; --site_merge_details
1306
1307 --END Validation
1308
1312
1309 /* Insert Merge header */
1310
1311 l_header_id := ra_customer_merge_headers_s.nextval;
1313 --Populate ra_customer_merge_headers
1314
1315 INSERT INTO RA_CUSTOMER_MERGE_HEADERS(
1316 CUSTOMER_MERGE_HEADER_ID
1317 , CREATION_DATE
1318 , CREATED_BY
1319 , LAST_UPDATE_DATE
1320 , LAST_UPDATED_BY
1321 , LAST_UPDATE_LOGIN
1322 , CUSTOMER_ID
1323 , CUSTOMER_NAME
1324 , CUSTOMER_NUMBER
1325 , CUSTOMER_REF
1326 , DUPLICATE_ID
1327 , DUPLICATE_NAME
1328 , DUPLICATE_NUMBER
1329 , DUPLICATE_REF
1330 , DELETE_DUPLICATE_FLAG
1331 , PROCESS_FLAG
1332 , CUSTOMER_FIRST_NAME
1333 , CUSTOMER_LAST_NAME
1334 , CUSTOMER_TYPE
1335 , DUPLICATE_FIRST_NAME
1336 , DUPLICATE_LAST_NAME
1337 , DUPLICATE_TYPE
1338 , MERGE_REASON_CODE
1339 , PRIORITY
1340 , ORG_ID)
1341 VALUES(l_header_id
1342 , sysdate
1343 , HZ_UTILITY_V2PUB.CREATED_BY
1344 , sysdate
1345 , HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1346 , HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN
1347 , l_account_merge_request_obj.merge_to_account.account_id
1348 , l_to_customer_name
1349 , l_account_merge_request_obj.merge_to_account.account_number
1350 , l_to_party_osr
1351 , l_account_merge_request_obj.merge_from_account.account_id
1352 , l_from_customer_name
1353 , l_account_merge_request_obj.merge_from_account.account_number
1354 , l_from_party_osr
1355 , NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N')
1356 , 'SAVED'
1357 , l_to_cust_first_name
1358 , l_to_cust_last_name
1359 , l_to_customer_type
1360 , l_from_cust_first_name
1361 , l_from_cust_last_name
1362 , l_from_customer_type
1363 , NVL(l_account_merge_request_obj.merge_reason_code, 'DEDUP')
1364 , NVL(l_account_merge_request_obj.priority, 'P3')
1365 , l_account_merge_request_obj.org_id);
1366
1367 --Populate ra_customer_merges with sites to be merged
1368
1369 IF (l_account_merge_request_obj.site_merge_details IS NOT NULL AND
1370 l_account_merge_request_obj.site_merge_details.count > 0) THEN
1371
1372 FOR i IN 1..l_account_merge_request_obj.site_merge_details.count LOOP
1373
1374 l_from_location_id := null;
1375 l_to_location_id := null;
1376 l_from_address := null;
1377 l_to_address := null;
1378
1379 IF l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id IS NOT NULL THEN
1380
1381 l_from_location_id := g_location_id(l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id);
1385 , LOC.ADDRESS1
1382 l_to_location_id := g_location_id(l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id);
1383
1384 select substr(ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
1386 , LOC.ADDRESS2
1387 , LOC.ADDRESS3
1388 , LOC.ADDRESS4
1389 , LOC.CITY
1390 , LOC.COUNTY
1391 , LOC.STATE
1392 , LOC.PROVINCE
1393 , LOC.POSTAL_CODE
1394 , TERR.TERRITORY_SHORT_NAME ),1,240) INTO l_from_address
1395 FROM hz_locations loc,
1396 FND_TERRITORIES_VL TERR
1397 WHERE loc.location_id = l_from_location_id
1398 and LOC.COUNTRY = TERR.TERRITORY_CODE (+);
1399
1400 select substr(ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
1401 , LOC.ADDRESS1
1402 , LOC.ADDRESS2
1403 , LOC.ADDRESS3
1404 , LOC.ADDRESS4
1405 , LOC.CITY
1406 , LOC.COUNTY
1407 , LOC.STATE
1408 , LOC.PROVINCE
1409 , LOC.POSTAL_CODE
1410 , TERR.TERRITORY_SHORT_NAME ),1,240) INTO l_to_address
1411 FROM hz_locations loc,
1412 FND_TERRITORIES_VL TERR
1413 WHERE loc.location_id = l_to_location_id
1414 and LOC.COUNTRY = TERR.TERRITORY_CODE (+);
1415
1416 IF (l_account_merge_request_obj.site_merge_details(i).site_use_merge_details IS NOT NULL AND
1417 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details.count > 0) THEN
1418
1419 For j IN 1..l_account_merge_request_obj.site_merge_details(i).site_use_merge_details.count LOOP
1420
1421 IF (l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj IS NOT NULL AND
1422 l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj IS NOT NULL) THEN
1423
1424 l_from_site_use_location := g_site_use_location(l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id);
1425 l_to_site_use_location := g_site_use_location(l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id);
1426
1427 --populate sites with site usage
1428
1429 INSERT INTO RA_CUSTOMER_MERGES(
1430 CUSTOMER_MERGE_ID
1431 , CREATION_DATE
1432 , CREATED_BY
1433 , LAST_UPDATE_DATE
1434 , LAST_UPDATED_BY
1435 , LAST_UPDATE_LOGIN
1436 , PROCESS_FLAG
1437 , CUSTOMER_ID
1438 , CUSTOMER_NAME
1439 , CUSTOMER_NUMBER
1440 , CUSTOMER_REF
1441 , CUSTOMER_ADDRESS_ID
1442 , CUSTOMER_ADDRESS
1443 , CUSTOMER_SITE_ID
1444 , CUSTOMER_SITE_CODE
1445 , CUSTOMER_LOCATION
1446 , CUSTOMER_PRIMARY_FLAG
1447 , DELETE_DUPLICATE_FLAG
1448 , DUPLICATE_ADDRESS
1449 , DUPLICATE_ADDRESS_ID
1450 , DUPLICATE_ID
1451 , DUPLICATE_LOCATION
1452 , DUPLICATE_NAME
1453 , DUPLICATE_NUMBER
1454 , DUPLICATE_PRIMARY_FLAG
1455 , DUPLICATE_REF
1456 , DUPLICATE_SITE_CODE
1457 , DUPLICATE_SITE_ID
1458 , SET_NUMBER
1459 , CUSTOMER_MERGE_HEADER_ID
1460 , CUSTOMER_FIRST_NAME
1461 , CUSTOMER_LAST_NAME
1462 , CUSTOMER_TYPE
1463 , DUPLICATE_FIRST_NAME
1464 , DUPLICATE_LAST_NAME
1465 , DUPLICATE_TYPE
1466 , CUSTOMER_CREATESAME
1467 , DUPLICATE_SITE_NUMBER
1468 , CUSTOMER_SITE_NUMBER
1469 , ORG_ID)
1470 VALUES( ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
1471 , sysdate /*CREATION_DATE*/
1472 , HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
1473 , sysdate /*LAST_UPDATE_DATE*/
1474 , HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
1475 , HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
1476 , 'SAVED' /*PROCESS_FLAG*/
1477 , l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
1478 , l_to_customer_name /*CUSTOMER_NAME*/
1479 , l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
1480 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref /*CUSTOMER_REF*/
1484 , l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_type /*CUSTOMER_SITE_CODE*/
1481 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id /*CUSTOMER_ADDRESS_ID*/
1482 , l_to_address /*CUSTOMER_ADDRESS*/
1483 , l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id /*CUSTOMER_SITE_ID*/
1485 , l_to_site_use_location /*CUSTOMER_LOCATION*/
1486 , g_primary_flag(l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_to_site_use_obj.acct_site_use_id) /*CUSTOMER_PRIMARY_FLAG*/
1487 , NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
1488 , l_from_address /* DUPLICATE_ADDRESS*/
1489 , l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id /*DUPLICATE_ADDRESS_ID*/
1490 , l_account_merge_request_obj.merge_from_account.account_id/* DUPLICATE_ID*/
1491 , l_from_site_use_location /* DUPLICATE_LOCATION*/
1492 , l_from_customer_name/* DUPLICATE_NAME*/
1493 , l_account_merge_request_obj.merge_from_account.account_number/* DUPLICATE_NUMBER*/
1494 , g_primary_flag(l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id) /*DUPLICATE_PRIMARY_FLAG*/
1495 , l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_orig_sys_ref/* DUPLICATE_REF*/
1496 , l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_type /*DUPLICATE_SITE_CODE*/
1497 , l_account_merge_request_obj.site_merge_details(i).site_use_merge_details(j).merge_from_site_use_obj.acct_site_use_id /*DUPLICATE_SITE_ID*/
1498 , 1 /* SET_NUMBER*/
1499 , l_header_id /* CUSTOMER_MERGE_HEADER_ID*/
1500 , l_to_cust_first_name /* CUSTOMER_FIRST_NAME*/
1501 , l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
1502 , l_to_customer_type /* CUSTOMER_TYPE*/
1503 , l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
1504 , l_from_cust_last_name /* DUPLICATE_LAST_NAME */
1505 , l_from_customer_type /* DUPLICATE_TYPE */
1506 , 'N' /* CUSTOMER_CREATESAME*/
1507 , l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.SITE_NUMBER /*DUPLICATE_SITE_NUMBER*/
1508 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.SITE_NUMBER /*CUSTOMER_SITE_NUMBER */
1509 , l_account_merge_request_obj.site_merge_details(i).org_id /*ORG_ID*/
1510 );
1511
1512 END IF;
1513
1514
1515 END LOOP;
1516
1517 ELSE
1518
1519 --populate sites with out site usage
1520
1521 INSERT INTO RA_CUSTOMER_MERGES(
1522 CUSTOMER_MERGE_ID
1523 , CREATION_DATE
1524 , CREATED_BY
1525 , LAST_UPDATE_DATE
1526 , LAST_UPDATED_BY
1527 , LAST_UPDATE_LOGIN
1528 , PROCESS_FLAG
1529 , CUSTOMER_ID
1530 , CUSTOMER_NAME
1531 , CUSTOMER_NUMBER
1532 , CUSTOMER_REF
1533 , CUSTOMER_ADDRESS_ID
1534 , CUSTOMER_ADDRESS
1535 , CUSTOMER_SITE_ID
1536 , CUSTOMER_SITE_CODE
1537 , CUSTOMER_LOCATION
1538 , CUSTOMER_PRIMARY_FLAG
1539 , DELETE_DUPLICATE_FLAG
1540 , DUPLICATE_ADDRESS
1541 , DUPLICATE_ADDRESS_ID
1542 , DUPLICATE_ID
1543 , DUPLICATE_LOCATION
1544 , DUPLICATE_NAME
1545 , DUPLICATE_NUMBER
1546 , DUPLICATE_PRIMARY_FLAG
1550 , SET_NUMBER
1547 , DUPLICATE_REF
1548 , DUPLICATE_SITE_CODE
1549 , DUPLICATE_SITE_ID
1551 , CUSTOMER_MERGE_HEADER_ID
1552 , CUSTOMER_FIRST_NAME
1553 , CUSTOMER_LAST_NAME
1554 , CUSTOMER_TYPE
1555 , DUPLICATE_FIRST_NAME
1556 , DUPLICATE_LAST_NAME
1557 , DUPLICATE_TYPE
1558 , CUSTOMER_CREATESAME
1559 , DUPLICATE_SITE_NUMBER
1560 , CUSTOMER_SITE_NUMBER
1561 , ORG_ID)
1562 VALUES( ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
1563 , sysdate /*CREATION_DATE*/
1564 , HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
1565 , sysdate /*LAST_UPDATE_DATE*/
1566 , HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
1567 , HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
1568 , 'SAVED' /*PROCESS_FLAG*/
1569 , l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
1570 , l_to_customer_name /*CUSTOMER_NAME*/
1571 , l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
1572 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref /*CUSTOMER_REF*/
1573 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_id /*CUSTOMER_ADDRESS_ID*/
1574 , l_to_address /*CUSTOMER_ADDRESS*/
1575 , -99 /*CUSTOMER_SITE_ID*/
1576 , 'NONE' /*CUSTOMER_SITE_CODE*/
1577 , NULL /*CUSTOMER_LOCATION*/
1578 , 'N' /*CUSTOMER_PRIMARY_FLAG*/
1579 , NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
1580 , l_from_address /* DUPLICATE_ADDRESS*/
1581 , l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.account_site_id /*DUPLICATE_ADDRESS_ID*/
1582 , l_account_merge_request_obj.merge_from_account.account_id /* DUPLICATE_ID*/
1583 , NULL /* DUPLICATE_LOCATION*/
1584 , l_from_customer_name/* DUPLICATE_NAME*/
1585 , l_account_merge_request_obj.merge_from_account.account_number/* DUPLICATE_NUMBER*/
1586 , 'N' /*DUPLICATE_PRIMARY_FLAG*/
1587 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.account_site_orig_sys_ref /* DUPLICATE_REF*/
1588 , 'NONE' /*DUPLICATE_SITE_CODE*/
1589 , -99 /*DUPLICATE_SITE_ID*/
1590 , 1 /* SET_NUMBER*/
1591 , l_header_id /* CUSTOMER_MERGE_HEADER_ID*/
1592 , l_to_cust_first_name /* CUSTOMER_FIRST_NAME*/
1593 , l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
1594 , l_to_customer_type /* CUSTOMER_TYPE*/
1595 , l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
1596 , l_from_cust_last_name /* DUPLICATE_LAST_NAME */
1597 , l_from_customer_type /* DUPLICATE_TYPE */
1598 , 'N' /* CUSTOMER_CREATESAME*/
1599 , l_account_merge_request_obj.site_merge_details(i).merge_from_account_site_obj.SITE_NUMBER /*DUPLICATE_SITE_NUMBER*/
1600 , l_account_merge_request_obj.site_merge_details(i).merge_to_account_site_obj.SITE_NUMBER /*CUSTOMER_SITE_NUMBER */
1601 , l_account_merge_request_obj.site_merge_details(i).org_id /*ORG_ID*/
1602 );
1603
1604 END IF; --site_use_merge_details
1605
1606 END IF;
1607
1611
1608 END LOOP; -- Insert site_merge_details
1609
1610 END IF; -- Insert site_merge_details
1612
1613 --Populate ra_customer_merges with sites to be transferred
1614
1615 IF (l_account_merge_request_obj.merge_to_account.account_id <> l_account_merge_request_obj.merge_from_account.account_id) THEN
1616 l_site_cnt := 0;
1617
1618 BEGIN
1619 SELECT 1 INTO l_site_cnt
1620 FROM hz_cust_acct_sites_all
1621 WHERE cust_account_id = l_account_merge_request_obj.merge_from_account.account_id
1622 AND rownum = 1;
1623 EXCEPTION
1624 WHEN NO_DATA_FOUND THEN
1625 l_site_cnt := 0;
1626 END;
1627
1628 IF l_site_cnt = 0 THEN
1629
1630 INSERT INTO RA_CUSTOMER_MERGES(
1631 CUSTOMER_MERGE_ID
1632 , CREATION_DATE
1633 , CREATED_BY
1634 , LAST_UPDATE_DATE
1635 , LAST_UPDATED_BY
1636 , LAST_UPDATE_LOGIN
1637 , PROCESS_FLAG
1638 , CUSTOMER_ID
1639 , CUSTOMER_NAME
1640 , CUSTOMER_NUMBER
1641 , CUSTOMER_ADDRESS_ID
1642 , CUSTOMER_SITE_ID
1643 , DELETE_DUPLICATE_FLAG
1644 , DUPLICATE_ADDRESS_ID
1645 , DUPLICATE_ID
1646 , DUPLICATE_NAME
1647 , DUPLICATE_NUMBER
1648 , DUPLICATE_SITE_ID
1649 , SET_NUMBER
1650 , CUSTOMER_MERGE_HEADER_ID
1651 , CUSTOMER_FIRST_NAME
1652 , CUSTOMER_LAST_NAME
1653 , CUSTOMER_TYPE
1654 , DUPLICATE_FIRST_NAME
1655 , DUPLICATE_LAST_NAME
1656 , DUPLICATE_TYPE
1657 , CUSTOMER_CREATESAME
1658 , ORG_ID)
1662 , sysdate /*LAST_UPDATE_DATE*/
1659 VALUES( ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
1660 , sysdate /*CREATION_DATE*/
1661 , HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
1663 , HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
1664 , HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
1665 , 'SAVED' /*PROCESS_FLAG*/
1666 , l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
1667 , l_to_customer_name /*CUSTOMER_NAME*/
1668 , l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
1669 , -1 /*CUSTOMER_ADDRESS_ID*/
1670 , -1 /*CUSTOMER_SITE_ID*/
1671 , NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
1672 , -1 /*DUPLICATE_ADDRESS_ID*/
1673 , l_account_merge_request_obj.merge_from_account.account_id /*DUPLICATE_ID*/
1674 , l_from_customer_name/* DUPLICATE_NAME*/
1675 , l_account_merge_request_obj.merge_from_account.account_number /*DUPLICATE_NUMBER*/
1676 , -1 /*DUPLICATE_SITE_ID*/
1677 , 1 /*SET_NUMBER*/
1678 , l_header_id /*CUSTOMER_MERGE_HEADER_ID*/
1679 , l_to_cust_first_name /*CUSTOMER_FIRST_NAME*/
1680 , l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
1681 , l_to_customer_type /* CUSTOMER_TYPE*/
1682 , l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
1683 , l_from_cust_last_name /* DUPLICATE_LAST_NAME */
1684 , l_from_customer_type /*DUPLICATE_TYPE */
1685 , 'N' /*CUSTOMER_CREATESAME*/
1686 , l_account_merge_request_obj.org_id);
1687
1688 ELSE
1689
1690 INSERT INTO RA_CUSTOMER_MERGES(
1691 CUSTOMER_MERGE_ID
1692 , CREATION_DATE
1693 , CREATED_BY
1694 , LAST_UPDATE_DATE
1695 , LAST_UPDATED_BY
1696 , LAST_UPDATE_LOGIN
1697 , PROCESS_FLAG
1698 , CUSTOMER_ID
1699 , CUSTOMER_NAME
1700 , CUSTOMER_NUMBER
1701 , CUSTOMER_REF
1702 , CUSTOMER_ADDRESS_ID
1703 , CUSTOMER_ADDRESS
1704 , CUSTOMER_SITE_ID
1705 , CUSTOMER_SITE_CODE
1706 , CUSTOMER_LOCATION
1707 , CUSTOMER_PRIMARY_FLAG
1708 , DELETE_DUPLICATE_FLAG
1709 , DUPLICATE_ADDRESS
1710 , DUPLICATE_ADDRESS_ID
1711 , DUPLICATE_ID
1712 , DUPLICATE_LOCATION
1713 , DUPLICATE_NAME
1714 , DUPLICATE_NUMBER
1715 , DUPLICATE_PRIMARY_FLAG
1716 , DUPLICATE_REF
1717 , DUPLICATE_SITE_CODE
1718 , DUPLICATE_SITE_ID
1719 , SET_NUMBER
1720 , CUSTOMER_MERGE_HEADER_ID
1721 , CUSTOMER_FIRST_NAME
1722 , CUSTOMER_LAST_NAME
1723 , CUSTOMER_TYPE
1724 , DUPLICATE_FIRST_NAME
1725 , DUPLICATE_LAST_NAME
1726 , DUPLICATE_TYPE
1727 , CUSTOMER_CREATESAME
1728 , DUPLICATE_SITE_NUMBER
1729 , CUSTOMER_SITE_NUMBER
1730 , ORG_ID)
1731 select ra_customer_merges_s.nextval /*CUSTOMER_MERGE_ID*/
1732 , sysdate /*CREATION_DATE*/
1733 , HZ_UTILITY_V2PUB.CREATED_BY /*CREATED_BY*/
1734 , sysdate /*LAST_UPDATE_DATE*/
1735 , HZ_UTILITY_V2PUB.LAST_UPDATED_BY /*LAST_UPDATED_BY*/
1736 , HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN /*LAST_UPDATE_LOGIN*/
1737 , 'SAVED' /*PROCESS_FLAG*/
1738 , l_account_merge_request_obj.merge_to_account.account_id /*CUSTOMER_ID*/
1739 , l_to_customer_name /*CUSTOMER_NAME*/
1740 , l_account_merge_request_obj.merge_to_account.account_number /*CUSTOMER_NUMBER*/
1741 , null /*CUSTOMER_REF*/
1742 , -1 /*CUSTOMER_ADDRESS_ID*/
1743 , substrb(a.concatenated_address, 1, 240) /*CUSTOMER_ADDRESS*/
1744 , -1 /*CUSTOMER_SITE_ID*/
1745 , a.site_use_code /*CUSTOMER_SITE_CODE*/
1746 , a.location /*CUSTOMER_LOCATION*/
1747 , a.primary_flag /*CUSTOMER_PRIMARY_FLAG*/
1748 , NVL(l_account_merge_request_obj.delete_after_merge_flag, 'N') /*DELETE_DUPLICATE_FLAG*/
1749 , substrb(a.concatenated_address, 1, 240) /*DUPLICATE_ADDRESS*/
1750 , a.address_id /*DUPLICATE_ADDRESS_ID*/
1751 , l_account_merge_request_obj.merge_from_account.account_id /*DUPLICATE_ID*/
1752 , a.location /*DUPLICATE_LOCATION*/
1753 , l_from_customer_name/* DUPLICATE_NAME*/
1754 , l_account_merge_request_obj.merge_from_account.account_number /*DUPLICATE_NUMBER*/
1755 , a.primary_flag /*DUPLICATE_PRIMARY_FLAG*/
1756 , a.orig_system_reference /*DUPLICATE_REF*/
1757 , a.site_use_code /*DUPLICATE_SITE_CODE*/
1758 , a.SITE_USE_ID /*DUPLICATE_SITE_ID*/
1759 , 1 /*SET_NUMBER*/
1760 , l_header_id /*CUSTOMER_MERGE_HEADER_ID*/
1761 , l_to_cust_first_name /*CUSTOMER_FIRST_NAME*/
1762 , l_to_cust_last_name /* CUSTOMER_LAST_NAME*/
1763 , l_to_customer_type /* CUSTOMER_TYPE*/
1764 , l_from_cust_first_name /* DUPLICATE_FIRST_NAME*/
1765 , l_from_cust_last_name /* DUPLICATE_LAST_NAME */
1766 , l_from_customer_type /*DUPLICATE_TYPE */
1772 where customer_id = l_account_merge_request_obj.merge_from_account.account_id
1767 , 'Y' /*CUSTOMER_CREATESAME*/
1768 , a.site_number /*DUPLICATE_SITE_NUMBER*/
1769 , NULL /*CUSTOMER_SITE_NUMBER*/
1770 , a.org_id /*ORG_ID*/
1771 from hz_merge_addresses_v a
1773 and a.org_id = decode(l_account_merge_request_obj.org_id,-1,a.org_id,l_account_merge_request_obj.org_id)
1774 and ((a.site_use_id <> -99 AND a.site_use_id not in (select duplicate_site_id
1775 from ra_customer_merges m
1776 where m.customer_merge_header_id = l_header_id))
1777
1778 OR
1779
1780 (a.site_use_id = -99 AND a.address_id not in (select duplicate_address_id
1781 from ra_customer_merges m
1782 where m.customer_merge_header_id = l_header_id
1783 and m.duplicate_site_id = -99))
1784 );
1785
1786 --Append location with C if same location already exists for merge-to customer
1787
1788 UPDATE ra_customer_merges m
1789 SET customer_location = duplicate_location||'-C'
1790 WHERE m.customer_merge_header_id = l_header_id
1791 AND m.customer_createsame = 'Y'
1792 AND m.duplicate_site_id <> -99
1793 AND exists (SELECT 'Y'
1794 FROM hz_cust_site_uses_all use,hz_cust_acct_sites_all site
1795 WHERE site.cust_account_id = m.customer_id
1796 AND site.org_id = m.org_id --SSUptake
1797 AND site.cust_acct_site_id = use.cust_acct_site_id
1798 AND site.org_id = use.org_id --SSUptake
1799 AND use.site_use_code = m.duplicate_site_code
1800 AND use.location = m.duplicate_location
1801 AND ROWNUM = 1);
1802
1803 END IF; --l_site_cnt
1804
1805 END IF; -- transfer
1806
1807 x_customer_merge_header_id := l_header_id;
1808
1809 l_site_cnt := 0;
1810
1811 BEGIN
1812 SELECT 1 INTO l_site_cnt
1813 FROM ra_customer_merges
1814 WHERE customer_merge_header_id = l_header_id
1815 AND rownum = 1;
1816 EXCEPTION
1817 WHEN NO_DATA_FOUND THEN
1818 l_site_cnt := 0;
1819 END;
1820
1821 IF l_site_cnt = 0 THEN
1822
1823 FND_MESSAGE.SET_NAME('AR','HZ_CM_SAME_CUST_MERGE');
1824 FND_MSG_PUB.ADD();
1825 RAISE fnd_api.g_exc_error;
1826
1827 END IF;
1828
1829
1830 COMMIT;
1831
1832 IF p_submit_request = 'Y' THEN
1833
1834 IF l_account_merge_request_obj.priority IS NULL THEN
1835 l_account_merge_request_obj.priority := 'P3';
1836 END IF;
1837
1838 IF l_account_merge_request_obj.org_id = -1 THEN
1839 l_ou_name := 'All';
1840 ELSE
1841
1842 select hr.name into l_ou_name
1843 from hr_operating_units hr
1844 where hr.organization_id = l_account_merge_request_obj.org_id;
1845
1846 END IF;
1847
1848 x_account_merge_request_id := FND_REQUEST.SUBMIT_REQUEST('AR', 'RAXMRG', 'Customer Merge',
1849 to_char(sysdate,'DD-MON-YY HH24:MI:SS') ,FALSE,
1850 'PROCESSING', l_account_merge_request_obj.merge_from_account.account_number,
1851 l_account_merge_request_obj.merge_to_account.account_number,
1852 l_account_merge_request_obj.priority,1,l_ou_name,chr(0),
1853 '','','','',
1854 '','','','','','','','','','',
1855 '','','','','','','','','','',
1856 '','','','','','','','','','',
1857 '','','','','','','','','','',
1858 '','','','','','','','','','',
1859 '','','','','','','','','','',
1860 '','','','','','','','','','',
1861 '','','','','','','','','','',
1862 '','','','','','','','','' );
1863 IF x_account_merge_request_id > 0 THEN
1864
1865 UPDATE ra_customer_merge_headers
1866 SET request_id = x_account_merge_request_id
1867 WHERE customer_merge_header_id = l_header_id;
1868
1869 UPDATE ra_customer_merges
1870 SET request_id = x_account_merge_request_id
1871 WHERE customer_merge_header_id = l_header_id;
1872
1873 END IF;
1874
1875 COMMIT;
1876 ELSE
1877
1878 x_account_merge_request_id := NULL;
1879
1880 END IF; --p_submit_request
1881
1882 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1883 hz_utility_v2pub.debug(p_message=>'create_account_merge_request(-)',
1884 p_prefix=>l_debug_prefix,
1885 p_msg_level=>fnd_log.level_procedure);
1886 END IF;
1887
1888
1889 EXCEPTION
1890 WHEN fnd_api.g_exc_error THEN
1891 x_return_status := fnd_api.g_ret_sts_error;
1892
1893 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1894 p_count => x_msg_count,
1895 p_data => x_msg_data);
1896
1897 -- Debug info.
1898 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1899 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1900 p_msg_data=>x_msg_data,
1901 p_msg_type=>'ERROR',
1902 p_msg_level=>fnd_log.level_error);
1903 END IF;
1904 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1905 hz_utility_v2pub.debug(p_message=>'create_account_merge_request(-)',
1906 p_prefix=>l_debug_prefix,
1907 p_msg_level=>fnd_log.level_procedure);
1908 END IF;
1909 WHEN fnd_api.g_exc_unexpected_error THEN
1910 x_return_status := fnd_api.g_ret_sts_unexp_error;
1911
1912 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1913 p_count => x_msg_count,
1914 p_data => x_msg_data);
1915
1916 -- Debug info.
1917 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1918 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1919 p_msg_data=>x_msg_data,
1920 p_msg_type=>'UNEXPECTED ERROR',
1921 p_msg_level=>fnd_log.level_error);
1922 END IF;
1926 p_msg_level=>fnd_log.level_procedure);
1923 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1924 hz_utility_v2pub.debug(p_message=>'create_account_merge_request(-)',
1925 p_prefix=>l_debug_prefix,
1927 END IF;
1928 WHEN OTHERS THEN
1929 x_return_status := fnd_api.g_ret_sts_unexp_error;
1930
1931 FND_MESSAGE.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1932 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1933 fnd_msg_pub.add;
1934
1935 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1936 p_count => x_msg_count,
1937 p_data => x_msg_data);
1938
1939 -- Debug info.
1940 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1941 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1942 p_msg_data=>x_msg_data,
1943 p_msg_type=>'SQL ERROR',
1944 p_msg_level=>fnd_log.level_error);
1945 END IF;
1946 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1947 hz_utility_v2pub.debug(p_message=>'create_account_merge_request(-)',
1948 p_prefix=>l_debug_prefix,
1949 p_msg_level=>fnd_log.level_procedure);
1950 END IF;
1951
1952 END create_account_merge_request;
1953
1954 END HZ_ACCOUNT_MERGE_PUB;