[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.3.12020000.2 2012/09/13 13:53:22 mkuppuch ship $ */
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;
49 l_per_information14 per_all_people_f.per_information14%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;
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 );
170 ('RU_FORM_LABELS',
167 hr_api.mandatory_arg_error
168 (p_api_name => l_proc,
169 p_argument => hr_general.decode_lookup
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
277 OR l_per_information15 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
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 IF per_per_bus.g_global_transfer_in_process IS NULL
526 OR per_per_bus.g_global_transfer_in_process = FALSE THEN --BUG 14509259
527 hr_api.mandatory_arg_error
528 (p_api_name => l_proc,
529 p_argument => hr_general.decode_lookup
530 ('RU_FORM_LABELS',
531 'CITIZENSHIP'
532 ),
533 p_argument_value => p_per_information4
534 );
535 hr_ru_utility.check_lookup_value
536 (p_argument => hr_general.decode_lookup
537 ('RU_FORM_LABELS',
538 'CITIZENSHIP'
539 ),
540 p_argument_value => p_per_information4,
541 p_lookup_type => 'RU_CITIZENSHIP',
542 p_effective_date => p_hire_date
543 );
544 END IF;
545
546 -- Check for first name
547 hr_api.mandatory_arg_error
548 (p_api_name => l_proc,
549 p_argument => hr_general.decode_lookup
550 ('RU_FORM_LABELS',
551 'FIRST_NAME'
552 ),
553 p_argument_value => p_first_name
554 );
555
556 validate_person (p_first_name => p_first_name,
557 p_effective_date => p_hire_date,
558 p_per_information1 => p_per_information1,
559 p_per_information4 => p_per_information4,
560 p_per_information5 => p_per_information5,
561 p_per_information6 => p_per_information6,
562 p_per_information7 => p_per_information7,
563 p_per_information8 => p_per_information8,
564 p_per_information9 => p_per_information9,
565 p_per_information10 => p_per_information10,
566 p_per_information11 => p_per_information11,
567 p_per_information12 => p_per_information12,
568 p_per_information13 => p_per_information13,
569 p_per_information14 => p_per_information14,
570 p_per_information15 => p_per_information15,
571 p_per_information18 => p_per_information18
572 );
573
574 /* Suppressing the error of military documents being mandatory
575 as per bug 8663065 */
576
577 -- Check if Military Document is provided for males between 17 and 65
578 -- l_effective_date := TRUNC (p_hire_date);
579
580 --IF (p_date_of_birth IS NOT NULL)
581 --THEN
582 -- l_age :=
583 -- TRUNC (MONTHS_BETWEEN (l_effective_date, p_date_of_birth) / 12);
584
585 -- IF p_sex = 'M'
586 -- AND (l_age BETWEEN 17 AND 65)
587 -- AND p_per_information5 IS NULL
588 -- THEN
589 -- hr_utility.set_message (800, 'HR_RU_MIL_DOC_REQUIRED');
590 -- hr_utility.raise_error;
591 -- END IF;
592 --END IF;
593 END IF;
594 END create_ru_employee;
595
596 PROCEDURE create_ru_applicant (
597 p_first_name VARCHAR2,
598 p_date_received DATE,
599 p_per_information1 VARCHAR2,
600 p_per_information4 VARCHAR2,
601 p_per_information5 VARCHAR2,
602 p_per_information6 VARCHAR2,
603 p_per_information7 VARCHAR2,
604 p_per_information8 VARCHAR2,
605 p_per_information9 VARCHAR2,
606 p_per_information10 VARCHAR2,
607 p_per_information11 VARCHAR2,
608 p_per_information12 VARCHAR2,
609 p_per_information13 VARCHAR2,
610 p_per_information14 VARCHAR2,
611 p_per_information15 VARCHAR2,
612 p_per_information18 VARCHAR2
613 )
614 IS
615 l_proc CONSTANT VARCHAR2 (72) := g_package || 'CREATE_RU_APPLICANT';
616 BEGIN
617 --
618 -- Added for GSI Bug 5472781
619 --
620 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
621 --
622 -- Check for Citizenship
623 hr_api.mandatory_arg_error
624 (p_api_name => l_proc,
625 p_argument => hr_general.decode_lookup
626 ('RU_FORM_LABELS',
627 'CITIZENSHIP'
628 ),
629 p_argument_value => p_per_information4
630 );
631 hr_ru_utility.check_lookup_value
632 (p_argument => hr_general.decode_lookup
633 ('RU_FORM_LABELS',
634 'CITIZENSHIP'
635 ),
636 p_argument_value => p_per_information4,
637 p_lookup_type => 'RU_CITIZENSHIP',
638 p_effective_date => p_date_received
639 );
640 validate_person (p_first_name => p_first_name,
641 p_effective_date => p_date_received,
642 p_per_information1 => p_per_information1,
643 p_per_information4 => p_per_information4,
644 p_per_information5 => p_per_information5,
645 p_per_information6 => p_per_information6,
646 p_per_information7 => p_per_information7,
647 p_per_information8 => p_per_information8,
648 p_per_information9 => p_per_information9,
649 p_per_information10 => p_per_information10,
650 p_per_information11 => p_per_information11,
651 p_per_information12 => p_per_information12,
652 p_per_information13 => p_per_information13,
653 p_per_information14 => p_per_information14,
654 p_per_information15 => p_per_information15,
655 p_per_information18 => p_per_information18
656 );
657 END IF;
658 END create_ru_applicant;
659
660 PROCEDURE create_ru_contact (
661 p_first_name VARCHAR2,
662 p_start_date DATE,
663 p_per_information1 VARCHAR2,
664 p_per_information4 VARCHAR2,
665 p_per_information5 VARCHAR2,
666 p_per_information6 VARCHAR2,
667 p_per_information7 VARCHAR2,
668 p_per_information8 VARCHAR2,
669 p_per_information9 VARCHAR2,
670 p_per_information10 VARCHAR2,
671 p_per_information11 VARCHAR2,
672 p_per_information12 VARCHAR2,
673 p_per_information13 VARCHAR2,
674 p_per_information14 VARCHAR2,
675 p_per_information15 VARCHAR2,
679 IS
676 p_per_information18 VARCHAR2
677
678 )
680 BEGIN
681 --
682 -- Added for GSI Bug 5472781
683 --
684 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
685 --
686 validate_person (p_first_name => p_first_name,
687 p_effective_date => p_start_date,
688 p_per_information1 => p_per_information1,
689 p_per_information4 => p_per_information4,
690 p_per_information5 => p_per_information5,
691 p_per_information6 => p_per_information6,
692 p_per_information7 => p_per_information7,
693 p_per_information8 => p_per_information8,
694 p_per_information9 => p_per_information9,
695 p_per_information10 => p_per_information10,
696 p_per_information11 => p_per_information11,
697 p_per_information12 => p_per_information12,
698 p_per_information13 => p_per_information13,
699 p_per_information14 => p_per_information14,
700 p_per_information15 => p_per_information15,
701 p_per_information18 => p_per_information18
702 );
703 END IF;
704 END create_ru_contact;
705
706 PROCEDURE create_ru_cwk (
707 p_first_name VARCHAR2,
708 p_start_date DATE,
709 p_per_information1 VARCHAR2,
710 p_per_information4 VARCHAR2,
711 p_per_information5 VARCHAR2,
712 p_per_information6 VARCHAR2,
713 p_per_information7 VARCHAR2,
714 p_per_information8 VARCHAR2,
715 p_per_information9 VARCHAR2,
716 p_per_information10 VARCHAR2,
717 p_per_information11 VARCHAR2,
718 p_per_information12 VARCHAR2,
719 p_per_information13 VARCHAR2,
720 p_per_information14 VARCHAR2,
721 p_per_information15 VARCHAR2,
722 p_per_information18 VARCHAR2
723 )
724 IS
725 l_proc CONSTANT VARCHAR2 (72) := g_package || 'CREATE_RU_CWK';
726 BEGIN
727 --
728 -- Added for GSI Bug 5472781
729 --
730 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
731 --
732 -- Check for Citizenship
733 hr_api.mandatory_arg_error
734 (p_api_name => l_proc,
735 p_argument => hr_general.decode_lookup
736 ('RU_FORM_LABELS',
737 'CITIZENSHIP'
738 ),
739 p_argument_value => p_per_information4
740 );
741 hr_ru_utility.check_lookup_value
742 (p_argument => hr_general.decode_lookup
743 ('RU_FORM_LABELS',
744 'CITIZENSHIP'
745 ),
746 p_argument_value => p_per_information4,
747 p_lookup_type => 'RU_CITIZENSHIP',
748 p_effective_date => p_start_date
749 );
750 validate_person (p_first_name => p_first_name,
751 p_effective_date => p_start_date,
752 p_per_information1 => p_per_information1,
753 p_per_information4 => p_per_information4,
754 p_per_information5 => p_per_information5,
755 p_per_information6 => p_per_information6,
756 p_per_information7 => p_per_information7,
757 p_per_information8 => p_per_information8,
758 p_per_information9 => p_per_information9,
759 p_per_information10 => p_per_information10,
760 p_per_information11 => p_per_information11,
761 p_per_information12 => p_per_information12,
762 p_per_information13 => p_per_information13,
763 p_per_information14 => p_per_information14,
764 p_per_information15 => p_per_information15,
765 p_per_information18 => p_per_information18
766 );
767 END IF;
768 END create_ru_cwk;
769
770 PROCEDURE update_ru_person (
771 p_person_id NUMBER,
772 p_first_name VARCHAR2,
773 p_person_type_id NUMBER,
774 p_effective_date DATE,
775 p_date_of_birth DATE,
776 p_sex VARCHAR2,
777 p_per_information1 VARCHAR2,
778 p_per_information4 VARCHAR2,
779 p_per_information5 VARCHAR2,
780 p_per_information6 VARCHAR2,
781 p_per_information7 VARCHAR2,
782 p_per_information8 VARCHAR2,
783 p_per_information9 VARCHAR2,
784 p_per_information10 VARCHAR2,
785 p_per_information11 VARCHAR2,
786 p_per_information12 VARCHAR2,
787 p_per_information13 VARCHAR2,
788 p_per_information14 VARCHAR2,
789 p_per_information15 VARCHAR2,
790 p_per_information18 VARCHAR2
791 )
792 IS
793 l_proc CONSTANT VARCHAR2 (72)
794 := g_package || 'UPDATE_RU_PERSON';
795 -- l_age NUMBER;
796 l_date_of_birth per_all_people_f.date_of_birth%TYPE;
800 l_seeded_person_type_key per_person_types.seeded_person_type_key%TYPE;
797 l_sex per_all_people_f.sex%TYPE;
798 l_per_information5 per_all_people_f.per_information5%TYPE;
799 l_system_person_type per_person_types.system_person_type%TYPE;
801 l_effective_date DATE;
802
803 CURSOR csr_get_person_info (p_id NUMBER)
804 IS
805 SELECT date_of_birth, sex, per_information5
806 FROM per_all_people_f papf
807 WHERE papf.person_id = p_id
808 AND p_effective_date BETWEEN papf.effective_start_date
809 AND papf.effective_end_date;
810
811 l_person_info_rec csr_get_person_info%ROWTYPE;
812
813 CURSOR csr_ptu_type (c_person_id NUMBER, c_session_date DATE)
814 IS
815 SELECT ppt.system_person_type, ppt.seeded_person_type_key
816 FROM per_person_types ppt, per_person_type_usages_f ptu
817 WHERE ppt.person_type_id = ptu.person_type_id
818 AND c_session_date BETWEEN ptu.effective_start_date
819 AND ptu.effective_end_date
820 AND ptu.person_id = c_person_id;
821 BEGIN
822 --
823 -- Added for GSI Bug 5472781
824 --
825 IF hr_utility.chk_product_install('Oracle Human Resources', 'RU') THEN
826 --
827 l_effective_date := TRUNC (p_effective_date);
828
829 IF (p_person_type_id <> hr_api.g_number
830 ) -- If person type has changed , get new type
831 THEN
832 OPEN csr_val_person_type (p_person_type_id);
833
834 FETCH csr_val_person_type
835 INTO l_system_person_type, l_seeded_person_type_key;
836
837 CLOSE csr_val_person_type;
838 ELSE --else get from per_person_type_usages_f
839 OPEN csr_ptu_type (p_person_id, l_effective_date);
840
841 FETCH csr_ptu_type
842 INTO l_system_person_type, l_seeded_person_type_key;
843
844 CLOSE csr_ptu_type;
845 END IF;
846 -- Check Place of Birth takes valid OKATO lookup value
847 IF (p_per_information1 <> hr_api.g_varchar2)
848 THEN
849 hr_ru_utility.check_lookup_value
850 (p_argument => hr_general.decode_lookup
851 ('RU_FORM_LABELS',
852 'PLACE_OF_BIRTH'
853 ),
854 p_argument_value => p_per_information1,
855 p_lookup_type => 'RU_OKATO',
856 p_effective_date => p_effective_date
857 );
858 END IF;
859 --
860 IF l_seeded_person_type_key <> 'CONTACT'
861 THEN
862 -- Check for Citizenship
863 hr_api.mandatory_arg_error
864 (p_api_name => l_proc,
865 p_argument => hr_general.decode_lookup
866 ('RU_FORM_LABELS',
867 'CITIZENSHIP'
868 ),
869 p_argument_value => p_per_information4
870 );
871
872 IF (p_per_information4 <> hr_api.g_varchar2)
873 THEN
874 hr_ru_utility.check_lookup_value
875 (p_argument => hr_general.decode_lookup
876 ('RU_FORM_LABELS',
877 'CITIZENSHIP'
878 ),
879 p_argument_value => p_per_information4,
880 p_lookup_type => 'RU_CITIZENSHIP',
881 p_effective_date => p_effective_date
882 );
883 END IF;
884 END IF;
885
886 -- Check if Military Document is provided for male employees between 17 and 65
887 IF l_system_person_type LIKE 'EMP%'
888 THEN
889 -- Check for first name
890 IF (p_first_name <> hr_api.g_varchar2) OR (p_first_name IS NULL)
891 THEN
892 hr_api.mandatory_arg_error
893 (p_api_name => l_proc,
894 p_argument => hr_general.decode_lookup
895 ('RU_FORM_LABELS',
896 'FIRST_NAME'
897 ),
898 p_argument_value => p_first_name
899 );
900 END IF;
901
902 IF (p_date_of_birth <> hr_api.g_date)
903 OR (p_sex <> hr_api.g_varchar2)
904 OR ( p_per_information5 <> hr_api.g_varchar2
905 OR p_per_information5 IS NULL
906 )
907 THEN
908 OPEN csr_get_person_info (p_person_id);
909
910 FETCH csr_get_person_info
911 INTO l_person_info_rec;
912
913 CLOSE csr_get_person_info;
914
915 l_sex := param_or_db (p_sex, l_person_info_rec.sex);
916 l_date_of_birth :=
917 param_or_db (p_date_of_birth, l_person_info_rec.date_of_birth);
918 l_per_information5 :=
919 param_or_db (p_per_information5,
920 l_person_info_rec.per_information5
921 );
922 /* Suppressing the error of military documents being mandatory
923 as per bug 8663065 */
924 --l_age :=
925 -- TRUNC (MONTHS_BETWEEN (l_effective_date, l_date_of_birth) / 12);
926
927 --IF l_sex = 'M'
928 -- AND (l_age BETWEEN 17 AND 65)
929 -- AND l_per_information5 IS NULL
930 --THEN
931 -- hr_utility.set_message (800, 'HR_RU_MIL_DOC_REQUIRED');
932 -- hr_utility.raise_error;
933 --END IF;
934 END IF;
935 END IF;
936
937 validate_person_upd (p_first_name => p_first_name,
938 p_person_id => p_person_id,
939 p_effective_date => p_effective_date,
940 p_per_information4 => p_per_information4,
941 p_per_information5 => p_per_information5,
942 p_per_information6 => p_per_information6,
943 p_per_information7 => p_per_information7,
944 p_per_information8 => p_per_information8,
945 p_per_information9 => p_per_information9,
946 p_per_information10 => p_per_information10,
947 p_per_information11 => p_per_information11,
948 p_per_information12 => p_per_information12,
949 p_per_information13 => p_per_information13,
950 p_per_information14 => p_per_information14,
951 p_per_information15 => p_per_information15,
952 p_per_information18 => p_per_information18
953 );
954 END IF;
955 END update_ru_person;
956 END hr_ru_people_leg_hook;