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