1 PACKAGE BODY HZ_EMAIL_DOMAINS_V2PUB AS
2 /*$Header: ARH2EMDB.pls 120.14 2005/06/16 21:07:20 jhuang noship $ */
3
4 g_debug_count NUMBER := 0;
5 --g_debug BOOLEAN := FALSE;
6
7 /*PROCEDURE enable_debug;
8
9 PROCEDURE disable_debug;
10 */
11
12
13 --------------------------------------
14 -- private procedures and functions
15 --------------------------------------
16
17 /**
18 * PRIVATE PROCEDURE enable_debug
19 *
20 * DESCRIPTION
21 * Turn on debug mode.
22 *
23 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
24 * hz_utility_v2pub.enable_debug
25 *
26 */
27
28 /*PROCEDURE enable_debug IS
29
30 BEGIN
31 g_debug_count := g_debug_count + 1;
32
33 IF g_debug_count = 1 THEN
34 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
35 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
36 THEN
37 hz_utility_v2pub.enable_debug;
38 g_debug := TRUE;
39 END IF;
40 END IF;
41 END enable_debug;
42 */
43
44 /**
45 * PRIVATE PROCEDURE disable_debug
46 *
47 * DESCRIPTION
48 * Turn off debug mode.
49 *
50 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
51 * hz_utility_v2pub.disable_debug
52 *
53 */
54
55 /*PROCEDURE disable_debug IS
56
57 BEGIN
58
59 IF g_debug THEN
60 g_debug_count := g_debug_count - 1;
61
62 IF g_debug_count = 0 THEN
63 hz_utility_v2pub.disable_debug;
64 g_debug := FALSE;
65 END IF;
66 END IF;
67
68 END disable_debug;
69 */
70
71 /**
72 * PRIVATE PROCEDURE validate_mandatory
73 *
74 * DESCRIPTION
75 * validate_mandatory if the column type is VARCHAR2.
76 *
77 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
78 *
79 */
80
81 PROCEDURE validate_mandatory (
82 p_create_update_flag IN VARCHAR2,
83 p_column IN VARCHAR2,
84 p_column_value IN VARCHAR2,
85 p_restricted IN VARCHAR2 DEFAULT 'N',
86 x_return_status IN OUT NOCOPY VARCHAR2
87 ) IS
88
89 l_error BOOLEAN := FALSE;
90
91 BEGIN
92
93 IF p_restricted = 'N' THEN
94 IF (p_create_update_flag = 'C' AND
95 (p_column_value IS NULL OR
96 p_column_value = fnd_api.g_miss_char)) OR
97 (p_create_update_flag = 'U' AND
98 p_column_value = fnd_api.g_miss_char)
99 THEN
100 l_error := TRUE;
101 END IF;
102 ELSE
103 IF (p_column_value IS NULL OR
104 p_column_value = fnd_api.g_miss_char)
105 THEN
106 l_error := TRUE;
107 END IF;
108 END IF;
109
110 IF l_error THEN
111 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
112 fnd_message.set_token('COLUMN', p_column);
113 fnd_msg_pub.add;
114 x_return_status := fnd_api.g_ret_sts_error;
115 END IF;
116
117 END validate_mandatory;
118
119 /**
120 * PRIVATE PROCEDURE validate_mandatory
121 *
122 * DESCRIPTION
123 * validate_mandatory if the column type is NUMBER.
124 *
125 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
126 *
127 */
128
129 PROCEDURE validate_mandatory (
130 p_create_update_flag IN VARCHAR2,
131 p_column IN VARCHAR2,
132 p_column_value IN NUMBER,
133 p_restricted IN VARCHAR2 DEFAULT 'N',
134 x_return_status IN OUT NOCOPY VARCHAR2
135 ) IS
136
137 l_error BOOLEAN := FALSE;
138
139 BEGIN
140
141 IF p_restricted = 'N' THEN
142 IF (p_create_update_flag = 'C' AND
143 (p_column_value IS NULL OR
144 p_column_value = fnd_api.g_miss_num)) OR
145 (p_create_update_flag = 'U' AND
146 p_column_value = fnd_api.g_miss_num)
147 THEN
148 l_error := TRUE;
149 END IF;
150 ELSE
151 IF (p_column_value IS NULL OR
152 p_column_value = fnd_api.g_miss_num)
153 THEN
154 l_error := TRUE;
155 END IF;
156 END IF;
157
158 IF l_error THEN
159 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
160 fnd_message.set_token('COLUMN', p_column);
161 fnd_msg_pub.add;
162 x_return_status := fnd_api.g_ret_sts_error;
163 END IF;
164
165 END validate_mandatory;
166
167 PROCEDURE do_email_domain_transfer(
168 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
169 x_to_id IN OUT NOCOPY NUMBER,
170 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
171 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
172 x_return_status IN OUT NOCOPY VARCHAR2
173 ) IS
174
175 BEGIN
176
177 IF NOT (x_to_id <> FND_API.G_MISS_NUM AND
178 x_to_id <> p_from_id) THEN
179
180 --commented out because this part we are doing in email_domain_merge
181 --procedure
182 --UPDATE HZ_EMAIL_DOMAINS
183 --SET
184 -- STATUS = 'M',
185 -- last_update_date = hz_utility_pub.last_update_date,
186 -- last_updated_by = hz_utility_pub.user_id,
187 -- last_update_login = hz_utility_pub.last_update_login
188 --WHERE email_domain_id = p_from_id;
189 --ELSE
190 -- Update and set party_id = p_to_fk_id where pk = from_id
191 UPDATE HZ_EMAIL_DOMAINS
192 SET
193 party_id = p_to_fk_id,
194 last_update_date = hz_utility_pub.last_update_date,
195 last_updated_by = hz_utility_pub.user_id,
196 last_update_login = hz_utility_pub.last_update_login
197 WHERE email_domain_id = p_from_id;
198
199 END IF;
200 EXCEPTION
201 WHEN OTHERS THEN
202 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
203 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
204 FND_MSG_PUB.ADD;
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 END do_email_domain_transfer;
207
208 /**
209 * FUNCTION transpose_domain
210 *
211 * DESCRIPTION
212 * This API will accept an input domain, and return it with the segments
213 * transposed (reversed). The return value should be all-uppercase.
214 *
215 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
216 *
217 * ARGUMENTS
218 * IN:
219 * p_domain_name Input domain
220 *
221 * IN/OUT:
222 * OUT:
223 *
224 * NOTES
225 *
226 * MODIFICATION HISTORY
227 *
228 * 21-APR-2003 Sreedhar Mohan o Created.
229 *
230 */
231
232 FUNCTION transpose_domain(
233 p_domain_name IN VARCHAR2
234 ) RETURN VARCHAR2
235 IS
236
237 l_ret_domain VARCHAR2(2000) := NULL;
238 l_domain_name VARCHAR2(2000) := p_domain_name;
239 l_debug_prefix VARCHAR2(30) := '';
240
241 BEGIN
242 --enable_debug;
243
244 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
245 hz_utility_v2pub.debug(p_message=>'transpose_domain (+)',
246 p_prefix=>l_debug_prefix,
247 p_msg_level=>fnd_log.level_procedure);
248 END IF;
249 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
250 hz_utility_v2pub.debug(p_message=>'l_domain_name: ' || l_domain_name,
251 p_prefix =>l_debug_prefix,
252 p_msg_level=>fnd_log.level_statement);
253 END IF;
254
255 WHILE instr(l_domain_name, '.') > 0 LOOP
256 l_ret_domain := l_ret_domain || substrb(l_domain_name, instrb( l_domain_name, '.', -1, 1)+1, lengthb(l_domain_name)) || '.';
257 l_domain_name := substrb( l_domain_name, 0, instrb( l_domain_name, '.', -1, 1) -1);
258 END LOOP;
259 l_ret_domain := l_ret_domain || l_domain_name;
260 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
261 hz_utility_v2pub.debug(p_message=>'l_ret_domain: ' || l_ret_domain,
262 p_prefix =>l_debug_prefix,
263 p_msg_level=>fnd_log.level_statement);
264 END IF;
265 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
266 hz_utility_v2pub.debug(p_message=>'transpose_domain (-)',
267 p_prefix=>l_debug_prefix,
268 p_msg_level=>fnd_log.level_procedure);
269 END IF;
270 --disable_debug;
271
272 RETURN upper(l_ret_domain);
273
274 END transpose_domain;
275
276 /**
277 * PROCEDURE create_email_domain
278 *
279 * DESCRIPTION
280 * This API will insert a row into the HZ_EMAIL_DOMAINS table. It should
281 * internally call the function defined above (transpose_domain), and
282 * insert the transposed value as well.
283 *
284 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
285 *
286 * ARGUMENTS
287 * IN:
288 * p_party_id Initialize message stack if it is set to
289 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
290 * p_domain_name Financial report record.
291 * IN/OUT:
292 * OUT:
293 * x_return_status Return status after the call. The status can
294 * be FND_API.G_RET_STS_SUCCESS (success),
295 * FND_API.G_RET_STS_ERROR (error),
296 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
297 * x_msg_count Number of messages in message stack.
298 * x_msg_data Message text if x_msg_count is 1.
299 *
300 * NOTES
301 *
302 * MODIFICATION HISTORY
303 *
304 * 21-APR-2003 Sreedhar Mohan o Created.
305 *
306 */
307
308 PROCEDURE create_email_domain(
309 p_party_id IN NUMBER,
310 p_domain_name IN VARCHAR2,
311 x_return_status OUT NOCOPY VARCHAR2,
312 x_msg_count OUT NOCOPY NUMBER,
313 x_msg_data OUT NOCOPY VARCHAR2) IS
314
315 l_debug_prefix VARCHAR2(30) := '';
316 l_dummy VARCHAR2(32);
317
318 CURSOR c_unique_email_domain ( p_party_id IN NUMBER,
319 p_domain_name IN VARCHAR2) IS
320 SELECT 'Y'
321 FROM hz_email_domains
322 WHERE party_id = p_party_id
323 AND domain_name = p_domain_name;
324
325 BEGIN
326
327 --enable_debug;
328 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
329 hz_utility_v2pub.debug(p_message=>'create_email_domain (+)',
330 p_prefix=>l_debug_prefix,
331 p_msg_level=>fnd_log.level_procedure);
332 END IF;
333
334 SAVEPOINT create_email_domain;
335
336 -------------------------------------
337 -- validation for logical primary_key
338 -------------------------------------
339
340 OPEN c_unique_email_domain ( p_party_id, p_domain_name);
341
342 FETCH c_unique_email_domain INTO l_dummy;
343
344 -- combination key is not unique, push an error onto the stack.
345 IF NVL(c_unique_email_domain%FOUND, FALSE) THEN
346 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
347 fnd_message.set_token('COLUMN', 'party_id and domain_name combination');
348 fnd_msg_pub.add;
349 x_return_status := fnd_api.g_ret_sts_error;
350 END IF;
351 CLOSE c_unique_email_domain;
352
353 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
354 hz_utility_v2pub.debug(p_message=>'The following column combination should be unique:' ||
355 ' PARTY_ID, DOMAIN_NAME. ' ||
356 ' x_return_status = ' || x_return_status,
357 p_prefix =>l_debug_prefix,
358 p_msg_level=>fnd_log.level_statement);
359 END IF;
360
361 -------------------------------------
362 -- validation for party_id
363 -------------------------------------
364
365 --party_id is a mandatory field
366 validate_mandatory (
367 p_create_update_flag => 'C',
368 p_column => 'party_id',
369 p_column_value => p_party_id,
370 x_return_status => x_return_status);
371
372 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
373 hz_utility_v2pub.debug(p_message=>'party_id is mandatory field. ' ||
374 'x_return_status = ' || x_return_status,
375 p_prefix =>l_debug_prefix,
376 p_msg_level=>fnd_log.level_statement);
377 END IF;
378
379 -- party_id has foreign key HZ_PARTIES.PARTY_ID
380 IF p_party_id IS NOT NULL
381 AND
382 p_party_id <> fnd_api.g_miss_num
383 THEN
384 BEGIN
385 SELECT 'Y'
386 INTO l_dummy
387 FROM hz_parties p
388 WHERE p.party_id = p_party_id;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
393 fnd_message.set_token('FK', 'party_id');
394 fnd_message.set_token('COLUMN', 'party_id');
395 fnd_message.set_token('TABLE', 'hz_parties');
396 fnd_msg_pub.add;
397 x_return_status := fnd_api.g_ret_sts_error;
398 END;
399
400
401 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
402 hz_utility_v2pub.debug(p_message=>'party_id has foreign key hz_parties.party_id. ' ||
403 'x_return_status = ' || x_return_status,
404 p_prefix =>l_debug_prefix,
405 p_msg_level=>fnd_log.level_statement);
406 END IF;
407
408 END IF;
409
410 -----------------------------
411 -- validation for domain_name
412 -----------------------------
413 validate_mandatory (
414 p_create_update_flag => 'C',
415 p_column => 'domain_name',
416 p_column_value => p_domain_name,
417 x_return_status => x_return_status);
418
419 -------------------------------
420 -- Insert into HZ_EMAIL_DOMAINS
421 -------------------------------
422 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
423 RAISE FND_API.G_EXC_ERROR;
424 END IF;
425
426 INSERT INTO HZ_EMAIL_DOMAINS(
427 EMAIL_DOMAIN_ID
428 , PARTY_ID
429 , TRANSPOSED_DOMAIN
430 , DOMAIN_NAME
431 , STATUS
432 , CREATION_DATE
433 , LAST_UPDATE_LOGIN
434 , LAST_UPDATE_DATE
435 , LAST_UPDATED_BY
436 , CREATED_BY ) VALUES (
437 hz_email_domains_s.nextval
438 , p_party_id
439 , transpose_domain( p_domain_name)
440 , p_domain_name
441 , 'A'
442 , hz_utility_v2pub.creation_date
443 , hz_utility_v2pub.last_update_login
444 , hz_utility_v2pub.last_update_date
445 , hz_utility_v2pub.last_updated_by
446 , hz_utility_v2pub.created_by
447 );
448
449 -- VJN introduced changes for the domain name project for ensuring
450 -- DQM sync happens, when ever an email domain is created, using this API.
451 BEGIN
452 select party_type into l_dummy
453 from hz_parties
454 where party_id = p_party_id;
455
456 IF l_dummy = 'ORGANIZATION'
457 THEN
458 HZ_DQM_SYNC.sync_org(p_party_id, 'U' );
459 ELSIF l_dummy = 'PERSON'
460 THEN
461 HZ_DQM_SYNC.sync_person(p_party_id, 'U' );
462 END IF;
463
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
467 fnd_message.set_token('FK', 'party_id');
468 fnd_message.set_token('COLUMN', 'party_id');
469 fnd_message.set_token('TABLE', 'hz_parties');
470 fnd_msg_pub.add;
471 x_return_status := fnd_api.g_ret_sts_error;
472
473
474 END;
475
476 x_return_status := FND_API.G_RET_STS_SUCCESS;
477 --disable_debug;
478
479 EXCEPTION
480 WHEN FND_API.G_EXC_ERROR THEN
481 ROLLBACK TO create_email_domain;
482 x_return_status := fnd_api.g_ret_sts_error;
483 WHEN OTHERS THEN
484 ROLLBACK TO create_email_domain;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
487 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
488 FND_MSG_PUB.ADD;
489
490 END create_email_domain;
491
492 PROCEDURE email_domains_merge(
493 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
494 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
495 x_to_id IN OUT NOCOPY NUMBER,
496 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
497 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
498 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
499 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
500 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
501 x_return_status OUT NOCOPY VARCHAR2
502
503 ) IS
504
505 l_to_id NUMBER;
506 l_dup_exists VARCHAR2(20);
507
508 BEGIN
509
510 IF (x_to_id IS NULL) THEN
511 l_to_id := FND_API.G_MISS_NUM;
512 ELSE
513 l_to_id := x_to_id;
514 END IF;
515
516 x_return_status := FND_API.G_RET_STS_SUCCESS;
517
518 check_params(p_entity_name, p_from_id, l_to_id, NULL,
519 p_from_id, p_par_entity_name,
520 'HZ_EMAIL_DOMAINS_V2PUB.email_domains_merge',
521 'HZ_EMAIL_DOMAINS','HZ_PARTIES',
522 'EMAIL_DOMAIN_ID', 'PARTY_ID',x_return_status);
523
524 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
525 l_dup_exists := HZ_EMAIL_DOMAINS_V2PUB.check_email_domain_dup(
526 p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
527 x_return_status);
528 END IF;
529
530 --since hz_email_domains does n't has its own id, p_from_id=p_from_fk_id
531 --and p_to_fk_id = party_id
532 IF (x_return_status =FND_API.G_RET_STS_SUCCESS AND l_dup_exists = FND_API.G_FALSE) THEN
533
534 do_email_domain_transfer(p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
535 x_return_status);
536 ELSIF (l_dup_exists = FND_API.G_TRUE) THEN
537 UPDATE HZ_EMAIL_DOMAINS
538 SET
539 STATUS = 'M',
540 last_update_date = hz_utility_pub.last_update_date,
541 last_updated_by = hz_utility_pub.user_id,
542 last_update_login = hz_utility_pub.last_update_login
543 WHERE email_domain_id = p_from_id;
544 END IF;
545
546 x_to_id := l_to_id;
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
551 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
552 FND_MSG_PUB.ADD;
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554 END email_domains_merge;
555
556 FUNCTION check_email_domain_dup(
557 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
558 x_to_id IN OUT NOCOPY NUMBER,
559 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
560 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
561 x_return_status IN OUT NOCOPY VARCHAR2)
562 RETURN VARCHAR2 IS
563
564 CURSOR dupcheck IS
565 SELECT PARTY_ID
566 FROM HZ_EMAIL_DOMAINS
567 WHERE party_id = p_to_fk_id
568 AND DOMAIN_NAME = ( SELECT DOMAIN_NAME
569 FROM HZ_EMAIL_DOMAINS
570 WHERE EMAIL_DOMAIN_ID= p_from_id);
571
572 l_record_id NUMBER;
573
574 BEGIN
575
576 x_to_id := FND_API.G_MISS_NUM;
577
578 OPEN dupcheck;
579 FETCH dupcheck INTO l_record_id;
580 IF dupcheck%NOTFOUND THEN
581 CLOSE dupcheck;
582 RETURN FND_API.G_FALSE;
583 END IF;
584
585 x_to_id := l_record_id;
586 CLOSE dupcheck;
587 RETURN FND_API.G_TRUE;
588
589 END check_email_domain_dup;
590
591 PROCEDURE check_params(
592 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
593 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
594 p_to_id IN NUMBER:=FND_API.G_MISS_NUM,
595 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
596 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
597 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
598 p_proc_name IN VARCHAR2,
599 p_exp_ent_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
600 p_exp_par_ent_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
601 p_pk_column IN VARCHAR2:=FND_API.G_MISS_CHAR,
602 p_par_pk_column IN VARCHAR2:=FND_API.G_MISS_CHAR,
603 x_return_status IN OUT NOCOPY VARCHAR2
604 ) IS
605
606 BEGIN
607 IF (p_entity_name <> p_exp_ent_name OR
608 p_par_entity_name <> p_exp_par_ent_name) THEN
609 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_ENTITIES');
610 FND_MESSAGE.SET_TOKEN('ENTITY' ,p_entity_name);
611 FND_MESSAGE.SET_TOKEN('PENTITY' ,p_par_entity_name);
612 FND_MESSAGE.SET_TOKEN('MPROC' ,p_proc_name);
613 FND_MSG_PUB.ADD;
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 END IF;
616
617 IF (p_from_id = FND_API.G_MISS_NUM) THEN
618 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_FROM_REC');
619 FND_MESSAGE.SET_TOKEN('MPROC' ,p_proc_name);
620 FND_MESSAGE.SET_TOKEN('ENTITY',p_entity_name);
621 FND_MESSAGE.SET_TOKEN('PKCOL',p_pk_column);
622 FND_MESSAGE.SET_TOKEN('PKVALUE',p_to_id);
623 FND_MSG_PUB.ADD;
624 x_return_status := FND_API.G_RET_STS_ERROR;
625 END IF;
626
627 IF (p_exp_par_ent_name <> FND_API.G_MISS_CHAR AND
628 p_to_fk_id = FND_API.G_MISS_NUM ) THEN
629 FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_MERGE_FROM_PAR_REC');
630 FND_MESSAGE.SET_TOKEN('MPROC' ,p_proc_name);
631 FND_MESSAGE.SET_TOKEN('ENTITY',p_par_entity_name);
632 FND_MESSAGE.SET_TOKEN('PKCOL',p_pk_column);
633 FND_MESSAGE.SET_TOKEN('PKVALUE',p_to_id);
634 FND_MSG_PUB.ADD;
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 END IF;
637 EXCEPTION
638 WHEN OTHERS THEN
639 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
640 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
641 FND_MSG_PUB.ADD;
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 END check_params;
644
645 ---------------- VJN INTRODUCED -----------------------------------------------------
646
647 /* -- will keep this for posterity
648
649
650 PROCEDURE extract_extension(
651 p_input_str VARCHAR2,
652 x_first_seg OUT NOCOPY VARCHAR2,
653 x_extension OUT NOCOPY VARCHAR2) IS
654
655 tmp NUMBER;
656 l_prev_tok VARCHAR2(2000);
657 l_tok VARCHAR2(2000);
658 l_leftover VARCHAR2(2000);
659 BEGIN
660
661 if (instrb(p_input_str,'.'))=0 THEN
662 x_extension:=p_input_str;
663 x_first_seg:=null;
664 return;
665 end if;
666
667 l_leftover := p_input_str; -- = UK.ORACLE.TV
668 l_prev_tok := null;
669 l_tok := HZ_DQM_SEARCH_UTIL.strtok(p_input_str,1,'.');
670 -- UK
671 WHILE l_tok IS NOT NULL LOOP
672 BEGIN
673 SELECT 1 INTO tmp FROM AR_LOOKUPS WHERE
674 lookup_type = 'HZ_DOMAIN_SUFFIX_LIST'
675 and lookup_code = l_leftover;
676
677 x_first_seg := l_prev_tok;
678 x_extension:= l_leftover;
679 RETURN;
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 l_prev_tok:=l_tok;
683
684 l_leftover := replace(l_leftover,l_tok||'.');
685 l_tok := HZ_DQM_SEARCH_UTIL.strtok;
686
687 END;
688 END LOOP;
689
690 x_extension:=l_prev_tok;
691
692 l_leftover:= replace(p_input_str,'.'||x_extension);
693 x_first_seg:=substrb(l_leftover,instrb(l_leftover,'.',-1)+1);
694 END;
695 */
696 -----------------------------------------------------------------------------------------
697 -- EMAIL ADDRESS: [email protected]
698 -- FULL DOMAIN: uk.oracle.com
699 -- CORE DOMAIN: oracle.com
700
701 -- this will return the domain name with shortest length or the concatenation of
702 -- all domain names of a given party
703 FUNCTION get_email_domains(
704 p_party_id IN NUMBER,
705 p_entity IN VARCHAR2,
706 p_attribute IN VARCHAR2,
707 p_context IN VARCHAR2 )
708 RETURN VARCHAR2 IS
709 l_ret_str varchar2(32000);
710
711 BEGIN
712 IF p_context = 'STAGE'
713 THEN
714 FOR email_domains_cur in
715 (select domain_name
716 from hz_email_domains
717 where party_id = p_party_id)
718 LOOP
719 l_ret_str := l_ret_str || ' ' || ltrim(rtrim(email_domains_cur.domain_name)) ;
720 END LOOP;
721 ELSE
722 FOR email_domain_cur in
723 (
724 select d_name
725 from
726 (select domain_name as d_name
727 from hz_email_domains
728 where party_id = p_party_id
729 order by length(domain_name)
730 )
731 where rownum = 1
732 )
733 LOOP
734 l_ret_str := ltrim(rtrim(email_domain_cur.d_name));
735 END LOOP;
736 END IF;
737 -- final treatment to get rid of trailing/leading spaces, if any
738 l_ret_str := ltrim(rtrim(l_ret_str));
739 return l_ret_str ;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 FND_MESSAGE.SET_NAME('AR', 'HZ_EMAIL_DOMAINS_V2PUB');
744 FND_MESSAGE.SET_TOKEN('PROC' ,'GET_EMAIL_DOMAINS');
745 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
746 FND_MSG_PUB.ADD;
747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
748 END get_email_domains ;
749
750
751 -- this will return the concatenation of all full domain names
752 -- given a concatenation of email addresses
753 FUNCTION FULL_DOMAIN(
754 p_input_str IN VARCHAR2,
755 p_language IN VARCHAR2,
756 p_attribute_name IN VARCHAR2,
757 p_entity_name IN VARCHAR2)
758 RETURN VARCHAR2
759 IS
760 treated_input_str VARCHAR2(4000);
761 tempstr VARCHAR2(4000);
762 retstr VARCHAR2(4000);
763 l_tok VARCHAR2(4000);
764 tmp NUMBER;
765 BEGIN
766
767
768 -- remove leading/trailing spaces
769 -- change all alphabetic characters to upper case
770 -- all numeric characters as is
771 -- all other characters to space
772 -- "." and "@" will remain as is
773 treated_input_str:= LTRIM(RTRIM(upper(p_input_str)));
774 -- dbms_output.put_line('Input string is ' || treated_input_str);
775 treated_input_str:= LTRIM(RTRIM(treated_input_str,'.'),'.');
776 -- empty strings do not go far
777 IF (treated_input_str IS NULL OR treated_input_str = '')
778 THEN
779 return '';
780 END IF ;
781
782 -- replace double spaces by single spaces
783 WHILE instrb(treated_input_str,' ')>0 LOOP
784 treated_input_str := REPLACE(treated_input_str, ' ',' ');
785 END LOOP;
786
787
788 -- build the return string by tokenization
789 -- tokenize the treated input string, with the delimiter
790 -- being a space.
791 l_tok := HZ_DQM_SEARCH_UTIL.strtok(treated_input_str,1,' ');
792
793 -- cycle through the tokens and construct the return string
794 -- by appending the right part of the @ of each token
795 WHILE l_tok IS NOT NULL
796 LOOP
797 -- get the full domain ie., the string to the right of the @
798 tempstr := substr(l_tok, instrb(l_tok,'@') + 1 );
799
800 -- dbms_output.put_line('after stripping @ full domain is' || tempstr);
801
802 -- do the ISP check on the tempstr
803 BEGIN
804 SELECT 1 INTO tmp FROM AR_LOOKUPS
805 WHERE
806 lookup_type = 'HZ_DOMAIN_ISP_LIST'
807 and lookup_code = tempstr ;
808
809 -- dbms_output.put_line('ISP validation fails');
810
811 l_tok := HZ_DQM_SEARCH_UTIL.strtok;
812
813 EXCEPTION
814 WHEN NO_DATA_FOUND THEN
815 retstr := retstr || ' '|| tempstr ;
816 l_tok := HZ_DQM_SEARCH_UTIL.strtok;
817 END;
818 END LOOP;
819
820 -- final treatment to get rid of trailing/leading spaces, if any
821 retstr := ltrim(rtrim(retstr));
822
823 return retstr;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 FND_MESSAGE.SET_NAME('AR', 'HZ_EMAIL_DOMAINS_V2PUB');
828 FND_MESSAGE.SET_TOKEN('PROC' ,'FULL_DOMAIN');
829 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
830 FND_MSG_PUB.ADD;
831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
832
833 END FULL_DOMAIN;
834
835
836
837 -- Given a full domain, this would extract the extension
838 -- and the first segment, with the validation being driven either
839 -- by a lookup , if it exists or by the following default assumption
840 -- extension = anything to the right of the first dot from backwards
841 -- first segment = any string to the left of the first dot ( delimiter of
842 -- "." would apply if there are more dots than one)
843
844 PROCEDURE extract_ext_and_segment(
845 p_input_str VARCHAR2,
846 x_first_seg OUT NOCOPY VARCHAR2,
847 x_extension OUT NOCOPY VARCHAR2)
848 IS
849 treated_input_str VARCHAR2(4000);
850 dot_occurence number;
851 tmp NUMBER;
852 pos number;
853 len number;
854
855 BEGIN
856 -- extreme case 1: if input string has no dots, extension is input string
857 -- and first segment is null
858 IF (instrb(p_input_str,'.'))=0
859 THEN
860 x_extension:=p_input_str;
861 x_first_seg:=null;
862 return;
863 ELSE
864 -- remove leading dot from input string
865 treated_input_str := ltrim(p_input_str,'.');
866
867 -- count the number of dots, defaulting to 1 at first
868 dot_occurence := 1 ;
869
870 WHILE instrb(treated_input_str,'.',1,dot_occurence) > 0
871 LOOP
872 dot_occurence := dot_occurence + 1;
873 END LOOP;
874
875 -- this will be the number of dots in the final domain name
876 dot_occurence := dot_occurence - 1;
877
878 -- extreme case 2 : if the input string has no dots after removal of
879 -- the leading dot, then we take extension to be what ever is left
880 IF dot_occurence = 0
881 THEN
882 x_extension := treated_input_str;
883 x_first_seg:=null;
884 return;
885 ELSE
886 FOR I IN 0..dot_occurence
887 LOOP
888 BEGIN
889 IF I = 0
890 THEN
891 x_extension := treated_input_str;
892 ELSE
893 x_extension := substr(treated_input_str,
894 instrb(treated_input_str,'.',1,I) + 1 );
895 END IF;
896
897 SELECT 1 INTO tmp FROM AR_LOOKUPS
898 WHERE
899 lookup_type = 'HZ_DOMAIN_SUFFIX_LIST'
900 and lookup_code = x_extension;
901
902 -- extreme case 3 : if the input string matches an extension
903 -- as is then first seg is null
904 IF I = 0
905 THEN
906 x_extension := treated_input_str;
907 x_first_seg := null;
908 return;
909 -- normal case
910 ELSIF I = 1
911 THEN
912 pos := 1;
913 len := instrb(treated_input_str,'.',1,1 ) - 1;
914
915 ELSE
916 pos := instrb(treated_input_str,'.',1, I-1) + 1 ;
917 len := instrb(treated_input_str,'.',1,I) - pos ;
918
919
920 END IF;
921
922 -- dbms_output.put_line('pos is ' || pos);
923 -- dbms_output.put_line('length is ' || len );
924
925 x_first_seg := substr(treated_input_str, pos , len );
926
927
928 return;
929
930 EXCEPTION
931 WHEN NO_DATA_FOUND THEN
932 null;
933 END;
934
935
936 END LOOP;
937 END IF;
938
939 -- if we get this far we did not find an appropriate lookup
940 -- in this case,
941 -- extension := string to the right of the last dot
942 -- first segment := string between penultimate dot/beginning and last dot.
943 IF dot_occurence = 1
944 THEN
945 pos := 1;
946 ELSE
947 pos := instrb(treated_input_str,'.',-1,2) + 1 ;
948
949 END IF;
950
951 len := instrb(treated_input_str,'.',-1,1) - pos;
952 x_extension := substr(treated_input_str,
953 instrb(treated_input_str,'.',-1,1) + 1 );
954 x_first_seg := substr(treated_input_str, pos, len );
955
956 END IF;
957
958
959 END;
960
961
962
963
964
965 -- this will return the concatenation of all core domain names
966 -- given a concatenation of email addresses
967 FUNCTION CORE_DOMAIN(
968 p_input_str IN VARCHAR2,
969 p_language IN VARCHAR2,
970 p_attribute_name IN VARCHAR2,
971 p_entity_name IN VARCHAR2)
972 RETURN VARCHAR2
973 IS
974 treated_input_str VARCHAR2(4000);
975 retstr VARCHAR2(4000);
976 l_tok VARCHAR2(4000);
977 x_first_seg VARCHAR2(4000);
978 x_extension VARCHAR2(4000);
979 BEGIN
980 -- empty strings do not go far
981 IF (p_input_str IS NULL OR p_input_str = '')
982 THEN
983 return '';
984 END IF ;
985
986 -- get all the domain names after stripping them from the input string
987 treated_input_str := FULL_DOMAIN(p_input_str, p_language,
988 p_attribute_name,p_entity_name );
989
990 -- replace double spaces by single spaces if any
991 WHILE instrb(treated_input_str,' ')>0 LOOP
992 treated_input_str := REPLACE(treated_input_str, ' ',' ');
993 END LOOP;
994
995 -- build the return string by tokenization
996 -- with the delimiter being a space.
997 l_tok := HZ_DQM_SEARCH_UTIL.strtok(treated_input_str,1,' ');
998
999 -- cycle through the tokens and construct the return string
1000 WHILE l_tok IS NOT NULL
1001 LOOP
1002 extract_ext_and_segment(l_tok, x_first_seg, x_extension);
1003 retstr := retstr ||ltrim(x_first_seg||'.'||x_extension||' ','.');
1004 l_tok := HZ_DQM_SEARCH_UTIL.strtok;
1005 END LOOP;
1006
1007 -- final treatment to get rid of trailing/leading spaces, if any
1008 retstr := ltrim(rtrim(retstr));
1009
1010 return retstr;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 FND_MESSAGE.SET_NAME('AR', 'HZ_EMAIL_DOMAINS_V2PUB');
1015 FND_MESSAGE.SET_TOKEN('PROC' ,'CORE_DOMAIN');
1016 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1017 FND_MSG_PUB.ADD;
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019
1020
1021 END CORE_DOMAIN;
1022
1023
1024
1025
1026
1027 END HZ_EMAIL_DOMAINS_V2PUB;
1028
1029
[Home] [Help]