[Home] [Help]
PACKAGE BODY: APPS.FND_FLEX_VAL_UTIL
Source
1 PACKAGE BODY fnd_flex_val_util AS
2 /* $Header: AFFFUTVB.pls 120.3.12010000.4 2009/02/04 19:35:01 hgeorgi ship $ */
3
4
5 chr_newline VARCHAR2(8); -- := fnd_global.newline;
6
7 g_package_name VARCHAR2(50) := 'FND_FLEX_VAL_UTIL';
8
9 g_internal_debug VARCHAR2(32000);
10 g_flag_debugging BOOLEAN := FALSE;
11
12 g_message_stack_number NUMBER := 0;
13
14 -- Time zone conversion directions. see fdffds.h
15
16 g_tz_server_to_local VARCHAR2(10) := '1';
17 g_tz_local_to_server VARCHAR2(20) := '2';
18
19 g_date_format_19 VARCHAR2(80) := 'RRRR/MM/DD HH24:MI:SS';
20 g_date_format_5 VARCHAR2(80) := 'HH24:MI';
21 g_date_format_8 VARCHAR2(80) := 'HH24:MI:SS';
22 g_date_format_9 VARCHAR2(80) := 'DD-MON-RR';
23 g_date_format_11 VARCHAR2(80) := 'DD-MON-RRRR';
24 g_date_format_15 VARCHAR2(80) := 'DD-MON-RR HH24:MI';
25 g_date_format_17 VARCHAR2(80) := 'DD-MON-RRRR HH24:MI';
26 g_date_format_18 VARCHAR2(80) := 'DD-MON-RR HH24:MI:SS';
27 g_date_format_20 VARCHAR2(80) := 'DD-MON-RRRR HH24:MI:SS';
28
29 -- ==============================
30 -- Masks
31 -- ==============================
32 --
33 -- Keep room for Universal Time Stamp.
34 --
35 m_sep VARCHAR2(1) := '|'; -- mask separator.
36 m_canonical_date VARCHAR2(50) := 'RRRR/MM/DD HH24:MI:SS';
37 m_canonical_datetime VARCHAR2(50) := 'RRRR/MM/DD HH24:MI:SS';
38 m_canonical_time VARCHAR2(50) := 'HH24:MI:SS';
39 m_canonical_numeric_chars VARCHAR2(2) := '.,';
40 m_db_numeric_chars VARCHAR2(2);
41
42 --
43 -- _IN masks can have multiple values separated by '|'. Keep enough room.
44 -- _OUT masks will have only one mask.
45 --
46 m_nls_date_in VARCHAR2(500);
47 m_nls_date_out VARCHAR2(50);
48 m_nls_datetime_in VARCHAR2(500);
49 m_nls_datetime_out VARCHAR2(50);
50 m_nls_time_in VARCHAR2(500);
51 m_nls_time_out VARCHAR2(50);
52 m_nls_numeric_chars_in VARCHAR2(100);
53 m_nls_numeric_chars_out VARCHAR2(10);
54
55 m_ds_can VARCHAR2(1) := '.'; -- Canonical decimal separator.
56 m_ds_db VARCHAR2(1); -- dataBase decimal separator.
57 m_ds_disp VARCHAR2(1); -- Display decimal separator.
58
59 -- =======================================================================
60 -- Added by NGOUGLER START
61 -- =======================================================================
62 -- g2u: Gregorian to User calendar
63 -- u2g: User to Gregorian calendar
64 g_cal_g2u VARCHAR2(10) := '1';
65 g_cal_u2g VARCHAR2(10) := '2';
66
67 -- =======================================================================
68 -- Added by NGOUGLER END
69 -- =======================================================================
70
71 -- ==================================================
72 -- VTV definitions
73 -- ==================================================
74 --
75 -- vtv_rec_type : Segment qualifier definition record.
76 -- For now we don't need all the details of segment qualifier.
77 -- Only lookup_type and default_value are enough.
78 --
79 TYPE vtv_rec_type IS RECORD
80 (
81 -- id_flex_application_id NUMBER,
82 -- id_flex_code VARCHAR2(10),
83 -- segment_attribute_type VARCHAR2(100),
84 -- value_attribute_type VARCHAR2(100),
85 -- assignment_date DATE,
86 lookup_type VARCHAR2(100),
87 default_value VARCHAR2(100));
88
89 --
90 -- vtv_arr_type : Segment qualifiers for a given value set.
91 --
92 TYPE vtv_arr_type IS TABLE OF vtv_rec_type INDEX BY BINARY_INTEGER;
93
94 g_vtv_array vtv_arr_type;
95 g_vtv_array_size NUMBER := NULL;
96 g_vtv_flex_value_set_id NUMBER := 0;
97
98 -- ==================================================
99 -- Private Function Definitions.
100 -- ==================================================
101 PROCEDURE debug(p_debug IN VARCHAR2);
102
103 PROCEDURE set_message_name(p_appl_short_name IN VARCHAR2,
104 p_message_name IN VARCHAR2);
105 PROCEDURE set_message_token(p_token_name IN VARCHAR2,
106 p_token_value IN VARCHAR2);
107 PROCEDURE set_message(p_appl_short_name IN VARCHAR2,
108 p_message_name IN VARCHAR2,
109 p_num_of_tokens IN NUMBER,
110 p_token_name_1 IN VARCHAR2 DEFAULT NULL,
111 p_token_value_1 IN VARCHAR2 DEFAULT NULL,
112 p_token_name_2 IN VARCHAR2 DEFAULT NULL,
113 p_token_value_2 IN VARCHAR2 DEFAULT NULL,
114 p_token_name_3 IN VARCHAR2 DEFAULT NULL,
115 p_token_value_3 IN VARCHAR2 DEFAULT NULL,
116 p_token_name_4 IN VARCHAR2 DEFAULT NULL,
117 p_token_value_4 IN VARCHAR2 DEFAULT NULL,
118 p_token_name_5 IN VARCHAR2 DEFAULT NULL,
119 p_token_value_5 IN VARCHAR2 DEFAULT NULL);
120
121 PROCEDURE internal_init;
122 PROCEDURE init_masks;
123 PROCEDURE ssv_exception(p_func_name IN VARCHAR2,
124 p_message IN VARCHAR2 DEFAULT NULL);
125
126 PROCEDURE ssv_bad_parameter(p_func_name IN VARCHAR2,
127 p_reason IN VARCHAR2 DEFAULT NULL);
128
129 PROCEDURE get_format_private(p_vset_name IN VARCHAR2,
130 p_vset_format IN VARCHAR2,
131 p_max_length IN NUMBER,
132 p_precision IN NUMBER DEFAULT NULL,
133 x_format_in OUT NOCOPY VARCHAR2,
134 x_format_out OUT NOCOPY VARCHAR2,
135 x_canonical OUT NOCOPY VARCHAR2,
136 x_number_format OUT NOCOPY VARCHAR2,
137 x_number_min OUT NOCOPY NUMBER,
138 x_number_max OUT NOCOPY NUMBER,
139 x_success OUT NOCOPY NUMBER);
140
141 PROCEDURE validate_value_private(p_value IN VARCHAR2,
142 p_is_displayed IN BOOLEAN DEFAULT TRUE,
143 p_vset_name IN VARCHAR2 DEFAULT NULL,
144 p_vset_format IN VARCHAR2 DEFAULT 'C',
145 p_max_length IN NUMBER DEFAULT 0,
146 p_precision IN NUMBER DEFAULT NULL,
147 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
148 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
149 p_zero_fill IN VARCHAR2 DEFAULT 'N',
150 p_min_value IN VARCHAR2 DEFAULT NULL,
151 p_max_value IN VARCHAR2 DEFAULT NULL,
152 x_storage_value OUT NOCOPY VARCHAR2,
153 x_display_value OUT NOCOPY VARCHAR2,
154 x_success OUT NOCOPY NUMBER);
155
156 FUNCTION get_vtv_lookup(p_lookup_type IN VARCHAR2,
157 p_lookup_code IN VARCHAR2,
158 p_use_default IN BOOLEAN,
159 p_default_code IN VARCHAR2)
160 RETURN VARCHAR2;
161
162 -- ==================================================
163 -- Public Functions.
164 -- ==================================================
165 PROCEDURE vtv_to_display_value(p_flex_value_set_id IN NUMBER,
166 p_use_default IN BOOLEAN,
167 p_storage_value IN VARCHAR2,
168 x_display_value OUT NOCOPY VARCHAR2)
169 IS
170 i NUMBER;
171 l_use_default BOOLEAN := Nvl(p_use_default, FALSE);
172 l_storage_value VARCHAR2(32000) := p_storage_value;
173 l_display_value VARCHAR2(32000) := NULL;
174 l_lookup_code VARCHAR2(100);
175 l_lookup_value VARCHAR2(100);
176 l_pos NUMBER;
177 CURSOR vtv_cur(p_flex_value_set_id IN NUMBER) IS
178 SELECT
179 -- fvq.id_flex_application_id,
180 -- fvq.id_flex_code,
181 -- fvq.segment_attribute_type,
182 -- fvq.value_attribute_type,
183 -- fvq.assignment_date,
184 vat.lookup_type,
185 vat.default_value
186 FROM fnd_flex_validation_qualifiers fvq,
187 fnd_value_attribute_types vat
188 WHERE fvq.flex_value_set_id = p_flex_value_set_id
189 AND fvq.id_flex_application_id = vat.application_id(+)
190 AND fvq.id_flex_code = vat.id_flex_code(+)
191 AND fvq.segment_attribute_type = vat.segment_attribute_type(+)
192 AND fvq.value_attribute_type = vat.value_attribute_type(+)
193 ORDER BY fvq.flex_value_set_id, fvq.assignment_date,
194 fvq.value_attribute_type;
195 --
196 -- Note : Above ORDER BY statement is important. Qualifiers are sorted
197 -- by assignment date.
198 --
199 BEGIN
200 internal_init();
201 IF (p_flex_value_set_id <> g_vtv_flex_value_set_id) THEN
202 --
203 -- Re-populate the global cache. Either this is the first time
204 -- this procedure is called or value set is changed.
205 -- Note : initial value of g_vtv_flex_value_set_id is 0
206 -- and none of the value sets can have 0 value set id.
207 --
208 g_vtv_array_size := 0;
209 FOR vtv_rec IN vtv_cur(p_flex_value_set_id) LOOP
210 g_vtv_array_size := g_vtv_array_size + 1;
211 g_vtv_array(g_vtv_array_size).lookup_type := vtv_rec.lookup_type;
212 g_vtv_array(g_vtv_array_size).default_value := vtv_rec.default_value;
213 END LOOP;
214 g_vtv_flex_value_set_id := p_flex_value_set_id;
215
216 IF (g_flag_debugging) THEN
217 debug('VTV: New Value Set Id : ' || To_char(p_flex_value_set_id));
218 debug('VTV: VTV Array Size : ' || To_char(g_vtv_array_size));
219 END IF;
220 END IF;
221
222 IF (g_vtv_array_size = 0) THEN
223 --
224 -- There are no qualifiers for this value set.
225 --
226 IF (g_flag_debugging) THEN
227 debug('VTV: No Qualifier : ' || To_char(p_flex_value_set_id));
228 END IF;
229 GOTO lbl_return;
230 END IF;
231
232 IF ((l_storage_value IS NULL) AND
233 (NOT l_use_default)) THEN
234 IF (g_flag_debugging) THEN
235 debug('VTV: NULL value and no default. : ' || To_char(p_flex_value_set_id));
236 END IF;
237 GOTO lbl_return;
238 END IF;
239
240 --
241 -- Add extra newline at the end for parsing.
242 --
243 l_storage_value := l_storage_value || chr_newline;
244 i := 0;
245 WHILE ((l_storage_value IS NOT NULL) AND
246 (i < g_vtv_array_size)) LOOP
247 i := i + 1;
248 l_pos := Instr(l_storage_value, chr_newline, 1, 1);
249 l_lookup_code := Substr(l_storage_value, 1, l_pos - 1);
250 l_storage_value := Substr(l_storage_value, l_pos + 1);
251 l_lookup_value := get_vtv_lookup(g_vtv_array(i).lookup_type,
252 l_lookup_code,
253 l_use_default,
254 g_vtv_array(i).default_value);
255 l_display_value := l_display_value || l_lookup_value || '.';
256 END LOOP;
257 --
258 -- Fill in the remaining VTV values.
259 -- If a value is created before a Qualifier is defined,
260 -- there will be remaining values.
261 --
262 WHILE ((i < g_vtv_array_size) AND
263 (l_use_default)) LOOP
264 i := i + 1;
265 l_lookup_value := get_vtv_lookup(g_vtv_array(i).lookup_type,
266 NULL,
267 l_use_default,
268 g_vtv_array(i).default_value);
269 l_display_value := l_display_value || l_lookup_value || '.';
270 END LOOP;
271 --
272 -- Remove the last '.'.
273 --
274 x_display_value := Substr(l_display_value, 1,
275 Nvl(Lengthb(l_display_value),0)-1);
276
277 <<lbl_return>>
278 x_display_value := l_display_value;
279 RETURN;
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF (g_flag_debugging) THEN
283 debug('vtv_to_display_value() Exception : ' || Sqlerrm);
284 END IF;
285 x_display_value := NULL;
286 RETURN;
287 END vtv_to_display_value;
288
289
290
291 FUNCTION is_success(p_success IN NUMBER)
292 RETURN BOOLEAN IS
293 BEGIN
294 IF (p_success = g_ret_no_error) THEN
295 RETURN(TRUE);
296 END IF;
297 RETURN(FALSE);
298 END is_success;
299
300 -- ==================================================
301 FUNCTION get_debug RETURN VARCHAR2
302 IS
303 BEGIN
304 RETURN(g_internal_debug);
305 END get_debug;
306
307 -- ==================================================
308 PROCEDURE set_debugging(p_flag IN BOOLEAN DEFAULT TRUE)
309 IS
310 BEGIN
311 g_flag_debugging := Nvl(p_flag, FALSE);
312 IF (NOT g_flag_debugging) THEN
313 g_internal_debug := ('Debugging is turned OFF. ' || chr_newline ||
314 'Please call set_debugging(TRUE) to turn it ON.');
315 END IF;
316 END set_debugging;
317
318 -- ==================================================
319 FUNCTION get_mask(p_mask_name IN VARCHAR2,
320 x_mask_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN
321 IS
322 l_code_name VARCHAR2(100) := g_package_name || '.get_mask()';
323 l_mask_value VARCHAR2(500);
324 BEGIN
325 internal_init();
326 SELECT Decode(Upper(p_mask_name),
327 'CANONICAL_DATE', m_canonical_date,
328 'CANONICAL_DATETIME', m_canonical_datetime,
329 'CANONICAL_TIME', m_canonical_time,
330 'CANONICAL_NUMERIC_CHARS', m_canonical_numeric_chars,
331 'DB_NUMERIC_CHARS', m_db_numeric_chars,
332 'NLS_DATE_IN', m_nls_date_in,
333 'NLS_DATE_OUT', m_nls_date_out,
334 'NLS_DATETIME_IN', m_nls_datetime_in,
335 'NLS_DATETIME_OUT', m_nls_datetime_out,
336 'NLS_TIME_IN', m_nls_time_in,
337 'NLS_TIME_OUT', m_nls_time_out,
338 'NLS_NUMERIC_CHARS_IN', m_nls_numeric_chars_in,
339 'NLS_NUMERIC_CHARS_OUT', m_nls_numeric_chars_out,
340 'FLEX_UNKNOWN_MASK')
341 INTO l_mask_value
342 FROM dual;
343 x_mask_value := l_mask_value;
344 IF (l_mask_value = 'FLEX_UNKNOWN_MASK') THEN
345 ssv_bad_parameter(l_code_name, 'Unknown mask name is passed.');
346 RETURN(FALSE);
347 ELSE
348 RETURN(TRUE);
349 END IF;
350 EXCEPTION
351 WHEN OTHERS THEN
352 ssv_exception(l_code_name);
353 RETURN(FALSE);
354 END get_mask;
355
356 -- ==================================================
357 FUNCTION set_mask(p_mask_name IN VARCHAR2,
358 p_mask_value IN VARCHAR2) RETURN BOOLEAN
359 IS
360 l_code_name VARCHAR2(100) := g_package_name || '.set_mask()';
361 l_mask_name VARCHAR2(100) := Upper(p_mask_name);
362 BEGIN
363 internal_init();
364 IF ((Instr(p_mask_name, '_OUT') > 0) AND
365 (Instr(p_mask_value, m_sep) >0)) THEN
366 ssv_bad_parameter(l_code_name,
367 '_OUT masks cannot have multiple values.');
368 RETURN(FALSE);
369 END IF;
370
371 IF (l_mask_name = 'NLS_DATE_IN') THEN
372 m_nls_date_in := p_mask_value;
373 ELSIF (l_mask_name = 'NLS_DATE_OUT') THEN
374 m_nls_date_out := p_mask_value;
375
376 ELSIF (l_mask_name = 'NLS_DATETIME_IN') THEN
377 m_nls_datetime_in := p_mask_value;
378 ELSIF (l_mask_name = 'NLS_DATETIME_OUT') THEN
379 m_nls_datetime_out := p_mask_value;
380
381 ELSIF (l_mask_name = 'NLS_TIME_IN') THEN
382 m_nls_time_in := p_mask_value;
383 ELSIF (l_mask_name = 'NLS_TIME_OUT') THEN
384 m_nls_time_out := p_mask_value;
385
386 ELSIF (l_mask_name = 'NLS_NUMERIC_CHARS_IN') THEN
387 m_nls_numeric_chars_in := p_mask_value;
388 ELSIF (l_mask_name = 'NLS_NUMERIC_CHARS_OUT') THEN
389 m_nls_numeric_chars_out := p_mask_value;
390 m_ds_disp := Substr(m_nls_numeric_chars_out, 1, 1);
391 ELSE
392 ssv_bad_parameter(l_code_name, 'Unknown mask name is passed.');
393 RETURN(FALSE);
394 END IF;
395 RETURN(TRUE);
396 EXCEPTION
397 WHEN OTHERS THEN
398 ssv_exception(l_code_name);
399 RETURN(FALSE);
400 END set_mask;
401
402 -- ==================================================
403 FUNCTION get_storage_format(p_vset_format IN VARCHAR2,
404 p_max_length IN NUMBER,
405 p_precision IN NUMBER DEFAULT NULL,
406 x_format OUT NOCOPY VARCHAR2) RETURN BOOLEAN
407 IS
408 l_code_name VARCHAR2(100) := g_package_name || '.get_storage_format()';
409 l_success NUMBER;
410
411 tmp_vc1 VARCHAR2(2000);
412 tmp_vc2 VARCHAR2(2000);
413 tmp_vc3 VARCHAR2(2000);
414 tmp_number1 NUMBER;
415 tmp_number2 NUMBER;
416 BEGIN
417 internal_init();
418 --
419 -- Use tmp_ variables for unused arguments.
420 --
421 get_format_private(p_vset_name => l_code_name,
422 p_vset_format => p_vset_format,
423 p_max_length => p_max_length,
424 p_precision => p_precision,
425 x_format_in => tmp_vc1,
426 x_format_out => tmp_vc2,
427 x_canonical => x_format,
428 x_number_format => tmp_vc3,
429 x_number_min => tmp_number1,
430 x_number_max => tmp_number2,
431 x_success => l_success);
432 RETURN(is_success(l_success));
433 EXCEPTION
434 WHEN OTHERS THEN
435 ssv_exception(l_code_name);
436 RETURN(FALSE);
437 END get_storage_format;
438
439 -- ==================================================
440 FUNCTION get_display_format(p_vset_format IN VARCHAR2,
441 p_max_length IN NUMBER,
442 p_precision IN NUMBER DEFAULT NULL,
443 x_format_in OUT NOCOPY VARCHAR2,
444 x_format_out OUT NOCOPY VARCHAR2) RETURN BOOLEAN
445 IS
446 l_code_name VARCHAR2(100) := g_package_name || '.get_display_mask()';
447 l_success NUMBER;
448
449 tmp_vc1 VARCHAR2(2000);
450 tmp_vc2 VARCHAR2(2000);
451 tmp_number1 NUMBER;
452 tmp_number2 NUMBER;
453 BEGIN
454 internal_init();
455 --
456 -- Use tmp_ variables for unused arguments.
457 --
458 get_format_private(p_vset_name => l_code_name,
459 p_vset_format => p_vset_format,
460 p_max_length => p_max_length,
461 p_precision => p_precision,
462 x_format_in => x_format_in,
463 x_format_out => x_format_out,
464 x_canonical => tmp_vc1,
465 x_number_format => tmp_vc2,
466 x_number_min => tmp_number1,
467 x_number_max => tmp_number2,
468 x_success => l_success);
469 RETURN(is_success(l_success));
470 EXCEPTION
471 WHEN OTHERS THEN
472 ssv_exception(l_code_name);
473 RETURN(FALSE);
474 END get_display_format;
475
476 -- ==================================================
477 FUNCTION is_date_private(p_value IN VARCHAR2,
478 p_nls_date_format IN VARCHAR2 DEFAULT NULL,
479 x_date OUT NOCOPY DATE)
480 RETURN BOOLEAN IS
481 l_nls_date_format VARCHAR2(500) := p_nls_date_format;
482 l_mask VARCHAR2(100);
483 l_pos NUMBER;
484 BEGIN
485 IF (p_value IS NULL) THEN
486 x_date := NULL;
487 RETURN(TRUE);
488 ELSIF (l_nls_date_format IS NULL) THEN
489 RETURN(FALSE);
490 ELSE
491 l_nls_date_format := Rtrim(l_nls_date_format, m_sep) || m_sep;
492 LOOP
493 l_pos := Instr(l_nls_date_format, m_sep);
494 IF (l_pos > 0) THEN
495 l_mask := Substr(l_nls_date_format, 1, l_pos - 1);
496 l_nls_date_format := Substr(l_nls_date_format, l_pos + 1);
497 ELSE
498 l_mask := l_nls_date_format;
499 l_nls_date_format := NULL;
500 END IF;
501
502 BEGIN
503 x_date := To_date(p_value, l_mask);
504 RETURN(TRUE);
505 EXCEPTION
506 --
507 -- This mask failed, try others, if we have.
508 --
509 WHEN OTHERS THEN
510 IF (l_nls_date_format IS NULL) THEN
511 RETURN(FALSE);
512 END IF;
513 END;
514 END LOOP;
515 END IF;
516 RETURN(TRUE);
517 EXCEPTION
518 WHEN OTHERS THEN
519 RETURN(FALSE);
520 END is_date_private;
521
522 -- ==================================================
523 FUNCTION is_date(p_value IN VARCHAR2,
524 p_nls_date_format IN VARCHAR2 DEFAULT NULL,
525 x_date OUT NOCOPY DATE)
526 RETURN BOOLEAN IS
527 l_nls_date_format VARCHAR2(500) := p_nls_date_format;
528 l_mask VARCHAR2(100);
529 l_pos NUMBER;
530 BEGIN
531 internal_init();
532 RETURN(is_date_private(p_value,
533 p_nls_date_format,
534 x_date));
535 END is_date;
536
537 -- ==================================================
538 FUNCTION flex_to_date(p_value IN VARCHAR2,
539 p_nls_date_format IN VARCHAR2) RETURN DATE
540 IS
541 l_date DATE;
542 BEGIN
543 internal_init();
544 IF (is_date_private(p_value, p_nls_date_format, l_date)) THEN
545 RETURN(l_date);
546 ELSE
547 RETURN(NULL);
548 END IF;
549 EXCEPTION
550 WHEN OTHERS THEN
551 RETURN(NULL);
552 END flex_to_date;
553
554 -- ==================================================
555 --
556 -- Takes with nls_numeric_chars mask, returns in db_numeric_chars mask.
557 --
558 FUNCTION is_number_private(p_value IN VARCHAR2,
559 p_nls_numeric_chars IN VARCHAR2,
560 x_value OUT NOCOPY VARCHAR2,
561 x_number OUT NOCOPY NUMBER)
562 RETURN BOOLEAN IS
563 l_nls_numeric_chars VARCHAR2(100) := p_nls_numeric_chars;
564 l_mask VARCHAR2(10);
565 l_pos NUMBER;
566 l_value VARCHAR2(2000) := p_value;
567 BEGIN
568 IF (p_value IS NULL) THEN
569 x_value := NULL;
570 x_number := NULL;
571 RETURN(TRUE);
572 ELSIF (l_nls_numeric_chars IS NULL) THEN
573 RETURN(FALSE);
574 ELSE
575 l_nls_numeric_chars := Rtrim(l_nls_numeric_chars, m_sep) || m_sep;
576 LOOP
577 l_pos := Instr(l_nls_numeric_chars, m_sep);
578 IF (l_pos > 0) THEN
579 l_mask := Substr(l_nls_numeric_chars, 1, l_pos - 1);
580 l_nls_numeric_chars := Substr(l_nls_numeric_chars, l_pos + 1);
581 ELSE
582 l_mask := l_nls_numeric_chars;
583 l_nls_numeric_chars := NULL;
584 END IF;
585
586 BEGIN
587 --
588 -- We do not use Group Separator.
589 -- To_number will use database decimal separator.
590 --
591 l_value := REPLACE(p_value, Substr(l_mask, 1, 1), m_ds_db);
592 x_number := To_number(l_value);
593 x_value := l_value;
594 RETURN(TRUE);
595 EXCEPTION
596 --
597 -- This mask failed, try others, if we have.
598 --
599 WHEN OTHERS THEN
600 IF (l_nls_numeric_chars IS NULL) THEN
601 RETURN(FALSE);
602 END IF;
603 END;
604 END LOOP;
605 END IF;
606 RETURN(TRUE);
607 EXCEPTION
608 WHEN OTHERS THEN
609 RETURN(FALSE);
610 END is_number_private;
611
612 -- ==================================================
613 --
614 -- Takes with nls_numeric_chars mask, returns in db_numeric_chars mask.
615 --
616 FUNCTION is_number(p_value IN VARCHAR2,
617 p_nls_numeric_chars IN VARCHAR2,
618 x_value OUT NOCOPY VARCHAR2,
619 x_number OUT NOCOPY NUMBER)
620 RETURN BOOLEAN
621 IS
622 BEGIN
623 internal_init();
624 RETURN(is_number_private(p_value,
625 p_nls_numeric_chars,
626 x_value,
627 x_number));
628 END is_number;
629
630 -- ==================================================
631 FUNCTION flex_to_number(p_value IN VARCHAR2,
632 p_nls_numeric_chars IN VARCHAR2) RETURN NUMBER
633 IS
634 l_number NUMBER;
635 l_value VARCHAR2(2000);
636 BEGIN
637 internal_init();
638 IF (is_number_private(p_value, p_nls_numeric_chars, l_value, l_number)) THEN
639 RETURN(l_number);
640 ELSE
641 RETURN(NULL);
642 END IF;
643 EXCEPTION
644 WHEN OTHERS THEN
645 RETURN(NULL);
646 END flex_to_number;
647
648
649 -- ==================================================
650 PROCEDURE validate_value(p_value IN VARCHAR2,
651 p_is_displayed IN BOOLEAN DEFAULT TRUE,
652 p_vset_name IN VARCHAR2 DEFAULT NULL,
653 p_vset_format IN VARCHAR2 DEFAULT 'C',
654 p_max_length IN NUMBER DEFAULT 0,
655 p_precision IN NUMBER DEFAULT NULL,
656 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
657 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
658 p_zero_fill IN VARCHAR2 DEFAULT 'N',
659 p_min_value IN VARCHAR2 DEFAULT NULL,
660 p_max_value IN VARCHAR2 DEFAULT NULL,
661 x_storage_value OUT NOCOPY VARCHAR2,
662 x_display_value OUT NOCOPY VARCHAR2,
663 x_success OUT NOCOPY BOOLEAN)
664 IS
665 l_code_name VARCHAR2(100) := g_package_name || '.validate_value()';
666 l_success NUMBER;
667 BEGIN
668 internal_init();
669 validate_value_private(p_value => p_value,
670 p_is_displayed => p_is_displayed,
671 p_vset_name => p_vset_name,
672 p_vset_format => p_vset_format,
673 p_max_length => p_max_length,
674 p_precision => p_precision,
675 p_alpha_allowed => p_alpha_allowed,
676 p_uppercase_only => p_uppercase_only,
677 p_zero_fill => p_zero_fill,
678 p_min_value => p_min_value,
679 p_max_value => p_max_value,
680 x_storage_value => x_storage_value,
681 x_display_value => x_display_value,
682 x_success => l_success);
683 x_success := is_success(l_success);
684 EXCEPTION
685 WHEN OTHERS THEN
686 ssv_exception(l_code_name,
687 ' Value Set : ' || p_vset_name ||
688 ' Value : ' || p_value);
689 x_success := FALSE;
690 END validate_value;
691
692 -- ==================================================
693 FUNCTION is_value_valid(p_value IN VARCHAR2,
694 p_is_displayed IN BOOLEAN DEFAULT TRUE,
695 p_vset_name IN VARCHAR2 DEFAULT NULL,
696 p_vset_format IN VARCHAR2 DEFAULT 'C',
697 p_max_length IN NUMBER DEFAULT 0,
698 p_precision IN NUMBER DEFAULT NULL,
699 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
700 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
701 p_zero_fill IN VARCHAR2 DEFAULT 'N',
702 p_min_value IN VARCHAR2 DEFAULT NULL,
703 p_max_value IN VARCHAR2 DEFAULT NULL,
704 x_storage_value OUT NOCOPY VARCHAR2,
705 x_display_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN
706 IS
707 l_code_name VARCHAR2(100) := g_package_name || '.is_value_valid()';
708 l_success NUMBER;
709 BEGIN
710 internal_init();
711 validate_value_private(p_value => p_value,
712 p_is_displayed => p_is_displayed,
713 p_vset_name => p_vset_name,
714 p_vset_format => p_vset_format,
715 p_max_length => p_max_length,
716 p_precision => p_precision,
717 p_alpha_allowed => p_alpha_allowed,
718 p_uppercase_only => p_uppercase_only,
719 p_zero_fill => p_zero_fill,
720 p_min_value => p_min_value,
721 p_max_value => p_max_value,
722 x_storage_value => x_storage_value,
723 x_display_value => x_display_value,
724 x_success => l_success);
725 RETURN(is_success(l_success));
726 EXCEPTION
727 WHEN OTHERS THEN
728 ssv_exception(l_code_name,
729 ' Value Set : ' || p_vset_name ||
730 ' Value : ' || p_value);
731 RETURN(FALSE);
732 END is_value_valid;
733
734
735 -- ==================================================
736 FUNCTION to_display_value(p_value IN VARCHAR2,
737 p_vset_format IN VARCHAR2 DEFAULT 'C',
738 p_vset_name IN VARCHAR2 DEFAULT NULL,
739 p_max_length IN NUMBER DEFAULT 0,
740 p_precision IN NUMBER DEFAULT NULL,
741 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
742 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
743 p_zero_fill IN VARCHAR2 DEFAULT 'N',
744 p_min_value IN VARCHAR2 DEFAULT NULL,
745 p_max_value IN VARCHAR2 DEFAULT NULL)
746 RETURN VARCHAR2
747 IS
748 l_code_name VARCHAR2(100) := g_package_name || '.to_display_value()';
749 l_vset_name VARCHAR2(100) := Nvl(p_vset_name, l_code_name);
750 l_max_length NUMBER := Nvl(p_max_length, Lengthb(p_value));
751 l_display_value VARCHAR2(2000);
752 l_success NUMBER;
753
754 tmp_vc VARCHAR2(2000);
755 BEGIN
756 internal_init();
757 validate_value_private(p_value => p_value,
758 p_is_displayed => FALSE,
759 p_vset_name => l_vset_name,
760 p_vset_format => p_vset_format,
761 p_max_length => l_max_length,
762 p_precision => p_precision,
763 p_alpha_allowed => p_alpha_allowed,
764 p_uppercase_only => p_uppercase_only,
765 p_zero_fill => p_zero_fill,
766 p_min_value => p_min_value,
767 p_max_value => p_max_value,
768 x_storage_value => tmp_vc,
769 x_display_value => l_display_value,
770 x_success => l_success);
771 IF (l_success = g_ret_no_error) THEN
772 RETURN(l_display_value);
773 ELSE
774 RETURN(NULL);
775 END IF;
776 EXCEPTION
777 WHEN OTHERS THEN
778 ssv_exception(l_code_name);
779 RETURN(NULL);
780 END to_display_value;
781
782 -- ==================================================
783 FUNCTION to_storage_value(p_value IN VARCHAR2,
784 p_vset_format IN VARCHAR2 DEFAULT 'C',
785 p_vset_name IN VARCHAR2 DEFAULT NULL,
786 p_max_length IN NUMBER DEFAULT 0,
787 p_precision IN NUMBER DEFAULT NULL,
788 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
789 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
790 p_zero_fill IN VARCHAR2 DEFAULT 'N',
791 p_min_value IN VARCHAR2 DEFAULT NULL,
792 p_max_value IN VARCHAR2 DEFAULT NULL)
793 RETURN VARCHAR2
794 IS
795 l_code_name VARCHAR2(100) := g_package_name || '.to_storage_value()';
796 l_vset_name VARCHAR2(100) := Nvl(p_vset_name, l_code_name);
797 l_max_length NUMBER := Nvl(p_max_length, Lengthb(p_value));
798 l_storage_value VARCHAR2(2000);
799 l_success NUMBER;
800
801 tmp_vc VARCHAR2(2000);
802
803 BEGIN
804 internal_init();
805 validate_value_private(p_value => p_value,
806 p_is_displayed => TRUE,
807 p_vset_name => l_vset_name,
808 p_vset_format => p_vset_format,
809 p_max_length => l_max_length,
810 p_precision => p_precision,
811 p_alpha_allowed => p_alpha_allowed,
812 p_uppercase_only => p_uppercase_only,
813 p_zero_fill => p_zero_fill,
814 p_min_value => p_min_value,
815 p_max_value => p_max_value,
816 x_storage_value => l_storage_value,
817 x_display_value => tmp_vc,
818 x_success => l_success);
819 IF (l_success = g_ret_no_error) THEN
820 RETURN(l_storage_value);
821 ELSE
822 RETURN(NULL);
823 END IF;
824 EXCEPTION
825 WHEN OTHERS THEN
826 ssv_exception(l_code_name);
827 RETURN(NULL);
828 END to_storage_value;
829
830 -- ==================================================
831 PROCEDURE validate_value_ssv(p_value IN VARCHAR2,
832 p_is_displayed IN BOOLEAN DEFAULT TRUE,
833 p_vset_name IN VARCHAR2 DEFAULT NULL,
834 p_vset_format IN VARCHAR2 DEFAULT 'C',
835 p_max_length IN NUMBER DEFAULT 0,
836 p_precision IN NUMBER DEFAULT NULL,
837 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
838 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
839 p_zero_fill IN VARCHAR2 DEFAULT 'N',
840 p_min_value IN VARCHAR2 DEFAULT NULL,
841 p_max_value IN VARCHAR2 DEFAULT NULL,
842 x_storage_value OUT NOCOPY VARCHAR2,
843 x_display_value OUT NOCOPY VARCHAR2,
844 x_success OUT NOCOPY NUMBER)
845 IS
846 l_code_name VARCHAR2(100) := g_package_name || '.validate_value_ssv()';
847 l_success NUMBER;
848 BEGIN
849 internal_init();
850 validate_value_private(p_value => p_value,
851 p_is_displayed => p_is_displayed,
852 p_vset_name => p_vset_name,
853 p_vset_format => p_vset_format,
854 p_max_length => p_max_length,
855 p_precision => p_precision,
856 p_alpha_allowed => p_alpha_allowed,
857 p_uppercase_only => p_uppercase_only,
858 p_zero_fill => p_zero_fill,
859 p_min_value => p_min_value,
860 p_max_value => p_max_value,
861 x_storage_value => x_storage_value,
862 x_display_value => x_display_value,
863 x_success => l_success);
864 x_success := l_success;
865 EXCEPTION
866 WHEN OTHERS THEN
867 ssv_exception(l_code_name,
868 ' Value Set : ' || p_vset_name ||
869 ' Value : ' || p_value);
870 x_success := g_ret_exception_others;
871 END validate_value_ssv;
872
873 -- ==================================================
874 -- Since client is using auvc1998 for varchar2 types,
875 -- Substr(X,1,1998) will be used for out varchar2's.
876 --
877 PROCEDURE get_server_global(p_char_in IN VARCHAR2,
878 x_char_out OUT NOCOPY VARCHAR2,
879 x_error OUT NOCOPY NUMBER,
880 x_message OUT NOCOPY VARCHAR2)
881 IS
882 l_code_name VARCHAR2(100) := (g_package_name ||
883 '.get_server_global()');
884 l_char_out VARCHAR2(32000);
885 l_plsql VARCHAR2(32000);
886 l_char_in VARCHAR2(2000) := Upper(p_char_in);
887 BEGIN
888 internal_init();
889 IF (l_char_in = 'FND_DATE.USER_MASK') THEN
890 l_char_out := fnd_date.user_mask;
891 ELSIF (l_char_in = 'FND_DATE.USERDT_MASK') THEN
892 l_char_out := fnd_date.userdt_mask;
893 ELSIF (l_char_in = 'FND_NUMBER.DECIMAL_CHAR') THEN
894 l_char_out := fnd_number.decimal_char;
895 ELSE
896 l_plsql := 'BEGIN :l_char_out := ' || p_char_in || '; END;';
897 EXECUTE IMMEDIATE l_plsql USING OUT l_char_out;
898 END IF;
899 IF (l_char_out IS NULL) THEN
900 x_error := -1;
901 x_message := 'NULL mask value for ' || l_char_in ||' in '|| l_code_name;
902 x_char_out := NULL;
903 RETURN;
904 END IF;
905
906 x_char_out := Substrb(l_char_out,1,1998);
907 x_error := 0;
908 x_message := NULL;
909 EXCEPTION
910 WHEN OTHERS THEN
911 --
912 -- ssv_exception will put SQLERRM in stack.
913 -- Store SQLCODE in x_error.
914 --
915 ssv_exception(l_code_name, 'p_char_in : ' || p_char_in);
916 x_char_out := NULL;
917 x_error := SQLCODE;
918 --
919 -- Hope encoded message will not be longer than 1998.
920 --
921 x_message := Substr(fnd_message.get_encoded,1,1998);
922 END get_server_global;
923
924 -- ==================================================
925 PROCEDURE flex_date_converter(p_vs_format_type IN VARCHAR2,
926 p_tz_direction IN VARCHAR2,
927 p_input_mask IN VARCHAR2,
928 p_input IN VARCHAR2,
929 p_output_mask IN VARCHAR2,
930 x_output OUT NOCOPY VARCHAR2,
931 x_error OUT NOCOPY NUMBER,
932 x_message OUT NOCOPY VARCHAR2)
933 IS
934 l_code_name VARCHAR2(100) := (g_package_name ||
935 '.flex_date_converter()');
936 l_input VARCHAR2(2000);
937 l_input_date DATE;
938 l_output VARCHAR2(2000);
939 l_output_date DATE;
940 BEGIN
941 internal_init();
942
943 l_input_date := To_date(p_input, p_input_mask);
944
945 IF (p_vs_format_type = 'Y') THEN
946 --
947 -- Standard Date Time Value Set.
948 --
949 IF (p_tz_direction = g_tz_server_to_local) THEN
950 --
951 -- Server to Local Time Zone conversion.
952 -- fnd_date.date_to_displayDT() returns in fnd_date.outputDT_mask.
953 --
954 l_output := fnd_date.date_to_displayDT(l_input_date);
955 l_output_date := To_date(l_output, fnd_date.outputDT_mask);
956
957 ELSIF (p_tz_direction = g_tz_local_to_server) THEN
958 --
959 -- Local to Server Time Zone conversion.
960 -- fnd_date.displayDT_to_date() expects in fnd_date.userDT_mask.
961 --
962 l_input := To_char(l_input_date, fnd_date.userDT_mask);
963 l_output_date := fnd_date.displayDT_to_date(l_input);
964
965 ELSE
966 ssv_exception(l_code_name, ('Invalid p_tz_direction: ' ||
967 p_tz_direction));
968 x_error := 1;
969 GOTO return_failure;
970 END IF;
971
972 ELSIF (p_vs_format_type IN ('D', 'T', 't', 'I', 'X', 'Z')) THEN
973 --
974 -- other DATE value sets.
975 --
976 l_output_date := l_input_date;
977
978 ELSE
979 ssv_exception(l_code_name, ('Invalid p_vs_format_type: ' ||
980 p_vs_format_type));
981 x_error := 2;
982 GOTO return_failure;
983 END IF;
984
985 <<return_success>>
986 x_error := 0;
987 l_output := To_char(l_output_date, p_output_mask);
988 x_message := ('DEBUG: p_vs_format_type: ' || p_vs_format_type ||
989 ', p_tz_direction: ' || p_tz_direction ||
990 ', p_input: ' || p_input ||
991 ', p_input_mask: ' || p_input_mask ||
992 ', l_otput: ' || l_output ||
993 ', p_output_mask: ' || p_output_mask ||
994 ', fnd_date.outputDT_mask: ' || fnd_date.outputDT_mask ||
995 ', fnd_date.userDT_mask: ' || fnd_date.userDT_mask);
996 x_output := l_output;
997 RETURN;
998
999 <<return_failure>>
1000 x_output := NULL;
1001 x_message := Substrb(fnd_message.get_encoded, 1, 1998);
1002 RETURN;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 x_output := NULL;
1006 x_error := SQLCODE;
1007 ssv_exception(l_code_name, ('p_vs_format_type: ' || p_vs_format_type ||
1008 ', p_tz_direction: ' || p_tz_direction ||
1009 ', p_input: ' || p_input ||
1010 ', p_input_mask: ' || p_input_mask ||
1011 ', p_output_mask: ' || p_output_mask ||
1012 ', fnd_date.outputDT_mask: ' || fnd_date.outputDT_mask ||
1013 ', fnd_date.userDT_mask: ' || fnd_date.userDT_mask));
1014
1015 x_message := Substrb(fnd_message.get_encoded,1,1998);
1016 END flex_date_converter;
1017
1018 -- =======================================================================
1019 -- Added by NGOUGLER START
1020 -- =======================================================================
1021 -- ==================================================
1022 PROCEDURE flex_date_converter_cal(p_vs_format_type IN VARCHAR2,
1023 p_tz_direction IN VARCHAR2,
1024 p_cal_direction IN VARCHAR2,
1025 p_mask IN VARCHAR2,
1026 p_calendar IN VARCHAR2,
1027 p_input IN VARCHAR2,
1028 x_output OUT NOCOPY VARCHAR2,
1029 x_error OUT NOCOPY NUMBER,
1030 x_message OUT NOCOPY VARCHAR2)
1031 IS
1032 l_code_name VARCHAR2(100) := (g_package_name ||
1033 '.flex_date_converter_cal()');
1034 l_input VARCHAR2(2000);
1035 l_input_date DATE;
1036 l_output VARCHAR2(2000);
1037 l_output_date DATE;
1038
1039 l_calendar_param VARCHAR2(100) := 'NLS_CALENDAR=''' || p_calendar || '''' ;
1040
1041 BEGIN
1042 internal_init();
1043
1044 -- l_input_date := To_date(p_input, p_input_mask); --- Original
1045 --
1046 -- Calendar conversion
1047 -- g2u: Assumes the input date value as Greogrian calendar date
1048 -- u2g: Assumes the input date value as User calendar date
1049 --
1050 IF (p_cal_direction = g_cal_g2u) THEN
1051 l_input_date := To_date(p_input, p_mask);
1052 ELSIF (p_cal_direction = g_cal_u2g) THEN
1053 l_input_date := To_date(p_input, p_mask, l_calendar_param);
1054 END IF;
1055
1056 -- === IF (p_vs_format_type = 'Y') THEN
1057 --
1058 -- Standard Date Time Value Set.
1059 --
1060 -- === IF (p_tz_direction = g_tz_server_to_local) THEN
1061 --
1062 -- Server to Local Time Zone conversion.
1063 -- fnd_date.date_to_displayDT() returns in fnd_date.outputDT_mask.
1064 --
1065 -- === l_output := fnd_date.date_to_displayDT(l_input_date);
1066 -- === l_output_date := To_date(l_output, fnd_date.outputDT_mask);
1067
1068 -- === ELSIF (p_tz_direction = g_tz_local_to_server) THEN
1069 --
1070 -- Local to Server Time Zone conversion.
1071 -- fnd_date.displayDT_to_date() expects in fnd_date.userDT_mask.
1072 --
1073 -- === l_input := To_char(l_input_date, fnd_date.userDT_mask);
1074 -- === l_output_date := fnd_date.displayDT_to_date(l_input);
1075
1076 -- === ELSE
1077 -- === ssv_exception(l_code_name, ('Invalid p_tz_direction: ' ||
1078 -- === p_tz_direction));
1079 -- === x_error := 1;
1080 -- === GOTO return_failure;
1081 -- === END IF;
1082
1083 IF (p_vs_format_type IN ('D', 'T', 't', 'I', 'X', 'Y', 'Z')) THEN
1084 --
1085 -- other DATE value sets.
1086 --
1087 l_output_date := l_input_date;
1088
1089 ELSE
1090 ssv_exception(l_code_name, ('Invalid p_vs_format_type: ' ||
1091 p_vs_format_type));
1092 x_error := 2;
1093 GOTO return_failure;
1094 END IF;
1095
1096 <<return_success>>
1097 x_error := 0;
1098 -- l_output := To_char(l_output_date, p_output_mask); --- ORIGINAL
1099 --
1100 -- Calendar conversion
1101 -- g2u: Returns the output date value as User calendar date
1102 -- u2g: Returns the output date value as Gregorian calendar date
1103 --
1104 IF (p_cal_direction = g_cal_g2u) THEN
1105 l_output := To_char(l_output_date, p_mask, l_calendar_param);
1106 ELSIF (p_cal_direction = g_cal_u2g) THEN
1107 l_output := To_char(l_output_date, p_mask);
1108 END IF;
1109
1110 x_message := ('DEBUG: p_vs_format_type: ' || p_vs_format_type ||
1111 ', p_tz_direction: ' || p_tz_direction ||
1112 ', p_cal_direction: ' || p_cal_direction ||
1113 ', p_input: ' || p_input ||
1114 ', p_mask: ' || p_mask ||
1115 ', l_output: ' || l_output ||
1116 ', p_calendar: ' || p_calendar ||
1117 ', fnd_date.outputDT_mask: ' || fnd_date.outputDT_mask ||
1118 ', fnd_date.userDT_mask: ' || fnd_date.userDT_mask);
1119 x_output := l_output;
1120 RETURN;
1121
1122 <<return_failure>>
1123 x_output := NULL;
1124 x_message := Substrb(fnd_message.get_encoded, 1, 1998);
1125 RETURN;
1126 EXCEPTION
1127 WHEN OTHERS THEN
1128 x_output := NULL;
1129 x_error := SQLCODE;
1130 ssv_exception(l_code_name, ('p_vs_format_type: ' || p_vs_format_type ||
1131 ', p_tz_direction: ' || p_tz_direction ||
1132 ', p_cal_direction: ' || p_cal_direction ||
1133 ', p_input: ' || p_input ||
1134 ', p_mask: ' || p_mask ||
1135 ', p_calendar: ' || p_calendar ||
1136 ', fnd_date.outputDT_mask: ' || fnd_date.outputDT_mask ||
1137 ', fnd_date.userDT_mask: ' || fnd_date.userDT_mask));
1138
1139 x_message := Substrb(fnd_message.get_encoded,1,1998);
1140 END flex_date_converter_cal;
1141 -- =======================================================================
1142 -- Added by NGOUGLER END
1143 -- =======================================================================
1144
1145
1146 -- ======================================================================
1147 -- Private Functions
1148 -- ======================================================================
1149 -- --------------------------------------------------
1150 FUNCTION get_vtv_lookup(p_lookup_type IN VARCHAR2,
1151 p_lookup_code IN VARCHAR2,
1152 p_use_default IN BOOLEAN,
1153 p_default_code IN VARCHAR2)
1154 RETURN VARCHAR2 IS
1155 l_lookup_value VARCHAR2(100) := NULL;
1156 CURSOR lookup_cur(p_lookup_type IN VARCHAR2,
1157 p_lookup_code IN VARCHAR2) IS
1158 SELECT meaning
1159 FROM fnd_lookups
1160 WHERE lookup_type = p_lookup_type
1161 AND lookup_code = p_lookup_code
1162 AND ROWNUM = 1;
1163 BEGIN
1164 --
1165 -- This loop will run 0 or 1 time because of ROWNUM = 1.
1166 -- OPEN and FETCH is not used since FOR handles
1167 -- 'no rows found' exception.
1168 --
1169 FOR lookup_rec IN lookup_cur(p_lookup_type, p_lookup_code) LOOP
1170 l_lookup_value := lookup_rec.meaning;
1171 END LOOP;
1172 --
1173 -- If lookup is not found and default can be used try
1174 -- default code. This happens if p_lookup_code is NULL
1175 -- or it is an invalid value.
1176 --
1177 IF ((l_lookup_value IS NULL) AND
1178 (Nvl(p_use_default, FALSE))) THEN
1179 FOR lookup_rec IN lookup_cur(p_lookup_type, p_default_code) LOOP
1180 l_lookup_value := lookup_rec.meaning;
1181 END LOOP;
1182 END IF;
1183 RETURN(l_lookup_value);
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 IF (g_flag_debugging) THEN
1187 debug('get_vtv_lookup() Exception : ' || Sqlerrm);
1188 END IF;
1189 RETURN(NULL);
1190 END get_vtv_lookup;
1191
1192 -- --------------------------------------------------
1193 PROCEDURE validate_value_private
1194 (p_value IN VARCHAR2,
1195 p_is_displayed IN BOOLEAN DEFAULT TRUE,
1196 p_vset_name IN VARCHAR2 DEFAULT NULL,
1197 p_vset_format IN VARCHAR2 DEFAULT 'C',
1198 p_max_length IN NUMBER DEFAULT 0,
1199 p_precision IN NUMBER DEFAULT NULL,
1200 p_alpha_allowed IN VARCHAR2 DEFAULT 'Y',
1201 p_uppercase_only IN VARCHAR2 DEFAULT 'N',
1202 p_zero_fill IN VARCHAR2 DEFAULT 'N',
1203 p_min_value IN VARCHAR2 DEFAULT NULL,
1204 p_max_value IN VARCHAR2 DEFAULT NULL,
1205 x_storage_value OUT NOCOPY VARCHAR2,
1206 x_display_value OUT NOCOPY VARCHAR2,
1207 x_success OUT NOCOPY NUMBER)
1208 IS
1209 l_code_name VARCHAR2(100) := (g_package_name ||
1210 '.validate_value_private()');
1211
1212 l_value VARCHAR2(2000) := p_value;
1213 l_lengthb NUMBER;
1214 l_is_displayed BOOLEAN := Nvl(p_is_displayed, TRUE);
1215 l_storage_value VARCHAR2(2000) := p_value;
1216 l_display_value VARCHAR2(2000) := p_value;
1217 --
1218 -- These min, and max display values are used for error reporting.
1219 --
1220 l_min_display_value VARCHAR2(2000) := p_min_value;
1221 l_max_display_value VARCHAR2(2000) := p_max_value;
1222
1223 l_is_in_range BOOLEAN := TRUE;
1224 l_is_too_long BOOLEAN := FALSE;
1225 l_is_number BOOLEAN := FALSE;
1226
1227 --
1228 -- Several Format Masks.
1229 --
1230 l_nls_numeric_chars VARCHAR2(100);
1231
1232 l_date_format VARCHAR2(500) := 'NO NEED TO SET';
1233 l_number_format VARCHAR2(500) := 'NO NEED TO SET';
1234 l_format_in VARCHAR2(500) := 'NO NEED TO SET';
1235 l_format_out VARCHAR2(100) := 'NO NEED TO SET';
1236 l_canonical VARCHAR2(100) := 'NO NEED TO SET';
1237
1238 l_success NUMBER := g_ret_no_error;
1239
1240 --
1241 -- Temporary variables.
1242 --
1243 tmp_success NUMBER;
1244 tmp_varchar2 VARCHAR2(2000);
1245 tmp_number NUMBER;
1246 tmp_date DATE;
1247 tmp_min_vc2 VARCHAR2(2000);
1248 tmp_max_vc2 VARCHAR2(2000);
1249 tmp_min_number NUMBER;
1250 tmp_max_number NUMBER;
1251 tmp_min_date DATE;
1252 tmp_max_date DATE;
1253
1254 BEGIN
1255 --
1256 -- NULL is always a valid value.
1257 --
1258 IF (l_value IS NULL) THEN
1259 GOTO lbl_return;
1260 END IF;
1261 --
1262 -- If displayed, no space around the value.
1263 --
1264 IF (l_is_displayed) THEN
1265 l_value := Ltrim(Rtrim(l_value));
1266 END IF;
1267
1268 --
1269 -- NLS_NUMERIC_CHARACTERS
1270 --
1271 IF (l_is_displayed) THEN
1272 l_nls_numeric_chars := m_nls_numeric_chars_in;
1273 ELSE
1274 l_nls_numeric_chars := m_canonical_numeric_chars;
1275 END IF;
1276
1277 --
1278 -- Lengths are in bytes.
1279 --
1280 l_lengthb := Lengthb(l_value);
1281
1282 --
1283 -- Check length.
1284 -- p_max_length doesn't work for X, Y, and Z in STORAGE mode.
1285 -- Date type value sets will be checked in format check.
1286 --
1287 IF (NOT l_is_displayed) THEN
1288 IF (p_vset_format IN ('X', 'Y', 'Z')) THEN
1289 IF (p_vset_format = 'X') THEN
1290 tmp_varchar2 := m_canonical_date;
1291 ELSIF (p_vset_format = 'Y') THEN
1292 tmp_varchar2 := m_canonical_datetime;
1293 ELSIF (p_vset_format = 'Z') THEN
1294 tmp_varchar2 := m_canonical_time;
1295 END IF;
1296 IF (l_lengthb > Lengthb(To_char(Sysdate, tmp_varchar2))) THEN
1297 l_is_too_long := TRUE;
1298 END IF;
1299 ELSE
1300 --
1301 -- We are calling this function for None validated value sets in
1302 -- Id mode too. This means the storage validation for them, and we
1303 -- should check the lengthb.
1304 --
1305 IF (l_lengthb > p_max_length) THEN
1306 l_is_too_long := TRUE;
1307 END IF;
1308 END IF;
1309 ELSE
1310 IF (l_lengthb > p_max_length) THEN
1311 l_is_too_long := TRUE;
1312 END IF;
1313 END IF;
1314
1315 IF (l_is_too_long) THEN
1316 set_message('FND', 'FLEX-VALUE TOO LONG', 2,
1317 'VALUE', p_value,
1318 'LENGTH', To_char(p_max_length));
1319 l_success := g_ret_value_too_long;
1320 GOTO lbl_return;
1321 END IF;
1322
1323 IF (p_vset_format = 'C') THEN
1324 --
1325 -- Char Format.
1326 --
1327 l_is_number := is_number_private(l_value, l_nls_numeric_chars,
1328 tmp_varchar2, tmp_number);
1329
1330 IF (p_alpha_allowed = 'N') THEN
1331 --
1332 -- Numbers Only.
1333 --
1334 IF (NOT l_is_number) THEN
1335 set_message('FND', 'FLEX-INVALID NUMBER', 1,
1336 'NUMBER', l_value);
1337 l_success := g_ret_invalid_number;
1338 GOTO lbl_return;
1339 END IF;
1340 --
1341 -- Now in DB format.
1342 --
1343 l_value := tmp_varchar2;
1344 END IF;
1345
1346 IF ((p_zero_fill = 'Y') AND
1347 (l_is_number)) THEN
1348 --
1349 -- Right Justify Zero Fill is enabled.
1350 --
1351 l_value := Nvl(Ltrim(l_value, '0'),'0');
1352
1353 IF (tmp_number < 0) THEN
1354 --
1355 -- First character is '-'.
1356 --
1357 l_value := Substr(l_value,2);
1358 l_value := Lpad(l_value, p_max_length-1, '0');
1359 l_value := '-' || l_value;
1360 ELSE
1361 l_value := Lpad(l_value, p_max_length, '0');
1362 END IF;
1363 END IF;
1364
1365 IF (p_uppercase_only = 'Y') THEN
1366 l_value := Upper(l_value);
1367 END IF;
1368
1369 l_display_value := l_value;
1370 l_storage_value := l_value;
1371 IF (p_alpha_allowed = 'N') THEN
1372 --
1373 -- Numbers Only.
1374 --
1375 l_display_value := REPLACE(l_value, m_ds_db, m_ds_disp);
1376 l_storage_value := REPLACE(l_value, m_ds_db, m_ds_can);
1377 END IF;
1378
1379 --
1380 -- Range Check.
1381 --
1382 IF (p_alpha_allowed = 'N') THEN
1383 --
1384 -- Numbers Only.
1385 --
1386 -- p_min_value and p_max_value must be in canonical format.
1387 --
1388 IF (is_number_private(p_min_value, m_canonical_numeric_chars,
1389 tmp_min_vc2, tmp_min_number) AND
1390 is_number_private(p_max_value, m_canonical_numeric_chars,
1391 tmp_max_vc2, tmp_max_number)) THEN
1392 IF (tmp_number < Nvl(tmp_min_number, tmp_number) OR
1393 tmp_number > Nvl(tmp_max_number, tmp_number)) THEN
1394 l_is_in_range := FALSE;
1395 l_min_display_value := REPLACE(p_min_value, m_ds_can, m_ds_disp);
1396 l_max_display_value := REPLACE(p_max_value, m_ds_can, m_ds_disp);
1397 END IF;
1398 ELSE
1399 set_message('FND', 'FLEX-VS BAD NUMRANGE', 1,
1400 'VSNAME', p_vset_name);
1401 l_success := g_ret_vs_bad_numrange;
1402 GOTO lbl_return;
1403 END IF;
1404 ELSE
1405 IF (l_storage_value < Nvl(p_min_value, l_storage_value) OR
1406 l_storage_value > Nvl(p_max_value, l_storage_value)) THEN
1407 l_is_in_range := FALSE;
1408 l_min_display_value := p_min_value;
1409 l_max_display_value := p_max_value;
1410 END IF;
1411 END IF;
1412
1413 ELSIF (p_vset_format = 'N') THEN
1414 --
1415 -- Number Format.
1416 --
1417 IF (NOT is_number_private(l_value, l_nls_numeric_chars,
1418 tmp_varchar2, tmp_number)) THEN
1419 set_message('FND', 'FLEX-INVALID NUMBER', 1,
1420 'NUMBER', l_value);
1421 l_success := g_ret_invalid_number;
1422 GOTO lbl_return;
1423 END IF;
1424
1425 get_format_private(p_vset_name => p_vset_name,
1426 p_vset_format => p_vset_format,
1427 p_max_length => p_max_length,
1428 p_precision => p_precision,
1429 x_format_in => l_format_in,
1430 x_format_out => l_format_out,
1431 x_canonical => l_canonical,
1432 x_number_format => l_number_format,
1433 x_number_min => tmp_min_number,
1434 x_number_max => tmp_max_number,
1435 x_success => tmp_success);
1436
1437 IF (NOT is_success(tmp_success)) THEN
1438 l_success := tmp_success;
1439 GOTO lbl_return;
1440 END IF;
1441
1442 --
1443 -- Check for universal limits.
1444 --
1445 IF ((tmp_number < tmp_min_number) OR
1446 (tmp_number > tmp_max_number)) THEN
1447 set_message('FND', 'FLEX-INVALID NUMBER', 1,
1448 'NUMBER', l_value);
1449 l_success := g_ret_invalid_number;
1450 GOTO lbl_return;
1451 END IF;
1452
1453 IF (p_precision IS NULL) THEN
1454 l_value := Ltrim(To_char(tmp_number));
1455 --
1456 -- To_char(0.45) returns '.45', make it '0.45'
1457 -- To_char(-0.45) returns '-.45', make it '-0.45'
1458 --
1459 IF (tmp_number > 0) THEN
1460 IF (Substr(l_value,1,1) = m_ds_db) THEN
1461 l_value := '0' || l_value;
1462 END IF;
1463 ELSIF (tmp_number < 0) THEN
1464 IF (Substr(l_value,2,1) = m_ds_db) THEN
1465 l_value := '-0' || Substr(l_value,2);
1466 END IF;
1467 END IF;
1468 l_value := Substr(l_value,1,p_max_length);
1469 ELSE
1470 l_value := Ltrim(To_char(tmp_number, l_number_format));
1471 END IF;
1472
1473 l_display_value := REPLACE(l_value, m_ds_db, m_ds_disp);
1474 l_storage_value := REPLACE(l_value, m_ds_db, m_ds_can);
1475
1476 --
1477 -- Range Check. p_min_value, and p_max_value must be in canonical format.
1478 --
1479 IF (is_number_private(p_min_value, m_canonical_numeric_chars,
1480 tmp_min_vc2, tmp_min_number) AND
1481 is_number_private(p_max_value, m_canonical_numeric_chars,
1482 tmp_max_vc2, tmp_max_number)) THEN
1483 IF (tmp_number < Nvl(tmp_min_number, tmp_number) OR
1484 tmp_number > Nvl(tmp_max_number, tmp_number)) THEN
1485 l_is_in_range := FALSE;
1486 l_min_display_value := REPLACE(p_min_value, m_ds_can, m_ds_disp);
1487 l_max_display_value := REPLACE(p_max_value, m_ds_can, m_ds_disp);
1488 END IF;
1489 ELSE
1490 set_message('FND', 'FLEX-VS BAD NUMRANGE', 1,
1491 'VSNAME', p_vset_name);
1492 l_success := g_ret_vs_bad_numrange;
1493 GOTO lbl_return;
1494 END IF;
1495
1496 ELSIF (p_vset_format IN ('D', 'T', 't', 'I', 'X', 'Y', 'Z')) THEN
1497 --
1498 -- All kind of date formats.
1499 --
1500 -- D = Date format. Sizes 9 or 11
1501 -- T = DateTime format. Sizes 15, 17, 18 or 20.
1502 -- I or t = Time format. Sizes 5 or 8
1503 -- X = Translatable date format
1504 -- Y = Translatable datetime format
1505 -- Z = Translatable time format
1506 --
1507
1508 get_format_private(p_vset_name => p_vset_name,
1509 p_vset_format => p_vset_format,
1510 p_max_length => p_max_length,
1511 p_precision => p_precision,
1512 x_format_in => l_format_in,
1513 x_format_out => l_format_out,
1514 x_canonical => l_canonical,
1515 x_number_format => tmp_varchar2,
1516 x_number_min => tmp_min_number,
1517 x_number_max => tmp_max_number,
1518 x_success => tmp_success);
1519 IF (NOT is_success(tmp_success)) THEN
1520 l_success := tmp_success;
1521 GOTO lbl_return;
1522 END IF;
1523
1524 IF (l_is_displayed) THEN
1525 l_date_format := l_format_in;
1526 ELSE
1527 l_date_format := l_canonical;
1528 END IF;
1529
1530 IF (NOT is_date_private(l_value, l_date_format, tmp_date)) THEN
1531 set_message('FND', 'FLEX-INVALID DATE', 2,
1532 'DATE', l_value,
1533 'FORMAT', l_date_format);
1534 l_success := g_ret_invalid_date;
1535 GOTO lbl_return;
1536 END IF;
1537
1538 l_display_value := To_char(tmp_date, l_format_out);
1539 l_storage_value := To_char(tmp_date, l_canonical);
1540
1541 --
1542 -- Range Check. p_min_value, and p_max_value must be in canonical format.
1543 --
1544 IF (is_date_private(p_min_value, l_canonical, tmp_min_date) AND
1545 is_date_private(p_max_value, l_canonical, tmp_max_date)) THEN
1546 IF (tmp_date < Nvl(tmp_min_date, tmp_date) OR
1547 tmp_date > Nvl(tmp_max_date, tmp_date)) THEN
1548 l_is_in_range := FALSE;
1549 l_min_display_value := To_char(tmp_min_date,l_format_out);
1550 l_max_display_value := To_char(tmp_max_date,l_format_out);
1551 END IF;
1552 ELSE
1553 set_message('FND', 'FLEX-VS BAD DATERANGE', 1,
1554 'VSNAME', p_vset_name);
1555 l_success := g_ret_vs_bad_daterange;
1556 GOTO lbl_return;
1557 END IF;
1558
1559 --
1560 -- Invalid Value Set Format.
1561 --
1562 ELSE
1563 set_message('FND', 'FLEX-VS BAD FORMAT', 2,
1564 'VSNAME', p_vset_name,
1565 'FMT', p_vset_format);
1566 l_success := g_ret_vs_bad_format;
1567 GOTO lbl_return;
1568 END IF;
1569
1570 --
1571 -- Min <= Value <= Max Range Check:
1572 --
1573 IF (NOT l_is_in_range) THEN
1574 set_message('FND', 'FLEX-VAL OUT OF RANGE', 3,
1575 'VALUE', l_display_value,
1576 'MINVALUE', l_min_display_value,
1577 'MAXVALUE', l_max_display_value);
1578 l_success := g_ret_val_out_of_range;
1579 GOTO lbl_return;
1580 END IF;
1581
1582 <<lbl_return>>
1583
1584 x_display_value := l_display_value;
1585 x_storage_value := l_storage_value;
1586 x_success := l_success;
1587
1588 IF (g_flag_debugging) THEN
1589 IF (p_vset_format = 'N' OR
1590 p_vset_format = 'C' AND p_alpha_allowed = 'N') THEN
1591 debug('l_nls_numeric_chars: ' || l_nls_numeric_chars);
1592 debug('l_number_format : ' || l_number_format);
1593 debug('l_format_in/out : ' || l_format_in || '-/-' || l_format_out);
1594 debug('m_db_numeric_chars : ' || m_db_numeric_chars);
1595 debug('m_nls_numeric_chars_in/out: ' || (m_nls_numeric_chars_in ||'-/-' ||
1596 m_nls_numeric_chars_out));
1597 ELSIF (p_vset_format IN ('D', 'T', 't', 'I', 'X', 'Y', 'Z')) THEN
1598 debug('l_canonical : ' || l_canonical);
1599 debug('l_format_in/out : ' || l_format_in || '-/-' || l_format_out);
1600 debug('l_date_format : ' || l_date_format);
1601 debug('m_nls_date_in/out : ' || m_nls_date_in || '-/-' || m_nls_date_out);
1602 debug('m_nls_datetimet_in/out:' || (m_nls_datetime_in || '-/-' ||
1603 m_nls_datetime_out));
1604 debug('m_nls_time_in/out : ' || (m_nls_time_in || '-/-' ||
1605 m_nls_time_out));
1606 END IF;
1607 debug('Value Disp/Stor : ' || l_display_value || '/' || l_storage_value);
1608 debug('Success Code : ' || To_char(l_success));
1609 END IF;
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 ssv_exception(l_code_name,
1613 ' Value Set : ' || p_vset_name ||
1614 ' Value : ' || p_value);
1615 x_success := g_ret_exception_others;
1616 END validate_value_private;
1617
1618
1619 -- --------------------------------------------------
1620 PROCEDURE get_format_private(p_vset_name IN VARCHAR2,
1621 p_vset_format IN VARCHAR2,
1622 p_max_length IN NUMBER,
1623 p_precision IN NUMBER DEFAULT NULL,
1624 x_format_in OUT NOCOPY VARCHAR2,
1625 x_format_out OUT NOCOPY VARCHAR2,
1626 x_canonical OUT NOCOPY VARCHAR2,
1627 x_number_format OUT NOCOPY VARCHAR2,
1628 x_number_min OUT NOCOPY NUMBER,
1629 x_number_max OUT NOCOPY NUMBER,
1630 x_success OUT NOCOPY NUMBER)
1631 IS
1632 l_code_name VARCHAR2(100) := g_package_name || '.get_format_private()';
1633 l_format VARCHAR2(1000);
1634 l_success NUMBER := g_ret_no_error;
1635
1636 tmp_varchar2 VARCHAR2(2000);
1637 tmp_min_vc2 VARCHAR2(2000);
1638 tmp_max_vc2 VARCHAR2(2000);
1639
1640 BEGIN
1641 x_format_in := NULL; -- _IN NLS format.
1642 x_format_out := NULL; -- _OUT NLS format
1643 x_canonical := NULL; -- Storage Format.
1644 x_number_format := NULL; -- Numbers only, mask with D.
1645 x_number_min := NULL; -- Universal min for Numbers.
1646 x_number_max := NULL; -- Universal max for numbers.
1647
1648 --
1649 -- Only Date, DateTime, Time, and Number value sets need masking.
1650 --
1651 IF (NOT (p_vset_format IN ('D', 'T', 't', 'I', 'X', 'Y', 'Z', 'N'))) THEN
1652 ssv_bad_parameter(l_code_name, 'Illegal Value Set Format is passed.');
1653 l_success := g_ret_bad_parameter;
1654 GOTO lbl_return;
1655 END IF;
1656
1657 IF (p_vset_format = 'N') THEN
1658 --
1659 -- Number Value Sets.
1660 --
1661 -- Construct Number Format and Universal Limits.
1662 -- Example :
1663 -- These are universal min and max boundaries.
1664 --
1665 -- We have to check these universal min and max values, otherwise
1666 -- to_char will return ######'s for not fitting numbers.
1667 --
1668 -- maximum universal number
1669 -- length precision minimum maximum format
1670 -- ---------- ------------ ------- ------- ------
1671 -- 5 NULL or <= 0 -9999 99999 99990
1672 -- 5 1 -99.9 999.9 990D9
1673 -- 5 2 -9.99 99.99 90D99
1674 -- 5 3 -.999 9.999 0D999
1675 -- 5 4 0 .9999 D9999
1676 -- 5 else bad precision.
1677 --
1678 IF ((p_precision IS NULL) OR (p_precision <= 0)) THEN
1679 l_format := Lpad('0', p_max_length, '9');
1680 tmp_varchar2 := Lpad('9', p_max_length -1 , '9');
1681 tmp_max_vc2 := '9' || tmp_varchar2;
1682 tmp_min_vc2 := '-' || Nvl(tmp_varchar2, '0');
1683
1684 ELSIF (p_precision < p_max_length - 2) THEN
1685 l_format := Rpad('0D', p_precision + 2, '9');
1686 l_format := Lpad(l_format, p_max_length, '9');
1687 tmp_varchar2 := Rpad('9' || m_ds_db , p_precision + 2, '9');
1688 tmp_varchar2 := Lpad(tmp_varchar2, p_max_length-1, '9');
1689 tmp_max_vc2 := '9' || tmp_varchar2;
1690 tmp_min_vc2 := '-' || tmp_varchar2;
1691
1692 ELSIF (p_precision < p_max_length - 1) THEN
1693 l_format := Rpad('0D', p_precision + 2, '9');
1694 l_format := Lpad(l_format, p_max_length, '9');
1695 tmp_varchar2 := Rpad(m_ds_db, p_precision + 1, '9');
1696 tmp_varchar2 := Lpad(tmp_varchar2, p_max_length-1, '9');
1697 tmp_max_vc2 := '9' || tmp_varchar2;
1698 tmp_min_vc2 := '-' || tmp_varchar2;
1699
1700 ELSIF (p_precision < p_max_length) THEN
1701 l_format := Rpad('D', p_max_length, '9');
1702 tmp_varchar2 := Rpad(m_ds_db, p_max_length, '9');
1703 tmp_max_vc2 := tmp_varchar2;
1704 tmp_min_vc2 := '0';
1705
1706 ELSE
1707 set_message('FND', 'FLEX-VS BAD PRECIS', 1,
1708 'VSNAME', p_vset_name);
1709 l_success := g_ret_vs_bad_precision;
1710 GOTO lbl_return;
1711 END IF;
1712
1713 x_number_format := l_format;
1714 x_number_min := To_number(tmp_min_vc2);
1715 x_number_max := To_number(tmp_max_vc2);
1716
1717 x_format_in := REPLACE(l_format, 'D',
1718 Substr(m_nls_numeric_chars_in, 1, 1));
1719 x_format_out := REPLACE(l_format, 'D', m_ds_disp);
1720 x_canonical := REPLACE(l_format, 'D', m_ds_can);
1721
1722 ELSIF (p_vset_format IN ('D', 'T', 'I', 't')) THEN
1723 --
1724 -- Regular Date, DateTime, Time Value sets.
1725 -- For these value sets (display mask = storage mask)
1726 --
1727 IF (p_vset_format = 'D' AND p_max_length = 9) THEN
1728 l_format := g_date_format_9;
1729 ELSIF (p_vset_format = 'D' AND p_max_length = 11) THEN
1730 l_format := g_date_format_11;
1731 ELSIF (p_vset_format IN ('I','t') AND p_max_length = 5) THEN
1732 l_format := g_date_format_5;
1733 ELSIF (p_vset_format IN ('I','t') AND p_max_length = 8) THEN
1734 l_format := g_date_format_8;
1735 ELSIF (p_vset_format = 'T' AND p_max_length = 15) THEN
1736 l_format := g_date_format_15;
1737 ELSIF (p_vset_format = 'T' AND p_max_length = 17) THEN
1738 l_format := g_date_format_17;
1739 ELSIF (p_vset_format = 'T' AND p_max_length = 18) THEN
1740 l_format := g_date_format_18;
1741 ELSIF (p_vset_format = 'T' AND p_max_length = 20) THEN
1742 l_format := g_date_format_20;
1743 ELSE
1744 set_message('FND', 'FLEX-VS BAD DATE', 1,
1745 'VSNAME', p_vset_name);
1746 l_success := g_ret_vs_bad_date;
1747 GOTO lbl_return;
1748 END IF;
1749 x_format_in := l_format;
1750 x_format_out := l_format;
1751 x_canonical := l_format;
1752
1753 ELSIF (p_vset_format IN ('X', 'Y', 'Z')) THEN
1754 --
1755 -- Standard Date, DateTime, Time Value Sets.
1756 --
1757 IF (p_vset_format = 'X') THEN
1758 x_format_in := m_nls_date_in;
1759 x_format_out := m_nls_date_out;
1760 x_canonical := m_canonical_date;
1761 ELSIF (p_vset_format = 'Y') THEN
1762 x_format_in := m_nls_datetime_in;
1763 x_format_out := m_nls_datetime_out;
1764 x_canonical := m_canonical_datetime;
1765 ELSIF (p_vset_format = 'Z') THEN
1766 x_format_in := m_nls_time_in;
1767 x_format_out := m_nls_time_out;
1768 x_canonical := m_canonical_time;
1769 END IF;
1770 END IF;
1771
1772 <<lbl_return>>
1773 x_success := l_success;
1774 EXCEPTION
1775 WHEN OTHERS THEN
1776 ssv_exception(l_code_name);
1777 x_success := g_ret_exception_others;
1778 END get_format_private;
1779
1780
1781 -- ==============================
1782 -- DEBUG
1783 -- ==============================
1784 -- --------------------------------------------------
1785 PROCEDURE debug(p_debug IN VARCHAR2)
1786 IS
1787 BEGIN
1788 IF g_flag_debugging THEN
1789 IF (Length(g_internal_debug) <= 31000) THEN
1790 g_internal_debug := g_internal_debug || p_debug || chr_newline;
1791 ELSE
1792 g_internal_debug := g_internal_debug ||
1793 'Maximum size is reached for debug string. ' || chr_newline ||
1794 'Debugging is turned OFF.';
1795 g_flag_debugging := FALSE;
1796 END IF;
1797 END IF;
1798 END debug;
1799
1800 -- ==============================
1801 -- FND_MESSAGE utility.
1802 -- ==============================
1803 -- --------------------------------------------------
1804 PROCEDURE set_message_name(p_appl_short_name IN VARCHAR2,
1805 p_message_name IN VARCHAR2)
1806 IS
1807 BEGIN
1808 IF (g_message_stack_number = 0) THEN
1809 IF (p_appl_short_name IS NULL) THEN
1810 fnd_message.set_name('FND', 'FLEX-USER DEFINED ERROR');
1811 fnd_message.set_token('MSG', p_message_name);
1812 ELSE
1813 fnd_message.set_name(p_appl_short_name, p_message_name);
1814 END IF;
1815 END IF;
1816 g_message_stack_number := g_message_stack_number + 1;
1817 END;
1818
1819 -- --------------------------------------------------
1820 PROCEDURE set_message_token(p_token_name IN VARCHAR2,
1821 p_token_value IN VARCHAR2)
1822 IS
1823 BEGIN
1824 IF (g_message_stack_number = 1) THEN
1825 fnd_message.set_token(p_token_name, p_token_value);
1826 END IF;
1827 END;
1828
1829 -- --------------------------------------------------
1830 PROCEDURE set_message(p_appl_short_name IN VARCHAR2,
1831 p_message_name IN VARCHAR2,
1832 p_num_of_tokens IN NUMBER,
1833 p_token_name_1 IN VARCHAR2 DEFAULT NULL,
1834 p_token_value_1 IN VARCHAR2 DEFAULT NULL,
1835 p_token_name_2 IN VARCHAR2 DEFAULT NULL,
1836 p_token_value_2 IN VARCHAR2 DEFAULT NULL,
1837 p_token_name_3 IN VARCHAR2 DEFAULT NULL,
1838 p_token_value_3 IN VARCHAR2 DEFAULT NULL,
1839 p_token_name_4 IN VARCHAR2 DEFAULT NULL,
1840 p_token_value_4 IN VARCHAR2 DEFAULT NULL,
1841 p_token_name_5 IN VARCHAR2 DEFAULT NULL,
1842 p_token_value_5 IN VARCHAR2 DEFAULT NULL)
1843 IS
1844 BEGIN
1845 set_message_name(p_appl_short_name, p_message_name);
1846 IF (p_num_of_tokens > 0) THEN
1847 set_message_token(p_token_name_1, p_token_value_1);
1848 END IF;
1849 IF (p_num_of_tokens > 1) THEN
1850 set_message_token(p_token_name_2, p_token_value_2);
1851 END IF;
1852 IF (p_num_of_tokens > 2) THEN
1853 set_message_token(p_token_name_3, p_token_value_3);
1854 END IF;
1855 IF (p_num_of_tokens > 3) THEN
1856 set_message_token(p_token_name_4, p_token_value_4);
1857 END IF;
1858 IF (p_num_of_tokens > 4) THEN
1859 set_message_token(p_token_name_5, p_token_value_5);
1860 END IF;
1861 END set_message;
1862
1863 -- --------------------------------------------------
1864 PROCEDURE internal_init
1865 IS
1866 BEGIN
1867 IF g_flag_debugging THEN
1868 g_internal_debug := g_package_name || ':' || chr_newline;
1869 END IF;
1870
1871 g_message_stack_number := 0;
1872
1873 init_masks();
1874 END internal_init;
1875
1876 -- --------------------------------------------------
1877 PROCEDURE ssv_bad_parameter(p_func_name IN VARCHAR2,
1878 p_reason IN VARCHAR2 DEFAULT NULL)
1879 IS
1880 BEGIN
1881 set_message('FND','FLEX-SSV EXCEPTION', 1,
1882 'MSG', p_func_name || 'is failed.' || chr_newline ||
1883 'Reason : ' ||
1884 Nvl(p_reason, 'Wrong parameters in function call.'));
1885 END ssv_bad_parameter;
1886
1887 -- --------------------------------------------------
1888 PROCEDURE ssv_exception(p_func_name IN VARCHAR2,
1889 p_message IN VARCHAR2 DEFAULT NULL)
1890 IS
1891 BEGIN
1892 set_message('FND', 'FLEX-SSV EXCEPTION', 1,
1893 'MSG', p_func_name || ' failed. ' || chr_newline ||
1894 'Message : ' || p_message || chr_newline ||
1895 'Error : ' || Sqlerrm);
1896 END ssv_exception;
1897
1898 -- --------------------------------------------------
1899 PROCEDURE init_masks
1900 IS
1901 BEGIN
1902 --
1903 -- Default NLS masks.
1904 --
1905 m_nls_date_in := fnd_date.user_mask;
1906 m_nls_date_out := fnd_date.output_mask;
1907 m_nls_datetime_in := fnd_date.userdt_mask;
1908 m_nls_datetime_out := fnd_date.outputdt_mask;
1909 m_nls_time_in := 'HH24:MI:SS';
1910 m_nls_time_out := 'HH24:MI:SS';
1911 m_db_numeric_chars := (Substr(To_char(1234.5,'FM9G999D9'), 6, 1) ||
1912 Substr(To_char(1234.5,'FM9G999D9'), 2, 1));
1913 m_ds_db := Substr(m_db_numeric_chars, 1, 1);
1914 m_nls_numeric_chars_in := m_db_numeric_chars;
1915 m_nls_numeric_chars_out := m_db_numeric_chars;
1916 m_ds_disp := Substr(m_nls_numeric_chars_out, 1, 1);
1917 END init_masks;
1918
1919 BEGIN
1920 chr_newline := fnd_global.newline;
1921 g_flag_debugging := FALSE;
1922 g_internal_debug := ('Debugging is turned OFF. ' || chr_newline ||
1923 'Please call set_debugging(TRUE) to turn it ON.');
1924
1925 init_masks();
1926 END fnd_flex_val_util;