[Home] [Help]
PACKAGE BODY: APPS.HR_DBCHKFMT
Source
1 package body hr_dbchkfmt as
2 /* $Header: pydbckft.pkb 115.4 2003/06/12 13:54:40 irgonzal ship $ */
3 ------------------------------------------------------------------------------
4 -- Private Global Definitions
5 -----------------------------------------------------------------------------
6 g_package varchar2(33) := ' hr_dbchkfmt.'; -- Global package name
7
8
9 -- PRIVATE PROCEDURES
10 ------------------------- is_upper ----------------------------
11 --
12 -- Name
13 -- is _upper - checks whether the input is upper case.
14 --
15 Procedure is_upper(p_str in varchar2,
16 p_result out nocopy boolean) is
17 l_upper_str varchar2(60) := upper(p_str);
18 l_proc varchar2(72) := g_package || 'is_upper';
19 begin
20 hr_utility.set_location ('Entering:'||l_proc,5);
21 if (l_upper_str = p_str) then
22 hr_utility.set_location (l_proc,10);
23 p_result:= TRUE;
24 else
25 hr_utility.set_location(l_proc,15);
26 p_result := FALSE;
27 end if;
28 end is_upper;
29 ----------------------- is _lower -------------------------------------
30 -- NAME
31 -- is_lower - checks whether the input is lower case
32 --
33 procedure is_lower (p_str in varchar2,
34 p_result out nocopy boolean) is
35 l_lower_str varchar2(80) ;
36 l_proc varchar2(72) := g_package ||'is_lower';
37 begin
38 hr_utility.set_location ('Entering:'||l_proc,5);
39 l_lower_str := lower(p_str);
40 if (l_lower_str = p_str) then
41 hr_utility.set_location (l_proc,10);
42 p_result := TRUE;
43 else
44 hr_utility.set_location (l_proc,15);
45 p_result := FALSE;
46 end if;
47 end is_lower;
48 -------------------------- is_initcap ----------------------------------
49 -- NAME
50 -- is_initcap - checks whether the input is in initcap form.
51 --
52 procedure is_initcap (p_str in varchar2,
53 p_result out nocopy boolean) is
54 l_initcap_str varchar2(50) ;
55 l_proc varchar2(72) := g_package || 'is_initcap';
56 begin
57 hr_utility.set_location ('Entering:'||l_proc,5);
58 l_initcap_str := initcap(p_str);
59 if (l_initcap_str = p_str) then
60 hr_utility.set_location (l_proc,10);
61 p_result:= TRUE;
62 else
63 hr_utility.set_location (l_proc,15);
64 p_result:= FALSE;
65 end if;
66 end is_initcap;
67 --
68 --
69 ------------------------- is_db_format -------------------------------
70 -- << overloaded >>
71 --
72 procedure is_db_format
73 (
74 p_value in varchar2,
75 p_arg_name in varchar2,
76 p_format in varchar2,
77 p_curcode in varchar2 default null
78 ) is
79
80 l_output varchar2(60) := null;
81 begin
82
83 is_db_format
84 (
85 p_value,
86 l_output,
87 p_arg_name,
88 p_format,
89 p_curcode
90 );
91
92 end is_db_format;
93 --
94 --
95 ------------------------- is_db_format --------------------------------
96 -- Name
97 -- is_db_format - Checks for valid database format.
98 -- Description
99 -- This checks the validity of the input format by comparing it
100 -- with equivalent database format.
101 --
102 procedure is_db_format
103 (
104 p_value in varchar2,
105 p_formatted_output in out nocopy varchar2, -- #2734822
106 p_arg_name in varchar2,
107 p_format in varchar2,
108 p_curcode in varchar2 default null
109 ) is
110 --
111 l_proc varchar2(72) := g_package || 'is_db_format';
112 l_result boolean;
113 l_output varchar2(60); --dummy to hold output from checkformat.
114 l_value varchar2(60); --dummy to hold value for checkformat.
115 l_rgeflg varchar2(1); --dummy to hold rangeflag from checkformat.
116 -------------------------------dbchkdbi ---------------------------------
117 -- NAME
118 -- dbchkdbi - check format of database item name.
119 -- DESCRIPTION
120 -- Check that db item name only contains legal characters.
121 -- The legal charaters are as follows:
122 -- First character : upper or lower case alphabetic characters.
123 -- Subsequent chars: alpha, numeric or underscore.
124 -- NOTES
125 -- Uses the translate function to spot illegal characters.
126 -- This function is very similar to chkpay, but has been
127 -- kept separate in case more differences between the
128 -- format models eventually emerge.
129 --
130 procedure dbchkdbi
131 (
132 p_value in varchar2,
133 p_result out nocopy boolean
134 ) is
135 l_proc varchar2(72) := g_package || 'dbchkdbi';
136 l_result varchar(240); -- result from the translate statement.
137 l_legal varchar(100); -- holds list of legal characters.
138 l_match varchar(100); -- holds match characters for translate.
139 l_ALPHA constant
140 varchar2(52):='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
141 l_NUMERIC constant varchar2(10) := '0123456789';
142 l_SPECIAL constant varchar2(2) := '_';
143 l_LEGCHAR constant varchar2(1) := 'A'; -- the legal character.
144 begin
145 hr_utility.set_location ('Entering:'||l_proc,5);
146 -- build up list of legal characters for first character.
147 l_legal := l_ALPHA;
148 -- now do a translate on the first character of value.
149 l_result := translate(substr(p_value,1,1),l_legal,l_LEGCHAR);
150 if(nvl(l_result,l_LEGCHAR) <> l_LEGCHAR) then
151 hr_utility.set_location (l_proc,10);
152 p_result :=FALSE;
153 end if;
154 -- if string is longer than one character,
155 -- check the full legal list.
156 if(length(p_value) > 1) then
157 l_legal := l_ALPHA || l_NUMERIC || l_SPECIAL;
158 l_match := lpad(l_LEGCHAR,length(l_legal),l_LEGCHAR);
159 l_result := translate(substr(p_value,2),l_legal,l_match);
160 l_result := replace(l_result,l_LEGCHAR,'');
161 -- if all characters were legal, expect result to be null.
162 if(l_result is not null) then
163 hr_utility.set_location (l_proc,15);
164 p_result := FALSE;
165 end if;
166 end if;
167 hr_utility.set_location ('Leaving'||l_proc,20);
168 p_result := TRUE;
169 exception
170 when others then
171 p_result := FALSE;
172 end dbchkdbi;
173 ----------------------------- dbchknacha -------------------------------
174 -- NAME
175 -- dbchkknacha - check legal NACHA string.
176 -- DESCRIPTION
177 -- Checks that inputs used for NACHA only contain
178 -- a certain defined range of characters. These are:
179 -- 0-9, A-Z (upper case), blank, asterisk, ampersand,
180 -- comma, hyphen, decimal and dollar.
181 -- NOTES
182 -- Uses translate to check for illegal characters.
183 --
184 procedure dbchknacha
185 (
186 p_value in varchar2, -- the name to check.
187 p_result out nocopy boolean -- result of the formatting.
188 ) is
189 l_proc varchar2(72) := g_package ||'dbchknacha';
190 l_value varchar2(240); -- local parameter to contain the name.
191 l_trres varchar(240); -- result from the translate statement.
192 l_legal varchar(100); -- holds list of legal characters.
193 l_match varchar(100); -- holds match characters for translate.
194 l_ALPHA constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
195 l_NUMERIC constant varchar(10) := '0123456789';
196 l_SPECIAL constant varchar(8) := '*&,-.$_ ';
197 l_LEGCHAR constant varchar(1) := '*';
198 begin
199 hr_utility.set_location ('Entering:'||l_proc,5);
200 -- convert any alpha characters to upper case.
201 l_value:=p_value;
202 l_value := nls_upper(l_value);
203 -- build up list of legal characters for first character.
204 l_legal := l_ALPHA;
205 -- now do a translate on the first character of value.
206 l_trres := translate(substr(l_value,1,1),l_legal,l_LEGCHAR);
207 if(nvl(l_trres,l_LEGCHAR) <> l_LEGCHAR) then
208 p_result := FALSE;
209 hr_utility.set_location (l_proc,10);
210 return;
211 end if;
212 -- if string is longer than one character,
213 -- check the full legal list.
214 if(length(l_value) > 1) then
215 l_legal := l_ALPHA || l_NUMERIC || l_SPECIAL;
216 l_match := lpad(l_LEGCHAR,length(l_legal),l_LEGCHAR);
217 l_trres := translate(substr(l_value,2),l_legal,l_LEGCHAR);
218 l_trres := replace(l_trres,l_LEGCHAR,'');
219 -- if all characters in value are legal, trres should be null.
220 if(l_trres is not null) then
221 p_result := FALSE;
222 hr_utility.set_location (l_proc,15);
223 return;
224 end if;
225 end if;
226 hr_utility.set_location ('LEAVING ' || l_proc, 20);
227 exception
228 when others then
229 p_result := FALSE;
230 end dbchknacha;
231 ---------------------------------- dbchkpay ---------------------------------
232 -- NAME
233 -- dbchkpay - check payroll name does not contain illegal characters.
234 -- DESCRIPTION
235 -- Used to ensure that a name passed in only comprises of:
236 -- First character : alpha characters (upper or lower case).
237 -- Subsequent chars: alpha, space and underscore.
238 -- NOTES
239 -- Use the translate function to check for illegal chars.
240 --
241 procedure dbchkpay
242 (
243 p_value in varchar2, -- the name to check.
244 p_result out nocopy boolean
245 ) is
246 l_proc varchar2(72) := g_package || 'dbchkpay';
247 l_result varchar(240); -- result from the translate statement.
248 l_legal varchar2(100); -- holds list of legal characters.
249 l_match varchar2(100); -- hold matching characters for translate.
250 l_ALPHA constant
251 varchar2(52):='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
252 l_NUMERIC constant varchar(10) := '0123456789';
253 l_SPECIAL constant varchar(2) := ' _';
254 l_LEGCHAR constant varchar(1) := 'A';
255 begin
256 hr_utility.set_location ('Entering:'||l_proc,5);
257 -- build up list of legal characters for first character.
258 l_legal := l_ALPHA;
259 -- now do a translate on the first character of value.
260 l_result := translate(substr(p_value,1,1),l_legal,l_LEGCHAR);
261 if(nvl(l_result,l_LEGCHAR) <> l_LEGCHAR) then
262 hr_utility.set_location (l_proc,10);
263 p_result := FALSE;
267 if(length(p_value) > 1) then
264 end if;
265 -- if string is longer than one character,
266 -- check the full legal list.
268 l_legal := l_ALPHA || l_NUMERIC || l_SPECIAL;
269 l_match := lpad(l_LEGCHAR,length(l_legal),l_LEGCHAR);
270 -- build match string.
271 l_result := translate(substr(p_value,2),l_legal,l_LEGCHAR);
272 l_result := replace(l_result,l_LEGCHAR,'');
273 -- if string contains legal characters, result should be null.
274 if(l_result is not null) then
275 hr_utility.set_location (l_proc,15);
276 p_result := FALSE;
277 end if;
278 end if;
279 hr_utility.set_location ('Leaving:'||l_proc,20);
280 p_result:= TRUE;
281 exception
282 when others then
283 p_result := FALSE;
284 end dbchkpay;
285 -------------------------------------------------------------------------
286 --
287 -- This is the main body of the is_db_format
288 --
289 ------------------------------------------------------------------------
290 begin
291 hr_utility.set_location ('Entering:'||l_proc,5);
292 hr_api.mandatory_arg_error(l_proc,p_arg_name,p_value);
293 l_result:= TRUE;
294 l_value := p_value;
295 -- Choose correct action for format specifier.
296 if(p_format = 'UPPER') then
297 is_upper(p_value,l_result);
298 if(l_result = FALSE) then
299 hr_utility.set_message (801, 'HR_7909_CHECK_FMT_UPPER');
300 hr_utility.set_message_token('ARG_NAME', p_arg_name);
301 hr_utility.set_message_token('ARG_VALUE', p_value);
302 hr_utility.raise_error;
303 end if;
304 elsif(p_format = 'LOWER' ) then
305 is_lower(p_value,l_result);
306 if(l_result = FALSE ) then
307 hr_utility.set_message (801, 'HR_7910_CHECK_FMT_LOWER');
308 hr_utility.set_message_token('ARG_NAME', p_arg_name);
309 hr_utility.set_message_token('ARG_VALUE', p_value);
310 hr_utility.raise_error;
311 end if;
312 elsif(p_format = 'INITCAP' ) then
313 is_initcap(p_value,l_result);
314 if(l_result = FALSE) then
315 hr_utility.set_message (801, 'HR_7911_CHECK_FMT_INITCAP');
316 hr_utility.set_message_token('ARG_NAME', p_arg_name);
317 hr_utility.set_message_token('ARG_VALUE', p_value);
318 hr_utility.raise_error;
319 end if;
320 elsif(p_format = 'M' or p_format = 'MONEY') then
321 begin
322 hr_chkfmt.checkformat(l_value, p_format, l_output, NULL, NULL, 'Y', l_rgeflg, p_curcode);
323 exception
324 when others then
325 hr_utility.set_message(801,'HR_7912_CHECK_FMT_MONEY');
326 hr_utility.set_message_token('ARG_NAME', p_arg_name);
327 hr_utility.set_message_token('ARG_VALUE', p_value);
328 hr_utility.raise_error;
329 end;
330 elsif(p_format = 'I' or p_format = 'INTEGER' or p_format = 'H_HH'
331 or p_format = 'NUMBER' or p_format = 'ND' or p_format = 'N') then
332 begin
333 hr_chkfmt.checkformat(l_value, p_format, l_output, NULL, NULL, 'Y', l_rgeflg, p_curcode);
334 exception
335 when others then
336 hr_utility.set_message(801,'HR_7914_CHECK_FMT_NUMBER');
337 hr_utility.set_message_token('ARG_NAME', p_arg_name);
338 hr_utility.set_message_token('ARG_VALUE', p_value);
339 hr_utility.raise_error;
340 end;
341 elsif(p_format = 'TIMES' or p_format = 'T') then
342 begin
343 hr_chkfmt.checkformat(l_value, p_format, l_output, NULL, NULL, 'Y', l_rgeflg, p_curcode);
344 exception
345 when others then
346 hr_utility.set_message(801,'HR_7916_CHECK_FMT_HHMM');
347 hr_utility.set_message_token('ARG_NAME', p_arg_name);
348 hr_utility.set_message_token('ARG_VALUE', p_value);
349 hr_utility.raise_error;
350 end;
351 elsif(p_format = 'H_DECIMAL1' or p_format = 'H_DECIMAL2' or p_format = 'H_HHMM' or p_format = 'H_HHMMSS'
352 or p_format = 'H_DECIMAL2' or p_format = 'H_DECIMAL3' or p_format = 'HOURS') then
353 begin
354 hr_chkfmt.checkformat(l_value, p_format, l_output, NULL, NULL, 'Y', l_rgeflg, p_curcode);
355 exception
356 when others then
357 hr_utility.set_message(801,'HR_7918_CHECK_FMT_HDECIMAL');
358 hr_utility.set_message_token('ARG_NAME', p_arg_name);
359 hr_utility.set_message_token('ARG_VALUE', p_value);
360 hr_utility.set_message_token('DECIMAL_POINT', '1');
361 hr_utility.raise_error;
362 end;
363 elsif(p_format = 'DB_ITEM_NAME') then
364 dbchkdbi(p_value,l_result);
365 if(l_result = FALSE) then
366 hr_utility.set_message(801,'HR_7919_CHECK_FMT_HR_NAME');
367 hr_utility.set_message_token('ARG_NAME', p_arg_name);
368 hr_utility.set_message_token('ARG_VALUE', p_value);
369 hr_utility.raise_error;
370 end if;
371 elsif(p_format = 'PAY_NAME') then
372 dbchkpay(p_value,l_result);
373 if(l_result = FALSE) then
374 hr_utility.set_message(801,'HR_7919_CHECK_FMT_HR_NAME');
375 hr_utility.set_message_token('ARG_NAME', p_arg_name);
376 hr_utility.set_message_token('ARG_VALUE', p_value);
377 hr_utility.raise_error;
378 end if;
379 elsif(p_format = 'NACHA') then
380 dbchknacha(p_value,l_result); -- check for legal NACHA characters
381 if(l_result = FALSE) then
382 hr_utility.set_message(801,'HR_7930_CHECK_FMT_NACHA');
383 hr_utility.set_message_token('ARG_NAME', p_arg_name);
384 hr_utility.set_message_token('ARG_VALUE', p_value);
385 hr_utility.raise_error;
386 end if;
387 else
388 -- invalid format.
389 hr_utility.set_message(801,'HR_7944_CHECK_FMT_BAD_FORMAT');
390 hr_utility.raise_error;
391 end if;
392 --
393 p_formatted_output := l_output; -- #2734822
394 --
395 hr_utility.set_location ('Leaving:'||l_proc,10);
396 end is_db_format;
397 end hr_dbchkfmt;