1 PACKAGE BODY hr_ni_chk_pkg AS
2 /* $Header: penichk.pkb 120.4 2011/06/14 10:20:59 npershad ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_ni_chk_pkg.';
7
8 /*
9 Name : hr_ni_chk_pkg (BODY)
10 */
11 --
12 -- ------------------- validate_national_identifier --------------------
13 --
14 --
15 function validate_national_identifier
16 ( p_national_identifier VARCHAR2,
17 p_birth_date DATE,
18 p_gender VARCHAR2,
19 p_business_group_id NUMBER,
20 p_session_date DATE)
21 return VARCHAR2 IS
22 --
23 l_return_value varchar2(240);
24 l_person_id per_people_f.person_id%TYPE;
25 l_legislation_code per_business_groups.legislation_code%TYPE;
26 l_proc varchar2(72) := g_package||'validate_national_identifier';
27 l_warning varchar2(1) :='N';
28 l_person_type_id per_people_f.person_type_id%TYPE;
29 l_region_of_birth per_people_f.region_of_birth%TYPE;
30 l_country_of_birth per_people_f.country_of_birth%TYPE;
31 --
32 cursor csr_bg is
33 select legislation_code
34 from per_business_groups pbg
35 where pbg.business_group_id = p_business_group_id;
36 --
37 Begin
38 --
39 hr_utility.set_location('Entering:'|| l_proc, 3);
40 --
41 -- validate arguments prior to calling validate_national_identifier
42 --
43 -- check national identifier is not null
44 --
45 if p_national_identifier is null then
46 hr_utility.set_message(801,'HR_51242_PER_NAT_ID_NULL');
47 hr_utility.raise_error;
48 end if;
49 l_return_value := p_national_identifier;
50 --
51 -- check birth date is not null
52 --
53 if p_birth_date is null then
54 hr_utility.set_message(800,'HR_52767_PER_DOB_NULL');
55 hr_utility.raise_error;
56 end if;
57 --
58 -- check gender is not null
59 --
60 if p_gender is null then
61 hr_utility.set_message(800,'HR_52766_PER_GENDER_NULL');
62 hr_utility.raise_error;
63 end if;
64 --
65 -- check session date is not null
66 --
67 if p_session_date is null then
68 hr_utility.set_message(800,'HR_52768_PER_SESSION_DATE_NULL');
69 hr_utility.raise_error;
70 end if;
71 --
72 -- validate p_business_group and derrive legislation code
73 --
74 if p_business_group_id is null then
75 hr_utility.set_message(800,'HR_52769_PER_BUS_GRP_NULL');
76 hr_utility.raise_error;
77 else
78 open csr_bg;
79 fetch csr_bg into l_legislation_code;
80 if csr_bg%notfound then
81 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
82 hr_utility.raise_error;
83 end if;
84 close csr_bg;
85 end if;
86 --
87 --
88 hr_utility.set_location(l_proc, 6);
89 --
90 --
91 -- Now call NI validation routine...
92 l_return_value := hr_ni_chk_pkg.validate_national_identifier(
93 p_national_identifier => p_national_identifier,
94 p_birth_date => p_birth_date,
95 p_gender => p_gender,
96 p_person_id => l_person_id,
97 p_business_group_id => p_business_group_id,
98 p_legislation_code => l_legislation_code,
99 p_session_date => p_session_date,
100 p_warning => l_warning,
101 p_person_type_id => l_person_type_id,
102 p_region_of_birth => l_region_of_birth,
103 p_country_of_birth => l_country_of_birth);
104 --
105 --
109 --
106 hr_utility.set_location('Leaving:'|| l_proc, 8);
107 --
108 return l_return_value;
110 end validate_national_identifier;
111 --
112 --
113 -- ------------------- validate_national_identifier -----------------------
114 --
115 --
116 function validate_national_identifier
117 ( p_national_identifier VARCHAR2,
118 p_birth_date DATE,
119 p_gender VARCHAR2,
120 p_event VARCHAR2 default 'WHEN-VALIDATE-RECORD',
121 p_person_id NUMBER,
122 p_business_group_id NUMBER,
123 p_legislation_code VARCHAR2,
124 p_session_date DATE,
125 p_warning OUT NOCOPY VARCHAR2,
126 p_person_type_id NUMBER default NULL,
127 p_region_of_birth VARCHAR2 default NULL,
128 p_country_of_birth VARCHAR2 default NULL
129 ) return VARCHAR2 IS
130 --
131 l_nationality varchar2(30);
132 l_return_value varchar2(240);
133 begin
134
135 l_return_value :=hr_ni_chk_pkg.validate_national_identifier(
136 p_national_identifier => p_national_identifier,
137 p_birth_date => p_birth_date,
138 p_gender => p_gender,
139 p_person_id => p_person_id,
140 p_business_group_id => p_business_group_id,
141 p_legislation_code => p_legislation_code,
142 p_session_date => p_session_date,
143 p_warning => p_warning,
144 p_person_type_id => p_person_type_id,
145 p_region_of_birth => p_region_of_birth,
146 p_country_of_birth => p_country_of_birth,
147 p_nationality => l_nationality );
148
149 return l_return_value; -- change for the 5970526
150
151 end validate_national_identifier;
152 --
153
154 -- added a new parameter p_nationality
155 FUNCTION validate_national_identifier
156 ( p_national_identifier VARCHAR2,
157 p_birth_date DATE,
158 p_gender VARCHAR2,
159 p_event VARCHAR2 default 'WHEN-VALIDATE-RECORD',
160 p_person_id NUMBER,
161 p_business_group_id NUMBER,
162 p_legislation_code VARCHAR2,
163 p_session_date DATE,
164 p_warning OUT NOCOPY VARCHAR2,
165 p_person_type_id NUMBER default NULL,
166 p_region_of_birth VARCHAR2 default NULL,
167 p_country_of_birth VARCHAR2 default NULL,
168 p_nationality varchar2 -- added for the bug 5961277
169 ) RETURN VARCHAR2 is
170
171 l_formula_id ff_formulas_f.formula_id%type;
172 l_effective_start_date ff_formulas_f.effective_start_date%type;
173 l_inputs ff_exec.inputs_t;
174 l_outputs ff_exec.outputs_t;
175 l_return_value varchar2(240);
176 l_invalid_mesg varchar2(240);
177 l_warning varchar2(1) := 'N';
178 l_compiled_formula_id number;
179 --
180 l_proc varchar2(72) := g_package||'validate_national_identifier';
181 --
182 --
183 -- Cursor to check that the formula being used has been compiled
184 --
185 cursor csr_compiled_formula is
186 select formula_id
187 from ff_compiled_info_f
188 where formula_id = l_formula_id
189 and p_session_date between effective_start_date and effective_end_date;
190
191 begin
192 --
193 hr_utility.set_location('Entering:'|| l_proc, 5);
194 --
195 --
196 l_return_value := p_national_identifier;
197 --
198 -- select formula_id,effective_start_date
199 -- into l_formula_id,l_effective_start_date
200 -- from ff_formulas_f
201 -- where formula_name='NI_VALIDATION'
202 -- and business_group_id is null
203 -- and legislation_code=p_legislation_code
204 -- and p_session_date between effective_start_date and effective_end_date;
205
206 --
207 --bug 2091601
208 --
209 SELECT FORMULA_ID,EFFECTIVE_START_DATE
210 into l_formula_id,l_effective_start_date
211 FROM FF_FORMULAS_F fo, ff_formula_types ft
212 WHERE ft.formula_type_name = 'Oracle Payroll'
213 and fo.formula_type_id = ft.formula_type_id
214 and fo.FORMULA_NAME = 'NI_VALIDATION'
215 AND fo.BUSINESS_GROUP_ID IS NULL
216 AND fo.LEGISLATION_CODE = p_legislation_code
217 AND p_session_date BETWEEN fo.EFFECTIVE_START_DATE AND fo.EFFECTIVE_END_DATE;
218 --
219 --
220 --
221
222 --
223 hr_utility.set_location(l_proc, 10);
224 --
225 -- Addition for 1891893
226 --
227 open csr_compiled_formula;
228 fetch csr_compiled_formula into l_compiled_formula_id;
229 if csr_compiled_formula%found then
230 --
231 -- End of current addition 1891893, elsif/error message below
232 --
233 --
234 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
235 --
236 for l_in_cnt in
237 l_inputs.first..l_inputs.last
238 loop
239 if l_inputs(l_in_cnt).name='NATIONAL_IDENTIFIER' then
240 l_inputs(l_in_cnt).value := p_national_identifier;
241 end if;
242 if l_inputs(l_in_cnt).name='BIRTH_DATE' then
243 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_birth_date);
244 end if;
245 if l_inputs(l_in_cnt).name='GENDER' then
246 l_inputs(l_in_cnt).value := p_gender;
247 end if;
248 if l_inputs(l_in_cnt).name='EVENT' then
249 l_inputs(l_in_cnt).value := p_event;
250 end if;
251 if l_inputs(l_in_cnt).name='PERSON_TYPE_ID' then
252 l_inputs(l_in_cnt).value := p_person_type_id;
253 end if;
254 if l_inputs(l_in_cnt).name='REGION_OF_BIRTH' then
255 l_inputs(l_in_cnt).value := p_region_of_birth;
256 end if;
260 if l_inputs(l_in_cnt).name='NATIONALITY' then
257 if l_inputs(l_in_cnt).name='COUNTRY_OF_BIRTH' then
258 l_inputs(l_in_cnt).value := p_country_of_birth;
259 end if;
261 l_inputs(l_in_cnt).value := p_nationality;
262 end if;
263 end loop;
264 --
265 hr_utility.set_location(l_proc, 15);
266 --
267 --
268 ff_exec.run_formula(l_inputs,l_outputs);
269 --
270 for l_out_cnt in
271 l_outputs.first..l_outputs.last
272 loop
273 if l_outputs(l_out_cnt).name='RETURN_VALUE' then
274 l_return_value := l_outputs(l_out_cnt).value;
275 end if;
276 if l_outputs(l_out_cnt).name='INVALID_MESG' then
277 l_invalid_mesg := l_outputs(l_out_cnt).value;
278 end if;
279 end loop;
280 --
281 if l_return_value = 'INVALID_ID' then
282 if fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'NONE' then
283 if fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'WARN' then
284 hr_utility.set_message(801,l_invalid_mesg);
285 hr_utility.raise_error;
286 else
287 l_warning :='Y';
288 end if;
289 end if;
290 end if;
291 p_warning := l_warning;
292 -- ********************************************
293 -- If the formula is invalid then error 1891893
294 -- ********************************************
295 elsif fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'NONE' then
296 fnd_message.set_name('PER','HR_289303_NI_FORMULA_ERROR');
297 hr_utility.raise_error;
298 end if;
299 close csr_compiled_formula;
300 --
301 -- End of fix for 1891893
302 --
303 hr_utility.set_location('Leaving:'|| l_proc, 20);
304 --
305 if fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') ='NONE' or fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') ='WARN' then
306 l_return_value := p_national_identifier;
307 end if;
308
309 --
310 return l_return_value;
311 exception
312 when NO_DATA_FOUND then
313 --
314 hr_utility.set_location('Leaving:'|| l_proc, 22);
315 --
316 return l_return_value;
317 --
318
319 end validate_national_identifier;
320 --
321 --
322 FUNCTION chk_nat_id_format(
323
324 /* This function checks that a supplied national identifier
325 is in the specified format. It also ensures that the correct
326 format mask is applied to the national identifier, which is
327 then returned to the calling program. If the validation fails
328 then the rountine passes back a '0'.
329
330 It should conform to business process validation standards,
331 from which it is called in order that the person API routines
332 remain under the control of one person.
333
334 */
335 p_national_identifier IN VARCHAR2,
336 p_format_string IN VARCHAR2
337
338 ) RETURN VARCHAR2 AS
339
340 l_nat_id VARCHAR2(30);
341 l_format_mask VARCHAR2(30);
342 l_format_string VARCHAR2(30);
343 l_valid NUMBER;
344 l_len_format_mask NUMBER;
345 l_number_format_ch NUMBER;
346 l_no_format_nat_id VARCHAR2(30);
347 l_no_format_string_opt VARCHAR2(30);
348 l_no_format_string_nopt VARCHAR2(30);
349 l_format_count NUMBER;
350 l_nat_id_count NUMBER;
351 l_lgth_string_nopt NUMBER;
352 l_lgth_string_opt NUMBER;
353 l_lgth_nat_id NUMBER;
354
355
356 l_proc varchar2(72) := g_package||'chk_nat_id_format';
357 --
358 begin
359 --
360 hr_utility.set_location('Entering:'|| l_proc, 5);
361 --
362
363 l_nat_id := '0';
364 l_valid := 1;
365
366 /* First Derive the format mask from the format string.
367 This is defined as the remainder of the string, after
368 the format characters, namely 'ABDEX' have been removed.
369 Also generate the format mask without any kind of
370 format characters for continued use in the processing */
371
372 l_format_mask := translate(p_format_string,'CABDEX','C');
373 l_format_string := translate(p_format_string,'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
374
375 /* Check validity of format string */
376
377 if translate(l_format_string,'CABDEX','C') is null then
378
379 /* Check validity of format mask */
380
381 if translate(upper(l_format_mask),'A !"$%^&*()-_+=`[]{};''#:@~<>?','A') is null then
382
383 /* Check that the format string and national identifier number are the same length */
384 /* - that is minus any optional characters */
385
386 l_no_format_string_opt:=translate(upper(l_format_string),'ABDEX','ABDEX');
387 l_no_format_string_nopt:=translate(upper(l_format_string),'ADXBE','ADX');
388 l_no_format_nat_id:=translate(upper(p_national_identifier),'A !"$%^&*()-_+=`[]{};''#:@~<>?','A');
389
390 l_lgth_string_nopt:=length(l_no_format_string_nopt);
391 l_lgth_string_opt:=length(l_no_format_string_opt);
392 l_lgth_nat_id:=length(l_no_format_nat_id);
393
394
395 if((l_lgth_nat_id>=l_lgth_string_nopt) and (l_lgth_nat_id<=l_lgth_string_opt)) then
396
397 /* If processing reaches this point, we have a valid format mask, a valid format string
398 and a format string that can be checked against the national identifier
399 Main format validation can now preceed */
400
401 FOR l_char_pos in 1..l_lgth_string_opt LOOP
402
403 if (substr(l_no_format_string_opt,l_char_pos,1)='A') then
404
405
406 if(substr(l_no_format_nat_id,l_char_pos,1)<'A' OR substr(l_no_format_nat_id,l_char_pos,1)>'Z') then
407 l_valid := 0;
408 end if;
409
413 if (l_lgth_nat_id >= l_char_pos) then
410 elsif (substr(l_no_format_string_opt,l_char_pos,1)='B') then
411
412
414
415 if(substr(l_no_format_nat_id,l_char_pos,1)<'A' OR substr(l_no_format_nat_id,l_char_pos,1)>'Z') then
416 l_valid := 0;
417 end if;
418
419 end if;
420
421 elsif (substr(l_no_format_string_opt,l_char_pos,1)='D') then
422
423
424 if(substr(l_no_format_nat_id,l_char_pos,1)<'0' OR substr(l_no_format_nat_id,l_char_pos,1)>'9') then
425 l_valid := 0;
426 end if;
427
428 elsif (substr(l_no_format_string_opt,l_char_pos,1)='E') then
429
430
431 if (l_lgth_nat_id >= l_char_pos) then
432
433 if(substr(l_no_format_nat_id,l_char_pos,1)<'0' OR substr(l_no_format_nat_id,l_char_pos,1)>'9') then
434 l_valid := 0;
435 end if;
436
437 end if;
438
439 elsif (substr(l_no_format_string_opt,l_char_pos,1)='X') then
440
441 if (substr(l_no_format_nat_id,l_char_pos,1)<'0' OR substr(l_no_format_nat_id,l_char_pos,1)>'9')
442 and (substr(l_no_format_nat_id,l_char_pos,1)<'A' OR substr(l_no_format_nat_id,l_char_pos,1)>'Z')
443 then
444 l_valid := 0;
445 end if;
446
447 end if;
448
449 EXIT WHEN l_valid=0;
450
451 END LOOP;
452
453 if l_valid = 1 then
454
455 /* We have a valid national identifier - now to return it in the format mask required */
456
457 l_format_count:=1;
458 l_nat_id_count:=1;
459
460 /* Reset the national identifier to null before adding the passed national identifier */
461
462 l_nat_id := '';
463 FOR l_format_pos in 1..length(p_format_string) LOOP
464 --
465 -- Bug 944746, rearranged string from ABCDEX to CABDEX.
466 --
467 if(translate(substr(p_format_string,l_format_pos,1),'CABDEX','C') is not null) then
468 /* We have a format character - add it on to the return national identifier */
469 l_nat_id := l_nat_id||substr(p_format_string,l_format_pos,1);
470 else
471 /* We have a national identifier character - add it on to the return variable */
472 l_nat_id := l_nat_id||substr(l_no_format_nat_id,l_nat_id_count,1);
473 l_nat_id_count:=l_nat_id_count+1;
474 end if;
475
476 END LOOP;
477
478 else
479
480 /* The national identifier is not in the valid format */
481
482 -- dbms_output.put_line('The format of the national identifier is not correct');
483 null;
484
485 end if;
486
487 else
488
489 /* The format string and national identifier are differing lengths */
490 -- dbms_output.put_line('The format string and national identifier (excluding formats)');
491 -- dbms_output.put_line('are not the same length');
492 null;
493
494 end if;
495
496 end if;
497
498 else
499
500
501 /* The format string contains unexecpected characters - check to see if
502 the format string and the national identifier are identical, if so,
503 then this corresponds to a special format inside the formula rather
504 than here, now that the formulae are calling this function */
505
506 -- dbms_output.put_line('Is this a special string - check inside the formula');
507 null;
508
509 /* End format string check */
510 end if;
511 --
512 hr_utility.set_location('Leaving:'|| l_proc, 10);
513 --
514
515 return l_nat_id;
516
517 end chk_nat_id_format;
518
519 -- ------------------- check_ni_unique --------------------
520 procedure check_ni_unique
521 ( p_national_identifier VARCHAR2,
522 p_person_id NUMBER,
523 p_business_group_id NUMBER,
524 p_raise_error_or_warning VARCHAR2)
525 --
526 is
527 --
528 l_status VARCHAR2(1);
529 l_legislation_code VARCHAR2(30);
530 l_nat_lbl VARCHAR2(2000);
531 --
532 local_warning exception;
533 l_proc varchar2(72) := g_package||'check_ni_unique';
534 --
535 begin
536 --
537 hr_utility.set_location('Entering:'|| l_proc, 5);
538 --
539 SELECT org_information9
540 INTO l_legislation_code
541 FROM hr_organization_information
542 WHERE org_information_context = 'Business Group Information'
543 AND organization_id = p_business_group_id;
544 --
545 --
546 --
547 hr_utility.set_location(l_proc, 10);
548 --
549 begin
550 /* Bug 12594992, introduced the below if else structure to cater to specific requirements of UK.
551 For UK, the first 8 digits of national identifier should only be checked for uniqueness.
552 */
553 if l_legislation_code = 'GB' then
554 SELECT 'Y'
555 INTO l_status
556 FROM sys.dual
557 WHERE exists(SELECT '1'
558 FROM per_all_people_f pp
559 WHERE (p_person_id IS NULL
560 OR p_person_id <> pp.person_id)
561 AND substr(p_national_identifier, 1, 8) = substr(pp.national_identifier, 1, 8)
562 AND pp.business_group_id +0 = p_business_group_id);
563 else
564 SELECT 'Y'
565 INTO l_status
566 FROM sys.dual
570 OR p_person_id <> pp.person_id)
567 WHERE exists(SELECT '1'
568 FROM per_all_people_f pp
569 WHERE (p_person_id IS NULL
571 AND p_national_identifier = pp.national_identifier
572 AND pp.business_group_id +0 = p_business_group_id);
573 end if;
574 --
575 fnd_message.set_name('PER','HR_NATIONAL_ID_NUMBER_'||l_legislation_code);
576 l_nat_lbl := fnd_message.get;
577 l_nat_lbl := rtrim(l_nat_lbl);
578 if l_nat_lbl = 'HR_NATIONAL_ID_NUMBER_'||l_legislation_code then
579 fnd_message.set_name('PER','HR_NATIONAL_IDENTIFIER_NUMBER');
580 l_nat_lbl := fnd_message.get;
581 l_nat_lbl := rtrim(l_nat_lbl);
582 end if;
583
584 if p_raise_error_or_warning = 'ERROR' then
585 hr_utility.set_message(801,'HR_NI_UNIQUE_ERROR');
586 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
587 hr_utility.raise_error;
588 else
589 /* psingla -- To execute null statement if the Legislation is Polish and the profile
590 PER_NI_UNIQUE_ERROR_WARNING is set to null
591 hr_utility.set_message(801,'HR_NI_UNIQUE_WARNING');
592 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
593 raise local_warning; */
594 if l_legislation_code = 'PL' and fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') is NULL then -- For Poland
595 null;
596 else
597 hr_utility.set_message(801,'HR_NI_UNIQUE_WARNING');
598 hr_utility.set_message_token('NI_NUMBER',l_nat_lbl);
599 raise local_warning;
600 end if;
601 end if;
602 --
603 --
604 hr_utility.set_location(' Leaving:'||l_proc, 15);
605 --
606 exception
607 when no_data_found then null;
608 when local_warning then
609 raise;
610 end;
611 exception
612 when NO_DATA_FOUND then
613 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
614 hr_utility.set_message_token('PROCEDURE','CHECK_NI_UNIQUE');
615 hr_utility.set_message_token('STEP','1');
616 hr_utility.raise_error;
617 when local_warning then
618 hr_utility.set_warning;
619 end check_ni_unique;
620 end hr_ni_chk_pkg;
621
[Home] [Help]