[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE12_PKG
Source
1 PACKAGE BODY PER_PEOPLE12_PKG AS
2 /* $Header: peper12t.pkb 120.11.12010000.2 2008/08/06 09:23:46 ubhat ship $ */
3 --
4 --
5 -- Bug fix 2824664
6 FUNCTION future_pactid_exists(p_person_id IN number, p_effective_date IN date)
7 RETURN boolean IS
8
9 l_action_chk VARCHAR2(1) := 'N';
10 begin
11 SELECT 'Y'
12 INTO l_action_chk
13 FROM sys.dual
14 WHERE exists
15 (SELECT null
16 FROM pay_payroll_actions pac,
17 pay_assignment_actions act
18 WHERE act.assignment_id =
19 (select assignment_id
20 from per_all_assignments_f
21 where person_id = p_person_id
22 and p_effective_date between
23 effective_start_date and effective_end_date
24 and primary_flag = 'Y'
25 and assignment_type = 'E')
26 AND pac.payroll_action_id = act.payroll_action_id
27 AND pac.action_type not in ('X','BEE') -- Bug 2898318. Exclude BEE
28 -- actions
29 AND pac.effective_date >= p_effective_date);
30 return(TRUE);
31 exception
32 when NO_DATA_FOUND then return(FALSE);
33
34 end future_pactid_exists;
35 --
36 -- Start new code for bug #2664569: Overloaded procedure. ********
37 procedure update_row1(p_rowid VARCHAR2
38 ,p_person_id NUMBER
39 ,p_effective_start_date DATE
40 ,p_effective_end_date DATE
41 ,p_business_group_id NUMBER
42 ,p_person_type_id NUMBER
43 ,p_last_name VARCHAR2
44 ,p_start_date DATE
45 ,p_applicant_number IN OUT NOCOPY VARCHAR2
46 ,p_comment_id NUMBER
47 ,p_current_applicant_flag in VARCHAR2
48 ,p_current_emp_or_apl_flag VARCHAR2
49 ,p_current_employee_flag VARCHAR2
50 ,p_date_employee_data_verified DATE
51 ,p_date_of_birth DATE
52 ,p_email_address VARCHAR2
53 ,p_employee_number IN OUT NOCOPY VARCHAR2
54 ,p_expense_check_send_to_addr VARCHAR2
55 ,p_first_name VARCHAR2
56 ,p_full_name VARCHAR2
57 ,p_known_as VARCHAR2
58 ,p_marital_status VARCHAR2
59 ,p_middle_names VARCHAR2
60 ,p_nationality VARCHAR2
61 ,p_national_identifier VARCHAR2
62 ,p_previous_last_name VARCHAR2
63 ,p_registered_disabled_flag VARCHAR2
64 ,p_sex VARCHAR2
65 ,p_title VARCHAR2
66 ,p_suffix VARCHAR2
67 ,p_vendor_id NUMBER
68 ,p_work_telephone VARCHAR2
69 ,p_request_id NUMBER
70 ,p_program_application_id NUMBER
71 ,p_program_id NUMBER
72 ,p_program_update_date DATE
73 ,p_a_cat VARCHAR2
74 ,p_a1 VARCHAR2
75 ,p_a2 VARCHAR2
76 ,p_a3 VARCHAR2
77 ,p_a4 VARCHAR2
78 ,p_a5 VARCHAR2
79 ,p_a6 VARCHAR2
80 ,p_a7 VARCHAR2
81 ,p_a8 VARCHAR2
82 ,p_a9 VARCHAR2
83 ,p_a10 VARCHAR2
84 ,p_a11 VARCHAR2
85 ,p_a12 VARCHAR2
86 ,p_a13 VARCHAR2
87 ,p_a14 VARCHAR2
88 ,p_a15 VARCHAR2
89 ,p_a16 VARCHAR2
90 ,p_a17 VARCHAR2
91 ,p_a18 VARCHAR2
92 ,p_a19 VARCHAR2
93 ,p_a20 VARCHAR2
94 ,p_a21 VARCHAR2
95 ,p_a22 VARCHAR2
96 ,p_a23 VARCHAR2
97 ,p_a24 VARCHAR2
98 ,p_a25 VARCHAR2
99 ,p_a26 VARCHAR2
100 ,p_a27 VARCHAR2
101 ,p_a28 VARCHAR2
102 ,p_a29 VARCHAR2
103 ,p_a30 VARCHAR2
104 ,p_last_update_date DATE
105 ,p_last_updated_by NUMBER
106 ,p_last_update_login NUMBER
107 ,p_created_by NUMBER
108 ,p_creation_date DATE
109 ,p_i_cat VARCHAR2
110 ,p_i1 VARCHAR2
111 ,p_i2 VARCHAR2
112 ,p_i3 VARCHAR2
113 ,p_i4 VARCHAR2
114 ,p_i5 VARCHAR2
115 ,p_i6 VARCHAR2
116 ,p_i7 VARCHAR2
117 ,p_i8 VARCHAR2
118 ,p_i9 VARCHAR2
119 ,p_i10 VARCHAR2
120 ,p_i11 VARCHAR2
121 ,p_i12 VARCHAR2
122 ,p_i13 VARCHAR2
123 ,p_i14 VARCHAR2
124 ,p_i15 VARCHAR2
125 ,p_i16 VARCHAR2
126 ,p_i17 VARCHAR2
127 ,p_i18 VARCHAR2
128 ,p_i19 VARCHAR2
129 ,p_i20 VARCHAR2
130 ,p_i21 VARCHAR2
131 ,p_i22 VARCHAR2
132 ,p_i23 VARCHAR2
133 ,p_i24 VARCHAR2
134 ,p_i25 VARCHAR2
135 ,p_i26 VARCHAR2
136 ,p_i27 VARCHAR2
137 ,p_i28 VARCHAR2
138 ,p_i29 VARCHAR2
139 ,p_i30 VARCHAR2
140 ,p_app_ass_status_type_id NUMBER
141 ,p_emp_ass_status_type_id NUMBER
142 ,p_system_person_type in VARCHAR2
143 ,p_s_system_person_type VARCHAR2
144 ,p_hire_date DATE
145 ,p_s_hire_date DATE
146 ,p_s_date_of_birth DATE
147 ,p_status in out nocopy VARCHAR2
148 ,p_new_primary_id in out nocopy NUMBER
149 ,p_update_primary in out nocopy VARCHAR2
150 ,p_legislation_code VARCHAR2
151 ,p_vacancy_id IN OUT NOCOPY NUMBER
152 ,p_session_date date
153 ,p_end_of_time date
154 ,p_work_schedule VARCHAR2
155 ,p_correspondence_language VARCHAR2
156 ,p_student_status VARCHAR2
157 ,p_fte_capacity NUMBER
158 ,p_on_military_service VARCHAR2
159 ,p_second_passport_exists VARCHAR2
160 ,p_background_check_status VARCHAR2
161 ,p_background_date_check DATE
162 ,p_blood_type VARCHAR2
163 ,p_last_medical_test_date DATE
164 ,p_last_medical_test_by VARCHAR2
165 ,p_rehire_recommendation VARCHAR2
166 ,p_rehire_reason VARCHAR2
167 ,p_resume_exists VARCHAR2
168 ,p_resume_last_updated DATE
169 ,p_office_number VARCHAR2
170 ,p_internal_location VARCHAR2
171 ,p_mailstop VARCHAR2
172 ,p_honors VARCHAR2
173 ,p_pre_name_adjunct VARCHAR2
174 ,p_hold_applicant_date_until DATE
175 ,p_benefit_group_id NUMBER
176 ,p_receipt_of_death_cert_date DATE
177 ,p_coord_ben_med_pln_no VARCHAR2
178 ,p_coord_ben_no_cvg_flag VARCHAR2
179 ,p_uses_tobacco_flag VARCHAR2
180 ,p_dpdnt_adoption_date DATE
181 ,p_dpdnt_vlntry_svce_flag VARCHAR2
182 ,p_date_of_death DATE
183 ,p_original_date_of_hire DATE
184 ,p_adjusted_svc_date DATE
185 ,p_s_adjusted_svc_date DATE
186 ,p_town_of_birth VARCHAR2
187 ,p_region_of_birth VARCHAR2
188 ,p_country_of_birth VARCHAR2
189 ,p_global_person_id VARCHAR2
190 ,p_npw_number IN OUT NOCOPY VARCHAR2
191 ,p_current_npw_flag VARCHAR2
192
193 ) is
194 l_sys_per_type varchar2(150);
195 l_current_apl varchar2(150);
196 l_table HR_EMPLOYEE_APPLICANT_API.t_ApplTable;
197 begin
198 l_sys_per_type := p_system_person_type;
199 l_current_apl := p_current_applicant_flag;
200
201 update_row1(p_rowid
202 ,p_person_id
203 ,p_effective_start_date
204 ,p_effective_end_date
205 ,p_business_group_id
206 ,p_person_type_id
207 ,p_last_name
208 ,p_start_date
209 ,p_applicant_number
210 ,p_comment_id
211 -- # 2264569:
212 ,l_current_apl
213 --
214 ,p_current_emp_or_apl_flag
215 ,p_current_employee_flag
216 ,p_date_employee_data_verified
217 ,p_date_of_birth
218 ,p_email_address
219 ,p_employee_number
220 ,p_expense_check_send_to_addr
221 ,p_first_name
222 ,p_full_name
223 ,p_known_as
224 ,p_marital_status
225 ,p_middle_names
226 ,p_nationality
227 ,p_national_identifier
228 ,p_previous_last_name
229 ,p_registered_disabled_flag
230 ,p_sex
231 ,p_title
232 ,p_suffix
233 ,p_vendor_id
234 ,p_work_telephone
235 ,p_request_id
236 ,p_program_application_id
237 ,p_program_id
238 ,p_program_update_date
239 ,p_a_cat
240 ,p_a1
241 ,p_a2
242 ,p_a3
243 ,p_a4
244 ,p_a5
245 ,p_a6
246 ,p_a7
247 ,p_a8
248 ,p_a9
249 ,p_a10
250 ,p_a11
251 ,p_a12
252 ,p_a13
253 ,p_a14
254 ,p_a15
255 ,p_a16
256 ,p_a17
257 ,p_a18
258 ,p_a19
259 ,p_a20
260 ,p_a21
261 ,p_a22
262 ,p_a23
263 ,p_a24
264 ,p_a25
265 ,p_a26
266 ,p_a27
267 ,p_a28
268 ,p_a29
269 ,p_a30
270 ,p_last_update_date
271 ,p_last_updated_by
272 ,p_last_update_login
273 ,p_created_by
274 ,p_creation_date
275 ,p_i_cat
276 ,p_i1
277 ,p_i2
278 ,p_i3
279 ,p_i4
280 ,p_i5
281 ,p_i6
282 ,p_i7
283 ,p_i8
284 ,p_i9
285 ,p_i10
286 ,p_i11
287 ,p_i12
288 ,p_i13
289 ,p_i14
290 ,p_i15
291 ,p_i16
292 ,p_i17
293 ,p_i18
294 ,p_i19
295 ,p_i20
296 ,p_i21
297 ,p_i22
298 ,p_i23
299 ,p_i24
300 ,p_i25
301 ,p_i26
302 ,p_i27
303 ,p_i28
304 ,p_i29
305 ,p_i30
306 ,p_app_ass_status_type_id
307 ,p_emp_ass_status_type_id
308 -- # 2264569
309 ,l_sys_per_type
310 ,p_s_system_person_type
311 ,p_hire_date
312 ,p_s_hire_date
313 ,p_s_date_of_birth
314 ,p_status
315 ,p_new_primary_id
316 ,p_update_primary
317 ,p_legislation_code
318 ,p_vacancy_id
319 ,p_session_date
320 ,p_end_of_time
321 ,p_work_schedule
322 ,p_correspondence_language
323 ,p_student_status
324 ,p_fte_capacity
325 ,p_on_military_service
326 ,p_second_passport_exists
327 ,p_background_check_status
328 ,p_background_date_check
329 ,p_blood_type
330 ,p_last_medical_test_date
331 ,p_last_medical_test_by
332 ,p_rehire_recommendation
333 ,p_rehire_reason
334 ,p_resume_exists
335 ,p_resume_last_updated
336 ,p_office_number
337 ,p_internal_location
338 ,p_mailstop
339 ,p_honors
340 ,p_pre_name_adjunct
341 ,p_hold_applicant_date_until
342 ,p_benefit_group_id
343 ,p_receipt_of_death_cert_date
344 ,p_coord_ben_med_pln_no
345 ,p_coord_ben_no_cvg_flag
346 ,p_uses_tobacco_flag
347 ,p_dpdnt_adoption_date
348 ,p_dpdnt_vlntry_svce_flag
349 ,p_date_of_death
350 ,p_original_date_of_hire
351 ,p_adjusted_svc_date
352 ,p_s_adjusted_svc_date
353 ,p_town_of_birth
354 ,p_region_of_birth
355 ,p_country_of_birth
356 ,p_global_person_id
357 ,p_npw_number
358 ,p_current_npw_flag
359 -- #2264569
360 ,l_table
361 ,null
362 ,null
363 ,null);
364 end update_row1;
365 -- End new code for bug # 2264569 ************************
366 --
367 procedure update_row1(p_rowid VARCHAR2
368 ,p_person_id NUMBER
369 ,p_effective_start_date DATE
370 ,p_effective_end_date DATE
371 ,p_business_group_id NUMBER
372 ,p_person_type_id NUMBER
373 ,p_last_name VARCHAR2
374 ,p_start_date DATE
375 ,p_applicant_number IN OUT NOCOPY VARCHAR2
376 ,p_comment_id NUMBER
377 -- *** Start commented code for bug 2264569 *****
378 --,p_current_applicant_flag VARCHAR2
379 -- *** End commented code for bug 2264569 *******
380 --
381 -- *** Start new code for bug 2264569 ***********
382 -- should be in out parameter
383 ,p_current_applicant_flag IN OUT NOCOPY VARCHAR2
384 -- *** End new code for bug 2264569 *************
385 ,p_current_emp_or_apl_flag VARCHAR2
386 ,p_current_employee_flag VARCHAR2
387 ,p_date_employee_data_verified DATE
388 ,p_date_of_birth DATE
389 ,p_email_address VARCHAR2
390 ,p_employee_number IN OUT NOCOPY VARCHAR2
391 ,p_expense_check_send_to_addr VARCHAR2
392 ,p_first_name VARCHAR2
393 ,p_full_name VARCHAR2
394 ,p_known_as VARCHAR2
395 ,p_marital_status VARCHAR2
396 ,p_middle_names VARCHAR2
397 ,p_nationality VARCHAR2
398 ,p_national_identifier VARCHAR2
399 ,p_previous_last_name VARCHAR2
400 ,p_registered_disabled_flag VARCHAR2
401 ,p_sex VARCHAR2
402 ,p_title VARCHAR2
403 ,p_suffix VARCHAR2
404 ,p_vendor_id NUMBER
405 ,p_work_telephone VARCHAR2
406 ,p_request_id NUMBER
407 ,p_program_application_id NUMBER
408 ,p_program_id NUMBER
409 ,p_program_update_date DATE
410 ,p_a_cat VARCHAR2
411 ,p_a1 VARCHAR2
412 ,p_a2 VARCHAR2
413 ,p_a3 VARCHAR2
414 ,p_a4 VARCHAR2
415 ,p_a5 VARCHAR2
416 ,p_a6 VARCHAR2
417 ,p_a7 VARCHAR2
418 ,p_a8 VARCHAR2
419 ,p_a9 VARCHAR2
420 ,p_a10 VARCHAR2
421 ,p_a11 VARCHAR2
422 ,p_a12 VARCHAR2
423 ,p_a13 VARCHAR2
424 ,p_a14 VARCHAR2
425 ,p_a15 VARCHAR2
426 ,p_a16 VARCHAR2
427 ,p_a17 VARCHAR2
428 ,p_a18 VARCHAR2
429 ,p_a19 VARCHAR2
430 ,p_a20 VARCHAR2
431 ,p_a21 VARCHAR2
432 ,p_a22 VARCHAR2
433 ,p_a23 VARCHAR2
434 ,p_a24 VARCHAR2
435 ,p_a25 VARCHAR2
436 ,p_a26 VARCHAR2
437 ,p_a27 VARCHAR2
438 ,p_a28 VARCHAR2
439 ,p_a29 VARCHAR2
440 ,p_a30 VARCHAR2
441 ,p_last_update_date DATE
442 ,p_last_updated_by NUMBER
443 ,p_last_update_login NUMBER
444 ,p_created_by NUMBER
445 ,p_creation_date DATE
446 ,p_i_cat VARCHAR2
447 ,p_i1 VARCHAR2
448 ,p_i2 VARCHAR2
449 ,p_i3 VARCHAR2
450 ,p_i4 VARCHAR2
451 ,p_i5 VARCHAR2
452 ,p_i6 VARCHAR2
453 ,p_i7 VARCHAR2
454 ,p_i8 VARCHAR2
455 ,p_i9 VARCHAR2
456 ,p_i10 VARCHAR2
457 ,p_i11 VARCHAR2
458 ,p_i12 VARCHAR2
459 ,p_i13 VARCHAR2
460 ,p_i14 VARCHAR2
461 ,p_i15 VARCHAR2
462 ,p_i16 VARCHAR2
463 ,p_i17 VARCHAR2
464 ,p_i18 VARCHAR2
465 ,p_i19 VARCHAR2
466 ,p_i20 VARCHAR2
467 ,p_i21 VARCHAR2
468 ,p_i22 VARCHAR2
469 ,p_i23 VARCHAR2
470 ,p_i24 VARCHAR2
471 ,p_i25 VARCHAR2
472 ,p_i26 VARCHAR2
473 ,p_i27 VARCHAR2
474 ,p_i28 VARCHAR2
475 ,p_i29 VARCHAR2
476 ,p_i30 VARCHAR2
477 ,p_app_ass_status_type_id NUMBER
478 ,p_emp_ass_status_type_id NUMBER
479 -- *** Start commented code for bug 2264569 ***
480 --,p_system_person_type VARCHAR2
481 -- *** End commented code for bug 2264569******
482 --
483 -- Start new code for bug 2264569 *************
484 -- should be in out parameter
485 ,p_system_person_type IN OUT NOCOPY VARCHAR2
486 -- End new code for bug 2264569 **************
487 --
488 ,p_s_system_person_type VARCHAR2
489 ,p_hire_date DATE
490 ,p_s_hire_date DATE
491 ,p_s_date_of_birth DATE
492 ,p_status in out nocopy VARCHAR2
493 ,p_new_primary_id in out nocopy NUMBER
494 ,p_update_primary in out nocopy VARCHAR2
495 ,p_legislation_code VARCHAR2
496 ,p_vacancy_id IN OUT NOCOPY NUMBER
497 ,p_session_date date
498 ,p_end_of_time date
499 ,p_work_schedule VARCHAR2
500 ,p_correspondence_language VARCHAR2
501 ,p_student_status VARCHAR2
502 ,p_fte_capacity NUMBER
503 ,p_on_military_service VARCHAR2
504 ,p_second_passport_exists VARCHAR2
505 ,p_background_check_status VARCHAR2
506 ,p_background_date_check DATE
507 ,p_blood_type VARCHAR2
508 ,p_last_medical_test_date DATE
509 ,p_last_medical_test_by VARCHAR2
510 ,p_rehire_recommendation VARCHAR2
511 ,p_rehire_reason VARCHAR2
512 ,p_resume_exists VARCHAR2
513 ,p_resume_last_updated DATE
514 ,p_office_number VARCHAR2
515 ,p_internal_location VARCHAR2
516 ,p_mailstop VARCHAR2
517 ,p_honors VARCHAR2
518 ,p_pre_name_adjunct VARCHAR2
519 ,p_hold_applicant_date_until DATE
520 ,p_benefit_group_id NUMBER
521 ,p_receipt_of_death_cert_date DATE
522 ,p_coord_ben_med_pln_no VARCHAR2
523 ,p_coord_ben_no_cvg_flag VARCHAR2
524 ,p_uses_tobacco_flag VARCHAR2
525 ,p_dpdnt_adoption_date DATE
526 ,p_dpdnt_vlntry_svce_flag VARCHAR2
527 ,p_date_of_death DATE
528 ,p_original_date_of_hire DATE
529 ,p_adjusted_svc_date DATE
530 ,p_s_adjusted_svc_date DATE
531 ,p_town_of_birth VARCHAR2
532 ,p_region_of_birth VARCHAR2
533 ,p_country_of_birth VARCHAR2
534 ,p_global_person_id VARCHAR2
535 ,p_npw_number IN OUT NOCOPY VARCHAR2
536 ,p_current_npw_flag VARCHAR2
537 -- Start new code for bug 2264569 ****************************
538 -- added pl/sql table
539 ,p_tab IN OUT NOCOPY HR_EMPLOYEE_APPLICANT_API.t_ApplTable
540 -- End new code for bug 2264569 ******************************
541 ,p_order_name IN VARCHAR2
542 ,p_global_name IN VARCHAR2
543 ,p_local_name IN VARCHAR2
544 ) is
545 --
546 l_period_of_service_id number; -- Period of Service id.
547 l_back2back boolean;
548 l_employ_emp_apl varchar2(1); -- Are we employing an EMP_APL?
549 l_fire_warning varchar2(1); -- If set Y return to form displaying warning.
550 l_num_appls NUMBER; -- Number of applicants.
551 l_num_accepted_appls NUMBER; -- Number of accepted spplicant assignments
552 l_set_of_books_id NUMBER; -- Required for GL.
553 v_dummy NUMBER; -- For cursor fetch.
554 l_npw_number per_all_people_f.npw_number%type;
555 --
556 l_warn_ee VARCHAR2(1) := 'N';
557 --
558 l_max_ele number;--added for bug 6600075
559
560 cursor get_pay_proposal
561 is
562 select PAY_PROPOSAL_ID
563 from per_pay_proposals
564 where change_date = p_s_hire_date
565 and assignment_id = (select assignment_id
566 from per_assignments_f
567 where person_id = p_person_id
568 and primary_flag = 'Y'
569 and effective_start_date = p_hire_date
570 and assignment_type = 'E'
571 );
572 --
573 --
574 /* BEGIN OF WWBUG 1975359 */
575 cursor c1 is
576 select party_id
577 from per_all_people_f
578 where person_id = p_person_id;
579 --
580 l_party_id number;
581 --
582 cursor c_person is
583 select *
584 from per_all_people_f
585 where person_id = p_person_id
586 and p_effective_start_date
587 between effective_start_date
588 and effective_end_date;
589 --
590 l_person per_all_people_f%rowtype;
591 --
592 /* END OF WWBUG 1975359 */
593 --
594 -- **** Start new code for bug 2264569 ******************************
595 cursor c_apl_flag(cp_person_type varchar2) is
596 select current_applicant_flag
597 from per_startup_person_types
598 where system_person_type = cp_person_type;
599 -- **** End new code for bug 2264569 *******************************
600 --
601 --bug no 5546586 starts here
602 cursor email_address is
603 select email_address
604 from per_all_people_f
605 where rowid = p_rowid;
606 --bug no 5546586 ends here
607 begin
608
609 --
610 -- Bug 3091465. The system_person_type at the person-level should
611 -- never be CWK; this information is only available from the PTU
612 -- records. Reset the person type. Note, this situation should
613 -- never occur; this is simply a safety net.
614 --
615 IF p_system_person_type = 'CWK' THEN
616 p_system_person_type := p_s_system_person_type;
617 END IF;
618
619 --
620 -- p_status has the Value of where the code should start on re-entry.
621 -- on startup = 'BEGIN'( First time called from form)
622 -- other values depend on what meesages have been returned to the client
623 -- and the re-entry point on return from the client.
624 --
625 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',5);
626 --
627 /* BEGIN OF WWBUG 1975359 */
628 open c1;
629 --
630 fetch c1 into l_party_id;
631 --
632 close c1;
633 --bug no 5546586 starts here
634
635 open email_address;
636 --
637 fetch email_address into per_hrtca_merge.g_old_email_address;
638 --
639 close email_address;
640 --bug no 5546586 starts here
641
642 /* BEGIN OF WWBUG 1975359 */
643 --
644 if p_status = 'BEGIN' then
645 --
646 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',10);
647 -- Test to see if the hire_date_has changed
648 -- Providing Person type has not and it is emp.
649 -- Or that it has changed to EMP
650 --
651 if (p_hire_date <> p_s_hire_date)
652 and (p_s_hire_date is not null)
653 and (((p_system_person_type = p_s_system_person_type)
654 and p_system_person_type in('EMP','EMP_APL'))
655 or ((p_system_person_type = 'EMP'
656 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
657 or (p_system_person_type = 'EMP_APL'
658 and p_s_system_person_type = 'APL')
659 or (p_system_person_type = 'EMP'
660 and p_s_system_person_type = 'EMP_APL'))) then
661 -- get the period_of_service_id
662 -- 303729 if person is a supervisor
663 -- test whether change to hire_date would invalidate this action
664 --
665 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',15);
666 --
667 if ((p_hire_date <> p_s_hire_date)
668 and (p_system_person_type = p_s_system_person_type))
669 then
670 per_people12_pkg.check_not_supervisor(p_person_id
671 ,p_hire_date
672 ,p_s_hire_date);
673 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',20);
674 end if;
675 begin
676 select pps.period_of_service_id
677 into l_period_of_service_id
678 from per_periods_of_service pps
679 where pps.person_id = p_person_id
680 and pps.date_start = p_s_hire_date;
681 --
682 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',25);
683 exception
684 when no_data_found then
685 --
686 -- If no data found and a previous hire date existed
687 -- then raise an error;
688 --
689 if p_s_hire_date is not null then
690 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
691 hr_utility.set_message_token('PROCEDURE','Update_row');
692 hr_utility.raise_error;
693 end if;
694 end;
695 --
696 -- check the integrity of the date change.
697 -- Date may come in between a person type change.
698 --
699 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',30);
700 hr_date_chk.check_hire_ref_int(p_person_id
701 ,p_business_group_id
702 ,l_period_of_service_id
703 ,p_s_hire_date
704 ,p_system_person_type
705 ,p_hire_date);
706 -- VT 12/05/96 bug #418637
707 -- check the existence of the recurring element entries
708 --
709 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',35);
710 per_people12_pkg.check_recur_ent(p_person_id,p_hire_date,
711 p_s_hire_date,l_warn_ee);
712 end if;
713 --
714 -- check session date and effective_start_date for differences
715 -- if any exists then ensure the person record is correct
716 -- i.e duplicate datetrack functionality as it currently uses
717 -- a global version of session date to update the rows (not good)
718 --
719 -- VT 08/13/96
720 if p_session_date <> p_effective_start_date then
721 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',40);
722 per_people9_pkg.update_old_person_row(p_person_id =>p_person_id
723 ,p_session_date => p_session_date
724 ,p_effective_start_date=>p_effective_start_date);
725 end if;
726 --
727 -- get the Employee and applicant numbers if necessary
728 -- only returns values depending on values of
729 -- p_current_applicant_flag, p_current_applicant_flag
730 -- and whether p_employee_number and p_applicant_number
731 -- are null.
732 --
733 -- VT #970014 08/19/99
734 --adhunter #2544613 comment out call completely, generate number is called from PERWSEPI.pld
735 --on PRE-UPDATE always
736 -- if p_current_employee_flag = 'Y' and
737 -- p_current_applicant_flag is null and
738 -- p_employee_number is not null then
739 -- null;
740 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',45);
741 -- else
742 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',50);
743 -- hr_person.generate_number(p_current_employee_flag
744 -- ,p_current_applicant_flag
745 -- ,null --p_current_npw_flag
746 -- ,p_national_identifier
747 -- ,p_business_group_id
748 -- ,p_person_id
749 -- ,p_employee_number
750 -- ,p_applicant_number
751 -- ,l_npw_number);
752 -- end if;
753 --
754 -- Test current numbers are not used by
755 -- the system already.
756 --
757 hr_person.validate_unique_number(p_person_id =>p_person_id
758 , p_business_group_id => p_business_group_id
759 , p_employee_number => p_employee_number
760 , p_applicant_number => p_applicant_number
761 , p_npw_number => null --p_npw_number
762 , p_current_employee => p_current_employee_flag
763 , p_current_applicant => p_current_applicant_flag
764 , p_current_npw => null --p_current_npw_flag
765 );
766 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',55);
767 -- VT 12/05/96 bug #418637
768 if l_warn_ee = 'Y' then
769 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',60);
770 p_status := 'RECUR_ENT_CHK'; -- Set status to next reentry point.
771 else
772 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',65);
773 p_status := 'VACANCY_CHECK'; -- Set status to next possible reentry point.
774 end if;
775 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',70);
776
777 --Start of fix for Bug 2167668
778
779 IF p_start_date > p_hold_applicant_date_until THEN
780 hr_utility.set_message('800', 'PER_289796_HOLD_UNTIL_DATE');
781 hr_utility.set_message_token('HOLD_DATE', p_start_date );
782 hr_utility.raise_error;
783 END IF;
784
785 -- End of fix for Bug 2167668
786 end if; -- End the First in section
787 --
788 -- VT 12/05/96 bug #418637
789 if p_status = 'RECUR_ENT_CHK' then
790 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',75);
791 return;
792 end if; -- End of RECUR_ENT_CHK
793 --
794 --
795 -- Start of Person type changes.
796 --
797 -- Has the Person type changed to become that of an applicant?
798 --
799 if (p_system_person_type ='APL'
800 and p_s_system_person_type = 'OTHER')
801 or (p_system_person_type = 'APL_EX_APL'
802 and p_s_system_person_type = 'EX_APL')
803 or (p_system_person_type = 'EMP_APL'
804 and p_s_system_person_type = 'EMP')
805 or (p_system_person_type = 'EX_EMP_APL'
806 and p_s_system_person_type = 'EX_EMP') then
807 --
808 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',80);
809 NULL;
810 -- 3652025 >> this process is replaced by call to
811 -- hr_applicant_internal.create_applicant_anytime() procedure.
812 -- called directly from PERWSEPI.pld
813 --
814 -- Ensure no future person_type_changes.
815 --
816 -- if hr_person.chk_future_person_type(p_s_system_person_type
817 -- ,p_person_id
818 -- ,p_business_group_id
819 -- ,p_effective_start_date) then
820 -- fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
821 -- app_exception.raise_exception;
822 --end if;
823 --
824 -- Ensure there are no future applicant assignments
825 --
826 --per_people3_pkg.check_future_apl(p_person_id => p_person_id
827 -- ,p_hire_date => greatest(p_hire_date
828 --,p_effective_start_date));
829 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',85);
830 --
831 -- Insert the default applicant row and applicant
832 -- assignment.
833 --
834 -- VT 08/13/96
835 --per_people9_pkg.insert_applicant_rows(p_person_id => p_person_id
836 -- ,p_effective_start_date => p_effective_start_date
837 -- ,p_effective_end_date => p_effective_end_date
838 -- ,p_business_group_id =>p_business_group_id
839 -- ,p_app_ass_status_type_id => p_app_ass_status_type_id
840 -- ,p_request_id => p_request_id
841 -- ,p_program_application_id => p_program_application_id
842 -- ,p_program_id => p_program_id
843 -- ,p_program_update_date => p_program_update_date
844 -- ,p_last_update_date => p_last_update_date
845 -- ,p_last_updated_by => p_last_updated_by
846 -- ,p_last_update_login => p_last_update_login
847 -- ,p_created_by => p_created_by
848 -- ,p_creation_date => p_creation_date);
849 --
850 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',90);
851 --
852 -- PTU Changes
853
854 --hr_per_type_usage_internal.maintain_person_type_usage
855 -- (p_effective_date => p_effective_start_date
856 -- ,p_person_id => p_person_id
857 -- ,p_person_type_id => p_person_type_id
858 -- );
859 --
860 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',92);
861 -- PTU Changes
862 -- <<
863 -- Has the Person type changed to become that of an employee
864 -- when the previous type is not a current applicant?
865 --
866 elsif (p_system_person_type = 'EMP'
867 and ( p_s_system_person_type = 'OTHER'
868 or p_s_system_person_type = 'EX_EMP'
869 or p_s_system_person_type = 'EX_APL')) then /* Bug 523924 */
870 -- or p_s_system_person_type = 'EX_EMP')) then
871 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',95);
872 --
873 -- Ensure no future person_type_changes.
874 --
875 if hr_person.chk_future_person_type(p_s_system_person_type
876 ,p_person_id
877 ,p_business_group_id
878 --changes for bug no 6070935
879 -- ,p_effective_start_date) then
880 ,p_session_date) then
881 --changes for bug no 6070935
882 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
883 app_exception.raise_exception;
884 end if;
885 --
886 if p_s_system_person_type = 'EX_EMP'
887 then
888 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',100);
889 --
890 -- Bug 3154253 stars here.
891 -- Passed earlier of p_ession_date and p_hire_date
892 -- (p_effective_start_date) to the check_hire procedure.
893 --
894 if p_session_date < p_effective_start_date then
895 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',101);
896 per_people12_pkg.check_rehire(p_person_id, p_session_date);
897 else
898 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',102);
899 per_people12_pkg.check_rehire(p_person_id, p_hire_date);
900 end if;
901 --
902 -- bug 3154253 ends here.
903 --
904 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',105);
905 end if;
906 per_people12_pkg.check_future_changes(p_person_id
907 ,p_effective_start_date);
908 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',110);
909 --
910 -- Ensure there are no future applicant assignments
911 --
912 /* per_people3_pkg.check_future_apl(p_person_id => p_person_id
913 ,p_hire_date => greatest(p_hire_date
914 p_effective_start_date));* commented for bug 5403222*/
915 --fix for bug 6600075
916 l_max_ele := p_tab.COUNT;
917
918 if l_max_ele > 0 then
919 per_people3_pkg.check_future_apl(p_person_id => p_person_id
920 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
921 ,p_table=>p_tab);
922 else
923 per_people3_pkg.check_future_apl(p_person_id => p_person_id
924 ,p_hire_date => greatest(p_hire_date,p_effective_start_date));
925 end if;
926
927 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',115);
928 --
929 -- Insert the default period_of service and assignment
930 -- rows.
931 --
932 -- VT 08/13/96
933 per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
934 ,p_effective_start_date => p_effective_start_date
935 ,p_effective_end_date => p_effective_end_date
936 ,p_business_group_id =>p_business_group_id
937 ,p_emp_ass_status_type_id => p_emp_ass_status_type_id
938 ,p_employee_number => p_employee_number
939 ,p_request_id => p_request_id
940 ,p_program_application_id => p_program_application_id
941 ,p_program_id => p_program_id
942 ,p_program_update_date => p_program_update_date
943 ,p_last_update_date => p_last_update_date
944 ,p_last_updated_by => p_last_updated_by
945 ,p_last_update_login => p_last_update_login
946 ,p_created_by => p_created_by
947 ,p_creation_date => p_creation_date
948 ,p_adjusted_svc_date => p_adjusted_svc_date);
949 --
950 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',120);
951
952 -- PTU Changes
953
954 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
955 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_effective_start_date);
956 if p_s_system_person_type in ('EX_EMP','EX_EMP_APL') -- Bug 3637893
957 and p_system_person_type = 'EMP'
958 -- and p_session_date = p_effective_start_date then
959 and l_back2back then
960 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1201);
961 hr_per_type_usage_internal.maintain_person_type_usage
962 (p_effective_date => p_effective_start_date
963 ,p_person_id => p_person_id
964 ,p_person_type_id => p_person_type_id
965 ,p_datetrack_update_mode => 'CORRECTION'
966 );
967 else
968 hr_per_type_usage_internal.maintain_person_type_usage
969 (p_effective_date => p_effective_start_date
970 ,p_person_id => p_person_id
971 ,p_person_type_id => p_person_type_id
972 );
973 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1202);
974 end if;
975
976 -- PTU Changes
977
978 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',121);
979
980 -- Has the Person become an Employee or Employee applicant from being an
981 -- applicant or employee applicant?
982 --
983 elsif ((p_system_person_type = 'EMP'
984 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
985 or (p_system_person_type = 'EMP_APL'
986 and p_s_system_person_type in ('APL','EX_EMP_APL')) /* Bug 732598 */
987 or (p_system_person_type = 'EMP'
988 and p_s_system_person_type = 'EMP_APL')) then
989 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',125);
990 --
991 -- Ensure no future person_type_changes.
992 --
993 if hr_person.chk_future_person_type(p_s_system_person_type
994 ,p_person_id
995 ,p_business_group_id
996 ,p_effective_start_date) then
997 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
998 hr_utility.raise_error;
999 end if;
1000 --
1001 -- Ensure there are no future applicant assignments
1002 --
1003 /* per_people3_pkg.check_future_apl(p_person_id => p_person_id
1004 ,p_hire_date => greatest(p_hire_date
1005 ,p_effective_start_date));* commented for bug 5403222*/
1006 --fix for bug 6600075
1007 l_max_ele := p_tab.COUNT;
1008
1009 if l_max_ele > 0 then
1010 per_people3_pkg.check_future_apl(p_person_id => p_person_id
1011 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
1012 ,p_table=>p_tab);
1013 else
1014 per_people3_pkg.check_future_apl(p_person_id => p_person_id
1015 ,p_hire_date => greatest(p_hire_date,p_effective_start_date));
1016 end if;
1017
1018 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',130);
1019 --
1020 -- Check if the person have open term_assignment records. These can be
1021 -- found by checking if the person have a periods_of_service with
1022 -- no FPD and a value for ATD. Bug 2881076
1023 per_people12_pkg.check_rehire(p_person_id
1024 ,p_hire_date);
1025 --
1026 -- Check that the change is valid.
1027 --
1028 if p_status = 'VACANCY_CHECK' then
1029 loop
1030 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',135);
1031 exit when p_status = 'BOOKINGS_EXIST';
1032 --
1033 -- Check each vacancy,if it is oversubscribed
1034 -- l_fire_warning = 'Y', return to client
1035 -- displaying relevant message.
1036 -- on return l_vacancy_id starts the cursor at the
1037 -- relevant point.
1038 --
1039 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',140);
1040 per_people3_pkg.vacancy_chk(p_person_id => p_person_id
1041 ,p_fire_warning => l_fire_warning
1042 ,p_vacancy_id => p_vacancy_id
1043 -- **** Start new code for bug 2264569 ****************
1044 ,p_table => p_tab -- #2381925
1045 -- **** End new code for bug 2264569 *****************
1046 );
1047 if l_fire_warning = 'Y' then
1048 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',145);
1049 return;
1050 elsif l_fire_warning = 'N' then
1051 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',150);
1052 p_status := 'BOOKINGS_EXIST'; -- Set next possible re-entry point.
1053 end if;
1054 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',155);
1055 end loop;
1056 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',160);
1057 end if; -- End of VACANCY_CHECK
1058 --
1059 if p_status = 'BOOKINGS_EXIST' then
1060 -- VT 09/18/96 #288087, #380280 , #2172590
1061 if (per_people3_pkg.chk_events_exist(p_person_id =>p_person_id
1062 ,p_business_group_id =>p_business_group_id
1063 ,p_hire_date => greatest(p_hire_date,p_session_date))) then
1064 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',165);
1065 return;
1066 else
1067 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',170);
1068 -- **** Start commented code for bug 2264569 *************************
1069 --p_status := 'GET_APPLS'; -- Set next possible re-entry point.
1070 -- **** End commented code for bug 2264569 ***************************
1071 -- **** Start new code for bug 2264569 ***************************
1072 p_status := 'CHOOSE_VAC'; -- Set next possible re-entry point.
1073 -- **** End new code for bug 2264569 *********************************
1074 end if;
1075 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',175);
1076 end if;
1077 if p_status = 'END_BOOKINGS' then
1078 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',180);
1079 hrhirapl.end_bookings(p_person_id
1080 , p_business_group_id
1081 , p_hire_date);
1082 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',185);
1083 --
1084 -- **** START commented code for bug 2264569 *************************** --
1085 --p_status := 'GET_APPLS'; -- Set next possible re-entry point.
1086 -- **** End commented code for bug 2264569 ************************
1087 -- **** Start new code for bug 2264569 ************************
1088 p_status := 'CHOOSE_VAC'; -- Set next possible re-entry point.
1089 -- **** END new code for bug 2264569 ********************************
1090 --
1091 end if;
1092 --
1093 -- **** START commented code for bug 2264569 *******************************
1094 --
1095 -- Removed references to 'end_unaccepted' and 'multiple_contracts'
1096 -- Get_appls has been moved to the client side.
1097 --
1098 -- if p_status='GET_APPLS' then
1099 --
1100 -- Get all the accepted applicants
1101 --
1102 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',190);
1103 -- per_people3_pkg.get_accepted_appls(p_person_id => p_person_id
1104 -- ,p_num_accepted_appls => l_num_accepted_appls
1105 -- ,p_new_primary_id =>p_new_primary_id);
1106 --
1107 -- Get all current applicant assignments.
1108 --
1109 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',195);
1110 -- per_people3_pkg.get_all_current_appls(p_person_id => p_person_id
1111 -- ,p_num_appls => l_num_appls);
1112 --
1113 -- if p_system_person_type = 'EMP_APL' then
1114 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',200);
1115 --
1116 -- If we have got this far then there must be > 0 Accepted
1117 -- applications,therefore check p_system_person_type if EMP_APL
1118 -- and number of accepted is equal to number of current assignments
1119 -- then there is an error. Otherwise go around end_accepted
1120 -- to multiple contracts.
1121 --
1122 -- if l_num_accepted_appls = l_num_appls then
1123 -- hr_utility.set_message('801','HR_6791_EMP_APL_NO_ASG');
1124 -- hr_utility.raise_error;
1125 -- else
1126 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',205);
1127 -- p_status := 'MULTIPLE_CONTRACTS';-- Set next re-entry point.
1128 -- end if;
1129 -- --
1130 -- Number of accepted does not equal number of current then
1131 -- end_accepted.
1132 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',210);
1133 --
1134 -- elsif l_num_accepted_appls <> l_num_appls then
1135 -- hr_utility.set_message('801','HR_EMP_UNACCEPTED_APPL');
1136 -- p_status := 'END_UNACCEPTED'; -- next code re-entry,
1137 -- return;
1138 -- --
1139 -- Otherwise ignore end_accepted.
1140 --
1141 -- else
1142 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',215);
1143 -- p_status := 'MULTIPLE_CONTRACTS'; -- next code re-entry.
1144 -- end if;
1145 -- end if; -- End of GET_APPLS
1146 --
1147 -- if p_status = 'END_UNACCEPTED' then
1148 --
1149 -- End the unaccepted assignments.
1150 --
1151 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',220);
1152 -- hrhirapl.end_unaccepted_app_assign(p_person_id
1153 -- ,p_business_group_id
1154 -- ,p_legislation_code
1155 -- ,p_session_date);
1156 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',225);
1157 -- p_status := 'MULTIPLE_CONTRACTS';
1158 -- end if; -- End of END_UNACCEPTED
1159 -- --
1160 -- -- Test to see if multiple contracts are a possibility.
1161 -- --
1162 --hr_utility.set_location('update_row - b4 MULTIPLE_CONTRACTS',1);
1163 -- if p_status = 'MULTIPLE_CONTRACTS' then -- MULTIPLE_CONTRACTS
1164 -- if l_num_accepted_appls >1 then
1165 -- hr_utility.set_message('801','HR_EMP_MULTIPLE_CONTRACTS');
1166 -- return;
1167 -- else
1168 -- p_status := 'CHOOSE_VAC'; -- next code re-entry.
1169 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',230);
1170 -- end if;
1171 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',235);
1172 -- end if; -- End of MULTIPLE_CONTRACTS
1173 --
1174 -- **** END commented code for bug 2264569 ****************************
1175 --
1176 -- Choose whether to change the Primary assignment
1177 -- and which vacancy is to be the primary if so.
1178 --
1179 hr_utility.set_location('update_row - b4 CHOOSE_VAC',1);
1180 if p_status = 'CHOOSE_VAC' then
1181 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',240);
1182 return;
1183 end if; --End of CHOOSE_VAC
1184 --
1185 -- Can now hire the Person
1186 -- Note HIRE status can only be set from client form
1187 -- as interaction is generally required.
1188 --
1189 hr_utility.set_location('update_row - b4 HIRE',1);
1190 -- +-------------------------------------------------------------------+
1191 -- +--------- BEGIN: Hire process -------------------------------------+
1192 -- +-------------------------------------------------------------------+
1193 if p_status = 'HIRE' then
1194 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',245);
1195
1196 -- bug fix 2824664:
1197 if p_update_primary = 'Y'
1198 and future_pactid_exists(p_person_id, p_effective_start_date)
1199 then
1200 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',246);
1201 fnd_message.set_name('PAY','HR_6591_ASS_ACTIONS_EXIST');
1202 hr_utility.raise_error;
1203 end if;
1204 -- end bug fix 2824664
1205 --
1206 -- If new is Emp and old was Emp_apl
1207 -- then l_emp_emp_apl is set to Y
1208 --
1209 if p_system_person_type = 'EMP'
1210 and p_s_system_person_type = 'EMP_APL' then
1211 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',250);
1212 l_employ_emp_apl := 'Y';
1213 else
1214 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',255);
1215 l_employ_emp_apl := 'N';
1216 end if;
1217 --
1218 -- Run the employ_applicant stored procedure
1219 --
1220 hr_utility.set_location('update_row - b4 hrhirapl',1);
1221 -- **** Start new code for bug 2264569 *****************************
1222 -- End date chosen unaccepted applicant assignments
1223 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',257);
1224 hrhirapl.end_unaccepted_app_assign(p_person_id
1225 ,p_business_group_id
1226 ,p_legislation_code
1227 ,p_session_date
1228 ,p_tab);
1229 hr_utility.set_location('update_row - b4 hrhirapl',2);
1230
1231 -- **** End new code for bug 2264569 *****************************
1232
1233 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',259);
1234 hrhirapl.employ_applicant(p_person_id
1235 ,p_business_group_id
1236 ,p_legislation_code
1237 ,p_new_primary_id
1238 ,p_emp_ass_status_type_id
1239 ,p_last_updated_by
1240 ,p_last_update_login
1241 ,p_effective_start_date
1242 ,p_end_of_time
1243 ,p_last_update_date
1244 ,p_update_primary
1245 ,p_employee_number
1246 ,l_set_of_books_id
1247 ,l_employ_emp_apl
1248 ,p_adjusted_svc_date
1249 ,p_session_date -- Bug 3564129
1250 -- **** Start new code for bug 2264569 ******
1251 ,p_tab
1252 -- **** End new code for bug 2264569 *******
1253 );
1254 hr_utility.set_location('update_row - after hrhirapl',2);
1255 hr_utility.set_location('manage PTU records',3);
1256 if p_system_person_type = 'EMP' then
1257 --
1258 -- PTU : Following Code has been added
1259 -- hr_per_type_usage_internal.maintain_ptu(
1260 -- p_action => 'HIRE_APL',
1261 -- p_person_id => p_person_id,
1262 -- p_actual_termination_date => p_effective_start_date-1);
1263 --
1264 -- **** START new code for bug 2264569 ******************************************
1265 -- Update the system person type to EMP_APL if user is keeping active APPLS.
1266 if hr_employee_applicant_api.retain_exists(p_tab) then
1267 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
1268 p_system_person_type := 'EMP_APL';
1269 open c_apl_flag(p_system_person_type);
1270 fetch c_apl_flag into p_current_applicant_flag;
1271 close c_apl_flag;
1272 hr_utility.trace(' current applicant_flag : '||p_current_applicant_flag);
1273 end if;
1274 -- **** END new code for bug 2264569 *******************************************
1275
1276 -- Bug 3637893 Starts
1277 /* hr_per_type_usage_internal.maintain_person_type_usage
1278 (p_effective_date => p_effective_start_date
1279 ,p_person_id => p_person_id
1280 ,p_person_type_id => p_person_type_id
1281 );
1282 */
1283 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
1284 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_effective_start_date);
1285 if p_s_system_person_type in ('EX_EMP','EX_EMP_APL')
1286 and p_system_person_type = 'EMP'
1287 -- and p_session_date = p_effective_start_date then
1288 and l_back2back then
1289 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1211);
1290 hr_per_type_usage_internal.maintain_person_type_usage
1291 (p_effective_date => p_effective_start_date
1292 ,p_person_id => p_person_id
1293 ,p_person_type_id => p_person_type_id
1294 ,p_datetrack_update_mode => 'CORRECTION'
1295 );
1296 else
1297 hr_per_type_usage_internal.maintain_person_type_usage
1298 (p_effective_date => p_effective_start_date
1299 ,p_person_id => p_person_id
1300 ,p_person_type_id => p_person_type_id
1301 );
1302 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1212);
1303 end if;
1304 -- Bug 3637893 Ends
1305 --
1306 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
1307 -- **** Start new code for bug 2264569 **********************************
1308 if NOT hr_employee_applicant_api.retain_exists(p_tab) then
1309 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',262);
1310 -- **** End new code for bug 2264569 **********************************
1311 hr_per_type_usage_internal.maintain_person_type_usage
1312 (p_effective_date => p_effective_start_date
1313 ,p_person_id => p_person_id
1314 ,p_person_type_id => hr_person_type_usage_info.get_default_person_type_id
1315 (p_business_group_id
1316 ,'EX_APL')
1317 );
1318 -- **** Start new code for bug 2264569 **********************************
1319 end if;
1320
1321 -- **** End new code for bug 2264569 **********************************
1322 -- PTU : End of changes
1323 --
1324 end if; -- End of hire
1325 -- +-------------------------------------------------------------------+
1326 -- +----------- END: Hire process -------------------------------------+
1327 -- +-------------------------------------------------------------------+
1328 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',265);
1329 end if; -- End of HIRE.
1330 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',270);
1331 end if; -- Of Person type change checks.
1332 --
1333 -- changed p_rowid => null to p_rowid => p_rowid
1334 --
1335 ben_dt_trgr_handle.person(p_rowid => p_rowid
1336 ,p_business_group_id => p_business_group_id
1337 ,p_person_id => p_person_id
1338 ,p_effective_start_date => p_effective_start_date
1339 ,p_effective_end_date => p_effective_end_date
1340 ,p_date_of_birth => p_date_of_birth
1341 ,p_date_of_death => p_date_of_death
1342 ,p_marital_status => p_marital_status
1343 ,p_on_military_service => p_on_military_service
1344 ,p_registered_disabled_flag => p_registered_disabled_flag
1345 ,p_sex => p_sex
1346 ,p_student_status => p_student_status
1347 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1348 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1349 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1350 ,p_benefit_group_id => p_benefit_group_id
1351 ,p_per_information10 => p_i10
1352 ,p_original_date_of_hire => p_original_date_of_hire
1353 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1354 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1355 ,p_attribute1 => p_a1
1356 ,p_attribute2 =>p_a2
1357 ,p_attribute3 =>p_a3
1358 ,p_attribute4 =>p_a4
1359 ,p_attribute5 =>p_a5
1360 ,p_attribute6 =>p_a6
1361 ,p_attribute7 =>p_a7
1362 ,p_attribute8 =>p_a8
1363 ,p_attribute9 =>p_a9
1364 ,p_attribute10 =>p_a10
1365 ,p_attribute11 =>p_a11
1366 ,p_attribute12 =>p_a12
1367 ,p_attribute13 =>p_a13
1368 ,p_attribute14 =>p_a14
1369 ,p_attribute15 =>p_a15
1370 ,p_attribute16 =>p_a16
1371 ,p_attribute17 =>p_a17
1372 ,p_attribute18 =>p_a18
1373 ,p_attribute19 =>p_a19
1374 ,p_attribute20 =>p_a20
1375 ,p_attribute21 =>p_a21
1376 ,p_attribute22 =>p_a22
1377 ,p_attribute23 =>p_a23
1378 ,p_attribute24 =>p_a24
1379 ,p_attribute25 =>p_a25
1380 ,p_attribute26 =>p_a26
1381 ,p_attribute27 =>p_a27
1382 ,p_attribute28 =>p_a28
1383 ,p_attribute29 =>p_a29
1384 ,p_attribute30 =>p_a30
1385 );
1386 --
1387 if l_party_id is null then
1388
1389 /*
1390 ** We tried to get the party_id at the start of this process however
1391 ** the person may not have had one. If they have undergone a change in
1392 ** person type they may very well have one by now so we'll try and get
1393 ** the current party_id from their person record (this will have been
1394 ** set when the TCAparty was created) if we don't currently have the value.
1395 **
1396 ** Ideally we should get the party_id returned from the PTU maintenance
1397 ** code where it would have been derived but this is not an ideal world
1398 ** so we won't.
1399 */
1400 open c1;
1401 --
1402 fetch c1 into l_party_id;
1403 --
1404 close c1;
1405
1406 end if;
1407
1408 hr_utility.set_location('update_row - b4 update',1);
1409 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',272);
1410 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_person_type_id,272);
1411 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_s_system_person_type,272);
1412 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_system_person_type,272);
1413 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||hr_person_type_usage_info.get_default_person_type_id(
1414 p_business_group_id,p_system_person_type),272);
1415
1416 -- Bug 6196362 Starts.
1417 -- update per_people_f ppf
1418 update per_all_people_f ppf
1419 -- Bug 6196362 Ends.
1420 set ppf.person_id = p_person_id
1421 ,ppf.effective_start_date = p_effective_start_date
1422 ,ppf.effective_end_date = p_effective_end_date
1423 ,ppf.business_group_id = p_business_group_id
1424 -- ,ppf.person_type_id = p_person_type_id
1425 --,ppf.person_type_id =hr_person_type_usage_info.get_default_person_type_id( p_business_group_id ,p_system_person_type) bug 6848958
1426 ,ppf.person_type_id =decode( p_system_person_type,'CWK',
1427 hr_person_type_usage_info.get_default_person_type_id(
1428 p_business_group_id,'OTHER'),
1429 hr_person_type_usage_info.get_default_person_type_id(
1430 p_business_group_id,p_system_person_type)) -- fix for bug6848958
1431 ,ppf.last_name = p_last_name
1432 ,ppf.start_date = p_start_date
1433 ,ppf.applicant_number = p_applicant_number
1434 ,ppf.comment_id = p_comment_id
1435 ,ppf.current_applicant_flag = p_current_applicant_flag
1436 ,ppf.current_emp_or_apl_flag = p_current_emp_or_apl_flag
1437 ,ppf.current_employee_flag = p_current_employee_flag
1438 ,ppf.date_employee_data_verified = p_date_employee_data_verified
1439 ,ppf.date_of_birth = p_date_of_birth
1440 ,ppf.email_address = p_email_address
1441 ,ppf.employee_number = p_employee_number
1442 ,ppf.expense_check_send_to_address = p_expense_check_send_to_addr
1443 ,ppf.first_name = p_first_name
1444 ,ppf.full_name = p_full_name
1445 ,ppf.known_as = p_known_as
1446 ,ppf.marital_status = p_marital_status
1447 ,ppf.middle_names = p_middle_names
1448 ,ppf.nationality = p_nationality
1449 ,ppf.national_identifier = p_national_identifier
1450 ,ppf.previous_last_name = p_previous_last_name
1451 ,ppf.registered_disabled_flag = p_registered_disabled_flag
1452 ,ppf.sex = p_sex
1453 ,ppf.title = p_title
1454 ,ppf.suffix = p_suffix
1455 ,ppf.vendor_id = p_vendor_id
1456 -- ,ppf.work_telephone = p_work_telephone
1457 ,ppf.request_id = p_request_id
1458 ,ppf.program_application_id = p_program_application_id
1459 ,ppf.program_id = p_program_id
1460 ,ppf.program_update_date = p_program_update_date
1461 ,ppf.attribute_category = p_a_cat
1462 ,ppf.attribute1 = p_a1
1463 ,ppf.attribute2 = p_a2
1464 ,ppf.attribute3 = p_a3
1465 ,ppf.attribute4 = p_a4
1466 ,ppf.attribute5 = p_a5
1467 ,ppf.attribute6 = p_a6
1468 ,ppf.attribute7 = p_a7
1469 ,ppf.attribute8 = p_a8
1470 ,ppf.attribute9 = p_a9
1471 ,ppf.attribute10 = p_a10
1472 ,ppf.attribute11 = p_a11
1473 ,ppf.attribute12 = p_a12
1474 ,ppf.attribute13 = p_a13
1475 ,ppf.attribute14 = p_a14
1476 ,ppf.attribute15 = p_a15
1477 ,ppf.attribute16 = p_a16
1478 ,ppf.attribute17 = p_a17
1479 ,ppf.attribute18 = p_a18
1480 ,ppf.attribute19 = p_a19
1481 ,ppf.attribute20 = p_a20
1482 ,ppf.attribute21 = p_a21
1483 ,ppf.attribute22 = p_a22
1484 ,ppf.attribute23 = p_a23
1485 ,ppf.attribute24 = p_a24
1486 ,ppf.attribute25 = p_a25
1487 ,ppf.attribute26 = p_a26
1488 ,ppf.attribute27 = p_a27
1489 ,ppf.attribute28 = p_a28
1490 ,ppf.attribute29 = p_a29
1491 ,ppf.attribute30 = p_a30
1492 ,ppf.last_update_date = p_last_update_date
1493 ,ppf.last_updated_by = p_last_updated_by
1494 ,ppf.last_update_login = p_last_update_login
1495 ,ppf.created_by = p_created_by
1496 ,ppf.creation_date = p_creation_date
1497 ,ppf.per_information_category = p_i_cat
1498 ,ppf.per_information1 = p_i1
1499 ,ppf.per_information2 = p_i2
1500 ,ppf.per_information3 = p_i3
1501 ,ppf.per_information4 = p_i4
1502 ,ppf.per_information5 = p_i5
1503 ,ppf.per_information6 = p_i6
1504 ,ppf.per_information7 = p_i7
1505 ,ppf.per_information8 = p_i8
1506 ,ppf.per_information9 = p_i9
1507 ,ppf.per_information10 = p_i10
1508 ,ppf.per_information11 = p_i11
1509 ,ppf.per_information12 = p_i12
1510 ,ppf.per_information13 = p_i13
1511 ,ppf.per_information14 = p_i14
1512 ,ppf.per_information15 = p_i15
1513 ,ppf.per_information16 = p_i16
1514 ,ppf.per_information17 = p_i17
1515 ,ppf.per_information18 = p_i18
1516 ,ppf.per_information19 = p_i19
1517 ,ppf.per_information20 = p_i20
1518 ,ppf.per_information21 = p_i21
1519 ,ppf.per_information22 = p_i22
1520 ,ppf.per_information23 = p_i23
1521 ,ppf.per_information24 = p_i24
1522 ,ppf.per_information25 = p_i25
1523 ,ppf.per_information26 = p_i26
1524 ,ppf.per_information27 = p_i27
1525 ,ppf.per_information28 = p_i28
1526 ,ppf.per_information29 = p_i29
1527 ,ppf.per_information30 = p_i30
1528 ,ppf.work_schedule = p_work_schedule
1529 ,ppf.correspondence_language = p_correspondence_language
1530 ,ppf.student_status = p_student_status
1531 ,ppf.fte_capacity = p_fte_capacity
1532 ,ppf.on_military_service = p_on_military_service
1533 ,ppf.second_passport_exists = p_second_passport_exists
1534 ,ppf.background_check_status = p_background_check_status
1535 ,ppf.background_date_check = p_background_date_check
1536 ,ppf.blood_type = p_blood_type
1537 ,ppf.last_medical_test_date = p_last_medical_test_date
1538 ,ppf.last_medical_test_by = p_last_medical_test_by
1539 ,ppf.rehire_recommendation = p_rehire_recommendation
1540 ,ppf.rehire_reason = p_rehire_reason
1541 ,ppf.resume_exists = p_resume_exists
1542 ,ppf.resume_last_updated = p_resume_last_updated
1543 ,ppf.office_number = p_office_number
1544 ,ppf.internal_location = p_internal_location
1545 ,ppf.mailstop = p_mailstop
1546 ,ppf.honors = p_honors
1547 ,ppf.pre_name_adjunct = p_pre_name_adjunct
1548 ,ppf.hold_applicant_date_until = p_hold_applicant_date_until
1549 ,ppf.benefit_group_id = p_benefit_group_id
1550 ,ppf.receipt_of_death_cert_date = p_receipt_of_death_cert_date
1551 ,ppf.coord_ben_med_pln_no = p_coord_ben_med_pln_no
1552 ,ppf.coord_ben_no_cvg_flag = p_coord_ben_no_cvg_flag
1553 ,ppf.uses_tobacco_flag = p_uses_tobacco_flag
1554 ,ppf.dpdnt_adoption_date = p_dpdnt_adoption_date
1555 ,ppf.dpdnt_vlntry_svce_flag = p_dpdnt_vlntry_svce_flag
1556 ,ppf.date_of_death = p_date_of_death
1557 ,ppf.original_date_of_hire = p_original_date_of_hire
1558 ,ppf.town_of_birth = p_town_of_birth
1559 ,ppf.region_of_birth = p_region_of_birth
1560 ,ppf.country_of_birth = p_country_of_birth
1561 ,ppf.global_person_id = p_global_person_id
1562 ,ppf.party_id = l_party_id
1563 ,ppf.npw_number = p_npw_number
1564 ,ppf.current_npw_flag = p_current_npw_flag
1565 ,ppf.order_name = p_order_name -- #3889584
1566 ,ppf.global_name = p_global_name
1567 ,ppf.local_name = p_local_name
1568 where ppf.rowid = p_rowid;
1569 --
1570 if sql%rowcount <1 then
1571 hr_utility.set_message(801,'HR_6001_ALL_MANDATORY_FIELD');
1572 hr_utility.set_message_token('MISSING_FIELD','rowid is'||p_rowid);
1573 hr_utility.raise_error;
1574 end if;
1575 --
1576
1577 -- Start of Fix #2447513
1578 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',275);
1579 --End of Fix
1580
1581 -- Tests required post-update
1582 --
1583 /* BEGIN OF WWBUG 1975359 */
1584 --
1585 open c_person;
1586 --
1587 fetch c_person into l_person;
1588 --
1589 close c_person;
1590 --
1591 per_hrtca_merge.update_tca_person(p_Rec => l_person);
1592 --
1593 hr_utility.set_location('update_row - after update',1);
1594 --
1595 /* END OF WWBUG 1975359 */
1596 --
1597 -- HR/WF Synchronization call
1598 --
1599 /* -- this now called later in prog so called after ptu. Bug 3297591
1600 per_hrwf_synch.per_per_wf(p_rec => l_person,
1601 p_action => 'UPDATE');
1602 */
1603 --
1604 -- Has the Date of Birth changed?
1605 --
1606 if p_date_of_birth is null and
1607 p_s_date_of_birth is not null then
1608 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',280);
1609 per_people12_pkg.check_birth_date(p_person_id);
1610 end if;
1611 if p_date_of_birth <> p_s_date_of_birth then
1612 --
1613 -- Run the assignment_link_usages and Element_entry
1614 -- code for Change of Personal qualifying conditions.
1615 --
1616 --
1617 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',282);
1618 per_people3_pkg.run_alu_ee(p_alu_mode => 'CHANGE_PQC'
1619 ,p_business_group_id=>p_business_group_id
1620 ,p_person_id =>p_person_id
1621 ,p_old_start =>p_s_hire_date
1622 ,p_start_date => p_last_update_date
1623 );
1624 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',285);
1625 end if;
1626 --
1627 hr_utility.set_location('update_row - after update',2);
1628 --
1629 -- test if hire_date has changed. and system person type has not.
1630 --
1631 if (((p_current_employee_flag = 'Y')
1632 and (p_hire_date <> p_s_hire_date)
1633 and (p_system_person_type = p_s_system_person_type)))
1634 or (nvl(p_adjusted_svc_date,hr_general.end_of_time) -- #1573563
1635 <> nvl(p_s_adjusted_svc_date,
1636 hr_general.end_of_time)
1637 -- and (p_s_system_person_type not in ('EX_EMP','EX_EMP_APL')) -- #2060744
1638 --
1639 -- Verify person has been employee before modifying the POS
1640 --
1641 and Hr_General2.is_person_type(p_person_id, 'EMP',p_s_hire_date) --#2472146
1642 ) then
1643 --
1644 -- Update the period of service for the employee
1645 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',290);
1646 --
1647 per_people3_pkg.update_period(p_person_id =>p_person_id
1648 ,p_hire_date => p_s_hire_date
1649 ,p_new_hire_date =>p_hire_date
1650 ,p_adjusted_svc_date => p_adjusted_svc_date);
1651 --
1652 hr_utility.set_location('update_row - after update',3);
1653 --
1654 -- Update the hire records i.e
1655 -- assignment etc.
1656 --
1657 --
1658 /*--- If condition is added for the bug 5907880 */
1659 if (((p_current_employee_flag = 'Y')
1660 and (p_hire_date <> p_s_hire_date)
1661 and (p_system_person_type = p_s_system_person_type))) then
1662 /*--- End changes for the bug 5907880 */
1663 hr_utility.set_location('update_row - after update',4);
1664 hr_date_chk.update_hire_records(p_person_id
1665 ,p_applicant_number
1666 ,p_hire_date
1667 ,p_s_hire_date
1668 ,p_last_updated_by
1669 ,p_last_update_login);
1670 End if;
1671 --
1672 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',295);
1673 -- Commented, as this action is being done in
1674 -- hr_change_start_date_api.update_pay_proposal (pehirapi.pkb)
1675 -- open get_pay_proposal;
1676 -- fetch get_pay_proposal into v_dummy;
1677 -- if get_pay_proposal%FOUND
1678 -- then
1679 -- close get_pay_proposal;
1680 -- begin
1681 -- update per_pay_proposals
1682 -- set change_date = p_hire_date
1683 -- where change_date = p_s_hire_date
1684 -- and assignment_id = (select assignment_id
1685 -- from per_assignments_f
1686 -- where person_id = p_person_id
1687 -- and primary_flag = 'Y'
1688 -- and effective_start_date = p_hire_date
1689 -- and assignment_type = 'E'
1690 -- );
1691 -- --
1692 -- if sql%ROWCOUNT <> 1
1693 -- then
1694 -- raise NO_DATA_FOUND;
1695 -- end if;
1696 -- exception
1697 -- when NO_DATA_FOUND then
1698 -- hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
1699 -- hr_utility.set_message_token('PROCEDURE','Update_row');
1700 -- hr_utility.set_message_token('STEP','4');
1701 -- hr_utility.raise_error;
1702 -- end;
1703 -- else
1704 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',300);
1705 -- close get_pay_proposal;
1706 -- end if;
1707 hr_utility.set_location('update_row - after update',5);
1708 --
1709 -- Update PTU records to reflect hire date change.
1710 --
1711 -- PTU changes: following has been added
1712 --
1713 -- hr_per_type_usage_internal.maintain_ptu(
1714 -- p_action =>'HIRE DATE',
1715 -- p_person_id => p_person_id,
1716 -- p_date_start => p_hire_date,
1717 -- p_old_date_start => p_s_hire_date);
1718 --
1719 hr_per_type_usage_internal.change_hire_date_ptu
1720 (p_date_start => p_hire_date
1721 ,p_old_date_start => p_s_hire_date
1722 ,p_person_id => p_person_id
1723 ,p_system_person_type => 'EMP'
1724 );
1725 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',305);
1726 --
1727 -- PTU : end of changes
1728 --
1729 --
1730 -- Run the assignment_link_usages and Element_entry
1731 -- code for Assignment Criteria.
1732 --
1733 per_people3_pkg.run_alu_ee(p_alu_mode => 'ASG_CRITERIA'
1734 ,p_business_group_id=>p_business_group_id
1735 ,p_person_id =>p_person_id
1736 ,p_old_start =>p_s_hire_date
1737 ,p_start_date => p_hire_date);
1738 --
1739 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',310);
1740 --
1741 end if;
1742 --
1743 -- 1766066, contact start date enh. start
1744 --
1745 if (((p_hire_date is not null
1746 and p_s_hire_date is not null
1747 and p_hire_date < p_s_hire_date)
1748 or (p_hire_date is not null
1749 and p_s_hire_date is null))
1750 and (NVL(p_current_npw_flag,'N') <> 'Y') -- 3813870
1751 ) then
1752 maintain_coverage(p_person_id => p_person_id
1753 ,p_type => 'EMP'
1754 );
1755 end if;
1756 --
1757 -- 1766066 end
1758 --
1759 -- call synch process here - Bug 3297591.
1760 --
1761 --/*
1762 per_hrwf_synch.per_per_wf(p_rec => l_person,
1763 p_action => 'UPDATE');
1764 --*/
1765 --
1766 p_status := 'END'; -- Status required to end update loop on server
1767 hr_utility.set_location('Leaving PER_PEOPLE12_PKG.update_row1',315);
1768 --
1769 end update_row1;
1770 --
1771 procedure check_future_changes(p_person_id NUMBER
1772 ,p_effective_start_date DATE)
1773 is
1774 --
1775 l_dummy VARCHAR2(1);
1776 --
1777 cursor future_exists
1778 is
1779 select '1'
1780 from sys.dual
1781 where exists (
1782 select 'future assignment exists'
1783 from per_people_f ppf
1784 where ppf.person_id = p_person_id
1785 and ppf.effective_start_date > p_effective_start_date
1786 );
1787 begin
1788 open future_exists;
1789 fetch future_exists into l_dummy;
1790 if future_exists%found then
1791 fnd_message.set_name('PAY','HR_7510_PER_FUT_CHANGE');
1792 app_exception.raise_exception;
1793 end if;
1794 close future_exists;
1795 end;
1796 --
1797 procedure check_not_supervisor(p_person_id NUMBER
1798 ,p_new_hire_date DATE
1799 ,p_old_hire_date DATE)
1800 is
1801 l_dummy VARCHAR2(1);
1802 --
1803 cursor supervisor
1804 is
1805 select 'Y'
1806 from per_assignments_f paf
1807 where paf.assignment_type = 'E'
1808 and paf.supervisor_id = p_person_id
1809 and p_new_hire_date > paf.effective_start_date
1810 and paf.effective_end_date >= p_old_hire_date ;
1811 --
1812 begin
1813 open supervisor;
1814 fetch supervisor into l_dummy;
1815 if supervisor%FOUND then
1816 close supervisor;
1817 fnd_message.set_name('PAY','HR_51031_INV_HIRE_CHG_IS_SUPER');
1818 app_exception.raise_exception;
1819 end if;
1820 close supervisor;
1821 end;
1822 --
1823 --
1824 procedure check_rehire(p_person_id NUMBER
1825 ,p_start_date DATE)
1826 IS
1827 cursor old_pps_exists
1828 is
1829 select 1
1830 from per_periods_of_service pps
1831 where pps.person_id = p_person_id
1832 and pps.actual_termination_date is not null;
1833 --
1834 -- 70.11 nvl(pps.final_process_date,p_start_date)+1 < p_start_date;
1835 --
1836 cursor pps_not_ended
1837 is
1838 --
1839 -- 115.67 (START)
1840 --
1841 --select pps.final_process_date
1842 select pps.last_standard_process_date,
1843 pps.final_process_date
1844 --
1845 -- 115.67 (END)
1846 --
1847 from per_periods_of_service pps
1848 where pps.person_id = p_person_id
1849 and pps.date_start = (select max(date_start)
1850 from per_periods_of_service pps1
1851 where pps1.person_id = pps.person_id
1852 and pps1.date_start <p_start_date
1853 )
1854 and nvl(pps.final_process_date,p_start_date) >= p_start_date;
1855 --
1856 v_dummy INTEGER;
1857 v_dummy_fpd date;
1858 --
1859 -- 115.67 (START)
1860 --
1861 v_dummy_lspd DATE;
1862 l_rule_value pay_legislation_rules.rule_mode%TYPE;
1863 l_rule_found BOOLEAN;
1864 l_legislation_code pay_legislation_rules.legislation_code%TYPE;
1865 --
1866 -- Cursor to get legislation code
1867 --
1868 CURSOR csr_per_legislation IS
1869 SELECT bus.legislation_code
1870 FROM per_people_f per
1871 ,per_business_groups bus
1872 WHERE per.person_id = p_person_id
1873 AND per.business_group_id+0 = bus.business_group_id
1874 AND p_start_date BETWEEN per.effective_start_date
1875 AND per.effective_end_date;
1876 --
1877 l_proc VARCHAR2(50);
1878 --
1879 -- 115.67 (END)
1880 --
1881 begin
1882 l_proc := 'per_people12_pkg.check_rehire';
1883 hr_utility.set_location('Entering '||l_proc,5);
1884 --
1885 -- 115.67 (START)
1886 --
1887 --
1888 -- Get legislation
1889 --
1890 OPEN csr_per_legislation;
1891 FETCH csr_per_legislation INTO l_legislation_code;
1892 CLOSE csr_per_legislation;
1893 --
1894 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
1895 ,l_legislation_code
1896 ,l_rule_value
1897 ,l_rule_found
1898 );
1899 --
1900 hr_utility.set_location(l_proc,10);
1901 --
1902 -- 115.67 (END)
1903 --
1904 --
1905 -- Check if old PPS row exists
1906 --
1907 open old_pps_exists;
1908 fetch old_pps_exists into v_dummy;
1909 if old_pps_exists%FOUND
1910 then
1911 close old_pps_exists;
1912 --
1913 hr_utility.set_location(l_proc,15);
1914 --
1915 -- if yes then check last PPS
1916 -- has had it's FPD closed down and that the FPD + 1
1917 -- is less than current hire date
1918 -- if not error;
1919 --
1920 open pps_not_ended;
1921 --
1922 -- 115.67 (START)
1923 --
1924 --fetch pps_not_ended into v_dummy_fpd;
1925 fetch pps_not_ended into v_dummy_lspd,v_dummy_fpd;
1926 --
1927 -- 115.67 (END)
1928 --
1929 if pps_not_ended%FOUND then
1930 --
1931 -- 115.67 (START)
1932 --
1933 --
1934 hr_utility.set_location(l_proc,20);
1935 --
1936 if (not(l_rule_found)
1937 OR
1938 (l_rule_found AND nvl(l_rule_value,'N') = 'N'))
1939 then
1940 --
1941 -- old behaviour as rehire before fpd is not enabled
1942 --
1943 hr_utility.set_location(l_proc,25);
1944 --
1945 -- 115.67 (END)
1946 --
1947 close pps_not_ended;
1948 if v_dummy_fpd is null then
1949 hr_utility.set_message('800','HR_51032_EMP_PREV_FPD_OPEN');
1950 else
1951 hr_utility.set_message('800','PER_289308_FUTURE_ENDED_FPD');
1952 end if;
1953 hr_utility.raise_error;
1954 --
1955 -- 115.67 (START)
1956 --
1957 else
1958 --
1959 hr_utility.set_location(l_proc,30);
1960 --
1961 -- Rehire before FPD allowed
1962 --
1963 if v_dummy_fpd is null then
1964 close pps_not_ended;
1965 hr_utility.set_message('800','HR_449756_FPD_PREV_PDS');
1966 hr_utility.raise_error;
1967 end if;
1968 if v_dummy_lspd >= p_start_date then
1969 close pps_not_ended;
1970 hr_utility.set_message('800','HR_449759_REHIRE_AFTER_LSPD');
1971 hr_utility.raise_error;
1972 end if;
1973 end if;
1974 --
1975 -- 115.67 (END)
1976 --
1977 end if;
1978 close pps_not_ended;
1979 else
1980 close old_pps_exists;
1981 end if;
1982 hr_utility.set_location('Leaving '||l_proc,50);
1983 end;
1984 --
1985 procedure check_birth_date(p_person_id NUMBER)
1986 is
1987 v_dummy NUMBER;
1988 --
1989 -- Cursor to check if any employee assignments have
1990 -- Payroll id set.
1991 --
1992 cursor get_payroll
1993 is
1994 select asg.assignment_id
1995 from per_assignments_f asg
1996 where asg.person_id = p_person_id
1997 and asg.payroll_id is not null;
1998 begin
1999 open get_payroll;
2000 fetch get_payroll into v_dummy;
2001 --
2002 -- If a row exists, flag an error to stop
2003 -- Date of birth being nulled when emp on payroll.
2004 --
2005 if get_payroll%FOUND
2006 then
2007 close get_payroll;
2008 hr_utility.set_message('801','HR_7950_PPM_NULL_DOB');
2009 hr_utility.raise_error;
2010 else
2011 close get_payroll;
2012 end if;
2013 end;
2014 -- VT 12/05/96 bug #418637 new procedure
2015 procedure check_recur_ent(p_person_id NUMBER,
2016 p_start_date DATE,
2017 p_old_date DATE,
2018 p_warn_raise IN OUT NOCOPY VARCHAR2)
2019 is
2020 --
2021 l_warn VARCHAR2(1);
2022 l_earlier_date DATE;
2023 l_later_date DATE;
2024 --
2025 begin
2026 l_warn := p_warn_raise;
2027 if p_start_date > p_old_date then
2028 l_earlier_date := p_old_date;
2029 l_later_date := p_start_date;
2030 else
2031 l_earlier_date := p_start_date;
2032 l_later_date := p_old_date;
2033 end if;
2034 begin
2035 select 'Y'
2036 into l_warn
2037 from dual
2038 where exists
2039 (select null
2040 from pay_element_entries_f ee,
2041 pay_element_links_f el,
2042 pay_element_types_f et
2043 where ee.assignment_id in
2044 (select assignment_id
2045 from per_assignments_f asg
2046 where asg.person_id = p_person_id
2047 and asg.effective_start_date between l_earlier_date and l_later_date)
2048 and ee.element_link_id = el.element_link_id
2049 and el.element_type_id = et.element_type_id
2050 and et.processing_type = 'R');
2051 exception when NO_DATA_FOUND then null;
2052 end;
2053 p_warn_raise := l_warn;
2054 end;
2055
2056 --
2057 --Added procedures for bug 1766066. Only maintain_coverage is declared in header
2058 --
2059 PROCEDURE ins_or_upd_precursor_row
2060 (p_person_id in number
2061 ,p_cov_date_start in date) is
2062 --
2063 cursor csr_per_details(c_person_id number) is
2064 select *
2065 from per_all_people_f
2066 where person_id = c_person_id
2067 order by effective_start_date asc;
2068 --
2069 cursor csr_per_exists(c_person_id number, c_effective_date date) is
2070 select 'Y'
2071 from dual
2072 where exists(
2073 select 1
2074 from per_all_people_f per
2075 where per.person_id = c_person_id
2076 and c_effective_date between per.effective_start_date and per.effective_end_date
2077 );
2078 --
2079 cursor csr_per_type(c_person_id number, c_effective_date date) is
2080 select ppt.system_person_type
2081 from per_person_types ppt,
2082 per_all_people_f ppf
2083 where ppf.person_id = c_person_id
2084 and ppf.person_type_id = ppt.person_type_id;
2085 --
2086 cursor csr_address(c_person_id number, c_date_from date) is
2087 select address_id
2088 from per_addresses
2089 where person_id = c_person_id
2090 and date_from = c_date_from;
2091 --
2092 cursor csr_get_other(c_business_group_id number) is
2093 select ppt.person_type_id
2094 from per_person_types ppt
2095 where ppt.business_group_id = c_business_group_id
2096 and ppt.default_flag = 'Y'
2097 and ppt.active_flag = 'Y'
2098 and ppt.system_person_type = 'OTHER';
2099 --
2100 l_proc varchar2(100) := 'per_people12_pkg.ins_or_upd_precursor_row';
2101 l_per_rec per_all_people_f%rowtype;
2102 l_system_person_type varchar2(60);
2103 l_dummy varchar2(10);
2104 l_cov_date_start date;
2105 l_ptu_nextval number;
2106 l_object_version_number number;
2107 --
2108 begin
2109 hr_utility.set_location('Entering '||l_proc,1);
2110 hr_utility.set_location('person_id: '||p_person_id,2);
2111 open csr_per_exists(p_person_id, p_cov_date_start);
2112 fetch csr_per_exists into l_dummy;
2113 if csr_per_exists%found then
2114 hr_utility.set_location(l_proc,5);
2115 close csr_per_exists; --no need to create precursor row since person exists as of cov.s.d.
2116 else
2117 hr_utility.set_location(l_proc,10);
2118 close csr_per_exists;
2119 --
2120 open csr_per_details(p_person_id);
2121 fetch csr_per_details into l_per_rec; -- fetch once to get earliest record only
2122 close csr_per_details;
2123 --
2124 open csr_per_type(p_person_id, l_per_rec.effective_start_date);
2125 fetch csr_per_type into l_system_person_type;
2126 close csr_per_type;
2127 --
2128
2129 for l_address_id in csr_address(p_person_id, l_per_rec.effective_start_date)
2130 loop
2131 update per_addresses
2132 set date_from = p_cov_date_start
2133 where address_id = l_address_id.address_id;
2134 end loop;
2135 --
2136 if l_system_person_type = 'OTHER' then --first DT record is "OTHER" so simply extend back
2137 hr_utility.set_location(l_proc,15);
2138 update per_all_people_f
2139 set effective_start_date = p_cov_date_start,
2140 start_date = p_cov_date_start
2141 where person_id = p_person_id
2142 and effective_start_date = l_per_rec.effective_start_date;
2143 --
2144 -- Fox for bug 3390731 starts here.
2145 --
2146 update per_all_people_f
2147 set start_date = p_cov_date_start
2148 where person_id = p_person_id;
2149 --
2150 -- Fix for bug 3390731 ends here.
2151 --
2152 update per_person_type_usages_f ptu
2153 set ptu.effective_start_date = p_cov_date_start
2154 where ptu.person_id = p_person_id
2155 and ptu.person_type_id in (select ppt.person_type_id
2156 from per_person_types ppt
2157 where ppt.system_person_type = 'OTHER');
2158 else
2159 hr_utility.set_location(l_proc,20);
2160 -- now change some of the fields before inserting precursor row
2161 l_per_rec.applicant_number := null;
2162 l_per_rec.employee_number := null;
2163 l_per_rec.current_employee_flag := null;
2164 l_per_rec.current_applicant_flag := null;
2165 l_per_rec.current_emp_or_apl_flag := null;
2166 l_per_rec.rehire_authorizor := null;
2167 l_per_rec.effective_end_date := l_per_rec.effective_start_date - 1;
2168 l_per_rec.effective_start_date := p_cov_date_start;
2169 l_per_rec.start_date := p_cov_date_start;
2170 open csr_get_other(l_per_rec.business_group_id);
2171 fetch csr_get_other into l_per_rec.person_type_id;
2172 close csr_get_other;
2173 --
2174 insert into per_all_people_f(
2175 person_id,
2176 effective_start_date,
2177 effective_end_date,
2178 business_group_id,
2179 person_type_id,
2180 last_name,
2181 start_date,
2182 comment_id,
2183 current_applicant_flag,
2184 current_emp_or_apl_flag,
2185 current_employee_flag,
2186 date_of_birth,
2187 first_name,
2188 full_name,
2189 middle_names,
2190 sex,
2191 title,
2192 pre_name_adjunct,
2193 suffix,
2194 national_identifier,
2195 attribute_category,
2196 attribute1,
2197 attribute2,
2198 attribute3,
2199 attribute4,
2200 attribute5,
2201 attribute6,
2202 attribute7,
2203 attribute8,
2204 attribute9,
2205 attribute10,
2206 attribute11,
2207 attribute12,
2208 attribute13,
2209 attribute14,
2210 attribute15,
2211 attribute16,
2212 attribute17,
2213 attribute18,
2214 attribute19,
2215 attribute20,
2216 attribute21,
2217 attribute22,
2218 attribute23,
2219 attribute24,
2220 attribute25,
2221 attribute26,
2222 attribute27,
2223 attribute28,
2224 attribute29,
2225 attribute30,
2226 per_information_category,
2227 per_information1,
2228 per_information2,
2229 per_information3,
2230 per_information4,
2231 per_information5,
2232 per_information6,
2233 per_information7,
2234 per_information8,
2235 per_information9,
2236 per_information10,
2237 per_information11,
2238 per_information12,
2239 per_information13,
2240 per_information14,
2241 per_information15,
2242 per_information16,
2243 per_information17,
2244 per_information18,
2245 per_information19,
2246 per_information20,
2247 per_information21,
2248 per_information22,
2249 per_information23,
2250 per_information24,
2251 per_information25,
2252 per_information26,
2253 per_information27,
2254 per_information28,
2255 per_information29,
2256 per_information30,
2257 known_as
2258 )
2259 values(
2260 l_per_rec.person_id,
2261 l_per_rec.effective_start_date,
2262 l_per_rec.effective_end_date,
2263 l_per_rec.business_group_id,
2264 l_per_rec.person_type_id,
2265 l_per_rec.last_name,
2266 l_per_rec.start_date,
2267 l_per_rec.comment_id,
2268 l_per_rec.current_applicant_flag,
2269 l_per_rec.current_emp_or_apl_flag,
2270 l_per_rec.current_employee_flag,
2271 l_per_rec.date_of_birth,
2272 l_per_rec.first_name,
2273 l_per_rec.full_name,
2274 l_per_rec.middle_names,
2275 l_per_rec.sex,
2276 l_per_rec.title,
2277 l_per_rec.pre_name_adjunct,
2278 l_per_rec.suffix,
2279 l_per_rec.national_identifier,
2280 l_per_rec.attribute_category,
2281 l_per_rec.attribute1,
2282 l_per_rec.attribute2,
2283 l_per_rec.attribute3,
2284 l_per_rec.attribute4,
2285 l_per_rec.attribute5,
2286 l_per_rec.attribute6,
2287 l_per_rec.attribute7,
2288 l_per_rec.attribute8,
2289 l_per_rec.attribute9,
2290 l_per_rec.attribute10,
2291 l_per_rec.attribute11,
2292 l_per_rec.attribute12,
2293 l_per_rec.attribute13,
2294 l_per_rec.attribute14,
2295 l_per_rec.attribute15,
2296 l_per_rec.attribute16,
2297 l_per_rec.attribute17,
2298 l_per_rec.attribute18,
2299 l_per_rec.attribute19,
2300 l_per_rec.attribute20,
2301 l_per_rec.attribute21,
2302 l_per_rec.attribute22,
2303 l_per_rec.attribute23,
2304 l_per_rec.attribute24,
2305 l_per_rec.attribute25,
2306 l_per_rec.attribute26,
2307 l_per_rec.attribute27,
2308 l_per_rec.attribute28,
2309 l_per_rec.attribute29,
2310 l_per_rec.attribute30,
2311 l_per_rec.per_information_category,
2312 l_per_rec.per_information1,
2313 l_per_rec.per_information2,
2314 l_per_rec.per_information3,
2315 l_per_rec.per_information4,
2316 l_per_rec.per_information5,
2317 l_per_rec.per_information6,
2318 l_per_rec.per_information7,
2319 l_per_rec.per_information8,
2320 l_per_rec.per_information9,
2321 l_per_rec.per_information10,
2322 l_per_rec.per_information11,
2323 l_per_rec.per_information12,
2324 l_per_rec.per_information13,
2325 l_per_rec.per_information14,
2326 l_per_rec.per_information15,
2327 l_per_rec.per_information16,
2328 l_per_rec.per_information17,
2329 l_per_rec.per_information18,
2330 l_per_rec.per_information19,
2331 l_per_rec.per_information20,
2332 l_per_rec.per_information21,
2333 l_per_rec.per_information22,
2334 l_per_rec.per_information23,
2335 l_per_rec.per_information24,
2336 l_per_rec.per_information25,
2337 l_per_rec.per_information26,
2338 l_per_rec.per_information27,
2339 l_per_rec.per_information28,
2340 l_per_rec.per_information29,
2341 l_per_rec.per_information30,
2342 l_per_rec.known_as
2343 );
2344 --
2345 -- Fox for bug 3390731 starts here.
2346 -- Update the start_date to the minimum of effective_start_date,
2347 -- in this case it is l_per_rec.start_date.
2348 --
2349 update per_all_people_f
2350 set start_date = l_per_rec.start_date
2351 where person_id = l_per_rec.person_id;
2352 --
2353 -- Fix for bug 3390731 ends here.
2354 --
2355 select per_person_type_usages_s.nextval into l_ptu_nextval
2356 from sys.dual;
2357 l_object_version_number := 1;
2358 --
2359 insert into per_person_type_usages_f(
2360 PERSON_TYPE_USAGE_ID,
2361 PERSON_ID,
2362 PERSON_TYPE_ID,
2363 EFFECTIVE_START_DATE,
2364 EFFECTIVE_END_DATE,
2365 OBJECT_VERSION_NUMBER
2366 )
2367 values
2368 (l_ptu_nextval,
2369 l_per_rec.person_id,
2370 l_per_rec.person_type_id,
2371 l_per_rec.effective_start_date,
2372 l_per_rec.effective_end_date,
2373 l_object_version_number
2374 );
2375 --
2376 end if;
2377 end if;
2378 hr_utility.set_location('Leaving '||l_proc,25);
2379 end ins_or_upd_precursor_row;
2380 --
2381 --
2382 PROCEDURE maintain_coverage
2383 (p_person_id in number
2384 ,p_type in varchar2) is
2385 --
2386 cursor csr_get_contacts(c_person_id number) is
2387 select contact_person_id, min(nvl(date_start,hr_api.g_sot)) date_start
2388 from per_contact_relationships
2389 where person_id = c_person_id
2390 group by contact_person_id;
2391 --
2392 cursor csr_get_person(c_contact_person_id number) is
2393 select ctr.person_id, min(nvl(ctr.date_start,hr_api.g_sot)) date_start
2394 from per_contact_relationships ctr
2395 ,per_person_type_usages_f ptu
2396 ,per_person_types ppt
2397 where ctr.contact_person_id = c_contact_person_id
2398 and ctr.person_id = ptu.person_id
2399 and ptu.person_type_id = ppt.person_type_id
2400 and ppt.system_person_type = 'EMP'
2401 group by ctr.person_id;
2402 --
2403 cursor csr_last_hire_date(c_person_id number) is
2404 select max(date_start)
2405 from per_periods_of_service
2406 where person_id = c_person_id;
2407 --
2408 l_proc varchar2(100) := 'per_people12_pkg.maintain_coverage';
2409 l_cov_date_start date;
2410 l_person_id number;
2411 l_contact_person_id number;
2412 l_ctr_date_start date;
2413 l_pds_date_start date;
2414 --
2415 begin
2416 if p_type = 'EMP' then
2417 hr_utility.set_location(l_proc,5);
2418 open csr_last_hire_date(p_person_id);
2419 fetch csr_last_hire_date into l_pds_date_start;
2420 close csr_last_hire_date;
2421 --
2422 for l_cov_rec --contact_person_id, date_start
2423 in csr_get_contacts(p_person_id)
2424 loop
2425 if l_cov_rec.date_start > l_pds_date_start then
2426 l_cov_date_start := l_cov_rec.date_start;
2427 else
2428 l_cov_date_start := l_pds_date_start;
2429 end if;
2430 hr_utility.set_location(l_proc,15);
2431 ins_or_upd_precursor_row(l_cov_rec.contact_person_id, l_cov_date_start);
2432 end loop;
2433 elsif p_type = 'CONT' then
2434 hr_utility.set_location(l_proc,20);
2435 for l_cov_rec1 --person_id, date_start
2436 in csr_get_person(p_person_id)
2437 loop
2438 open csr_last_hire_date(l_cov_rec1.person_id);
2439 fetch csr_last_hire_date into l_pds_date_start;
2440 close csr_last_hire_date;
2441 --
2442 if l_cov_rec1.date_start > l_pds_date_start then
2443 l_cov_date_start := l_cov_rec1.date_start;
2444 else
2445 l_cov_date_start := l_pds_date_start;
2446 end if;
2447 hr_utility.set_location(l_proc,30);
2448 ins_or_upd_precursor_row(p_person_id, l_cov_date_start);
2449 end loop;
2450 end if;
2451 hr_utility.set_location('Leaving '||l_proc,40);
2452 end maintain_coverage;
2453 --
2454 --
2455 END PER_PEOPLE12_PKG;