[Home] [Help]
PACKAGE BODY: APPS.PER_RO_NI_VALIDATION
Source
1 package body per_ro_ni_validation as
2 /* $Header: perronival.pkb 120.0.12020000.7 2013/05/08 12:28:01 ssanjays noship $ */
3
4 function validate_ni_formula(
5 P_NATIONAL_IDENTIFIER in VARCHAR2
6 )
7 return number
8 is
9 l_valid_flag boolean default TRUE;
10 return_value number default 0;
11 S1 number;
12 Y2 number;
13 Y3 number;
14 M4 number;
15 M5 number;
16 D6 number;
17 D7 number;
18 C8 number;
19 C9 number;
20 N10 number;
21 N11 number;
22 N12 number;
23 V13 number;
24 chksum number;
25 result number;
26
27 begin
28 hr_utility.set_location('NI_VALIDATION Formula:: ',200);
29 hr_utility.set_location('P_NATIONAL_IDENTIFIER :: '||P_NATIONAL_IDENTIFIER,200);
30
31 if (hr_ni_chk_pkg.chk_nat_id_format(P_NATIONAL_IDENTIFIER,'DDDDDDDDDDDDD') = 0) then
32 l_valid_flag := FALSE;
33 return_value := 1;
34 end if;
35 hr_utility.set_location('NI_VALIDATION Formula :: '||return_value,11);
36 -- algo validation
37 if l_valid_flag then
38 S1:=to_number(substr(P_NATIONAL_IDENTIFIER,1,1));
39 Y2:=to_number(substr(P_NATIONAL_IDENTIFIER,2,1));
40 Y3:=to_number(substr(P_NATIONAL_IDENTIFIER,3,1));
41 M4:=to_number(substr(P_NATIONAL_IDENTIFIER,4,1));
42 M5:=to_number(substr(P_NATIONAL_IDENTIFIER,5,1));
43 D6:=to_number(substr(P_NATIONAL_IDENTIFIER,6,1));
44 D7:=to_number(substr(P_NATIONAL_IDENTIFIER,7,1));
45 C8:=to_number(substr(P_NATIONAL_IDENTIFIER,8,1));
46 C9:=to_number(substr(P_NATIONAL_IDENTIFIER,9,1));
47 N10:=to_number(substr(P_NATIONAL_IDENTIFIER,10,1));
48 N11:=to_number(substr(P_NATIONAL_IDENTIFIER,11,1));
49 N12:=to_number(substr(P_NATIONAL_IDENTIFIER,12,1));
50 V13:=to_number(substr(P_NATIONAL_IDENTIFIER,13,1));
51
52 result := 2*S1+7*Y2+9*Y3+1*M4+4*M5+6*D6+3*D7+5*C8+8*C9+2*N10+7*N11+9*N12 ;
53
54 chksum := MOD(result,11);
55 hr_utility.trace('result : '||result);
56 hr_utility.trace('chksum : '||chksum);
57 if chksum <> V13 then
58 l_valid_flag := FALSE;
59 return_value := 2;
60 end if;
61 -- validation with other fields
62 -- DOB
63 hr_utility.set_location('NI_VALIDATION Formula :: '||return_value,22);
64 end if;
65 --hr_utility.trace_off();
66 return return_value;
67 end validate_ni_formula;
68
69
70 function validate_ni(
71 p_person_id in number
72 ,p_business_group_id in number
73 ,P_NATIONAL_IDENTIFIER in VARCHAR2
74 ,P_SEX in VARCHAR2
75 ,P_DATE_OF_BIRTH in DATE
76 ,P_REGION_OF_BIRTH in VARCHAR2
77 ,P_COUNTRY_OF_BIRTH in VARCHAR2
78 ,P_CITIZENSHIP in varchar2
79 ,P_RESIDENCE in varchar2
80 )
81 return number
82 is
83 l_valid_flag boolean default TRUE;
84 return_value number default 0;
85 S1 number;
86 Y2 number;
87 M4 number;
88 D6 number;
89 C8 number;
90
91 l_year number;
92 l_month number;
93 l_date number;
94 l_county number;
95
96 cursor c_county_code(county_name in varchar2) is
97 select LOOKUP_CODE from hr_lookups where LOOKUP_TYPE = 'RO_PER_COUNTIES' and MEANING = county_name;
98
99 begin
100 --hr_utility.trace_on(null,'Test');
101 hr_utility.set_location('NI_VALIDATION :: ',200);
102 hr_utility.set_location('p_person_id :: '||p_person_id,200);
103 hr_utility.set_location('p_business_group_id :: '||p_business_group_id,200);
104 hr_utility.set_location('P_NATIONAL_IDENTIFIER :: '||P_NATIONAL_IDENTIFIER,200);
105 hr_utility.set_location('P_SEX :: '||P_SEX,200);
106 hr_utility.set_location('P_DATE_OF_BIRTH :: '||P_DATE_OF_BIRTH,200);
107 hr_utility.set_location('P_REGION_OF_BIRTH :: '||P_REGION_OF_BIRTH,200);
108 hr_utility.set_location('P_COUNTRY_OF_BIRTH :: '||P_COUNTRY_OF_BIRTH,200);
109 hr_utility.set_location('P_CITIZENSHIP :: '||P_CITIZENSHIP,200);
110 hr_utility.set_location('P_RESIDENCE :: '||P_RESIDENCE,200);
111
112
113 -- other validations
114 return_value := validate_ni_formula(P_NATIONAL_IDENTIFIER);
115 if return_value <> 0 then
116 l_valid_flag := FALSE;
117 end if;
118 hr_utility.set_location('NI_VALIDATION Formula :: '||return_value,1);
119 -- unique chk
120 hr_ni_chk_pkg.check_ni_unique(P_NATIONAL_IDENTIFIER,p_person_id,p_business_group_id,'ERROR');
121
122 hr_utility.set_location('NI_VALIDATION Formula :: '||return_value,2);
123 --
124 S1:=to_number(substr(P_NATIONAL_IDENTIFIER,1,1));
125 Y2:=to_number(substr(P_NATIONAL_IDENTIFIER,2,2));
126 M4:=to_number(substr(P_NATIONAL_IDENTIFIER,4,2));
127 D6:=to_number(substr(P_NATIONAL_IDENTIFIER,6,2));
128 C8:=to_number(substr(P_NATIONAL_IDENTIFIER,8,2));
129 select extract(YEAR FROM P_DATE_OF_BIRTH) into l_year from dual;
130 select extract(Month FROM P_DATE_OF_BIRTH) into l_month from dual;
131 select extract(day FROM P_DATE_OF_BIRTH) into l_date from dual;
132
133 if (S1 in (1,3,5,7,9) and P_SEX <> 'M') or (S1 in (2,4,6,8,9) and P_SEX <> 'F') or S1 =0 then
134 l_valid_flag := FALSE;
135 return_value := 4;
136 end if;
137
138 if nvl(P_DATE_OF_BIRTH,hr_api.g_date) <> hr_api.g_date and l_valid_flag then-- to skip vaidations when called from formula
139 if ((mod(l_year,100) <> Y2) or (l_month <> M4) or (l_date <> D6)) then -- date of birth mismatch
140 l_valid_flag := FALSE;
141 return_value := 3;
142 end if;
143 end if;
144
145 hr_utility.trace('p_citizenship'||p_citizenship);
146 hr_utility.trace('P_SEX'||P_SEX);
147 hr_utility.trace('S1'||S1);
148 hr_utility.trace('(trunc(l_year,-2)/100)'||(trunc(l_year,-2)/100));
149 -- Gender | Citizenship | Residence
150 if l_valid_flag then -- skip validation when citizenship not provided
151 if (nvl(p_citizenship,'X')= 'RO' or S1 <= 6 ) and nvl(P_DATE_OF_BIRTH,hr_api.g_date) <> hr_api.g_date then -- chk for Romanian Citizenship
152 case (trunc(l_year,-2)/100)
153 when 19 then
154 if not((P_SEX='M' and S1 = 1) or (P_SEX ='F' and S1 = 2)) then
155 l_valid_flag := FALSE;
156 return_value := 4;
157 end if;
158 when 18 then
159 if not((P_SEX='M' and S1 = 3) or (P_SEX ='F' and S1 = 4)) then
160 l_valid_flag := FALSE;
161 return_value := 4;
162 end if;
163 when 20 then
164 if not((P_SEX='M' and S1 = 5) or (P_SEX ='F' and S1 = 6)) then
165 l_valid_flag := FALSE;
166 return_value := 4;
167 end if;
168 else
169 l_valid_flag := FALSE;
170 return_value := 3;
171 end case;
172 elsif ( p_citizenship is not null ) and p_residence is not null then -- chk for foriegn citizens
173 if (p_residence = 'R') then
174 if not((P_SEX='M' and S1=7 ) or (P_SEX='F' and S1=8)) then
175 l_valid_flag := FALSE;
176 return_value := 4;
177 elsif nvl(P_DATE_OF_BIRTH,hr_api.g_date) <> hr_api.g_date then --((trunc(l_year,-2)/100) <> 19) and
178 l_valid_flag := FALSE;
179 return_value := 3;
180 end if;
181 else
182 if S1 <> 9 then
183 l_valid_flag := FALSE;
184 return_value := 4;
185 elsif nvl(P_DATE_OF_BIRTH,hr_api.g_date) <> hr_api.g_date then -- ((trunc(l_year,-2)/100) <> 19)
186 l_valid_flag := FALSE;
187 return_value := 3;
188 end if;
189 end if;
190 end if;
191 end if; -- Citiznship | Residence | Gender
192 hr_utility.set_location('NI_VALIDATION Formula :: '||return_value,3);
193 if P_COUNTRY_OF_BIRTH = 'RO' and P_REGION_OF_BIRTH is not null and l_valid_flag then
194 open c_county_code(P_REGION_OF_BIRTH);
195 fetch c_county_code into l_county;
196 if c_county_code%notfound then
197 l_valid_flag := FALSE;
198 return_value := 5;
199 end if;
200 close c_county_code;
201 if (l_county <> C8) then
202 l_valid_flag := FALSE;
203 return_value := 6;
204 hr_utility.trace('Raising Warning');
205 end if;
206 end if;
207 hr_utility.set_location('NI_VALIDATION Formula :: '||return_value,4);
208 return return_value;
209 end validate_ni;
210
211 end per_ro_ni_validation;
212