[Home] [Help]
PACKAGE BODY: APPS.HZ_FORMAT_PHONE_V2PUB
Source
1 package body HZ_FORMAT_PHONE_V2PUB AS
2 /*$Header: ARHPHFMB.pls 120.9 2006/02/09 07:03:13 vravicha noship $ */
3
4
5 --------------------------------------
6 -- declaration of private global types and varibles
7 --------------------------------------
8
9 TYPE num_indexed_table IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
10 TYPE var_indexed_table IS TABLE OF VARCHAR2(400) INDEX BY VARCHAR2(30);
11 TYPE var_indexed_num_table IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
12 TYPE var_table IS TABLE OF VARCHAR(30);
13 TYPE num_table IS TABLE OF NUMBER;
14
15 -- cache party preference
16 pref_user_territory_code_tab num_indexed_table;
17 pref_default_area_code_tab num_indexed_table;
18 pref_display_area_code_tab num_indexed_table;
19 pref_display_all_prefix_tab num_indexed_table;
20 pref_pabx_prefix_tab num_indexed_table;
21
22 -- cache phone country codes based on territory
23 terr_phone_country_tab var_indexed_table;
24 terr_intl_prefix_tab var_indexed_table;
25 terr_trunk_prefix_tab var_indexed_table;
26
27 -- cache territory codes via phone country code
28 phone_territory_tab var_indexed_table;
29
30 -- cache phone formats
31 format_style_tab var_indexed_table;
32 format_area_code_size_tab var_indexed_num_table;
33 format_phone_country_tab var_indexed_table;
34
35 g_debug_count NUMBER := 0;
36 g_international_flag VARCHAR2(1):= fnd_api.g_miss_char;
37 g_user_territory_code HZ_PHONE_COUNTRY_CODES.TERRITORY_CODE%TYPE:=
38 fnd_api.g_miss_char;
39 g_user_phone_country_code HZ_PHONE_COUNTRY_CODES.PHONE_COUNTRY_CODE%TYPE :=
40 fnd_api.g_miss_char;
41
42 --------------------------------------
43 -- declaration of private procedures
44 --------------------------------------
45
46 PROCEDURE get_user_phone_preferences (
47 p_customer_id IN VARCHAR2,
48 x_user_phone_preferences_rec OUT NOCOPY user_phone_preferences_rec
49 );
50
51 PROCEDURE cache_phone_country_via_terr (
52 p_territory_code IN VARCHAR2,
53 x_phone_country_code OUT NOCOPY VARCHAR2,
54 x_trunk_prefix OUT NOCOPY VARCHAR2,
55 x_intl_prefix OUT NOCOPY VARCHAR2
56 );
57
58 PROCEDURE cache_terr_via_phone_country (
59 p_phone_country_code IN VARCHAR2,
60 x_phone_territory_code OUT NOCOPY VARCHAR2
61 );
62
63 PROCEDURE get_phone_format (
64 p_raw_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
65 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
66 p_area_code IN VARCHAR2,
67 x_phone_country_code OUT NOCOPY VARCHAR2,
68 x_phone_format_style OUT NOCOPY VARCHAR2,
69 x_area_code_size OUT NOCOPY NUMBER,
70 x_msg OUT NOCOPY VARCHAR2
71 );
72
73 PROCEDURE parse_intl_prefix(
74 p_with_iprefix IN VARCHAR2 := fnd_api.g_miss_char,
75 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
76 x_intl_prefix OUT NOCOPY VARCHAR2,
77 x_without_iprefix OUT NOCOPY VARCHAR2) ;
78
79 PROCEDURE parse_trunk_prefix(
80 p_with_tprefix IN VARCHAR2 := fnd_api.g_miss_char,
81 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
82 x_trunk_prefix OUT NOCOPY VARCHAR2,
83 x_without_tprefix OUT NOCOPY VARCHAR2);
84
85 PROCEDURE parse_country_code(
86 p_with_country_code IN VARCHAR2 := fnd_api.g_miss_char,
87 x_country_code OUT NOCOPY VARCHAR2,
88 x_without_country_code OUT NOCOPY VARCHAR2);
89
90 PROCEDURE parse_area_code(
91 p_with_area_code IN VARCHAR2 := fnd_api.g_miss_char,
92 p_phone_country_code IN VARCHAR2 := fnd_api.g_miss_char,
93 x_parsed_area_code OUT NOCOPY VARCHAR2,
94 x_parsed_phone_number OUT NOCOPY VARCHAR2 ) ;
95
96 FUNCTION filter_phone_number (
97 p_phone_number IN VARCHAR2,
98 p_isformat IN NUMBER := 0
99 ) RETURN VARCHAR2;
100
101 PROCEDURE translate_raw_phone_number (
102 p_raw_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
103 p_phone_format_style IN VARCHAR2 := fnd_api.g_miss_char,
104 x_formatted_phone_number OUT NOCOPY VARCHAR2
105 );
106
107 --
108 -- PROCEDURE phone_parse
109 --
110 -- DESCRIPTION
111 -- parses a phone number
112 --
113 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
114 --
115 -- ARGUMENTS
116 -- IN:
117 -- p_init_msg_list Initialize message stack if it is set to
118 -- FND_API.G_TRUE. Default is fnd_api.g_false.
119 -- p_raw_phone_number Raw phone number.
120 -- p_territory_code Optional parameter for supplying the
121 -- territory code of the Phone No.
122 -- OUT:
123 -- x_phone_country_code Phone country code.
124 -- x_phone_area_code Phone area code.
125 -- x_phone_number Phone number.
126 -- x_mobile_flag Flag indicating if the Number is mobile
127 -- x_return_status Return status after the call. The status can
128 -- be fnd_api.g_ret_sts_success (success),
129 -- fnd_api.g_ret_sts_error (error),
130 -- fnd_api.g_ret_sts_unexp_error
131 -- (unexpected error).
132 -- x_msg_count Number of messages in message stack.
133 -- x_msg_data Message text if x_msg_count is 1.
134 --
135 -- NOTES
136 --
137 -- MODIFICATION HISTORY
138 --
139 -- 01-10-2002 Jyoti Pandey o Created.
140 --
141 --
142
143 PROCEDURE phone_parse (
144 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
145 p_raw_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
146 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
147 x_phone_country_code OUT NOCOPY VARCHAR2,
148 x_phone_area_code OUT NOCOPY VARCHAR2,
149 x_phone_number OUT NOCOPY VARCHAR2,
150 x_mobile_flag OUT NOCOPY VARCHAR2,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2
154 ) IS
155 l_customer_id NUMBER;
156 x_user_phone_preferences_rec user_phone_preferences_rec;
157
158 i_territory_code HZ_PHONE_COUNTRY_CODES.TERRITORY_CODE%TYPE;
159 lp_phone_country_code HZ_CONTACT_POINTS.PHONE_country_code%TYPE;
160 l_phone_area_code HZ_PHONE_AREA_CODES.AREA_CODE%TYPE;
161 l_phone_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
162
163 l_filtered_number HZ_CONTACT_POINTS.RAW_PHONE_NUMBER%TYPE;
164 l_raw_phone_number HZ_CONTACT_POINTS.RAW_PHONE_NUMBER%TYPE;
165
166 l_phone_format_style HZ_PHONE_FORMATS.PHONE_FORMAT_STYLE%TYPE;
167 l_filtered_phone_format_style HZ_PHONE_FORMATS.PHONE_FORMAT_STYLE%TYPE;
168 l_length_style NUMBER;
169 l_length_matches VARCHAR2(1);
170 l_initial_sign VARCHAR2(1);
171
172 l_p_country_code_check VARCHAR2(20);
173 l_length_cntry_code NUMBER;
174
175 x_intl_prefix VARCHAR2(5);
176 x_without_intl_prefix HZ_CONTACT_POINTS.RAW_PHONE_NUMBER%TYPE;
177 x_trunk_prefix VARCHAR2(5);
178 x_without_trunk_prefix HZ_CONTACT_POINTS.RAW_PHONE_NUMBER%TYPE;
179
180 x_parsed_country_code HZ_PHONE_COUNTRY_CODES.PHONE_COUNTRY_CODE%TYPE;
181 x_without_country_code HZ_CONTACT_POINTS.RAW_PHONE_NUMBER%TYPE;
182
183 x_parsed_area_code HZ_PHONE_AREA_CODES.AREA_CODE%TYPE;
184 x_parsed_phone_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
185
186 l_msg_name VARCHAR2(50) := NULL;
187 l_msg_count NUMBER :=0;
188 l_msg_data VARCHAR2(2000):= NULL;
189
190 CURSOR c_phone_country_code(i_territory_code varchar2) IS
191 SELECT phone_country_code
192 FROM hz_phone_country_codes
193 WHERE territory_code = i_territory_code;
194
195 CURSOR c_get_format_length IS
196 SELECT PHONE_FORMAT_STYLE
197 FROM hz_phone_formats
198 WHERE territory_code = g_user_territory_code;
199 l_debug_prefix VARCHAR2(30) := '';
200
201 BEGIN
202
203 --SAVEPOINT
204
205 -- Check if API is called in debug mode. If yes, enable debug.
206 --enable_debug;
207
208 -- Debug info.
209 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
210 hz_utility_v2pub.debug(p_message=>'phone_parse (+)',
211 p_prefix=>l_debug_prefix,
212 p_msg_level=>fnd_log.level_procedure);
213 END IF;
214
215 -- Initialize message list if p_init_msg_list is set to TRUE.
216 IF fnd_api.to_boolean(p_init_msg_list) THEN
217 fnd_msg_pub.initialize;
218 END IF;
219
220 -- Initialize API return status to success.
221 x_return_status := fnd_api.g_ret_sts_success;
222
223 -- Check if raw phone number is passed
224 IF p_raw_phone_number IS NOT NULL AND
225 p_raw_phone_number <> fnd_api.g_miss_char THEN
226 l_raw_phone_number := p_raw_phone_number;
227 END IF;
228
229
230 /*--------------------------------------------------------
231 1: Strip out punctuation and spaces except + sign
232 --------------------------------------------------------*/
233
234 l_filtered_number := TRANSLATE (
235 l_raw_phone_number,
236 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .''~`\/@#$%^*_,|}{[]?<>=";:',
237 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
238
239 ---Check if the first character is the + sign
240 if SUBSTR(l_filtered_number,1,1) = '+' then
241 ---set the initial sign as it is potentially an international number
242 l_initial_sign := 'Y';
243
244 end if;
245
246 ---Remove all the occurances of the + sign
247 l_filtered_number := filter_phone_number (l_filtered_number);
248
249 /*--------------------------------------------------------
250 2: Get the user's territory code
251 --------------------------------------------------------*/
252 l_customer_id := fnd_global.customer_id;
253
254 IF l_customer_id IS NOT NULL THEN
255 get_user_phone_preferences (
256 l_customer_id,
257 x_user_phone_preferences_rec);
258 END IF;
259
260 g_user_territory_code:= x_user_phone_preferences_rec.user_territory_code;
261
262 IF g_user_territory_code is not null THEN
263 --- Get the user's phone country code based on the territory code
264 OPEN c_phone_country_code(g_user_territory_code);
265 FETCH c_phone_country_code INTO g_user_phone_country_code;
266 IF c_phone_country_code%NOTFOUND THEN
267 g_user_phone_country_code := NULL;
268 END IF;
269 CLOSE c_phone_country_code;
270 END IF; ----g_user_territory_code is not null
271
272 /*----------------------------------------------------------------------
273 3: Check if the p_territory_code was passed
274 if it was passed then just the area code needs to parsed
275 g_international_flag='N'
276 else try to set g_international_flag by parsing intl or trunk prefix
277 ---------------------------------------------------------------------*/
278 IF p_territory_code is not null THEN
279 --- Get the phone country code of the input number
280 OPEN c_phone_country_code(p_territory_code);
281 FETCH c_phone_country_code INTO lp_phone_country_code;
282 IF c_phone_country_code%NOTFOUND THEN
283 lp_phone_country_code := NULL;
284 END IF;
285 CLOSE c_phone_country_code;
286 ELSE
287 lp_phone_country_code:= NULL;
288 END IF;
289
290 IF lp_phone_country_code IS NOT NULL AND
291 lp_phone_country_code <> fnd_api.g_miss_char THEN
292
293 --Do not need to parse the country code
294 g_international_flag := 'N';
295
296 --filter off the trunk/international prefix if present
297
298 PARSE_INTL_PREFIX(l_filtered_number,
299 nvl(g_user_territory_code,p_territory_code),
300 x_intl_prefix,
301 x_without_intl_prefix);
302
303 if x_intl_prefix is null then
304 PARSE_TRUNK_PREFIX(l_filtered_number,
305 nvl(g_user_territory_code,p_territory_code),
306 x_trunk_prefix,
307 x_without_trunk_prefix);
308
309 l_filtered_number := x_without_trunk_prefix;
310 else
311
312 l_filtered_number := x_without_intl_prefix;
313
314 end if;
315
316 --filter off the country code from the number if present
317 l_length_cntry_code := LENGTH(lp_phone_country_code);
318 l_p_country_code_check:=SUBSTR(l_filtered_number,1,l_length_cntry_code);
319
320 if l_p_country_code_check = lp_phone_country_code then
321
322 ---chop off the cntry code from i/p no.
323 l_filtered_number :=
324 SUBSTR(l_filtered_number ,l_length_cntry_code+1 ,
325 LENGTH(l_filtered_number));
326 end if;
327
328 ---set the i/p parameters required for parse_area_code API
329 x_parsed_country_code := lp_phone_country_code;
330 x_without_country_code := l_filtered_number;
331
332 ELSE ---lp_phone_country_code is null (no territory_code)
333
334 PARSE_INTL_PREFIX(l_filtered_number,
335 nvl(g_user_territory_code,p_territory_code),
336 x_intl_prefix,
337 x_without_intl_prefix);
338
339 if x_intl_prefix is not null then
340 g_international_flag := 'Y';
341 else ---if x_intl_prefix could not be parsed
342
343 if l_initial_sign = 'Y' then --check if there was a + sign
344 g_international_flag := 'Y';
345 x_without_intl_prefix := l_filtered_number;
346 else ---neither x_intl_prefix was parsed nor +
347
348 PARSE_TRUNK_PREFIX(l_filtered_number,
349 g_user_territory_code,
350 x_trunk_prefix,
351 x_without_trunk_prefix);
352
353 if x_trunk_prefix is not null then
354 g_international_flag := 'N';
355 x_without_country_code := x_without_trunk_prefix;
356 x_parsed_country_code := g_user_phone_country_code;
357 else
358 g_international_flag := NULL;
359 end if;
360 end if;
361 end if;
362
363 END IF ; ---lp_phone_country_code
364
365 /*----------------------------------------------------------------------
366 4: Based on g_international_flag ,parse country_code and/or area_code
367 ----------------------------------------------------------------------*/
368 IF g_international_flag = 'Y'THEN ---if intl_prefix was parsed
369
370 parse_country_code(
371 x_without_intl_prefix,
372 x_parsed_country_code,
373 x_without_country_code );
374
375 parse_area_code(
376 x_without_country_code,
377 x_parsed_country_code,
378 x_parsed_area_code,
382 x_phone_area_code := x_parsed_area_code;
379 x_parsed_phone_number);
380
381 x_phone_country_code := x_parsed_country_code;
383 x_phone_number := x_parsed_phone_number;
384
385 ELSIF g_international_flag = 'N'THEN ---if trunk_prefix was parsed
386
387 parse_area_code(
388 x_without_country_code,
389 x_parsed_country_code,
390 x_parsed_area_code,
391 x_parsed_phone_number);
392 x_phone_country_code := x_parsed_country_code;
393 x_phone_area_code := x_parsed_area_code;
394 x_phone_number := x_parsed_phone_number;
395
396 ELSE
397
398 begin
399 ----first treat the number as international number
400 parse_country_code(
401 l_filtered_number,
402 x_parsed_country_code,
403 x_without_country_code );
404
405 parse_area_code(
406 x_without_country_code,
407 x_parsed_country_code,
408 x_parsed_area_code,
409 x_parsed_phone_number);
410
411 IF( (x_parsed_area_code IS NOT NULL) AND
412 (x_parsed_country_code IS NOT NULL) ) THEN
413 x_phone_country_code := x_parsed_country_code;
414 x_phone_area_code := x_parsed_area_code;
415 x_phone_number := x_parsed_phone_number;
416 ELSE ---if could not parse the country and area code
417 x_phone_area_code := NULL;
418 x_phone_number := l_filtered_number;
419 END IF;
420
421 end;
422 END IF; ---g_international_flag
423
424 HZ_FORMAT_PHONE_V2PUB.check_mobile_phone (
425 'T',
426 x_parsed_country_code,
427 x_parsed_area_code,
428 x_parsed_phone_number,
429 x_mobile_flag,
430 x_return_status ,
431 x_msg_count ,
432 x_msg_data );
433
434
435 -- Standard call to get msg count and if count is 1, get msg info.
436 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
437 p_count => x_msg_count,
438 p_data => x_msg_data);
439
440 -- Debug info.
441 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
442 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
443 p_msg_data=>x_msg_data,
444 p_msg_type=>'WARNING',
445 p_msg_level=>fnd_log.level_exception);
446 END IF;
447 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
448 hz_utility_v2pub.debug(p_message=>'phone_format (-)',
449 p_prefix=>l_debug_prefix,
450 p_msg_level=>fnd_log.level_procedure);
451 END IF;
452
453 -- Check if API is called in debug mode. If yes, disable debug.
454 --disable_debug;
455
456 EXCEPTION
457 WHEN fnd_api.g_exc_error THEN
458 x_return_status := fnd_api.g_ret_sts_error;
459
460 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
461 p_count => x_msg_count,
462 p_data => x_msg_data);
463
464 -- Debug info.
465 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
466 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
467 p_msg_data=>x_msg_data,
468 p_msg_type=>'ERROR',
469 p_msg_level=>fnd_log.level_error);
470 END IF;
471 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
472 hz_utility_v2pub.debug(p_message=>'phone_format (-)',
473 p_prefix=>l_debug_prefix,
474 p_msg_level=>fnd_log.level_procedure);
475 END IF;
476
477 -- Check if API is called in debug mode. If yes, disable debug.
478 --disable_debug;
479
480 WHEN fnd_api.g_exc_unexpected_error THEN
481 x_return_status := fnd_api.g_ret_sts_unexp_error;
482
483 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
484 p_count => x_msg_count,
485 p_data => x_msg_data);
486
487 -- Debug info.
488 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
489 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
490 p_msg_data=>x_msg_data,
491 p_msg_type=>'UNEXPECTED ERROR',
492 p_msg_level=>fnd_log.level_error);
493 END IF;
494 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
495 hz_utility_v2pub.debug(p_message=>'phone_format (-)',
496 p_prefix=>l_debug_prefix,
497 p_msg_level=>fnd_log.level_procedure);
498 END IF;
499
500 -- Check if API is called in debug mode. If yes, disable debug.
501 --disable_debug;
502
503 WHEN OTHERS THEN
504 x_return_status := fnd_api.g_ret_sts_unexp_error;
505
506 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
507 fnd_message.set_token('ERROR' ,SQLERRM);
508 fnd_msg_pub.add;
509
510 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
511 p_count => x_msg_count,
512 p_data => x_msg_data);
513
514 -- Debug info.
515 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
516 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
520 END IF;
517 p_msg_data=>x_msg_data,
518 p_msg_type=>'SQL ERROR',
519 p_msg_level=>fnd_log.level_error);
521 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
522 hz_utility_v2pub.debug(p_message=>'phone_format (-)',
523 p_prefix=>l_debug_prefix,
524 p_msg_level=>fnd_log.level_procedure);
525 END IF;
526
527 -- Check if API is called in debug mode. If yes, disable debug.
528 --disable_debug;
529
530 END;
531 --
532 -- PROCEDURE phone_display
533 --
534 -- DESCRIPTION
535 -- displays a phone number
536 --
537 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
538 --
539 -- ARGUMENTS
540 -- IN:
541 -- p_init_msg_list Initialize message stack if it is set to
542 -- FND_API.G_TRUE. Default is fnd_api.g_false.
543 -- p_contact_point_id Contact point id of TYPE PHONE
544 -- territory code of the Phone No.
545 -- OUT:
546 -- x_formatted_number Number formatted acc. to defaults set in
547 -- HZ_PHONE_FORMATS or default format
548 -- x_return_status Return status after the call. The status can
549 -- be fnd_api.g_ret_sts_success (success),
550 -- fnd_api.g_ret_sts_error (error),
551 -- fnd_api.g_ret_sts_unexp_error
552 -- (unexpected error).
553 -- x_msg_count Number of messages in message stack.
554 -- x_msg_data Message text if x_msg_count is 1.
555 --
556 -- NOTES
557 --
558 -- MODIFICATION HISTORY
559 --
560 -- 01-10-2002 Jyoti Pandey o Created.
561 -- 07-01-2002 V.Srinivasan Bug 2415007: Handled "No Data Found" error
562 -- whenever the contact point id passed is
563 -- invalid.
564 -- 11-03-2005 Idris Ali o Bug 4578945: changed the procedure to return null when the area_code
565 -- country_code and phone_number are all null.
566 --
567 --
568 --
569
570 PROCEDURE phone_display(
571 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
572 p_contact_point_id IN NUMBER,
573 x_formatted_phone_number OUT NOCOPY VARCHAR2,
574 x_return_status OUT NOCOPY VARCHAR2,
575 x_msg_count OUT NOCOPY NUMBER,
576 x_msg_data OUT NOCOPY VARCHAR2 ) IS
577
578 x_phone_format_style HZ_PHONE_FORMATS.PHONE_FORMAT_STYLE%TYPE;
579 l_default_prefix VARCHAR2(30) := ''; -- get_phone_format
580 l_empty BOOLEAN;
581
582 CURSOR c_territory(p_country_code VARCHAR2) IS
583 SELECT territory_code
584 FROM hz_phone_country_codes
585 WHERE phone_country_code = p_country_code;
586
587 l_customer_id NUMBER;
588 l_phone_format_style hz_phone_formats.phone_format_style%TYPE;
589 l_phone_country_code hz_phone_country_codes.phone_country_code%TYPE;
590 l_raw_phone_number HZ_CONTACT_POINTS.raw_phone_number%type;
591 --3865843
592 l_formatted_phone_number VARCHAR2(120);
593
594 l_format_cntry_code hz_phone_country_codes.phone_country_code%TYPE;
595
596 --User Preferences
597 x_user_phone_preferences_rec user_phone_preferences_rec;
598 l_user_country_code HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%TYPE;
599 l_user_territory_code HZ_PHONE_FORMATS.TERRITORY_CODE%TYPE;
600 l_default_area_code HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE;
601 l_display_area_code VARCHAR2(1);
602 l_display_all_prefixes VARCHAR2(1);
603 l_pabx_prefix VARCHAR2(1);
604
605 l_phone_intl_prefix HZ_PHONE_COUNTRY_CODES.INTL_PREFIX%TYPE;
606 l_phone_trunk_prefix HZ_PHONE_COUNTRY_CODES.TRUNK_PREFIX%TYPE;
607 l_prefix VARCHAR2(1) := null;
608
609 l_msg_name VARCHAR2(100):= NULL;
610 l_area_code_size NUMBER;
611 l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
612 l_phone_area_code HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE;
613 l_phone_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
614 l_phone_territory_code HZ_PHONE_FORMATS.TERRITORY_CODE%TYPE;
615 l_default_format VARCHAR2(1):= 'N';
616 l_debug_prefix VARCHAR2(30) := '';
617 --3865843
618 l_formatted_phone_area_code VARCHAR2(12);
619
620 x_area_code_size NUMBER;
621
622 BEGIN
623
624 -- standard start of API savepoint
625 -- SAVEPOINT phone_display;
626
627 -- Check if API is called in debug mode. If yes, enable debug.
628 --enable_debug;
629
630 -- Debug info.
631 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
632 hz_utility_v2pub.debug(p_message=>'phone_display (+)',
633 p_prefix=>l_debug_prefix,
634 p_msg_level=>fnd_log.level_procedure);
635 END IF;
636
637 -- initialize message list if p_init_msg_list is set to TRUE.
638 IF FND_API.to_Boolean(p_init_msg_list) THEN
639 FND_MSG_PUB.initialize;
640 END IF;
641
642 -- initialize API return status to success.
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645
646 -- Initialize return variables
647 x_phone_format_style := fnd_api.g_miss_char;
648
649 --If the contact_point_is of wrong type raise the error
650 BEGIN
651 select contact_point_type
655 EXCEPTION
652 into l_contact_point_type
653 from HZ_CONTACT_POINTS
654 where CONTACT_POINT_ID = p_contact_point_id;
656 WHEN NO_DATA_FOUND THEN
657 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
658 fnd_message.set_token('RECORD', 'Contact Point');
659 fnd_message.set_token('VALUE', TO_CHAR(p_contact_point_id));
660 fnd_msg_pub.add;
661 RAISE fnd_api.g_exc_error;
662 END ;
663
664
665 IF l_contact_point_type <> 'PHONE' THEN
666 fnd_message.set_name('AR', 'HZ_WRONG_CONTACT_POINT_TYPE');
667 fnd_msg_pub.add;
668 RAISE fnd_api.g_exc_error;
669 END IF;
670
671 /*--------------------------------------------------------
672 Get the user preferences
673 --------------------------------------------------------*/
674
675 l_customer_id := fnd_global.customer_id;
676
677 IF l_customer_id IS NOT NULL THEN
678 get_user_phone_preferences(
679 l_customer_id,
680 x_user_phone_preferences_rec);
681 END IF;
682
683 l_user_territory_code := x_user_phone_preferences_rec.user_territory_code;
684 l_default_area_code := x_user_phone_preferences_rec.default_area_code;
685 l_display_area_code := x_user_phone_preferences_rec.display_area_code;
686 l_display_all_prefixes:= x_user_phone_preferences_rec.display_all_prefixes;
687 l_pabx_prefix := x_user_phone_preferences_rec.pabx_prefix;
688
689
690 ---based on user's territory code get the phone_country_code
691 ---for comparision
692
693 if l_user_territory_code is not null then
694 select phone_country_code , intl_prefix, trunk_prefix
695 into l_user_country_code, l_phone_intl_prefix, l_phone_trunk_prefix
696 from hz_phone_country_codes
697 where territory_code = l_user_territory_code;
698 else
699 l_user_country_code := null;
700 end if;
701
702 ---Get the phone number info from the contact_point_id
703 BEGIN
704 select phone_country_code,phone_area_code, phone_number
705 into l_phone_country_code ,l_phone_area_code, l_phone_number
706 from HZ_CONTACT_POINTS
707 where CONTACT_POINT_ID = p_contact_point_id
708 and CONTACT_POINT_TYPE = 'PHONE';
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
712 fnd_message.set_token('RECORD', 'Contact Point');
713 fnd_message.set_token('VALUE', TO_CHAR(p_contact_point_id));
714 fnd_msg_pub.add;
715 RAISE fnd_api.g_exc_error;
716 END ;
717
718 IF l_phone_area_code IS NULL OR
719 l_phone_area_code = fnd_api.g_miss_char THEN
720 l_raw_phone_number := filter_phone_number(l_phone_number);
721 ELSE
722 l_raw_phone_number := filter_phone_number(l_phone_area_code ||
723 l_phone_number);
724 END IF;
725
726 ---IN NAMP all the participating countries will have same country_code
727 ---e.g. US , Canada they will have similar format styles
728 ---GET the Phone number's territory from the phone_country_code
729
730 if l_phone_country_code is null then
731 l_formatted_phone_number := null;
732 else
733 begin
734 select pc.territory_code
735 into l_phone_territory_code
736 from hz_phone_country_codes pc
737 where phone_country_code = l_phone_country_code
738 AND exists (select phone_format_style
739 from hz_phone_formats pf
740 where pf.territory_code = pc.territory_code)
741 AND rownum =1;
742
743 exception
744 when no_data_found then
745 l_formatted_phone_number := NULL;
746
747 end;
748
749 if l_phone_territory_code is not null then
750 -- Call subroutine to get the format style to be
751 --applied for the given raw phone number and territory
752
753 get_phone_format(
754 p_raw_phone_number => l_raw_phone_number,
755 p_territory_code => l_phone_territory_code,
756 p_area_code => l_phone_area_code,
757 x_phone_country_code => l_format_cntry_code,
758 x_phone_format_style => l_phone_format_style,
759 x_area_code_size => l_area_code_size,
760 x_msg => l_msg_name);
761
762 if l_phone_format_style is not null then
763 -- Apply the format style and get translated number
764 translate_raw_phone_number (
765 p_raw_phone_number => l_raw_phone_number,
766 p_phone_format_style => l_phone_format_style,
767 x_formatted_phone_number => l_formatted_phone_number);
768 else
769 l_formatted_phone_number := null;
770 end if;
771
772 end if; ---l_phone_territory_code is not null
773 end if; --l_phone_countey_code is not null
774
775 if l_formatted_phone_number is null then
776 l_default_format := 'Y';
777 end if;
778
779 IF l_default_format = 'Y' then
780
781 if l_phone_area_code is null then
782 --3865843
783 l_formatted_phone_area_code := '';
784 else l_formatted_phone_area_code := '('||l_phone_area_code|| ')';
785 end if;
786
787 l_phone_number := filter_phone_number(l_phone_number);
791
788 l_formatted_phone_number := l_formatted_phone_area_code||l_phone_number;
789
790 END IF;
792
793 -- Append country code if user's country is different
794 --than the phone_country
795 IF ((l_phone_country_code <> l_user_country_code) OR
796 (l_user_country_code IS NULL))
797 THEN
798 IF l_phone_country_code IS NOT NULL AND
799 l_phone_country_code <> fnd_api.g_miss_char --Bug 4578945
800 THEN
801 IF l_display_all_prefixes = 'Y' THEN
802 l_formatted_phone_number := l_phone_country_code ||' '||l_formatted_phone_number;
803 ELSE
804 l_formatted_phone_number := '+'||l_phone_country_code ||' '||l_formatted_phone_number;
805 END IF;
806 l_prefix := 'I';
807 ELSE
808 l_prefix := null;
809 END IF;
810
811
812 elsif l_phone_country_code = l_user_country_code THEN
813
814 ---check if the user's area code is same as phone's area code
815 IF ( (l_phone_area_code = l_default_area_code)
816 AND (l_DISPLAY_AREA_CODE = 'N' ) ) THEN
817 l_formatted_phone_number := l_phone_number;
818 ELSE
819
820 if l_phone_area_code is not null
821 and l_phone_area_code <> fnd_api.g_miss_char
822 then
823 l_prefix := 'T';
824 else
825 l_prefix := null;
826 end if; ---l_phone_area_code is not null
827 END IF; ---l_phone_area_code=l_default_area_code
828 end if; ---l_phone_country_code=l_user_country_code
829
830 --Check the DISPLAY_ALL_PREFIXES flag
831 --if set then check pabx prefix
832 if l_display_all_prefixes = 'Y' AND
833 l_formatted_phone_number IS NOT NULL THEN --Bug 4578945
834 if l_prefix = 'I' then
835 l_formatted_phone_number := l_phone_intl_prefix||' '||
836 l_formatted_phone_number;
837 elsif l_prefix = 'T' then
838 l_formatted_phone_number := l_phone_trunk_prefix||' '||
839 l_formatted_phone_number;
840 end if;
841
842 ---append the user's PABX prefix if user has set it up
843 if l_pabx_prefix is not null then
844 l_formatted_phone_number := l_pabx_prefix||' '||
845 l_formatted_phone_number;
846 end if;
847
848 end if;
849
850 x_formatted_phone_number := l_formatted_phone_number;
851
852 -- Standard call to get msg count and if count is 1, get msg info.
853 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
854 p_count => x_msg_count,
855 p_data => x_msg_data);
856
857 -- Debug info.
858 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
859 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
860 p_msg_data=>x_msg_data,
861 p_msg_type=>'WARNING',
862 p_msg_level=>fnd_log.level_exception);
863 END IF;
864 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
865 hz_utility_v2pub.debug(p_message=>'phone_display (-)',
866 p_prefix=>l_debug_prefix,
867 p_msg_level=>fnd_log.level_procedure);
868 END IF;
869
870 -- Check if API is called in debug mode. If yes, disable debug.
871 --disable_debug;
872
873 EXCEPTION
874
875 WHEN fnd_api.g_exc_error THEN
876 x_return_status := fnd_api.g_ret_sts_error;
877
878 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
879 p_count => x_msg_count,
880 p_data => x_msg_data);
881
882 -- Debug info.
883 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
884 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
885 p_msg_data=>x_msg_data,
886 p_msg_type=>'ERROR',
887 p_msg_level=>fnd_log.level_error);
888 END IF;
889 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
890 hz_utility_v2pub.debug(p_message=>'phone_display (-)',
891 p_prefix=>l_debug_prefix,
892 p_msg_level=>fnd_log.level_procedure);
893 END IF;
894
895 -- Check if API is called in debug mode. If yes, disable debug.
896 --disable_debug;
897
898 WHEN fnd_api.g_exc_unexpected_error THEN
899 x_return_status := fnd_api.g_ret_sts_unexp_error;
900
901 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
902 p_count => x_msg_count,
903 p_data => x_msg_data);
904
905 -- Debug info.
906 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
907 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
908 p_msg_data=>x_msg_data,
909 p_msg_type=>'UNEXPECTED ERROR',
910 p_msg_level=>fnd_log.level_error);
911 END IF;
912 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
913 hz_utility_v2pub.debug(p_message=>'phone_display (-)',
917
914 p_prefix=>l_debug_prefix,
915 p_msg_level=>fnd_log.level_procedure);
916 END IF;
918
919 -- Check if API is called in debug mode. If yes, disable debug.
920 --disable_debug;
921
922 WHEN OTHERS THEN
923 x_return_status := fnd_api.g_ret_sts_unexp_error;
924
925 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
926 fnd_message.set_token('ERROR' ,SQLERRM);
927 fnd_msg_pub.add;
928
929 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
930 p_count => x_msg_count,
931 p_data => x_msg_data);
932
933 -- Debug info.
934 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
935 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
936 p_msg_data=>x_msg_data,
937 p_msg_type=>'SQL ERROR',
938 p_msg_level=>fnd_log.level_error);
939 END IF;
940 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
941 hz_utility_v2pub.debug(p_message=>'phone_display (-)',
942 p_prefix=>l_debug_prefix,
943 p_msg_level=>fnd_log.level_procedure);
944 END IF;
945 -- Check if API is called in debug mode. If yes, disable debug.
946 --disable_debug;
947
948 END phone_display;
949
950 --
951 -- PROCEDURE phone_display
952 --
953 -- DESCRIPTION
954 -- displays a phone number
955 --
956 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
957 --
958 -- ARGUMENTS
959 -- IN:
960 -- p_init_msg_list Initialize message stack if it is set to
961 -- FND_API.G_TRUE. Default is fnd_api.g_false.
962 -- p_territory_code Optional parameter for supplying the
963 -- territory code of the Phone No.
964 -- p_phone_country code Country code of the phone number
965 -- p_phone_area_code Area code
966 -- p_phone_number Phone Number
967 --
968 -- OUT:
969 -- x_formatted_number Number formatted acc. to defaults set in
970 -- HZ_PHONE_FORMATS or default format
971 -- x_return_status Return status after the call. The status can
972 -- be fnd_api.g_ret_sts_success (success),
973 -- fnd_api.g_ret_sts_error (error),
974 -- fnd_api.g_ret_sts_unexp_error
975 -- (unexpected error).
976 -- x_msg_count Number of messages in message stack.
977 -- x_msg_data Message text if x_msg_count is 1.
978 --
979 -- NOTES
980 --
981 -- MODIFICATION HISTORY
982 --
983 -- 01-10-2002 Jyoti Pandey o Created.
984 -- 10-20-2005 Idris Ali o Bug 4578945:modified procedure such that x_formatted_phone_number
985 -- should have a null value when p_territory_code,p_phone_country_code,
986 -- p_phone_area_code,p_phone_number are null.
987 --
988
989 PROCEDURE phone_display(
990 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
991 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
992 p_phone_country_code IN VARCHAR2 := fnd_api.g_miss_char,
993 p_phone_area_code IN VARCHAR2 := fnd_api.g_miss_char,
994 p_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
995 x_formatted_phone_number OUT NOCOPY VARCHAR2,
996 x_return_status OUT NOCOPY VARCHAR2,
997 x_msg_count OUT NOCOPY NUMBER,
998 x_msg_data OUT NOCOPY VARCHAR2
999 ) IS
1000
1001 l_default_prefix VARCHAR2(30) := ''; -- get_phone_format
1002 x_phone_format_style HZ_PHONE_FORMATS.PHONE_FORMAT_STYLE%TYPE;
1003 l_phone_country_code HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%TYPE;
1004 l_phone_area_code HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE;
1005 l_phone_number HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE;
1006 l_customer_id NUMBER;
1007 --3865843
1008 l_formatted_phone_area_code VARCHAR2(12);
1009 --User Preferences
1010 x_user_phone_preferences_rec user_phone_preferences_rec := NULL;
1011 l_user_territory_code HZ_PHONE_FORMATS.TERRITORY_CODE%TYPE;
1012 l_default_area_code HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE;
1013 l_display_area_code VARCHAR2(1);
1014 l_display_all_prefixes VARCHAR2(1);
1015 l_pabx_prefix VARCHAR2(1);
1016 l_user_country_code HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%TYPE;
1017 l_phone_intl_prefix HZ_PHONE_COUNTRY_CODES.INTL_PREFIX%TYPE;
1018 l_phone_trunk_prefix HZ_PHONE_COUNTRY_CODES.TRUNK_PREFIX%TYPE;
1019 l_raw_phone_number HZ_CONTACT_POINTS.raw_phone_number%type;
1020 l_phone_territory_code HZ_PHONE_FORMATS.TERRITORY_CODE%TYPE;
1021 l_msg_name VARCHAR2(100):= NULL;
1022 l_area_code_size NUMBER;
1023 l_format_cntry_code hz_phone_country_codes.phone_country_code%TYPE;
1024 l_phone_format_style hz_phone_formats.phone_format_style%TYPE;
1025 l_empty BOOLEAN;
1026 --3865843
1027 l_formatted_phone_number VARCHAR2(120);
1028 l_prefix VARCHAR2(1);
1029 x_area_code_size NUMBER;
1030 p_default_format VARCHAR2(1):= 'N';
1031 l_debug_prefix VARCHAR2(30) := '';
1032
1033 BEGIN
1034
1035 -- Debug info.
1039 p_prefix => l_debug_prefix,
1036 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1037 hz_utility_v2pub.debug(
1038 p_message => 'phone_display (+)',
1040 p_msg_level => fnd_log.level_procedure);
1041 END IF;
1042
1043 -- initialize message list if p_init_msg_list is set to TRUE.
1044 IF FND_API.to_Boolean(p_init_msg_list) THEN
1045 FND_MSG_PUB.initialize;
1046 END IF;
1047
1048 -- initialize API return status to success.
1049 x_return_status := FND_API.G_RET_STS_SUCCESS;
1050
1051 -- Initialize return variables
1052 x_phone_format_style := fnd_api.g_miss_char;
1053
1054 IF p_phone_country_code <> fnd_api.g_miss_char THEN
1055 l_phone_country_code := p_phone_country_code;
1056 END IF;
1057 IF p_phone_area_code <> fnd_api.g_miss_char THEN
1058 l_phone_area_code := p_phone_area_code;
1059 END IF;
1060 IF p_phone_number <> fnd_api.g_miss_char THEN
1061 l_phone_number := p_phone_number;
1062 END IF;
1063
1064 -- Get the user preferences
1065
1066 l_customer_id := fnd_global.customer_id;
1067
1068 IF l_customer_id IS NOT NULL THEN
1069 get_user_phone_preferences(
1070 l_customer_id,
1071 x_user_phone_preferences_rec);
1072 END IF;
1073
1074 l_user_territory_code := x_user_phone_preferences_rec.user_territory_code;
1075 l_default_area_code := x_user_phone_preferences_rec.default_area_code;
1076 l_display_area_code := x_user_phone_preferences_rec.display_area_code;
1077 l_display_all_prefixes:= x_user_phone_preferences_rec.display_all_prefixes;
1078 l_pabx_prefix := x_user_phone_preferences_rec.pabx_prefix;
1079
1080 ---based on user's territory code get the user's phone_country_code
1081 ---for comparision
1082
1083 IF l_user_territory_code IS NOT NULL THEN
1084 cache_phone_country_via_terr(
1085 l_user_territory_code,
1086 l_user_country_code,
1087 l_phone_trunk_prefix,
1088 l_phone_intl_prefix);
1089 ELSE
1090 l_user_country_code := null;
1091 END IF;
1092
1093 IF l_phone_area_code IS NULL OR
1094 l_phone_area_code = fnd_api.g_miss_char
1095 THEN
1096 l_raw_phone_number := filter_phone_number(l_phone_number);
1097 ELSE
1098 l_raw_phone_number := filter_phone_number(l_phone_area_code || l_phone_number);
1099 END IF;
1100
1101 ---IN NAMP all the participating countries will have same country_code
1102 ---e.g. US , Canada they will have similar format styles
1103 ---GET the Phone number's territory from the phone_country_code
1104
1105 IF p_territory_code IS NOT NULL AND
1106 p_territory_code <> fnd_api.g_miss_char
1107 THEN
1108 l_phone_territory_code := p_territory_code;
1109 ELSE
1110 IF l_phone_country_code IS NOT NULL THEN
1111 cache_terr_via_phone_country (
1112 l_phone_country_code,
1113 l_phone_territory_code
1114 );
1115 ELSE
1116 l_phone_territory_code:= null;
1117 END IF;
1118 END IF;
1119
1120 IF l_phone_territory_code IS NULL THEN --no territory found
1121 l_formatted_phone_number := null;
1122 ELSE
1123 -- Call subroutine to get the format style to be applied
1124 --for the given raw phone number and territory
1125
1126 get_phone_format(
1127 p_raw_phone_number => l_raw_phone_number,
1128 p_territory_code => l_phone_territory_code,
1129 p_area_code => p_phone_area_code,
1130 x_phone_country_code => l_format_cntry_code,
1131 x_phone_format_style => l_phone_format_style,
1132 x_area_code_size => l_area_code_size,
1133 x_msg => l_msg_name);
1134
1135 IF l_phone_format_style IS NOT NULL THEN
1136 -- Apply the format style and get translated number
1137 translate_raw_phone_number (
1138 p_raw_phone_number => l_raw_phone_number,
1139 p_phone_format_style => l_phone_format_style,
1140 x_formatted_phone_number => l_formatted_phone_number);
1141 ELSE
1142 l_formatted_phone_number := null;
1143 END IF;
1144 END IF;
1145
1146 IF l_formatted_phone_number IS NULL THEN
1147 p_default_format := 'Y';
1148 END IF;
1149
1150 IF p_default_format = 'Y' THEN
1151 IF l_phone_area_code IS NULL THEN
1152 --3865843
1153 l_formatted_phone_area_code := '';
1154 ELSE
1155 l_formatted_phone_area_code :='('||l_phone_area_code|| ')';
1156 END IF;
1157
1158 l_phone_number := filter_phone_number(l_phone_number);
1159 l_formatted_phone_number := l_formatted_phone_area_code||l_phone_number;
1160 END IF;
1161
1162 -- Append country code if user's country is different
1163 --than the phone_country
1164
1165 IF ((l_phone_country_code <> l_user_country_code) OR
1166 (l_user_country_code IS NULL))
1167 THEN
1168 IF l_phone_country_code IS NOT NULL AND
1169 l_phone_country_code <> fnd_api.g_miss_char --Bug 4578945
1170 THEN
1171 IF l_display_all_prefixes = 'Y' THEN
1172 l_formatted_phone_number := l_phone_country_code ||' '||l_formatted_phone_number;
1173 ELSE
1174 l_formatted_phone_number := '+'||l_phone_country_code ||' '||l_formatted_phone_number;
1175 END IF;
1176 l_prefix := 'I';
1177 ELSE
1178 l_prefix := null;
1179 END IF;
1180
1181 ELSIF l_phone_country_code = l_user_country_code THEN
1185 THEN
1182 ---check if the user's area code is same as phone's area code
1183 IF ((l_phone_area_code = l_default_area_code) AND
1184 (l_display_area_code = 'N'))
1186 l_formatted_phone_number := l_phone_number;
1187 ELSE
1188 l_formatted_phone_number := l_formatted_phone_number;
1189
1190 IF l_phone_area_code IS NOT NULL AND
1191 l_phone_area_code <> fnd_api.g_miss_char
1192 THEN
1193 l_prefix := 'T';
1194 ELSE
1195 l_prefix := null;
1196 END IF; ---l_phone_area_code is not null
1197 END IF; ---l_phone_area_code=l_default_area_code
1198 END IF; ---l_phone_country_code=l_user_country_code
1199
1200 --Check the DISPLAY_ALL_PREFIXES flag
1201 --if set then check pabx prefix
1202
1203 IF l_display_all_prefixes = 'Y' AND
1204 l_formatted_phone_number IS NOT NULL THEN --Bug 4578945
1205 IF l_prefix = 'I' THEN
1206 l_formatted_phone_number := l_phone_intl_prefix||' '||
1207 l_formatted_phone_number;
1208 ELSIF l_prefix = 'T' THEN
1209 l_formatted_phone_number := l_phone_trunk_prefix||' '||
1210 l_formatted_phone_number;
1211 END IF;
1212
1213 ---append the user's PABX prefix if user has set it up
1214 IF l_pabx_prefix IS NOT NULL THEN
1215 l_formatted_phone_number := l_pabx_prefix||' '||
1216 l_formatted_phone_number;
1217 END IF;
1218 END IF;
1219
1220 x_formatted_phone_number := l_formatted_phone_number;
1221
1222 -- Standard call to get msg count and if count is 1, get msg info.
1223 fnd_msg_pub.count_and_get(
1224 p_encoded => fnd_api.g_false,
1225 p_count => x_msg_count,
1226 p_data => x_msg_data);
1227
1228 -- Debug info.
1229 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1230 hz_utility_v2pub.debug_return_messages(
1231 p_msg_count => x_msg_count,
1232 p_msg_data => x_msg_data,
1233 p_msg_type => 'WARNING',
1234 p_msg_level => fnd_log.level_error);
1235 END IF;
1236 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1237 hz_utility_v2pub.debug(
1238 p_message => 'phone_display (-)',
1239 p_prefix => l_debug_prefix,
1240 p_msg_level => fnd_log.level_procedure);
1241 END IF;
1242
1243 EXCEPTION
1244 WHEN fnd_api.g_exc_error THEN
1245 x_return_status := fnd_api.g_ret_sts_error;
1246 fnd_msg_pub.count_and_get(
1247 p_encoded => fnd_api.g_false,
1248 p_count => x_msg_count,
1249 p_data => x_msg_data);
1250
1251 -- Debug info.
1252 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1253 hz_utility_v2pub.debug_return_messages(
1254 p_msg_count => x_msg_count,
1255 p_msg_data => x_msg_data,
1256 p_msg_type => 'ERROR',
1257 p_msg_level => fnd_log.level_error);
1258 END IF;
1259 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1260 hz_utility_v2pub.debug(
1261 p_message => 'phone_display (-)',
1262 p_prefix => l_debug_prefix,
1263 p_msg_level => fnd_log.level_procedure);
1264 END IF;
1265
1266
1267 WHEN fnd_api.g_exc_unexpected_error THEN
1268 x_return_status := fnd_api.g_ret_sts_unexp_error;
1269 fnd_msg_pub.count_and_get(
1270 p_encoded => fnd_api.g_false,
1271 p_count => x_msg_count,
1272 p_data => x_msg_data);
1273
1274 -- Debug info.
1275 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1276 hz_utility_v2pub.debug_return_messages(
1277 p_msg_count => x_msg_count,
1278 p_msg_data => x_msg_data,
1279 p_msg_type => 'UNEXPECTED ERROR',
1280 p_msg_level => fnd_log.level_error);
1281 END IF;
1282 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1283 hz_utility_v2pub.debug(
1284 p_message => 'phone_display (-)',
1285 p_prefix => l_debug_prefix,
1286 p_msg_level => fnd_log.level_procedure);
1287 END IF;
1288
1289 WHEN OTHERS THEN
1290 x_return_status := fnd_api.g_ret_sts_unexp_error;
1291
1292 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1293 fnd_message.set_token('ERROR' ,SQLERRM);
1294 fnd_msg_pub.add;
1295
1296 fnd_msg_pub.count_and_get(
1297 p_encoded => fnd_api.g_false,
1298 p_count => x_msg_count,
1299 p_data => x_msg_data);
1300
1301 -- Debug info.
1302 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
1303 hz_utility_v2pub.debug_return_messages(
1304 p_msg_count => x_msg_count,
1305 p_msg_data => x_msg_data,
1306 p_msg_type => 'SQL ERROR',
1307 p_msg_level => fnd_log.level_error);
1308 END IF;
1309 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
1310 hz_utility_v2pub.debug(
1311 p_message => 'phone_display (-)',
1312 p_prefix => l_debug_prefix,
1313 p_msg_level => fnd_log.level_procedure);
1314 END IF;
1318 --
1315
1316 END Phone_display;
1317
1319 -- PROCEDURE check_mobile_phone
1320 --
1321 -- DESCRIPTION
1322 -- Checks if the Number is Mobile or not
1323 --
1324 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1325 --
1326 -- ARGUMENTS
1327 -- IN:
1328 -- p_init_msg_list Initialize message stack if it is set to
1329 -- FND_API.G_TRUE. Default is fnd_api.g_false.
1330 -- p_phone_country code Country code of the phone number
1331 -- p_phone_area_code Area code
1332 -- p_phone_number Phone Number
1333 --
1334 -- OUT:
1335 -- x_mobile_flag Flag indicating if the number is mobile
1336 -- HZ_PHONE_FORMATS or default format
1337 -- x_return_status Return status after the call. The status can
1338 -- be fnd_api.g_ret_sts_success (success),
1339 -- fnd_api.g_ret_sts_error (error),
1340 -- fnd_api.g_ret_sts_unexp_error
1341 -- (unexpected error).
1342 -- x_msg_count Number of messages in message stack.
1343 -- x_msg_data Message text if x_msg_count is 1.
1344 --
1345 -- NOTES
1346 --
1347 -- MODIFICATION HISTORY
1348 --
1349 -- 01-10-2002 Jyoti Pandey o Created.
1350
1351
1352 PROCEDURE check_mobile_phone (
1353 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1354 p_phone_country_code IN VARCHAR2 := fnd_api.g_miss_char,
1355 p_phone_area_code IN VARCHAR2 := fnd_api.g_miss_char,
1356 p_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
1357 x_mobile_flag OUT NOCOPY VARCHAR2,
1358 x_return_status OUT NOCOPY VARCHAR2,
1359 x_msg_count OUT NOCOPY NUMBER,
1360 x_msg_data OUT NOCOPY VARCHAR2)IS
1361
1362
1363 l_debug_prefix VARCHAR2(30) := ''; -- translate_raw_phone_number
1364 l_possible_prefix VARCHAR2(30);
1365 l_count_mobile_prefix NUMBER := 0;
1366 l_length_mobile_prefix NUMBER := 0;
1367 l_mobile_prefix_check HZ_MOBILE_PREFIXES.MOBILE_PREFIX%TYPE;
1368 l_mobile_prefix HZ_MOBILE_PREFIXES.MOBILE_PREFIX%TYPE;
1369
1370 BEGIN
1371
1372 SAVEPOINT check_mobile_phone;
1373
1374 -- Check if API is called in debug mode. If yes, enable debug.
1375 --enable_debug;
1376
1377 -- Debug info.
1378 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1379 hz_utility_v2pub.debug(p_message=>'check_mobile_phone (+)',
1380 p_prefix=>l_debug_prefix,
1381 p_msg_level=>fnd_log.level_procedure);
1382 END IF;
1383
1384 -- initialize message list if p_init_msg_list is set to TRUE.
1385 IF FND_API.to_Boolean(p_init_msg_list) THEN
1386 FND_MSG_PUB.initialize;
1387 END IF;
1388
1389 -- initialize API return status to success.
1390 x_return_status := FND_API.G_RET_STS_SUCCESS;
1391
1392
1393 IF p_phone_country_code is NOT NULL THEN
1394
1395 l_possible_prefix := p_phone_area_code||p_phone_number;
1396
1397 l_possible_prefix := filter_phone_number (l_possible_prefix);
1398
1399 select count(mobile_prefix)
1400 into l_count_mobile_prefix
1401 from hz_mobile_prefixes
1402 where phone_country_code = p_phone_country_code;
1403
1404 select max(length(mobile_prefix))
1405 into l_length_mobile_prefix
1406 from hz_mobile_prefixes
1407 where phone_country_code = p_phone_country_code;
1408
1409 IF l_count_mobile_prefix > 0 then
1410
1411 --parse using that table
1412 for i in 1..l_length_mobile_prefix
1413 loop
1414 begin
1415 l_mobile_prefix_check := SUBSTR(l_possible_prefix,1,i);
1416
1417 select mobile_prefix
1418 into l_mobile_prefix
1419 from hz_mobile_prefixes
1420 where mobile_prefix = l_mobile_prefix_check
1421 and phone_country_code = p_phone_country_code ;
1422
1423 if l_mobile_prefix_check = l_mobile_prefix then
1424 x_mobile_flag := 'Y';
1425 exit;
1426 else
1427 null;
1428 end if;
1429
1430 exception
1431 when no_data_found then
1432 Null;
1433 end;
1434 end loop;
1435
1436 if x_mobile_flag is null then
1437 x_mobile_flag := 'N';
1438 end if;
1439
1440 ELSE
1441 x_mobile_flag := 'N'; ---l_count_mobile_prefix <= 0
1442 END IF;
1443 ELSE ---p_phone_country_code is NULL
1444 x_mobile_flag := 'N';
1445 END IF; ---p_phone_country_code is NULL
1446
1447 -- Standard call to get msg count and if count is 1, get msg info.
1448 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1449 p_count => x_msg_count,
1450 p_data => x_msg_data);
1451
1452 -- Debug info.
1453 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1454 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1455 p_msg_data=>x_msg_data,
1456 p_msg_type=>'WARNING',
1457 p_msg_level=>fnd_log.level_exception);
1458 END IF;
1459 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1460 hz_utility_v2pub.debug(p_message=>'check_mobile_phone (-)',
1461 p_prefix=>l_debug_prefix,
1462 p_msg_level=>fnd_log.level_procedure);
1466 --disable_debug;
1463 END IF;
1464
1465 -- Check if API is called in debug mode. If yes, disable debug.
1467
1468 EXCEPTION
1469 WHEN fnd_api.g_exc_error THEN
1470 x_return_status := fnd_api.g_ret_sts_error;
1471
1472 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1473 p_count => x_msg_count,
1474 p_data => x_msg_data);
1475
1476 -- Debug info.
1477 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1478 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1479 p_msg_data=>x_msg_data,
1480 p_msg_type=>'ERROR',
1481 p_msg_level=>fnd_log.level_error);
1482 END IF;
1483 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1484 hz_utility_v2pub.debug(p_message=>'check_mobile_phone (-)',
1485 p_prefix=>l_debug_prefix,
1486 p_msg_level=>fnd_log.level_procedure);
1487 END IF;
1488
1489 -- Check if API is called in debug mode. If yes, disable debug.
1490 --disable_debug;
1491
1492 WHEN fnd_api.g_exc_unexpected_error THEN
1493 x_return_status := fnd_api.g_ret_sts_unexp_error;
1494
1495 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1496 p_count => x_msg_count,
1497 p_data => x_msg_data);
1498
1499 -- Debug info.
1500 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1501 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1502 p_msg_data=>x_msg_data,
1503 p_msg_type=>'UNEXPECTED ERROR',
1504 p_msg_level=>fnd_log.level_error);
1505 END IF;
1506 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1507 hz_utility_v2pub.debug(p_message=>'check_mobile_phone (-)',
1508 p_prefix=>l_debug_prefix,
1509 p_msg_level=>fnd_log.level_procedure);
1510 END IF;
1511
1512
1513 -- Check if API is called in debug mode. If yes, disable debug.
1514 --disable_debug;
1515
1516 WHEN OTHERS THEN
1517 x_return_status := fnd_api.g_ret_sts_unexp_error;
1518
1519 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1520 fnd_message.set_token('ERROR' ,SQLERRM);
1521 fnd_msg_pub.add;
1522
1523 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1524 p_count => x_msg_count,
1525 p_data => x_msg_data);
1526
1527 -- Debug info.
1528 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1529 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1530 p_msg_data=>x_msg_data,
1531 p_msg_type=>'SQL ERROR',
1532 p_msg_level=>fnd_log.level_error);
1533 END IF;
1534 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1535 hz_utility_v2pub.debug(p_message=>'check_mobile_phone (-)',
1536 p_prefix=>l_debug_prefix,
1537 p_msg_level=>fnd_log.level_procedure);
1538 END IF;
1539
1540 -- Check if API is called in debug mode. If yes, disable debug.
1541 --disable_debug;
1542
1543
1544 END check_mobile_phone;
1545
1546
1547 --------------------------------------
1548 -- private procedures and functions
1549 --------------------------------------
1550
1551 PROCEDURE get_user_phone_preferences(
1552 p_customer_id IN VARCHAR2,
1553 x_user_phone_preferences_rec OUT NOCOPY user_phone_preferences_rec
1554 ) IS
1555
1556 CURSOR c_party (
1557 p_party_id NUMBER
1558 ) IS
1559 SELECT null
1560 FROM hz_party_preferences
1561 WHERE party_id = p_party_id
1562 AND ROWNUM = 1;
1563
1564 l_dummy VARCHAR2(1);
1565 l_user_territory_code VARCHAR2(400);
1566 l_default_area_code VARCHAR2(400);
1567 l_display_area_code VARCHAR2(400);
1568 l_display_all_prefixes VARCHAR2(400);
1569 l_pabx_prefix VARCHAR2(400);
1570
1571 BEGIN
1572
1573 x_user_phone_preferences_rec := NULL;
1574
1575 IF (NOT (pref_user_territory_code_tab.EXISTS(p_customer_id))) THEN
1576
1577 OPEN c_party (p_customer_id);
1578 FETCH c_party INTO l_dummy;
1579
1580 IF c_party%NOTFOUND THEN
1581 pref_user_territory_code_tab(p_customer_id) := NULL;
1582 pref_default_area_code_tab(p_customer_id) := NULL;
1583 pref_display_area_code_tab(p_customer_id) := NULL;
1584 pref_display_all_prefix_tab(p_customer_id) := NULL;
1585 pref_pabx_prefix_tab(p_customer_id) := NULL;
1586
1587 ELSE
1588 -- get party preference if it is a valid party and
1589 -- has preferences
1590
1591 l_user_territory_code :=
1592 hz_preference_pub.value_varchar2(
1593 p_customer_id,'TCA Phone','USER_TERRITORY_CODE');
1594
1595 l_default_area_code :=
1596 hz_preference_pub.value_varchar2(
1597 p_customer_id,'TCA Phone','DEFAULT_AREA_CODE');
1598
1599 l_display_area_code :=
1600 hz_preference_pub.value_varchar2(
1601 p_customer_id,'TCA Phone','DISPLAY_AREA_CODE');
1602
1603 l_display_all_prefixes :=
1604 hz_preference_pub.value_varchar2(
1608 hz_preference_pub.value_varchar2(
1605 p_customer_id,'TCA Phone','DISPLAY_ALL_PREFIXES');
1606
1607 l_pabx_prefix :=
1609 p_customer_id,'TCA Phone','PABX_PREFIX');
1610
1611 pref_user_territory_code_tab(p_customer_id) := l_user_territory_code;
1612 pref_default_area_code_tab(p_customer_id) := l_default_area_code;
1613 pref_display_area_code_tab(p_customer_id) := l_display_area_code;
1614 pref_display_all_prefix_tab(p_customer_id) := l_display_all_prefixes;
1615 pref_pabx_prefix_tab(p_customer_id) := l_pabx_prefix;
1616
1617 -- construct return
1618 x_user_phone_preferences_rec.user_territory_code := l_user_territory_code;
1619 x_user_phone_preferences_rec.default_area_code := l_default_area_code;
1620 x_user_phone_preferences_rec.display_area_code := l_display_area_code;
1621 x_user_phone_preferences_rec.display_all_prefixes := l_display_all_prefixes;
1622 x_user_phone_preferences_rec.pabx_prefix := l_pabx_prefix;
1623
1624 END IF;
1625 CLOSE c_party;
1626
1627 ELSE
1628 -- construct return
1629 x_user_phone_preferences_rec.user_territory_code := pref_user_territory_code_tab(p_customer_id);
1630 x_user_phone_preferences_rec.default_area_code := pref_default_area_code_tab(p_customer_id);
1631 x_user_phone_preferences_rec.display_area_code := pref_display_area_code_tab(p_customer_id);
1632 x_user_phone_preferences_rec.display_all_prefixes := pref_display_all_prefix_tab(p_customer_id);
1633 x_user_phone_preferences_rec.pabx_prefix := pref_pabx_prefix_tab(p_customer_id);
1634 END IF;
1635
1636 END get_user_phone_preferences;
1637
1638
1639 PROCEDURE cache_phone_country_via_terr (
1640 p_territory_code IN VARCHAR2,
1641 x_phone_country_code OUT NOCOPY VARCHAR2,
1642 x_trunk_prefix OUT NOCOPY VARCHAR2,
1643 x_intl_prefix OUT NOCOPY VARCHAR2
1644 ) IS
1645
1646 CURSOR c_phone_country IS
1647 SELECT phone_country_code,
1648 trunk_prefix,
1649 intl_prefix
1650 FROM hz_phone_country_codes
1651 WHERE territory_code = p_territory_code;
1652
1653 BEGIN
1654
1655 IF terr_phone_country_tab.EXISTS(p_territory_code) THEN
1656 x_phone_country_code := terr_phone_country_tab(p_territory_code);
1657 x_trunk_prefix := terr_trunk_prefix_tab(p_territory_code);
1658 x_intl_prefix := terr_intl_prefix_tab(p_territory_code);
1659 ELSE
1660 OPEN c_phone_country;
1661 FETCH c_phone_country INTO
1662 x_phone_country_code, x_trunk_prefix, x_intl_prefix;
1663
1664 IF c_phone_country%NOTFOUND THEN
1665 terr_phone_country_tab(p_territory_code) := NULL;
1666 terr_trunk_prefix_tab(p_territory_code) := NULL;
1667 terr_intl_prefix_tab(p_territory_code) := NULL;
1668 ELSE
1669 terr_phone_country_tab(p_territory_code) := x_phone_country_code;
1670 terr_trunk_prefix_tab(p_territory_code) := x_trunk_prefix;
1671 terr_intl_prefix_tab(p_territory_code) := x_intl_prefix;
1672 END IF;
1673 CLOSE c_phone_country;
1674
1675 END IF;
1676
1677 END cache_phone_country_via_terr;
1678
1679
1680 PROCEDURE cache_terr_via_phone_country (
1681 p_phone_country_code IN VARCHAR2,
1682 x_phone_territory_code OUT NOCOPY VARCHAR2
1683 ) IS
1684
1685 CURSOR c_territory IS
1686 SELECT pc.territory_code
1687 FROM hz_phone_country_codes pc
1688 WHERE phone_country_code = p_phone_country_code
1689 AND EXISTS (
1690 SELECT null
1691 FROM hz_phone_formats pf
1692 WHERE pf.territory_code = pc.territory_code)
1693 AND ROWNUM =1;
1694
1695 BEGIN
1696
1697 IF phone_territory_tab.EXISTS(p_phone_country_code) THEN
1698 x_phone_territory_code := phone_territory_tab(p_phone_country_code);
1699 ELSE
1700 OPEN c_territory;
1701 FETCH c_territory INTO x_phone_territory_code;
1702
1703 IF c_territory%NOTFOUND THEN
1704 phone_territory_tab(p_phone_country_code) := NULL;
1705 ELSE
1706 phone_territory_tab(p_phone_country_code) := x_phone_territory_code;
1707 END IF;
1708 CLOSE c_territory;
1709 END IF;
1710
1711 END cache_terr_via_phone_country;
1712
1713
1714 /*----------------------parse_intl_prefix--------------------------*/
1715
1716 PROCEDURE parse_intl_prefix(
1717 p_with_iprefix IN VARCHAR2 := fnd_api.g_miss_char,
1718 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
1719 x_intl_prefix OUT NOCOPY VARCHAR2,
1720 x_without_iprefix OUT NOCOPY VARCHAR2) IS
1721
1722 l_with_iprefix VARCHAR2(30);
1723 l_without_iprefix VARCHAR2(30);
1724 l_intl_prefix VARCHAR2(5);
1725 l_check_sign NUMBER;
1726 l_prefix_check VARCHAR2(30);
1727 l_length_with_iprefix NUMBER;
1728 l_length_of_iprefix NUMBER;
1729 l_debug_prefix VARCHAR2(30) := '';
1730 BEGIN
1731
1732 -- Debug info.
1733 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1734 hz_utility_v2pub.debug(p_message=>'parse_intl_prefix (+)',
1735 p_prefix=>l_debug_prefix,
1736 p_msg_level=>fnd_log.level_procedure);
1737 END IF;
1738
1739 l_with_iprefix := p_with_iprefix;
1740
1741 ---Get the International prefix for the country
1742 select intl_prefix
1743 into l_intl_prefix
1744 from hz_phone_country_codes
1748 l_length_of_iprefix := LENGTH(l_intl_prefix);
1745 where territory_code = p_territory_code;
1746
1747 l_length_with_iprefix := LENGTH(l_with_iprefix);
1749
1750 ---check for the presence of the intl prefix(max=3) in the number
1751
1752 if l_intl_prefix is not null then
1753 l_prefix_check := SUBSTR(l_with_iprefix,1,l_length_of_iprefix);
1754
1755 if l_prefix_check = l_intl_prefix then
1756 ---chop off the intl prefix
1757 l_without_iprefix :=
1758 SUBSTR(l_with_iprefix ,l_length_of_iprefix+1 ,
1759 l_length_with_iprefix);
1760 else
1761
1762 ---if could not parse the intl prefix after the loop
1763 l_prefix_check := NULL;
1764 l_without_iprefix := l_with_iprefix;
1765 end if;
1766
1767 else ---if l_intl_prefix is null
1768 l_prefix_check := NULL;
1769 l_without_iprefix := l_with_iprefix;
1770
1771 end if; ---for l_intl_prefix not null
1772
1773
1774 x_intl_prefix := l_prefix_check;
1775 x_without_iprefix := l_without_iprefix;
1776
1777 -- Debug info.
1778 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1779 hz_utility_v2pub.debug(p_message=>'parse_intl_prefix (-)',
1780 p_prefix=>l_debug_prefix,
1781 p_msg_level=>fnd_log.level_procedure);
1782 END IF;
1783
1784 EXCEPTION
1785
1786 WHEN NO_DATA_FOUND THEN
1787 x_intl_prefix := NULL;
1788 x_without_iprefix := l_with_iprefix;
1789
1790 WHEN OTHERS THEN
1791 RAISE fnd_api.g_exc_unexpected_error;
1792
1793 END parse_intl_prefix;
1794
1795 /*---------------------parse_trunk_prefix------------------------*/
1796
1797 PROCEDURE parse_trunk_prefix(
1798 p_with_tprefix IN VARCHAR2 := fnd_api.g_miss_char,
1799 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
1800 x_trunk_prefix OUT NOCOPY VARCHAR2,
1801 x_without_tprefix OUT NOCOPY VARCHAR2) IS
1802
1803 l_with_tprefix VARCHAR2(30);
1804 l_without_tprefix VARCHAR2(30);
1805 l_trunk_prefix VARCHAR2(5);
1806 l_tprefix_check VARCHAR2(30);
1807 l_length_with_tprefix NUMBER;
1808 l_length_of_tprefix NUMBER;
1809 l_debug_prefix VARCHAR2(30) := '';
1810
1811
1812 BEGIN
1813
1814 -- Debug info.
1815 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1816 hz_utility_v2pub.debug(p_message=>'parse_trunk_prefix (+)',
1817 p_prefix=>l_debug_prefix,
1818 p_msg_level=>fnd_log.level_procedure);
1819 END IF;
1820
1821 l_with_tprefix := p_with_tprefix;
1822
1823 ---Get the trunk prefix for the country
1824 select trunk_prefix
1825 into l_trunk_prefix
1826 from hz_phone_country_codes
1827 where territory_code = p_territory_code;
1828
1829 l_length_with_tprefix := LENGTH(l_with_tprefix);
1830 l_length_of_tprefix := LENGTH(l_trunk_prefix);
1831
1832
1833 ---check for the presence of the trunk prefix(max=3) in the number
1834
1835 if l_trunk_prefix is not null then
1836 l_tprefix_check := SUBSTR(l_with_tprefix,1,l_length_of_tprefix);
1837
1838 if l_tprefix_check = l_trunk_prefix then
1839 ---chop off the trunk prefix
1840
1841 l_without_tprefix :=
1842 SUBSTR(l_with_tprefix ,l_length_of_tprefix+1 ,
1843 l_length_with_tprefix);
1844 g_international_flag := 'N';
1845
1846 else
1847 l_tprefix_check := NULL;
1848 l_without_tprefix := l_with_tprefix;
1849 end if;
1850
1851 else ---if l_trunk_prefix is null
1852
1853 l_tprefix_check := NULL;
1854 l_without_tprefix := l_with_tprefix;
1855
1856 end if; ---for l_trunk_prefix not null
1857
1858 x_trunk_prefix := l_tprefix_check;
1859 x_without_tprefix := l_without_tprefix;
1860
1861 -- Debug info.
1862 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1863 hz_utility_v2pub.debug(p_message=>'parse_trunk_prefix (-)',
1864 p_prefix=>l_debug_prefix,
1865 p_msg_level=>fnd_log.level_procedure);
1866 END IF;
1867
1868 EXCEPTION
1869 WHEN NO_DATA_FOUND THEN
1870 x_trunk_prefix := NULL;
1871 x_without_tprefix := l_with_tprefix;
1872
1873 WHEN OTHERS THEN
1874 RAISE fnd_api.g_exc_unexpected_error;
1875
1876 END parse_trunk_prefix;
1877
1878 -------PARSE COUNTRY CODE ----------------------
1879 PROCEDURE parse_country_code(
1880 p_with_country_code IN VARCHAR2 := fnd_api.g_miss_char,
1881 x_country_code OUT NOCOPY VARCHAR2,
1882 x_without_country_code OUT NOCOPY VARCHAR2) IS
1883
1884 x_parsed_country_code VARCHAR2(5);
1885 l_with_country_code VARCHAR2(30);
1886 l_without_country_code VARCHAR2(30);
1887 l_phone_country_code VARCHAR2(5);
1888 l_check_sign number;
1889 l_country_code_check varchar2(30);
1890 l_length_ccode NUMBER;
1891 l_debug_prefix VARCHAR2(30) := '';
1892 BEGIN
1893
1894 -- Debug info.
1895 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1899 END IF;
1896 hz_utility_v2pub.debug(p_message=>'parse_country_code (+)',
1897 p_prefix=>l_debug_prefix,
1898 p_msg_level=>fnd_log.level_procedure);
1900
1901 l_with_country_code := p_with_country_code ;
1902 l_length_ccode := LENGTH(l_with_country_code);
1903
1904 for i in 1..4
1905 loop
1906 begin
1907 l_country_code_check := SUBSTR(l_with_country_code,1,i);
1908
1909 --row num =1 to eliminate where 2 countries with same country_code
1910 select phone_country_code
1911 into l_phone_country_code
1912 from hz_phone_country_codes
1913 where phone_country_code = l_country_code_check
1914 and rownum = 1;
1915
1916
1917 l_without_country_code :=
1918 SUBSTR(l_with_country_code ,i+1 ,l_length_ccode);
1919 x_country_code := l_country_code_check;
1920 exit;
1921
1922 exception
1923 when no_data_found then
1924 Null;
1925 end;
1926 end loop;
1927
1928
1929 if l_without_country_code is null then
1930 l_country_code_check := NULL;
1931 l_without_country_code := l_with_country_code;
1932 end if;
1933
1934 x_parsed_country_code := l_country_code_check;
1935 x_without_country_code := l_without_country_code;
1936
1937 -- Debug info.
1938 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1939 hz_utility_v2pub.debug(p_message=>'parse_country_code (-)',
1940 p_prefix=>l_debug_prefix,
1941 p_msg_level=>fnd_log.level_procedure);
1942 END IF;
1943
1944 EXCEPTION
1945 WHEN OTHERS THEN
1946 RAISE fnd_api.g_exc_unexpected_error;
1947
1948 end parse_country_code;
1949
1950 ---------PARSE AREA CODE AND PHONE NUMBER-----------------
1951 PROCEDURE parse_area_code(
1952 p_with_area_code IN VARCHAR2 := fnd_api.g_miss_char,
1953 p_phone_country_code IN VARCHAR2 := fnd_api.g_miss_char,
1954 x_parsed_area_code OUT NOCOPY VARCHAR2,
1955 x_parsed_phone_number OUT NOCOPY VARCHAR2) IS
1956
1957 l_with_area_code varchar2(30);
1958 l_without_area_code VARCHAR2(30);
1959 l_area_code varchar2(5);
1960 l_area_code_check varchar2(30);
1961 l_country_code_for_area_code varchar2(5);
1962 l_length_with_code NUMBER;
1963 l_area_code_length NUMBER;
1964 l_phone_length NUMBER;
1965 l_count_area_code NUMBER;
1966 l_debug_prefix VARCHAR2(30) := '';
1967
1968
1969 BEGIN
1970
1971 -- Debug info.
1972 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1973 hz_utility_v2pub.debug(p_message=>'parse_area_code (+)',
1974 p_prefix=>l_debug_prefix,
1975 p_msg_level=>fnd_log.level_procedure);
1976 END IF;
1977
1978 l_with_area_code := p_with_area_code ;
1979 l_length_with_code := LENGTH(l_with_area_code);
1980
1981 ----If country code not parsed/provided assume local number
1982 IF p_phone_country_code is NULL THEN
1983 l_country_code_for_area_code := g_user_phone_country_code;
1984 ELSE
1985 l_country_code_for_area_code := p_phone_country_code;
1986 END IF;
1987
1988 ----First see if the length of the area code is fixed
1989 SELECT area_code_length,
1990 phone_length
1991 into l_area_code_length, l_phone_length
1992 from hz_phone_country_codes
1993 where phone_country_code = l_country_code_for_area_code
1994 and rownum = 1;
1995
1996
1997 IF ( l_area_code_length is not null) then
1998
1999 if ((l_phone_length is null) OR (l_phone_length =l_length_with_code) ) then
2000
2001 l_area_code_check := SUBSTR(l_with_area_code,1,
2002 l_area_code_length);
2003 l_without_area_code :=
2004 SUBSTR(l_with_area_code ,l_area_code_length+1 ,
2005 l_length_with_code);
2006 x_parsed_area_code := l_area_code_check;
2007 else
2008 l_area_code_check := NULL;
2009 l_without_area_code := l_with_area_code;
2010 end if;
2011
2012 ELSE
2013
2014 select count(area_code)
2015 into l_count_area_code
2016 from hz_phone_area_codes
2017 where phone_country_code = l_country_code_for_area_code;
2018
2019 IF l_count_area_code > 0 then
2020
2021 --parse using that table
2022 for i in 1..5
2023 loop
2024 begin
2025 l_area_code_check := SUBSTR(l_with_area_code,1,i);
2026
2027 select area_code
2028 into l_area_code
2029 from hz_phone_area_codes
2030 where area_code = l_area_code_check
2031 and phone_country_code = l_country_code_for_area_code;
2032
2033
2034
2035 if l_area_code_check = l_area_code then
2036 l_without_area_code := SUBSTR(l_with_area_code ,i+1 ,
2037 l_length_with_code);
2038 x_parsed_area_code := l_area_code_check;
2039 exit;
2040 else
2041 null;
2042 end if;
2043
2044 exception
2045 when no_data_found then
2046 Null;
2047 end;
2048 end loop;
2049
2053 END IF;
2050 IF l_without_area_code is null then
2051 l_area_code_check := NULL;
2052 l_without_area_code := l_with_area_code;
2054
2055 ELSE ---area code list is not there to parse
2056 l_area_code_check := NULL;
2057 l_without_area_code := l_with_area_code;
2058 END IF;
2059 END IF;
2060
2061
2062 x_parsed_area_code := l_area_code_check;
2063 x_parsed_phone_number := l_without_area_code;
2064
2065 -- Debug info.
2066 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2067 hz_utility_v2pub.debug(p_message=>'parse_area_code (-)',
2068 p_prefix=>l_debug_prefix,
2069 p_msg_level=>fnd_log.level_procedure);
2070 END IF;
2071
2072 exception
2073 when no_data_found then
2074 null;
2075
2076
2077 END PARSE_AREA_CODE;
2078
2079 /*-----------------filter_phone_number----------------------------*/
2080 FUNCTION filter_phone_number (
2081 p_phone_number IN VARCHAR2,
2082 p_isformat IN NUMBER := 0
2083 ) RETURN VARCHAR2 IS
2084
2085 l_filtered_number VARCHAR2(100);
2086 l_debug_prefix VARCHAR2(30) := '';
2087
2088 BEGIN
2089
2090 -- Debug info.
2091 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2092 hz_utility_v2pub.debug(p_message=>'filter_phone_number (+)',
2093 p_prefix=>l_debug_prefix,
2094 p_msg_level=>fnd_log.level_procedure);
2095 END IF;
2096
2097 IF p_isformat = 0 THEN
2098 l_filtered_number := TRANSLATE (
2099 p_phone_number,
2100 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz()- .+''~`\/@#$%^&*_,|}{[]?<>=";:',
2101 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
2102 ELSE
2103 l_filtered_number := TRANSLATE (
2104 p_phone_number,
2105 '9012345678ABCDEFGHIJKLMNOPQRSTUVWXYZ()- .+''~`\/@#$%^&*_,|}{[]?<>=";:',
2106 '9');
2107 END IF;
2108
2109 -- Debug info.
2110 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2111 hz_utility_v2pub.debug(p_message=>'filter_phone_number (-)',
2112 p_prefix=>l_debug_prefix,
2113 p_msg_level=>fnd_log.level_procedure);
2114 END IF;
2115
2116 RETURN l_filtered_number;
2117
2118 END filter_phone_number;
2119
2120
2121 PROCEDURE get_phone_format (
2122 p_raw_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
2123 p_territory_code IN VARCHAR2 := fnd_api.g_miss_char,
2124 p_area_code IN VARCHAR2,
2125 x_phone_country_code OUT NOCOPY VARCHAR2,
2126 x_phone_format_style OUT NOCOPY VARCHAR2,
2127 x_area_code_size OUT NOCOPY NUMBER,
2128 x_msg OUT NOCOPY VARCHAR2
2129 ) IS
2130
2131 l_debug_prefix VARCHAR2(30) := '';
2132 phone_format_style_tab var_table := var_table();
2133 area_code_size_tab num_table := num_table();
2134 phone_country_code_tab var_table := var_table();
2135 i NUMBER;
2136
2137 -- Query all the format styles along with other flags
2138 CURSOR c_formats IS
2139 SELECT pf.phone_format_style,
2140 pf.area_code_size,
2141 pcc.phone_country_code
2142 FROM hz_phone_country_codes pcc,
2143 hz_phone_formats pf
2144 WHERE pcc.territory_code = p_territory_code
2145 AND pcc.territory_code = pf.territory_code;
2146
2147 BEGIN
2148
2149 -- Debug info.
2150 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2151 hz_utility_v2pub.debug(
2152 p_message => 'get_phone_format (+)',
2153 p_prefix => l_debug_prefix,
2154 p_msg_level => fnd_log.level_procedure);
2155 END IF;
2156
2157 -- cache phone formats
2158
2159 IF (NOT format_style_tab.EXISTS(p_territory_code || '#1')) THEN
2160 OPEN c_formats;
2161 FETCH c_formats BULK COLLECT INTO
2162 phone_format_style_tab, area_code_size_tab, phone_country_code_tab;
2163 CLOSE c_formats;
2164
2165 IF phone_format_style_tab.count = 0 THEN
2166 format_style_tab(p_territory_code || '#' || 1) := null;
2167 format_area_code_size_tab(p_territory_code || '#' || 1) := null;
2168 format_phone_country_tab(p_territory_code || '#' || 1) := null;
2169
2170 RETURN;
2171 ELSE
2172 FOR i IN 1..phone_format_style_tab.count LOOP
2173 format_style_tab(p_territory_code || '#' || i) := phone_format_style_tab(i);
2174 format_area_code_size_tab(p_territory_code || '#' || i) := area_code_size_tab(i);
2175 format_phone_country_tab(p_territory_code || '#' || i) := phone_country_code_tab(i);
2176 END LOOP;
2177 END IF;
2178
2179 ELSE -- get value from cache.
2180 i := 1;
2181 WHILE (format_style_tab.EXISTS(p_territory_code || '#' || i) AND
2182 format_style_tab(p_territory_code || '#' || i) IS NOT NULL)
2183 LOOP
2184 phone_format_style_tab.extend(1);
2185 phone_format_style_tab(i) := format_style_tab(p_territory_code || '#' || i);
2186
2187 area_code_size_tab.extend(1);
2188 area_code_size_tab(i) := format_area_code_size_tab(p_territory_code || '#' || i);
2189
2190 phone_country_code_tab.extend(1);
2191 phone_country_code_tab(i) := format_phone_country_tab(p_territory_code || '#' || i);
2192
2193 i := i + 1;
2197
2194 END LOOP;
2195
2196 END IF;
2198 FOR i IN 1..phone_format_style_tab.count LOOP
2199 IF LENGTHB(filter_phone_number(phone_format_style_tab(i), 1)) =
2200 LENGTHB(p_raw_phone_number)
2201 THEN
2202 IF p_area_code IS NULL OR
2203 (p_area_code IS NOT NULL AND
2204 LENGTHB(p_area_code) = area_code_size_tab(i))
2205 THEN
2206 x_phone_format_style := phone_format_style_tab(i);
2207 x_area_code_size := area_code_size_tab(i);
2208 x_phone_country_code := phone_country_code_tab(i);
2209
2210 EXIT;
2211 END IF;
2212 END IF;
2213 END LOOP;
2214
2215 -- Debug info.
2216 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2217 hz_utility_v2pub.debug(
2218 p_message => 'get_phone_format (-)',
2219 p_prefix => l_debug_prefix,
2220 p_msg_level => fnd_log.level_procedure);
2221 END IF;
2222
2223 END get_phone_format;
2224
2225 /*------------------------translate_raw_phone_number----------------*/
2226
2227 PROCEDURE translate_raw_phone_number (
2228 p_raw_phone_number IN VARCHAR2 := fnd_api.g_miss_char,
2229 p_phone_format_style IN VARCHAR2 := fnd_api.g_miss_char,
2230 x_formatted_phone_number OUT NOCOPY VARCHAR2
2231 ) IS
2232
2233 l_debug_prefix VARCHAR2(30) := ''; -- translate_raw_phone_number
2234
2235 l_phone_counter NUMBER := 1;
2236 l_format_length NUMBER;
2237 l_format_char VARCHAR2(1);
2238
2239 BEGIN
2240
2241 -- Debug info.
2242 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2243 hz_utility_v2pub.debug(p_message=>'translate_raw_phone_number (+)',
2244 p_prefix=>l_debug_prefix,
2245 p_msg_level=>fnd_log.level_procedure);
2246 END IF;
2247
2248 l_format_length := LENGTH(p_phone_format_style);
2249 x_formatted_phone_number := '';
2250
2251 -- Loop through each character of the phone format string
2252 -- and construct the formatted phone number
2253
2254 FOR i IN 1..l_format_length LOOP
2255 l_format_char := SUBSTR(p_phone_format_style, i, 1);
2256
2257 IF l_format_char = '9' THEN
2258 x_formatted_phone_number := x_formatted_phone_number ||
2259 SUBSTR(p_raw_phone_number,
2260 l_phone_counter, 1);
2261 l_phone_counter := l_phone_counter + 1;
2262 ELSE
2263 x_formatted_phone_number := x_formatted_phone_number ||
2264 l_format_char;
2265 END IF;
2266 END LOOP;
2267 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2268 hz_utility_v2pub.debug(p_message=>'translate_raw_phone_number (-)',
2269 p_prefix=>l_debug_prefix,
2270 p_msg_level=>fnd_log.level_procedure);
2271 END IF;
2272
2273 END translate_raw_phone_number;
2274
2275
2276 FUNCTION get_formatted_phone(
2277 p_contact_point_id IN NUMBER,
2278 p_display_purpose IN VARCHAR2 := fnd_api.g_true)
2279 RETURN VARCHAR2 IS
2280 --3865843
2281 x_formatted_phone_number VARCHAR2(220);
2282 x_return_status VARCHAR2(1);
2283 x_msg_count NUMBER;
2284 x_msg_data VARCHAR2(2000);
2285 x_phone_line_type VARCHAR2(30);
2286 x_phone_extension VARCHAR2(20);
2287
2288 l_phone_country_code varchar2(100);
2289 l_phone_area_code varchar2(100);
2290 l_phone_number varchar2(100);
2291 l_phone_extension varchar2(100);
2292 l_phone_line_type varchar2(100);
2293 BEGIN
2294
2295 IF p_contact_point_id IS NULL THEN
2296 RETURN NULL;
2297 END IF;
2298
2299 phone_display(
2300 fnd_api.g_true,
2301 p_contact_point_id,
2302 x_formatted_phone_number,
2303 x_return_status,
2304 x_msg_count,
2305 x_msg_data);
2306
2307 SELECT phone_extension, hz_utility_v2pub.get_lookupmeaning('AR_LOOKUPS', 'PHONE_LINE_TYPE', phone_line_type)
2308 INTO x_phone_extension, x_phone_line_type
2309 FROM hz_contact_points
2310 WHERE contact_point_id = p_contact_point_id;
2311
2312 IF x_phone_extension IS NOT NULL THEN
2313 x_formatted_phone_number := x_formatted_phone_number || ' x' || x_phone_extension;
2314 END IF;
2315
2316 IF p_display_purpose = fnd_api.g_true THEN
2317 x_formatted_phone_number := x_formatted_phone_number || ' (' || x_phone_line_type || ')';
2318 END IF;
2319 RETURN x_formatted_phone_number;
2320
2321 END get_formatted_phone;
2322
2323
2324 /**
2325 * FUNCTION get_formatted_phone
2326 *
2327 * DESCRIPTION
2328 * Overloaded function to return a formatted phone number.
2329 *
2330 * ARGUMENTS
2331 * IN:
2332 * p_phone_country_code phone country code
2333 * p_phone_area_code phone area code
2334 * p_phone_number phone number
2335 * p_phone_extension phone extension
2336 * p_phone_line_type phone line type
2337 * p_display_purpose If the return is for display purpose.
2338 * When the value is FND_API.G_TRUE, return
2339 * formatted phone number (phone line type)
2340 *
2341 * RETURNS : VARCHAR2
2342 *
2343 */
2344
2345 FUNCTION get_formatted_phone (
2346 p_phone_country_code IN VARCHAR2,
2350 p_phone_line_type IN VARCHAR2,
2347 p_phone_area_code IN VARCHAR2,
2348 p_phone_number IN VARCHAR2,
2349 p_phone_extension IN VARCHAR2,
2351 p_display_purpose IN VARCHAR2 DEFAULT NULL
2352 ) RETURN VARCHAR2 IS
2353
2354 l_display_purpose VARCHAR2(10);
2355 l_return_status VARCHAR2(1);
2356 l_msg_count NUMBER;
2357 l_msg_data VARCHAR2(2000);
2358 l_phone_line_meaning VARCHAR2(80);
2359 --3865843
2360 x_formatted_phone_number VARCHAR2(220);
2361
2362 BEGIN
2363
2364 IF p_display_purpose IS NULL THEN
2365 l_display_purpose := fnd_api.g_true;
2366 ELSE
2367 l_display_purpose := p_display_purpose;
2368 END IF;
2369
2370 phone_display (
2371 p_phone_country_code => p_phone_country_code,
2372 p_phone_area_code => p_phone_area_code,
2373 p_phone_number => p_phone_number,
2374 x_formatted_phone_number => x_formatted_phone_number,
2375 x_return_status => l_return_status,
2376 x_msg_count => l_msg_count,
2377 x_msg_data => l_msg_data
2378 );
2379
2380 IF p_phone_extension IS NOT NULL THEN
2381 x_formatted_phone_number :=
2382 x_formatted_phone_number || ' x' || p_phone_extension;
2383 END IF;
2384
2385
2386 IF p_phone_line_type IS NOT NULL -- Bug 4917862
2387 AND p_phone_line_type<>fnd_api.g_miss_char
2388 AND l_display_purpose = fnd_api.g_true
2389 THEN
2390 l_phone_line_meaning :=
2391 hz_utility_v2pub.get_lookupmeaning('AR_LOOKUPS', 'PHONE_LINE_TYPE', p_phone_line_type);
2392
2393 x_formatted_phone_number :=
2394 x_formatted_phone_number || ' (' || l_phone_line_meaning || ')';
2395 END IF;
2396
2397 RETURN x_formatted_phone_number;
2398
2399 END get_formatted_phone;
2400
2401
2402 END HZ_FORMAT_PHONE_V2PUB;