1 PACKAGE BODY pay_us_report_data_validation AS
2 /* $Header: payusdatavalid.pkb 115.3 2003/12/09 17:04 sodhingr noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_report_data_validation
12
13 Purpose
14 The purpose of this package is to validate EIN, SSN etc
15 to support the generation of magnetic tape W2 / SQWL reports
16 for US legilsative requirements.
17
18 Notes
19 Referenced By: Package pay_us_reporting_utils_pkg
20
21 Notes
22
23 History
24 15-Jul-03 ppanda 115.0 Created
25 15-OCT-03 ppanda 115.2 2787752 EIN Validation for set of numbers
26 commented out.
27 3084344 SSN Validation for W-2 Mag changed
28 Invalid SSN would be 111-11-1111, 333-33-3333
29 first 3 digits 000 or 666
30 and last 4 digits 0000
31 09-DEC-03 sodhingr 115.3 3084344 changed the variable l_last_four_chars to take the
32 last four chars
33 */
34
35 /* Following is to validate EIN to support SQWL Reporting */
36 FUNCTION validate_SQWL_EIN( p_report_qualifier IN varchar2,
37 p_record_name IN varchar2,
38 p_input_2 IN varchar2,
39 p_input_4 IN varchar2,
40 p_validate IN varchar2,
41 p_err OUT nocopy boolean
42 ) return varchar2 IS
43 -- Local Variables
44 return_value varchar2(100);
45 BEGIN
46 IF p_report_qualifier = 'NY_SQWL' THEN
47 return_value := rpad(substr(replace(p_input_2,'-'),1,11),11,' ');
48 ELSE
49 return_value := lpad(substr(replace(p_input_2,'-'),1,9),9,0); /*Bug:2409031*/
50 END IF;
51 return return_value;
52 END validate_SQWL_EIN;
53
54 /* Following is to validate EIN to support W2 Reporting */
55
56 FUNCTION validate_W2_EIN( p_report_qualifier IN varchar2,
57 p_record_name IN varchar2,
58 p_input_2 IN varchar2,
59 p_input_4 IN varchar2,
60 p_validate IN varchar2,
61 p_err OUT nocopy boolean
62 ) return varchar2 IS
63 -- Local Variables
64 return_value varchar2(100);
65 l_description varchar2(50);
66 l_err boolean := FALSE;
67
68 BEGIN
69 /* IF the EIN starts with any of these numbers the exclude flag
70 should be set based on p_validate.*/
71 /* This validation is commented to fix Bug # 2787752
72 EIN should not be Validated for these starting Numbers
73
74 IF (( substr(p_input_2,1,2) = '00' ) OR
75 ( substr(p_input_2,1,2) = '07' ) OR
76 ( substr(p_input_2,1,2) = '08' ) OR
77 ( substr(p_input_2,1,2) = '09' ) OR
78 ( substr(p_input_2,1,2) = '10' ) OR
79 ( substr(p_input_2,1,2) = '17' ) OR
80 ( substr(p_input_2,1,2) = '18' ) OR
81 ( substr(p_input_2,1,2) = '19' ) OR
82 ( substr(p_input_2,1,2) = '20' ) OR
83 ( substr(p_input_2,1,2) = '26' ) OR
84 ( substr(p_input_2,1,2) = '27' ) OR
85 ( substr(p_input_2,1,2) = '28' ) OR
86 ( substr(p_input_2,1,2) = '29' ) OR
87 ( substr(p_input_2,1,2) = '30' ) OR
88 ( substr(p_input_2,1,2) = '40' ) OR
89 ( substr(p_input_2,1,2) = '49' ) OR
90 ( substr(p_input_2,1,2) = '50' ) OR
91 ( substr(p_input_2,1,2) = '60' ) OR
92 ( substr(p_input_2,1,2) = '69' ) OR
93 ( substr(p_input_2,1,2) = '70' ) OR
94 ( substr(p_input_2,1,2) = '78' ) OR
95 ( substr(p_input_2,1,2) = '79' ) OR
96 ( substr(p_input_2,1,2) = '80' ) OR
97 ( substr(p_input_2,1,2) = '89' ) OR
98 ( substr(p_input_2,1,2) = '90' ) )
99 */
100 IF p_input_2 IS NULL
101 THEN
102 --{
103 hr_utility.trace('ERROR: EIN is NULL');
104 l_description:='EIN '||substr(p_input_2,1,9)||
105 ' is Invalid. EIN cannot be NULL';
106 pay_core_utils.push_message(801,'PAY_INVALID_ER_FORMAT','P');
107 pay_core_utils.push_token('record_name',p_record_name);
108 pay_core_utils.push_token('name_or_number',p_input_4);
109 pay_core_utils.push_token('description',l_description);
110 l_err:=TRUE;
111 --}
112 END IF;
113 p_err := l_err;
114 /*Bug:2159881 */
115 return_value := rpad(substr(replace(replace(nvl(replace(p_input_2,' '),' '),'-'),'/'),1,9),9);
116 return return_value;
117 end validate_W2_EIN;
118 -- End EIN Validation for W2 reporting
119 --
120 /* Following is to validate SSN to support SQWL Reporting */
121 FUNCTION validate_SQWL_SSN(p_effective_date IN varchar2,
122 p_report_type IN varchar2,
123 p_format IN varchar2,
124 p_report_qualifier IN varchar2,
125 p_record_name IN varchar2,
126 p_input_1 IN varchar2,
127 p_input_2 IN varchar2,
128 p_input_3 IN varchar2,
129 p_input_4 IN varchar2,
130 p_input_5 IN varchar2,
131 p_validate IN varchar2,
132 p_err OUT nocopy boolean
133 ) return varchar2 IS
134 -- Local Variables
135 l_err boolean := FALSE;
136 return_value varchar2(100);
137 l_length number(10);
138 l_message varchar2(2000);
139 l_number_length number(10);
140 l_description varchar2(50);
141 l_input_2 varchar2(100);
142 l_ssn varchar2(50);
143
144 TYPE special_numbers is record(p_number_set varchar2(50));
145 special_number_record special_numbers;
146
147 TYPE ssn_special_number_rec is table of special_number_record%type
148 INDEX BY binary_integer;
149 ssn_check ssn_special_number_rec;
150 BEGIN
151 /* SSN valid check */
152
153 hr_utility.trace('Input SSN before Validation '||p_input_2);
154 IF ((p_report_qualifier = 'MN_SQWL') OR
155 (p_report_qualifier = 'GA_SQWL')) THEN
156 -- Character hypen, period and quotes are eliminated
157 l_ssn := replace(replace(replace(
158 replace(pay_us_reporting_utils_pkg.character_check(p_input_2),
159 ' '),'-'),'.'),'''');
160 ELSE
161 -- Character I is eliminated in addition to hypen, period and quotes
162 l_ssn := replace(replace(replace(replace(
163 replace(pay_us_reporting_utils_pkg.character_check(p_input_2),
164 ' '),'I'),'-'),'.'),'''');
165 END IF;
166
167 hr_utility.trace('SSN after eliminating special chars = '||l_ssn);
168 -- Validation for SSN starting with 8 or 9
169 -- When SSN starting with 8 or 9 a warning is logged
170 --
171 IF ((substr(l_ssn,1,1) = '8') OR (substr(l_ssn,1,1) = '9')) THEN
172 l_description:= 'Invalid SSN. SSN should not begin with '||
173 substr(l_ssn,1,1);
174 --l_err:=TRUE;
175 -- Bug # 2183859
176 -- This should be a warning instead of an error
177 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT_WARNING','A');
178 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
179 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
180 pay_core_utils.push_token('description',substr(l_description,1,50));
181 /* WARNING in RW record for employee 1234.SSN 912345697 is invalid.SSN
182 cannot begin with 9 */
183 hr_utility.trace('Warning in '||p_record_name||'for employee '||
184 p_input_4||l_description);
185 ELSE
186 -- Validation for Special numbers wrongly used as SSN
187 -- An error is logged for these numbers
188 ssn_check(1).p_number_set := '111111111';
189 ssn_check(2).p_number_set := '222222222';
190 ssn_check(3).p_number_set := '333333333';
191 ssn_check(4).p_number_set := '444444444';
192 ssn_check(5).p_number_set := '555555555';
193 ssn_check(6).p_number_set := '666666666';
194 ssn_check(7).p_number_set := '777777777';
195 ssn_check(8).p_number_set := '123456789';
196
197 FOR i in 1 .. 8 LOOP
198 IF l_ssn = ssn_check(i).p_number_set THEN
199 l_err:=TRUE;
200 l_description := 'Social Security '||l_ssn||' is Invalid.';
201 hr_utility.trace(l_description);
202 END IF;
203 END LOOP;
204 END IF; -- End of check for SSN starting with 8 or 9
205
206 -- For SQWL an error message is logged but record is not invalidated
207 -- when these numbers used as SSN
208 IF l_err THEN
209 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','A');
210 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
211 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
212 pay_core_utils.push_token('description',substr(l_description,1,50));
213 /* Error in RW record for employee 1234.SSN 912345697 is
214 invalid.SSN cannot begin with 9 */
215 hr_utility.trace('Error in '||p_record_name||'for employee '||
216 p_input_4||l_description);
217 return_value:= rpad(substr(l_ssn,1,9),9);
218 IF p_report_type = 'SQWL' THEN /*Bug:2309772. */
219 l_err := FALSE;
220 END IF;
221 ELSE
222 hr_utility.trace('Valid SSN');
223 IF l_ssn IS NULL THEN --SSN null check
224 return_value:= '000000000';
225 IF p_report_qualifier = 'AZ_SQWL' THEN
226 hr_utility.trace('SSN is BLANK.Padding spaces to 1 ');
227 return_value := rpad('1',9);
228 ELSE
229 l_description:= 'SSN is blank. Padded with zeros';
230 -- Bug # 2183859
231 -- This would be a informative warning
232 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT_WARNING','A');
233 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
234 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
235 pay_core_utils.push_token('description',substr(l_description,1,50));
236 /* WARNING in RW record for employee 1234.SSN is blank padded with zeros*/
237 hr_utility.trace('Warning in '||p_record_name||'for employee '||
238 p_input_4||l_description);
239 END IF;
240 -- If SSN is I after stripping hypen, period and quotes a warning message
241 -- is logged and I padded with blanks is reported as SSN
242 --
243 ELSIF l_ssn = 'I' THEN
244 return_value := rpad(l_ssn,9);
245 ELSE
246 -- If SSN is valid and Not Null
247 hr_utility.trace('Valid SSN after all checks = '||return_value);
248 return_value:= rpad(substr(l_ssn,1,9),9);
249 END IF; --SSN null check
250 END IF; -- SSN valid check
251 p_err := l_err;
252 return return_value;
253 END validate_SQWL_SSN;
254
255
256 /* Following is to validate EIN to support W2 Reporting */
257
258 FUNCTION validate_W2_SSN(p_effective_date IN varchar2,
259 p_report_type IN varchar2,
260 p_format IN varchar2,
261 p_report_qualifier IN varchar2,
262 p_record_name IN varchar2,
263 p_input_1 IN varchar2,
264 p_input_2 IN varchar2,
265 p_input_3 IN varchar2,
266 p_input_4 IN varchar2,
267 p_input_5 IN varchar2,
268 p_validate IN varchar2,
269 p_err OUT nocopy boolean
270 ) return varchar2 IS
271 l_err boolean := FALSE;
272 l_warning boolean := FALSE;
273 return_value varchar2(100);
274 l_length number(10);
275 l_message varchar2(2000);
276 l_number_length number(10);
277 l_description varchar2(50);
278 l_input_2 varchar2(100);
279 l_ssn varchar2(50);
280 l_1st_three_chars varchar2(10);
281 l_last_four_chars varchar2(10);
282
283 TYPE special_numbers is record(p_number_set varchar2(50));
284 special_number_record special_numbers;
285
286 TYPE ssn_special_number_rec is table of special_number_record%type
287 INDEX BY binary_integer;
288 ssn_check ssn_special_number_rec;
289 BEGIN
290 /* SSN validation for W2 reporting */
291 hr_utility.trace('Input SSN before Validation '||p_input_2);
292
293 -- Character I is eliminated in addition to hypen, period and quotes
294 l_ssn := replace(replace(replace(replace(
295 replace(pay_us_reporting_utils_pkg.character_check(p_input_2),
296 ' '),'I'),'-'),'.'),'''');
297 hr_utility.trace('SSN after eliminating special chars = '||l_ssn);
298 l_1st_three_chars := substr(l_ssn,1,3);
299 l_last_four_chars := substr(l_ssn,(length(l_ssn)-3));
300
301 -- Validation for SSN starting with 8 or 9
302 -- When SSN starting with 8 or 9 a warning is logged
303 --
304 IF ((substr(l_ssn,1,1) = '8') OR (substr(l_ssn,1,1) = '9')) THEN
305 -- Bug # 2183859
306 -- This should be a warning instead of an error
307 l_warning := TRUE;
308 l_description:= 'Invalid SSN. SSN should not begin with '||
309 substr(l_ssn,1,1);
310 ELSE
311
312 /* New set of Number need to be used for SSN Validation
313 Validation for Special numbers wrongly used as SSN
314 A warning is logged for these numbers
315 This is result of bug fix 3084344
316 */
317 ssn_check(1).p_number_set := '111111111';
318 ssn_check(2).p_number_set := '222222222';
319 ssn_check(3).p_number_set := '333333333';
320 ssn_check(4).p_number_set := '444444444';
321 ssn_check(5).p_number_set := '555555555';
322 ssn_check(6).p_number_set := '666666666';
323 ssn_check(7).p_number_set := '777777777';
324 ssn_check(8).p_number_set := '123456789';
325
326 FOR i in 1 .. 8 LOOP
327 IF l_ssn = ssn_check(i).p_number_set THEN
328 l_warning :=TRUE;
329 l_description := 'SSN '||l_ssn||' has Invalid combinations.';
330 hr_utility.trace(l_description);
331 END IF;
332 END LOOP;
333 -- First 3 digits of SSN should not be 000 or 666
334 if (l_1st_three_chars = '000' or
335 l_1st_three_chars = '666' ) then
336 --{
337 l_warning:=TRUE;
338 l_description := 'SSN '||l_ssn||' is Invalid as 1st 3 digits 000/666';
339 hr_utility.trace(l_description);
340 --}
341 -- Last 4 digits of SSN should not be 0000
342 elsif (l_last_four_chars = '0000') then
343 l_warning:=TRUE;
344 l_description := 'SSN '||l_ssn||' is Invalid as last 4 digits 0000';
345 hr_utility.trace(l_description);
346 end if;
347
348 END IF; -- End of check for SSN starting with 8 or 9
349
350 -- Set Return Value
351 IF l_ssn IS NULL THEN --SSN null check
352 l_warning:=TRUE;
353 return_value:= '000000000';
354 l_description:= 'SSN is blank. Padded with zeros';
355 ELSE
356 return_value:= rpad(substr(l_ssn,1,9),9);
357 END IF;
358
359
360 -- For W2 a warning message is logged but record is processed without Invalidating
361 --
362 IF l_warning THEN
363 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT_WARNING','A');
364 pay_core_utils.push_token('record_name',substr(p_record_name,1,50));
365 pay_core_utils.push_token('name_or_number',substr(p_input_4,1,50));
366 pay_core_utils.push_token('description',substr(l_description,1,50));
367 hr_utility.trace('WARNING: in '||p_record_name||'for employee '||
368 p_input_4||l_description);
369 END IF; -- SSN valid check
370 p_err := FALSE;
371 return return_value;
372 END validate_W2_SSN;
373 -- End of validate_W2_SSN
374 --
375
376 -- End of Package Body pay_us_reporting_data_validation
377 END pay_us_report_data_validation;
[Home] [Help]