1 PACKAGE BODY hr_person AS
2 /* $Header: peperson.pkb 120.5.12010000.2 2008/08/06 09:27:37 ubhat ship $ */
3 --
4 g_debug boolean := hr_utility.debug_enabled;
5
6 -- Bug 2678794: defintions changed to varchar2
7 -- to avoid ORA-06502 when implicit numeric conversions occur.
8 --
9 emp_number_sv per_all_people_f.employee_number%TYPE := '0'; --NUMBER := 0;
10 apl_number_sv per_all_people_f.applicant_number%TYPE := '0'; --NUMBER := 0;
11 npw_number_sv per_all_people_f.npw_number%TYPE := '0'; --NUMBER := 0;
12
13 -- --------------------------------------------------------------------- +
14 -- #2660279 Is_Unique_Person_number +
15 -- --------------------------------------------------------------------- +
16 -- Returns 'Y' if number is unique within Business Group
17 -- otherwise returns 'N'
18 --
19 FUNCTION IS_UNIQUE_PERSON_NUMBER (p_person_id IN number
20 ,p_person_type IN per_number_generation_controls.type%TYPE
21 ,p_person_number IN varchar2
22 ,p_business_group_id IN number
23 )
24 RETURN varchar2 IS
25
26 l_status varchar2(1);
27 BEGIN
28 l_status := 'N';
29 if p_person_type = 'APL' then
30 SELECT 'N'
31 INTO l_status
32 FROM sys.dual
33 WHERE exists (SELECT 'Y'
34 FROM per_all_people_f pp
35 WHERE (p_person_id IS NULL
36 OR p_person_id <> pp.person_id)
37 AND pp.business_group_id +0 = p_business_group_id
38 AND pp.applicant_number = p_person_number);
39 --
40 end if;
41 --
42 if p_person_type = 'EMP' then
43 --
44 -- The employee number is also validated against the npw number when
45 -- the CWK numbering method if Use Employee Numbering.
46 --
47 SELECT 'N'
48 INTO l_status
49 FROM sys.dual
50 WHERE exists (select 'Y'
51 FROM per_all_people_f pp
52 WHERE (p_person_id IS NULL
53 OR p_person_id <> pp.person_id)
54 AND pp.business_group_id = p_business_group_id
55 AND (pp.employee_number = p_person_number
56 OR (pp.npw_number = p_person_number
57 AND EXISTS
58 (SELECT null
59 FROM per_business_groups pbg
60 WHERE pbg.business_group_id = p_business_group_id
61 AND NVL(method_of_generation_cwk_num,hr_api.g_varchar2) = 'E'))));
62 --
63 end if;
64 --
65 if p_person_type = 'CWK' then
66 --
67 SELECT 'N'
68 INTO l_status
69 FROM sys.dual
70 WHERE exists (select 'Y'
71 FROM per_all_people_f pp
72 WHERE (p_person_id IS NULL
73 OR p_person_id <> pp.person_id)
74 AND pp.business_group_id = p_business_group_id
75 AND (pp.npw_number = p_person_number
76 OR (pp.employee_number = p_person_number
77 AND EXISTS
78 (SELECT null
79 FROM per_business_groups pbg
80 WHERE pbg.business_group_id = p_business_group_id
81 AND NVL(method_of_generation_cwk_num,hr_api.g_varchar2) = 'E'))));
82 --
83 end if;
84
85 RETURN(l_status);
86
87 EXCEPTION
88 when no_data_found then
89 if g_debug then
90 hr_utility.trace('Unique Person Number 999');
91 end if;
92 return('Y');
93
94 END IS_UNIQUE_PERSON_NUMBER;
95 -- --------------------------------------------------------------------- +
96 -- #2660279 Get_Person_Number +
97 -- --------------------------------------------------------------------- +
98 -- Gets the next person number from the per_number_generation_controls
99 -- table/global sequence. It will check that the number is unique within the
100 -- Business Group. In case this number already exists, it will check the next
101 -- value (maximum 25 times) until an unassigned number is found.
102 -- If after 25 times, a number could not be found, then an error message
103 -- will be raised.
104 --
105 --
106 PROCEDURE GET_PERSON_NUMBER (p_person_type IN per_number_generation_controls.type%TYPE
107 ,p_person_number IN OUT NOCOPY varchar2
108 ,p_business_group_id IN number
109 ,p_person_id IN number
110 ,p_effective_date IN date
111 ,p_party_id IN number
112 ,p_date_of_birth IN date
113 ,p_start_date IN date
114 ,p_national_id IN varchar2
115 ) IS
116 --
117 e_MaxExceeds exception;
118 PRAGMA EXCEPTION_INIT(e_MaxExceeds, -1438);
119
120 cursor csr_getSessionDate is
121 select fnd.effective_date
122 from fnd_sessions fnd
123 where fnd.session_id = userenv('sessionid');
124
125 l_counter number := 1;
126 l_person_number varchar2(30);
127 l_is_unique varchar2(1);
128 l_max_sequence number := 25; -- determines maximum iterations for loop
129 l_rowid ROWID ;
130 l_use_sequence boolean := false; -- based on x-bg person numbering profile
131 l_formula_id number;
132 l_effective_date date;
133 l_legislation_code varchar2(150);
134
135 BEGIN
136 l_is_unique := 'N';
137 l_formula_id := PER_BG_NUMBERING_METHOD_PKG.Get_PersonNumber_Formula
138 (p_person_type, p_effective_date);
139 IF l_formula_id is not null THEN
140 -- ------------------------------------------------------------------------+
141 -- Process number generation using Fast Formula +
142 -- ------------------------------------------------------------------------+
143 --
144 -- Get other parameters
145 --
146 l_legislation_code := per_utility_functions.get_legislation
147 (p_business_group_id => p_business_group_id);
148 --
149 -- Execute formula
150 --
151 l_person_number := PER_BG_NUMBERING_METHOD_PKG.Execute_Get_Person_Number_FF(
152 p_formula_id => l_formula_id
153 ,p_effective_date => p_effective_date
154 ,p_business_group_id => p_business_group_id
155 ,p_person_type => p_person_type
156 ,p_legislation_code => l_legislation_code
157 ,p_person_id => p_person_id
158 ,p_person_number => p_person_number
159 ,p_party_id => p_party_id
160 ,p_date_of_birth => p_date_of_birth
161 ,p_start_date => p_start_date
162 ,p_national_id => p_national_id
163 );
164 --
165 -- verify person number is unique
166 --
167 l_is_unique := IS_UNIQUE_PERSON_NUMBER
168 (p_person_id => p_person_id
169 ,p_person_type => p_person_type
170 ,p_person_number => l_person_number
171 ,p_business_group_id => p_business_group_id
172 );
173
174 if l_is_unique = 'N' then
175 p_person_number := null;
176 if p_person_type = 'EMP' then
177 hr_utility.set_message(800,'HR_7692_PERSON_NUM_EXIST');
178 elsif p_person_type = 'APL' then
179 hr_utility.set_message(800,'HR_7693_PERSON_NUM_EXISTS');
180 elsif p_person_type = 'CWK' then
181 hr_utility.set_message(800,'HR_289656_CWK_NUM_EXIST');
182 end if;
183 hr_utility.raise_error;
184 end if;
185 p_person_number := l_person_number;
186 ELSE
187 -- ------------------------------------------------------------------------+
188 -- Process number generation using existing mechanism +
189 -- ------------------------------------------------------------------------+
190 BEGIN
191 l_use_sequence := PER_BG_NUMBERING_METHOD_PKG.Global_person_numbering(p_person_type);
192 if l_use_sequence then
193 -- retrieve number from sequence
194 l_person_number := PER_BG_NUMBERING_METHOD_PKG.GetGlobalPersonNum(p_person_type);
195 else
196 -- Table-based method is used
197 SELECT next_value
198 , rowid
199 INTO l_person_number
200 , l_rowid
201 FROM per_number_generation_controls
202 WHERE business_group_id = p_business_group_id
203 AND type = p_person_type
204 FOR UPDATE OF next_value ;
205 end if;
206 WHILE (l_counter <= l_max_sequence and l_is_unique = 'N') LOOP
207
208 l_is_unique := IS_UNIQUE_PERSON_NUMBER
209 (p_person_id => p_person_id
210 ,p_person_type => p_person_type
211 ,p_person_number => l_person_number
212 ,p_business_group_id => p_business_group_id
213 );
214 if l_is_unique = 'N' then
215 if l_use_sequence then
216 -- get number from sequence
217 l_person_number := PER_BG_NUMBERING_METHOD_PKG.GetGlobalPersonNum(p_person_type);
218 else
219 l_counter := l_counter + 1;
220 l_person_number := l_person_number + 1;
221 end if;
222 end if;
223 END LOOP;
224 if l_is_unique = 'N' then
225 p_person_number := null;
226 if p_person_type = 'EMP' then
227 hr_utility.set_message(800,'HR_7692_PERSON_NUM_EXIST');
228 elsif p_person_type = 'APL' then
229 hr_utility.set_message(800,'HR_7693_PERSON_NUM_EXISTS');
230 elsif p_person_type = 'CWK' then
231 hr_utility.set_message(800,'HR_289656_CWK_NUM_EXIST');
232 end if;
233 hr_utility.raise_error;
234 else
235 if NOT l_use_sequence then
236 UPDATE per_number_generation_controls
237 SET next_value = l_person_number + 1
238 WHERE rowid = l_rowid ;
239 end if;
240 --
241 p_person_number := l_person_number;
242 end if;
243 EXCEPTION
244 when e_MaxExceeds then
245 hr_utility.set_message(800,'PER_289194_MAX_NUM_REACHED');
246 hr_utility.raise_error;
247 END;
248 -- ------------------------------------------------------------------------+
249 -- End existing mechanisms +
250 -- ------------------------------------------------------------------------+
251 END IF;
252
253 END GET_PERSON_NUMBER;
254 -- --------------------------------------------------------------------- +
255 --
256 -- ----------------------- generate_number ------------------------
257 --
258 -- Procedure accepts the current emp/apl/npw flags, national identifier
259 -- and business group and outputs the appropriate person number
260 -- (Note if the person numbers are supplied and the method
261 -- is not automatic - the numbers will remain unchanged).
262 --
263 PROCEDURE generate_number
264 (p_current_employee VARCHAR2 default null,
265 p_current_applicant VARCHAR2 default null,
266 p_current_npw VARCHAR2 default null,
267 p_national_identifier VARCHAR2 default null,
268 p_business_group_id NUMBER,
269 p_person_id NUMBER,
270 p_employee_number IN OUT NOCOPY VARCHAR2 ,
271 p_applicant_number IN OUT NOCOPY VARCHAR2 ,
272 p_npw_number IN OUT NOCOPY VARCHAR2) IS
273
274 BEGIN
275 generate_number
276 (p_current_employee => p_current_employee
277 ,p_current_applicant => p_current_applicant
278 ,p_current_npw => p_current_npw
279 ,p_national_identifier => p_national_identifier
280 ,p_business_group_id => p_business_group_id
281 ,p_person_id => p_person_id
282 ,p_employee_number => p_employee_number
283 ,p_applicant_number => p_applicant_number
284 ,p_npw_number => p_npw_number
285 ,p_effective_date => null
286 ,p_party_id => null
287 ,p_date_of_birth => null
288 ,p_start_date => null
289 );
290
291
292 END generate_number;
293 --
294 -- Overloaded
295 --
296 PROCEDURE generate_number
297 (p_current_employee VARCHAR2 default null
298 ,p_current_applicant VARCHAR2 default null
299 ,p_current_npw VARCHAR2 default null
300 ,p_national_identifier VARCHAR2 default null
301 ,p_business_group_id NUMBER
302 ,p_person_id NUMBER
303 ,p_employee_number IN OUT NOCOPY VARCHAR2
304 ,p_applicant_number IN OUT NOCOPY VARCHAR2
305 ,p_npw_number IN OUT NOCOPY VARCHAR2
306 ,p_effective_date IN date
307 ,p_party_id IN number
308 ,p_date_of_birth IN date
309 ,p_start_date IN date default null
310 )
311 --
312 --
313 IS
314 l_method_of_generation VARCHAR2(30);
315 l_method_of_gen_emp VARCHAR2(30);
316 l_legislation_code VARCHAR2(30);
317 l_rowid ROWID ;
318 l_person_id NUMBER;
319 --
320 begin
321 --
322 --
323 hr_utility.set_location('hr_person.generate_number',1);
324 --
325 if p_current_applicant = 'Y' then
326 --
327 if g_debug then
328 hr_utility.set_location('number_generation',1);
329 end if;
330 --
331 SELECT pbg.method_of_generation_apl_num
332 , pbg.legislation_code
333 INTO l_method_of_generation
334 , l_legislation_code
335 FROM per_business_groups pbg
336 WHERE pbg.business_group_id = p_business_group_id;
337 --
338 if l_method_of_generation = 'A' then
339 if g_debug then
340 hr_utility.set_location('number_generation',2);
341 end if;
342 if p_applicant_number is NOT NULL then
343 begin
344 select person_id
345 into l_person_id
346 from per_people_f ppf
347 where ppf.applicant_number = p_applicant_number
348 and ppf.business_group_id +0 = p_business_group_id
349 and rownum = 1;
350
351 if (l_person_id = p_person_id) then
352 apl_number_sv := 0;
353 else
354 p_applicant_number := NULL;
355 end if;
356 exception
357 when no_data_found then
358 if p_applicant_number = apl_number_sv then
359 p_applicant_number := NULL;
360 else
361 apl_number_sv := 0;
362 end if;
363 end;
364
365 end if;
366
367 --Second Check
368 if p_applicant_number is NOT NULL then
369 begin
370 select null
371 into p_applicant_number
372 from sys.dual
373 where (p_person_id is not null
374 and not exists (select '1'
375 from per_assignments_f paf
376 where assignment_type = 'A'
377 and paf.person_id = p_person_id
378 )
379 );
380 exception
381 when no_data_found then NULL;
382 end;
383 end if;
384 -- 3652025 >>
385 if (p_applicant_number is NULL) then
386 begin
387 select applicant_number into p_applicant_number
388 from per_people_f
389 where person_id = p_person_id
390 and applicant_number is not null
391 and rownum = 1;
392 exception
393 when others then p_applicant_number := NULL;
394 end;
395 if g_debug then
396 hr_utility.set_location('hr_person.generate_number',15);
397 end if;
398 apl_number_sv := p_applicant_number;
399 end if;
400 -- <<
401 if (p_applicant_number is NULL) then
402 --
403 -- -> #2660279: NEW code
404 Get_Person_Number(p_person_type => 'APL'
405 ,p_person_number => p_applicant_number
406 ,p_business_group_id => p_business_group_id
407 ,p_person_id => p_person_id
408 ,p_effective_date => p_effective_date
409 ,p_party_id => p_party_id
410 ,p_date_of_birth => p_date_of_birth
411 ,p_start_date => p_start_date
412 ,p_national_id => p_national_identifier);
413 --
414 -- <- end NEW code
415 -- ------------------------------------------------------------+
416 -- this code gets replaced with a call to Get_Person_Number |
417 -- ------------------------------------------------------------+
418 -- SELECT next_value
419 -- , rowid
420 -- INTO p_applicant_number
421 -- , l_rowid
422 -- FROM per_number_generation_controls
423 -- WHERE business_group_id +0 = p_business_group_id
424 -- AND type = 'APL'
425 -- FOR UPDATE OF next_value ;
426 --
427 -- UPDATE per_number_generation_controls
428 -- SET next_value = next_value + 1
429 -- WHERE rowid = l_rowid ;
430 -- ------------------------------------------------------------+
431
432 if g_debug then
433 hr_utility.set_location('hr_person.generate_number',20);
434 end if;
435 apl_number_sv := p_applicant_number;
436
437 end if;
438 --
439 elsif l_method_of_generation = 'N'
440 and ((p_applicant_number IS NULL)
441 or (p_national_identifier is null)
442 or (p_national_identifier is not null
443 and p_applicant_number <> p_national_identifier)
444 )
445 then
446 if p_national_identifier is null then
447 if l_legislation_code = 'US' then
448 hr_utility.set_message(801,'HR_7580_ALL_MAN_SOL_FIELD');
449 else
450 hr_utility.set_message(801,'HR_7578_ALL_MAN_NAT_FIELD');
451 end if;
452 hr_utility.raise_error;
453 elsif p_applicant_number is null then -- bug2986823
454 p_applicant_number := p_national_identifier ;
455 end if;
456 elsif l_method_of_generation = 'M'
457 and p_applicant_number IS NULL then
458 hr_utility.set_message(801,'HR_7579_ALL_MAN_APP_FIELD');
459 hr_utility.raise_error;
460 end if;
461 --
462 end if;
463 if p_current_employee = 'Y' then
464 --
465 if g_debug then
466 hr_utility.set_location('hr_person.generate_number',4);
467 end if;
468 SELECT pbg.method_of_generation_emp_num
469 , pbg.legislation_code
470 INTO l_method_of_generation
471 , l_legislation_code
472 FROM per_business_groups pbg
473 WHERE pbg.business_group_id = p_business_group_id;
474 --
475 if l_method_of_generation = 'A' then
476 if g_debug then
477 hr_utility.set_location('hr_person.generate_number',5);
478 end if;
479 if p_employee_number is NOT NULL then
480 begin
481 select person_id
482 into l_person_id
483 from per_people_f ppf
484 where ppf.employee_number = p_employee_number
485 and ppf.business_group_id +0 = p_business_group_id
486 and rownum = 1;
487
488 if (l_person_id = p_person_id) then
489 emp_number_sv := 0;
490 else
491 p_employee_number := NULL;
492 end if;
493 exception
494 when no_data_found then
495 if p_employee_number = emp_number_sv then
496 p_employee_number := NULL;
497 else
498 emp_number_sv := 0;
499 end if;
500 end;
501 end if;
502
503 -- Second check
504 if p_employee_number is NOT NULL then
505 begin
506 select null
507 into p_employee_number
508 from sys.dual
509 where (p_person_id is not null
510 and not exists (select '1'
511 from per_assignments_f paf
512 where assignment_type = 'E'
513 and paf.person_id = p_person_id
514 )
515 );
516 exception
517 when no_data_found then NULL;
518 end;
519 end if;
520
521 if p_employee_number IS NULL then
522 --
523 -- Special case for SSHR if the profile is set
524 -- as we need to make sure that the generation controls table is not
525 -- locked.
526 --
527 if fnd_profile.value('PER_SSHR_NO_EMPNUM_GENERATION') = 'Y' then
528 return;
529 end if;
530 --
531 -- -> #2660279: NEW code
532 Get_Person_Number(p_person_type => 'EMP'
533 ,p_person_number => p_employee_number
534 ,p_business_group_id => p_business_group_id
535 ,p_person_id => p_person_id
536 ,p_effective_date => p_effective_date
537 ,p_party_id => p_party_id
538 ,p_date_of_birth => p_date_of_birth
539 ,p_start_date => p_start_date
540 ,p_national_id => p_national_identifier);
541 --
542 -- <- end NEW code
543 -- ------------------------------------------------------------+
544 -- this code gets replaced with a call to Get_Person_Number |
545 -- ------------------------------------------------------------+
546 -- SELECT next_value
547 -- , rowid
548 -- INTO p_employee_number
549 -- , l_rowid
550 -- FROM per_number_generation_controls
551 -- WHERE business_group_id +0 = p_business_group_id
552 -- AND type = 'EMP'
553 -- FOR UPDATE OF next_value ;
554 --
555 -- UPDATE per_number_generation_controls
556 -- SET next_value = next_value + 1
557 -- WHERE rowid = l_rowid ;
558 -- ------------------------------------------------------------+
559
560 if g_debug then
561 hr_utility.set_location('hr_person.generate_number',6);
562 end if;
563 emp_number_sv := p_employee_number;
564
565 end if;
566 --
567 elsif l_method_of_generation = 'N'
568 and ((p_employee_number IS NULL)
569 or (p_national_identifier is null)
570 or (p_national_identifier is not null
571 and p_employee_number <> p_national_identifier)
572 )
573 then
574 if p_national_identifier is null then
575 if l_legislation_code = 'US' then
576 hr_utility.set_message(801,'HR_7580_ALL_MAN_SOL_FIELD');
577 else
578 hr_utility.set_message(801,'HR_7578_ALL_MAN_NAT_FIELD');
579 end if;
580 hr_utility.raise_error;
581 elsif p_employee_number is null then -- bug#2986823
582 p_employee_number := p_national_identifier ;
583 end if;
584 elsif l_method_of_generation = 'M'
585 and p_employee_number IS NULL then
586 hr_utility.set_message(801,'HR_7581_ALL_MAN_EMP');
587 hr_utility.raise_error;
588 end if;
589 --
590 end if;
591 if p_current_npw = 'Y' then
592 --
593 if g_debug then
594 hr_utility.set_location('hr_person.generate_number',10);
595 end if;
596 SELECT pbg.method_of_generation_cwk_num
597 , pbg.method_of_generation_emp_num
598 , pbg.legislation_code
599 INTO l_method_of_generation
600 , l_method_of_gen_emp
601 , l_legislation_code
602 FROM per_business_groups pbg
603 WHERE pbg.business_group_id = p_business_group_id;
604 --
605 if l_method_of_generation = 'A'
606 or (l_method_of_generation = 'E' and
607 l_method_of_gen_emp = 'A') then
608 if g_debug then
609 hr_utility.set_location('hr_person.generate_number',15);
610 end if;
611 if p_npw_number is NOT NULL then
612 begin
613 select person_id
614 into l_person_id
615 from per_people_f ppf
616 where ppf.npw_number = p_npw_number
617 and ppf.business_group_id +0 = p_business_group_id
618 and rownum = 1;
619
620 if (l_person_id = p_person_id) then
621 npw_number_sv := 0;
622 else
623 p_npw_number := NULL;
624 end if;
625 exception
626 when no_data_found then
627 if p_npw_number = npw_number_sv then
628 p_npw_number := NULL;
629 else
630 npw_number_sv := 0;
631 end if;
632 end;
633 end if;
634
635 -- Second check
636 if p_npw_number is NOT NULL then
637 if g_debug then
638 hr_utility.set_location('hr_person.generate_number',20);
639 end if;
640 begin
641 select null
642 into p_npw_number
643 from sys.dual
644 where (p_person_id is not null
645 and not exists (select '1'
646 from per_assignments_f paf
647 where assignment_type = 'C'
648 and paf.person_id = p_person_id
649 )
650 );
651 exception
652 when no_data_found then NULL;
653 end;
654 end if;
655
656 if p_npw_number IS NULL then
657 if g_debug then
658 hr_utility.set_location('hr_person.generate_number',25);
659 end if;
660
661 if l_method_of_generation = 'A' then
662 --
663 -- Automatic numbering so use the CWK number type.
664 --
665 if g_debug then
666 hr_utility.set_location('hr_person.generate_number',27);
667 end if;
668 -- -> #2660279: NEW code
669 Get_Person_Number(p_person_type => 'CWK'
670 ,p_person_number => p_npw_number
671 ,p_business_group_id => p_business_group_id
672 ,p_person_id => p_person_id
673 ,p_effective_date => p_effective_date
674 ,p_party_id => p_party_id
675 ,p_date_of_birth => p_date_of_birth
676 ,p_start_date => p_start_date
677 ,p_national_id => p_national_identifier);
678 -- <- end NEW code
679 -- ------------------------------------------------------------+
680 -- this code gets replaced with a call to Get_Person_Number |
681 -- ------------------------------------------------------------+
682 -- SELECT next_value
683 -- , rowid
684 -- INTO p_npw_number
685 -- , l_rowid
686 -- FROM per_number_generation_controls
687 -- WHERE business_group_id = p_business_group_id
688 -- AND type = 'CWK'
689 -- FOR UPDATE OF next_value ;
690 --
691 -- UPDATE per_number_generation_controls
692 -- SET next_value = next_value + 1
693 -- WHERE rowid = l_rowid ;
694 -- ------------------------------------------------------------+
695 --
696 elsif l_method_of_generation = 'E'
697 and l_method_of_gen_emp = 'A' then
698 --
699 -- This is based on emp numbering which is automatic.
700 -- Use the next employee number sequence instead of the
701 -- CWK type.
702 --
703 if g_debug then
704 hr_utility.set_location('hr_person.generate_number',28);
705 end if;
706 -- -> #2660279: NEW code
707 Get_Person_Number(p_person_type => 'EMP'
708 ,p_person_number => p_npw_number
709 ,p_business_group_id => p_business_group_id
710 ,p_person_id => p_person_id
711 ,p_effective_date => p_effective_date
712 ,p_party_id => p_party_id
713 ,p_date_of_birth => p_date_of_birth
714 ,p_start_date => p_start_date
715 ,p_national_id => p_national_identifier);
716 --
717 -- <- end NEW code
718 -- ------------------------------------------------------------+
719 -- this code gets replaced with a call to Get_Person_Number |
720 -- ------------------------------------------------------------+
721 -- SELECT next_value
722 -- , rowid
723 -- INTO p_npw_number
724 -- , l_rowid
725 -- FROM per_number_generation_controls
726 -- WHERE business_group_id = p_business_group_id
727 -- AND type = 'EMP'
728 -- FOR UPDATE OF next_value ;
729 --
730 -- UPDATE per_number_generation_controls
731 -- SET next_value = next_value + 1
732 -- WHERE rowid = l_rowid ;
733 -- ------------------------------------------------------------+
734
735 end if;
736
737 if g_debug then
738 hr_utility.set_location('hr_person.generate_number',29);
739 end if;
740 npw_number_sv := p_npw_number;
741
742 end if;
743 --
744 elsif (l_method_of_generation = 'N'
745 or (l_method_of_generation = 'E' and
746 l_method_of_gen_emp = 'N'))
747 and ((p_npw_number IS NULL)
748 or (p_national_identifier is null)
749 or (p_national_identifier is not null
750 and p_npw_number <> p_national_identifier)
751 )
752 then
753 if g_debug then
754 hr_utility.set_location('hr_person.generate_number',30);
755 end if;
756 if p_national_identifier is null then
757 if l_legislation_code = 'US' then
758 hr_utility.set_message(801,'HR_7580_ALL_MAN_SOL_FIELD');
759 else
760 hr_utility.set_message(801,'HR_7578_ALL_MAN_NAT_FIELD');
761 end if;
762 hr_utility.raise_error;
763 elsif p_npw_number is null then -- bug#2986823
764 p_npw_number := p_national_identifier ;
765 end if;
766 elsif(l_method_of_generation = 'M'
767 or (l_method_of_generation = 'E' and
768 l_method_of_gen_emp = 'M'))
769 and p_npw_number IS NULL then
770 if g_debug then
771 hr_utility.set_location('hr_person.generate_number',35);
772 end if;
773 -- Changed the application id form 801 to 800 for fix of #3295346
774 hr_utility.set_message(800,'HR_289692_ALL_MAN_CWK');
775 hr_utility.raise_error;
776 end if;
777 --
778 end if;
779 --
780 -- Fix for bug 3529732 starts here. Commented the following block.
781 -- If the person is not current employee or applicant or CWK
782 -- then pass the same values back to the calling proc.
783 --
784 /*
785 if p_current_employee IS NULL
786 and p_current_applicant IS NULL
787 and p_current_npw IS NULL then
788 begin
789 select p.employee_number
790 , p.applicant_number
791 , p.npw_number
792 into p_employee_number
793 , p_applicant_number
794 , p_npw_number
795 from per_people p
796 where p.person_id = p_person_id;
797 --
798 exception
799 when no_data_found then
800 p_applicant_number := null ;
801 p_employee_number := null ;
802 p_npw_number := null ;
803 when others then
804 raise;
805 end;
806 --
807 end if;
808 */
809 --
810 -- Fix for bug 3529732 ends here.
811 --
812 end generate_number ;
813 --
814 -- -------------------------- derive_full_name ---------------------------
815 -- Construct FULL_NAME based on all name fields and if this name and date of
816 -- birth combination already exists (upper or lower case) then write an error
817 -- but DO NOT FAIL the procedure. Full Name may still be required as forms
818 -- treats this as a warning not an error
819 --
820 PROCEDURE derive_full_name
821 (p_first_name VARCHAR2,
822 p_middle_names VARCHAR2,
823 p_last_name VARCHAR2,
824 p_known_as VARCHAR2,
825 p_title VARCHAR2,
826 p_suffix VARCHAR2,
827 p_pre_name_adjunct VARCHAR2,
828 p_date_of_birth DATE,
829 p_person_id NUMBER,
830 p_business_group_id NUMBER,
831 p_full_name OUT NOCOPY VARCHAR2 ,
832 p_duplicate_flag OUT NOCOPY VARCHAR2,
833 p_per_information1 VARCHAR2 DEFAULT NULL,
834 p_per_information2 VARCHAR2 DEFAULT NULL,
835 p_per_information3 VARCHAR2 DEFAULT NULL,
836 p_per_information4 VARCHAR2 DEFAULT NULL,
837 p_per_information5 VARCHAR2 DEFAULT NULL,
838 p_per_information6 VARCHAR2 DEFAULT NULL,
839 p_per_information7 VARCHAR2 DEFAULT NULL,
840 p_per_information8 VARCHAR2 DEFAULT NULL,
841 p_per_information9 VARCHAR2 DEFAULT NULL,
842 p_per_information10 VARCHAR2 DEFAULT NULL,
843 p_per_information11 VARCHAR2 DEFAULT NULL,
844 p_per_information12 VARCHAR2 DEFAULT NULL,
845 p_per_information13 VARCHAR2 DEFAULT NULL,
846 p_per_information14 VARCHAR2 DEFAULT NULL,
847 p_per_information15 VARCHAR2 DEFAULT NULL,
848 p_per_information16 VARCHAR2 DEFAULT NULL,
849 p_per_information17 VARCHAR2 DEFAULT NULL,
850 p_per_information18 VARCHAR2 DEFAULT NULL,
851 p_per_information19 VARCHAR2 DEFAULT NULL,
852 p_per_information20 VARCHAR2 DEFAULT NULL,
853 p_per_information21 VARCHAR2 DEFAULT NULL,
854 p_per_information22 VARCHAR2 DEFAULT NULL,
855 p_per_information23 VARCHAR2 DEFAULT NULL,
856 p_per_information24 VARCHAR2 DEFAULT NULL,
857 p_per_information25 VARCHAR2 DEFAULT NULL,
858 p_per_information26 VARCHAR2 DEFAULT NULL,
859 p_per_information27 VARCHAR2 DEFAULT NULL,
860 p_per_information28 VARCHAR2 DEFAULT NULL,
861 p_per_information29 VARCHAR2 DEFAULT NULL,
862 p_per_information30 VARCHAR2 DEFAULT NULL)
863 IS
864 --
865 --
866
867 -- These definitions are used to allow the check for duplicate names
868 -- to be case insensitive whilst still using the index. This technique
869 -- is used by forms for items with the case insensitive query option.
870 --
871 -- Example taken from Forms reference manual
872 -- In order to search for names like 'Blake'
873 --
874 -- SELECT * FROM EMP
875 -- WHERE UPPER(ENAME) = 'BLAKE'
876 -- AND ( ENAME LIKE 'Bl%' OR ENAME LIKE 'bL%' OR
877 -- ENAME LIKE 'BL%' OR ENAME LIKE 'bl%' ) ;
878 --
879 -- VT 1159810 01/26/00
880 l_first_char VARCHAR2(5) := substr( p_last_name , 1 , 1 ) ;
881 l_second_char VARCHAR2(5) := substr( p_last_name , 2 , 1 ) ;
882 l_ul_check VARCHAR2(15) := upper(l_first_char)||lower(l_second_char)||'%';
883 l_lu_check VARCHAR2(15) := lower(l_first_char)||upper(l_second_char)||'%';
884 l_uu_check VARCHAR2(15) := upper(l_first_char)||upper(l_second_char)||'%';
885 l_ll_check VARCHAR2(15) := lower(l_first_char)||lower(l_second_char)||'%';
886 --
887 --
888 --
889 l_full_name VARCHAR2(240);
890 l_status VARCHAR2(1);
891 l_title_meaning VARCHAR2(80);
892 --
893 local_warning exception;
894
895 --
896 cursor csr_leg_pkg(p_pkg VARCHAR2) IS
897 select '1'
898 from user_objects
899 where object_name = p_pkg
900 and object_type = 'PACKAGE';
901
902 cursor csr_leg_cod is
903 select legislation_code
904 from per_business_groups_perf -- #3907786 - Changed to per_business_groups_perf
905 where business_group_id = p_business_group_id;
906
907 l_leg_code VARCHAR2(150);
908 -- l_cursor NUMBER;
909 l_dummy VARCHAR2(1);
910 l_procedure_name VARCHAR2(50);
911 l_proc_call VARCHAR2(4790);
912 l_package_name VARCHAR2(50);
913 v_fullname VARCHAR2(240);
914 --
915 begin
916 --
917 p_duplicate_flag:='N';
918 --
919
920 if g_debug then
921 hr_utility.set_location('hr_person.derive_full_name',1);
922 end if;
923
924 if p_title IS NOT NULL and
925 fnd_profile.value('PER_USE_TITLE_IN_FULL_NAME') = 'Y' then
926 SELECT meaning
927 INTO l_title_meaning
928 FROM hr_lookups
929 WHERE lookup_type = 'TITLE'
930 AND p_title = lookup_code;
931 end if;
932
933 if g_debug then
934 hr_utility.set_location('l_title_meaning = '||l_title_meaning,4);
935 end if;
936 --
937 --
938 -- Note this is only a select because PL/SQL can't cope with DECODEs
939 --
940 if g_debug then
941 hr_utility.set_location('hr_person.derive_full_name',2);
942 end if;
943 SELECT rtrim(substrb(DECODE(p_pre_name_adjunct,'','',p_pre_name_adjunct||' ')||
944 p_last_name||','||DECODE(l_title_meaning,'','',
945 ' '||l_title_meaning)||DECODE(p_first_name,'','',
946 ' '||p_first_name)||DECODE(p_middle_names,'','',
947 ' '||p_middle_names)||
948 DECODE(p_suffix,'','',' '||p_suffix)||
949 DECODE(p_known_as,'','',
950 ' ('||p_known_as||')'),1,240))
951 INTO l_full_name
952 FROM sys.dual ;
953 --
954 -- Performing rtrim to remove any blank spaces from the full name, see bug 2042825 for details
955 --
956 p_full_name := rtrim(l_full_name);
957 --
958 if g_debug then
959 hr_utility.set_location('hr_person.derive_full_name',4);
960 end if;
961 open csr_leg_cod;
962 fetch csr_leg_cod into l_leg_code;
963 if csr_leg_cod%found then
964
965 -- Start of bug # 2459815
966 -- check for installed legislation
967 -- added the or condition for the fix of #3291084
968 if ( hr_utility.chk_product_install('Oracle Human Resources',l_leg_code) or (l_leg_code = 'JP')) then
969 -- if a legislation exits then we must call the function
970 -- hr_XX_utility.per_XX_full_name which derives the full name
971 -- according to the legislation.
972
973 l_package_name := 'HR_'||l_leg_code||'_UTILITY';
974 l_procedure_name := 'per_'||lower(l_leg_code)||'_full_name';
975
976
977 -- check package exists
978 open csr_leg_pkg(l_package_name);
979 fetch csr_leg_pkg into l_dummy;
980 if csr_leg_pkg%found then
981 /*
982 #1858645 replaced dbms_sql with native dynamic sql call
983 l_cursor := dbms_sql.open_cursor;
984 */
985
986 -- construct an anonymous block with bind variable
987
988 l_proc_call := 'SELECT rtrim(substrb( '|| l_package_name ||'.'||l_procedure_name||'(:p_first_name,:p_middle_names,:p_last_name,:p_known_as,:p_title,';
989
990 l_proc_call := l_proc_call||':p_suffix,:p_pre_name_adjunct,:p_per_information1,:p_per_information2,:p_per_information3,:p_per_information4,:p_per_information5,';
991
992 l_proc_call := l_proc_call||':p_per_information6,:p_per_information7,:p_per_information8,:p_per_information9,:p_per_information10,';
993
994 l_proc_call := l_proc_call||':p_per_information11,:p_per_information12,:p_per_information13,:p_per_information14,:p_per_information15,:p_per_information16,:p_per_information17,';
995
996 l_proc_call := l_proc_call||':p_per_information18,:p_per_information19,:p_per_information20,:p_per_information21,:p_per_information22,:p_per_information23,:p_per_information24,';
997
998 l_proc_call := l_proc_call||':p_per_information25,:p_per_information26,:p_per_information27,:p_per_information28,:p_per_information29,:p_per_information30),1,240)) FROM sys.dual ';
999
1000 /* #1858645 replaced dbms_sql with native dynamic sql call
1001
1002 -- Parse the statment
1003
1004 dbms_sql.parse(l_cursor, l_proc_call, dbms_sql.V7);
1005
1006 -- Bind input variables
1007 dbms_sql.bind_variable(l_cursor,':p_first_name',p_first_name);
1008 dbms_sql.bind_variable(l_cursor,':p_middle_names',p_middle_names);
1009 dbms_sql.bind_variable(l_cursor,':p_last_name',p_last_name);
1010 dbms_sql.bind_variable(l_cursor,':p_known_as',p_known_as);
1011 dbms_sql.bind_variable(l_cursor,':p_title',p_title);
1012 dbms_sql.bind_variable(l_cursor,':p_suffix',p_suffix);
1013 dbms_sql.bind_variable(l_cursor,':p_pre_name_adjunct',p_pre_name_adjunct);
1014 dbms_sql.bind_variable(l_cursor,':p_per_information1',p_per_information1);
1015 dbms_sql.bind_variable(l_cursor,':p_per_information2',p_per_information2);
1016 dbms_sql.bind_variable(l_cursor,':p_per_information3',p_per_information3);
1017 dbms_sql.bind_variable(l_cursor,':p_per_information4',p_per_information4);
1018 dbms_sql.bind_variable(l_cursor,':p_per_information5',p_per_information5);
1019 dbms_sql.bind_variable(l_cursor,':p_per_information6',p_per_information6);
1020 dbms_sql.bind_variable(l_cursor,':p_per_information7',p_per_information7);
1021 dbms_sql.bind_variable(l_cursor,':p_per_information8',p_per_information8);
1022 dbms_sql.bind_variable(l_cursor,':p_per_information9',p_per_information9);
1023 dbms_sql.bind_variable(l_cursor,':p_per_information10',p_per_information10);
1024 dbms_sql.bind_variable(l_cursor,':p_per_information11',p_per_information11);
1025 dbms_sql.bind_variable(l_cursor,':p_per_information12',p_per_information12);
1026 dbms_sql.bind_variable(l_cursor,':p_per_information13',p_per_information13);
1027 dbms_sql.bind_variable(l_cursor,':p_per_information14',p_per_information14);
1028 dbms_sql.bind_variable(l_cursor,':p_per_information15',p_per_information15);
1029 dbms_sql.bind_variable(l_cursor,':p_per_information16',p_per_information16);
1030 dbms_sql.bind_variable(l_cursor,':p_per_information17',p_per_information17);
1031 dbms_sql.bind_variable(l_cursor,':p_per_information18',p_per_information18);
1032 dbms_sql.bind_variable(l_cursor,':p_per_information19',p_per_information19);
1033 dbms_sql.bind_variable(l_cursor,':p_per_information20',p_per_information20);
1034 dbms_sql.bind_variable(l_cursor,':p_per_information21',p_per_information21);
1035 dbms_sql.bind_variable(l_cursor,':p_per_information22',p_per_information22);
1036 dbms_sql.bind_variable(l_cursor,':p_per_information23',p_per_information23);
1037 dbms_sql.bind_variable(l_cursor,':p_per_information24',p_per_information24);
1038 dbms_sql.bind_variable(l_cursor,':p_per_information27',p_per_information27);
1039 dbms_sql.bind_variable(l_cursor,':p_per_information28',p_per_information28);
1040 dbms_sql.bind_variable(l_cursor,':p_per_information29',p_per_information29);
1041 dbms_sql.bind_variable(l_cursor,':p_per_information30',p_per_information30);
1042
1043 -- Define the Ouput Variables
1044 dbms_sql.define_column(l_cursor,1,v_fullname,240);
1045
1046 -- Execute the statement
1047 l_dummy := dbms_sql.execute(l_cursor);
1048
1049 -- fetch loop
1050 LOOP
1051
1052 IF DBMS_SQL.FETCH_ROWS(l_cursor) = 0 THEN
1053 EXIT;
1054 END IF;
1055
1056 DBMS_SQL.COLUMN_VALUE(l_cursor,1,v_fullname);
1057 END LOOP;
1058
1059
1060 native dynamic sql
1061 */
1062 EXECUTE IMMEDIATE l_proc_call
1063 INTO v_fullname
1064 USING p_first_name
1065 ,p_middle_names
1066 ,p_last_name
1067 ,p_known_as
1068 ,p_title
1069 ,p_suffix
1070 ,p_pre_name_adjunct
1071 ,p_per_information1
1072 ,p_per_information2
1073 ,p_per_information3
1074 ,p_per_information4
1075 ,p_per_information5
1076 ,p_per_information6
1077 ,p_per_information7
1078 ,p_per_information8
1079 ,p_per_information9
1080 ,p_per_information10
1081 ,p_per_information11
1082 ,p_per_information12
1083 ,p_per_information13
1084 ,p_per_information14
1085 ,p_per_information15
1086 ,p_per_information16
1087 ,p_per_information17
1088 ,p_per_information18
1089 ,p_per_information19
1090 ,p_per_information20
1091 ,p_per_information21
1092 ,p_per_information22
1093 ,p_per_information23
1094 ,p_per_information24
1095 ,p_per_information25
1096 ,p_per_information26
1097 ,p_per_information27
1098 ,p_per_information28
1099 ,p_per_information29
1100 ,p_per_information30;
1101
1102
1103 p_full_name := rtrim(v_fullname);
1104 end if;
1105 --
1106 -- Code inserted for Bug 1654922
1107 --
1108 close csr_leg_pkg;
1109 --
1110 -- End of insert for Bug 1654922
1111 --
1112 end if; -- end of bug #2459815
1113 end if;
1114 --
1115 -- Code inserted for Bug 1654922
1116 --
1117 close csr_leg_cod;
1118 --
1119 -- End of insert for Bug 1654922
1120 --
1121
1122 --
1123 -- Bug 2040730
1124 -- When Cross Business Groups is enabled, new global duplicate checking is
1125 -- carried out, so don't need to repeat check here
1126 --
1127 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
1128 begin
1129 --
1130 if g_debug then
1131 hr_utility.set_location('hr_person.derive_full_name',3);
1132 end if;
1133 SELECT 'Y'
1134 INTO l_status
1135 FROM sys.dual
1136 WHERE EXISTS (SELECT 'Duplicate Person Exists'
1137 FROM per_all_people_f pp
1138 WHERE /* Perform case insensitive check on last name */
1139 /* trying to use the index on last name */
1140 upper(pp.last_name) = upper(p_last_name)
1141 AND ( pp.last_name like l_ul_check
1142 OR pp.last_name like l_lu_check
1143 OR pp.last_name like l_uu_check
1144 OR pp.last_name like l_ll_check
1145 )
1146 AND (upper(pp.first_name) = upper(p_first_name)
1147 OR p_first_name IS NULL
1148 OR pp.first_name IS NULL)
1149 AND (pp.date_of_birth = p_date_of_birth
1150 OR p_date_of_birth IS NULL
1151 OR pp.date_of_birth IS NULL)
1152 AND ((p_person_id IS NOT NULL
1153 AND p_person_id <> pp.person_id)
1154 OR p_person_id IS NULL)
1155 AND pp.business_group_id +0 = p_business_group_id);
1156 --
1157 hr_utility.set_message(801,'HR_PERSON_DUPLICATE');
1158
1159 raise local_warning;
1160
1161 --
1162 exception
1163 when NO_DATA_FOUND then null ;
1164 --
1165 end;
1166 end if;
1167 --
1168 --
1169 exception
1170 when local_warning then
1171 hr_utility.set_warning;
1172 p_duplicate_flag:='Y';
1173 -- #3907786 start
1174 when others then
1175 if csr_leg_cod%isopen then
1176 close csr_leg_cod;
1177 end if;
1178 raise;
1179 -- #3907786 end
1180 --
1181 end derive_full_name;
1182 --
1183 PROCEDURE derive_full_name
1184 (p_first_name VARCHAR2,
1185 p_middle_names VARCHAR2,
1186 p_last_name VARCHAR2,
1187 p_known_as VARCHAR2,
1188 p_title VARCHAR2,
1189 p_suffix VARCHAR2,
1190 p_date_of_birth DATE,
1191 p_person_id NUMBER,
1192 p_business_group_id NUMBER,
1193 p_full_name OUT NOCOPY VARCHAR2 ,
1194 p_duplicate_flag OUT NOCOPY VARCHAR2,
1195 p_per_information1 VARCHAR2 DEFAULT NULL,
1196 p_per_information2 VARCHAR2 DEFAULT NULL,
1197 p_per_information3 VARCHAR2 DEFAULT NULL,
1198 p_per_information4 VARCHAR2 DEFAULT NULL,
1199 p_per_information5 VARCHAR2 DEFAULT NULL,
1200 p_per_information6 VARCHAR2 DEFAULT NULL,
1201 p_per_information7 VARCHAR2 DEFAULT NULL,
1202 p_per_information8 VARCHAR2 DEFAULT NULL,
1203 p_per_information9 VARCHAR2 DEFAULT NULL,
1204 p_per_information10 VARCHAR2 DEFAULT NULL,
1205 p_per_information11 VARCHAR2 DEFAULT NULL,
1206 p_per_information12 VARCHAR2 DEFAULT NULL,
1207 p_per_information13 VARCHAR2 DEFAULT NULL,
1208 p_per_information14 VARCHAR2 DEFAULT NULL,
1209 p_per_information15 VARCHAR2 DEFAULT NULL,
1210 p_per_information16 VARCHAR2 DEFAULT NULL,
1211 p_per_information17 VARCHAR2 DEFAULT NULL,
1212 p_per_information18 VARCHAR2 DEFAULT NULL,
1213 p_per_information19 VARCHAR2 DEFAULT NULL,
1214 p_per_information20 VARCHAR2 DEFAULT NULL,
1215 p_per_information21 VARCHAR2 DEFAULT NULL,
1216 p_per_information22 VARCHAR2 DEFAULT NULL,
1217 p_per_information23 VARCHAR2 DEFAULT NULL,
1218 p_per_information24 VARCHAR2 DEFAULT NULL,
1219 p_per_information25 VARCHAR2 DEFAULT NULL,
1220 p_per_information26 VARCHAR2 DEFAULT NULL,
1221 p_per_information27 VARCHAR2 DEFAULT NULL,
1222 p_per_information28 VARCHAR2 DEFAULT NULL,
1223 p_per_information29 VARCHAR2 DEFAULT NULL,
1224 p_per_information30 VARCHAR2 DEFAULT NULL) is
1225 l_pre_name_adjunct VARCHAR2(30);
1226 begin
1227 hr_person.derive_full_name(
1228 p_first_name =>p_first_name
1229 ,p_middle_names =>p_middle_names
1230 ,p_last_name =>p_last_name
1231 ,p_known_as =>p_known_as
1232 ,p_title =>p_title
1233 ,p_suffix =>p_suffix
1234 ,p_pre_name_adjunct =>l_pre_name_adjunct
1235 ,p_date_of_birth =>p_date_of_birth
1236 ,p_person_id => p_person_id
1237 ,p_business_group_id => p_business_group_id
1238 ,p_full_name => p_full_name
1239 ,p_duplicate_flag =>p_duplicate_flag
1240 ,p_per_information1 => p_per_information1
1241 , p_per_information2 => p_per_information2
1242 ,p_per_information3 => p_per_information3
1243 ,p_per_information4 => p_per_information4
1244 ,p_per_information5 => p_per_information5
1245 ,p_per_information6 => p_per_information6
1246 ,p_per_information7 =>p_per_information7
1247 ,p_per_information8 => p_per_information8
1248 ,p_per_information9 => p_per_information9
1249 ,p_per_information10 => p_per_information10
1250 ,p_per_information11 => p_per_information11
1251 ,p_per_information12 => p_per_information12
1252 ,p_per_information13 => p_per_information13
1253 ,p_per_information14 => p_per_information14
1254 ,p_per_information15 => p_per_information15
1255 ,p_per_information16 => p_per_information16
1256 ,p_per_information17 => p_per_information17
1257 ,p_per_information18 => p_per_information18
1258 ,p_per_information19 => p_per_information19
1259 ,p_per_information20 => p_per_information20
1260 ,p_per_information21 => p_per_information21
1261 ,p_per_information22 => p_per_information22
1262 ,p_per_information23 => p_per_information23
1263 ,p_per_information24 => p_per_information24
1264 ,p_per_information25 => p_per_information25
1265 ,p_per_information26 => p_per_information26
1266 ,p_per_information27 => p_per_information27
1267 ,p_per_information28 => p_per_information28
1268 ,p_per_information29 => p_per_information29
1269 ,p_per_information30 => p_per_information30);
1270 end;
1271 --
1272 PROCEDURE derive_full_name
1273 (p_first_name VARCHAR2,
1274 p_middle_names VARCHAR2,
1275 p_last_name VARCHAR2,
1276 p_known_as VARCHAR2,
1277 p_title VARCHAR2,
1278 p_date_of_birth DATE,
1279 p_person_id NUMBER,
1280 p_business_group_id NUMBER,
1281 p_full_name OUT NOCOPY VARCHAR2 ,
1282 p_duplicate_flag OUT NOCOPY VARCHAR2,
1283 p_per_information1 VARCHAR2 DEFAULT NULL,
1284 p_per_information2 VARCHAR2 DEFAULT NULL,
1285 p_per_information3 VARCHAR2 DEFAULT NULL,
1286 p_per_information4 VARCHAR2 DEFAULT NULL,
1287 p_per_information5 VARCHAR2 DEFAULT NULL,
1288 p_per_information6 VARCHAR2 DEFAULT NULL,
1289 p_per_information7 VARCHAR2 DEFAULT NULL,
1290 p_per_information8 VARCHAR2 DEFAULT NULL,
1291 p_per_information9 VARCHAR2 DEFAULT NULL,
1292 p_per_information10 VARCHAR2 DEFAULT NULL,
1293 p_per_information11 VARCHAR2 DEFAULT NULL,
1294 p_per_information12 VARCHAR2 DEFAULT NULL,
1295 p_per_information13 VARCHAR2 DEFAULT NULL,
1296 p_per_information14 VARCHAR2 DEFAULT NULL,
1297 p_per_information15 VARCHAR2 DEFAULT NULL,
1298 p_per_information16 VARCHAR2 DEFAULT NULL,
1299 p_per_information17 VARCHAR2 DEFAULT NULL,
1300 p_per_information18 VARCHAR2 DEFAULT NULL,
1301 p_per_information19 VARCHAR2 DEFAULT NULL,
1302 p_per_information20 VARCHAR2 DEFAULT NULL,
1303 p_per_information21 VARCHAR2 DEFAULT NULL,
1304 p_per_information22 VARCHAR2 DEFAULT NULL,
1305 p_per_information23 VARCHAR2 DEFAULT NULL,
1306 p_per_information24 VARCHAR2 DEFAULT NULL,
1307 p_per_information25 VARCHAR2 DEFAULT NULL,
1308 p_per_information26 VARCHAR2 DEFAULT NULL,
1309 p_per_information27 VARCHAR2 DEFAULT NULL,
1310 p_per_information28 VARCHAR2 DEFAULT NULL,
1311 p_per_information29 VARCHAR2 DEFAULT NULL,
1312 p_per_information30 VARCHAR2 DEFAULT NULL) is
1313 l_suffix VARCHAR2(30);
1314 l_pre_name_adjunct VARCHAR2(30);
1315 begin
1316 hr_person.derive_full_name(
1317 p_first_name =>p_first_name
1318 ,p_middle_names =>p_middle_names
1319 ,p_last_name =>p_last_name
1320 ,p_known_as =>p_known_as
1321 ,p_title =>p_title
1322 ,p_suffix =>l_suffix
1323 ,p_pre_name_adjunct =>l_pre_name_adjunct
1324 ,p_date_of_birth =>p_date_of_birth
1325 ,p_person_id => p_person_id
1326 ,p_business_group_id => p_business_group_id
1327 ,p_full_name => p_full_name
1328 ,p_duplicate_flag =>p_duplicate_flag
1329 ,p_per_information1 => p_per_information1
1330 , p_per_information2 => p_per_information2
1331 ,p_per_information3 => p_per_information3
1332 ,p_per_information4 => p_per_information4
1333 ,p_per_information5 => p_per_information5
1334 ,p_per_information6 => p_per_information6
1335 ,p_per_information7 =>p_per_information7
1336 ,p_per_information8 => p_per_information8
1337 ,p_per_information9 => p_per_information9
1338 ,p_per_information10 => p_per_information10
1339 ,p_per_information11 => p_per_information11
1340 ,p_per_information12 => p_per_information12
1341 ,p_per_information13 => p_per_information13
1342 ,p_per_information14 => p_per_information14
1343 ,p_per_information15 => p_per_information15
1344 ,p_per_information16 => p_per_information16
1345 ,p_per_information17 => p_per_information17
1346 ,p_per_information18 => p_per_information18
1347 ,p_per_information19 => p_per_information19
1348 ,p_per_information20 => p_per_information20
1349 ,p_per_information21 => p_per_information21
1350 ,p_per_information22 => p_per_information22
1351 ,p_per_information23 => p_per_information23
1352 ,p_per_information24 => p_per_information24
1353 ,p_per_information25 => p_per_information25
1354 ,p_per_information26 => p_per_information26
1355 ,p_per_information27 => p_per_information27
1356 ,p_per_information28 => p_per_information28
1357 ,p_per_information29 => p_per_information29
1358 ,p_per_information30 => p_per_information30 );
1359 end;
1360
1361 --
1362 -- ------------------- check_ni_unique --------------------
1363 procedure check_ni_unique
1364 ( p_national_identifier VARCHAR2,
1365 p_person_id NUMBER,
1366 p_business_group_id NUMBER)
1367 --
1368 is
1369 --
1370 l_status VARCHAR2(1);
1371 l_legislation_code VARCHAR2(30);
1372 --
1373 local_warning exception;
1374 --
1375 begin
1376 if g_debug then
1377 hr_utility.set_location('hr_person.validate_national_identifier',1);
1378 end if;
1379 SELECT org_information9
1380 INTO l_legislation_code
1381 FROM hr_organization_information
1382 WHERE org_information_context = 'Business Group Information'
1383 AND organization_id = p_business_group_id;
1384 --
1385 --
1386 --
1387 begin
1388 SELECT 'Y'
1389 INTO l_status
1390 FROM sys.dual
1391 WHERE exists(SELECT '1'
1392 FROM per_all_people_f pp
1393 WHERE (p_person_id IS NULL
1394 OR p_person_id <> pp.person_id)
1395 AND p_national_identifier = pp.national_identifier
1396 AND pp.business_group_id +0 = p_business_group_id
1397 );
1398 --
1399 -- Note this should be printed out as a warning when called from the form
1400 -- but should be picked up as an error by HRLink
1401 --
1402 if l_legislation_code = 'US' then
1403 hr_utility.set_message(801,'HR_EMP_SS_EXISTS');
1404 elsif l_legislation_code = 'GB' then
1405 hr_utility.set_message(801,'HR_EMP_NI_EXISTS');
1406 -- psingla - If the legislation is Polish and profile PER_NI_UNIQUE_ERROR_WARNING is set to NULL
1407 elsif l_legislation_code = 'PL' and fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') is NULL then -- For Poland
1408 null;
1409 else
1410 hr_utility.set_message(801,'HR_EMP_NAT_ID_EXISTS');
1411 end if;
1412 /* psingla - If the legislation is Polish and profile PER_NI_UNIQUE_ERROR_WARNING
1413 is set to NULL then only null statement to be executed.*/
1414 if l_legislation_code = 'PL' and fnd_profile.value('PER_NI_UNIQUE_ERROR_WARNING') is NULL then -- For Poland
1415 null;
1416 else
1417 raise local_warning;
1418 end if;
1419 --
1420 exception
1421 when no_data_found then null;
1422 when local_warning then
1423 raise;
1424 end;
1425 exception
1426 when NO_DATA_FOUND then
1427 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1428 hr_utility.set_message_token('PROCEDURE','CHECK_NI_UNIQUE');
1429 hr_utility.set_message_token('STEP','1');
1430 hr_utility.raise_error;
1431 when local_warning then
1432 hr_utility.set_warning;
1433 end check_ni_unique;
1434 -- ------------------- validate_national_identifier -----------------------
1435 --
1436 -- Pass in national identifier and validate both construct (dependent on
1437 -- the legislation of the business group) and uniqueness within business
1438 -- group
1439 --
1440 PROCEDURE validate_national_identifier
1441 ( p_national_identifier VARCHAR2,
1442 p_person_id NUMBER,
1443 p_business_group_id NUMBER)
1444 --
1445 IS
1446 --
1447 l_legislation_code VARCHAR2(30);
1448 --
1449 begin
1450 --
1451 --
1452 if g_debug then
1453 hr_utility.set_location('hr_person.validate_national_identifier',1);
1454 end if;
1455 SELECT org_information9
1456 INTO l_legislation_code
1457 FROM hr_organization_information
1458 WHERE org_information_context = 'Business Group Information'
1459 AND organization_id = p_business_group_id;
1460 --
1461 --
1462 if l_legislation_code = 'GB' then
1463 if substr(p_national_identifier,1,1) >= 'A'
1464 AND substr(p_national_identifier,1,1) <= 'Z'
1465 AND substr(p_national_identifier,2,1) >= 'A'
1466 AND substr(p_national_identifier,2,1) <= 'Z'
1467 AND substr(p_national_identifier,3,1) >= '0'
1468 AND substr(p_national_identifier,3,1) <= '9'
1469 AND substr(p_national_identifier,4,1) >= '0'
1470 AND substr(p_national_identifier,4,1) <= '9'
1471 AND substr(p_national_identifier,5,1) >= '0'
1472 AND substr(p_national_identifier,5,1) <= '9'
1473 AND substr(p_national_identifier,6,1) >= '0'
1474 AND substr(p_national_identifier,6,1) <= '9'
1475 AND substr(p_national_identifier,7,1) >= '0'
1476 AND substr(p_national_identifier,7,1) <= '9'
1477 AND substr(p_national_identifier,8,1) >= '0'
1478 AND substr(p_national_identifier,8,1) <= '9'
1479 AND ((substr(p_national_identifier,9,1) IN ('A','B','C','D')
1480 AND substr(p_national_identifier,1,2) <> 'TN')
1481 OR (substr(p_national_identifier,9,1) IN ('M','F')
1482 AND substr(p_national_identifier,1,2) = 'TN'))
1483 AND length(p_national_identifier) = 9 then
1484 NULL ;
1485 else
1486 --Fix for bug2356249 start here.
1487 if nvl(fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION'),'ERROR') ='WARN' THEN
1488 hr_utility.set_warning;
1489 elsif nvl(fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION'),'ERROR') ='ERROR' then
1490 hr_utility.set_message(801,'HR_6522_EMP_INVALID_NI_NO');
1491 hr_utility.raise_error;
1492 end if;
1493 --Fix for bug2356249 ends here.
1494 end if;
1495
1496 elsif l_legislation_code = 'US' then
1497 --
1498 -- Translate all possible values out of the string
1499 -- and check for the correct placement of the delimiters
1500 --
1501 if (translate(p_national_identifier,'A01234567890-','A') is null
1502 and substr(p_national_identifier,4,1) = '-'
1503 and substr(p_national_identifier,7,1) = '-')
1504 and length(p_national_identifier) = 11 then
1505 null;
1506 else
1507 --Fix for bug2356249 start here.
1508 if nvl(fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION'),'ERROR') ='WARN' THEN
1509 hr_utility.set_warning;
1510 elsif nvl(fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION'),'ERROR') ='ERROR' then
1511 hr_utility.set_message(801,'HR_7056_EMP_INVALID_SS_NO');
1512 hr_utility.raise_error;
1513 end if;
1514 --Fix for bug2356249 ends here.
1515 end if;
1516 end if;
1517 exception
1518 when NO_DATA_FOUND then
1519 null;
1520 --
1521 --
1522 end validate_national_identifier;
1523 --
1524 --
1525 -- ----------------------- validate_dob ------------------------------------
1526 --
1527 -- Date of Birth must be greater than start date for employees and applicants
1528 --
1529 PROCEDURE validate_dob
1530 (p_date_of_birth DATE,
1531 p_start_date DATE)
1532 --
1533 IS
1534 --
1535 begin
1536 --
1537 --
1538 if g_debug then
1539 hr_utility.set_location('hr_person.validate_dob',1);
1540 end if;
1541 if p_date_of_birth > p_start_date
1542 then
1543 hr_utility.set_message(801,'HR_6523_PERSON_DOB_GT_START');
1544 hr_utility.raise_error;
1545 end if;
1546 --
1547 end validate_dob;
1548 --
1549 --
1550 PROCEDURE validate_sex_and_title (p_current_employee VARCHAR2
1551 , p_sex VARCHAR2
1552 , p_title VARCHAR2)
1553 IS
1554 --
1555 local_warning exception;
1556 --
1557 begin
1558 --
1559 --
1560 if g_debug then
1561 hr_utility.set_location('hr_person.validate_sex_and_title',1);
1562 end if;
1563 if p_current_employee = 'Y' then
1564 if p_sex IS NULL then
1565 hr_utility.set_message(801,'HR_6524_EMP_MANDATORY_SEX');
1566 hr_utility.raise_error;
1567 end if;
1568 end if;
1569 --
1570 if g_debug then
1571 hr_utility.set_location('hr_person.validate_sex_and_title',2);
1572 end if;
1573 if p_sex IS NULL then
1574 hr_utility.set_message(801,'PAY_6361_USER_TABLE_UNIQUE');
1575 raise local_warning;
1576 elsif p_title IS NULL then
1577 hr_utility.set_message(801,'PAY_6361_USER_TABLE_UNIQUE');
1578 raise local_warning;
1579 elsif p_title = 'MR.' then
1580 if p_sex <> 'M' then
1581 hr_utility.set_message(801,'HR_6527_PERSON_SEX_AND_TITLE');
1582 hr_utility.raise_error;
1583 end if;
1584 elsif p_title IN ('MRS.','MS.','MISS') then
1585 if p_sex <> 'F' then
1586 hr_utility.set_message(801,'HR_6527_PERSON_SEX_AND_TITLE');
1587 hr_utility.raise_error;
1588 end if;
1589 end if;
1590 --
1591 exception
1592 when local_warning then
1593 hr_utility.set_warning;
1594 --
1595 end validate_sex_and_title;
1596 --
1597 -- --------------------------------------------------------------------- +
1598 -- Validate_Unique_Number +
1599 -- --------------------------------------------------------------------- +
1600 PROCEDURE validate_unique_number (p_person_id NUMBER
1601 , p_business_group_id NUMBER
1602 , p_employee_number VARCHAR2
1603 , p_applicant_number VARCHAR2
1604 , p_npw_number VARCHAR2
1605 , p_current_employee VARCHAR2
1606 , p_current_applicant VARCHAR2
1607 , p_current_npw VARCHAR2)
1608 IS
1609 --
1610 l_status VARCHAR2(1);
1611 --
1612 begin
1613 --
1614 --
1615 if g_debug then
1616 hr_utility.set_location('hr_person.validate_unique_number',1);
1617 end if;
1618 if p_current_applicant = 'Y' then
1619 if p_applicant_number IS NULL then
1620 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1621 hr_utility.set_message_token('PROCEDURE','VALIDATE_UNIQUE_NUMBER');
1622 hr_utility.set_message_token('STEP','1');
1623 hr_utility.raise_error;
1624 end if;
1625 --
1626 if g_debug then
1627 hr_utility.set_location('hr_person.validate_unique_number',2);
1628 end if;
1629 -- #2660279:
1630 -- --> delete this code
1631 -- SELECT 'Y'
1632 -- INTO l_status
1633 -- FROM sys.dual
1634 -- WHERE exists (SELECT 'Y'
1635 -- FROM per_all_people_f pp
1636 -- WHERE (p_person_id IS NULL
1637 -- OR p_person_id <> pp.person_id)
1638 -- AND pp.business_group_id +0 = p_business_group_id
1639 -- AND pp.applicant_number = p_applicant_number);
1640 -- <- end delete
1641 -- >> new code
1642 if is_unique_person_number(p_person_id => p_person_id
1643 ,p_person_type => 'APL'
1644 ,p_person_number => p_applicant_number
1645 ,p_business_group_id => p_business_group_id
1646 ) = 'N'
1647 then
1648
1649 hr_utility.set_message(801,'HR_7693_PERSON_NUM_EXISTS');
1650 hr_utility.raise_error;
1651 end if;
1652 -- << End New code
1653 --
1654 end if;
1655 --
1656 if g_debug then
1657 hr_utility.set_location('hr_person.validate_unique_number',3);
1658 end if;
1659 if p_current_employee = 'Y' then
1660 if p_employee_number IS NULL then
1661 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1662 hr_utility.set_message_token('PROCEDURE','VALIDATE_UNIQUE_NUMBER');
1663 hr_utility.set_message_token('STEP','3');
1664 hr_utility.raise_error;
1665 end if;
1666 --
1667 if g_debug then
1668 hr_utility.set_location('hr_person.validate_unique_number',4);
1669 end if;
1670 -- #2660279: >> New code
1671 if is_unique_person_number(p_person_id => p_person_id
1672 ,p_person_type => 'EMP'
1673 ,p_person_number => p_employee_number
1674 ,p_business_group_id => p_business_group_id
1675 ) = 'N'
1676 then
1677
1678 hr_utility.set_message(801,'HR_7692_PERSON_NUM_EXIST');
1679 hr_utility.raise_error;
1680 end if;
1681 -- << end new code
1682 --
1683
1684 -- SELECT 'Y'
1685 -- INTO l_status
1686 -- FROM sys.dual
1687 -- WHERE exists (select 'Y'
1688 -- FROM per_all_people_f pp
1689 -- WHERE (p_person_id IS NULL
1690 -- OR p_person_id <> pp.person_id)
1691 -- AND pp.business_group_id = p_business_group_id
1692 -- AND (pp.employee_number = p_employee_number
1693 -- OR (pp.npw_number = p_employee_number
1694 -- AND EXISTS
1695 -- (SELECT null
1696 -- FROM per_business_groups pbg
1697 -- WHERE pbg.business_group_id = p_business_group_id
1698 -- AND NVL(method_of_generation_cwk_num,hr_api.g_varchar2) = 'E'))));
1699 --
1700 -- << end 2660279
1701 end if;
1702 if g_debug then
1703 hr_utility.set_location('hr_person.validate_unique_number',5);
1704 end if;
1705 -- +---------------------------------------------------------------------+
1706 -- Processing contingent workers
1707 -- +---------------------------------------------------------------------+
1708 if p_current_npw = 'Y' then
1709 if p_npw_number IS NULL then
1710 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1711 hr_utility.set_message_token('PROCEDURE','VALIDATE_UNIQUE_NUMBER');
1712 hr_utility.set_message_token('STEP','4');
1713 hr_utility.raise_error;
1714 end if;
1715 --
1716 if g_debug then
1717 hr_utility.set_location('hr_person.validate_unique_number',6);
1718 end if;
1719 -- #2660279:
1720 -- >> delete code
1721 -- SELECT 'Y'
1722 -- INTO l_status
1723 -- FROM sys.dual
1724 -- WHERE exists (select 'Y'
1725 -- FROM per_all_people_f pp
1726 -- WHERE (p_person_id IS NULL
1727 -- OR p_person_id <> pp.person_id)
1728 -- AND pp.business_group_id = p_business_group_id
1729 -- AND (pp.npw_number = p_npw_number
1730 -- OR (pp.employee_number = p_npw_number
1731 -- AND EXISTS
1732 -- (SELECT null
1733 -- FROM per_business_groups pbg
1734 -- WHERE pbg.business_group_id = p_business_group_id
1735 -- AND NVL(method_of_generation_cwk_num,hr_api.g_varchar2) = 'E'))));
1736 -- << end delete code
1737 -- >> new code
1738 if is_unique_person_number(p_person_id => p_person_id
1739 ,p_person_type => 'CWK'
1740 ,p_person_number => p_npw_number
1741 ,p_business_group_id => p_business_group_id
1742 ) = 'N'
1743 then
1744
1745 hr_utility.set_message(800,'HR_289656_CWK_NUM_EXIST');
1746 hr_utility.raise_error;
1747 end if;
1748 -- << end new code
1749 --
1750 end if;
1751 --
1752 --
1753 end validate_unique_number;
1754 --
1755 --
1756 -------------------- BEGIN: product_installed ------------------------------
1757 /*
1758 NAME
1759 product_installed
1760 DESCRIPTION
1761 Returns 'Y' if this product is installed, 'N' if not in p_yes_no
1762 and the ORACLEID of the application in p_oracle_username.
1763 */
1764 --
1765 PROCEDURE product_installed (p_application_short_name IN varchar2,
1766 p_status OUT NOCOPY varchar2,
1767 p_yes_no OUT NOCOPY varchar2,
1768 p_oracle_username OUT NOCOPY varchar2)
1769 IS
1770 --
1771 BEGIN
1772 --
1773 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1774 --
1775 hr_person_internal.product_installed(p_application_short_name,
1776 p_status,
1777 p_yes_no,
1778 p_oracle_username);
1779
1780 --
1781 END product_installed;
1782 -------------------- END: product_installed --------------------------------
1783 --
1784 -------------------- BEGIN: weak_predel_validation -------------------------
1785 /*
1786 NAME
1787 weak_predel_validation
1788 DESCRIPTION
1789 Validates whether a person can be deleted from the HR database.
1790 This is the weak validation performed prior to delete using the
1791 Delete Person form.
1792 */
1793 --
1794 PROCEDURE weak_predel_validation (p_person_id IN number,
1795 p_session_date IN date)
1796 IS
1797 --
1798 BEGIN
1799 --
1800 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1801 --
1802 hr_person_internal.weak_predel_validation(p_person_id,
1803 p_session_date);
1804 END weak_predel_validation;
1805 -------------------- END: weak_predel_validation --------------------------
1806 --
1807 -------------------- BEGIN: strong_predel_validation ---------------------
1808 /*
1809 NAME
1810 strong_predel_validation
1811 DESCRIPTION
1812 Called from PERREAQE and PERPEEPI. It performs many checks
1813 to find if additional data has been entered for this person. It is
1814 more stringent than weak_predel_validation and ensures that this
1815 person only has the default data set up by entering a person, contact
1816 or applicant afresh onto the system.
1817 If additional data is found then the delete of this person from
1818 the calling module is invalid as it is beyond its scope. The Delete
1819 Person form should therefore be used (which only performs
1820 weak_predel_validation) if a delete really is required.
1821 p_person_mode - 'A' check for applicants
1822 'E' check for employees
1823 'O' check for other types
1824
1825 NOTE
1826 No validation is required for security (PER_PERSON_LIST* tables) as
1827 this is implicit for the person via assignment criteria. The
1828 rows in these tables can just be deleted.
1829 */
1830 PROCEDURE strong_predel_validation (p_person_id IN number,
1831 p_session_date IN date)
1832 IS
1833 --
1834 --
1835 BEGIN
1836 --
1837 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1838 --
1839 hr_person_internal.strong_predel_validation(p_person_id,
1840 p_session_date);
1841 END strong_predel_validation;
1842 -------------------- END: strong_predel_validation -----------------------
1843 --
1844 -------------------- BEGIN: check_contact ---------------------------------
1845 /*
1846 NAME
1847 check_contact
1848 DESCRIPTION
1849 Is this contact a contact for anybody else? If so then do nothing.
1850 If not then check if this person has ever been an employee or
1851 applicant. If they have not then check whether they have any extra
1852 info entered for them (other than default info). If they have not
1853 then delete this contact also. Otherwise do nothing.
1854 NOTES
1855 p_person_id non-contact in relationship
1856 p_contact_person_id contact in this relationship - the person
1857 who the check is performed against.
1858 p_contact_relationship_id relationship which is currently being
1859 considered for this contact.
1860 */
1861 --
1862 PROCEDURE check_contact (p_person_id IN number,
1863 p_contact_person_id IN number,
1864 p_contact_relationship_id IN number,
1865 p_session_date IN date)
1866 IS
1867 --
1868 BEGIN
1869 --
1870 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1871 --
1872 hr_person_internal.check_contact(p_person_id,
1873 p_contact_person_id,
1874 p_contact_relationship_id,
1875 p_session_date);
1876 END check_contact;
1877 -------------------- END: check_contact ---------------------------------
1878 --
1879 -------------------- BEGIN: delete_a_person --------------------------------
1880 /*
1881 NAME
1882 delete_a_person
1883 DESCRIPTION
1884 Validates whether a person can be deleted from the HR database.
1885 It is assumed that weak_predel_validation and the other application
1886 *_delete_person.*_predel_valdation procedures have been successfully
1887 completed first.
1888 Cascades are all performed according to the locking ladder.
1889 NOTE
1890 P_FORM_CALL is set to 'Y' if this procedure is called from a forms
1891 module. In this case, the deletes are performed post-delete and a
1892 row therefore may not exist in per_people_f (for this person_id).
1893 For this reason the existance check will be ignored.
1894 */
1895 --
1896 PROCEDURE delete_a_person (p_person_id IN number,
1897 p_form_call IN boolean,
1898 p_session_date IN date)
1899 IS
1900 --
1901 BEGIN
1902 --
1903 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1904 --
1905 hr_person_internal.delete_person(p_person_id,
1906 -- p_form_call,
1907 p_session_date);
1908 END delete_a_person;
1909 -------------------- END: delete_a_person ----------------------------------
1910 --
1911 -------------------- BEGIN: people_default_deletes -------------------------
1912 /*
1913 NAME
1914 people_default_deletes
1915 DESCRIPTION
1916 Delete routine for deleting information set up as default when people
1917 are created. Used primarily for delete on PERPEEPI (Enter Person).
1918 The strong_predel_validation should first be performed to ensure that
1919 no additional info (apart from default) has been entered.
1920 NOTE
1921 See delete_a_person for p_form_call details. Further, p_form_call is
1922 set to TRUE when this procedure is called from check_contact as
1923 there is no need to check the existance of the contact.
1924 */
1925 --
1926 PROCEDURE people_default_deletes (p_person_id IN number,
1927 p_form_call IN boolean)
1928 IS
1929 --
1930 --
1931 --
1932 BEGIN
1933 --
1934 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1935 --
1936 hr_person_internal.people_default_deletes(p_person_id);
1937 -- p_form_call);
1938 --
1939 END people_default_deletes;
1940 -------------------- END: people_default_deletes --------------------------
1941 --
1942 -------------------- BEGIN: applicant_default_deletes ---------------------
1943 /*
1944 NAME
1945 applicant_default_deletes
1946 DESCRIPTION
1947 Delete routine for deleting information set up as default when
1948 applicants are entered. Used primarily for delete on PERREAQE
1949 (Applicant Quick Entry). The strong_predel_validation should first be
1950 performed to ensure that no additional info (apart from default) has
1951 been entered.
1952 NOTE
1953 See delete_a_person for p_form_call details.
1954 */
1955 --
1956 PROCEDURE applicant_default_deletes (p_person_id IN number,
1957 p_form_call IN boolean)
1958 IS
1959 --
1960 --
1961 BEGIN
1962 --
1963 -- Fix for bug 3908271. replace hr_person_delete with hr_person_internal.
1964 --
1965 hr_person_internal.applicant_default_deletes(p_person_id);
1966 -- p_form_call);
1967 END applicant_default_deletes;
1968 -------------------- END: applicant_default_deletes -----------------------
1969 --
1970 ------------------------- BEGIN: chk_future_person_type --------------------
1971 --
1972 --NAME
1973 -- chk_future_person_type
1974 --DESCRIPTION
1975 -- Returns TRUE or FALSE depending on status of applicants Person_types.
1976 --PARAMETERS
1977 -- p_system_person_type : Current system person type of person.
1978 -- p_person_id : Unique id of the Applicant being hired.
1979 -- p_business_group_id : Id of the business group.
1980 -- p_end_date : End date of the unaccepted applications = start date - 1
1981 --
1982 FUNCTION chk_future_person_type
1983 (p_system_person_type IN VARCHAR2
1984 ,p_person_id IN INTEGER
1985 ,p_business_group_id IN INTEGER
1986 ,p_effective_start_date IN DATE) RETURN BOOLEAN IS
1987 l_check_all VARCHAR2 (1);
1988 l_return boolean;
1989 begin
1990 l_check_all := 'Y';
1991 l_return := chk_future_person_type(p_system_person_type => p_system_person_type
1992 ,p_person_id => p_person_id
1993 ,p_business_group_id => p_business_group_id
1994 ,p_check_all => l_check_all
1995 ,p_effective_start_date => p_effective_start_date);
1996 return l_return;
1997 end;
1998 FUNCTION chk_future_person_type
1999 (p_system_person_type IN VARCHAR2
2000 ,p_person_id IN INTEGER
2001 ,p_business_group_id IN INTEGER
2002 ,p_check_all IN VARCHAR2 DEFAULT 'Y'
2003 ,p_effective_start_date IN DATE) RETURN BOOLEAN IS
2004 --
2005 p_test_func varchar2(60);
2006 --
2007 BEGIN
2008 --
2009 if g_debug then
2010 hr_utility.set_location('hr_person.chk_future_person_type',1);
2011 end if;
2012 --
2013 --
2014 select 'Y'
2015 into p_test_func
2016 from sys.dual
2017 where exists(
2018 select 'Future Person Type exists'
2019 from per_people_f ppf
2020 ,per_person_types ppt
2021 ,per_startup_person_types pst
2022 where ppf.person_type_id = ppt.person_type_id
2023 and ppf.person_id = p_person_id
2024 and ppf.business_group_id +0 = ppt.business_group_id+0
2025 and ppf.business_group_id +0 = p_business_group_id
2026 and (((p_effective_start_date < ppf.effective_start_date)
2027 and p_check_all = 'Y')
2028 or (p_effective_start_date = ppf.effective_start_date))
2029 and ppt.system_person_type <> pst.system_person_type
2030 and pst.system_person_type = p_system_person_type
2031 union
2032 select 'Future Person Type exists'
2033 from per_periods_of_service pps
2034 where pps.person_id = p_person_id
2035 and p_effective_start_date < pps.date_start
2036 union --fix for bug 6730008
2037 select 'Future Person Type exists'
2038 from per_periods_of_placement pps
2039 where p_system_person_type='OTHER'
2040 and pps.person_id = p_person_id
2041 and p_effective_start_date < nvl(pps.actual_termination_date,p_effective_start_date)
2042 );
2043 --
2044 RETURN TRUE;
2045 --
2046 exception
2047 when no_data_found then
2048 RETURN FALSE;
2049 when hr_utility.hr_error then
2050 raise;
2051 RETURN FALSE;
2052 when others then
2053 hr_utility.oracle_error(sqlcode);
2054 RETURN FALSE;
2055 END chk_future_person_type;
2056 ------------------------- END: chk_future_person_type --------------------
2057 --
2058 ------------------------- BEGIN: chk_prev_person_type --------------------
2059 --
2060 --NAME
2061 -- chk_prev_person_type
2062 --DESCRIPTION
2063 -- Returns TRUE or FALSE depending on status of applicants Person_types.
2064 --PARAMETERS
2065 -- p_system_person_type : Current system person type of person.
2066 -- p_person_id : Unique id of the Applicant being hired.
2067 -- p_business_group_id : Id of the business group.
2068 -- p_end_date : End date of the unaccepted applications = start date - 1
2069 --
2070 FUNCTION chk_prev_person_type
2071 (p_system_person_type IN VARCHAR2
2072 ,p_person_id IN INTEGER
2073 ,p_business_group_id IN INTEGER
2074 ,p_effective_start_date IN DATE) RETURN BOOLEAN IS
2075 --
2076 p_test_func varchar2(60);
2077 --
2078 BEGIN
2079 --
2080 if g_debug then
2081 hr_utility.set_location('hr_person.chk_prev_person_type',1);
2082 hr_utility.set_location('p_system_person_type= '||p_system_person_type,10);
2083 hr_utility.set_location('p_person_id= '||p_person_id,20);
2084 hr_utility.set_location('p_business_group_id= '||p_business_group_id,30);
2085 hr_utility.set_location('p_effective_start_date= '||p_effective_start_date,40);
2086 end if;
2087
2088 select 'Y'
2089 into p_test_func
2090 from sys.dual
2091 where exists
2092 (
2093 -- code change start for bug 3957689
2094 select 'Previous Person type exists'
2095 from per_all_people_f ppf ,
2096 per_person_types ppt ,
2097 per_startup_person_types pst ,
2098 per_person_type_usages_f ptu
2099 where ppf.person_id = p_person_id
2100 and ppf.business_group_id +0= p_business_group_id
2101 and ppf.business_group_id +0= ppt.business_group_id +0
2102 and pst.system_person_type = p_system_person_type
2103 and ppt.system_person_type <> pst.system_person_type
2104 and ppf.person_id = ptu.person_id
2105 and ptu.person_type_id = ppt.person_type_id
2106 /* and p_effective_start_date between
2107 ptu.effective_start_date and ptu.effective_end_date --- fix for bug 6161469 */
2108 union
2109 select 'Previous Person type exists'
2110 from per_periods_of_service pps
2111 where pps.person_id = p_person_id
2112 and p_effective_start_date > nvl(pps.actual_termination_date,
2113 p_effective_start_date)
2114 union
2115 select 'Previous Person type exists'
2116 from per_periods_of_placement ppp
2117 where ppp.person_id = p_person_id
2118 and p_effective_start_date > nvl(ppp.actual_termination_date,
2119 p_effective_start_date)); --fix for bug 5961371.
2120
2121
2122 /*
2123 select 'Previous Person type exists'
2124 from per_people_f ppf
2125 ,per_person_types ppt
2126 ,per_startup_person_types pst
2127 where ppf.person_type_id = ppt.person_type_id
2128 and ppf.person_id = p_person_id
2129 and ppf.business_group_id +0= ppt.business_group_id +0
2130 and ppf.business_group_id +0= p_business_group_id
2131 and p_effective_start_date > ppf.effective_start_date
2132 and ppt.system_person_type <> pst.system_person_type
2133 and pst.system_person_type = p_system_person_type
2134 union
2135 select 'Previous Person type exists'
2136 from per_periods_of_service pps
2137 where pps.person_id = p_person_id
2138 and p_effective_start_date > nvl(pps.actual_termination_date,
2139 p_effective_start_date));
2140 */
2141 -- code change ended for bug 3957689
2142 --
2143 RETURN TRUE;
2144 --
2145 exception
2146 when no_data_found then
2147 RETURN FALSE;
2148 when hr_utility.hr_error then
2149 raise;
2150 RETURN FALSE;
2151 when others then
2152 hr_utility.oracle_error(sqlcode);
2153 RETURN FALSE;
2154 END chk_prev_person_type;
2155 ------------------------- BEGIN: chk_prev_person_type --------------------
2156 --
2157 ------------------------- BEGIN: validate_address --------------------
2158 PROCEDURE validate_address(p_person_id INTEGER
2159 ,p_business_group_id INTEGER
2160 ,p_address_id INTEGER
2161 ,p_date_from DATE
2162 ,p_date_to DATE
2163 ,p_end_of_time DATE
2164 ,p_primary_flag VARCHAR2) IS
2165 /*
2166 --NAME
2167 -- validate_address
2168 --DESCRIPTION
2169 -- Returns TRUE or FALSE depending on status of applicants Person_types.
2170 --PARAMETERS
2171 -- p_person_id : Unique Id of the person.
2172 -- p_business_group_id:Id of the business group.
2173 -- p_address_id:Id of the addrtess.
2174 -- p_date_from: Start date of the address being validated.
2175 -- p_date_to: End date of the address being validated.
2176 -- p_end_of_time :Ultimate date on Oracle system 31-Dec-4712.
2177 -- p_primary_flag: Whether primary or secondary.
2178 */
2179 --
2180 v_dummy VARCHAR2(30);
2181 -- primary flag test.
2182 l_primary_flag VARCHAR2(1) :='Y';
2183 --
2184 begin
2185 if g_debug then
2186 hr_utility.set_location('hr_person.validate_address',1);
2187 end if;
2188 --
2189 --
2190 select 'Error : Primary address exists'
2191 into v_dummy
2192 from sys.dual
2193 where exists (select 'address exists'
2194 from per_addresses pa
2195 where pa.person_id = p_person_id
2196 and pa.business_group_id +0 = p_business_group_id
2197 and (pa.address_id <> p_address_id
2198 or p_address_id is null)
2199 and pa.primary_flag = l_primary_flag
2200 and (p_date_from between pa.date_from
2201 and nvl(pa.date_to,p_end_of_time)
2202 or nvl(p_date_to,p_end_of_time) between
2203 pa.date_from and nvl(pa.date_to,p_end_of_time))
2204 );
2205 --
2206 -- Primary exists and form trying to enter primary
2207 -- then raise error
2208 --
2209 if p_primary_flag = 'Y' then
2210 hr_utility.set_message(801,'HR_6510_PER_PRIMARY_ADDRESS');
2211 hr_utility.raise_error;
2212 end if;
2213 exception
2214 when NO_DATA_FOUND then
2215 -- if no primary found
2216 -- then if form has primary set
2217 -- do nothing
2218 -- else flag an error
2219 --
2220 if p_primary_flag <> 'Y' then
2221 hr_utility.set_message(801,'HR_7144_PER_NO_PRIM_ADD');
2222 hr_utility.raise_error;
2223 end if;
2224 end validate_address;
2225 ------------------------- END: validate_address --------------------
2226
2227 end hr_person;
2228
[Home] [Help]