1 PACKAGE BODY HR_KW_VALIDATE_PKG AS
2 /* $Header: pekwvald.pkb 120.10 2006/12/29 14:19:24 spendhar noship $ */
3 g_type VARCHAR2(1) := NULL;
4 g_per_type VARCHAR2(1) := NULL;
5 PROCEDURE VALIDATE
6 (p_person_type_id in number
7 ,p_sex in varchar2
8 ,p_first_name in varchar2 default null
9 ,p_nationality in varchar2 default null
10 ,p_national_identifier in varchar2 default null
11 ,p_title in varchar2 default null
12 ,p_per_information_category in varchar2 default null
13 ,p_per_information1 in varchar2 default null
14 ,p_per_information2 in varchar2 default null
15 ,p_per_information3 in varchar2 default null
16 ,p_per_information4 in varchar2 default null
17 ,p_per_information5 in varchar2 default null
18 ,p_per_information6 in varchar2 default null
19 ,p_per_information7 in varchar2 default null
20 ,p_per_information8 in varchar2 default null
21 ,p_per_information9 in varchar2 default null
22 ,p_per_information10 in varchar2 default null
23 ) IS
24 v_field varchar2(300);
25 l_valid_date varchar2(10);
26 CURSOR c_type IS
27 SELECT /*+ INDEX(ppt,PER_PERSON_TYPES_PK) */ 'Y'
28 FROM per_person_types ppt
29 WHERE ppt.system_person_type IN ('EMP','APL')
30 AND ppt.person_type_Id = p_person_type_id;
31 CURSOR c_per_type IS
32 SELECT 'Y'
33 FROM per_person_types ppt
34 WHERE ppt.system_person_type LIKE 'EMP%'
35 AND ppt.person_type_Id = p_person_type_id;
36 BEGIN
37
38 /* Added for GSI Bug 5472781 */
39 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
40
41 v_field := NULL;
42 g_type := NULL;
43 g_per_type := NULL;
44 IF p_per_information_category = 'KW' THEN
45
46 OPEN c_per_type;
47 FETCH c_per_type INTO g_per_type;
48 CLOSE c_per_type;
49
50 --Validate not null fields
51 IF g_per_type IS NOT NULL THEN -- for fixing Bug 4436984
52 IF p_first_name IS NULL THEN
53 IF v_field IS NULL THEN
54 v_field := hr_general.decode_lookup('KW_FORM_LABELS','M_FIRST_NAME');
55 ELSE
56 v_field := v_field||', '||hr_general.decode_lookup('KW_FORM_LABELS','M_FIRST_NAME');
57 END IF;
58 END IF;
59 END IF; -- end for EMP check
60
61
62 -- For enhancement 4522277. Making national id non mandatory for all person types.
63
64 /*IF g_per_type IS NOT NULL THEN
65
66 IF p_national_identifier IS NULL THEN
67 IF v_field IS NULL THEN
68 v_field := hr_general.decode_lookup('KW_FORM_LABELS','CIVIL_IDENTIFIER');
69 ELSE
70 v_field := v_field||hr_general.decode_lookup('KW_FORM_LABELS','CIVIL_IDENTIFIER');
71 END IF;
72 END IF;
73 END IF;*/
74
75 /*IF g_per_type IS NOT NULL THEN
76 IF p_per_information3 IS NULL THEN
77 IF v_field IS NULL THEN
78 v_field := hr_general.decode_lookup('KW_FORM_LABELS','M_ALT_FIRST_NAME');
79 ELSE
80 v_field := v_field||', '||hr_general.decode_lookup('KW_FORM_LABELS','M_ALT_FIRST_NAME');
81 END IF;
82 END IF;
83 END IF;
84
85 IF g_per_type IS NOT NULL THEN
86 IF p_per_information6 IS NULL THEN
87 IF v_field IS NULL THEN
88 v_field := hr_general.decode_lookup('KW_FORM_LABELS','M_ALT_FAMILY_NAME');
89 ELSE
90 v_field := v_field||', '||hr_general.decode_lookup('KW_FORM_LABELS','M_ALT_FAMILY_NAME');
91 END IF;
92 END IF;
93 END IF;*/ -- Removed with reference to bug #4150446
94
95 OPEN c_type;
96 FETCH c_type INTO g_type;
97 CLOSE c_type;
98 IF g_per_type IS NOT NULL THEN -- for fixing Bug 4436984
99 IF p_nationality IS NULL THEN
100 IF v_field IS NULL THEN
101 v_field := hr_general.decode_lookup('KW_FORM_LABELS','NATIONALITY');
102 ELSE
103 v_field := v_field||', '||hr_general.decode_lookup('KW_FORM_LABELS','NATIONALITY');
104 END IF;
105 END IF;
106 END IF;
107 IF v_field IS NOT NULL THEN
108 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
109 fnd_message.set_token('NAME',v_field, translate => true );
110 hr_utility.raise_error;
111 END IF;
112 END IF;
113 END IF;
114 END VALIDATE;
115 --Procedure for validating person
116 PROCEDURE PERSON_VALIDATE
117 (p_person_id in number
118 ,p_person_type_id in number
119 ,p_effective_date in date
120 ,p_sex in varchar2
121 ,p_first_name in varchar2 default null
122 ,p_nationality in varchar2 default null
123 ,p_national_identifier in varchar2 default null
124 ,p_title in varchar2 default null
125 ,p_per_information_category in varchar2 default null
126 ,p_per_information1 in varchar2 default null
127 ,p_per_information2 in varchar2 default null
128 ,p_per_information3 in varchar2 default null
129 ,p_per_information4 in varchar2 default null
130 ,p_per_information5 in varchar2 default null
131 ,p_per_information6 in varchar2 default null
132 ,p_per_information7 in varchar2 default null
133 ,p_per_information8 in varchar2 default null
134 ,p_per_information9 in varchar2 default null
135 ,p_per_information10 in varchar2 default null
136 ) IS
137 CURSOR csr_person_type_id IS
138 SELECT person_type_id
139 FROM per_all_people_f
140 WHERE person_id = p_person_id
141 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
142 l_person_type_id NUMBER;
143 l_person_type VARCHAR2(20);
144 CURSOR chk_person_type IS
145 SELECT 'Y'
146 FROM per_person_types ppt
147 WHERE ppt.system_person_type IN ('CWK')
148 AND ppt.person_type_id = p_person_type_id;
149 BEGIN
150 OPEN csr_person_type_id;
151 FETCH csr_person_type_id INTO l_person_type_id;
152 CLOSE csr_person_type_id;
153 l_person_type := NULL;
154 OPEN chk_person_type;
155 FETCH chk_person_type INTO l_person_type;
156 CLOSE chk_person_type;
157 IF NVL(l_person_type,'N') <> 'Y' THEN
158 validate
159 (p_person_type_id => l_person_type_id
160 ,p_sex => p_sex
161 ,p_first_name => p_first_name
162 ,p_nationality => p_nationality
163 ,p_national_identifier => p_national_identifier
164 ,p_title => p_title
165 ,p_per_information_category => p_per_information_category
166 ,p_per_information1 => p_per_information1
167 ,p_per_information2 => p_per_information2
168 ,p_per_information3 => p_per_information3
169 ,p_per_information4 => p_per_information4
170 ,p_per_information5 => p_per_information5
171 ,p_per_information6 => p_per_information6
172 ,p_per_information7 => p_per_information7
173 ,p_per_information8 => p_per_information8
174 ,p_per_information9 => p_per_information9
175 ,p_per_information10 => p_per_information10);
176 END IF;
177
178 /* Added for GSI Bug 5472781 */
179 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
180
181 if g_type IS NOT NULL THEN
182
183 if p_per_information_category = 'KW' and (p_per_information8 is not null and p_per_information8 <> hr_api.g_varchar2 ) then
184 --
185 -- Check that the religion exists in hr_lookups for the
186 -- lookup type 'GCC_RELIGION' with an enabled flag set to 'Y' and that
187 -- the effective start date of the person is between start date
188 -- active and end date active in hr_lookups.
189 --
190 if hr_api.not_exists_in_hr_lookups
191 (p_effective_date => p_effective_date
192 ,p_lookup_type => 'GCC_RELIGION'
193 ,p_lookup_code => p_per_information8
194 )
195 then
196 --
197 hr_utility.set_message(800, 'HR_375407_KW_INVALID_RELIGION');
198 hr_utility.raise_error;
199 --
200 end if;
201 end if;
202
203 /* Added for previous nationality check */
204
205 if p_per_information_category = 'KW' and (p_per_information7 is not null and p_per_information7 <> hr_api.g_varchar2 )then
206
207 if hr_api.not_exists_in_hr_lookups
208 (p_effective_date => p_effective_date
209 ,p_lookup_type => 'NATIONALITY'
210 ,p_lookup_code => p_per_information7
211 )
212 then
213 --
214 hr_utility.set_message(800, 'HR_375411_KW_INVALID_PREV_NAT');
215 hr_utility.raise_error;
216 --
217 end if;
218 end if;
219
220 end if;
221 END IF;
222 END PERSON_VALIDATE;
223 --Procedure for validating applicant
224 PROCEDURE APPLICANT_VALIDATE
225 (p_business_group_id in number
226 ,p_person_type_id in number
227 ,p_date_received in date
228 ,p_sex in varchar2
229 ,p_first_name in varchar2 default null
230 ,p_nationality in varchar2 default null
231 ,p_national_identifier in varchar2 default null
232 ,p_title in varchar2 default null
233 ,p_per_information_category in varchar2 default null
234 ,p_per_information1 in varchar2 default null
235 ,p_per_information2 in varchar2 default null
236 ,p_per_information3 in varchar2 default null
237 ,p_per_information4 in varchar2 default null
238 ,p_per_information5 in varchar2 default null
239 ,p_per_information6 in varchar2 default null
240 ,p_per_information7 in varchar2 default null
241 ,p_per_information8 in varchar2 default null
242 ,p_per_information9 in varchar2 default null
243 ,p_per_information10 in varchar2 default null
244 ) IS
245 l_person_type_id NUMBER;
246 BEGIN
247 per_per_bus.chk_person_type
248 (p_person_type_id => l_person_type_id
249 ,p_business_group_id => p_business_group_id
250 ,p_expected_sys_type => 'APL'
251 );
252 validate
253 (p_person_type_id => l_person_type_id
254 ,p_sex => p_sex
255 ,p_first_name => p_first_name
256 ,p_nationality => p_nationality
257 ,p_national_identifier => p_national_identifier
258 ,p_title => p_title
259 ,p_per_information_category => p_per_information_category
260 ,p_per_information1 => p_per_information1
261 ,p_per_information2 => p_per_information2
262 ,p_per_information3 => p_per_information3
263 ,p_per_information4 => p_per_information4
264 ,p_per_information5 => p_per_information5
265 ,p_per_information6 => p_per_information6
266 ,p_per_information7 => p_per_information7
267 ,p_per_information8 => p_per_information8
268 ,p_per_information9 => p_per_information9
269 ,p_per_information10 => p_per_information10);
270
271 /* Added for GSI Bug 5472781 */
272 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
273
274 if g_type IS NOT NULL THEN
275
276 if p_per_information_category = 'KW' and (p_per_information8 is not null and p_per_information8 <> hr_api.g_varchar2 ) then
277 --
278 -- Check that the religion exists in hr_lookups for the
279 -- lookup type 'GCC_RELIGION' with an enabled flag set to 'Y' and that
280 -- the effective start date of the person is between start date
281 -- active and end date active in hr_lookups.
282 --
283 if hr_api.not_exists_in_hr_lookups
284 (p_effective_date => p_date_received
285 ,p_lookup_type => 'GCC_RELIGION'
286 ,p_lookup_code => p_per_information8
287 )
288 then
289 --
290 hr_utility.set_message(800, 'HR_375407_KW_INVALID_RELIGION');
291 hr_utility.raise_error;
292 --
293 end if;
294 end if;
295
296 /* Added for previous nationality check */
297 if p_per_information_category = 'KW' and (p_per_information7 is not null and p_per_information7 <> hr_api.g_varchar2 ) then
298
299 if hr_api.not_exists_in_hr_lookups
300 (p_effective_date => p_date_received
301 ,p_lookup_type => 'NATIONALITY'
302 ,p_lookup_code => p_per_information7
303 )
304 then
305 --
306 hr_utility.set_message(800, 'HR_375411_KW_INVALID_PREV_NAT');
307 hr_utility.raise_error;
308 --
309 end if;
310 end if;
311 end if;
312 END IF;
313 END APPLICANT_VALIDATE;
314 --Procedure for validating employee
315 PROCEDURE EMPLOYEE_VALIDATE
316 (p_business_group_id in number
317 ,p_person_type_id in number
318 ,p_hire_date in date
319 ,p_sex in varchar2
320 ,p_first_name in varchar2 default null
321 ,p_nationality in varchar2 default null
322 ,p_national_identifier in varchar2 default null
323 ,p_title in varchar2 default null
324 ,p_per_information_category in varchar2 default null
325 ,p_per_information1 in varchar2 default null
326 ,p_per_information2 in varchar2 default null
327 ,p_per_information3 in varchar2 default null
328 ,p_per_information4 in varchar2 default null
329 ,p_per_information5 in varchar2 default null
330 ,p_per_information6 in varchar2 default null
331 ,p_per_information7 in varchar2 default null
332 ,p_per_information8 in varchar2 default null
333 ,p_per_information9 in varchar2 default null
334 ,p_per_information10 in varchar2 default null
335 ) IS
336 l_person_type_id number;
337 l_valid_date varchar2(10);
338 BEGIN
339 per_per_bus.chk_person_type
340 (p_person_type_id => l_person_type_id
341 ,p_business_group_id => p_business_group_id
342 ,p_expected_sys_type => 'EMP'
343 );
344
345 validate
346 (p_person_type_id => l_person_type_id
347 ,p_sex => p_sex
348 ,p_first_name => p_first_name
349 ,p_nationality => p_nationality
350 ,p_national_identifier => p_national_identifier
351 ,p_title => p_title
352 ,p_per_information_category => p_per_information_category
353 ,p_per_information1 => p_per_information1
354 ,p_per_information2 => p_per_information2
355 ,p_per_information3 => p_per_information3
356 ,p_per_information4 => p_per_information4
357 ,p_per_information5 => p_per_information5
358 ,p_per_information6 => p_per_information6
359 ,p_per_information7 => p_per_information7
360 ,p_per_information8 => p_per_information8
361 ,p_per_information9 => p_per_information9
362 ,p_per_information10 => p_per_information10);
363
364 /* Added for GSI Bug 5472781 */
365 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
366
367 if g_type IS NOT NULL THEN
368 if p_per_information_category = 'KW' and (p_per_information8 is not null and p_per_information8 <> hr_api.g_varchar2 ) then
369 --
370 -- Check that the religion exists in hr_lookups for the
371 -- lookup type 'GCC_RELIGION' with an enabled flag set to 'Y' and that
372 -- the effective start date of the person is between start date
373 -- active and end date active in hr_lookups.
374 --
375 if hr_api.not_exists_in_hr_lookups
376 (p_effective_date => p_hire_date
377 ,p_lookup_type => 'GCC_RELIGION'
378 ,p_lookup_code => p_per_information8
379 )
380 then
381 --
382 hr_utility.set_message(800, 'HR_375407_KW_INVALID_RELIGION');
383 hr_utility.raise_error;
384 --
385 end if;
386 end if;
387
388 /* Added for previous nationality check */
389 if p_per_information_category = 'KW' and (p_per_information7 is not null and p_per_information7 <> hr_api.g_varchar2 ) then
390
391 if hr_api.not_exists_in_hr_lookups
392 (p_effective_date => p_hire_date
393 ,p_lookup_type => 'NATIONALITY'
394 ,p_lookup_code => p_per_information7
395 )
396 then
397 --
398 hr_utility.set_message(800, 'HR_375411_KW_INVALID_PREV_NAT');
399 hr_utility.raise_error;
400 --
401 end if;
402 end if;
403
404 end if;
405 END IF;
406 END EMPLOYEE_VALIDATE;
407
408 --Procedure for validating contact
409 PROCEDURE CONTACT_VALIDATE
410 (p_business_group_id in number
411 ,p_person_type_id in number
412 ,p_start_date in date
413 ,p_sex in varchar2
414 ,p_first_name in varchar2 default null
415 ,p_last_name in varchar2 default null
416 ,p_nationality in varchar2 default null
417 ,p_national_identifier in varchar2 default null
418 ,p_title in varchar2 default null
419 ,p_per_information_category in varchar2 default null
420 ,p_per_information1 in varchar2 default null
421 ,p_per_information2 in varchar2 default null
422 ,p_per_information3 in varchar2 default null
423 ,p_per_information4 in varchar2 default null
424 ,p_per_information5 in varchar2 default null
425 ,p_per_information6 in varchar2 default null
426 ,p_per_information7 in varchar2 default null
427 ,p_per_information8 in varchar2 default null
428 ,p_per_information9 in varchar2 default null
429 ,p_per_information10 in varchar2 default null
430 ) IS
431 l_person_type_id NUMBER;
432 BEGIN
433 per_per_bus.chk_person_type
434 (p_person_type_id => l_person_type_id
435 ,p_business_group_id => p_business_group_id
436 ,p_expected_sys_type => 'OTHER'
437 );
438 validate
439 (p_person_type_id => l_person_type_id
443 ,p_national_identifier => p_national_identifier
440 ,p_sex => p_sex
441 ,p_first_name => p_first_name
442 ,p_nationality => p_nationality
444 ,p_title => p_title
445 ,p_per_information_category => p_per_information_category
446 ,p_per_information1 => p_per_information1
447 ,p_per_information2 => p_per_information2
448 ,p_per_information3 => p_per_information3
449 ,p_per_information4 => p_per_information4
450 ,p_per_information5 => p_per_information5
451 ,p_per_information6 => p_per_information6
452 ,p_per_information7 => p_per_information7
453 ,p_per_information8 => p_per_information8
454 ,p_per_information9 => p_per_information9
455 ,p_per_information10 => p_per_information10);
456
457 /* Added for GSI Bug 5472781 */
458 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
459
460 if g_type IS NOT NULL THEN
461 if p_per_information_category = 'KW' and (p_per_information8 is not null and p_per_information8 <> hr_api.g_varchar2 ) then
462 --
463 -- Check that the religion exists in hr_lookups for the
464 -- lookup type 'GCC_RELIGION' with an enabled flag set to 'Y' and that
465 -- the effective start date of the person is between start date
466 -- active and end date active in hr_lookups.
467 --
468 if hr_api.not_exists_in_hr_lookups
469 (p_effective_date => p_start_date
470 ,p_lookup_type => 'GCC_RELIGION'
471 ,p_lookup_code => p_per_information8
472 )
473 then
474 --
475 hr_utility.set_message(800, 'HR_375407_KW_INVALID_RELIGION');
476 hr_utility.raise_error;
477 --
478 end if;
479 end if;
480
481 /* Added for previous nationality check */
482 if p_per_information_category = 'KW' and (p_per_information7 is not null and p_per_information7 <> hr_api.g_varchar2 ) then
483
484 if hr_api.not_exists_in_hr_lookups
485 (p_effective_date => p_start_date
486 ,p_lookup_type => 'NATIONALITY'
487 ,p_lookup_code => p_per_information7
488 )
489 then
490 --
491 hr_utility.set_message(800, 'HR_375411_KW_INVALID_PREV_NAT');
492 hr_utility.raise_error;
493 --
494 end if;
495 end if;
496
497 end if;
498 END IF;
499 END CONTACT_VALIDATE;
500
501 PROCEDURE CWK_VALIDATE
502 (p_business_group_id in number
503 ,p_person_type_id in number
504 ,p_start_date in date
505 ,p_sex in varchar2
506 ,p_first_name in varchar2 default null
507 ,p_nationality in varchar2 default null
508 ,p_national_identifier in varchar2 default null
509 ,p_title in varchar2 default null
510 ,p_per_information_category in varchar2 default null
511 ,p_per_information1 in varchar2 default null
512 ,p_per_information2 in varchar2 default null
513 ,p_per_information3 in varchar2 default null
514 ,p_per_information4 in varchar2 default null
515 ,p_per_information5 in varchar2 default null
516 ,p_per_information6 in varchar2 default null
517 ,p_per_information7 in varchar2 default null
518 ,p_per_information8 in varchar2 default null
519 ,p_per_information9 in varchar2 default null
520 ,p_per_information10 in varchar2 default null
521 ) IS
522 l_person_type_id NUMBER;
523 BEGIN
524 per_per_bus.chk_person_type
525 (p_person_type_id => l_person_type_id
526 ,p_business_group_id => p_business_group_id
527 ,p_expected_sys_type => 'CWK'
528 );
529 validate
530 (p_person_type_id => l_person_type_id
531 ,p_sex => p_sex
532 ,p_first_name => p_first_name
533 ,p_nationality => p_nationality
534 ,p_national_identifier => p_national_identifier
535 ,p_title => p_title
536 ,p_per_information_category => p_per_information_category
537 ,p_per_information1 => p_per_information1
538 ,p_per_information2 => p_per_information2
539 ,p_per_information3 => p_per_information3
540 ,p_per_information4 => p_per_information4
541 ,p_per_information5 => p_per_information5
542 ,p_per_information6 => p_per_information6
543 ,p_per_information7 => p_per_information7
544 ,p_per_information8 => p_per_information8
545 ,p_per_information9 => p_per_information9
546 ,p_per_information10 => p_per_information10);
547
548 /* Added for GSI Bug 5472781 */
549 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
550
551 if g_type IS NOT NULL THEN
552 if p_per_information_category = 'KW' and (p_per_information8 is not null and p_per_information8 <> hr_api.g_varchar2 ) then
553 --
557 -- active and end date active in hr_lookups.
554 -- Check that the religion exists in hr_lookups for the
555 -- lookup type 'GCC_RELIGION' with an enabled flag set to 'Y' and that
556 -- the effective start date of the person is between start date
558 --
559 if hr_api.not_exists_in_hr_lookups
560 (p_effective_date => p_start_date
561 ,p_lookup_type => 'GCC_RELIGION'
562 ,p_lookup_code => p_per_information8
563 )
564 then
565 --
566 hr_utility.set_message(800, 'HR_375407_KW_INVALID_RELIGION');
567 hr_utility.raise_error;
568 --
569 end if;
570 end if;
571
572 /* Added for previous nationality check */
573 if p_per_information_category = 'KW' and (p_per_information7 is not null and p_per_information7 <> hr_api.g_varchar2 ) then
574
575 if hr_api.not_exists_in_hr_lookups
576 (p_effective_date => p_start_date
577 ,p_lookup_type => 'NATIONALITY'
578 ,p_lookup_code => p_per_information7
579 )
580 then
581 --
582 hr_utility.set_message(800, 'HR_375411_KW_INVALID_PREV_NAT');
583 hr_utility.raise_error;
584 --
585 end if;
586 end if;
587
588 end if;
589 END IF;
590 END CWK_VALIDATE;
591
592
593 --Procedure for validating contract
594 PROCEDURE CONTRACT_VALIDATE
595 (p_effective_date in date
596 ,p_type in varchar2
597 ,p_duration in number default null
598 ,p_duration_units in varchar2 default null
599 ,p_ctr_information_category in varchar2 default null
600 ,p_ctr_information1 in varchar2 default null
601 ,p_ctr_information2 in varchar2 default null
602 ,p_ctr_information3 in varchar2 default null
603 ,p_ctr_information4 in varchar2 default null
604 ,p_ctr_information5 in varchar2 default null
605 ) is
606 l_field VARCHAR2(300);
607 BEGIN
608
609 /* Added for GSI Bug 5472781 */
610 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
611
612 l_field := NULL;
613
614
615 IF p_ctr_information_category = 'KW' THEN
616 -- Check that the employment status exists in hr_lookups for the
617 -- lookup type 'KW_EMPLOYMENT_STATUS' with an enabled flag set to 'Y'
618 --
619 IF p_ctr_information1 IS NOT NULL THEN
620 IF hr_api.not_exists_in_hr_lookups
621 (p_effective_date => p_effective_date
622 ,p_lookup_type => 'KW_EMPLOYMENT_STATUS'
623 ,p_lookup_code => p_ctr_information1
624 )
625 THEN
626 --
627 hr_utility.set_message(800, 'HR_375405_KW_INVALID_EMP_STAT');
628 hr_utility.raise_error;
629 END IF;
630 END IF;
631
632 --
633 IF p_ctr_information2 IS NOT NULL THEN
634
635 IF (fnd_date.canonical_to_date(p_ctr_information2) < p_effective_date)
636 THEN
637 --
638 hr_utility.set_message(800, 'HR_375406_KW_EXPIRY_INVALID');
639 hr_utility.raise_error;
640 --
641 END IF;
642 END IF;
643
644 END IF;
645 END IF;
646 END CONTRACT_VALIDATE;
647
648 --Procedure for validating previous_employer
649 PROCEDURE PREVIOUS_EMPLOYER_VALIDATE
650 (p_employer_name IN varchar2 default hr_api.g_varchar2
651 ,p_effective_date IN date default hr_api.g_date
652 ,p_pem_information_category IN varchar2 default hr_api.g_varchar2
653 ,p_pem_information1 IN varchar2 default hr_api.g_varchar2
654 ) IS
655
656 v_field VARCHAR2(300);
657
658 BEGIN
659
660 /* Added for GSI Bug 5472781 */
661 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
662
663 v_field := NULL;
664 IF p_pem_information_category = 'KW' then
665 IF p_employer_name is null then
666 IF v_field IS NULL THEN
667 v_field := hr_general.decode_lookup('KW_FORM_LABELS','PREVIOUS_EMPLOYER');
668 ELSE
669 v_field := v_field||', '||hr_general.decode_lookup('KW_FORM_LABELS','PREVIOUS_EMPLOYER');
670 END IF;
671
672 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
673 fnd_message.set_token('NAME',v_field, translate => true );
674 hr_utility.raise_error;
675 END IF;
676
677 IF p_pem_information1 IS NOT NULL THEN
678 IF hr_api.not_exists_in_hr_lookups
679 (p_effective_date => p_effective_date
680 ,p_lookup_type => 'LEAV_REAS'
681 ,p_lookup_code => p_pem_information1
682 )
683 THEN
684 hr_utility.set_message(800, 'HR_375410_KW_INVALID_LEAV_REAS');
685 hr_utility.raise_error;
686 END IF;
687 END IF;
688 END IF;
689 END IF;
690 END PREVIOUS_EMPLOYER_VALIDATE;
691
692 PROCEDURE VALIDATE_CREATE_ORG_INF(
696 ,p_org_information1 IN VARCHAR2 DEFAULT null
693 p_effective_date IN DATE
694 ,p_organization_id IN NUMBER
695 ,p_org_info_type_code IN VARCHAR2
697 ,p_org_information2 IN VARCHAR2 DEFAULT null
698 ,p_org_information3 IN VARCHAR2 DEFAULT null
699 ,p_org_information4 IN VARCHAR2 DEFAULT null
700 ,p_org_information5 IN VARCHAR2 DEFAULT null
701 ,p_org_information6 IN VARCHAR2 DEFAULT null
702 ,p_org_information7 IN VARCHAR2 DEFAULT null
703 ,p_org_information8 IN VARCHAR2 DEFAULT null
704 ,p_org_information9 IN VARCHAR2 DEFAULT null
705 ,p_org_information10 IN VARCHAR2 DEFAULT null
706 )
707
708 AS
709 return_value VARCHAR2(100);
710 invalid_mesg VARCHAR2(100);
711 l_civil_id VARCHAR2(100);
712
713 l_length NUMBER;
714 l_var1 NUMBER;
715 m_s NUMBER;
716 m_total NUMBER;
717 m_rem NUMBER;
718 m_num NUMBER;
719 BEGIN
720
721 /* Added for GSI Bug 5472781 */
722 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
723
724 l_length := 0;
725 l_var1 := 0;
726 m_s := 0;
727 IF p_org_info_type_code = 'KW_LEGAL_EMPLOYER_DETAILS' THEN
728 IF p_org_information6 IS NOT NULL THEN
729 return_value := p_org_information6;
730
731 l_length := LENGTH(p_org_information6);
732
733 IF(l_length > 8) THEN
734
735 return_value := '0';
736 fnd_message.set_name('PER', 'HR_375403_KW_INVALID_EMPCID_LN');
737 hr_utility.raise_error;
738
739 ELSIF(l_length = 8) THEN
740
741 l_var1 := TO_NUMBER(SUBSTR(p_org_information6,1,2));
742 IF( l_var1 = 32 OR l_var1 = 34 OR l_var1 = 35 OR
743 l_var1 = 36 OR l_var1 = 39 OR l_var1 = 41 OR
744 l_var1 = 91 ) THEN
745 return_value := p_org_information6; --, invalid_mesg
746 ELSE
747 fnd_message.set_name('PER', 'HR_375404_KW_INVALID_EMPCID');
748 hr_utility.raise_error;
749 END IF;
750
751 ELSIF(l_length < 8) THEN
752
753 l_civil_id := LPAD(p_org_information6,7,'0');
754
755 m_s := TO_NUMBER (SUBSTR(l_civil_id,-1,1));
756 m_total := 0;
757 m_total := m_total +
758 (TO_NUMBER(SUBSTR(l_civil_id,1,1))*1) +
759 (TO_NUMBER(SUBSTR(l_civil_id,2,1))*6) +
760 (TO_NUMBER(SUBSTR(l_civil_id,3,1))*3) +
761 (TO_NUMBER(SUBSTR(l_civil_id,4,1))*7) +
762 (TO_NUMBER(SUBSTR(l_civil_id,5,1))*9) +
763 (TO_NUMBER(SUBSTR(l_civil_id,6,1))*10);
764
765 m_rem := MOD(m_total,11);
766 m_num := 11 - m_rem;
767
768 IF(m_num <> m_s) THEN
769 fnd_message.set_name('PER', 'HR_375404_KW_INVALID_EMPCID');
770 hr_utility.raise_error;
771 END IF;
772
773 END IF;
774
775 END IF; --not null check
776
777 IF p_org_information4 IS NOT NULL THEN
778
779 l_length := LENGTH(p_org_information4);
780
781 IF(l_length > 9) THEN
782 fnd_message.set_name('PER', 'HR_375402_KW_SSN_INVALID');
783 hr_utility.raise_error;
784 END IF;
785 END IF;
786
787 END IF; -- legcode check
788 END IF;
789 END VALIDATE_CREATE_ORG_INF;
790
791 PROCEDURE VALIDATE_UPDATE_ORG_INF(
792 p_effective_date IN DATE
793 ,p_org_information_id IN NUMBER
794 ,p_org_info_type_code IN VARCHAR2
795 ,p_org_information1 IN VARCHAR2 DEFAULT null
796 ,p_org_information2 IN VARCHAR2 DEFAULT null
797 ,p_org_information3 IN VARCHAR2 DEFAULT null
798 ,p_org_information4 IN VARCHAR2 DEFAULT null
799 ,p_org_information5 IN VARCHAR2 DEFAULT null
800 ,p_org_information6 IN VARCHAR2 DEFAULT null
801 ,p_org_information7 IN VARCHAR2 DEFAULT null
802 ,p_org_information8 IN VARCHAR2 DEFAULT null
803 ,p_org_information9 IN VARCHAR2 DEFAULT null
804 ,p_org_information10 IN VARCHAR2 DEFAULT null
805 )
806 AS
807 return_value VARCHAR2(100);
808 invalid_mesg VARCHAR2(100);
809 l_civil_id VARCHAR2(100);
810
811 l_length NUMBER;
812 l_var1 NUMBER;
813 m_s NUMBER;
814 m_total NUMBER;
815 m_rem NUMBER;
816 m_num NUMBER;
817 BEGIN
818
819 /* Added for GSI Bug 5472781 */
820 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
821
822 l_length := 0;
823 l_var1 := 0;
824 m_s := 0;
825 IF p_org_info_type_code = 'KW_LEGAL_EMPLOYER_DETAILS' THEN
826 IF p_org_information6 IS NOT NULL THEN
827
828 return_value := p_org_information6;
829
830 l_length := LENGTH(p_org_information6);
831
832 IF(l_length > 8) THEN
833
834 return_value := '0';
835 fnd_message.set_name('PER', 'HR_375403_KW_INVALID_EMPCID_LN');
836 hr_utility.raise_error;
837
838 ELSIF(l_length = 8) THEN
839
840 l_var1 := TO_NUMBER(SUBSTR(p_org_information6,1,2));
841 IF( l_var1 = 32 OR l_var1 = 34 OR l_var1 = 35 OR
842 l_var1 = 36 OR l_var1 = 39 OR l_var1 = 41 OR
843 l_var1 = 91 ) THEN
844 return_value := p_org_information6; --, invalid_mesg
845 ELSE
846 fnd_message.set_name('PER', 'HR_375404_KW_INVALID_EMPCID');
847 hr_utility.raise_error;
848 END IF;
849
850 ELSIF(l_length < 8) THEN
851
852 l_civil_id := LPAD(p_org_information6,7,'0');
853
854 m_s := TO_NUMBER (SUBSTR(l_civil_id,-1,1));
855 m_total := 0;
856 m_total := m_total +
857 (TO_NUMBER(SUBSTR(l_civil_id,1,1))*1) +
858 (TO_NUMBER(SUBSTR(l_civil_id,2,1))*6) +
859 (TO_NUMBER(SUBSTR(l_civil_id,3,1))*3) +
860 (TO_NUMBER(SUBSTR(l_civil_id,4,1))*7) +
861 (TO_NUMBER(SUBSTR(l_civil_id,5,1))*9) +
862 (TO_NUMBER(SUBSTR(l_civil_id,6,1))*10);
863
864 m_rem := MOD(m_total,11);
865 m_num := 11 - m_rem;
866
867 IF(m_num <> m_s) THEN
868 fnd_message.set_name('PER', 'HR_375404_KW_INVALID_EMPCID');
869 hr_utility.raise_error;
870 END IF;
871
872 END IF;
873
874 END IF; --not null check
875 IF p_org_information4 IS NOT NULL THEN
876
877 l_length := LENGTH(p_org_information4);
878
879 IF(l_length > 9) THEN
880 fnd_message.set_name('PER', 'HR_375402_KW_SSN_INVALID');
881 hr_utility.raise_error;
882 END IF;
883 END IF;
884
885 END IF; -- legcode check
886 END IF;
887 END VALIDATE_UPDATE_ORG_INF;
888
889 PROCEDURE ASSIGNMENT_VALIDATE(
890 p_segment2 IN VARCHAR2
891 ,p_effective_date IN DATE
892 ,p_assignment_id IN NUMBER
893 ) IS
894
895 l_nationality VARCHAR2(40);
896 l_annuities VARCHAR2(40);
897 l_person_id NUMBER;
898 l_organization_id NUMBER;
899 l_local_nationality VARCHAR2(40);
900 v_field VARCHAR2(300) := NULL;
901
902 CURSOR csr_nationality IS
903 SELECT NATIONALITY
904 FROM per_all_people_f
905 WHERE person_id = l_person_id
906 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
907 BEGIN
908
909 /* Added for GSI Bug 5472781 */
910 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
911
912 SELECT person_id
913 INTO l_person_id
914 FROM per_all_assignments_f
915 WHERE assignment_id = p_assignment_id;
916
917 open csr_nationality;
918 fetch csr_nationality into l_nationality;
919 close csr_nationality;
920
921 --Fetch Local Nationality
922 BEGIN
923 l_organization_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
924 SELECT Org_Information1
925 INTO l_local_nationality
926 FROM HR_ORGANIZATION_INFORMATION
927 WHERE ORG_INFORMATION_CONTEXT = 'KW_BG_DETAILS'
928 AND ORGANIZATION_ID = l_organization_id;
929 EXCEPTION
930 WHEN no_data_found THEN
931 NULL;
932 END;
933
934 /*IF UPPER(l_nationality) = l_local_nationality THEN
935 IF (p_segment2 IS NULL) THEN
936 IF v_field IS NULL THEN
937 v_field := hr_general.decode_lookup('KW_FORM_LABELS','SOCIAL_SEC_NUMBER');
938 ELSE
939 v_field := v_field||', '||hr_general.decode_lookup('KW_FORM_LABELS','SOCIAL_SEC_NUMBER');
940 END IF;
941 END IF;
942 END IF;*/
943
944
945 IF v_field IS NOT NULL THEN
946 fnd_message.set_name('PER', 'PQH_FR_MANDATORY_MSG');
947 fnd_message.set_token('NAME',v_field, translate => true );
948 hr_utility.raise_error;
949 END IF;
950 END IF;
951 END ASSIGNMENT_VALIDATE;
952
953 PROCEDURE DISABILITY_VALIDATE(
954 p_effective_date in date
955 ,p_category in varchar2
956 ,p_degree in number default null
957 ,p_dis_information_category in varchar2 default null
958 ,p_dis_information1 in varchar2 default null
959 ,p_dis_information2 in varchar2 default null
960 )
961 AS
962 BEGIN
963
964 /* Added for GSI Bug 5472781 */
965 IF hr_utility.chk_product_install('Oracle Human Resources', 'KW') THEN
966
967 IF p_dis_information_category = 'KW' THEN
968 IF (p_degree IS NOT NULL AND p_dis_information1 IS NOT NULL) THEN
969 fnd_message.set_name('PER', 'HR_375408_KW_DISABILITY');
970 hr_utility.raise_error;
971 END IF;
972 END IF;
973
974 END IF;
975 END DISABILITY_VALIDATE;
976
977 END HR_KW_VALIDATE_PKG;
[Home] [Help]