4 -- ----------------------------------------------------------------------------
1 Package per_per_bus AUTHID CURRENT_USER as
2 /* $Header: peperrhi.pkh 120.3 2011/11/17 09:18:28 sidsaxen ship $ */
3 --
5 -- | Global Definitions - Internal Development Use Only |
6 -- ----------------------------------------------------------------------------
7 --
8 --
9 -- Following used to keep track of system_person_type derived by
10 -- return_system_person_type function when called multiple times
11 -- in same pass through validation procedures.
12 --
13 g_previous_sys_per_type per_person_types.system_person_type%TYPE;
14 --
15 -- Following used to allow omitting validation on employee number when a
16 -- global transfer is being processed
17 --
18 g_global_transfer_in_process boolean;
19 --
20 -- ----------------------------------------------------------------------------
21 -- |---------------------------< insert_validate >----------------------------|
22 -- ----------------------------------------------------------------------------
23 -- {Start Of Comments}
24 --
25 -- Description:
26 -- This procedure controls the execution of all insert business rules
27 -- validation.
28 --
29 -- Pre Conditions:
30 -- This private procedure is called from ins procedure.
31 --
32 -- In Arguments:
33 -- A Pl/Sql record structre.
34 --
35 -- Post Success:
36 -- Processing continues.
37 --
38 -- Post Failure:
39 -- If a business rules fails the error will not be handled by this procedure
40 -- unless explicity coded.
41 --
42 -- Developer Implementation Notes:
43 -- For insert, your business rules should be coded within this procedure and
44 -- should ideally (unless really necessary) just be straight procedure or
45 -- function calls. Try and avoid using conditional branching logic.
46 --
47 -- Validate Important Attributes
48 --
49 -- Access Status:
50 -- Internal Table Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure insert_validate
58 p_validation_start_date in date,
55 (p_rec in out nocopy per_per_shd.g_rec_type,
56 p_effective_date in date,
57 p_datetrack_mode in varchar2,
59 p_validation_end_date in date,
60 p_name_combination_warning out nocopy boolean,
61 p_dob_null_warning out nocopy boolean,
62 p_orig_hire_warning out nocopy boolean);
63 --
64 -- ----------------------------------------------------------------------------
65 -- |---------------------------< update_validate >----------------------------|
66 -- ----------------------------------------------------------------------------
67 -- {Start Of Comments}
68 --
69 -- Description:
70 -- This procedure controls the execution of all update business rules
71 -- validation.
72 --
73 -- Pre Conditions:
74 -- This private procedure is called from upd procedure.
75 --
76 -- In Arguments:
77 -- A Pl/Sql record structre.
78 --
79 -- Post Success:
80 -- Processing continues.
81 --
82 -- Post Failure:
83 -- If a business rules fails the error will not be handled by this procedure
84 -- unless explicity coded.
85 --
86 -- Developer Implementation Notes:
87 -- For update, your business rules should be coded within this procedure and
88 -- should ideally (unless really necessary) just be straight procedure or
89 -- function calls. Try and avoid using conditional branching logic.
90 --
91 -- Validate Important Attributes
92 --
93 -- Access Status:
94 -- Internal Table Handler Use Only.
95 --
96 -- {End Of Comments}
97 -- ----------------------------------------------------------------------------
98 Procedure update_validate
99 (p_rec in out nocopy per_per_shd.g_rec_type,
100 p_effective_date in date,
101 p_datetrack_mode in varchar2,
102 p_validation_start_date in date,
103 p_validation_end_date in date,
104 p_name_combination_warning out nocopy boolean,
105 p_dob_null_warning out nocopy boolean,
106 p_orig_hire_warning out nocopy boolean
107 );
108 --
109 -- ----------------------------------------------------------------------------
110 -- |---------------------------< delete_validate >----------------------------|
111 -- ----------------------------------------------------------------------------
112 -- {Start Of Comments}
113 --
114 -- Description:
115 -- This procedure controls the execution of all delete business rules
116 -- validation.
117 --
118 -- Pre Conditions:
119 -- This private procedure is called from del procedure.
120 --
121 -- In Arguments:
122 -- A Pl/Sql record structre.
123 --
124 -- Post Success:
125 -- Processing continues.
126 --
127 -- Post Failure:
128 -- If a business rules fails the error will not be handled by this procedure
129 -- unless explicity coded.
130 --
131 -- Developer Implementation Notes:
132 -- For delete, your business rules should be coded within this procedure and
133 -- should ideally (unless really necessary) just be straight procedure or
134 -- function calls. Try and avoid using conditional branching logic.
135 --
136 -- Access Status:
137 -- Internal Table Handler Use Only.
138 --
139 -- {End Of Comments}
140 -- ----------------------------------------------------------------------------
141 Procedure delete_validate
142 (p_rec in per_per_shd.g_rec_type,
143 p_effective_date in date,
144 p_datetrack_mode in varchar2,
145 p_validation_start_date in date,
146 p_validation_end_date in date);
147 --
148 -- ---------------------------------------------------------------------------
149 -- |---------------------< return_legislation_code >-------------------------|
150 -- ---------------------------------------------------------------------------
151 --
152 -- Description:
153 -- Return the legislation code for a specific person
154 --
155 -- Prerequisites:
156 -- The person identified by p_person_id already exists.
157 --
158 -- In Arguments:
159 -- p_person_id
160 --
161 -- Post Success:
162 -- If the person is found this function will return the person's business
163 -- group legislation code.
164 --
165 -- Post Failure:
166 -- An error is raised if the person does not exist.
167 --
168 -- Access Status:
169 -- Internal Development Use Only.
170 --
171 function return_legislation_code
172 (p_person_id in number
173 ) return varchar2;
174 --
175 -- ---------------------------------------------------------------------------
176 -- |-----------------------< chk_system_pers_type >--------------------------|
177 -- ---------------------------------------------------------------------------
178 --
179 -- Description:
180 -- Checks if the system person type has been changed between the
181 -- validation start date and validation end date.
182 --
183 -- Pre-conditions:
184 -- None
185 --
186 -- In Arguments:
187 -- p_person_id
188 -- p_validation_start_date
189 -- p_validation_end_date
190 -- p_datetrack_mode
191 -- p_effective_date
192 --
193 -- Post Success:
194 -- If no system person type changes exist between the validation start
195 -- date and validation end date then processing continues.
196 --
197 -- Post Failure:
198 -- If the system person type changes in the future an application error
199 -- is raised and processing is terminated.
203 --
200 --
201 -- Access Status:
202 -- Internal Development Use Only.
204 procedure chk_system_pers_type
205 (p_person_id in number
206 ,p_validation_start_date in date
207 ,p_validation_end_date in date
208 ,p_datetrack_mode in varchar2
209 ,p_effective_date in date
210 );
211 --
212 -- ----------------------------------------------------------------------------
213 -- |----------------------------< CHK_PERSON_TYPE >---------------------------|
214 -- ----------------------------------------------------------------------------
215 -- {Start Of Comments}
216 --
217 -- Description:
218 -- This procedure first checks that the specified business group exists.
219 -- Then, if a person type has been specified, it checks that it is valid,
220 -- active, in the correct business group and for the correct system person
221 -- type. If a person type has not been specified, then this procedure will
222 -- determine the default for the current business group and system person
223 -- type.
224 --
225 -- The procedure is called from various Person-related business processes.
226 --
227 -- Pre Conditions:
228 -- p_business_group_id is known to be an existing business group.
229 -- p_expected_sys_type is either APL, APL_EX_APL, EMP, EMP_APL, EX_APL,
230 -- EX_EMP, EX_EMP_APL or OTHER.
231 --
232 -- In Arguments:
233 -- p_person_type_id
234 -- p_business_group_id
235 -- p_expected_sys_type
236 --
237 -- Post Success:
238 -- If p_person_type_id is null it will be set to the default type for
239 -- p_expected_sys_type in the business group.
240 --
241 -- Post Failure:
242 -- Raises an application error if any of the following cases are found:
243 -- a) p_person_type_id does not exist.
244 -- b) p_person_type_id does exist but not in the same business group.
245 -- c) p_person_type_id does exist but the corresponding system person
246 -- type is not p_expected_sys_type.
247 -- d) p_person_type_id is not active.
248 --
249 -- Access Status:
250 -- Internal Development Use Only.
251 --
252 -- {End Of Comments}
253 --
254 procedure chk_person_type
255 (p_person_type_id in out nocopy number
256 ,p_business_group_id in number
257 ,p_expected_sys_type in varchar2);
258 --
259 -- -------------------------------------------------------------------------
260 -- ------------------------< chk_start_date >------------------------------|
261 -- -------------------------------------------------------------------------
262 --
263 -- Description:
264 -- Checks that a start date value is valid
265 --
266 -- Pre-conditions:
267 --
268 -- In Arguments:
269 -- p_person_id
270 -- p_start_date
271 -- p_effective_date
272 -- p_object_version_number
273 --
274 -- Post Success:
275 -- On insert if a start date is the same as effective date then
276 -- processing continues
277 --
278 -- On update if a start date is the same as the minimum effective start date
279 -- then processing continues
280 --
281 -- Post Failure:
282 -- On insert if a start date is not the same as the effective
283 -- date then an application error will be raised and processing is
284 -- terminated
285 --
286 -- On update if a start date is not the same as the minimum effective start
287 -- date then an application error will be raised and processing is
288 -- terminated
289 --
290 -- Access Status:
291 -- Internal Table Handler Use Only.
292 --
293 procedure chk_start_date
294 (p_person_id in per_all_people_f.person_id%TYPE
295 ,p_start_date in date
296 ,p_effective_date in date
297 ,p_object_version_number in per_all_people_f.object_version_number%TYPE);
298 --
299 procedure chk_party_id
300 (p_person_id in per_all_people_f.person_id%TYPE
301 ,p_party_id in number
302 ,p_effective_date in date
303 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
304 );
305 -- ---------------------------------------------------------------------------
306 -- |--------------------< chk_orig_and_start_dates >----------------------|
307 -- -------------------------------------------------------------------------
308 --
309 --
310 -- Description:
311 -- - Validates that for person type of 'EMP','EMP_APL','EX_EMP' or
312 -- 'EX_EMP_APL' the original date of hire is the same or earlier
313 -- than the earliest per_periods_of_service start date. For any
314 -- other person type a warning is raised if an original date of
315 -- hire is entered.
316 --
317 -- Pre-conditions:
318 -- Valid p_person_id.
319 --
320 -- In Arguments:
321 -- p_person_id
322 -- p_person_type_id
323 -- p_business_group_id
324 -- p_original_date_of_hire
325 -- p_effective_date
326 -- p_start_date
327 -- p_object_version_number
328 --
329 -- Out Arguments:
330 -- p_orig_hire_warning
331 --
332 -- Post Success:
333 -- Processing continues if:
334 -- - person type is 'EMP','EMP_APL','EX_EMP' or 'EX_EMP_APL' and
335 -- original_date_of_hire is on or before the start_date
339 -- and original_date_of_hire is entered and the warning
336 -- - person type is not 'EMP','EMP_APL','EX_EMP' or 'EX_EMP_APL'
337 -- and original_date_of_hire is not entered
338 -- - person type is not 'EMP','EMP_APL','EX_EMP' or 'EX_EMP_APL'
340 -- message is acknowledged.
341 --
342 -- Post Failure:
343 -- An application error is raised and processing is terminated if:
344 -- - person type is 'EMP','EMP_APL','EX_EMP' or 'EX_EMP_APL' and
345 -- the original_date_of_hire is later than the start date.
346 --
347 -- Access Status:
348 --
349 -- Internal Table Handler Use Only.
350 --
351 procedure chk_orig_and_start_dates
352 (p_person_id in per_all_people_f.person_id%TYPE
353 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
354 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
355 ,p_original_date_of_hire in per_all_people_f.original_date_of_hire%TYPE
356 ,p_start_date in per_all_people_f.start_date%TYPE
357 ,p_effective_date in date
358 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
359 ,p_orig_hire_warning out nocopy boolean
360 );
361 --
362 -- ----------------------------------------------------------------------------
363 -- |------------------------< set_security_group_id >-------------------------|
364 -- ----------------------------------------------------------------------------
365 -- {Start Of Comments}
366 --
367 -- Description:
368 -- This is a stub routine to provide dual maintainance
369 -- for release 11.5 and will Set the security_group_id in CLIENT_INFO for the person's business
370 -- group context.
371 --
372 -- Prerequisites:
373 -- None,
374 --
375 -- In Parameters:
376 -- This is a parameterless procedure for release 11.0
377 -- for release 11.5
378 -- Name Reqd Type Description
379 -- p_person_id Yes Number Person to use for
380 -- deriving the security group
381 -- context.
382 --
383 -- Post Success:
384 -- for release 11.5 The security_group_id will be set in CLIENT_INFO.
385 --
386 -- Post Failure:
387 -- for release 11.5 An error is raised if the person does not exist.
388 --
389 -- Access Status:
390 -- Internal Development Use Only.
391 --
392 -- {End Of Comments}
393 --
394 -- ----------------------------------------------------------------------------
395 procedure set_security_group_id
396 (
397 p_person_id in per_all_people_f.person_id%TYPE
398 ,p_associated_column1 in varchar2 default null
399 );
400 --
401 --
402 -- ---------------------------------------------------------------------------
403 -- |------------------------< chk_date_of_birth >--------------------------|
404 -- ---------------------------------------------------------------------------
405 --
406 -- Description:
407 -- Checks that a date of birth value is valid
408 --
409 -- Pre-conditions:
410 -- None
411 --
412 -- In Arguments:
413 -- p_person_id
414 -- p_person_type_id
415 -- p_business_group_id
416 -- p_start_date
417 -- p_date_of_birth
418 -- p_effective_date
419 -- p_object_version_number
420 --
421 -- Out Arguments
422 -- p_dob_null_warning
423 --
424 -- Post Success:
425 -- If a date of birth <= the start date then
426 -- processing continues
427 --
428 -- If date of birth is null on insert when system person type is 'EMP' then
429 -- a warning is flagged and processing continues
430 --
431 -- If the persons age is between the minimum and maximum ages defined
432 -- for the business group then
433 -- processing continues
434 --
435 -- Post Failure:
436 -- If a date of birth > the start date then
437 -- an application error will be raised and processing is terminated
438 --
439 -- If the persons age is not between the minimum and maximum ages defined
440 -- for the business group then
441 -- an application error will be raised and processing is terminated
442 --
443 -- If the person type is EMP, and any assignment has its payroll component
444 -- set, then an application error will be raised and processing terminated
445 -- if the date of birth is updated to null.
446 --
447 -- Access Status:
448 -- Internal Development Use Only.
449 --
450 procedure chk_date_of_birth
451 (p_person_id in per_all_people_f.person_id%TYPE
452 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
453 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
454 ,p_start_date in date
455 ,p_date_of_birth in date
456 ,p_dob_null_warning out nocopy boolean
457 ,p_effective_date in date
458 ,p_validation_start_date in date
459 ,p_validation_end_date in date
460 ,p_object_version_number in per_all_people_f.object_version_number%TYPE);
461 --
462 --start for bug 6241572
463 --
464 -- ---------------------------------------------------------------------------
465 -- |-------------------< chk_national_identifier >-------------------------|
466 -- ---------------------------------------------------------------------------
467 --
468 -- Description:
469 -- Calls process hr_person.validate_national_identifier
470 --
471 -- Pre-conditions:
472 -- Business Group id must be valid
473 --
474 -- In Arguments:
478 -- p_date_of_birth
475 -- p_person_id
476 -- p_business_group_id
477 -- p_national_identifier
479 -- p_sex
480 -- p_effective_date
481 -- p_object_version_number
482 -- p_legislation_code
483 -- p_person_type_id
484 --
485 --for bug 6241572
486 -- p_region_of_birth
487 -- p_country_of_birth
488 -- p_nationality
489
490 -- Post Success:
491 -- If the national identifier is valid then
492 -- processing continues
493 --
494 -- Post Failure:
495 -- If the national identifier is not valid then
496 -- an application error is raised and processing is terminated
497 --
498 -- Access Status:
499 -- Internal Development Use Only.
500 --
501 procedure chk_national_identifier
502 (p_person_id in per_all_people_f.person_id%TYPE
503 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
504 ,p_national_identifier in per_all_people_f.national_identifier%TYPE
505 ,p_date_of_birth in date
509 ,p_legislation_code in per_business_groups.legislation_code%TYPE
506 ,p_sex in per_all_people_f.sex%TYPE
507 ,p_effective_date in date
508 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
510 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
511 ,p_region_of_birth in per_all_people_f.region_of_birth%TYPE default NULL
512 ,p_country_of_birth in per_all_people_f.country_of_birth%TYPE default NULL
513 ,p_nationality in per_all_people_f.nationality%TYPE);
514
515 --end for bug 6241572
516 --
517 --
518 -- ---------------------------------------------------------------------------
519 -- |-------------------< chk_national_identifier >-------------------------|
520 -- ---------------------------------------------------------------------------
521 --
522 -- Description:
523 -- Calls process hr_person.validate_national_identifier
524 --
525 -- Pre-conditions:
526 -- Business Group id must be valid
527 --
528 -- In Arguments:
529 -- p_person_id
530 -- p_business_group_id
531 -- p_national_identifier
532 -- p_date_of_birth
533 -- p_sex
534 -- p_effective_date
535 -- p_object_version_number
536 -- p_legislation_code
537 -- p_person_type_id - Bug 1642707
538 --
539 -- Post Success:
540 -- If the national identifier is valid then
541 -- processing continues
542 --
543 -- Post Failure:
544 -- If the national identifier is not valid then
545 -- an application error is raised and processing is terminated
546 --
547 -- Access Status:
548 -- Internal Development Use Only.
549 --
550 procedure chk_national_identifier
551 (p_person_id in per_all_people_f.person_id%TYPE
552 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
553 ,p_national_identifier in per_all_people_f.national_identifier%TYPE
554 ,p_date_of_birth in date
555 ,p_sex in per_all_people_f.sex%TYPE
556 ,p_effective_date in date
557 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
558 ,p_legislation_code in per_business_groups.legislation_code%TYPE
559 ,p_person_type_id in per_all_people_f.person_type_id%TYPE);
560 --
561 --
562 -- ---------------------------------------------------------------------------
563 -- |-----------------------< chk_employee_number >-------------------------|
564 -- ---------------------------------------------------------------------------
565 --
566 -- Description:
567 -- Checks that an employee value is valid
568 --
569 -- Pre-conditions:
570 -- p_person_type_id must be valid
571 -- p_business_group_id must be valid for p_person_id
572 -- p_national_identifier must be valid
573 --
574 -- In Arguments:
575 -- p_person_id
576 -- p_business_group_id
577 -- p_person_type_id
578 -- p_employee_number
579 -- p_national_identifier
580 -- p_effective_date
581 -- p_object_version_number
582 --
583 -- Post Success:
584 -- If system person type is 'EMP', 'EX_EMP', 'EMP_APL' or 'EX_EMP_APL' then
585 -- employee number is defined based on employee number generation method as
586 -- follows :
587 --
588 -- If employee number is not null and employee number generation method
589 -- is 'Manual' then processing continues.
590 -- If employee number is null and employee number generation method is
591 -- 'Automatic' then employee number is generated and processing
592 -- continues.
593 -- If employee number is null and national identifier is not null and
594 -- the employee number generation method is 'National identifier' then
595 -- employee number is set to national identifier and processing
596 -- continues.
597 --
601 -- Post Failure:
598 -- If the employee number is unique within the business group then
599 -- processing continues
600 --
602 -- If system person type is 'EMP', 'EX_EMP', 'EMP_APL' or 'EX_EMP_APL' then
603 -- If employee number is null then
604 -- an application error will be raised and processing is terminated
605 --
606 -- If system person type is anything other than 'EMP', 'EX_EMP', 'EMP_APL'
607 -- or 'EX_EMP_APL' then
608 -- If employee number is not null then
609 -- an application error will be raised and processing is terminated
610 --
611 -- If the employee number is not unique within the business group then
612 -- an application error will be raised and processing is terminated
613 --
614 -- Access Status:
615 -- Internal Development Use Only.
616 --
617 procedure chk_employee_number
618 (p_person_id in per_all_people_f.person_id%TYPE
619 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
620 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
621 ,p_employee_number in out nocopy per_all_people_f.employee_number%TYPE
622 ,p_national_identifier in per_all_people_f.national_identifier%TYPE
623 ,p_effective_date in date
624 ,p_object_version_number in per_all_people_f.object_version_number%TYPE);
625 --
626 procedure chk_employee_number
627 (p_person_id in per_all_people_f.person_id%TYPE
628 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
629 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
630 ,p_employee_number in out nocopy per_all_people_f.employee_number%TYPE
631 ,p_national_identifier in per_all_people_f.national_identifier%TYPE
632 ,p_effective_date in date
633 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
634 ,p_party_id in per_all_people_f.party_id%TYPE
635 ,p_date_of_birth in per_all_people_f.date_of_birth%TYPE
636 ,p_start_date in per_periods_of_service.date_start%TYPE);
637 --
638 --
639 -- ---------------------------------------------------------------------------
640 -- |-----------------------< chk_npw_number >------------------------------|
641 -- ---------------------------------------------------------------------------
642 --
643 -- Description:
644 -- Checks that cwk number value is valid
645 --
646 -- Pre-conditions:
647 -- p_person_type_id must be valid
648 -- p_business_group_id must be valid for p_person_id
649 -- p_national_identifier must be valid
650 --
651 -- In Arguments:
652 -- p_person_id
653 -- p_business_group_id
654 -- p_current_npw_flag
655 -- p_npw_number
656 -- p_national_identifier
657 -- p_effective_date
658 -- p_object_version_number
659 --
660 -- Post Success:
661 -- If current_npw_flag = Y or there exists a previous CWK record on PTU,
662 -- npw number is defined based on cwk number generation method as
663 -- follows :
664 --
665 -- If npw number is not null and cwk number generation method
666 -- is 'Manual' then processing continues.
667 -- If npw number is null and cwk number generation method is
668 -- 'Automatic' then npw number is generated and processing
669 -- continues.
670 -- If npw number is null and national identifier is not null and
671 -- the cwk number generation method is 'National identifier' then
672 -- npw number is set to national identifier and processing
673 -- continues.
674 --
675 -- If the npw number is unique within the business group then
676 -- processing continues
677 --
678 -- Post Failure:
679 -- If current_npw_flag = 'Y' or there exists a previous CWK record on PTU
680 -- If npw number is null then
681 -- an application error will be raised and processing is terminated
682 --
683 -- If current_npw_flag = N or (is null and no previous CWK record exists on PTU)
684 -- If npw number is not null then
685 -- an application error will be raised and processing is terminated
686 --
687 -- If the npw number is not unique within the business group then
688 -- an application error will be raised and processing is terminated
689 --
690 -- Access Status:
691 -- Internal Development Use Only.
692 --
693 procedure chk_npw_number
694 (p_person_id in per_all_people_f.person_id%TYPE
695 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
696 ,p_current_npw_flag in per_all_people_f.current_npw_flag%TYPE
697 ,p_npw_number in out nocopy per_all_people_f.npw_number%TYPE
698 ,p_national_identifier in per_all_people_f.national_identifier%TYPE
699 ,p_effective_date in date
700 ,p_object_version_number in per_all_people_f.object_version_number%TYPE);
701 --
702 procedure chk_npw_number
703 (p_person_id in per_all_people_f.person_id%TYPE
704 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
705 ,p_current_npw_flag in per_all_people_f.current_npw_flag%TYPE
706 ,p_npw_number in out nocopy per_all_people_f.npw_number%TYPE
707 ,p_national_identifier in per_all_people_f.national_identifier%TYPE
708 ,p_effective_date in date
709 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
710 ,p_party_id in per_all_people_f.party_id%TYPE
711 ,p_date_of_birth in per_all_people_f.date_of_birth%TYPE
712 ,p_start_date in date);
713 --
717 --
714 -- ---------------------------------------------------------------------------
715 -- |-----------------------< chk_sex_title >-------------------------------|
716 -- ---------------------------------------------------------------------------
718 -- Description:
719 -- - Validates that the sex exists as a lookup code on
720 -- HR_LOOKUPS for the lookup type 'SEX' with an enabled
721 -- flag set to 'Y' and the effective start date of the person between
722 -- start date active and end date active on HR_LOOKUPS.
723 -- - Validates that the title exists as a lookup code on
724 -- HR_LOOKUPS for the lookup type 'TITLE' with an enabled
725 -- flag set to 'Y' and the effective start date of the person between
726 -- start date active and end date active on HR_LOOKUPS.
727 --
728 -- Pre-conditions:
729 -- A valid person type
730 --
731 -- In Arguments:
732 -- p_person_id
733 -- p_business_group_id
734 -- p_person_type_id
735 -- p_title
736 -- p_sex
737 -- p_effective_date
738 -- p_validation_start_date
739 -- p_validation_end_date
740 -- p_object_version_number
741 --
742 -- Post Success:
743 -- Processing continues if:
744 -- - a sex exists as a lookup code in HR_LOOKUPS for the lookup type
745 -- 'SEX' where the enabled flag is 'Y' and the effective start
746 -- date of the person is between start date active and end date
747 -- active on HR_LOOKUPS.
748 -- - a title exists as a lookup code in HR_LOOKUPS for the lookup type
749 -- 'TITLE' where the enabled flag is 'Y' and the effective start
750 -- date of the person is between start date active and end date
751 -- active on HR_LOOKUPS.
752 -- - a sex value is 'M' and the title value is not 'MISS','MRS.',
753 -- 'MS.'
754 -- - a sex value is 'F' and the title value is 'MR'.
755 -- - the related system person type is 'EMP' and a sex value is
756 -- set.
757 --
758 -- Post Failure:
759 -- An application error is raised and processing is terminated if:
760 -- - a sex does'nt exist as a lookup code in HR_LOOKUPS for the lookup
761 -- type 'SEX' where the enabled flag is 'Y' and the effective start
762 -- date of the person is between start date active and end date
763 -- active on HR_LOOKUPS.
764 -- - a title does'nt exist as a lookup code in HR_LOOKUPS for the lookup
765 -- type 'TITLE' where the enabled flag is 'Y' and the effective start
766 -- date of the person is between start date active and end date
767 -- active on HR_LOOKUPS.
768 -- - a sex value is 'M' and the title value is 'MISS','MRS.', 'MS.'
769 -- - a sex value is 'F' and the title value is 'MR.'
770 -- - the related system person type is 'EMP' and a sex value is not
771 -- set.
772 --
773 -- Access Status:
774 -- Internal Developer Use Only.
775 --
776 procedure chk_sex_title
777 (p_person_id in per_all_people_f.person_id%TYPE
778 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
779 ,p_person_type_id in per_all_people_f.person_type_id%TYPe
780 ,p_title in per_all_people_f.title%TYPE
781 ,p_sex in per_all_people_f.sex%TYPE
782 ,p_effective_date in date
783 ,p_validation_start_date in date
784 ,p_validation_end_date in date
785 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
786 );
787 --
788 -- ---------------------------------------------------------------------------
789 -- |-----------------------< chk_applicant_number >------------------------|
790 -- ---------------------------------------------------------------------------
791 --
792 -- Description:
793 -- Checks that applicant number is valid on insert and delete based on
794 -- applicant number generation method.
795 --
796 -- Some specific tests are performed in this procedure and then if all
797 -- is still okay the hr_person.generate_number routine is called to
798 -- finish of the validation/generation process.
799 --
800 -- Pre-conditions:
801 -- Valid person_id
802 -- Valid current_applicant_flag
803 -- Valid current_employee_flag
804 -- Valid business_group_id
805 -- Valid person_type_id
806 --
807 -- In Arguments:
808 -- p_person_id
809 -- p_applicant_number
810 -- p_business_group_id
811 -- p_current_applicant
812 -- p_effective_date
813 -- p_object_version_number
814 --
815 -- Post Success:
816 --
817 -- If the following conditions apply then an applicant_number is generated
818 -- and processing continues :
819 --
820 -- a) Applicant number must be not null if system person type is 'APL',
821 -- 'APL_EX_APL','EMP_APL','EX_EMP_APL'.
822 --
823 -- b) Applicant number must not be modified to null if the system person
824 -- type is 'EMP' or 'EX_EMP' and the applicant number is not null
825 --
826 -- c) Applicant number must be null if the system person type is 'EMP' and
827 -- no previous changes to system person type exist
828 --
829 -- d) Applicant number must be null if the system person type is 'OTHER'
830 --
831 -- e) Applicant number is mandatory in Manual generation mode
832 --
833 -- f) Number generation mode of associated business group id can only
834 -- be 'A' or 'M'
835 --
836 -- g) Applicant number can only be updated in generation mode 'M'
837 --
838 -- h) Applicant number must be unique within the business group
839 --
840 -- Post Failure:
841 --
845 --
842 -- If the following conditions apply then processing fails :
843 --
844 -- a) Applicant number is not null, system person type is 'OTHER'
846 -- b) Applicant number has changed from not null to null and the system
847 -- person type is 'EMP' or 'EX_EMP'
848 --
849 -- c) Applicant number updated when generation mode is 'A'
850 --
851 -- d) Applicant number is not null, system person type is 'EMP' and
852 -- no historic changes in system person type exist for this person.
853 -- i.e they are 'EMP' now and have always been an 'EMP'.
854 --
855 -- e) Applicant number is null when generation mode is 'M'
856 --
857 -- Access Status:
858 -- Internal Table Handler Use Only.
859 --
860 procedure chk_applicant_number
861 (p_person_id in per_all_people_f.person_id%TYPE
862 ,p_applicant_number in out nocopy per_all_people_f.applicant_number%TYPE
863 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
864 ,p_current_applicant in per_all_people_f.current_applicant_flag%TYPE
865 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
866 ,p_effective_date in date
867 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
868 );
869 --
870 procedure chk_applicant_number
871 (p_person_id in per_all_people_f.person_id%TYPE
872 ,p_applicant_number in out nocopy per_all_people_f.applicant_number%TYPE
873 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
874 ,p_current_applicant in per_all_people_f.current_applicant_flag%TYPE
875 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
876 ,p_effective_date in date
877 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
878 ,p_party_id in per_all_people_f.party_id%TYPE
879 ,p_date_of_birth in per_all_people_f.date_of_birth%TYPE
880 ,p_start_date in date
881 );
882 --
883 --
884 -- ---------------------------------------------------------------------------
885 -- |--------------------< chk_date_emp_data_verified >---------------------|
886 -- ---------------------------------------------------------------------------
887 --
888 -- Description:
889 -- Checks that date employee data verified is always null on insert. On
890 -- update date employee data verified cannot be set to a not null value.
891 -- However, a not null value for date employee data verified is permissable
892 -- when other attributes apart from date employee data verified are being
893 -- set.
894 --
895 -- Pre-conditions:
896 -- Valid person_id
897 --
898 -- In Arguments:
899 -- p_person_id
900 -- p_date_employee_data_verified
901 -- p_effective_start_date
902 -- p_object_version_number
903 --
904 -- Post Success:
905 -- If date_employee_data_verified is after the effective_start_date
906 -- of the person record then process succeeds
907 --
908 -- Post Failure:
909 -- If date_employee_data_verified is before the effective_start_date of
910 -- the person record an application error will be raised and processing
911 -- is terminated
912 --
913 -- Access Status:
914 -- Internal Developer Use Only.
915 --
916 procedure chk_date_emp_data_verified
917 (p_person_id in per_all_people_f.person_id%TYPE
918 ,p_date_employee_data_verified in
919 per_all_people_f.date_employee_data_verified%TYPE
920 ,p_effective_start_date in date
921 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
922 );
923 --
924 --
925 -- ---------------------------------------------------------------------------
926 -- |-----------------------< chk_vendor_id >-------------------------------|
927 -- ---------------------------------------------------------------------------
928 --
929 -- Description:
930 -- Checks that vendor id is valid.
931 --
932 -- Pre-conditions:
933 -- Valid person_id
934 --
935 -- In Arguments:
936 -- p_person_id
937 -- p_vendor_id
938 -- p_effective_date
939 -- p_object_version_number
940 --
941 -- Post Success:
942 -- If vendor id is null and system person type is not one of 'EMP',
943 -- 'EMP_APL','EX_EMP',EX_EMP_APL then process succeeds.
944 -- If vendor id is not null, system person type is one of 'EMP','EMP_APL',
945 -- 'EX_EMP','EX_EMP_APL' and vendor id exists in lookup table then process
946 -- succeeds.
947 --
948 -- Post Failure:
949 -- If vendor id is not null and system person type is not one of 'EMP',
950 -- 'EMP_APL','EX_EMP',EX_EMP_APL then process is terminated.
951 -- If vendor id is not null, system person type is one of 'EMP','EMP_APL',
952 -- 'EX_EMP','EX_EMP_APL' and vendor id does not exists in lookup table then
953 -- process is terminated.
954 --
955 -- Access Status:
956 -- Internal Table Handler Use Only.
957 --
958 procedure chk_vendor_id
959 (p_person_id in per_all_people_f.person_id%TYPE
960 ,p_vendor_id in per_all_people_f.vendor_id%TYPE
961 ,p_person_type_id in per_all_people_f.person_type_id%TYPE
962 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
963 ,p_effective_date in date
964 ,p_object_version_number in per_all_people_f.object_version_number%TYPE
965 );
966 --
967 end per_per_bus;
[Home] [Help]