[Home] [Help]
PACKAGE BODY: APPS.PER_MX_VALIDATIONS
Source
1 PACKAGE BODY per_mx_validations AS
2 /* $Header: permxval.pkb 120.2.12000000.4 2007/09/13 05:58:55 srikared noship $ */
3
4 --------------------------------------------------------------------------
5 -- --
6 -- Name : check_RFC --
7 -- Type : Procedure --
8 -- Access : Public --
9 -- Description : Procedure to validate RFC ID --
10 -- Parameters : --
11 -- IN : p_rfc_id VARCHAR2 --
12 -- p_person_id NUMBER --
13 -- p_business_group_id NUMBER --
14 -- OUT : p_warning VARCHAR2 --
15 -- RETURN : N/A --
16 -- --
17 --------------------------------------------------------------------------
18 PROCEDURE check_RFC( p_rfc_id IN VARCHAR2,
19 p_person_id IN NUMBER,
20 p_business_group_id IN NUMBER,
21 p_warning OUT NOCOPY VARCHAR2,
22 p_valid_rfc OUT NOCOPY VARCHAR2) AS
23
24 l_valid VARCHAR2(30);
25 l_status VARCHAR2(1);
26 l_warning VARCHAR2(1);
27 l_chk_unique BOOLEAN;
28 BEGIN
29 --
30
31 -- Initialization of variables.
32 --
33 p_valid_rfc := p_rfc_id;
34 l_chk_unique := TRUE;
35 l_valid := p_rfc_id;
36 l_warning := 'N';
37
38 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'NONE' THEN
39
40 IF p_rfc_id IS NOT NULL THEN
41
42 -- Call the core package to perform the format check on the RFC ID
43 -- Added format mask (Bug 3566864)
44 -- Last character to allow alpha-numerics (Bug 4033022)
45 --
46 l_valid := hr_ni_chk_pkg.chk_nat_id_format( p_rfc_id, 'AAAA-DDDDDD-XXX');
47
48 IF l_valid = '0' THEN
49
50 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'WARN' THEN
51 hr_utility.set_message(800,'HR_MX_INVALID_RFC');
52 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'ERROR'));
53 hr_utility.raise_error;
54 -- p_warning := l_warning;
55 ELSE
56 hr_utility.set_message(800,'HR_MX_INVALID_RFC');
57 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
58 l_warning := 'Y';
59
60 END IF;
61
62 p_warning := l_warning;
63
64 -- Uniqueness need not be verified, since the RFC ID is invalid
65 --
66 l_chk_unique := FALSE;
67 END IF;
68
69 END IF;
70
71 END IF;
72
73 IF (l_chk_unique and p_rfc_id IS NOT NULL) THEN
74
75 -- Check for uniqueness of RFC ID
76 --
77 BEGIN
78 SELECT 'Y'
79 INTO l_status
80 FROM sys.dual
81 WHERE exists(SELECT /*+index(pp)*/'1'
82 FROM per_all_people_f pp
83 WHERE (p_person_id IS NULL
84 OR p_person_id <> pp.person_id)
85 AND translate(l_valid, '1-', '1') = translate(pp.per_information2, '1-', '1')
86 AND pp.business_group_id +0 = p_business_group_id);
87
88 IF fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') = 'ERROR' THEN
89 hr_utility.set_message(800,'HR_MX_RFC_UNIQUE_ERROR');
90 hr_utility.set_message_token( 'ACTION', ' ');
91 hr_utility.raise_error;
92 ELSE
93 hr_utility.set_message(800,'HR_MX_RFC_UNIQUE_ERROR');
94 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
95 l_warning := 'Y';
96 END IF;
97 p_warning := l_warning;
98
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 p_valid_rfc := l_valid;
102 END;
103
104 END IF;
105
106 EXCEPTION
107 WHEN VALUE_ERROR THEN
108 hr_utility.set_message(800,'HR_MX_INVALID_RFC');
109 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'ERROR'));
110 hr_utility.raise_error;
111 --
112 END check_RFC;
113
114 --------------------------------------------------------------------------
115 -- --
116 -- Name : check_SS --
117 -- Type : Procedure --
118 -- Access : Public --
119 -- Description : Procedure to validate Social Security ID --
120 -- Parameters : --
121 -- IN : p_ss_id VARCHAR2 --
122 -- p_person_id NUMBER --
123 -- p_business_group_id NUMBER --
124 -- OUT : p_warning VARCHAR2 --
125 -- p_valid_ss VARCHAR2 --
126 -- RETURN : N/A --
127 -- --
128 --------------------------------------------------------------------------
129 PROCEDURE check_SS( p_ss_id IN VARCHAR2,
130 p_person_id IN NUMBER,
131 p_business_group_id IN NUMBER,
132 p_warning OUT NOCOPY VARCHAR2,
133 p_valid_ss OUT NOCOPY VARCHAR2) AS
134
135 l_valid VARCHAR2(30);
136 l_status VARCHAR2(1);
137 l_warning VARCHAR2(1);
138 l_chk_unique BOOLEAN;
139
140 BEGIN
141
142 -- Initialization of variables.
143 --
144 p_valid_ss := p_ss_id;
145 l_chk_unique := TRUE;
146 l_valid := p_ss_id;
147 l_warning := 'N';
148
149
150 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' THEN
151
152 IF p_ss_id IS NOT NULL THEN
153
154 -- Call the core package to perform the format check on the SS ID
155 --
156 l_valid := hr_ni_chk_pkg.chk_nat_id_format( p_ss_id, 'DD-DD-DD-DDDD-D');
157
158 IF l_valid = '0' THEN
159
160 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'WARN' THEN
161 hr_utility.set_message(800,'HR_MX_INVALID_SS');
162 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'ERROR'));
163 hr_utility.raise_error;
164 ELSE
165 hr_utility.set_message(800,'HR_MX_INVALID_SS');
166 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
167 l_warning := 'Y';
168
169 END IF;
170
171 -- Uniqueness need not be verified, since the SS ID is invalid
172 --
173 p_warning := l_warning;
174 l_chk_unique := FALSE;
175
176 END IF;
177
178 END IF;
179
180 END IF;
181
182 IF (l_chk_unique and p_ss_id IS NOT NULL) THEN
183
184 -- Check for uniqueness of SS ID. The format characters
185 -- will be removed before comparison.
186 --
187 BEGIN
188 SELECT 'Y'
189 INTO l_status
190 FROM sys.dual
191 WHERE exists(SELECT /*+INDEX(pp)*/'1'
192 FROM per_all_people_f pp
193 WHERE (p_person_id IS NULL
194 OR p_person_id <> pp.person_id)
195 AND translate(l_valid, '1-', '1') = translate(pp.per_information3, '1-', '1')
196 AND pp.business_group_id +0 = p_business_group_id);
197
198 IF fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') = 'ERROR' THEN
199 hr_utility.set_message(800,'HR_MX_SS_UNIQUE_ERROR');
200 hr_utility.set_message_token( 'ACTION', ' ');
201 hr_utility.raise_error;
202 ELSE
203 hr_utility.set_message(800,'HR_MX_SS_UNIQUE_ERROR');
204 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
205 l_warning := 'Y';
206 END IF;
207 p_warning := l_warning;
208
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 p_valid_ss := l_valid;
212
213 END;
214
215 END IF;
216
217 EXCEPTION
218 WHEN VALUE_ERROR THEN
219 hr_utility.set_message(800,'HR_MX_INVALID_SS');
220 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'ERROR'));
221 hr_utility.raise_error;
222
223 --
224 END check_SS;
225
226 --------------------------------------------------------------------------
227 -- --
228 -- Name : check_MS --
229 -- Type : Procedure --
230 -- Access : Public --
231 -- Description : Procedure to validate Military Service ID --
232 -- Parameters : --
233 -- IN : p_ms_id VARCHAR2 --
234 -- p_person_id NUMBER --
235 -- p_business_group_id NUMBER --
236 -- OUT : p_warning VARCHAR2 --
237 -- RETURN : N/A --
238 -- --
239 --------------------------------------------------------------------------
240 PROCEDURE check_MS( p_ms_id IN VARCHAR2,
241 p_person_id IN NUMBER,
242 p_business_group_id IN NUMBER,
243 p_warning OUT NOCOPY VARCHAR2) AS
244
245 l_valid VARCHAR2(11);
246 l_status VARCHAR2(1);
247 l_warning VARCHAR2(1);
248
249 BEGIN
250
251 -- Initialization of variables.
252 --
253 l_valid := '1';
254 l_warning := 'N';
255
256 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'NONE' THEN
257
258 IF p_ms_id IS NOT NULL THEN
259
260 --
261 -- Check if Military Service ID is 13 characters long
262 --
263 IF length(p_ms_id) <> 13 THEN
264
265 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'WARN' THEN
266 hr_utility.set_message(800,'HR_MX_INVALID_MS');
267 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'ERROR'));
268 hr_utility.raise_error;
269 ELSE
270 hr_utility.set_message(800,'HR_MX_INVALID_MS');
271 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
272
273 l_warning := 'Y';
274
275 END IF;
276
277 p_warning := l_warning;
278 l_valid := '0';
279 END IF;
280
281 END IF;
285 IF l_valid = '1' THEN
282
283 END IF;
284
286
287 -- Check for uniqueness of Military Service ID
288 --
289 BEGIN
290 SELECT 'Y'
291 INTO l_status
292 FROM sys.dual
293 WHERE exists(SELECT '1'
294 FROM per_all_people_f pp
295 WHERE (p_person_id IS NULL
296 OR p_person_id <> pp.person_id)
297 AND p_ms_id = pp.per_information6
298 AND pp.business_group_id = p_business_group_id);
299
300 IF fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') = 'ERROR' THEN
301 hr_utility.set_message(800,'HR_MX_MS_UNIQUE_ERROR');
302 hr_utility.set_message_token( 'ACTION', ' ');
303 hr_utility.raise_error;
304 ELSE
305 hr_utility.set_message(800,'HR_MX_MS_UNIQUE_ERROR');
306 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
307 l_warning := 'Y';
308 END IF;
309 p_warning := l_warning;
310
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN null;
313 END;
314
315 END IF;
316
317 --
318 END check_MS;
319
320 --------------------------------------------------------------------------
321 -- --
322 -- Name : check_FGA --
323 -- Type : Procedure --
324 -- Access : Public --
325 -- Description : Procedure to validate Federal Gov. Affiliation ID --
326 -- Parameters : --
327 -- IN : p_fga_id VARCHAR2 --
328 -- p_person_id NUMBER --
329 -- p_business_group_id NUMBER --
330 -- OUT : p_warning VARCHAR2 --
331 -- RETURN : N/A --
332 -- --
333 --------------------------------------------------------------------------
334 PROCEDURE check_FGA( p_fga_id IN VARCHAR2,
335 p_person_id IN NUMBER,
336 p_business_group_id IN NUMBER,
337 p_warning OUT NOCOPY VARCHAR2) AS
338
339 l_valid VARCHAR2(11);
340 l_status VARCHAR2(1);
341 l_warning VARCHAR2(1);
342
343 BEGIN
344
345 -- Initialization of variables.
346 --
347 l_valid := '1';
348 l_warning := 'N';
349
350 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <>'NONE' THEN
351
352 IF p_fga_id IS NOT NULL THEN
353
354 --
355 -- Check if Federal Government Affiliation ID is 13 characters long
356 --
357 IF length(p_fga_id) <> 13 THEN
358
359 IF fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'WARN' THEN
360 hr_utility.set_message(800,'HR_MX_INVALID_FGA');
361 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'ERROR'));
362 hr_utility.raise_error;
363 ELSE
364 hr_utility.set_message(800,'HR_MX_INVALID_FGA');
365 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
366 l_warning := 'Y';
367
368 END IF;
369
370 p_warning := l_warning;
371 l_valid := '0';
372 END IF;
373
374 END IF;
375
376 END IF;
377
378 IF l_valid = '1' THEN
379
380 -- Check for uniqueness of Federal Government Affiliation ID
381 --
382 BEGIN
383 SELECT 'Y'
384 INTO l_status
385 FROM sys.dual
386 WHERE exists(SELECT '1'
387 FROM per_all_people_f pp
388 WHERE (p_person_id IS NULL
389 OR p_person_id <> pp.person_id)
390 AND p_fga_id = pp.per_information5
391 AND pp.business_group_id = p_business_group_id);
392
393 IF fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') = 'ERROR' THEN
394 hr_utility.set_message(800,'HR_MX_FGA_UNIQUE_ERROR');
395 hr_utility.set_message_token( 'ACTION', ' ');
396 hr_utility.raise_error;
397 ELSE
398 hr_utility.set_message(800,'HR_MX_FGA_UNIQUE_ERROR');
399 hr_utility.set_message_token( 'ACTION', hr_general.decode_lookup('MX_ACTION_TOKEN', 'WARN'));
400 l_warning := 'Y';
401 END IF;
402 p_warning := l_warning;
403
404 EXCEPTION
405 WHEN NO_DATA_FOUND THEN null;
409
406 END;
407
408 END IF;
410 --
411 END check_FGA;
412
413 --------------------------------------------------------------------------
414 -- --
415 -- Name : check_IMC --
416 -- Type : Procedure --
417 -- Access : Public --
418 -- Description : Procedure to validate the IMSS Medical Center --
419 -- Parameters : --
420 -- IN : p_imc_id VARCHAR2 --
421 -- OUT : N/A --
422 -- RETURN : N/A --
423 -- --
424 --------------------------------------------------------------------------
425 PROCEDURE check_IMC( p_imc_id IN VARCHAR2) AS
426
427 BEGIN
428 --
429 BEGIN
430 --
431 IF p_imc_id IS NOT NULL THEN
432 --
433 -- Check if IMSS Medical Center is upto 3 digits long
434 --
435 IF to_number(p_imc_id) > 999 OR to_number(p_imc_id) < 0 THEN
436
437 hr_utility.set_message(800,'HR_MX_INVALID_IMC');
438 hr_utility.raise_error;
439
440 END IF;
441 END IF;
442
443 EXCEPTION
444
445 WHEN VALUE_ERROR THEN
446 --
447 -- Raise error when non-numeric characters are present in the IMSS Medical Center
448 --
449 hr_utility.set_message(800,'HR_MX_INVALID_IMC');
450 hr_utility.raise_error;
451
452 END;
453
454 --
455 END check_IMC;
456
457 --------------------------------------------------------------------------
458 -- --
459 -- Name : check_regstrn_ID --
460 -- Type : Procedure --
461 -- Access : Public --
462 -- Description : Procedure to validate the Disability (Registration) --
463 -- ID. --
464 -- Parameters : --
465 -- IN : p_disab_id VARCHAR2 --
466 -- p_regstrn_id VARCHAR2 --
467 -- OUT : N/A --
468 -- RETURN : N/A --
469 -- --
470 --------------------------------------------------------------------------
471 PROCEDURE check_regstrn_id( p_regstrn_id IN VARCHAR2,
472 p_disab_id IN NUMBER) AS
473
474 l_valid VARCHAR2(30);
475 l_status VARCHAR2(1);
476
477 BEGIN
478
479 IF p_regstrn_id IS NOT NULL THEN
480
481 -- Call the core package to perform the format check on the Registration ID
482 --
483 l_valid := hr_ni_chk_pkg.chk_nat_id_format( p_regstrn_id, 'AADDDDDD');
484
485 IF l_valid = '0' THEN
486
487 hr_utility.set_message(800,'HR_MX_INVALID_DISAB_ID');
488 hr_utility.raise_error;
489
490 END IF;
491
492 -- Check for uniqueness of Registration ID.
493 --
494 BEGIN
495 SELECT 'Y'
496 INTO l_status
497 FROM sys.dual
498 WHERE exists(SELECT /*+index(pdf per_disabilities_f_pk)*/'1'
499 FROM per_disabilities_f pdf
500 WHERE (p_disab_id IS NULL
501 OR p_disab_id <> pdf.disability_id)
502 AND l_valid = pdf.registration_id
503 );
504
505 hr_utility.set_message(800,'HR_MX_DISAB_ID_UNIQUE_ERROR');
506 hr_utility.raise_error;
507
508 EXCEPTION
509 WHEN NO_DATA_FOUND THEN
510 null;
511
512 END;
513
514 END IF;
515
516 --
517 END check_regstrn_id;
518
519 --------------------------------------------------------------------------
520 -- --
521 -- Name : check_SS_Leaving_Reason --
522 -- Type : Procedure --
523 -- Access : Public --
524 -- Description : Procedure to validate the SS Leaving Reason lookup --
525 -- code. --
526 -- Parameters : --
527 -- IN : p_ss_leaving_reason VARCHAR2 --
528 -- OUT : N/A --
529 -- RETURN : N/A --
530 -- --
531 --------------------------------------------------------------------------
532 PROCEDURE check_SS_Leaving_Reason( p_ss_leaving_reason IN VARCHAR2) AS
533
534 BEGIN
535 --
536 IF p_ss_leaving_reason IS NOT NULL THEN
537 --
538 -- Check if the lookup_code for leaving reason exists in the lookup
539 --
543 hr_utility.set_message_token( 'LOOKUP_TYPE', 'MX_STAT_IMSS_LEAVING_REASON');
540 IF hr_general.decode_lookup('MX_STAT_IMSS_LEAVING_REASON', p_ss_leaving_reason) IS NULL THEN
541
542 hr_utility.set_message(800,'HR_MX_INVALID_LOOKUP_CODE');
544 hr_utility.set_message_token( 'LOOKUP_CODE', p_ss_leaving_reason);
545 hr_utility.raise_error;
546
547 END IF;
548 END IF;
549 --
550 END check_SS_Leaving_Reason;
551
552 --
553 END per_mx_validations;