[Home] [Help]
PACKAGE BODY: APPS.HR_RU_PEOPLE_LEG_HOOK
Source
1 PACKAGE BODY hr_ru_people_leg_hook AS
2 /* $Header: peruvald.pkb 120.2 2006/09/20 10:51:43 mgettins noship $ */
3 g_package CONSTANT VARCHAR2 (30) := 'HR_RU_PEOPLE_LEG_HOOK .';
4
5 FUNCTION param_or_db (param VARCHAR2, db_item VARCHAR2)
6 RETURN VARCHAR2
7 IS
8 BEGIN
9 IF (param <> hr_api.g_varchar2) OR (param IS NULL)
10 THEN
11 RETURN param;
12 ELSE
13 RETURN db_item;
14 END IF;
15 END param_or_db;
16
17 PROCEDURE validate_person_upd (
18 p_first_name VARCHAR2,
19 p_person_id NUMBER,
20 p_effective_date DATE,
21 p_per_information4 VARCHAR2,
22 p_per_information5 VARCHAR2,
23 p_per_information6 VARCHAR2,
24 p_per_information7 VARCHAR2,
25 p_per_information8 VARCHAR2,
26 p_per_information9 VARCHAR2,
27 p_per_information10 VARCHAR2,
28 p_per_information11 VARCHAR2,
29 p_per_information12 VARCHAR2,
30 p_per_information13 VARCHAR2,
31 p_per_information14 VARCHAR2,
32 p_per_information15 VARCHAR2,
33 p_per_information18 VARCHAR2
34 )
35 IS
36 l_proc CONSTANT VARCHAR2 (72)
37 := g_package || 'VALIDATE_PERSON_UPD';
38 l_effective_date DATE;
39 l_err NUMBER;
40 l_age NUMBER;
41 l_per_information5 per_all_people_f.per_information5%TYPE;
42 l_per_information6 per_all_people_f.per_information6%TYPE;
43 l_per_information7 per_all_people_f.per_information7%TYPE;
44 l_per_information8 per_all_people_f.per_information8%TYPE;
45 l_per_information9 per_all_people_f.per_information9%TYPE;
46 l_per_information10 per_all_people_f.per_information10%TYPE;
47 l_per_information11 per_all_people_f.per_information11%TYPE;
48 l_per_information12 per_all_people_f.per_information12%TYPE;
49 l_per_information14 per_all_people_f.per_information14%TYPE;
50 l_per_information15 per_all_people_f.per_information15%TYPE;
51 l_per_information18 per_all_people_f.per_information18%TYPE;
52
53 CURSOR csr_get_mil_info (p_id NUMBER)
54 IS
55 SELECT per_information5, per_information6, per_information7,
56 per_information8, per_information9, per_information10,
57 per_information11, per_information12, per_information13,
58 per_information14,per_information15,per_information18
59 FROM per_all_people_f papf
60 WHERE papf.person_id = p_id
61 AND p_effective_date BETWEEN papf.effective_start_date
62 AND papf.effective_end_date;
63
64 db_person_rec csr_get_mil_info%ROWTYPE;
65 BEGIN
66
67
68 l_effective_date := TRUNC (p_effective_date);
69
70 --Validate Pension fund number
71 IF p_per_information13 <> hr_api.g_varchar2
72 AND p_per_information13 IS NOT NULL
73 THEN
74 l_err :=
75 hr_ru_utility.validate_spifn (p_per_information13,
76 l_effective_date
77 );
78
79 IF (l_err = 1)
80 THEN
81 hr_utility.set_message (800, 'HR_RU_INVALID_SPIF_NUMBER');
82 hr_utility.set_message_token
83 ('NUMBER',
84 hr_general.decode_lookup ('RU_FORM_LABELS',
85 'SPIFN'
86 )
87 );
88 hr_utility.raise_error;
89 ELSIF l_err = 2
90 THEN
91 hr_utility.set_message (800, 'HR_RU_SPIFN_FORMULA_ERROR');
92 hr_utility.raise_error;
93 END IF;
94 END IF;
95
96 --Check for Invalid Combination of military document and other military details
97 IF (p_per_information5 <> hr_api.g_varchar2)
98 OR (p_per_information5 IS NULL)
99 OR (p_per_information6 <> hr_api.g_varchar2)
100 OR (p_per_information6 IS NULL)
101 OR (p_per_information7 <> hr_api.g_varchar2)
102 OR (p_per_information7 IS NULL)
103 OR (p_per_information8 <> hr_api.g_varchar2)
104 OR (p_per_information8 IS NULL)
105 OR (p_per_information9 <> hr_api.g_varchar2)
106 OR (p_per_information9 IS NULL)
107 OR (p_per_information10 <> hr_api.g_varchar2)
108 OR (p_per_information10 IS NULL)
109 OR (p_per_information11 <> hr_api.g_varchar2)
110 OR (p_per_information11 IS NULL)
111 OR (p_per_information12 <> hr_api.g_varchar2)
112 OR (p_per_information12 IS NULL)
113 OR (p_per_information14 <> hr_api.g_varchar2)
114 OR (p_per_information14 IS NULL)
115 OR (p_per_information15 <> hr_api.g_varchar2)
116 OR (p_per_information15 IS NULL)
117 OR (p_per_information18 <> hr_api.g_varchar2)
118 OR (p_per_information18 IS NULL)
119 THEN
120 OPEN csr_get_mil_info (p_person_id);
121
122 FETCH csr_get_mil_info
123 INTO db_person_rec;
124
125 CLOSE csr_get_mil_info;
126
127 l_per_information5 :=
128 param_or_db (p_per_information5, db_person_rec.per_information5);
129 l_per_information6 :=
130 param_or_db (p_per_information6, db_person_rec.per_information6);
131 l_per_information7 :=
132 param_or_db (p_per_information7, db_person_rec.per_information7);
133 l_per_information8 :=
134 param_or_db (p_per_information8, db_person_rec.per_information8);
135 l_per_information9 :=
136 param_or_db (p_per_information9, db_person_rec.per_information9);
137 l_per_information10 :=
138 param_or_db (p_per_information10,
139 db_person_rec.per_information10);
140 l_per_information11 :=
141 param_or_db (p_per_information11,
142 db_person_rec.per_information11);
143 l_per_information12 :=
144 param_or_db (p_per_information12,
145 db_person_rec.per_information12);
146 l_per_information14 :=
147 param_or_db (p_per_information14,
148 db_person_rec.per_information14);
149 l_per_information15 :=
150 param_or_db (p_per_information15,
151 db_person_rec.per_information15);
152 l_per_information18 :=
153 param_or_db (p_per_information18,
154 db_person_rec.per_information18);
155
156 IF l_per_information5 IS NOT NULL
157 THEN
158 hr_ru_utility.check_lookup_value
159 (p_argument => hr_general.decode_lookup
160 ('RU_FORM_LABELS',
161 'MILITARY_DOCUMENT'
162 ),
163 p_argument_value => l_per_information5,
164 p_lookup_type => 'RU_MILITARY_DOC_TYPE',
165 p_effective_date => p_effective_date
166 );
167 hr_api.mandatory_arg_error
168 (p_api_name => l_proc,
169 p_argument => hr_general.decode_lookup
170 ('RU_FORM_LABELS',
171 'MIL_SERVICE_READINESS'
172 ),
173 p_argument_value => l_per_information10
174 );
175
176 IF l_per_information12 IS NOT NULL
177 THEN
178 hr_ru_utility.check_lookup_value
179 (p_argument => hr_general.decode_lookup
180 ('RU_FORM_LABELS',
181 'QUITTING_MARK'
182 ),
183 p_argument_value => l_per_information12,
184 p_lookup_type => 'RU_QUITTING_MARK',
185 p_effective_date => p_effective_date
186 );
187 END IF;
188
189 IF l_per_information5 = '2'
190 AND ( l_per_information6 IS NOT NULL
191 OR (l_per_information7 IS NOT NULL AND l_per_information7<>'2')
192 OR l_per_information8 IS NOT NULL
193 OR l_per_information9 IS NOT NULL
194 OR l_per_information14 IS NOT NULL
195 OR l_per_information15 IS NOT NULL
196 OR l_per_information18 IS NOT NULL
197 )
198 THEN
199 hr_utility.set_message (800, 'HR_RU_MIL_INVALID_COMBINATION');
200 hr_utility.raise_error;
201 ELSIF l_per_information5 = '1'
202 THEN
203 IF (l_per_information6 IS NOT NULL)
204 THEN
205 hr_ru_utility.check_lookup_value
206 (p_argument => hr_general.decode_lookup
207 ('RU_FORM_LABELS',
208 'RESERVE_CATEGORY'
209 ),
210 p_argument_value => l_per_information6,
211 p_lookup_type => 'RU_RESERVE_CATEGORY',
212 p_effective_date => p_effective_date
213 );
214 END IF;
215
216 IF (l_per_information7 IS NOT NULL)
217 THEN
218 hr_ru_utility.check_lookup_value
219 (p_argument => hr_general.decode_lookup
220 ('RU_FORM_LABELS',
221 'MILITARY_RANK'
222 ),
223 p_argument_value => l_per_information7,
224 p_lookup_type => 'RU_MILITARY_RANK',
225 p_effective_date => p_effective_date
226 );
227 END IF;
228
229 IF (l_per_information8 IS NOT NULL)
230 THEN
231 hr_ru_utility.check_lookup_value
232 (p_argument => hr_general.decode_lookup
233 ('RU_FORM_LABELS',
234 'PROFILE'
235 ),
236 p_argument_value => l_per_information8,
237 p_lookup_type => 'RU_MILITARY_PROFILE',
238 p_effective_date => p_effective_date
239 );
240 END IF;
241
242 IF (l_per_information18 IS NOT NULL)
243 THEN
244 hr_ru_utility.check_lookup_value
245 (p_argument => hr_general.decode_lookup
246 ('RU_FORM_LABELS',
247 'MILITARY_REG_TYPE'
248 ),
249 p_argument_value => l_per_information18,
250 p_lookup_type => 'RU_MILITARY_REGISTRATION',
251 p_effective_date => p_effective_date
252 );
253 END IF;
254
255 IF l_per_information10 = 'E'
256 THEN
257 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
258 hr_utility.set_message_token ('API_NAME', l_proc);
259 hr_utility.set_message_token
260 ('ARGUMENT',
261 hr_general.decode_lookup ('RU_FORM_LABELS',
262 'MIL_SERVICE_READINESS'
263 )
264 );
265 hr_utility.raise_error;
266 END IF;
267 END IF;
268 ELSIF l_per_information5 IS NULL
269 AND ( l_per_information6 IS NOT NULL
270 OR l_per_information7 IS NOT NULL
271 OR l_per_information8 IS NOT NULL
272 OR l_per_information9 IS NOT NULL
273 OR l_per_information10 IS NOT NULL
274 OR l_per_information11 IS NOT NULL
275 OR l_per_information12 IS NOT NULL
276 OR l_per_information14 IS NOT NULL
277 OR l_per_information15 IS NOT NULL
278 OR l_per_information18 IS NOT NULL
279 )
280 THEN
281 hr_utility.set_message (800, 'HR_RU_MIL_INVALID_COMBINATION');
282 hr_utility.raise_error;
283 END IF;
284 END IF;
285 END validate_person_upd;
286
287 PROCEDURE validate_person (
288 p_first_name VARCHAR2,
289 p_effective_date DATE,
290 p_per_information1 VARCHAR2,
291 p_per_information4 VARCHAR2,
292 p_per_information5 VARCHAR2,
293 p_per_information6 VARCHAR2,
294 p_per_information7 VARCHAR2,
295 p_per_information8 VARCHAR2,
296 p_per_information9 VARCHAR2,
297 p_per_information10 VARCHAR2,
298 p_per_information11 VARCHAR2,
299 p_per_information12 VARCHAR2,
300 p_per_information13 VARCHAR2,
301 p_per_information14 VARCHAR2,
302 p_per_information15 VARCHAR2,
303 p_per_information18 VARCHAR2
304 )
305 IS
306 l_proc CONSTANT VARCHAR2 (72) := g_package || 'VALIDATE_PERSON';
307 l_effective_date DATE;
308 l_err NUMBER;
309 l_age NUMBER;
310 BEGIN
311
312 l_effective_date := TRUNC (p_effective_date);
313 --
314 -- Check Place of Birth takes valid OKATO lookup value
315 IF (p_per_information1 IS NOT NULL)
316 THEN
317 hr_ru_utility.check_lookup_value
318 (p_argument => hr_general.decode_lookup
319 ('RU_FORM_LABELS',
320 'PLACE_OF_BIRTH'
321 ),
322 p_argument_value => p_per_information1,
323 p_lookup_type => 'RU_OKATO',
324 p_effective_date => p_effective_date
325 );
326 END IF;
327
328
329 --Validate Pension fund number
330 IF p_per_information13 IS NOT NULL
331 THEN
332 l_err :=
333 hr_ru_utility.validate_spifn (p_per_information13,
334 l_effective_date
335 );
336
337 IF (l_err = 1)
338 THEN
339 hr_utility.set_message (800, 'HR_RU_INVALID_SPIF_NUMBER');
340 hr_utility.set_message_token
341 ('NUMBER',
342 hr_general.decode_lookup ('RU_FORM_LABELS',
343 'SPIFN'
344 )
345 );
346 hr_utility.raise_error;
347 ELSIF l_err = 2
348 THEN
349 hr_utility.set_message (800, 'HR_RU_SPIFN_FORMULA_ERROR');
350 hr_utility.raise_error;
351 END IF;
352 END IF;
353
354 --Check for Invalid Combination of military document and other military details
355 IF p_per_information5 IS NOT NULL
356 THEN
357 hr_ru_utility.check_lookup_value
358 (p_argument => hr_general.decode_lookup
359 ('RU_FORM_LABELS',
360 'MILITARY_DOCUMENT'
361 ),
362 p_argument_value => p_per_information5,
363 p_lookup_type => 'RU_MILITARY_DOC_TYPE',
364 p_effective_date => p_effective_date
365 );
366 hr_api.mandatory_arg_error
367 (p_api_name => l_proc,
368 p_argument => hr_general.decode_lookup
369 ('RU_FORM_LABELS',
370 'MIL_SERVICE_READINESS'
371 ),
372 p_argument_value => p_per_information10
373 );
374 hr_ru_utility.check_lookup_value
375 (p_argument => hr_general.decode_lookup
376 ('RU_FORM_LABELS',
377 'MIL_SERVICE_READINESS'
378 ),
379 p_argument_value => p_per_information10,
380 p_lookup_type => 'RU_MILITARY_SERVICE_READINESS',
381 p_effective_date => p_effective_date
382 );
383
384 IF p_per_information12 IS NOT NULL
385 THEN
386 hr_ru_utility.check_lookup_value
387 (p_argument => hr_general.decode_lookup
388 ('RU_FORM_LABELS',
389 'QUITTING_MARK'
390 ),
391 p_argument_value => p_per_information12,
392 p_lookup_type => 'RU_QUITTING_MARK',
393 p_effective_date => p_effective_date
394 );
395 END IF;
396
397 IF p_per_information5 = '2'
398 AND ( p_per_information6 IS NOT NULL
399 OR (p_per_information7 IS NOT NULL AND p_per_information7 <>'2')
400 OR p_per_information8 IS NOT NULL
401 OR p_per_information9 IS NOT NULL
402 OR p_per_information14 IS NOT NULL
403 OR p_per_information15 IS NOT NULL
404 OR p_per_information18 IS NOT NULL
405 )
406 THEN
407 hr_utility.set_message (800, 'HR_RU_MIL_INVALID_COMBINATION');
408 hr_utility.raise_error;
409 ELSIF p_per_information5 = '1'
410 THEN
411 IF (p_per_information6 IS NOT NULL)
412 THEN
413 hr_ru_utility.check_lookup_value
414 (p_argument => hr_general.decode_lookup
415 ('RU_FORM_LABELS',
416 'RESERVE_CATEGORY'
417 ),
418 p_argument_value => p_per_information6,
419 p_lookup_type => 'RU_RESERVE_CATEGORY',
420 p_effective_date => p_effective_date
421 );
422 END IF;
423
424 IF (p_per_information7 IS NOT NULL)
425 THEN
426 hr_ru_utility.check_lookup_value
427 (p_argument => hr_general.decode_lookup
428 ('RU_FORM_LABELS',
429 'MILITARY_RANK'
430 ),
431 p_argument_value => p_per_information7,
432 p_lookup_type => 'RU_MILITARY_RANK',
433 p_effective_date => p_effective_date
434 );
435 END IF;
436
437 IF (p_per_information8 IS NOT NULL)
438 THEN
439 hr_ru_utility.check_lookup_value
440 (p_argument => hr_general.decode_lookup
441 ('RU_FORM_LABELS',
442 'PROFILE'
443 ),
444 p_argument_value => p_per_information8,
445 p_lookup_type => 'RU_MILITARY_PROFILE',
446 p_effective_date => p_effective_date
447 );
448 END IF;
449
450 IF (p_per_information18 IS NOT NULL)
451 THEN
452 hr_ru_utility.check_lookup_value
453 (p_argument => hr_general.decode_lookup
454 ('RU_FORM_LABELS',
455 'MILITARY_REG_TYPE'
456 ),
457 p_argument_value => p_per_information18,
458 p_lookup_type => 'RU_MILITARY_REGISTRATION',
459 p_effective_date => p_effective_date
460 );
461 END IF;
462
463 IF p_per_information10 = 'E'
464 THEN
465 hr_utility.set_message (800, 'HR_7209_API_LOOK_INVALID');
466 hr_utility.set_message_token ('API_NAME', l_proc);
467 hr_utility.set_message_token
468 ('ARGUMENT',
469 hr_general.decode_lookup ('RU_FORM_LABELS',
470 'MIL_SERVICE_READINESS'
471 )
472 );
473 hr_utility.raise_error;
474 END IF;
475 END IF;
476 ELSIF p_per_information5 IS NULL
477 AND ( p_per_information6 IS NOT NULL
478 OR p_per_information7 IS NOT NULL
479 OR p_per_information8 IS NOT NULL
480 OR p_per_information9 IS NOT NULL
481 OR p_per_information10 IS NOT NULL
482 OR p_per_information11 IS NOT NULL
483 OR p_per_information12 IS NOT NULL
484 OR p_per_information14 IS NOT NULL
485 OR p_per_information15 IS NOT NULL
486 OR p_per_information18 IS NOT NULL
487 )
488 THEN
489 hr_utility.set_message (800, 'HR_RU_MIL_INVALID_COMBINATION');
490 hr_utility.raise_error;
491 END IF;
492 END validate_person;
493
494 PROCEDURE create_ru_employee (
495 p_first_name VARCHAR2,
496 p_hire_date DATE,
497 p_date_of_birth DATE,
498 p_sex VARCHAR2,
499 p_per_information1 VARCHAR2,
500 p_per_information4 VARCHAR2,
501 p_per_information5 VARCHAR2,
502 p_per_information6 VARCHAR2,
503 p_per_information7 VARCHAR2,
504 p_per_information8 VARCHAR2,
505 p_per_information9 VARCHAR2,
506 p_per_information10 VARCHAR2,
507 p_per_information11 VARCHAR2,
508 p_per_information12 VARCHAR2,
509 p_per_information13 VARCHAR2,
510 p_per_information14 VARCHAR2,
511 p_per_information15 VARCHAR2,
512 p_per_information18 VARCHAR2
513 )
514 IS
515 l_proc CONSTANT VARCHAR2 (72) := g_package || 'CREATE_RU_EMPLOYEE';
516 l_age NUMBER;
517 l_effective_date DATE;
518 BEGIN
519 --
520 -- Added for GSI Bug 5472781
521 --
522 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
523 --
524 -- Check for Citizenship
525 hr_api.mandatory_arg_error
526 (p_api_name => l_proc,
527 p_argument => hr_general.decode_lookup
528 ('RU_FORM_LABELS',
529 'CITIZENSHIP'
530 ),
531 p_argument_value => p_per_information4
532 );
533 hr_ru_utility.check_lookup_value
534 (p_argument => hr_general.decode_lookup
535 ('RU_FORM_LABELS',
536 'CITIZENSHIP'
537 ),
538 p_argument_value => p_per_information4,
539 p_lookup_type => 'RU_CITIZENSHIP',
540 p_effective_date => p_hire_date
541 );
542 -- Check for first name
543 hr_api.mandatory_arg_error
544 (p_api_name => l_proc,
545 p_argument => hr_general.decode_lookup
546 ('RU_FORM_LABELS',
547 'FIRST_NAME'
548 ),
549 p_argument_value => p_first_name
550 );
551
552 validate_person (p_first_name => p_first_name,
553 p_effective_date => p_hire_date,
554 p_per_information1 => p_per_information1,
555 p_per_information4 => p_per_information4,
556 p_per_information5 => p_per_information5,
557 p_per_information6 => p_per_information6,
558 p_per_information7 => p_per_information7,
559 p_per_information8 => p_per_information8,
560 p_per_information9 => p_per_information9,
561 p_per_information10 => p_per_information10,
562 p_per_information11 => p_per_information11,
563 p_per_information12 => p_per_information12,
564 p_per_information13 => p_per_information13,
565 p_per_information14 => p_per_information14,
566 p_per_information15 => p_per_information15,
567 p_per_information18 => p_per_information18
568 );
569 -- Check if Military Document is provided for males between 17 and 65
570 l_effective_date := TRUNC (p_hire_date);
571
572 IF (p_date_of_birth IS NOT NULL)
573 THEN
574 l_age :=
575 TRUNC (MONTHS_BETWEEN (l_effective_date, p_date_of_birth) / 12);
576
577 IF p_sex = 'M'
578 AND (l_age BETWEEN 17 AND 65)
579 AND p_per_information5 IS NULL
580 THEN
581 hr_utility.set_message (800, 'HR_RU_MIL_DOC_REQUIRED');
582 hr_utility.raise_error;
583 END IF;
584 END IF;
585 END IF;
586 END create_ru_employee;
587
588 PROCEDURE create_ru_applicant (
589 p_first_name VARCHAR2,
590 p_date_received DATE,
591 p_per_information1 VARCHAR2,
592 p_per_information4 VARCHAR2,
593 p_per_information5 VARCHAR2,
594 p_per_information6 VARCHAR2,
595 p_per_information7 VARCHAR2,
596 p_per_information8 VARCHAR2,
597 p_per_information9 VARCHAR2,
598 p_per_information10 VARCHAR2,
599 p_per_information11 VARCHAR2,
600 p_per_information12 VARCHAR2,
601 p_per_information13 VARCHAR2,
602 p_per_information14 VARCHAR2,
603 p_per_information15 VARCHAR2,
604 p_per_information18 VARCHAR2
605 )
606 IS
607 l_proc CONSTANT VARCHAR2 (72) := g_package || 'CREATE_RU_APPLICANT';
608 BEGIN
609 --
610 -- Added for GSI Bug 5472781
611 --
612 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
613 --
614 -- Check for Citizenship
615 hr_api.mandatory_arg_error
616 (p_api_name => l_proc,
617 p_argument => hr_general.decode_lookup
618 ('RU_FORM_LABELS',
619 'CITIZENSHIP'
620 ),
621 p_argument_value => p_per_information4
622 );
623 hr_ru_utility.check_lookup_value
624 (p_argument => hr_general.decode_lookup
625 ('RU_FORM_LABELS',
626 'CITIZENSHIP'
627 ),
628 p_argument_value => p_per_information4,
629 p_lookup_type => 'RU_CITIZENSHIP',
630 p_effective_date => p_date_received
631 );
632 validate_person (p_first_name => p_first_name,
633 p_effective_date => p_date_received,
634 p_per_information1 => p_per_information1,
635 p_per_information4 => p_per_information4,
636 p_per_information5 => p_per_information5,
637 p_per_information6 => p_per_information6,
638 p_per_information7 => p_per_information7,
639 p_per_information8 => p_per_information8,
640 p_per_information9 => p_per_information9,
641 p_per_information10 => p_per_information10,
642 p_per_information11 => p_per_information11,
643 p_per_information12 => p_per_information12,
644 p_per_information13 => p_per_information13,
645 p_per_information14 => p_per_information14,
646 p_per_information15 => p_per_information15,
647 p_per_information18 => p_per_information18
648 );
649 END IF;
650 END create_ru_applicant;
651
652 PROCEDURE create_ru_contact (
653 p_first_name VARCHAR2,
654 p_start_date DATE,
655 p_per_information1 VARCHAR2,
656 p_per_information4 VARCHAR2,
657 p_per_information5 VARCHAR2,
658 p_per_information6 VARCHAR2,
659 p_per_information7 VARCHAR2,
660 p_per_information8 VARCHAR2,
661 p_per_information9 VARCHAR2,
662 p_per_information10 VARCHAR2,
663 p_per_information11 VARCHAR2,
664 p_per_information12 VARCHAR2,
665 p_per_information13 VARCHAR2,
666 p_per_information14 VARCHAR2,
667 p_per_information15 VARCHAR2,
668 p_per_information18 VARCHAR2
669
670 )
671 IS
672 BEGIN
673 --
674 -- Added for GSI Bug 5472781
675 --
676 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
677 --
678 validate_person (p_first_name => p_first_name,
679 p_effective_date => p_start_date,
680 p_per_information1 => p_per_information1,
681 p_per_information4 => p_per_information4,
682 p_per_information5 => p_per_information5,
683 p_per_information6 => p_per_information6,
684 p_per_information7 => p_per_information7,
685 p_per_information8 => p_per_information8,
686 p_per_information9 => p_per_information9,
687 p_per_information10 => p_per_information10,
688 p_per_information11 => p_per_information11,
689 p_per_information12 => p_per_information12,
690 p_per_information13 => p_per_information13,
691 p_per_information14 => p_per_information14,
692 p_per_information15 => p_per_information15,
693 p_per_information18 => p_per_information18
694 );
695 END IF;
696 END create_ru_contact;
697
698 PROCEDURE create_ru_cwk (
699 p_first_name VARCHAR2,
700 p_start_date DATE,
701 p_per_information1 VARCHAR2,
702 p_per_information4 VARCHAR2,
703 p_per_information5 VARCHAR2,
704 p_per_information6 VARCHAR2,
705 p_per_information7 VARCHAR2,
706 p_per_information8 VARCHAR2,
707 p_per_information9 VARCHAR2,
708 p_per_information10 VARCHAR2,
709 p_per_information11 VARCHAR2,
710 p_per_information12 VARCHAR2,
711 p_per_information13 VARCHAR2,
712 p_per_information14 VARCHAR2,
713 p_per_information15 VARCHAR2,
714 p_per_information18 VARCHAR2
715 )
716 IS
717 l_proc CONSTANT VARCHAR2 (72) := g_package || 'CREATE_RU_CWK';
718 BEGIN
719 --
720 -- Added for GSI Bug 5472781
721 --
722 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
723 --
724 -- Check for Citizenship
725 hr_api.mandatory_arg_error
726 (p_api_name => l_proc,
727 p_argument => hr_general.decode_lookup
728 ('RU_FORM_LABELS',
729 'CITIZENSHIP'
730 ),
731 p_argument_value => p_per_information4
732 );
733 hr_ru_utility.check_lookup_value
734 (p_argument => hr_general.decode_lookup
735 ('RU_FORM_LABELS',
736 'CITIZENSHIP'
737 ),
738 p_argument_value => p_per_information4,
739 p_lookup_type => 'RU_CITIZENSHIP',
740 p_effective_date => p_start_date
741 );
742 validate_person (p_first_name => p_first_name,
743 p_effective_date => p_start_date,
744 p_per_information1 => p_per_information1,
745 p_per_information4 => p_per_information4,
746 p_per_information5 => p_per_information5,
747 p_per_information6 => p_per_information6,
748 p_per_information7 => p_per_information7,
749 p_per_information8 => p_per_information8,
750 p_per_information9 => p_per_information9,
751 p_per_information10 => p_per_information10,
752 p_per_information11 => p_per_information11,
753 p_per_information12 => p_per_information12,
754 p_per_information13 => p_per_information13,
755 p_per_information14 => p_per_information14,
756 p_per_information15 => p_per_information15,
757 p_per_information18 => p_per_information18
758 );
759 END IF;
760 END create_ru_cwk;
761
762 PROCEDURE update_ru_person (
763 p_person_id NUMBER,
764 p_first_name VARCHAR2,
765 p_person_type_id NUMBER,
766 p_effective_date DATE,
767 p_date_of_birth DATE,
768 p_sex VARCHAR2,
769 p_per_information1 VARCHAR2,
770 p_per_information4 VARCHAR2,
771 p_per_information5 VARCHAR2,
772 p_per_information6 VARCHAR2,
773 p_per_information7 VARCHAR2,
774 p_per_information8 VARCHAR2,
775 p_per_information9 VARCHAR2,
776 p_per_information10 VARCHAR2,
777 p_per_information11 VARCHAR2,
778 p_per_information12 VARCHAR2,
779 p_per_information13 VARCHAR2,
780 p_per_information14 VARCHAR2,
781 p_per_information15 VARCHAR2,
782 p_per_information18 VARCHAR2
783 )
784 IS
785 l_proc CONSTANT VARCHAR2 (72)
786 := g_package || 'UPDATE_RU_PERSON';
787 l_age NUMBER;
788 l_date_of_birth per_all_people_f.date_of_birth%TYPE;
789 l_sex per_all_people_f.sex%TYPE;
790 l_per_information5 per_all_people_f.per_information5%TYPE;
791 l_system_person_type per_person_types.system_person_type%TYPE;
792 l_seeded_person_type_key per_person_types.seeded_person_type_key%TYPE;
793 l_effective_date DATE;
794
795 CURSOR csr_get_person_info (p_id NUMBER)
796 IS
797 SELECT date_of_birth, sex, per_information5
798 FROM per_all_people_f papf
799 WHERE papf.person_id = p_id
800 AND p_effective_date BETWEEN papf.effective_start_date
801 AND papf.effective_end_date;
802
803 l_person_info_rec csr_get_person_info%ROWTYPE;
804
805 CURSOR csr_ptu_type (c_person_id NUMBER, c_session_date DATE)
806 IS
807 SELECT ppt.system_person_type, ppt.seeded_person_type_key
808 FROM per_person_types ppt, per_person_type_usages_f ptu
809 WHERE ppt.person_type_id = ptu.person_type_id
810 AND c_session_date BETWEEN ptu.effective_start_date
811 AND ptu.effective_end_date
812 AND ptu.person_id = c_person_id;
813 BEGIN
814 --
815 -- Added for GSI Bug 5472781
816 --
817 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
818 --
819 l_effective_date := TRUNC (p_effective_date);
820
821 IF (p_person_type_id <> hr_api.g_number
822 ) -- If person type has changed , get new type
823 THEN
824 OPEN csr_val_person_type (p_person_type_id);
825
826 FETCH csr_val_person_type
827 INTO l_system_person_type, l_seeded_person_type_key;
828
829 CLOSE csr_val_person_type;
830 ELSE --else get from per_person_type_usages_f
831 OPEN csr_ptu_type (p_person_id, l_effective_date);
832
833 FETCH csr_ptu_type
834 INTO l_system_person_type, l_seeded_person_type_key;
835
836 CLOSE csr_ptu_type;
837 END IF;
838 -- Check Place of Birth takes valid OKATO lookup value
839 IF (p_per_information1 <> hr_api.g_varchar2)
840 THEN
841 hr_ru_utility.check_lookup_value
842 (p_argument => hr_general.decode_lookup
843 ('RU_FORM_LABELS',
844 'PLACE_OF_BIRTH'
845 ),
846 p_argument_value => p_per_information1,
847 p_lookup_type => 'RU_OKATO',
848 p_effective_date => p_effective_date
849 );
850 END IF;
851 --
852 IF l_seeded_person_type_key <> 'CONTACT'
853 THEN
854 -- Check for Citizenship
855 hr_api.mandatory_arg_error
856 (p_api_name => l_proc,
857 p_argument => hr_general.decode_lookup
858 ('RU_FORM_LABELS',
859 'CITIZENSHIP'
860 ),
861 p_argument_value => p_per_information4
862 );
863
864 IF (p_per_information4 <> hr_api.g_varchar2)
865 THEN
866 hr_ru_utility.check_lookup_value
867 (p_argument => hr_general.decode_lookup
868 ('RU_FORM_LABELS',
869 'CITIZENSHIP'
870 ),
871 p_argument_value => p_per_information4,
872 p_lookup_type => 'RU_CITIZENSHIP',
873 p_effective_date => p_effective_date
874 );
875 END IF;
876 END IF;
877
878 -- Check if Military Document is provided for male employees between 17 and 65
879 IF l_system_person_type LIKE 'EMP%'
880 THEN
881 -- Check for first name
882 IF (p_first_name <> hr_api.g_varchar2) OR (p_first_name IS NULL)
883 THEN
884 hr_api.mandatory_arg_error
885 (p_api_name => l_proc,
886 p_argument => hr_general.decode_lookup
887 ('RU_FORM_LABELS',
888 'FIRST_NAME'
889 ),
890 p_argument_value => p_first_name
891 );
892 END IF;
893
894 IF (p_date_of_birth <> hr_api.g_date)
895 OR (p_sex <> hr_api.g_varchar2)
896 OR ( p_per_information5 <> hr_api.g_varchar2
897 OR p_per_information5 IS NULL
898 )
899 THEN
900 OPEN csr_get_person_info (p_person_id);
901
902 FETCH csr_get_person_info
903 INTO l_person_info_rec;
904
905 CLOSE csr_get_person_info;
906
907 l_sex := param_or_db (p_sex, l_person_info_rec.sex);
908 l_date_of_birth :=
909 param_or_db (p_date_of_birth, l_person_info_rec.date_of_birth);
910 l_per_information5 :=
911 param_or_db (p_per_information5,
912 l_person_info_rec.per_information5
913 );
914 l_age :=
915 TRUNC (MONTHS_BETWEEN (l_effective_date, l_date_of_birth) / 12);
916
917 IF l_sex = 'M'
918 AND (l_age BETWEEN 17 AND 65)
919 AND l_per_information5 IS NULL
920 THEN
921 hr_utility.set_message (800, 'HR_RU_MIL_DOC_REQUIRED');
922 hr_utility.raise_error;
923 END IF;
924 END IF;
925 END IF;
926
927 validate_person_upd (p_first_name => p_first_name,
928 p_person_id => p_person_id,
929 p_effective_date => p_effective_date,
930 p_per_information4 => p_per_information4,
931 p_per_information5 => p_per_information5,
932 p_per_information6 => p_per_information6,
933 p_per_information7 => p_per_information7,
934 p_per_information8 => p_per_information8,
935 p_per_information9 => p_per_information9,
936 p_per_information10 => p_per_information10,
937 p_per_information11 => p_per_information11,
938 p_per_information12 => p_per_information12,
939 p_per_information13 => p_per_information13,
940 p_per_information14 => p_per_information14,
941 p_per_information15 => p_per_information15,
942 p_per_information18 => p_per_information18
943 );
944 END IF;
945 END update_ru_person;
946 END hr_ru_people_leg_hook;