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