1 package body hr_chkfmt as
2 /* $Header: pychkfmt.pkb 120.7 2007/11/06 10:38:55 ayegappa noship $ */
3
4 g_group_sep_profile CONSTANT VARCHAR2(2) := NVL(fnd_profile.value('HR_NUMBER_SEPARATOR'),'N');
5
6 FUNCTION add_remove_group_separator ( input IN VARCHAR2
7 , remove_separator IN BOOLEAN DEFAULT FALSE)
8 RETURN VARCHAR2
9 IS
10 l_output VARCHAR2(100);
11 l_group_separator VARCHAR2(2) := substr(ltrim(to_char(1032,'0G999')),2,1);
12 --NVL(SUBSTR(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),2,1),',');
13 l_dec_separator VARCHAR2(2) := substr(ltrim(to_char(.3,'0D0')),2,1);
14 --NVL(SUBSTR(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1),'.');
15 l_negative_flag BOOLEAN := FALSE ;
16 BEGIN
17
18 IF remove_separator THEN
19 l_output := REPLACE(input,l_group_separator,'');
20 ELSE
21
22 IF (input<0 and input>-1) THEN -- Added for bug 5707731.
23 l_negative_flag := TRUE;
24 END IF;
25
26 l_output := LTRIM(TO_CHAR( TRUNC(TO_NUMBER(input)), '99G999G999G999G999G999G990'));
27
28 IF INSTR(input,l_dec_separator) <> 0 THEN
29 l_output := l_output || l_dec_separator || SUBSTR(input,INSTR(input,l_dec_separator) + 1);
30 END IF;
31
32 IF (l_negative_flag) THEN -- Added for bug 5707731
33 l_output := '-' || l_output ;
34 END IF;
35
36
37 END IF;
38
39 RETURN l_output;
40
41 END add_remove_group_separator;
42
43 ----------------------------- chkmoney ----------------------------------
44 /*
45 NAME
46 chkmoney - format currency.
47 DESCRIPTION
48 Formats and checks currency input.
49 Uses FND_CURRENCIES to get information about precision.
50 NOTES
51 p_value can be passed in in either NLS or canonical format, as
52 indicated by p_value_is_canonical. p_value is always returned in
53 NLS format, rounded and padded according to the rules in table
54 fnd_currencies.
55 */
56 PROCEDURE chkmoney
57 (
58 p_value IN OUT NOCOPY VARCHAR2, -- the input to be formatted (see notes).
59 p_output IN OUT NOCOPY VARCHAR2, -- the canonical format of returned p_value.
60 p_curcode IN VARCHAR2, -- currency code.
61 p_minimum IN VARCHAR2, -- minimum in canonical format.
62 p_maximum IN VARCHAR2, -- maximum in canonical format.
63 p_rgeflg IN OUT NOCOPY VARCHAR2, -- success or otherwise of range check.
64 p_result OUT NOCOPY BOOLEAN, -- the result of the format check.
65 p_value_is_canonical IN BOOLEAN --TRUE if value is passed IN in canonical format.
66 ) IS
67 RGE_SUCC CONSTANT VARCHAR2(1) := 'S';
68 RGE_FAIL CONSTANT VARCHAR2(1) := 'F';
69 --
70 -- Max and min value that can be handled by the money format.
71 --
72 MAX_VALUE CONSTANT NUMBER := 99999999999999999999;
73 MIN_VALUE CONSTANT NUMBER := -99999999999999999999;
74
75 nvalue NUMBER; -- used to hold p_value as a number.
76 noutput NUMBER; -- used to hold p_output as a number.
77 l_value VARCHAR2(100);
78 BEGIN
79 p_result := TRUE;
80 IF p_value_is_canonical THEN
81 nvalue := fnd_number.canonical_to_number( p_value );
82 ELSE
83 IF g_group_sep_profile = 'Y' THEN
84 l_value := add_remove_group_separator(p_value,TRUE);
85 IF p_value = l_value THEN
86 nvalue := TO_NUMBER( l_value );
87 ELSIF p_value = add_remove_group_separator(l_value) THEN
88 nvalue := TO_NUMBER(l_value);
89 ELSE
90 nvalue := p_value;
91 p_result := FALSE;
92 END IF;
93 ELSE
94 nvalue := TO_NUMBER( p_value ); --uses session NLS settings.
95 END IF;
96 END IF;
97 SELECT DECODE --round to min acct limits if available.
98 ( fc.minimum_accountable_unit,
99 NULL, ROUND( nvalue, fc.precision ),
100 ROUND( nvalue / fc.minimum_accountable_unit ) * fc.minimum_accountable_unit
101 )
102 , LTRIM
103 ( TO_CHAR
104 ( DECODE --round to min acct limits if available.
105 ( fc.minimum_accountable_unit,
106 NULL, ROUND( nvalue, fc.precision ),
107 ROUND( nvalue / fc.minimum_accountable_unit ) * fc.minimum_accountable_unit
108 )
109 , CONCAT --construct NLS format mask.
110 ( '99999999999999999990', --currencies formatted without NLS 'G'.
111 DECODE( fc.precision, 0, '', RPAD( 'D', fc.precision+1, '9' ) )
112 )
113 ), ' ' --left trim white space.
114 )
115 INTO noutput
116 , p_value
117 FROM fnd_currencies fc
118 WHERE fc.currency_code = p_curcode;
119 -- range checking.
120 IF p_minimum IS NOT NULL THEN
121 IF noutput < fnd_number.canonical_to_number( p_minimum ) THEN
122 p_rgeflg := RGE_FAIL;
123 END IF;
124 END IF;
125 IF p_maximum IS NOT NULL THEN
126 IF noutput > fnd_number.canonical_to_number( p_maximum ) THEN
127 p_rgeflg := RGE_FAIL;
128 END IF;
129 END IF;
130
131 IF not (noutput between MIN_VALUE and MAX_VALUE) then
132 p_result := FALSE;
133 END IF;
134
135 IF g_group_sep_profile = 'Y' THEN
136 p_value := add_remove_group_separator(p_value);
137 END IF;
138
139 p_output := fnd_number.number_to_canonical( noutput );
140 EXCEPTION
141 -- this catches illegal numbers.
142 WHEN VALUE_ERROR THEN
143 p_result := FALSE;
144 WHEN OTHERS THEN
145 p_result := FALSE;
146 END chkmoney;
147 --
148 ------------------------------ chkdech ----------------------------------
149 /*
150 NAME
151 chkdech - format check hours in decimal format..
152 DESCRIPTION
153 Converts p_value to an NLS VARCHAR2 representation with p_format
154 number of significant decimal places.
155 P_result returns FALSE if p_value fails conversion to a number.
156 NOTES
157 p_value can be passed in in either NLS or canonical format, as
158 indicated by p_value_is_canonical. p_value is always returned in
159 NLS format, rounded and padded according to p_format.
160 */
161 PROCEDURE chkdech
162 (
163 p_value IN OUT NOCOPY VARCHAR2, -- the input to be formatted.
164 p_format IN VARCHAR2, -- the specific format.
165 p_minimum IN VARCHAR2, -- minimum in canonical format.
166 p_maximum IN VARCHAR2, -- maximum in canonical format.
167 p_rgeflg IN OUT NOCOPY VARCHAR2, -- success or otherwise of range check.
168 p_result OUT NOCOPY BOOLEAN, -- the result of the format check.
169 p_value_is_canonical IN BOOLEAN --TRUE if value is passed IN in canonical format.
170 ) IS
171 RGE_SUCC CONSTANT VARCHAR2(1) := 'S';
172 RGE_FAIL CONSTANT VARCHAR2(1) := 'F';
173 decplace PLS_INTEGER; -- number of decimal places.
174 nvalue NUMBER; -- used to hold p_value as a number.
175 BEGIN
176 p_result := TRUE; -- start by assuming success.
177 IF p_value_is_canonical THEN
178 nvalue := fnd_number.canonical_to_number( p_value );
179 ELSE
180 nvalue := TO_NUMBER( p_value ); --uses session NLS settings.
181 END IF;
182 -- can get dec places from the last character of the format:
183 IF p_format = 'HOURS' THEN
184 decplace := 3; -- for backwards compatability.
185 ELSE
186 decplace := TO_NUMBER( SUBSTR( p_format, -1, 1 ) );
187 END IF;
188 -- round and format the number.
189 nvalue := ROUND( nvalue, decplace );
190 SELECT LTRIM( TO_CHAR( nvalue, CONCAT( '999999999999990',
191 DECODE( decplace, 0, '', RPAD( 'D', decplace+1, '9' ) ) ) ) , ' ' )
192 INTO p_value
193 FROM dual;
194 -- range checking.
195 IF p_minimum IS NOT NULL THEN
196 IF nvalue < fnd_number.canonical_to_number( p_minimum ) THEN
197 p_rgeflg := RGE_FAIL;
198 END IF;
199 END IF;
200 IF p_maximum IS NOT NULL THEN
201 IF nvalue > fnd_number.canonical_to_number( p_maximum ) THEN
202 p_rgeflg := RGE_FAIL;
203 END IF;
204 END IF;
205 EXCEPTION
206 WHEN OTHERS THEN --when varchar2 conversion to number fails.
207 p_result := FALSE;
208 END chkdech;
209 --
210 --------------------------------- chknum --------------------------------
211 /*
212 NAME
213 chknum - check format of number
214 DESCRIPTION
215 Check format of number (decimal) and integer.
216 P_result returns FALSE if p_value fails conversion to a number.
217 NOTES
218 p_value can be passed in in either NLS or canonical format, as
219 indicated by p_value_is_canonical. p_value is always returned in
220 NLS format (integers are unaffected by NLS formatting). Note
221 Decimal separater is still required, even for whole decimal
222 numbers. E.g. 20 becomes '20.'.
223 */
224 PROCEDURE chknum
225 (
226 p_value IN OUT NOCOPY VARCHAR2, -- value to be formatted.
227 p_output IN OUT NOCOPY VARCHAR2, -- the canonical format of returned p_value.
228 p_minimum IN VARCHAR2, -- minimum in canonical format.
229 p_maximum IN VARCHAR2, -- maximum in canonical format.
230 p_rgeflg IN OUT NOCOPY VARCHAR2, -- success or otherwise of range check.
231 p_format IN VARCHAR2, -- the format to check.
235 RGE_SUCC CONSTANT VARCHAR2(1) := 'S';
232 p_result OUT NOCOPY BOOLEAN, -- true (success) or false (failure).
233 p_value_is_canonical IN BOOLEAN --TRUE if value is passed IN in canonical format.
234 ) IS
236 RGE_FAIL CONSTANT VARCHAR2(1) := 'F';
237
238 --
239 -- Max and min value that can be handled by the number format.
240 --
241 MAX_VALUE CONSTANT NUMBER := 99999999999999999999;
242 MIN_VALUE CONSTANT NUMBER := -99999999999999999999;
243
244 nvalue NUMBER; -- used to hold p_value as a number.
245 l_value VARCHAR2(100);
246 BEGIN
247 p_result := TRUE; -- start by assuming success.
248 IF p_value_is_canonical THEN
249 nvalue := fnd_number.canonical_to_number( p_value );
250 ELSE
251 IF g_group_sep_profile = 'Y' THEN
252 l_value := add_remove_group_separator(p_value,TRUE);
253 IF p_value = l_value THEN
254 nvalue := TO_NUMBER( l_value );
255 ELSIF p_value = add_remove_group_separator(l_value) THEN
256 nvalue := TO_NUMBER(l_value);
257 ELSE
258 nvalue := p_value;
259 p_result := FALSE;
260 END IF;
261 ELSE
262 nvalue := TO_NUMBER( p_value ); --uses session NLS settings.
263 END IF;
264 END IF;
265
266 IF p_format = 'INTEGER' or p_format = 'I' THEN
267 IF MOD( nvalue, 1 ) <> 0 THEN
268 p_result := FALSE; --p_value is not an integer.
269 ELSE
270 p_value := TO_CHAR( nvalue ); --integers do not have Decimal separater.
271 END IF;
272 ELSE
273 -- Convert number to NLS string.
274 if (nvalue = trunc(nvalue)) then
275 p_value := LTRIM( TO_CHAR( nvalue, '99999999999999999990' ) );
276 else
277 p_value := LTRIM( RTRIM( TO_CHAR( nvalue,
278 '99999999999999999990D99999999999999999999' ), '0' ) );
279 end if;
280 END IF;
281 -- range checking.
282 IF p_minimum IS NOT NULL THEN
283 IF nvalue < fnd_number.canonical_to_number( p_minimum ) THEN
284 p_rgeflg := RGE_FAIL;
285 END IF;
286 END IF;
287 IF p_maximum IS NOT NULL THEN
288 IF nvalue > fnd_number.canonical_to_number( p_maximum ) THEN
289 p_rgeflg := RGE_FAIL;
290 END IF;
291 END IF;
292
293 IF not (nvalue between MIN_VALUE and MAX_VALUE) then
294 p_result := FALSE;
295 END IF;
296
297 p_output := fnd_number.number_to_canonical(p_value);
298
299 IF g_group_sep_profile = 'Y' THEN
300 p_value := add_remove_group_separator(p_value);
301 END IF;
302 EXCEPTION
303 WHEN OTHERS THEN --when varchar2 conversion to number fails.
304 p_result := FALSE;
305 END chknum;
306 --
307 --------------------------- checkformat -----------------------------------
308 /*
309 NAME
310 checkformat - checks format of various inputs.
311 DESCRIPTION
312 Entry point for the checkformat routine.
313 Is used to check the validity of the following formats:
314 CHAR : arbitrary string of characters.
315 UPPER : converts string to upper case.
316 LOWER : converts string to lower case.
317 INITCAP : init caps string.
318 INTEGER : checks that input is integer.
319 NUMBER : checks input is valid decimal number.
320 ND : Same as number, (another days format).
321 TIMES : checks input is valid time.
322 DATE : checks input is valid date (DD-MON-YYYY).
323 HOURS : checks input is valid number of hours.
324 DB_ITEM_NAME : checks input is valid database item name.
325 PAY_NAME : checks input is valid payroll name.
326 NACHA : checks input contains valid nacha digits.
327 KANA : checks input is KANA character.
331 Use canonical format for decimal numbers.
328 NOTES
329 This procedure is called directly from FF RSP user exit.
330 Maximum and minimum parameters:
332 */
336 format in varchar2, -- the format to check.
333 procedure checkformat
334 (
335 value in out nocopy varchar2, -- the value to be formatted.
337 output in out nocopy varchar2, -- the formatted value on output.
338 minimum in varchar2, -- minimum value (can be null).
339 maximum in varchar2, -- maximum value (can be null).
340 nullok in varchar2, -- is ok to be null ?
341 rgeflg in out nocopy varchar2, -- used for range checking.
342 curcode in varchar2 -- currency code to be used for money format.
343 ) is
344 result boolean;
345 RGE_SUCC constant varchar2(1) := 'S';
346 RGE_FAIL constant varchar2(1) := 'F';
347 --
348 -------------------------------- chkdate --------------------------------
349 /*
350 NAME
351 chkdate - check format of date
352 DESCRIPTION
353 Checks date formats.
354 NOTES
355 The following date formats are now handled:
356 Specified Real Input Field Output Field
357 ------------ ----------- ----------- ------------
358 D_DDMONYY DD-MON-RR 28-JAN-92 28-JAN-1992
359 D_DDMONYYYY DD-MON-RRRR 28-JAN-1992 28-JAN-1992
360 D_DDMONYYYY DD-MON-RRRR 28-AUG-01 28-AUG-2001
361 D_DDMMYY DD-MM-RR 28-01-92 28-JAN-1992
362 D_DDMMYYYY DD-MM-RRRR 28-01-1992 28-JAN-1992
363 D_DDMMYYYY DD-MM-RRRR 28-01-01 28-JAN-2001
364 D_MMDDYY MM-DD-RR 01-28-02 28-JAN-2002
365 D_MMDDYYYY MM-DD-RRRR 01-28-1992 28-JAN-1992
366 D_MMDDYYYY MM-DD-RRRR 01-28-01 28-JAN-2001
367 --
368 - If format is one of the 'YYYY' types, and only 'YY'
369 value is input, the date is output using century rounding.
370 See the examples above.
371 - The exception handler is used to detect illegal dates.
372 - Range checking is allowed on all formats, but the
373 limits must be in the same format as the input.
374 - The output format is always in 'DD-MON-YYYY'.
375 - If the input format has a 2 digit year
376 */
377 procedure chkdate
378 (
379 value in out nocopy varchar2, -- date value to be checked.
380 format in varchar2, -- the particular date format.
381 output in out nocopy varchar2, -- the converted date format.
382 minimum in varchar2, -- minimum date.
383 maximum in varchar2, -- maximum date.
384 rgeflg in out nocopy varchar2, -- success or otherwise of range check.
385 result out nocopy boolean -- format success or fail.
386 ) is
387 realfmt varchar2(11); -- the real format.
388 l_date date;
389 begin
390
391 result := TRUE;
392 -- now check that we have the correct date format
393 -- passed in.
394 l_date := fnd_date.displaydate_to_date(value);
395 --
396 -- the date format has been correctly verified.
397 -- now return it to the output field in the
398 -- canonical format of of 'YYYY/MM/DD'
399 output := fnd_date.date_to_canonical(l_date);
400 --
401 -- Return the corrected format to the output.
402 -- Needed for instance where user inputs a
403 -- value like '01-jan-99' which we want displayed
404 -- as '01-JAN-1999'.
405 value := fnd_date.date_to_displaydate(l_date);
406 --
407 -- minimum and maximum checking.
408 if(minimum is not null) then
409 if(l_date < fnd_date.canonical_to_date(minimum)) then
410 rgeflg := RGE_FAIL;
411 return;
412 end if;
413 end if;
414 if(maximum is not null) then
415 if(l_date > fnd_date.canonical_to_date(maximum)) then
416 rgeflg := RGE_FAIL;
417 return;
418 end if;
419 end if;
420 exception
421 -- have to user the 'others' exception, since there is
422 -- no specific exception to catch illegal date formats.
423 when others then
424 result := FALSE;
425 end chkdate;
426 --
427 ------------------------------- chkhours --------------------------------
428 /*
429 NAME
430 chkhours - check hours format and convert.
431 DESCRIPTION
432 Performs the following actions. Firstly, validates
433 hours in the following formats: H_HH, H_HHMM, H_HHMMSS.
434 Secondly, it converts these to an internal format,
435 which is currently number(40,20).
436 Will perform conversions such as 112:3 -> 112:30.
437 NOTES
438 <none>
439 */
440 procedure chkhours
441 (
442 value in out nocopy varchar2, -- the input value to be formatted.
443 format in varchar2, -- the specific format.
444 output in out nocopy varchar2, -- value in canonical format.
445 minimum in varchar2, -- min hours value in canonical format.
446 maximum in varchar2, -- min hours value in canonical format.
447 rgeflg in out nocopy varchar2, -- indicate success or otherwise of range.
448 result out nocopy boolean -- success or failure flag.
449 ) is
450 INT_PREC constant number := 20; -- internal format precision.
451 SEPARATOR constant varchar2(1) := ':';
452 MIN_HHMM constant number := 3;
453 MIN_HHMMSS constant number := 6;
454 hours varchar2(40);
455 minutes varchar2(40);
456 seconds varchar2(40);
457 minplussec varchar2(40); -- holds minutes + seconds string.
458 len number; -- length of the input string.
459 minseppos number; -- minute separator position.
460 secseppos number; -- seconds separator position.
464 minutes := 0;
461 negative boolean; -- is the input negative??
462 begin
463 hours := 0;
465 seconds := 0;
466 negative := FALSE;
467 -- if the first character of the input
468 -- is a minus sign, we assume the input is negative.
469 if(substr(value,1,1) = '-') then
470 negative := TRUE;
471 value := substr(value,2); -- remove the minus sign.
472 end if;
473 -- bug 6522314. Added nvl
474 len := nvl(length(value),0); -- len of input without any negation sign.
475 -- get the values of hours, minutes and seconds,
476 -- depending on the format passed.
477 if(format = 'H_HH') then
478 -- check is number and integer.
479 if(trunc(value) <> value) then
480 result := FALSE;
481 return;
482 end if;
483 hours := value;
484 elsif(format = 'H_HHMM') then
485 declare
486 check_fmt varchar2(10);
487 begin
488 if(len < MIN_HHMM) then
489 result := FALSE;
490 return;
491 end if;
492 minseppos := instr(value,SEPARATOR); -- where is the colon char.
493 -- error if either the separator is the first or last
494 -- character, or there is an illegal separator.
495 if(minseppos = 1 or minseppos = len or minseppos = 0) then
496 result := FALSE;
497 return;
498 end if;
499 hours := substr(value,1,(minseppos - 1)); -- hours string.
500 minutes := substr(value,(minseppos + 1),len); -- minutes string.
501 -- Check that we do not have any illegal
502 -- characters in our format.
503 check_fmt := to_char(to_date(minutes,'MI'),'MI');
504 minutes := rpad(minutes,2,'0'); -- format correctly.
505 end;
506 elsif(format = 'H_HHMMSS') then
507 minseppos := instr(value,SEPARATOR); -- pos of minutes sep.
508 -- Check that we have a legal minutes separator.
509 if(minseppos = 1 or minseppos = len or minseppos = 0) then
510 result := FALSE;
511 return;
512 end if;
513 hours := substr(value,1,(minseppos - 1));
514 -- check that the hours string represents an integer.
515 if(trunc(hours) <> hours) then
516 result := FALSE;
517 return;
518 end if;
519 minplussec := substr(value,(minseppos + 1),len);
520 minutes := to_char(to_date(minplussec,'MI:SS'),'MI');
521 seconds := to_char(to_date(minplussec,'MI:SS'),'SS');
522 end if;
523 --
524 -- apply some sanity checks.
525 if(hours < 0 or minutes < 0 or minutes > 59 or
526 seconds < 0 or seconds > 59) then
527 result := FALSE;
528 return;
529 end if;
530 --
531 -- do the output of the format.
532 if(format = 'H_HHMM') then
533 value := hours || SEPARATOR || minutes;
534 elsif(format = 'H_HHMMSS') then
535 value := hours || SEPARATOR || minutes || SEPARATOR || seconds;
536 else
537 -- format is H_HH.
538 null;
539 end if;
540 --
541 -- output the converted value.
542 output := fnd_number.number_to_canonical(
543 round(hours + (minutes/60) + (seconds/3600),INT_PREC) );
544 --
545 -- having done the checks, we need to check
546 -- if we originally had negative input
547 if(negative = TRUE) then
548 output := '-' || output;
549 value := '-' || value;
550 end if;
551 -- minimum and maximum checking.
552 if(minimum is not null) then
553 if(fnd_number.canonical_to_number(output) <
554 fnd_number.canonical_to_number(minimum)) then
555 rgeflg := RGE_FAIL;
556 return;
557 end if;
558 end if;
559 if(maximum is not null) then
560 if(fnd_number.canonical_to_number(output) >
561 fnd_number.canonical_to_number(maximum)) then
562 rgeflg := RGE_FAIL;
563 return;
564 end if;
565 end if;
566 exception
567 -- this exception could be raised if illegal
568 -- number supplied as a minimum or maximum.
569 when value_error then
570 result := FALSE;
571 when others then
572 result := FALSE;
573 end chkhours;
574 --
575 -------------------------------- chktime --------------------------------
576 /*
577 NAME
578 chktime - check the format of time.
579 DESCRIPTION
580 Routine checks that time is correcly formatted.
581 Will do format conversions like '2:2' -> '02:20'.
582 NOTES
583 Exceptions processing used to catch any illegal
584 time formats passed to routine.
585 */
586 procedure chktime
587 (
588 value in out nocopy varchar2,
589 minimum in varchar2, -- minimum allow able value
590 maximum in varchar2, -- maximum allowable value
591 rgeflg in out nocopy varchar2, -- success or otherwise of range check.
592 result out nocopy boolean
593 ) is
594 TIME_SEPARATOR constant varchar2(1) := ':'; -- make this a constant.
595 isdate date; -- used when checking is legal date.
596 hours varchar2(2); -- hold hours component.
597 minutes varchar2(2); -- hold minutes component.
598 len number; -- length of string.
599 seppos number; -- the character position of the time separator.
600 --
601 begin
605 if(seppos = 0) then
602 -- first thing, check that we have a legal oracle time format.
603 isdate := to_date(value,'HH24:MI'); -- raises exception if not.
604 seppos := instr(value,TIME_SEPARATOR); -- look for legal separator.
606 result := FALSE;
607 return;
608 end if;
609 -- separate hours and minutes.
610 len := length(value); -- how long the time string.
611 hours := substr(value,1,(seppos - 1)); -- get hours string.
612 minutes := substr(value,(seppos + 1),len); -- get minutes string.
613 -- now we put them together using lpad and rpad to format correctly.
614 value := lpad(hours,2,'0') || TIME_SEPARATOR || rpad(minutes,2,'0');
615 --
616 -- check this again following formatting.
617 isdate := to_date(value,'HH24:MI');
618 --
619 result := TRUE;
620 if(minimum is not null) then
621 if(isdate <
622 to_date(minimum,'HH24:MI')) then
623 rgeflg := RGE_FAIL;
624 return;
625 end if;
626 end if;
627 if(maximum is not null) then
628 if(isdate >
629 to_date(maximum,'HH24:MI')) then
630 rgeflg := RGE_FAIL;
631 return;
632 end if;
633 end if;
634 exception
635 -- use 'others' exception because there are
636 -- no specific errors we can trap for illegal times.
637 when others then
638 result := FALSE;
639 end chktime;
640 --
641 -------------------------------- chkpay ---------------------------------
642 /*
643 NAME
644 chkpay - check payroll name does not contain illegal characters.
645 DESCRIPTION
646 Used to ensure that a name passed in only comprises of:
647 First character : alpha characters (upper or lower case).
648 Subsequent chars: alpha, numeric, space and underscore.
649 NOTES
650 Use the translate function and dbms_sql package to check for
651 illegal chars. Cannot be called from pragma restrict_references
652 code.
653 */
654 function chkpay
655 (
656 value in varchar2 -- the name to check.
657 ) return boolean is
658 l_cursor_id binary_integer; -- Cursor for dynamic PL/SQL.
659 statement varchar2(2000); -- Dynamic PL/SQL statement.
660
661 copy varchar2(240); -- Copy of string without spaces.
662
663 chunk varchar2(2000); -- Chunk to be syntax-checked.
664 spos integer; -- Start position of chunk in input string.
665 epos integer; -- End position of chunk in input string.
666 clen number; -- Length of input string in characters.
667 first boolean; -- Is this the first chunk being processed ?
668
669 -- The following string will be added to any chunks to avoid problems
670 -- with reserved words being rejected.
671 MUCK constant varchar2(3) := 'ZQX';
672
673 -- Variables for checking for PL/SQL characters that aren't
674 -- allowed in name syntax.
675 exchk varchar2(240);
676 match varchar2(240);
677 PADCH constant varchar2(1) := '$';
678
679 SPACE constant varchar2(1) := ' ';
680
681 -- Valid PL/SQL identifier name characters that are are illegal
682 -- in HR, Payroll, and Formula names.
683 PLSQL_EXCLUDE varchar2(64);
684 -- Maximum length of the name being checked in bytes.
685 MAX_NAME_LEN constant number := 80;
686 -- Maximum PL/SQL name length in bytes.
687 MAX_PLSQL_LEN constant number := 30;
688 begin
689 -------------------------------------------------------------
690 -- Name syntax is a letter followed by a string containing --
691 -- letters, digits, ' ', and '_'. PL/SQL does not have any --
692 -- character classification functions e.g. to say what's a --
693 -- 'letter' or a 'digit'. PL/SQL identifier name syntax is --
694 -- the same as the name syntax except that it allows '#' --
695 -- and '$' characters in the names, and allows " quoted --
696 -- identifier names which may contain spaces. --
697 -------------------------------------------------------------
698 -- The basic algorithm is to use the input value as a --
699 -- PL/SQL identifier name in some dynamic PL/SQL code. The --
700 -- PL/SQL parser will syntax check the value as a PL/SQL --
701 -- identifier name - if an error is detected an exception --
702 -- will be raised. The code can reject values that contain --
703 -- '#', '$', or could be quoted identifiers, before trying --
704 -- to parse the dynamic PL/SQL. If the parse is okay, the --
705 -- syntax is okay. The algorithm is slightly complicated --
706 -- by fact the PL/SQL names may only be up 30 bytes in --
707 -- length, whereas the names being checked may be up to 80 --
708 -- bytes in length. The input value is chopped into chunks --
709 -- of size 30 bytes or less and the PL/SQL test is run on --
710 -- the chunks. --
711 -------------------------------------------------------------
712
713 if ( ( value is not null or length( value ) <> 0 ) and
714 lengthb( value ) <= MAX_NAME_LEN ) then
715
716 -- Set up the PL/SQL exclusion characters.
717 PLSQL_EXCLUDE := '"$#' || to_multi_byte( '"$#' );
718
719 -- Check that the string does not contain any excluded PL/SQL
720 -- characters.
721 match := PADCH;
722 match := lpad( match, length( PLSQL_EXCLUDE ), PADCH );
723 exchk := translate( value, PLSQL_EXCLUDE, match );
724 exchk := replace( exchk, PADCH, '' );
728
725 if ( exchk is null or length( exchk ) < length( value ) ) then
726 return( FALSE );
727 end if;
729 -- We have to operate on a copy of the string without the spaces
730 -- because PL/SQL variable names may not contain spaces.
731 if ( substr( value, 1, 1 ) = SPACE ) then
732 return( FALSE );
733 end if;
734 copy := replace( value, SPACE, '' );
735 clen := length( copy );
736
737 -- Check the name string chunk by chunk. Need to do this chunk
738 -- by chunk because string can be longer than the PL/SQL allows.
739
740 -- Set up information for the first chunk to be checked.
741 first := TRUE;
742 spos := 1;
743 epos := clen;
744 chunk := copy || MUCK;
745
746 while ( spos <= clen ) loop
747 -- Make sure that the chunk is short enough for PL/SQL to
748 -- handle.
749 while ( lengthb( chunk ) > MAX_PLSQL_LEN ) loop
750 epos := ( spos + epos ) / 2;
751 if ( first ) then
752 -- Append MUCK to make sure that the start of the name
753 -- is okay.
754 chunk := substr( copy, spos, epos - spos + 1 ) || MUCK;
755 else
756 -- Prepend MUCK because the chunk may not start with a
757 -- letter.
758 chunk := MUCK || substr( copy, spos, epos - spos + 1 );
759 end if;
760 end loop;
761
762 if ( first ) then
763 first := FALSE;
764 end if;
765
766 -- Build a dynamic SQL statement using the name as a variable
767 -- name. This will syntax check the name as a valid PL/SQL name.
768 statement := 'DECLARE ' || chunk || ' NUMBER;BEGIN ' ||
769 chunk || ':=1;END;';
770 l_cursor_id := dbms_sql.open_cursor;
771 dbms_sql.parse( l_cursor_id, statement, dbms_sql.native );
772 dbms_sql.close_cursor( l_cursor_id );
773
774 -- Move onto next the chunk.
775 spos := epos + 1;
776 epos := clen;
777 if ( spos <= clen ) then
778 -- Prepend MUCK because the chunk may not start
779 -- with a letter.
780 chunk := MUCK || substr( copy, spos, epos - spos + 1 );
781 end if;
782 end loop;
783
784 -- Exited the loop without problems, so value is good.
785 return( TRUE );
786 else
787 -- String is null, zero length, or too long.
788 return( FALSE );
789 end if;
790
791 -- Exception handler to catch errors in the dynamic SQL ie.
792 -- the syntax of the chunk being tested is invalid.
793 exception
794 when others then
795 begin
796 --
797 -- Make sure that the cursor was closed.
798 --
799 if ( dbms_sql.is_open( l_cursor_id ) ) then
800 dbms_sql.close_cursor( l_cursor_id );
801 end if;
802 return( FALSE );
803 end;
804 end chkpay;
805 --
806 -------------------------------- chkdbi ---------------------------------
807 /*
808 NAME
809 chkdbi - check format of database item name.
810 DESCRIPTION
811 Check that db item name only contains legal characters.
812 The are the following:
813 First character : upper or lower case alphabetic characters.
814 Subsequent chars: alpha, numeric or underscore.
815
816 JULY-2004: introduced a new style of database item name where
817 any text within double-quotes is allowed.
818
819 NOTES
820 Use the translate function and dbms_sql package to check for
821 illegal chars. Cannot be called from pragma restrict_references
822 code.
823 */
824 function chkdbi
825 (
826 value in varchar2 -- the name to check.
827 ) return boolean is
828 l_cursor_id binary_integer; -- Cursor for dynamic PL/SQL.
829 statement varchar2(2000); -- Dynamic PL/SQL statement.
830
831 chunk varchar2(2000); -- Chunk to be syntax-checked.
832 spos integer; -- Start position of chunk in input string.
833 epos integer; -- End position of chunk in input string.
834 clen number; -- Length of input string in characters.
835 first boolean; -- Is this the first chunk being processed ?
836
837 -- The following string will be added to any chunks to avoid problems
838 -- with reserved words being rejected.
839 MUCK constant varchar2(3) := 'ZQX';
840
841 -- Variables for checking for PL/SQL characters that aren't
842 -- allowed in name syntax.
843 exchk varchar2(240);
844 match varchar2(240);
845 PADCH constant varchar2(1) := '$';
846
847 -- Valid PL/SQL identifier name characters that are are illegal
848 -- in HR, Payroll, and Formula names.
849 PLSQL_EXCLUDE varchar2(64);
850 -- Maximum length of the name being checked in characters.
851 MAX_NAME_LEN constant number := 160;
852 -- Maximum PL/SQL name length in bytes.
853 MAX_PLSQL_LEN constant number := 30;
854 -- Maximum name length in bytes.
855 MAX_BYTE_LENGTH constant number := 240;
856
857 QUOTE1 varchar2(16);
858 QUOTE2 varchar2(16);
859 thechar varchar2(16);
860 begin
861 -------------------------------------------------------------
862 -- Name syntax is a letter followed by a string containing --
866 -- the same as the name syntax except that it allows '#' --
863 -- letters, digits, and '_'. PL/SQL does not have any --
864 -- character classification functions e.g. to say what's a --
865 -- 'letter' or a 'digit'. PL/SQL identifier name syntax is --
867 -- and '$' characters in the names, and allows " quoted --
868 -- identifier names which may contain spaces. --
869 -------------------------------------------------------------
870 -- The basic algorithm is to use the input value as a --
871 -- PL/SQL identifier name in some dynamic PL/SQL code. The --
872 -- PL/SQL parser will syntax check the value as a PL/SQL --
873 -- identifier name - if an error is detected an exception --
874 -- will be raised. The code can reject values that contain --
875 -- '#', '$', or could be quoted identifiers, before trying --
876 -- to parse the dynamic PL/SQL. If the parse is okay, the --
877 -- syntax is okay. The algorithm is slightly complicated --
878 -- by fact the PL/SQL names may only be up 30 bytes in --
879 -- length, whereas the names being checked may be up to 80 --
880 -- bytes in length. The input value is chopped into chunks --
881 -- of size 30 bytes or less and the PL/SQL test is run on --
882 -- the chunks. --
883 -------------------------------------------------------------
884
885 if ( ( value is not null or length( value ) <> 0 ) and
886 length( value ) <= MAX_NAME_LEN
887 and lengthb( value ) <= MAX_BYTE_LENGTH ) then
888 --
889 -- First look for double-quote delimited value.
890 --
891 QUOTE1 := '"';
892 QUOTE2 := to_multi_byte('"');
893 thechar := substr(value, 1, 1);
894 if thechar = QUOTE1 or thechar = QUOTE2 then
895 --
896 -- Minimum acceptable string is "".
897 --
898 clen := length(value);
899 if clen = 1 then
900 return FALSE;
901 end if;
902 --
903 -- Check for end-quote.
904 --
905 thechar := substr(value, clen, 1);
906 if thechar = QUOTE1 or thechar = QUOTE2 then
907 --
908 -- Quotes must be paired within the string.
909 --
910 if clen > 2 then
911 --
912 -- Strip off start and end quotes.
913 --
914 chunk := substr(value, 2, clen - 2);
915 while chunk is not null and
916 instr(chunk, QUOTE1) <> 0 and
917 instr(chunk, QUOTE2) <> 0 loop
918 thechar := substr(chunk, 1, 1);
919 if thechar = QUOTE1 or thechar = QUOTE2 then
920 thechar := substr(chunk, 2, 1);
921 if thechar is not null and
922 (thechar = QUOTE1 or thechar = QUOTE2) then
923 --
924 -- Skip the paired quotes.
925 --
926 chunk := substr(chunk, 3);
927 else
928 --
929 -- Error because of unpaired quote.
930 --
931 return FALSE;
932 end if;
933 else
934 --
935 -- Skip the first character.
936 --
937 chunk := substr(chunk, 2);
938 end if;
939 end loop;
940 end if;
941 --
942 -- It's got here so it's valid.
943 --
944 return TRUE;
945 end if;
946 --
947 -- No end-quote found.
948 --
949 return FALSE;
950 end if;
951
952 -- Set up the PL/SQL exclusion characters.
953 PLSQL_EXCLUDE := '"$#' || to_multi_byte( '"$#' );
954
955 -- Save the length (in characters) of the input string.
956 clen := length( value );
957
958 -- Check that the string does not contain any excluded PL/SQL
959 -- characters.
960 match := PADCH;
961 match := lpad( match, length( PLSQL_EXCLUDE ), PADCH );
962 exchk := translate( value, PLSQL_EXCLUDE, match );
963 exchk := replace( exchk, PADCH, '' );
964 if ( exchk is null or length( exchk ) < clen ) then
965 return( FALSE );
966 end if;
967
968 -- Check the name string chunk by chunk. Need to do this chunk
969 -- by chunk because string can be longer than the PL/SQL allows.
970
971 -- Set up information for the first chunk to be checked.
972 first := TRUE;
973 spos := 1;
974 epos := clen;
975 chunk := value || MUCK;
976
977 while ( spos <= clen ) loop
978 -- Make sure that the chunk is short enough for PL/SQL to
979 -- handle.
980 while ( lengthb( chunk ) > MAX_PLSQL_LEN ) loop
981 epos := ( spos + epos ) / 2;
982 if ( first ) then
983 -- Append MUCK to make sure that the start of the name
984 -- is okay.
985 chunk := substr( value, spos, epos - spos + 1 ) || MUCK;
986 else
987 -- Prepend MUCK because the chunk may not start with a
988 -- letter.
989 chunk := MUCK || substr( value, spos, epos - spos + 1 );
993 if ( first ) then
990 end if;
991 end loop;
992
994 first := FALSE;
995 end if;
996
997 -- Build a dynamic SQL statement using the name as a variable
998 -- name. This will syntax check the name as a valid PL/SQL name.
999 statement := 'DECLARE ' || chunk || ' NUMBER;BEGIN ' ||
1000 chunk || ':=1;END;';
1001 l_cursor_id := dbms_sql.open_cursor;
1002 dbms_sql.parse( l_cursor_id, statement, dbms_sql.native );
1003 dbms_sql.close_cursor( l_cursor_id );
1004
1005 -- Move onto next the chunk.
1006 spos := epos + 1;
1007 epos := clen;
1008 if ( spos <= clen ) then
1009 -- Prepend MUCK because the chunk may not start
1010 -- with a letter.
1011 chunk := MUCK || substr( value, spos, epos - spos + 1 );
1012 end if;
1013 end loop;
1014
1015 -- Exited the loop without problems, so value is good.
1016 return( TRUE );
1017 else
1018 -- String is null, zero length, or too long.
1019 return( FALSE );
1020 end if;
1021
1022 -- Exception handler to catch errors in the dynamic SQL ie.
1023 -- the syntax of the chunk being tested is invalid.
1024 exception
1025 when others then
1026 begin
1027 --
1028 -- Make sure that the cursor was closed.
1029 --
1030 if ( dbms_sql.is_open( l_cursor_id ) ) then
1031 dbms_sql.close_cursor( l_cursor_id );
1032 end if;
1033 return( FALSE );
1034 end;
1035 end chkdbi;
1036 --
1037 -------------------------------- chknacha -------------------------------
1038 /*
1039 NAME
1040 chkknacha - check legal NACHA string.
1041 DESCRIPTION
1042 Checks that inputs used for NACHA only contain
1043 a certain defined range of characters. These are:
1044 0-9, A-Z (upper case), blank, asterisk, ampersand,
1045 comma, hyphen, decimal and dollar.
1046 NOTES
1047 Uses translate to check for illegal characters.
1048 */
1049 procedure chknacha
1050 (
1051 value in out nocopy varchar2, -- the name to check.
1052 result out nocopy boolean -- result of the formatting.
1053 ) is
1054 trres varchar(240); -- result from the translate statement.
1055 legal varchar(100); -- holds list of legal characters.
1056 match varchar(100); -- holds match characters for translate.
1057 ALPHA constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
1058 NUMERIC constant varchar(10) := '0123456789';
1059 SPECIAL constant varchar(8) := '*&,-.$_ ';
1060 LEGCHAR constant varchar(1) := '*';
1061 begin
1062 -- convert any alpha characters to upper case.
1063 value := nls_upper(value);
1064 -- build up list of legal characters for first character.
1065 legal := ALPHA;
1066 -- now do a translate on the first character of value.
1067 trres := translate(substr(value,1,1),legal,LEGCHAR);
1068 if(nvl(trres,LEGCHAR) <> LEGCHAR) then
1069 result := FALSE;
1070 return;
1071 end if;
1072 -- if string is longer than one character,
1073 -- check the full legal list.
1074 if(length(value) > 1) then
1075 legal := ALPHA || NUMERIC || SPECIAL;
1076 match := lpad(LEGCHAR,length(legal),LEGCHAR);
1077 trres := translate(substr(value,2),legal,LEGCHAR);
1078 trres := replace(trres,LEGCHAR,'');
1079 -- if all characters in value are legal, trres should be null.
1080 if(trres is not null) then
1081 result := FALSE;
1082 return;
1083 end if;
1084 end if;
1085 end chknacha;
1086 --
1087 ------------------------ chk_half_kana --------------------------------
1088 procedure chk_half_kana(value in varchar2,
1089 result out nocopy boolean) is
1090 l_charset varchar2(64) := substr(userenv('language'),instr(userenv('language'),'.')+1);
1091 l_strlen number := length(value);
1092 l_ch varchar2(5);
1093 l_correct BOOLEAN := TRUE;
1094 i number := 1;
1095 begin
1096 --
1097 -- make sure that all the characters are half kana
1098 --
1099 while i <= l_strlen and l_correct loop
1100 l_ch := substr(value, i, 1);
1101 --
1102 -- Check if characters are valid against the characterset.
1103 --
1104 if l_charset='JA16SJIS' then
1105 if not ascii(l_ch) between 32 and 126 and
1106 not ascii(l_ch) between 161 and 223 then
1107 l_correct := FALSE;
1108 end if;
1109 elsif l_charset='JA16EUC' then
1110 if not ascii(l_ch) between 32 and 126 and
1111 not ascii(l_ch) between 36513 and 36575 then
1112 l_correct := FALSE;
1113 end if;
1114 elsif (l_charset='UTF8' or l_charset='AL32UTF8') then
1115 if not ascii(l_ch) between 32 and 126 and
1116 not ascii(l_ch) between 15711649 and 15711679 and
1117 not ascii(l_ch) between 15711872 and 15711903 then
1118 l_correct := FALSE;
1119 end if;
1120 else
1121 -- (Bug 1477718)
1122 -- Exit and return true when another characterset is used.
1123 --
1124 exit;
1125 end if;
1126 i := i + 1;
1127 end loop;
1128 --
1129 -- Set out variable
1130 --
1131 result:=l_correct;
1132 --
1133 end chk_half_kana;
1137 -- start by checking if the input is allowed to be null.
1134 --
1135 begin
1136 rgeflg := RGE_SUCC; -- start by saying range checking succeeded.
1138 if(nullok = 'N' and value is null) then
1139 hr_utility.set_message(801,'HR_51159_INVAL_VALUE_FORMAT');
1140 hr_utility.raise_error;
1141 end if;
1142 -- if ok to be null and value is null, then return immediately.
1143 if(nullok = 'Y' and value is null) then
1144 output := NULL;
1145 return;
1146 end if;
1147 -- Choose correct action for format specifier.
1148 if(format = 'CHAR' or format = 'C') then
1149 -- we can have minimum and maximum values.
1150 if(minimum is not null) then
1151 if(value < minimum) then
1152 rgeflg := RGE_FAIL;
1153 end if;
1154 end if;
1155 if(maximum is not null) then
1156 if(value > maximum) then
1157 rgeflg := RGE_FAIL;
1158 end if;
1159 end if;
1160 output := value;
1161 elsif(format = 'UPPER') then
1162 value := nls_upper(value);
1163 output := value;
1164 elsif(format = 'LOWER') then
1165 value := nls_lower(value);
1166 output := value;
1167 elsif(format = 'INITCAP') then
1168 value := nls_initcap(value);
1169 output := value;
1170 --
1171 elsif(format = 'M' or format = 'MONEY') then
1172 chkmoney(value,output,curcode,minimum,maximum,rgeflg,result,FALSE);
1173 if(result = FALSE) then
1174 hr_utility.set_message(801,'HR_51152_INVAL_MON_FORMAT');
1175 hr_utility.raise_error;
1176 end if;
1177 elsif(format = 'I' or format = 'N' or format = 'NUMBER'
1178 or format = 'ND') then
1179 chknum(value,output,minimum,maximum,rgeflg,format,result,FALSE);
1180 if(result = FALSE) then
1181 hr_utility.set_message(801,'HR_51153_INVAL_NUM_FORMAT');
1182 hr_utility.raise_error;
1183 end if;
1184 --output is in canonical format
1185 --output := fnd_number.number_to_canonical( to_number(value) );
1186 elsif(format = 'H_HH' or format = 'H_HHMM' or
1187 format = 'H_HHMMSS') then
1188 chkhours(value,format,output,minimum,maximum,rgeflg,result);
1189 if(result = FALSE) then
1190 hr_utility.set_message(801,'HR_51153_INVAL_NUM_FORMAT');
1191 hr_utility.raise_error;
1192 end if;
1193 elsif(format = 'TIMES' or format = 'T') then
1194 chktime(value, minimum, maximum, rgeflg, result);
1195 if(result = FALSE) then
1196 hr_utility.set_message(801,'HR_51154_INVAL_TIME_FORMAT');
1197 hr_utility.raise_error;
1198 end if;
1199 output := value;
1200
1201 --elsif(format = 'D_DDMONYY' or format = 'D_DDMONYYYY' or
1202 --format = 'D_DDMMYY' or format = 'D_DDMMYYYY' or
1203 --format = 'D_MMDDYY' or format = 'D_MMDDYYYY' or
1204 --format = 'DATE') then
1205 elsif (format = 'D' or format = 'DATE') then
1206 chkdate(value,format,output,minimum,maximum,rgeflg,result);
1207 if(result = FALSE) then
1208 hr_utility.set_message(801,'HR_51155_INVAL_DATE_FORMAT');
1209 hr_utility.raise_error;
1210 end if;
1211 elsif(format = 'H_DECIMAL1' or format = 'H_DECIMAL2'
1212 or format = 'H_DECIMAL3' or format = 'HOURS') then
1213 chkdech(value,format,minimum,maximum,rgeflg,result,FALSE);
1214 if(result = FALSE) then
1215 hr_utility.set_message(801,'HR_51153_INVAL_NUM_FORMAT');
1216 hr_utility.raise_error;
1217 end if;
1218 --output is in canonical format
1219 output := fnd_number.number_to_canonical( to_number(value) );
1220 elsif(format = 'DB_ITEM_NAME') then
1221 if(chkdbi(value) = FALSE) then
1222 hr_utility.set_message(801,'HR_51156_INVAL_NUM_FORMAT');
1223 hr_utility.raise_error;
1224 end if;
1225 output := value;
1226 elsif(format = 'PAY_NAME') then
1227 if(chkpay(value) = FALSE) then
1228 hr_utility.set_message(801,'HR_51157_INVAL_PAY_NAME_FORMAT');
1229 hr_utility.raise_error;
1230 end if;
1231 output := value;
1232 elsif(format = 'NACHA') then
1233 chknacha(value,result); -- check for legal NACHA characters
1234 if(result = FALSE) then
1235 hr_utility.set_message(801,'HR_51158_INVAL_NACHA_FORMAT');
1236 hr_utility.raise_error;
1237 end if;
1238 output := value;
1239 elsif(format = 'KANA'or format = 'K') then
1240 chk_half_kana(value,result);
1241 if(result = FALSE) then
1242 hr_utility.set_message(801, 'HR_72021_PER_INVALID_KANA');
1243 hr_utility.raise_error;
1244 end if;
1245 else
1246 -- invalid format.
1247 hr_utility.set_message(801,'HR_51159_INVAL_VALUE_FORMAT');
1248 hr_utility.raise_error;
1249 end if;
1250 end checkformat;
1251 --
1252 --------------------------- changeformat -----------------------------------
1253 /*
1254 NAME
1255 changeformat - converts from internal to external formats.
1256 DESCRIPTION
1257 Is called when you need to convert from a format that is
1258 held in one format internally but which needs to be
1259 displayed in another format.
1260 NOTES
1261 Currently, the following formats require conversion:
1262 date formats, money and H_HHMM/H_HHMMSS.
1263 */
1264 function changeformat
1265 (
1266 input in varchar2, -- the input format.
1267 format in varchar2, -- indicates the format to convert to.
1268 curcode in varchar2 -- currency code for money format.
1269 ) return varchar2 is
1270 --
1271 value varchar2(240); -- needed when calling chkdech.
1272 rgeflg varchar2(1); -- needed when calling chkdech.
1276 ---------------------------- to_money ----------------------------------
1273 result boolean; -- needed when calling chkdech.
1274 output varchar2(240); -- the output format.
1275 --
1277 /*
1278 NAME
1279 to_money - converts internal currency format to external one.
1280 DESCRIPTION
1281 Converts a decimal number (in which currency is held)
1282 to the external format.
1283 NOTES
1284 Currently, the internal and external formats are the same,
1285 but this function exists as a stub for later use.
1286 */
1287 function to_money
1288 (
1289 input in varchar2, -- the input to format.
1290 curcode in varchar2 -- the currency code.
1291 ) return varchar2 is
1292 value varchar2(240);
1293 output varchar2(240);
1294 rgeflg varchar2(10);
1295 result boolean;
1296 begin
1297 -- use the checkformat procedure to handle format.
1298 value := input;
1299 chkmoney(value,output,curcode,null,null,rgeflg,result,TRUE);
1300 return(value);
1301 end to_money;
1302 --
1303 ---------------------------- to_hours -----------------------------------
1304 /*
1305 NAME
1306 to_hours - converts decimal hours to hours formats.
1307 DESCRIPTION
1308 This converts from decimal into either H_HHMM or H_HHMMSS.
1309 NOTES
1310 <none>
1311 */
1312 function to_hours(input in varchar2, format in varchar2)
1313 return varchar2 is
1314 hours varchar2(100); -- holds the hours string.
1315 mins number; -- holds minutes (and seconds) string.
1316 begin
1317 -- separate hours string.
1318 hours := trunc(fnd_number.canonical_to_number( input ));
1319 mins := abs(round((fnd_number.canonical_to_number( input ) - hours)
1320 * 3600));
1321 --
1322 -- Bugfix 4269787
1323 -- If mins has been rounded to 3600 we have 1 hour!
1324 if mins = 3600 then
1325 hours := hours+1;
1326 mins := 0;
1327 end if;
1328 --
1329 -- Bugfix 3650335
1330 -- Ensure the sign of the input is maintained.
1331 if hours = '0' and mins > 0
1332 and sign(fnd_number.canonical_to_number(input)) = -1 then
1333 --
1334 -- Prefix the hours value with a minus sign as this will have been
1335 -- lost in the conversion process.
1336 --
1337 hours := '-'||hours;
1338 --
1339 end if;
1340 --
1341 -- process diffferently for H_HHMM and H_HHMMSS.
1342 if(format = 'H_HHMM') then
1343 return(hours || ':' ||
1344 to_char(to_date(to_char(mins),'SSSSS'),'MI'));
1345 else
1346 return(hours || ':' ||
1347 to_char(to_date(to_char(mins),'SSSSS'),'MI:SS'));
1348 end if;
1349 end to_hours;
1350 --
1351 ---------------------------- conv_date ----------------------------------
1352 /*
1353 NAME
1354 conv_date - convert internal to displayed date format.
1355 DESCRIPTION
1356 Dates are converted from the universal internal format
1357 of 'DD-MON-YYYY' to whatever is specified by 'format'.
1358 NOTES
1359 <none>
1360 */
1361 function conv_date(input in varchar2,format in varchar2)
1362 return varchar2 is
1363 l_date date ;
1364 oraformat varchar2(11);
1365 begin
1366 if(format = 'D' or format = 'DATE') then
1367 l_date := fnd_date.canonical_to_date(input) ;
1368 else
1369 oraformat := 'UNKNOWN'; -- this should cause failure.
1370 end if;
1371 --
1372 -- now convert to external format.
1373 return(fnd_date.date_to_displaydate(l_date));
1374 end conv_date;
1375 --
1376 begin
1377 if(input is null) then
1378 output := NULL; -- if input is null, do not do any processing.
1379 -- main statement to do the conversion.
1380 else
1381 if(format = 'H_HHMM' or format = 'H_HHMMSS') then
1382 output := to_hours(input,format);
1383 elsif(format = 'M' or format = 'MONEY') then
1384 output := to_money(input,curcode);
1385 elsif(format like 'H_DECIMAL%') then
1386 value := input;
1387 chkdech(value,format,null,null,rgeflg,result,TRUE);
1388 output := value;
1389 elsif(format = 'N' or format = 'NUMBER' or format = 'ND') then
1390 value := input;
1391 chknum(value,output,null,null,rgeflg,format,result,TRUE);
1392 output := value;
1393 elsif(format = 'INTEGER' or format = 'I' ) then
1394 value := input;
1395 chknum(value,output,null,null,rgeflg,format,result,TRUE);
1396 output := value;
1397 elsif(format = 'D' or format = 'DATE') then
1398 -- convert to required external format.
1399 output := conv_date(input,format);
1400 else
1401 -- all other formats remain the same.
1402 output := input;
1403 end if;
1404 end if;
1405 return(output);
1406 end changeformat;
1407 --
1408 --------------------------- changeformat -----------------------------------
1409 /*
1410 NAME
1411 changeformat - converts from internal to external formats.
1412 DESCRIPTION
1413 Is called when you need to convert from a format that is
1414 held in one format internally but which needs to be
1415 displayed in another format.
1416 NOTES
1417 Currently, the following formats require conversion:
1418 date formats, money and H_HHMM/H_HHMMSS.
1419 */
1420 procedure changeformat
1421 (
1422 input in varchar2, -- the input format.
1426 ) is
1423 output out nocopy varchar2, -- the output formated
1424 format in varchar2, -- indicates the format to convert to.
1425 curcode in varchar2 -- currency code for money format.
1427 begin
1428 output := changeformat(input, format, curcode);
1429 end changeformat;
1430 --
1431 end hr_chkfmt;
[Home] [Help]