[Home] [Help]
PACKAGE BODY: APPS.HR_RO_PERSON_VALIDATE
Source
1 package body hr_ro_person_validate as
2 /* $Header: perropval.pkb 120.0.12020000.5 2013/04/19 10:06:07 ssanjays noship $ */
3
4 g_package CONSTANT VARCHAR2 (30) := 'hr_ro_person_validate.';
5 PROCEDURE check_lookup_value(
6 p_argument IN VARCHAR2,
7 p_argument_value IN VARCHAR2,
8 p_lookup_type IN VARCHAR2,
9 p_effective_date IN DATE )
10 IS
11 --
12 BEGIN
13 --
14 IF (hr_api.not_exists_in_hr_lookups (p_effective_date, p_lookup_type, p_argument_value ) ) THEN
15 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
16 hr_utility.set_message_token ('ARGUMENT', p_argument);
17 hr_utility.raise_error;
18 END IF;
19 --
20 END check_lookup_value;
21
22 PROCEDURE validate_ro_employee(
23 p_person_id NUMBER,
24 p_business_group_id NUMBER,
25 p_first_name VARCHAR2,
26 p_hire_date DATE,
27 p_date_of_birth DATE,
28 p_sex VARCHAR2, -- gender
29 p_national_identifier VARCHAR2,
30 p_country_of_birth VARCHAR2,
31 p_region_of_birth VARCHAR2,
32 p_town_of_birth VARCHAR2,
33 p_per_information1 VARCHAR2, -- residence
34 p_per_information2 VARCHAR2, -- citizenship
35 p_per_information4 VARCHAR2 -- previous npc/frn
36 )
37 AS
38 l_proc CONSTANT VARCHAR2 (72) := g_package || 'validate_ro_employee';
39 l_county_code NUMBER DEFAULT 0;
40 l_valid_locality NUMBER DEFAULT 0;
41 l_valid_ni NUMBER DEFAULT 10;
42
43 CURSOR csr_validate_locality (p_locality VARCHAR2, p_county VARCHAR2, p_business_group_id NUMBER)
44 IS
45 SELECT 1
46 FROM dual
47 WHERE p_locality IN
48 (SELECT pucif.value
49 FROM pay_user_column_instances_f pucif,
50 pay_user_columns puc,
51 pay_user_tables put,
52 pay_user_rows_f purf
53 WHERE puc.user_column_name = 'LOCALITY'
54 AND puc.legislation_code = 'RO'
55 AND puc.business_group_id IS NULL
56 AND puc.user_table_id = put.user_table_id
57 AND pucif.user_column_id = puc.user_column_id
58 AND pucif.business_group_id = p_business_group_id
59 AND put.user_table_name = 'RO_LOCALITY_DETAILS'
60 AND put.legislation_code = 'RO'
61 AND put.business_group_id IS NULL
62 AND purf.user_table_id = put.user_table_id
63 AND pucif.user_row_id = purf.user_row_id
64 AND purf.business_group_id = p_business_group_id
65 AND purf.row_low_range_or_name IN
66 (SELECT purf1.ROW_LOW_RANGE_OR_NAME
67 FROM pay_user_rows_f purf1,
68 pay_user_column_instances_f pucif1,
69 pay_user_columns puc1,
70 pay_user_column_instances_f pucif2,
71 pay_user_columns puc2,
72 pay_user_rows_f purf2
73 WHERE puc1.user_column_name = 'COUNTY CODE'
74 AND puc1.legislation_code = 'RO'
75 AND puc1.business_group_id IS NULL
76 AND puc1.user_table_id = put.user_table_id
77 AND pucif1.user_column_id = puc1.user_column_id
78 AND pucif1.business_group_id = p_business_group_id
79 AND pucif1.value =
80 (SELECT lookup_code
81 FROM hr_lookups
82 WHERE lookup_type = 'RO_PER_COUNTIES'
83 AND enabled_flag ='Y'
84 AND meaning = p_county
85 )
86 AND pucif1.user_row_id = purf1.user_row_id
87 AND purf1.user_table_id = put.user_table_id
88 AND purf1.business_group_id = p_business_group_id
89 AND purf1.row_low_range_or_name = purf2.row_low_range_or_name
90 AND purf2.user_table_id = put.user_table_id
91 AND puc2.user_column_name = 'LEVEL'
92 AND puc2.legislation_code = 'RO'
93 AND puc2.business_group_id IS NULL
94 AND puc2.user_table_id = put.user_table_id
95 AND pucif2.user_column_id = puc2.user_column_id
96 AND pucif2.business_group_id = p_business_group_id
97 AND pucif2.value = '3'
98 AND purf2.user_row_id = pucif2.user_row_id
99 AND purf2.business_group_id = p_business_group_id
100 )
101 );
102
103
104 BEGIN
105 -- mandatory first name
106 -- lookup chk :: Residence, Citizenship
107 -- country x region x town
108 -- ni validation -- DOB,Birth Place, Gender,residence, citizenship -- npc and previous npc
109 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
110 hr_utility.trace('RO not installed.Leaving person validation');
111 RETURN;
112 END IF;
113 -- mandatory first name
114 hr_api.mandatory_arg_error (p_api_name => l_proc, p_argument => 'First Name', p_argument_value => p_first_name );
115 hr_utility.set_location('Person validation :: ',1);
116 -- lookup chk :: Residence, Citizenship
117 IF nvl(p_per_information1,hr_api.g_varchar2) <> hr_api.g_varchar2 THEN
118 check_lookup_value(hr_general.decode_lookup('RO_FORM_LABELS','RESIDENCE'),p_per_information1,'PER_RO_RESIDENCE',p_hire_date);
119 END IF;
120 IF nvl(p_per_information2,hr_api.g_varchar2) <> hr_api.g_varchar2 THEN
121 check_lookup_value(hr_general.decode_lookup('RO_FORM_LABELS','CITIZENSHIP'),p_per_information2,'PER_RO_CITIZENSHIP',p_hire_date);
122 END IF;
123 hr_utility.set_location('Person validation :: ',2);
124 -- country x region x town
125 IF p_country_of_birth = 'RO' THEN
126 IF nvl(p_region_of_birth,hr_api.g_varchar2) <> hr_api.g_varchar2 THEN
127 l_county_code := hr_api.return_lookup_code(p_region_of_birth,'RO_PER_COUNTIES');
128 IF p_town_of_birth IS NOT NULL THEN
129 OPEN csr_validate_locality(p_town_of_birth,p_region_of_birth,p_business_group_id);
130 FETCH csr_validate_locality INTO l_valid_locality;
131 IF csr_validate_locality%notfound THEN
132 hr_utility.set_message(800,'HR_520032_LOC_CNTY_INV');
133 hr_utility.raise_error;
134 END IF;
135 END IF;
136 END IF;
137 END IF;
138 hr_utility.set_location('Person validation :: ',3);
139 -- ni validation -- DOB,Birth Place, Gender,residence, citizenship -- npc and previous npc
140 IF nvl(p_national_identifier,hr_api.g_varchar2) <> hr_api.g_varchar2 THEN
141 l_valid_ni := per_ro_ni_validation.validate_ni( p_person_id,
142 p_business_group_id,
143 p_national_identifier,
144 p_sex,
145 p_date_of_birth,
146 p_country_of_birth,
147 p_region_of_birth,
148 p_per_information2,
149 p_per_information1 );
150 hr_utility.set_location('Person validation :: '||l_valid_ni,4);
151 IF l_valid_ni <> 0 THEN
152 IF l_valid_ni = 1 THEN
153 hr_utility.set_message(800,'HR_520037_INVALID_NI_DIGITS');
154 elsif l_valid_ni = 2 THEN
155 hr_utility.set_message(800,'HR_520038_INVALID_NI');
156 elsif l_valid_ni = 3 THEN
157 hr_utility.set_message(800,'HR_520039_INVALID_NI_DOB');
158 elsif l_valid_ni = 4 THEN
159 hr_utility.set_message(800,'HR_520040_INVALID_NI_GENDER');
160 elsif l_valid_ni = 5 THEN
161 hr_utility.set_message(800,'HR_520041_INVALID_NI_BIRTH');
162 END IF;
163 hr_utility.raise_error;
164 -- raise warning when 6
165 END IF;
166 END IF;
167 hr_utility.set_location('Person validation :: '||l_valid_ni,5);
168 IF nvl(p_per_information4,hr_api.g_varchar2) <> hr_api.g_varchar2 THEN
169 l_valid_ni := 10;
170 l_valid_ni := per_ro_ni_validation.validate_ni( p_person_id,
171 p_business_group_id,
172 p_per_information4,
173 p_sex,
174 p_date_of_birth,
175 p_country_of_birth,
176 p_region_of_birth,
177 p_per_information2,
178 p_per_information1 );
179 hr_utility.set_location('Person validation :: '||l_valid_ni,6);
180 IF l_valid_ni <> 0 THEN
181 IF l_valid_ni = 1 THEN
182 hr_utility.set_message(800,'HR_520037_INVALID_NI_DIGITS');
183 elsif l_valid_ni = 2 THEN
184 hr_utility.set_message(800,'HR_520038_INVALID_NI');
185 elsif l_valid_ni = 3 THEN
186 hr_utility.set_message(800,'HR_520039_INVALID_NI_DOB');
187 elsif l_valid_ni = 4 THEN
188 hr_utility.set_message(800,'HR_520040_INVALID_NI_GENDER');
189 elsif l_valid_ni = 5 THEN
190 hr_utility.set_message(800,'HR_520041_INVALID_NI_BIRTH');
191 END IF;
192 hr_utility.raise_error;
193 -- raise warning when 6
194 END IF;
195 hr_utility.set_location('Person validation :: ',7);
196 END IF;
197 END validate_ro_employee;
198
199 PROCEDURE validate_ro_employee_create(
200 p_business_group_id NUMBER,
201 p_first_name VARCHAR2,
202 p_hire_date DATE,
203 p_date_of_birth DATE,
204 p_sex VARCHAR2, -- gender
205 p_national_identifier VARCHAR2,
206 p_country_of_birth VARCHAR2,
207 p_region_of_birth VARCHAR2,
208 p_town_of_birth VARCHAR2,
209 p_per_information1 VARCHAR2, -- residence
210 p_per_information2 VARCHAR2, -- citizenship
211 p_per_information4 VARCHAR2 -- previous npc/frn
212 )
213 AS
214 l_person_id NUMBER DEFAULT NULL;
215 BEGIN
216 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
217 hr_utility.trace('RO not installed.Leaving person validation');
218 RETURN;
219 END IF;
220
221 validate_ro_employee(
222 l_person_id ,
223 p_business_group_id,
224 p_first_name ,
225 p_hire_date ,
226 p_date_of_birth ,
227 p_sex , -- gender
228 p_national_identifier,
229 p_country_of_birth ,
230 p_region_of_birth ,
231 p_town_of_birth ,
232 p_per_information1 , -- residence
233 p_per_information2 , -- citizenship
234 p_per_information4 -- previous npc/frn
235 );
236 END validate_ro_employee_create;
237
238 PROCEDURE validate_ro_employee_update(
239 p_person_id NUMBER,
240 --p_business_group_id Number,
241 p_first_name VARCHAR2,
242 --p_hire_date DATE,
243 p_date_of_birth DATE,
244 p_sex VARCHAR2, -- gender
245 p_national_identifier VARCHAR2,
246 p_country_of_birth VARCHAR2,
247 p_region_of_birth VARCHAR2,
248 p_town_of_birth VARCHAR2,
249 p_per_information1 VARCHAR2, -- residence
250 p_per_information2 VARCHAR2, -- citizenship
251 p_per_information4 VARCHAR2, -- previous npc/frn
252 p_effective_date DATE )
253 AS
254 l_business_group_id NUMBER;
255 l_hire_date DATE;
256 l_national_identifier varchar2(13);
257 l_sex varchar2(1);
258 l_country_of_birth varchar2(6);
259 l_region_of_birth varchar2(100);
260 l_per_information1 varchar2(10);
261 l_per_information2 varchar2(10);
262 l_per_information4 varchar2(13);
263 BEGIN
264 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
265 hr_utility.trace('RO not installed.Leaving person validation');
266 RETURN;
267 END IF;
268 BEGIN
269 SELECT BUSINESS_GROUP_ID,
270 ORIGINAL_DATE_OF_HIRE
271 INTO l_BUSINESS_GROUP_ID,
272 l_HIRE_DATE
273 FROM PER_ALL_PEOPLE_F
274 WHERE PERSON_ID = P_PERSON_ID
275 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
276 EXCEPTION
277 WHEN OTHERS THEN
278 NULL;
279 END;
280
281 l_national_identifier := p_national_identifier;
282 IF (p_national_identifier = hr_api.g_varchar2) THEN
283 BEGIN
284 SELECT national_identifier
285 INTO l_national_identifier
286 FROM PER_ALL_PEOPLE_F
287 WHERE PERSON_ID = P_PERSON_ID
288 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
289 EXCEPTION
290 WHEN OTHERS THEN
291 hr_utility.trace('Unable to fetch National Identifier');
292 END;
293 END IF;
294
295 l_sex := p_sex;
296 IF (p_sex = hr_api.g_varchar2) THEN
297 BEGIN
298 SELECT sex
299 INTO l_sex
300 FROM PER_ALL_PEOPLE_F
301 WHERE PERSON_ID = P_PERSON_ID
302 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
303 EXCEPTION
304 WHEN OTHERS THEN
305 hr_utility.trace('Unable to fetch Gender');
306 END;
307 END IF;
308
309 l_country_of_birth := p_country_of_birth;
310 IF (p_country_of_birth = hr_api.g_varchar2) THEN
311 BEGIN
312 SELECT country_of_birth
313 INTO l_country_of_birth
314 FROM PER_ALL_PEOPLE_F
315 WHERE PERSON_ID = P_PERSON_ID
316 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
317 EXCEPTION
318 WHEN OTHERS THEN
319 hr_utility.trace('Unable to fetch country_of_birth');
320 END;
321 END IF;
322
323 l_region_of_birth := p_region_of_birth;
324 IF (p_region_of_birth = hr_api.g_varchar2) THEN
325 BEGIN
326 SELECT region_of_birth
327 INTO l_region_of_birth
328 FROM PER_ALL_PEOPLE_F
329 WHERE PERSON_ID = P_PERSON_ID
330 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
331 EXCEPTION
332 WHEN OTHERS THEN
333 hr_utility.trace('Unable to fetch region_of_birth');
334 END;
335 END IF;
336
337 l_per_information1 := p_per_information1;
338 IF (p_per_information1 = hr_api.g_varchar2) THEN
339 BEGIN
340 SELECT per_information1
341 INTO l_per_information1
342 FROM PER_ALL_PEOPLE_F
343 WHERE PERSON_ID = P_PERSON_ID
344 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
345 EXCEPTION
346 WHEN OTHERS THEN
347 hr_utility.trace('Unable to fetch Residence');
348 END;
349 END IF;
350
351 l_per_information2 := p_per_information2;
352 IF (p_per_information2 = hr_api.g_varchar2) THEN
353 BEGIN
354 SELECT per_information2
355 INTO l_per_information2
356 FROM PER_ALL_PEOPLE_F
357 WHERE PERSON_ID = P_PERSON_ID
358 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
359 EXCEPTION
360 WHEN OTHERS THEN
361 hr_utility.trace('Unable to fetch Citizenship');
362 END;
363 END IF;
364
365 l_per_information4 := p_per_information4;
366 IF (p_per_information4 = hr_api.g_varchar2) THEN
367 BEGIN
368 SELECT per_information4
369 INTO l_per_information4
370 FROM PER_ALL_PEOPLE_F
371 WHERE PERSON_ID = P_PERSON_ID
372 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
373 EXCEPTION
374 WHEN OTHERS THEN
375 hr_utility.trace('Unable to fetch Previous NPC/FRN');
376 END;
377 END IF;
378
379 validate_ro_employee(
380 p_person_id ,
381 l_business_group_id,
382 p_first_name ,
383 l_hire_date ,
384 p_date_of_birth ,
385 p_sex , -- gender
386 p_national_identifier,
387 p_country_of_birth ,
388 p_region_of_birth ,
389 p_town_of_birth ,
390 p_per_information1 , -- residence
391 p_per_information2 , -- citizenship
392 p_per_information4 -- previous npc/frn
393 );
394 END validate_ro_employee_update;
395
396 end hr_ro_person_validate;
397