1 package body PER_CN_VALIDATIONS_PKG as
2 /* $Header: percnval.pkb 115.1 2002/11/20 07:21:18 mkandasa noship $ */
3 --------------------------------------------------------------------------------
4
5 /*
6 +==============================================================================+
7 | Copyright (c) 1994 Oracle Corporation |
8 | Redwood Shores, California, USA |
9 | All rights reserved. |
10 +==============================================================================+
11
12 Name
13 National Identifier card number validations.
14 Purpose
15 To validate the National Identifier entered in people form.
16 History
17 23-OCT-02 mkandasa Created
18 */
19
20 -------------------------------------------------------------------------------
21 --This function will calculate the weight for the position p_pos.
22 --The weight will be calculated using the formula Wi=2pow(i-1) (Mod 11);
23
24 function weight(p_pos number)
25 return number is
26 l_counter number;
27 l_weight number :=1;
28 begin
29 for l_counter in 1 .. p_pos - 1
30 loop
31 l_weight := mod (l_weight * 2,11);
32 end loop;
33 return l_weight;
34 end;
35
36 -- This function used to calucate the check digit for the given
37 -- National Identifier number
38 -- The formual used to calculate the check digit is given below
39 -- Checkdigit = 12 - Sigma i=2 to 18 ( Ai X Wi ) MOD 11
40
41 function calculate_check_digit(p_national_identifier varchar2)
42 return char is
43 l_pos number :=0;
44 l_digit number :=0;
45 l_weight number :=0;
46 l_sum number :=0;
47 l_chk_dgt number :=0;
48 begin
49 hr_utility.set_location('PER_CN_VALIDATIONS_PKG.calculate_check_digit',1);
50 for l_pos in 2 .. 18
51 loop
52 l_digit := to_number(substr(p_national_identifier,19 - l_pos,1)) ;
53 l_weight := weight(l_pos);
54 l_sum := MOD(l_sum + l_digit * l_weight,11 );
55 -- hr_utility.trace('taking the digit'||l_digit||'at pos '||l_pos||' and weight is'||l_weight||'and sum'||l_sum);
56 end loop;
57
58 l_chk_dgt := MOD(12 - l_sum,11);
59 hr_utility.trace('The check digit is '||l_chk_dgt);
60 if (l_chk_dgt < 10 ) then
61 return to_char(l_chk_dgt);
62 else
63 return 'X';
64 end if;
65 hr_utility.set_location('PER_CN_VALIDATIONS_PKG.calculate_check_digit',2);
66 end calculate_check_digit;
67
68
69 --This function used to validate the national Identifier number entered
70 -- for china legislation.
71
72 function validate_national_identifier(
73 p_national_identifier in varchar2,
74 p_dob in date,
75 p_gender in varchar2
76 )
77 return number
78 is
79 l_gender number;
80 l_status number;
81 l_ni_dob varchar2(8);
82 l_entered_dob varchar2(8);
83 l_cal_chk_dgt char;
84 l_ni_chk_dgt char;
85
86
87 Begin
88 hr_utility.set_location('PER_CN_VALIDATIONS_PKG.validate_national_identifier',1);
89 l_status := 0;
90 if ( length(p_national_identifier) = 18 ) then
91 begin
92 l_gender :=mod( to_number(substr(p_national_identifier,17,1)),2);
93 l_ni_dob :=substr(p_national_identifier,7,8);
94 l_entered_dob :=to_char(p_dob,'YYYYMMDD');
95 l_ni_chk_dgt :=substr(p_national_identifier,18,1);
96 l_cal_chk_dgt :=calculate_check_digit(p_national_identifier);
97 end;
98 elsif (length(p_national_identifier) = 15 ) then
99 begin
100 l_gender :=mod( to_number(substr(p_national_identifier,15,1)),2);
101 l_ni_dob :=substr(p_national_identifier,7,6);
102 l_entered_dob :=to_char(p_dob,'YYMMDD');
103 l_ni_chk_dgt :=null;
104 l_cal_chk_dgt :=null;
105 end;
106 end if;
107
108 hr_utility.trace('NI'||l_gender||'dob'||l_ni_dob||'chk'||l_ni_chk_dgt);
109 hr_utility.trace('EN'||p_gender||'dob'||l_entered_dob||'chk'||l_cal_chk_dgt);
110 -- Check for mismatch between gender entered and gender given in NI no.
111 if ( ( l_gender = 0 AND p_gender = 'M' ) OR ( l_gender = 1 AND p_gender = 'F' )) then
112 l_status := -1;
113 -- Check for mismatch between dob entered and dob given in NI no.
114 elsif ( to_char(p_dob,'DD/MM/YYYY') <> '01/01/0001') AND
115 ( l_ni_dob <> l_entered_dob ) then
116 l_status := -2;
117 -- Check for mismatch between dob entered and dob given in NI no.
118 elsif ( nvl(l_ni_chk_dgt,'#') <> nvl( l_cal_chk_dgt, '#' )) then
119 l_status := -3;
120 end if;
121
122 hr_utility.set_location('PER_CN_VALIDATIONS_PKG.validate_national_identifier',1);
123 -- If there is an error then l_stauts will be less than zero else it will be zero.
124 return l_status;
125
126 End validate_national_identifier;
127
128 End PER_CN_VALIDATIONS_PKG;
[Home] [Help]