[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE12_PKG
Source
1 PACKAGE BODY PER_PEOPLE12_PKG AS
2 /* $Header: peper12t.pkb 120.15.12020000.7 2013/04/22 04:52:35 srannama 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 -- ER FPT
37 -- this procedure handles future person type changes
38 -- will be called direcly from update_row1 if a future person type change exists
39 -- or future assignment changes exists
40 -- The profile option HR:Future Dated Person Change must be set to Yes
41 procedure update_row2(p_rowid VARCHAR2
42 ,p_person_id NUMBER
43 ,p_effective_start_date DATE
44 ,p_effective_end_date DATE
45 ,p_business_group_id NUMBER
46 ,p_person_type_id NUMBER
47 ,p_last_name VARCHAR2
48 ,p_start_date DATE
49 ,p_applicant_number IN OUT NOCOPY VARCHAR2
50 ,p_comment_id NUMBER
51 -- *** Start commented code for bug 2264569 *****
52 --,p_current_applicant_flag VARCHAR2
53 -- *** End commented code for bug 2264569 *******
54 --
55 -- *** Start new code for bug 2264569 ***********
56 -- should be in out parameter
57 ,p_current_applicant_flag IN OUT NOCOPY VARCHAR2
58 -- *** End new code for bug 2264569 *************
59 ,p_current_emp_or_apl_flag VARCHAR2
60 ,p_current_employee_flag VARCHAR2
61 ,p_date_employee_data_verified DATE
62 ,p_date_of_birth DATE
63 ,p_email_address VARCHAR2
64 ,p_employee_number IN OUT NOCOPY VARCHAR2
65 ,p_expense_check_send_to_addr VARCHAR2
66 ,p_first_name VARCHAR2
67 ,p_full_name VARCHAR2
68 ,p_known_as VARCHAR2
69 ,p_marital_status VARCHAR2
70 ,p_middle_names VARCHAR2
71 ,p_nationality VARCHAR2
72 ,p_national_identifier VARCHAR2
73 ,p_previous_last_name VARCHAR2
74 ,p_registered_disabled_flag VARCHAR2
75 ,p_sex VARCHAR2
76 ,p_title VARCHAR2
77 ,p_suffix VARCHAR2
78 ,p_vendor_id NUMBER
79 ,p_work_telephone VARCHAR2
80 ,p_request_id NUMBER
81 ,p_program_application_id NUMBER
82 ,p_program_id NUMBER
83 ,p_program_update_date DATE
84 ,p_a_cat VARCHAR2
85 ,p_a1 VARCHAR2
86 ,p_a2 VARCHAR2
87 ,p_a3 VARCHAR2
88 ,p_a4 VARCHAR2
89 ,p_a5 VARCHAR2
90 ,p_a6 VARCHAR2
91 ,p_a7 VARCHAR2
92 ,p_a8 VARCHAR2
93 ,p_a9 VARCHAR2
94 ,p_a10 VARCHAR2
95 ,p_a11 VARCHAR2
96 ,p_a12 VARCHAR2
97 ,p_a13 VARCHAR2
98 ,p_a14 VARCHAR2
99 ,p_a15 VARCHAR2
100 ,p_a16 VARCHAR2
101 ,p_a17 VARCHAR2
102 ,p_a18 VARCHAR2
103 ,p_a19 VARCHAR2
104 ,p_a20 VARCHAR2
105 ,p_a21 VARCHAR2
106 ,p_a22 VARCHAR2
107 ,p_a23 VARCHAR2
108 ,p_a24 VARCHAR2
109 ,p_a25 VARCHAR2
110 ,p_a26 VARCHAR2
111 ,p_a27 VARCHAR2
112 ,p_a28 VARCHAR2
113 ,p_a29 VARCHAR2
114 ,p_a30 VARCHAR2
115 ,p_last_update_date DATE
116 ,p_last_updated_by NUMBER
117 ,p_last_update_login NUMBER
118 ,p_created_by NUMBER
119 ,p_creation_date DATE
120 ,p_i_cat VARCHAR2
121 ,p_i1 VARCHAR2
122 ,p_i2 VARCHAR2
123 ,p_i3 VARCHAR2
124 ,p_i4 VARCHAR2
125 ,p_i5 VARCHAR2
126 ,p_i6 VARCHAR2
127 ,p_i7 VARCHAR2
128 ,p_i8 VARCHAR2
129 ,p_i9 VARCHAR2
130 ,p_i10 VARCHAR2
131 ,p_i11 VARCHAR2
132 ,p_i12 VARCHAR2
133 ,p_i13 VARCHAR2
134 ,p_i14 VARCHAR2
135 ,p_i15 VARCHAR2
136 ,p_i16 VARCHAR2
137 ,p_i17 VARCHAR2
138 ,p_i18 VARCHAR2
139 ,p_i19 VARCHAR2
140 ,p_i20 VARCHAR2
141 ,p_i21 VARCHAR2
142 ,p_i22 VARCHAR2
143 ,p_i23 VARCHAR2
144 ,p_i24 VARCHAR2
145 ,p_i25 VARCHAR2
146 ,p_i26 VARCHAR2
147 ,p_i27 VARCHAR2
148 ,p_i28 VARCHAR2
149 ,p_i29 VARCHAR2
150 ,p_i30 VARCHAR2
151 ,p_app_ass_status_type_id NUMBER
152 ,p_emp_ass_status_type_id NUMBER
153 -- *** Start commented code for bug 2264569 ***
154 --,p_system_person_type VARCHAR2
155 -- *** End commented code for bug 2264569******
156 --
157 -- Start new code for bug 2264569 *************
158 -- should be in out parameter
159 ,p_system_person_type IN OUT NOCOPY VARCHAR2
160 -- End new code for bug 2264569 **************
161 --
162 ,p_s_system_person_type VARCHAR2
163 ,p_hire_date DATE
164 ,p_s_hire_date DATE
165 ,p_s_date_of_birth DATE
166 ,p_status in out nocopy VARCHAR2
167 ,p_new_primary_id in out nocopy NUMBER
168 ,p_update_primary in out nocopy VARCHAR2
169 ,p_legislation_code VARCHAR2
170 ,p_vacancy_id IN OUT NOCOPY NUMBER
171 ,p_session_date date
172 ,p_end_of_time date
173 ,p_work_schedule VARCHAR2
174 ,p_correspondence_language VARCHAR2
175 ,p_student_status VARCHAR2
176 ,p_fte_capacity NUMBER
177 ,p_on_military_service VARCHAR2
178 ,p_second_passport_exists VARCHAR2
179 ,p_background_check_status VARCHAR2
180 ,p_background_date_check DATE
181 ,p_blood_type VARCHAR2
182 ,p_last_medical_test_date DATE
183 ,p_last_medical_test_by VARCHAR2
184 ,p_rehire_recommendation VARCHAR2
185 ,p_rehire_reason VARCHAR2
186 ,p_resume_exists VARCHAR2
187 ,p_resume_last_updated DATE
188 ,p_office_number VARCHAR2
189 ,p_internal_location VARCHAR2
190 ,p_mailstop VARCHAR2
191 ,p_honors VARCHAR2
192 ,p_pre_name_adjunct VARCHAR2
193 ,p_hold_applicant_date_until DATE
194 ,p_benefit_group_id NUMBER
195 ,p_receipt_of_death_cert_date DATE
196 ,p_coord_ben_med_pln_no VARCHAR2
197 ,p_coord_ben_no_cvg_flag VARCHAR2
198 ,p_uses_tobacco_flag VARCHAR2
199 ,p_dpdnt_adoption_date DATE
200 ,p_dpdnt_vlntry_svce_flag VARCHAR2
201 ,p_date_of_death DATE
202 ,p_original_date_of_hire DATE
203 ,p_adjusted_svc_date DATE
204 ,p_s_adjusted_svc_date DATE
205 ,p_town_of_birth VARCHAR2
206 ,p_region_of_birth VARCHAR2
207 ,p_country_of_birth VARCHAR2
208 ,p_global_person_id VARCHAR2
209 ,p_npw_number IN OUT NOCOPY VARCHAR2
210 ,p_current_npw_flag VARCHAR2
211 -- Start new code for bug 2264569 ****************************
212 -- added pl/sql table
213 ,p_tab IN OUT NOCOPY HR_EMPLOYEE_APPLICANT_API.t_ApplTable
214 -- End new code for bug 2264569 ******************************
215 ,p_order_name IN VARCHAR2
216 ,p_global_name IN VARCHAR2
217 ,p_local_name IN VARCHAR2
218 ) is
219 --
220 l_period_of_service_id number; -- Period of Service id.
221 l_back2back boolean;
222 l_employ_emp_apl varchar2(1); -- Are we employing an EMP_APL?
223 l_fire_warning varchar2(1); -- If set Y return to form displaying warning.
224 l_num_appls NUMBER; -- Number of applicants.
225 l_num_accepted_appls NUMBER; -- Number of accepted spplicant assignments
226 l_set_of_books_id NUMBER; -- Required for GL.
227 v_dummy NUMBER; -- For cursor fetch.
228 l_npw_number per_all_people_f.npw_number%type;
229 --
230 l_warn_ee VARCHAR2(1) := 'N';
231 --
232 l_max_ele number;--added for bug 6600075
233
234 cursor get_pay_proposal
235 is
236 select PAY_PROPOSAL_ID
237 from per_pay_proposals
238 where change_date = p_s_hire_date
239 and assignment_id = (select assignment_id
240 from per_assignments_f
241 where person_id = p_person_id
242 and primary_flag = 'Y'
243 and effective_start_date = p_hire_date
244 and assignment_type = 'E'
245 );
246 --
247 --
248 /* BEGIN OF WWBUG 1975359 */
249 cursor c1 is
250 select party_id
251 from per_all_people_f
252 where person_id = p_person_id
253 and p_effective_start_date
254 between effective_start_date
255 and effective_end_date; /* Fix for Bug 7442246 */
256 --
257 l_party_id number;
258 --
259 cursor c_person is
260 select *
261 from per_all_people_f
262 where person_id = p_person_id
263 and p_effective_start_date
264 between effective_start_date
265 and effective_end_date;
266 --
267 l_person per_all_people_f%rowtype;
268 --
269 /* END OF WWBUG 1975359 */
270 --
271 -- **** Start new code for bug 2264569 ******************************
272 cursor c_apl_flag(cp_person_type varchar2) is
273 select current_applicant_flag
274 from per_startup_person_types
275 where system_person_type = cp_person_type;
276 -- **** End new code for bug 2264569 *******************************
277 --
278 --bug no 5546586 starts here
279 cursor email_address is
280 select email_address
281 from per_all_people_f
282 where rowid = p_rowid;
283 --bug no 5546586 ends here
284
285 -- call to ethnicity
286 cursor c_person_enthn is
287 select business_group_id,per_information1
288 from per_all_people_f
289 where person_id = p_person_id
290 and p_effective_start_date
291 between effective_start_date
292 and effective_end_date;
293
294 l_bgid number;
295 l_per_info1 varchar2(150);
296 -- call to ethnicity
297
298
299 -- bug no 9248094 Starts here
300
301 l_msg varchar2(2000);
302 -- ER FPT
303
304 l_create_apl varchar2(1) := 'N';
305 l_next_person_type varchar2(30);
306 l_fut_ex_apl_exists varchar2(1);
307 l_datetrack_mode varchar2(50);
308 l_apl_end_date date;
309 l_fpt_hire_flag varchar2(1);
310 l_application_id number;
311 l_cur_apl_id number;
312 l_new_application_id number;
313 l_current_apl_end_date date;
314 l_yes_backtoback varchar2(1) := 'N';
315 l_per_latest_ovn number;
316 l_person_type_id1 varchar2(50);
317 l_per_effective_start_date date;
318 l_per_effective_end_date date;
319 l_current_applicant_flag varchar2(1);
320 l_current_emp_or_apl_flag varchar2(1);
321 l_current_employee_flag varchar2(1);
322 l_full_name varchar2(240);
323 l_name_combination_warning boolean;
324 l_assign_payroll_warning boolean;
325 l_orig_hire_warning boolean;
326 l_comment_id number;
327 l_effective_start_date date;
328 l_exists varchar2(1);
329 l_first_apl_date date := hr_api.g_eot;
330
331 cursor person_record(p_effective_date date) is
332 select * from per_all_people_f
333 where person_id = p_person_id
334 and p_effective_date between effective_start_date and effective_end_date;
335
336 per_rec person_record%rowtype;
337
338
339 cursor per_future_rows(p_eff_start_date date) is
340 select 'Y' from per_all_people_f
341 where person_id = p_person_id
342 and effective_start_date > p_eff_start_date;
343
344 cursor csr_get_apln_details(p_hire_date date) is
345 select date_received,date_end
346 from per_applications
347 where person_id= p_person_id and date_received > p_hire_date;
348
349 cursor csr_back_toback_apln(p_date_end date) is
350 select 'Y' from per_applications
351 where person_id = p_person_id
352 and date_received = p_date_end +1;
353
354 cursor chk_fut_ex_apl(p_hire_date date) is
355 select 'Y' from per_person_type_usages_f
356 where person_id = p_person_id
357 and person_type_id = hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'EX_APL')
358 and effective_start_date > p_hire_date;
359
360 cursor csr_fut_asg_rec(p_application_id number) is
361 select assignment_id, effective_start_date, effective_end_date
362 from per_all_assignments_f
363 where person_id = p_person_id
364 and assignment_id <> p_new_primary_id
365 and application_id = p_application_id
366 and effective_start_date > greatest(p_hire_date,p_session_date)
367 and assignment_type = 'A'
368 order by effective_start_date;
369
370 cursor chk_apl_exists(p_asg_start_date date) is
371 select application_id, date_end from per_applications
372 where person_id = p_person_id
373 and p_asg_start_date between date_received and nvl(date_end,hr_api.g_eot);
374
375 cursor get_apln_id is
376 select distinct application_id
377 from per_all_assignments_f
378 where person_id = p_person_id
379 and assignment_id = p_new_primary_id;
380
381 --ER FPT
382
383 begin
384
385 if p_date_of_death is not null and trunc(p_date_of_death) > trunc(sysdate) then
386 fnd_message.set_name('PER','PER_289974_TCA_PERSON');
387 fnd_message.set_token('PROCEDURE','PER_PEOPLE12_PKG.update_row2');
388 fnd_message.set_token('STEP','1');
389 l_msg := fnd_message.get_string('AR','HZ_API_NO_FUTURE_DATE_ALLOWED');
390 l_msg := replace(l_msg,'&COLUMN',' DATE_OF_DEATH ');
391 fnd_message.set_token('ERROR', l_msg);
392 fnd_message.raise_error;
393 end if;
394
395 -- bug no 9248094 ends here
396 --
397 -- Bug 3091465. The system_person_type at the person-level should
398 -- never be CWK; this information is only available from the PTU
399 -- records. Reset the person type. Note, this situation should
400 -- never occur; this is simply a safety net.
401 --
402 IF p_system_person_type = 'CWK' THEN
403 p_system_person_type := p_s_system_person_type;
404 END IF;
405
406 --
407 -- p_status has the Value of where the code should start on re-entry.
408 -- on startup = 'BEGIN'( First time called from form)
409 -- other values depend on what meesages have been returned to the client
410 -- and the re-entry point on return from the client.
411 --
412 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',5);
413 --
414 /* BEGIN OF WWBUG 1975359 */
415 open c1;
416 --
417 fetch c1 into l_party_id;
418 --
419 close c1;
420 --bug no 5546586 starts here
421
422 open email_address;
423 --
424 fetch email_address into per_hrtca_merge.g_old_email_address;
425 --
426 close email_address;
427 --bug no 5546586 starts here
428
429 /* BEGIN OF WWBUG 1975359 */
430 --
431 if p_status = 'BEGIN' then
432 --
433 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',10);
434 -- Test to see if the hire_date_has changed
435 -- Providing Person type has not and it is emp.
436 -- Or that it has changed to EMP
437 --
438 if (p_hire_date <> p_s_hire_date)
439 and (p_s_hire_date is not null)
440 and (((p_system_person_type = p_s_system_person_type)
441 and p_system_person_type in('EMP','EMP_APL'))
442 or ((p_system_person_type = 'EMP'
443 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
444 or (p_system_person_type = 'EMP_APL'
445 and p_s_system_person_type = 'APL')
446 or (p_system_person_type = 'EMP'
447 and p_s_system_person_type = 'EMP_APL'))) then
448 -- get the period_of_service_id
449 -- 303729 if person is a supervisor
450 -- test whether change to hire_date would invalidate this action
451 --
452 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',15);
453 --
454 if ((p_hire_date <> p_s_hire_date)
455 and (p_system_person_type = p_s_system_person_type))
456 then
457 per_people12_pkg.check_not_supervisor(p_person_id
458 ,p_hire_date
459 ,p_s_hire_date);
460 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',20);
461 end if;
462 begin
463 select pps.period_of_service_id
464 into l_period_of_service_id
465 from per_periods_of_service pps
466 where pps.person_id = p_person_id
467 and pps.date_start = p_s_hire_date;
468 --
469 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',25);
470 exception
471 when no_data_found then
472 --
473 -- If no data found and a previous hire date existed
474 -- then raise an error;
475 --
476 if p_s_hire_date is not null then
477 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
478 hr_utility.set_message_token('PROCEDURE','update_row2');
479 hr_utility.raise_error;
480 end if;
481 end;
482 --
483 -- check the integrity of the date change.
484 -- Date may come in between a person type change.
485 --
486 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',30);
487 hr_date_chk.check_hire_ref_int(p_person_id
488 ,p_business_group_id
489 ,l_period_of_service_id
490 ,p_s_hire_date
491 ,p_system_person_type
492 ,p_hire_date);
493 -- VT 12/05/96 bug #418637
494 -- check the existence of the recurring element entries
495 --
496 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',35);
497 per_people12_pkg.check_recur_ent(p_person_id,p_hire_date,
498 p_s_hire_date,l_warn_ee);
499 end if;
500 --
501 -- check session date and effective_start_date for differences
502 -- if any exists then ensure the person record is correct
503 -- i.e duplicate datetrack functionality as it currently uses
504 -- a global version of session date to update the rows (not good)
505 --
506 -- VT 08/13/96
507 if p_session_date <> p_effective_start_date then
508 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',40);
509 per_people9_pkg.update_old_person_row(p_person_id =>p_person_id
510 ,p_session_date => p_session_date
511 ,p_effective_start_date=>p_effective_start_date);
512 end if;
513 --
514 -- get the Employee and applicant numbers if necessary
515 -- only returns values depending on values of
516 -- p_current_applicant_flag, p_current_applicant_flag
517 -- and whether p_employee_number and p_applicant_number
518 -- are null.
519 --
520 -- VT #970014 08/19/99
521 --adhunter #2544613 comment out call completely, generate number is called from PERWSEPI.pld
522 --on PRE-UPDATE always
523 -- if p_current_employee_flag = 'Y' and
524 -- p_current_applicant_flag is null and
525 -- p_employee_number is not null then
526 -- null;
527 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',45);
528 -- else
529 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',50);
530 -- hr_person.generate_number(p_current_employee_flag
531 -- ,p_current_applicant_flag
532 -- ,null --p_current_npw_flag
533 -- ,p_national_identifier
534 -- ,p_business_group_id
535 -- ,p_person_id
536 -- ,p_employee_number
537 -- ,p_applicant_number
538 -- ,l_npw_number);
539 -- end if;
540 --
541 -- Test current numbers are not used by
542 -- the system already.
543 --
544 hr_person.validate_unique_number(p_person_id =>p_person_id
545 , p_business_group_id => p_business_group_id
546 , p_employee_number => p_employee_number
547 , p_applicant_number => p_applicant_number
548 , p_npw_number => null --p_npw_number
549 , p_current_employee => p_current_employee_flag
550 , p_current_applicant => p_current_applicant_flag
551 , p_current_npw => null --p_current_npw_flag
552 );
553 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',55);
554 -- VT 12/05/96 bug #418637
555 if l_warn_ee = 'Y' then
556 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',60);
557 p_status := 'RECUR_ENT_CHK'; -- Set status to next reentry point.
558 else
559 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',65);
560 p_status := 'VACANCY_CHECK'; -- Set status to next possible reentry point.
561 end if;
562 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',70);
563
564 --Start of fix for Bug 2167668
565
566 IF p_start_date > p_hold_applicant_date_until THEN
567 hr_utility.set_message('800', 'PER_289796_HOLD_UNTIL_DATE');
568 hr_utility.set_message_token('HOLD_DATE', p_start_date );
569 hr_utility.raise_error;
570 END IF;
571
572 -- End of fix for Bug 2167668
573 end if; -- End the First in section
574 --
575 -- VT 12/05/96 bug #418637
576 if p_status = 'RECUR_ENT_CHK' then
577 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',75);
578 return;
579 end if; -- End of RECUR_ENT_CHK
580 --
581 --
582 -- Start of Person type changes.
583 --
584 -- Has the Person type changed to become that of an applicant?
585 --
586 if (p_system_person_type ='APL'
587 and p_s_system_person_type = 'OTHER')
588 or (p_system_person_type = 'APL_EX_APL'
589 and p_s_system_person_type = 'EX_APL')
590 or (p_system_person_type = 'EMP_APL'
591 and p_s_system_person_type = 'EMP')
592 or (p_system_person_type = 'EX_EMP_APL'
593 and p_s_system_person_type = 'EX_EMP') then
594 --
595 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',80);
596 l_create_apl := 'Y';
597 -- 3652025 >> this process is replaced by call to
598 -- hr_applicant_internal.create_applicant_anytime() procedure.
599 -- called directly from PERWSEPI.pld
600 --
601 -- Ensure no future person_type_changes.
602 --
603 -- if hr_person.chk_future_person_type(p_s_system_person_type
604 -- ,p_person_id
605 -- ,p_business_group_id
606 -- ,p_effective_start_date) then
607 -- fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
608 -- app_exception.raise_exception;
609 --end if;
610 --
611 -- Ensure there are no future applicant assignments
612 --
613 --per_people3_pkg.check_future_apl(p_person_id => p_person_id
614 -- ,p_hire_date => greatest(p_hire_date
615 --,p_effective_start_date));
616 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',85);
617 --
618 -- Insert the default applicant row and applicant
619 -- assignment.
620 --
621 -- VT 08/13/96
622 --per_people9_pkg.insert_applicant_rows(p_person_id => p_person_id
623 -- ,p_effective_start_date => p_effective_start_date
624 -- ,p_effective_end_date => p_effective_end_date
625 -- ,p_business_group_id =>p_business_group_id
626 -- ,p_app_ass_status_type_id => p_app_ass_status_type_id
627 -- ,p_request_id => p_request_id
628 -- ,p_program_application_id => p_program_application_id
629 -- ,p_program_id => p_program_id
630 -- ,p_program_update_date => p_program_update_date
631 -- ,p_last_update_date => p_last_update_date
632 -- ,p_last_updated_by => p_last_updated_by
633 -- ,p_last_update_login => p_last_update_login
634 -- ,p_created_by => p_created_by
635 -- ,p_creation_date => p_creation_date);
636 --
637 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',90);
638 --
639 -- PTU Changes
640
641 --hr_per_type_usage_internal.maintain_person_type_usage
642 -- (p_effective_date => p_effective_start_date
643 -- ,p_person_id => p_person_id
644 -- ,p_person_type_id => p_person_type_id
645 -- );
646 --
647 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',92);
648 -- PTU Changes
649 -- <<
650 -- Has the Person type changed to become that of an employee
651 -- when the previous type is not a current applicant?
652 --
653 elsif (p_system_person_type = 'EMP'
654 and ( p_s_system_person_type = 'OTHER'
655 or p_s_system_person_type = 'EX_EMP'
656 or p_s_system_person_type = 'EX_APL')) then /* Bug 523924 */
657 -- or p_s_system_person_type = 'EX_EMP')) then
658 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',95);
659 --
660 -- Ensure no future person_type_changes.
661 --
662 if hr_person.fpt_check_ft_person_type(p_s_system_person_type
663 ,p_person_id
664 ,p_business_group_id
665 ,'Y'
666 ,p_session_date) then
667 --changes for bug no 6070935
668 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
669 app_exception.raise_exception;
670 end if;
671 --
672 if p_s_system_person_type = 'EX_EMP'
673 then
674 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',100);
675 --
676 -- Bug 3154253 stars here.
677 -- Passed earlier of p_ession_date and p_hire_date
678 -- (p_effective_start_date) to the check_hire procedure.
679 --
680 if p_session_date < p_effective_start_date then
681 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',101);
682 per_people12_pkg.check_rehire(p_person_id, p_session_date);
683 else
684 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',102);
685 per_people12_pkg.check_rehire(p_person_id, p_hire_date);
686 end if;
687 --
688 -- bug 3154253 ends here.
689 --
690 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',105);
691 end if;
692 per_people12_pkg.check_future_changes(p_person_id
693 ,p_effective_start_date);
694 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',110);
695 --
696 -- Ensure there are no future applicant assignments
697 --
698 /* per_people3_pkg.check_future_apl(p_person_id => p_person_id
699 ,p_hire_date => greatest(p_hire_date
700 p_effective_start_date));* commented for bug 5403222*/
701 --fix for bug 6600075
702 l_max_ele := p_tab.COUNT;
703 -- ER FPT
704 if l_max_ele > 0 then
705 per_people3_pkg.fpt_check_future_apl(p_person_id => p_person_id
706 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
707 ,p_table=>p_tab);
708 else
709 per_people3_pkg.fpt_check_future_apl(p_person_id => p_person_id
710 ,p_hire_date => greatest(p_hire_date,p_effective_start_date));
711 end if;
712
713 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',115);
714 --
715 -- Insert the default period_of service and assignment
716 -- rows.
717 --
718 -- VT 08/13/96
719 per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
720 ,p_effective_start_date => p_effective_start_date
721 ,p_effective_end_date => hr_api.g_eot -- ER FPT
722 ,p_business_group_id =>p_business_group_id
723 ,p_emp_ass_status_type_id => p_emp_ass_status_type_id
724 ,p_employee_number => p_employee_number
725 ,p_request_id => p_request_id
726 ,p_program_application_id => p_program_application_id
727 ,p_program_id => p_program_id
728 ,p_program_update_date => p_program_update_date
729 ,p_last_update_date => p_last_update_date
730 ,p_last_updated_by => p_last_updated_by
731 ,p_last_update_login => p_last_update_login
732 ,p_created_by => p_created_by
733 ,p_creation_date => p_creation_date
734 ,p_adjusted_svc_date => p_adjusted_svc_date);
735 --
736 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',120);
737
738 -- PTU Changes
739
740 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
741 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_effective_start_date);
742 if p_s_system_person_type in ('EX_EMP','EX_EMP_APL') -- Bug 3637893
743 and p_system_person_type = 'EMP'
744 -- and p_session_date = p_effective_start_date then
745 and l_back2back then
746 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1201);
747 hr_per_type_usage_internal.maintain_person_type_usage
748 (p_effective_date => p_effective_start_date
749 ,p_person_id => p_person_id
750 ,p_person_type_id => p_person_type_id
751 ,p_datetrack_update_mode => 'CORRECTION'
752 );
753 else
754 hr_per_type_usage_internal.maintain_person_type_usage
755 (p_effective_date => p_effective_start_date
756 ,p_person_id => p_person_id
757 ,p_person_type_id => p_person_type_id
758 );
759 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1202);
760 end if;
761
762 -- PTU Changes
763
764 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',121);
765
766 -- Has the Person become an Employee or Employee applicant from being an
767 -- applicant or employee applicant?
768 --
769 elsif ((p_system_person_type = 'EMP'
770 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
771 or (p_system_person_type = 'EMP_APL'
772 and p_s_system_person_type in ('APL','EX_EMP_APL')) /* Bug 732598 */
773 or (p_system_person_type = 'EMP'
774 and p_s_system_person_type = 'EMP_APL')) then
775 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',125);
776 --
777 -- Ensure no future person_type_changes.
778 --
779 -- ER FPT
780 -- there should not be any future person type of Employee or CWK
781 if hr_person.fpt_check_ft_person_type(p_s_system_person_type
782 ,p_person_id
783 ,p_business_group_id
784 ,'Y'
785 ,p_effective_start_date) then
786 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
787 hr_utility.raise_error;
788 end if;
789
790 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',126);
791
792 l_max_ele := p_tab.COUNT;
793 if l_max_ele > 0 then
794 -- there should not be any future 'E' asgs
795 -- there should not be any updates to the asgt on which it's hired >= hire date
796 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',127);
797 per_people3_pkg.fpt_check_future_apl(p_person_id => p_person_id
798 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
799 ,p_table=>p_tab);
800
801 else
802 -- there should not be any future 'E' (employee) asgs
803 -- there should not be any updates to the asgt on which it's hired or the appln chosen to End >= hire date
804 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',128);
805 per_people3_pkg.fpt_check_future_apl(p_person_id => p_person_id
806 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
807 ,p_assignment_id => p_new_primary_id);
808 end if;
809
810 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',130);
811 --
812 -- Check if the person have open term_assignment records. These can be
813 -- found by checking if the person have a periods_of_service with
814 -- no FPD and a value for ATD. Bug 2881076
815 per_people12_pkg.check_rehire(p_person_id
816 ,p_hire_date);
817 --
818 -- Check that the change is valid.
819 --
820 if p_status = 'VACANCY_CHECK' then
821 loop
822 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',135);
823 exit when p_status = 'BOOKINGS_EXIST';
824 --
825 -- Check each vacancy,if it is oversubscribed
826 -- l_fire_warning = 'Y', return to client
827 -- displaying relevant message.
828 -- on return l_vacancy_id starts the cursor at the
829 -- relevant point.
830 --
831 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',140);
832 per_people3_pkg.vacancy_chk(p_person_id => p_person_id
833 ,p_fire_warning => l_fire_warning
834 ,p_vacancy_id => p_vacancy_id
835 -- **** Start new code for bug 2264569 ****************
836 ,p_table => p_tab -- #2381925
837 -- **** End new code for bug 2264569 *****************
838 );
839 if l_fire_warning = 'Y' then
840 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',145);
841 return;
842 elsif l_fire_warning = 'N' then
843 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',150);
844 p_status := 'BOOKINGS_EXIST'; -- Set next possible re-entry point.
845 end if;
846 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',155);
847 end loop;
848 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',160);
849 end if; -- End of VACANCY_CHECK
850 --
851 if p_status = 'BOOKINGS_EXIST' then
852 -- VT 09/18/96 #288087, #380280 , #2172590
853 if (per_people3_pkg.chk_events_exist(p_person_id =>p_person_id
854 ,p_business_group_id =>p_business_group_id
855 ,p_hire_date => greatest(p_hire_date,p_session_date))) then
856 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',165);
857 return;
858 else
859 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',170);
860 -- **** Start commented code for bug 2264569 *************************
861 --p_status := 'GET_APPLS'; -- Set next possible re-entry point.
862 -- **** End commented code for bug 2264569 ***************************
863 -- **** Start new code for bug 2264569 ***************************
864 p_status := 'CHOOSE_VAC'; -- Set next possible re-entry point.
865 -- **** End new code for bug 2264569 *********************************
866 end if;
867 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',175);
868 end if;
869 if p_status = 'END_BOOKINGS' then
870 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',180);
871 hrhirapl.end_bookings(p_person_id
872 , p_business_group_id
873 , p_hire_date);
874 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',185);
875 --
876 -- **** START commented code for bug 2264569 *************************** --
877 --p_status := 'GET_APPLS'; -- Set next possible re-entry point.
878 -- **** End commented code for bug 2264569 ************************
879 -- **** Start new code for bug 2264569 ************************
880 p_status := 'CHOOSE_VAC'; -- Set next possible re-entry point.
881 -- **** END new code for bug 2264569 ********************************
882 --
883 end if;
884 --
885 --
886 -- Choose whether to change the Primary assignment
887 -- and which vacancy is to be the primary if so.
888 --
889 hr_utility.set_location('update_row2 - b4 CHOOSE_VAC',1);
890 if p_status = 'CHOOSE_VAC' then
891 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',240);
892 return;
893 end if; --End of CHOOSE_VAC
894 --
895 -- Can now hire the Person
896 -- Note HIRE status can only be set from client form
897 -- as interaction is generally required.
898 --
899 hr_utility.set_location('update_row2 - b4 HIRE',1);
900 -- +-------------------------------------------------------------------+
901 -- +--------- BEGIN: Hire process -------------------------------------+
902 -- +-------------------------------------------------------------------+
903 if p_status = 'HIRE' then
904 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',245);
905
906 -- bug fix 2824664:
907 if p_update_primary = 'Y'
908 and future_pactid_exists(p_person_id, p_effective_start_date)
909 then
910 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',246);
911 fnd_message.set_name('PAY','HR_6591_ASS_ACTIONS_EXIST');
912 hr_utility.raise_error;
913 end if;
914 -- end bug fix 2824664
915 --
916 -- If new is Emp and old was Emp_apl
917 -- then l_emp_emp_apl is set to Y
918 --
919 if p_system_person_type = 'EMP'
920 and p_s_system_person_type = 'EMP_APL' then
921 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',250);
922 l_employ_emp_apl := 'Y';
923 else
924 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',255);
925 l_employ_emp_apl := 'N';
926 end if;
927 --
928 -- Run the employ_applicant stored procedure
929 --
930 hr_utility.set_location('update_row2 - b4 hrhirapl',1);
931 -- **** Start new code for bug 2264569 *****************************
932 -- End date chosen unaccepted applicant assignments
933 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',257);
934 hrhirapl.end_unaccepted_app_assign(p_person_id
935 ,p_business_group_id
936 ,p_legislation_code
937 ,p_session_date
938 ,p_tab);
939 hr_utility.set_location('update_row2 - b4 hrhirapl',2);
940
941 -- **** End new code for bug 2264569 *****************************
942
943 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',259);
944 hrhirapl.employ_applicant(p_person_id
945 ,p_business_group_id
946 ,p_legislation_code
947 ,p_new_primary_id
948 ,p_emp_ass_status_type_id
949 ,p_last_updated_by
950 ,p_last_update_login
951 ,p_effective_start_date
952 ,p_end_of_time
953 ,p_last_update_date
954 ,p_update_primary
955 ,p_employee_number
956 ,l_set_of_books_id
957 ,l_employ_emp_apl
958 ,p_adjusted_svc_date
959 ,p_session_date -- Bug 3564129
960 -- **** Start new code for bug 2264569 ******
961 ,p_tab
962 -- **** End new code for bug 2264569 *******
963 );
964 -- ER FPT
965
966 -- get the current application ID on which the person is hired
967 open get_apln_id;
968 fetch get_apln_id into l_application_id;
969 if get_apln_id%found then
970
971 -- loop through the future dated asgts associated with the application ID
972 for apl in csr_fut_asg_rec (l_application_id)
973 loop
974 l_fpt_hire_flag := 'Y';
975
976 select max(effective_end_date) into l_apl_end_date
977 from per_all_assignments_f
978 where person_id = p_person_id
979 and assignment_id = apl.assignment_id
980 and assignment_type = 'A';
981
982 if l_apl_end_date = hr_api.g_eot then
983 l_apl_end_date := null;
984 end if;
985
986 -- check whether an application exists on the start date of the assignment
987 open chk_apl_exists(apl.effective_start_date);
988 fetch chk_apl_exists into l_cur_apl_id, l_current_apl_end_date;
989
990 -- if application not found create a new application
991 if chk_apl_exists%notfound then
992 SELECT per_applications_s.nextval into l_new_application_id FROM sys.dual;
993 l_cur_apl_id := l_new_application_id;
994 begin
995 INSERT INTO PER_APPLICATIONS(
996 application_id,
997 business_group_id,
998 person_id,
999 date_received,
1000 date_end)
1001 VALUES (l_new_application_id,p_business_group_id,p_person_id,apl.effective_start_date,l_apl_end_date);
1002
1003 if apl.effective_start_date < l_first_apl_date then
1004 l_first_apl_date := apl.effective_start_date;
1005 end if;
1006
1007 exception
1008 when others then
1009 raise;
1010 end;
1011
1012 -- if application is found, then extend the applciation till the end of the asgt's end date
1013 else
1014
1015 if nvl(l_current_apl_end_date,hr_api.g_eot) < nvl(l_apl_end_date,hr_api.g_eot) then
1016 update per_applications
1017 set date_end = l_apl_end_date
1018 where person_id = p_person_id
1019 and application_id = l_cur_apl_id;
1020 end if;
1021
1022 end if;
1023 close chk_apl_exists;
1024
1025 -- update the asgt with the application ID
1026 update per_all_assignments_f
1027 set application_id = l_cur_apl_id
1028 where person_id = p_person_id
1029 and assignment_id = apl.assignment_id
1030 and effective_start_date = apl.effective_start_date
1031 and effective_end_date = apl.effective_end_date;
1032
1033 end loop;
1034 end if;
1035 close get_apln_id;
1036 --ER FPT
1037 hr_utility.set_location('update_row2 - after hrhirapl',2);
1038 hr_utility.set_location('manage PTU records',3);
1039 if p_system_person_type = 'EMP' then
1040 --
1041 -- PTU : Following Code has been added
1042 -- hr_per_type_usage_internal.maintain_ptu(
1043 -- p_action => 'HIRE_APL',
1044 -- p_person_id => p_person_id,
1045 -- p_actual_termination_date => p_effective_start_date-1);
1046 --
1047 -- **** START new code for bug 2264569 ******************************************
1048 -- Update the system person type to EMP_APL if user is keeping active APPLS.
1049 if hr_employee_applicant_api.retain_exists(p_tab) then
1050 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',260);
1051 p_system_person_type := 'EMP_APL';
1052 open c_apl_flag(p_system_person_type);
1053 fetch c_apl_flag into p_current_applicant_flag;
1054 close c_apl_flag;
1055 hr_utility.trace(' current applicant_flag : '||p_current_applicant_flag);
1056 end if;
1057 -- **** END new code for bug 2264569 *******************************************
1058
1059 -- Bug 3637893 Starts
1060 /* hr_per_type_usage_internal.maintain_person_type_usage
1061 (p_effective_date => p_effective_start_date
1062 ,p_person_id => p_person_id
1063 ,p_person_type_id => p_person_type_id
1064 );
1065 */
1066 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
1067 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_effective_start_date);
1068 if p_s_system_person_type in ('EX_EMP','EX_EMP_APL')
1069 and p_system_person_type = 'EMP'
1070 -- and p_session_date = p_effective_start_date then
1071 and l_back2back then
1072 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1211);
1073 hr_per_type_usage_internal.maintain_person_type_usage
1074 (p_effective_date => p_effective_start_date
1075 ,p_person_id => p_person_id
1076 ,p_person_type_id => p_person_type_id
1077 ,p_datetrack_update_mode => 'CORRECTION'
1078 );
1079 else
1080 hr_per_type_usage_internal.maintain_person_type_usage
1081 (p_effective_date => p_effective_start_date
1082 ,p_person_id => p_person_id
1083 ,p_person_type_id => p_person_type_id
1084 );
1085 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',1212);
1086 end if;
1087 -- Bug 3637893 Ends
1088 --
1089 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',260);
1090 -- **** Start new code for bug 2264569 **********************************
1091 if NOT hr_employee_applicant_api.retain_exists(p_tab) then
1092 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',262);
1093 -- **** End new code for bug 2264569 **********************************
1094
1095 -- ER FPT
1096 -- date track mode is set to UPDATE_CHANGE_INSERT when future EX_APL records exist
1097 open chk_fut_ex_apl(greatest(p_hire_date,p_session_date));
1098 fetch chk_fut_ex_apl into l_fut_ex_apl_exists;
1099 if chk_fut_ex_apl%found then
1100 l_datetrack_mode := hr_api.g_update_change_insert;
1101 else
1102 l_datetrack_mode := hr_api.g_update;
1103 end if;
1104
1105 hr_per_type_usage_internal.maintain_person_type_usage
1106 (p_effective_date => p_effective_start_date
1107 ,p_person_id => p_person_id
1108 ,p_person_type_id => hr_person_type_usage_info.get_default_person_type_id
1109 (p_business_group_id
1110 ,'EX_APL')
1111 ,p_datetrack_update_mode => l_datetrack_mode
1112 );
1113 -- **** Start new code for bug 2264569 **********************************
1114 end if;
1115
1116 -- **** End new code for bug 2264569 **********************************
1117 -- PTU : End of changes
1118 --
1119 -- call to ethnicity
1120 open c_person_enthn;
1121 fetch c_person_enthn into l_bgid,l_per_info1;
1122 close c_person_enthn;
1123
1124 hr_utility.set_location('l_bgid call enthn pkg :'||l_bgid,262);
1125 hr_utility.set_location('p_s_system_person_type enthn pkg :'||p_s_system_person_type,262);
1126 hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
1127
1128 if hr_api.return_legislation_code (l_bgid) ='US' and
1129 p_s_system_person_type in ('APL','EX_EMP_APL') and l_per_info1 is not null then
1130
1131 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 call enthn pkg',262);
1132
1133 per_us_eth_orig_mig_pkg.irc_upgrade_ethnic_origin(p_person_id,'Y');
1134 end if;
1135
1136
1137 ---- call to ethnicity
1138
1139 end if; -- End of hire
1140 -- +-------------------------------------------------------------------+
1141 -- +----------- END: Hire process -------------------------------------+
1142 -- +-------------------------------------------------------------------+
1143 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',265);
1144 end if; -- End of HIRE.
1145 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',270);
1146 end if; -- Of Person type change checks.
1147 --
1148 -- changed p_rowid => null to p_rowid => p_rowid
1149 --
1150 ben_dt_trgr_handle.person(p_rowid => p_rowid
1151 ,p_business_group_id => p_business_group_id
1152 ,p_person_id => p_person_id
1153 ,p_effective_start_date => p_effective_start_date
1154 ,p_effective_end_date => p_effective_end_date
1155 ,p_date_of_birth => p_date_of_birth
1156 ,p_date_of_death => p_date_of_death
1157 ,p_marital_status => p_marital_status
1158 ,p_on_military_service => p_on_military_service
1159 ,p_registered_disabled_flag => p_registered_disabled_flag
1160 ,p_sex => p_sex
1161 ,p_student_status => p_student_status
1162 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
1163 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
1164 ,p_uses_tobacco_flag => p_uses_tobacco_flag
1165 ,p_benefit_group_id => p_benefit_group_id
1166 ,p_per_information10 => p_i10
1167 ,p_original_date_of_hire => p_original_date_of_hire
1168 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
1169 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
1170 ,p_attribute1 => p_a1
1171 ,p_attribute2 =>p_a2
1172 ,p_attribute3 =>p_a3
1173 ,p_attribute4 =>p_a4
1174 ,p_attribute5 =>p_a5
1175 ,p_attribute6 =>p_a6
1176 ,p_attribute7 =>p_a7
1177 ,p_attribute8 =>p_a8
1178 ,p_attribute9 =>p_a9
1179 ,p_attribute10 =>p_a10
1180 ,p_attribute11 =>p_a11
1181 ,p_attribute12 =>p_a12
1182 ,p_attribute13 =>p_a13
1183 ,p_attribute14 =>p_a14
1184 ,p_attribute15 =>p_a15
1185 ,p_attribute16 =>p_a16
1186 ,p_attribute17 =>p_a17
1187 ,p_attribute18 =>p_a18
1188 ,p_attribute19 =>p_a19
1189 ,p_attribute20 =>p_a20
1190 ,p_attribute21 =>p_a21
1191 ,p_attribute22 =>p_a22
1192 ,p_attribute23 =>p_a23
1193 ,p_attribute24 =>p_a24
1194 ,p_attribute25 =>p_a25
1195 ,p_attribute26 =>p_a26
1196 ,p_attribute27 =>p_a27
1197 ,p_attribute28 =>p_a28
1198 ,p_attribute29 =>p_a29
1199 ,p_attribute30 =>p_a30
1200 );
1201 --
1202 if l_party_id is null then
1203
1204 /*
1205 ** We tried to get the party_id at the start of this process however
1206 ** the person may not have had one. If they have undergone a change in
1207 ** person type they may very well have one by now so we'll try and get
1208 ** the current party_id from their person record (this will have been
1209 ** set when the TCAparty was created) if we don't currently have the value.
1210 **
1211 ** Ideally we should get the party_id returned from the PTU maintenance
1212 ** code where it would have been derived but this is not an ideal world
1213 ** so we won't.
1214 */
1215 open c1;
1216 --
1217 fetch c1 into l_party_id;
1218 --
1219 close c1;
1220
1221 end if;
1222
1223 hr_utility.set_location('update_row2 - b4 update',1);
1224 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',272);
1225 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||p_person_type_id,272);
1226 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||p_s_system_person_type,272);
1227 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||p_system_person_type,272);
1228 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2 '||hr_person_type_usage_info.get_default_person_type_id(
1229 p_business_group_id,p_system_person_type),272);
1230
1231 -- Bug 6196362 Starts.
1232 -- update per_people_f ppf
1233 update per_all_people_f ppf
1234 -- Bug 6196362 Ends.
1235 set ppf.person_id = p_person_id
1236 ,ppf.effective_start_date = p_effective_start_date
1237 ,ppf.effective_end_date = p_effective_end_date
1238 ,ppf.business_group_id = p_business_group_id
1239 -- ,ppf.person_type_id = p_person_type_id
1240 --,ppf.person_type_id =hr_person_type_usage_info.get_default_person_type_id( p_business_group_id ,p_system_person_type) bug 6848958
1241 ,ppf.person_type_id =decode( p_system_person_type,'CWK',
1242 hr_person_type_usage_info.get_default_person_type_id(
1243 p_business_group_id,'OTHER'),
1244 hr_person_type_usage_info.get_default_person_type_id(
1245 p_business_group_id,p_system_person_type)) -- fix for bug6848958
1246 ,ppf.last_name = p_last_name
1247 ,ppf.start_date = p_start_date
1248 ,ppf.applicant_number = p_applicant_number
1249 ,ppf.comment_id = p_comment_id
1250 ,ppf.current_applicant_flag = p_current_applicant_flag
1251 ,ppf.current_emp_or_apl_flag = p_current_emp_or_apl_flag
1252 ,ppf.current_employee_flag = p_current_employee_flag
1253 ,ppf.date_employee_data_verified = p_date_employee_data_verified
1254 ,ppf.date_of_birth = p_date_of_birth
1255 ,ppf.email_address = p_email_address
1256 ,ppf.employee_number = p_employee_number
1257 ,ppf.expense_check_send_to_address = p_expense_check_send_to_addr
1258 ,ppf.first_name = p_first_name
1259 ,ppf.full_name = p_full_name
1260 ,ppf.known_as = p_known_as
1261 ,ppf.marital_status = p_marital_status
1262 ,ppf.middle_names = p_middle_names
1263 ,ppf.nationality = p_nationality
1264 ,ppf.national_identifier = p_national_identifier
1265 ,ppf.previous_last_name = p_previous_last_name
1266 ,ppf.registered_disabled_flag = p_registered_disabled_flag
1267 ,ppf.sex = p_sex
1268 ,ppf.title = p_title
1269 ,ppf.suffix = p_suffix
1270 ,ppf.vendor_id = p_vendor_id
1271 -- ,ppf.work_telephone = p_work_telephone
1272 ,ppf.request_id = p_request_id
1273 ,ppf.program_application_id = p_program_application_id
1274 ,ppf.program_id = p_program_id
1275 ,ppf.program_update_date = p_program_update_date
1276 ,ppf.attribute_category = p_a_cat
1277 ,ppf.attribute1 = p_a1
1278 ,ppf.attribute2 = p_a2
1279 ,ppf.attribute3 = p_a3
1280 ,ppf.attribute4 = p_a4
1281 ,ppf.attribute5 = p_a5
1282 ,ppf.attribute6 = p_a6
1283 ,ppf.attribute7 = p_a7
1284 ,ppf.attribute8 = p_a8
1285 ,ppf.attribute9 = p_a9
1286 ,ppf.attribute10 = p_a10
1287 ,ppf.attribute11 = p_a11
1288 ,ppf.attribute12 = p_a12
1289 ,ppf.attribute13 = p_a13
1290 ,ppf.attribute14 = p_a14
1291 ,ppf.attribute15 = p_a15
1292 ,ppf.attribute16 = p_a16
1293 ,ppf.attribute17 = p_a17
1294 ,ppf.attribute18 = p_a18
1295 ,ppf.attribute19 = p_a19
1296 ,ppf.attribute20 = p_a20
1297 ,ppf.attribute21 = p_a21
1298 ,ppf.attribute22 = p_a22
1299 ,ppf.attribute23 = p_a23
1300 ,ppf.attribute24 = p_a24
1301 ,ppf.attribute25 = p_a25
1302 ,ppf.attribute26 = p_a26
1303 ,ppf.attribute27 = p_a27
1304 ,ppf.attribute28 = p_a28
1305 ,ppf.attribute29 = p_a29
1306 ,ppf.attribute30 = p_a30
1307 ,ppf.last_update_date = p_last_update_date
1308 ,ppf.last_updated_by = p_last_updated_by
1309 ,ppf.last_update_login = p_last_update_login
1310 ,ppf.created_by = p_created_by
1311 ,ppf.creation_date = p_creation_date
1312 ,ppf.per_information_category = p_i_cat
1313 ,ppf.per_information1 = p_i1
1314 ,ppf.per_information2 = p_i2
1315 ,ppf.per_information3 = p_i3
1316 ,ppf.per_information4 = p_i4
1317 ,ppf.per_information5 = p_i5
1318 ,ppf.per_information6 = p_i6
1319 ,ppf.per_information7 = p_i7
1320 ,ppf.per_information8 = p_i8
1321 ,ppf.per_information9 = p_i9
1322 ,ppf.per_information10 = p_i10
1323 ,ppf.per_information11 = p_i11
1324 ,ppf.per_information12 = p_i12
1325 ,ppf.per_information13 = p_i13
1326 ,ppf.per_information14 = p_i14
1327 ,ppf.per_information15 = p_i15
1328 ,ppf.per_information16 = p_i16
1329 ,ppf.per_information17 = p_i17
1330 ,ppf.per_information18 = p_i18
1331 ,ppf.per_information19 = p_i19
1332 ,ppf.per_information20 = p_i20
1333 ,ppf.per_information21 = p_i21
1334 ,ppf.per_information22 = p_i22
1335 ,ppf.per_information23 = p_i23
1336 ,ppf.per_information24 = p_i24
1337 ,ppf.per_information25 = p_i25
1338 ,ppf.per_information26 = p_i26
1339 ,ppf.per_information27 = p_i27
1340 ,ppf.per_information28 = p_i28
1341 ,ppf.per_information29 = p_i29
1342 ,ppf.per_information30 = p_i30
1343 ,ppf.work_schedule = p_work_schedule
1344 ,ppf.correspondence_language = p_correspondence_language
1345 ,ppf.student_status = p_student_status
1346 ,ppf.fte_capacity = p_fte_capacity
1347 ,ppf.on_military_service = p_on_military_service
1348 ,ppf.second_passport_exists = p_second_passport_exists
1349 ,ppf.background_check_status = p_background_check_status
1350 ,ppf.background_date_check = p_background_date_check
1351 ,ppf.blood_type = p_blood_type
1352 ,ppf.last_medical_test_date = p_last_medical_test_date
1353 ,ppf.last_medical_test_by = p_last_medical_test_by
1354 ,ppf.rehire_recommendation = p_rehire_recommendation
1355 ,ppf.rehire_reason = p_rehire_reason
1356 ,ppf.resume_exists = p_resume_exists
1357 ,ppf.resume_last_updated = p_resume_last_updated
1358 ,ppf.office_number = p_office_number
1359 ,ppf.internal_location = p_internal_location
1360 ,ppf.mailstop = p_mailstop
1361 ,ppf.honors = p_honors
1362 ,ppf.pre_name_adjunct = p_pre_name_adjunct
1363 ,ppf.hold_applicant_date_until = p_hold_applicant_date_until
1364 ,ppf.benefit_group_id = p_benefit_group_id
1365 ,ppf.receipt_of_death_cert_date = p_receipt_of_death_cert_date
1366 ,ppf.coord_ben_med_pln_no = p_coord_ben_med_pln_no
1367 ,ppf.coord_ben_no_cvg_flag = p_coord_ben_no_cvg_flag
1368 ,ppf.uses_tobacco_flag = p_uses_tobacco_flag
1369 ,ppf.dpdnt_adoption_date = p_dpdnt_adoption_date
1370 ,ppf.dpdnt_vlntry_svce_flag = p_dpdnt_vlntry_svce_flag
1371 ,ppf.date_of_death = p_date_of_death
1372 ,ppf.original_date_of_hire = p_original_date_of_hire
1373 ,ppf.town_of_birth = p_town_of_birth
1374 ,ppf.region_of_birth = p_region_of_birth
1375 ,ppf.country_of_birth = p_country_of_birth
1376 ,ppf.global_person_id = p_global_person_id
1377 ,ppf.party_id = l_party_id
1378 ,ppf.npw_number = p_npw_number
1379 ,ppf.current_npw_flag = p_current_npw_flag
1380 ,ppf.order_name = p_order_name -- #3889584
1381 ,ppf.global_name = p_global_name
1382 ,ppf.local_name = p_local_name
1383 where ppf.rowid = p_rowid;
1384 --
1385 if sql%rowcount <1 then
1386 hr_utility.set_message(801,'HR_6001_ALL_MANDATORY_FIELD');
1387 hr_utility.set_message_token('MISSING_FIELD','rowid is'||p_rowid);
1388 hr_utility.raise_error;
1389 end if;
1390 --
1391
1392 -- Start of Fix #2447513
1393 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',275);
1394
1395 --End of Fix
1396 -- ER FPT
1397 -- correct per table data
1398
1399 IF p_effective_end_date <> hr_api.g_eot and l_create_apl <> 'Y' THEN
1400
1401 SELECT ppt.system_person_type
1402 INTO l_next_person_type
1403 FROM per_all_people_f per, per_person_types ppt
1404 WHERE per.person_id = p_person_id
1405 AND per.person_type_id = ppt.person_type_id
1406 AND per.effective_start_date = p_effective_end_date + 1;
1407
1408
1409 IF l_next_person_type in ('EMP','EX_APL','EX_EMP') then
1410
1411 UPDATE per_all_people_f
1412 SET effective_end_date = (
1413 SELECT effective_end_date
1414 FROM per_all_people_f
1415 WHERE effective_start_date = p_effective_end_date + 1
1416 AND person_id = p_person_id
1417 )
1418 WHERE rowid = p_rowid;
1419
1420 DELETE
1421 FROM per_all_people_f
1422 WHERE person_id = p_person_id
1423 AND effective_start_date = p_effective_end_date + 1;
1424
1425 END IF;
1426
1427 -- All the future APL & EX_EMP_APL records will be converted to EMP_APL
1428 UPDATE per_all_people_f
1429 SET person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EMP_APL')
1430 ,current_employee_flag = 'Y'
1431 ,employee_number = p_employee_number
1432 ,original_date_of_hire = p_original_date_of_hire
1433 WHERE person_id = p_person_id
1434 AND effective_start_date > p_effective_end_date
1435 AND (person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'APL')
1436 OR person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EX_EMP_APL'));
1437
1438 -- All the future EX_APL & EX_EMP records will be converted to EMP
1439 UPDATE per_all_people_f
1440 SET person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EMP')
1441 ,current_employee_flag = 'Y'
1442 ,current_emp_or_apl_flag = 'Y'
1443 ,employee_number = p_employee_number
1444 ,original_date_of_hire = p_original_date_of_hire
1445 WHERE person_id = p_person_id
1446 AND effective_start_date > p_effective_end_date
1447 AND (person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EX_APL')
1448 OR person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EX_EMP'));
1449
1450 END IF;
1451
1452 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',276);
1453
1454 if nvl(l_fpt_hire_flag,'N') = 'Y' then
1455
1456 if l_first_apl_date <> hr_api.g_eot then
1457
1458 update per_all_people_f
1459 set person_type_id = hr_person_type_usage_info.get_default_person_type_id(p_business_group_id,'EMP')
1460 ,current_applicant_flag = null
1461 where person_id = p_person_id
1462 and effective_start_date between p_effective_end_date and l_first_apl_date;
1463
1464 end if;
1465
1466 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',277);
1467
1468 -- loop through the future applications > hire date
1469 for apl in csr_get_apln_details(greatest(p_hire_date,p_session_date))
1470 loop
1471
1472 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1473 (p_business_group_id,'APL');
1474
1475 -- creating APL records in PTU table
1476 hr_per_type_usage_internal.maintain_person_type_usage
1477 (p_effective_date => apl.date_received
1478 ,p_person_id => p_person_id
1479 ,p_person_type_id => l_person_type_id1);
1480
1481 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1482 (p_business_group_id,'EMP_APL');
1483
1484 select object_version_number,effective_start_date into l_per_latest_ovn,l_effective_start_date
1485 from per_all_people_f
1486 where person_id = p_person_id
1487 and apl.date_received between effective_start_date and effective_end_date;
1488
1489 -- creating EMP_APL records in PER table
1490
1491 if l_effective_start_date = apl.date_received then
1492 l_datetrack_mode := 'CORRECTION';
1493 else
1494 open per_future_rows(l_effective_start_date);
1495 fetch per_future_rows into l_exists;
1496 if per_future_rows%found then
1497 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1498 else
1499 l_datetrack_mode := 'UPDATE';
1500 end if;
1501 close per_future_rows;
1502 end if;
1503
1504 open person_record(apl.date_received);
1505 fetch person_record into per_rec;
1506 close person_record;
1507
1508 per_per_upd.upd
1509 (p_person_id => p_person_id
1510 ,p_effective_start_date => l_per_effective_start_date
1511 ,p_effective_end_date => l_per_effective_end_date
1512 ,p_person_type_id => l_person_type_id1
1513 ,p_last_name => per_rec.last_name
1514 ,p_start_date => per_rec.start_date
1515 ,p_applicant_number => p_applicant_number
1516 ,p_comment_id => l_comment_id
1517 ,p_current_applicant_flag => l_current_applicant_flag
1518 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
1519 ,p_current_employee_flag => l_current_employee_flag
1520 ,p_date_employee_data_verified => per_rec.date_employee_data_verified
1521 ,p_date_of_birth => per_rec.date_of_birth
1522 ,p_email_address => per_rec.email_address
1523 ,p_employee_number => p_employee_number
1524 ,p_expense_check_send_to_addres => per_rec.EXPENSE_CHECK_SEND_TO_ADDRESS
1525 ,p_first_name => per_rec.first_name
1526 ,p_full_name => l_full_name
1527 ,p_known_as => per_rec.known_as
1528 ,p_marital_status => per_rec.marital_status
1529 ,p_middle_names => per_rec.middle_names
1530 ,p_nationality => per_rec.nationality
1531 ,p_national_identifier => per_rec.national_identifier
1532 ,p_previous_last_name => per_rec.previous_last_name
1533 ,p_registered_disabled_flag => per_rec.registered_disabled_flag
1534 ,p_sex => per_rec.sex
1535 ,p_title => per_rec.title
1536 ,p_vendor_id => per_rec.vendor_id
1537 ,p_work_telephone => per_rec.work_telephone
1538 ,p_request_id => per_rec.request_id
1539 ,p_program_application_id => per_rec.program_application_id
1540 ,p_program_id => per_rec.program_id
1541 ,p_program_update_date => per_rec.program_update_date
1542 ,p_attribute_category => per_rec.attribute_category
1543 ,p_attribute1 => per_rec.attribute1
1544 ,p_attribute2 => per_rec.attribute2
1545 ,p_attribute3 => per_rec.attribute3
1546 ,p_attribute4 => per_rec.attribute4
1547 ,p_attribute5 => per_rec.attribute5
1548 ,p_attribute6 => per_rec.attribute6
1549 ,p_attribute7 => per_rec.attribute7
1550 ,p_attribute8 => per_rec.attribute8
1551 ,p_attribute9 => per_rec.attribute9
1552 ,p_attribute10 => per_rec.attribute10
1553 ,p_attribute11 => per_rec.attribute11
1554 ,p_attribute12 => per_rec.attribute12
1555 ,p_attribute13 => per_rec.attribute13
1556 ,p_attribute14 => per_rec.attribute14
1557 ,p_attribute15 => per_rec.attribute15
1558 ,p_attribute16 => per_rec.attribute16
1559 ,p_attribute17 => per_rec.attribute17
1560 ,p_attribute18 => per_rec.attribute18
1561 ,p_attribute19 => per_rec.attribute19
1562 ,p_attribute20 => per_rec.attribute20
1563 ,p_attribute21 => per_rec.attribute21
1564 ,p_attribute22 => per_rec.attribute22
1565 ,p_attribute23 => per_rec.attribute23
1566 ,p_attribute24 => per_rec.attribute24
1567 ,p_attribute25 => per_rec.attribute25
1568 ,p_attribute26 => per_rec.attribute26
1569 ,p_attribute27 => per_rec.attribute27
1570 ,p_attribute28 => per_rec.attribute28
1571 ,p_attribute29 => per_rec.attribute29
1572 ,p_attribute30 => per_rec.attribute30
1573 ,p_per_information_category => per_rec.per_information_category
1574 ,p_per_information1 => per_rec.per_information1
1575 ,p_per_information2 => per_rec.per_information2
1576 ,p_per_information3 => per_rec.per_information3
1577 ,p_per_information4 => per_rec.per_information4
1578 ,p_per_information5 => per_rec.per_information5
1579 ,p_per_information6 => per_rec.per_information6
1580 ,p_per_information7 => per_rec.per_information7
1581 ,P_per_information8 => per_rec.per_information8
1582 ,p_per_information9 => per_rec.per_information9
1583 ,p_per_information10 => per_rec.per_information10
1584 ,p_per_information11 => per_rec.per_information11
1585 ,p_per_information12 => per_rec.per_information12
1586 ,p_per_information13 => per_rec.per_information13
1587 ,p_per_information14 => per_rec.per_information14
1588 ,p_per_information15 => per_rec.per_information15
1589 ,p_per_information16 => per_rec.per_information16
1590 ,p_per_information17 => per_rec.per_information17
1591 ,p_per_information18 => per_rec.per_information18
1592 ,p_per_information19 => per_rec.per_information19
1593 ,p_per_information20 => per_rec.per_information20
1594 ,p_PER_INFORMATION21 => per_rec.per_information21
1595 ,p_PER_INFORMATION22 => per_rec.per_information22
1596 ,p_PER_INFORMATION23 => per_rec.per_information23
1597 ,p_PER_INFORMATION24 => per_rec.per_information24
1598 ,p_PER_INFORMATION25 => per_rec.per_information25
1599 ,p_PER_INFORMATION26 => per_rec.per_information26
1600 ,p_PER_INFORMATION27 => per_rec.per_information27
1601 ,p_PER_INFORMATION28 => per_rec.per_information28
1602 ,p_PER_INFORMATION29 => per_rec.per_information29
1603 ,p_PER_INFORMATION30 => per_rec.per_information30
1604 ,p_suffix => per_rec.suffix
1605 ,p_DATE_OF_DEATH => per_rec.DATE_OF_DEATH
1606 ,p_BACKGROUND_CHECK_STATUS => per_rec.BACKGROUND_CHECK_STATUS
1607 ,p_BACKGROUND_DATE_CHECK => per_rec.BACKGROUND_DATE_CHECK
1608 ,p_BLOOD_TYPE => per_rec.BLOOD_TYPE
1609 ,p_CORRESPONDENCE_LANGUAGE => per_rec.CORRESPONDENCE_LANGUAGE
1610 ,p_FTE_CAPACITY => per_rec.FTE_CAPACITY
1611 ,p_HOLD_APPLICANT_DATE_UNTIL => per_rec.HOLD_APPLICANT_DATE_UNTIL
1612 ,p_HONORS => per_rec.HONORS
1613 ,p_INTERNAL_LOCATION => per_rec.INTERNAL_LOCATION
1614 ,p_LAST_MEDICAL_TEST_BY => per_rec.LAST_MEDICAL_TEST_BY
1615 ,p_LAST_MEDICAL_TEST_DATE => per_rec.LAST_MEDICAL_TEST_DATE
1616 ,p_MAILSTOP => per_rec.MAILSTOP
1617 ,p_OFFICE_NUMBER => per_rec.OFFICE_NUMBER
1618 ,p_ON_MILITARY_SERVICE => per_rec.ON_MILITARY_SERVICE
1619 ,p_ORDER_NAME => per_rec.ORDER_NAME
1620 ,p_PRE_NAME_ADJUNCT => per_rec.PRE_NAME_ADJUNCT
1621 ,p_REHIRE_RECOMMENDATION => per_rec.REHIRE_RECOMMENDATION
1622 ,p_RESUME_EXISTS => per_rec.RESUME_EXISTS
1623 ,p_RESUME_LAST_UPDATED => per_rec.RESUME_LAST_UPDATED
1624 ,p_SECOND_PASSPORT_EXISTS => per_rec.SECOND_PASSPORT_EXISTS
1625 ,p_STUDENT_STATUS => per_rec.STUDENT_STATUS
1626 ,p_WORK_SCHEDULE => per_rec.WORK_SCHEDULE
1627 ,p_REHIRE_REASON => per_rec.REHIRE_REASON
1628 ,p_BENEFIT_GROUP_ID => per_rec.BENEFIT_GROUP_ID
1629 ,p_RECEIPT_OF_DEATH_CERT_DATE => per_rec.RECEIPT_OF_DEATH_CERT_DATE
1630 ,p_COORD_BEN_MED_PLN_NO => per_rec.COORD_BEN_MED_PLN_NO
1631 ,p_COORD_BEN_NO_CVG_FLAG => per_rec.COORD_BEN_NO_CVG_FLAG
1632 ,p_USES_TOBACCO_FLAG => per_rec.USES_TOBACCO_FLAG
1633 ,p_DPDNT_ADOPTION_DATE => per_rec.DPDNT_ADOPTION_DATE
1634 ,p_DPDNT_VLNTRY_SVCE_FLAG => per_rec.DPDNT_VLNTRY_SVCE_FLAG
1635 ,p_ORIGINAL_DATE_OF_HIRE => per_rec.ORIGINAL_DATE_OF_HIRE
1636 ,p_town_of_birth => per_rec.town_of_birth
1637 ,p_region_of_birth => per_rec.region_of_birth
1638 ,p_country_of_birth => per_rec.country_of_birth
1639 ,p_global_person_id => per_rec.global_person_id
1640 ,p_party_id => per_rec.party_id
1641 ,p_npw_number => per_rec.npw_number
1642 ,p_current_npw_flag => per_rec.current_npw_flag
1643 ,p_object_version_number => l_per_latest_ovn
1644 ,p_effective_date => apl.date_received
1645 ,p_datetrack_mode => l_datetrack_mode
1646 ,p_name_combination_warning => l_name_combination_warning
1647 ,p_dob_null_warning => l_assign_payroll_warning
1648 ,p_orig_hire_warning => l_orig_hire_warning
1649 );
1650
1651 if nvl(apl.date_end,hr_api.g_eot) <> hr_api.g_eot then
1652
1653 -- check whether back-to-back application exists
1654
1655 open csr_back_toback_apln(apl.date_end) ;
1656 fetch csr_back_toback_apln into l_yes_backtoback;
1657 close csr_back_toback_apln;
1658
1659 if l_yes_backtoback ='N' then
1660
1661 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1662 (p_business_group_id,'EX_APL');
1663
1664 -- creating EX_APL records in PTU table
1665 hr_per_type_usage_internal.maintain_person_type_usage
1666 (p_effective_date => apl.date_end + 1
1667 ,p_person_id => p_person_id
1668 ,p_person_type_id => l_person_type_id1
1669 );
1670
1671 select object_version_number,effective_start_date into l_per_latest_ovn,l_effective_start_date
1672 from per_all_people_f
1673 where person_id= p_person_id
1674 and apl.date_end + 1 between effective_start_date and effective_end_date;
1675
1676 if l_effective_start_date = apl.date_end + 1 then
1677 l_datetrack_mode := 'CORRECTION';
1678 else
1679 open per_future_rows(l_effective_start_date);
1680 fetch per_future_rows into l_exists;
1681 if per_future_rows%found then
1682 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1683 else
1684 l_datetrack_mode := 'UPDATE';
1685 end if;
1686 close per_future_rows;
1687 end if;
1688
1689 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1690 (p_business_group_id,'EMP');
1691
1692
1693 -- creating EMP records in PER table
1694
1695 open person_record(apl.date_end+1);
1696 fetch person_record into per_rec;
1697 close person_record;
1698
1699 per_per_upd.upd
1700 (p_person_id => p_person_id
1701 ,p_effective_start_date => l_per_effective_start_date
1702 ,p_effective_end_date => l_per_effective_end_date
1703 ,p_person_type_id => l_person_type_id1
1704 ,p_last_name => per_rec.last_name
1705 ,p_start_date => per_rec.start_date
1706 ,p_applicant_number => p_applicant_number
1707 ,p_comment_id => l_comment_id
1708 ,p_current_applicant_flag => l_current_applicant_flag
1709 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
1710 ,p_current_employee_flag => l_current_employee_flag
1711 ,p_date_employee_data_verified => per_rec.date_employee_data_verified
1712 ,p_date_of_birth => per_rec.date_of_birth
1713 ,p_email_address => per_rec.email_address
1714 ,p_employee_number => p_employee_number
1715 ,p_expense_check_send_to_addres => per_rec.EXPENSE_CHECK_SEND_TO_ADDRESS
1716 ,p_first_name => per_rec.first_name
1717 ,p_full_name => l_full_name
1718 ,p_known_as => per_rec.known_as
1719 ,p_marital_status => per_rec.marital_status
1720 ,p_middle_names => per_rec.middle_names
1721 ,p_nationality => per_rec.nationality
1722 ,p_national_identifier => per_rec.national_identifier
1723 ,p_previous_last_name => per_rec.previous_last_name
1724 ,p_registered_disabled_flag => per_rec.registered_disabled_flag
1725 ,p_sex => per_rec.sex
1726 ,p_title => per_rec.title
1727 ,p_vendor_id => per_rec.vendor_id
1728 ,p_work_telephone => per_rec.work_telephone
1729 ,p_request_id => per_rec.request_id
1730 ,p_program_application_id => per_rec.program_application_id
1731 ,p_program_id => per_rec.program_id
1732 ,p_program_update_date => per_rec.program_update_date
1733 ,p_attribute_category => per_rec.attribute_category
1734 ,p_attribute1 => per_rec.attribute1
1735 ,p_attribute2 => per_rec.attribute2
1736 ,p_attribute3 => per_rec.attribute3
1737 ,p_attribute4 => per_rec.attribute4
1738 ,p_attribute5 => per_rec.attribute5
1739 ,p_attribute6 => per_rec.attribute6
1740 ,p_attribute7 => per_rec.attribute7
1741 ,p_attribute8 => per_rec.attribute8
1742 ,p_attribute9 => per_rec.attribute9
1743 ,p_attribute10 => per_rec.attribute10
1744 ,p_attribute11 => per_rec.attribute11
1745 ,p_attribute12 => per_rec.attribute12
1746 ,p_attribute13 => per_rec.attribute13
1747 ,p_attribute14 => per_rec.attribute14
1748 ,p_attribute15 => per_rec.attribute15
1749 ,p_attribute16 => per_rec.attribute16
1750 ,p_attribute17 => per_rec.attribute17
1751 ,p_attribute18 => per_rec.attribute18
1752 ,p_attribute19 => per_rec.attribute19
1753 ,p_attribute20 => per_rec.attribute20
1754 ,p_attribute21 => per_rec.attribute21
1755 ,p_attribute22 => per_rec.attribute22
1756 ,p_attribute23 => per_rec.attribute23
1757 ,p_attribute24 => per_rec.attribute24
1758 ,p_attribute25 => per_rec.attribute25
1759 ,p_attribute26 => per_rec.attribute26
1760 ,p_attribute27 => per_rec.attribute27
1761 ,p_attribute28 => per_rec.attribute28
1762 ,p_attribute29 => per_rec.attribute29
1763 ,p_attribute30 => per_rec.attribute30
1764 ,p_per_information_category => per_rec.per_information_category
1765 ,p_per_information1 => per_rec.per_information1
1766 ,p_per_information2 => per_rec.per_information2
1767 ,p_per_information3 => per_rec.per_information3
1768 ,p_per_information4 => per_rec.per_information4
1769 ,p_per_information5 => per_rec.per_information5
1770 ,p_per_information6 => per_rec.per_information6
1771 ,p_per_information7 => per_rec.per_information7
1772 ,P_per_information8 => per_rec.per_information8
1773 ,p_per_information9 => per_rec.per_information9
1774 ,p_per_information10 => per_rec.per_information10
1775 ,p_per_information11 => per_rec.per_information11
1776 ,p_per_information12 => per_rec.per_information12
1777 ,p_per_information13 => per_rec.per_information13
1778 ,p_per_information14 => per_rec.per_information14
1779 ,p_per_information15 => per_rec.per_information15
1780 ,p_per_information16 => per_rec.per_information16
1781 ,p_per_information17 => per_rec.per_information17
1782 ,p_per_information18 => per_rec.per_information18
1783 ,p_per_information19 => per_rec.per_information19
1784 ,p_per_information20 => per_rec.per_information20
1785 ,p_PER_INFORMATION21 => per_rec.per_information21
1786 ,p_PER_INFORMATION22 => per_rec.per_information22
1787 ,p_PER_INFORMATION23 => per_rec.per_information23
1788 ,p_PER_INFORMATION24 => per_rec.per_information24
1789 ,p_PER_INFORMATION25 => per_rec.per_information25
1790 ,p_PER_INFORMATION26 => per_rec.per_information26
1791 ,p_PER_INFORMATION27 => per_rec.per_information27
1792 ,p_PER_INFORMATION28 => per_rec.per_information28
1793 ,p_PER_INFORMATION29 => per_rec.per_information29
1794 ,p_PER_INFORMATION30 => per_rec.per_information30
1795 ,p_suffix => per_rec.suffix
1796 ,p_DATE_OF_DEATH => per_rec.DATE_OF_DEATH
1797 ,p_BACKGROUND_CHECK_STATUS => per_rec.BACKGROUND_CHECK_STATUS
1798 ,p_BACKGROUND_DATE_CHECK => per_rec.BACKGROUND_DATE_CHECK
1799 ,p_BLOOD_TYPE => per_rec.BLOOD_TYPE
1800 ,p_CORRESPONDENCE_LANGUAGE => per_rec.CORRESPONDENCE_LANGUAGE
1801 ,p_FTE_CAPACITY => per_rec.FTE_CAPACITY
1802 ,p_HOLD_APPLICANT_DATE_UNTIL => per_rec.HOLD_APPLICANT_DATE_UNTIL
1803 ,p_HONORS => per_rec.HONORS
1804 ,p_INTERNAL_LOCATION => per_rec.INTERNAL_LOCATION
1805 ,p_LAST_MEDICAL_TEST_BY => per_rec.LAST_MEDICAL_TEST_BY
1806 ,p_LAST_MEDICAL_TEST_DATE => per_rec.LAST_MEDICAL_TEST_DATE
1807 ,p_MAILSTOP => per_rec.MAILSTOP
1808 ,p_OFFICE_NUMBER => per_rec.OFFICE_NUMBER
1809 ,p_ON_MILITARY_SERVICE => per_rec.ON_MILITARY_SERVICE
1810 ,p_ORDER_NAME => per_rec.ORDER_NAME
1811 ,p_PRE_NAME_ADJUNCT => per_rec.PRE_NAME_ADJUNCT
1812 ,p_REHIRE_RECOMMENDATION => per_rec.REHIRE_RECOMMENDATION
1813 ,p_RESUME_EXISTS => per_rec.RESUME_EXISTS
1814 ,p_RESUME_LAST_UPDATED => per_rec.RESUME_LAST_UPDATED
1815 ,p_SECOND_PASSPORT_EXISTS => per_rec.SECOND_PASSPORT_EXISTS
1816 ,p_STUDENT_STATUS => per_rec.STUDENT_STATUS
1817 ,p_WORK_SCHEDULE => per_rec.WORK_SCHEDULE
1818 ,p_REHIRE_REASON => per_rec.REHIRE_REASON
1819 ,p_BENEFIT_GROUP_ID => per_rec.BENEFIT_GROUP_ID
1820 ,p_RECEIPT_OF_DEATH_CERT_DATE => per_rec.RECEIPT_OF_DEATH_CERT_DATE
1821 ,p_COORD_BEN_MED_PLN_NO => per_rec.COORD_BEN_MED_PLN_NO
1822 ,p_COORD_BEN_NO_CVG_FLAG => per_rec.COORD_BEN_NO_CVG_FLAG
1823 ,p_USES_TOBACCO_FLAG => per_rec.USES_TOBACCO_FLAG
1824 ,p_DPDNT_ADOPTION_DATE => per_rec.DPDNT_ADOPTION_DATE
1825 ,p_DPDNT_VLNTRY_SVCE_FLAG => per_rec.DPDNT_VLNTRY_SVCE_FLAG
1826 ,p_ORIGINAL_DATE_OF_HIRE => per_rec.ORIGINAL_DATE_OF_HIRE
1827 ,p_town_of_birth => per_rec.town_of_birth
1828 ,p_region_of_birth => per_rec.region_of_birth
1829 ,p_country_of_birth => per_rec.country_of_birth
1830 ,p_global_person_id => per_rec.global_person_id
1831 ,p_party_id => per_rec.party_id
1832 ,p_npw_number => per_rec.npw_number
1833 ,p_current_npw_flag => per_rec.current_npw_flag
1834 ,p_object_version_number => l_per_latest_ovn
1835 ,p_effective_date => apl.date_end + 1
1836 ,p_datetrack_mode => l_datetrack_mode
1837 ,p_name_combination_warning => l_name_combination_warning
1838 ,p_dob_null_warning => l_assign_payroll_warning
1839 ,p_orig_hire_warning => l_orig_hire_warning
1840 );
1841
1842
1843 END IF;
1844
1845 end if;
1846
1847 end loop;
1848 elsif p_status = 'HIRE' and p_effective_end_date <> hr_api.g_eot then
1849
1850 UPDATE per_all_people_f
1851 SET person_type_id = hr_person_type_usage_info.get_default_person_type_id (p_business_group_id,'EMP')
1852 ,current_applicant_flag = NULL
1853 WHERE person_id = p_person_id
1854 AND effective_start_date > p_effective_end_date;
1855 end if;
1856 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',278);
1857 -- ER FPT
1858 -- Tests required post-update
1859 --
1860 /* BEGIN OF WWBUG 1975359 */
1861 --
1862 open c_person;
1863 --
1864 fetch c_person into l_person;
1865 --
1866 close c_person;
1867 --
1868 per_hrtca_merge.update_tca_person(p_Rec => l_person);
1869 --
1870 hr_utility.set_location('update_row2 - after update',1);
1871 --
1872 /* END OF WWBUG 1975359 */
1873 --
1874 -- HR/WF Synchronization call
1875 --
1876 /* -- this now called later in prog so called after ptu. Bug 3297591
1877 per_hrwf_synch.per_per_wf(p_rec => l_person,
1878 p_action => 'UPDATE');
1879 */
1880 --
1881 -- Has the Date of Birth changed?
1882 --
1883 if p_date_of_birth is null and
1884 p_s_date_of_birth is not null then
1885 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',280);
1886 per_people12_pkg.check_birth_date(p_person_id);
1887 end if;
1888 if p_date_of_birth <> p_s_date_of_birth then
1889 --
1890 -- Run the assignment_link_usages and Element_entry
1891 -- code for Change of Personal qualifying conditions.
1892 --
1893 --
1894 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',282);
1895 per_people3_pkg.run_alu_ee(p_alu_mode => 'CHANGE_PQC'
1896 ,p_business_group_id=>p_business_group_id
1897 ,p_person_id =>p_person_id
1898 ,p_old_start =>p_s_hire_date
1899 ,p_start_date => p_last_update_date
1900 );
1901 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',285);
1902 end if;
1903 --
1904 hr_utility.set_location('update_row2 - after update',2);
1905 --
1906 -- test if hire_date has changed. and system person type has not.
1907 --
1908 if (((p_current_employee_flag = 'Y')
1909 and (p_hire_date <> p_s_hire_date)
1910 and (p_system_person_type = p_s_system_person_type)))
1911 or (nvl(p_adjusted_svc_date,hr_general.end_of_time) -- #1573563
1912 <> nvl(p_s_adjusted_svc_date,
1913 hr_general.end_of_time)
1914 -- and (p_s_system_person_type not in ('EX_EMP','EX_EMP_APL')) -- #2060744
1915 --
1916 -- Verify person has been employee before modifying the POS
1917 --
1918 and Hr_General2.is_person_type(p_person_id, 'EMP',p_s_hire_date) --#2472146
1919 ) then
1920 --
1921 -- Update the period of service for the employee
1922 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',290);
1923 --
1924 per_people3_pkg.update_period(p_person_id =>p_person_id
1925 ,p_hire_date => p_s_hire_date
1926 ,p_new_hire_date =>p_hire_date
1927 ,p_adjusted_svc_date => p_adjusted_svc_date);
1928 --
1929 hr_utility.set_location('update_row2 - after update',3);
1930 --
1931 -- Update the hire records i.e
1932 -- assignment etc.
1933 --
1934 --
1935 /*--- If condition is added for the bug 5907880 */
1936 if (((p_current_employee_flag = 'Y')
1937 and (p_hire_date <> p_s_hire_date)
1938 and (p_system_person_type = p_s_system_person_type))) then
1939 /*--- End changes for the bug 5907880 */
1940 hr_utility.set_location('update_row2 - after update',4);
1941 hr_date_chk.update_hire_records(p_person_id
1942 ,p_applicant_number
1943 ,p_hire_date
1944 ,p_s_hire_date
1945 ,p_last_updated_by
1946 ,p_last_update_login);
1947 End if;
1948 --
1949 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',295);
1950 -- Commented, as this action is being done in
1951 -- hr_change_start_date_api.update_pay_proposal (pehirapi.pkb)
1952 -- open get_pay_proposal;
1953 -- fetch get_pay_proposal into v_dummy;
1954 -- if get_pay_proposal%FOUND
1955 -- then
1956 -- close get_pay_proposal;
1957 -- begin
1958 -- update per_pay_proposals
1959 -- set change_date = p_hire_date
1960 -- where change_date = p_s_hire_date
1961 -- and assignment_id = (select assignment_id
1962 -- from per_assignments_f
1963 -- where person_id = p_person_id
1964 -- and primary_flag = 'Y'
1965 -- and effective_start_date = p_hire_date
1966 -- and assignment_type = 'E'
1967 -- );
1968 -- --
1969 -- if sql%ROWCOUNT <> 1
1970 -- then
1971 -- raise NO_DATA_FOUND;
1972 -- end if;
1973 -- exception
1974 -- when NO_DATA_FOUND then
1975 -- hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
1976 -- hr_utility.set_message_token('PROCEDURE','Update_row');
1977 -- hr_utility.set_message_token('STEP','4');
1978 -- hr_utility.raise_error;
1979 -- end;
1980 -- else
1981 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',300);
1982 -- close get_pay_proposal;
1983 -- end if;
1984 hr_utility.set_location('update_row2 - after update',5);
1985 --
1986 -- Update PTU records to reflect hire date change.
1987 --
1988 -- PTU changes: following has been added
1989 --
1990 -- hr_per_type_usage_internal.maintain_ptu(
1991 -- p_action =>'HIRE DATE',
1992 -- p_person_id => p_person_id,
1993 -- p_date_start => p_hire_date,
1994 -- p_old_date_start => p_s_hire_date);
1995 --
1996 hr_per_type_usage_internal.change_hire_date_ptu
1997 (p_date_start => p_hire_date
1998 ,p_old_date_start => p_s_hire_date
1999 ,p_person_id => p_person_id
2000 ,p_system_person_type => 'EMP'
2001 );
2002 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',305);
2003 --
2004 -- PTU : end of changes
2005 --
2006 --
2007 -- Run the assignment_link_usages and Element_entry
2008 -- code for Assignment Criteria.
2009 --
2010 per_people3_pkg.run_alu_ee(p_alu_mode => 'ASG_CRITERIA'
2011 ,p_business_group_id=>p_business_group_id
2012 ,p_person_id =>p_person_id
2013 ,p_old_start =>p_s_hire_date
2014 ,p_start_date => p_hire_date);
2015 --
2016 hr_utility.set_location('PER_PEOPLE12_PKG.update_row2',310);
2017 --
2018 end if;
2019 --
2020 -- 1766066, contact start date enh. start
2021 --
2022 if (((p_hire_date is not null
2023 and p_s_hire_date is not null
2024 and p_hire_date < p_s_hire_date)
2025 or (p_hire_date is not null
2026 and p_s_hire_date is null))
2027 and (NVL(p_current_npw_flag,'N') <> 'Y') -- 3813870
2028 ) then
2029 maintain_coverage(p_person_id => p_person_id
2030 ,p_type => 'EMP'
2031 );
2032 end if;
2033 --
2034 -- 1766066 end
2035 --
2036 -- call synch process here - Bug 3297591.
2037 --
2038 --/*
2039 per_hrwf_synch.per_per_wf(p_rec => l_person,
2040 p_action => 'UPDATE');
2041 --*/
2042 --
2043 p_status := 'END'; -- Status required to end update loop on server
2044 hr_utility.set_location('Leaving PER_PEOPLE12_PKG.update_row2',315);
2045 --
2046 end update_row2;
2047 -- ER FPT
2048
2049
2050 -- Start new code for bug #2664569: Overloaded procedure. ********
2051 procedure update_row1(p_rowid VARCHAR2
2052 ,p_person_id NUMBER
2053 ,p_effective_start_date DATE
2054 ,p_effective_end_date DATE
2055 ,p_business_group_id NUMBER
2056 ,p_person_type_id NUMBER
2057 ,p_last_name VARCHAR2
2058 ,p_start_date DATE
2059 ,p_applicant_number IN OUT NOCOPY VARCHAR2
2060 ,p_comment_id NUMBER
2061 ,p_current_applicant_flag in VARCHAR2
2062 ,p_current_emp_or_apl_flag VARCHAR2
2063 ,p_current_employee_flag VARCHAR2
2064 ,p_date_employee_data_verified DATE
2065 ,p_date_of_birth DATE
2066 ,p_email_address VARCHAR2
2067 ,p_employee_number IN OUT NOCOPY VARCHAR2
2068 ,p_expense_check_send_to_addr VARCHAR2
2069 ,p_first_name VARCHAR2
2070 ,p_full_name VARCHAR2
2071 ,p_known_as VARCHAR2
2072 ,p_marital_status VARCHAR2
2073 ,p_middle_names VARCHAR2
2074 ,p_nationality VARCHAR2
2075 ,p_national_identifier VARCHAR2
2076 ,p_previous_last_name VARCHAR2
2077 ,p_registered_disabled_flag VARCHAR2
2078 ,p_sex VARCHAR2
2079 ,p_title VARCHAR2
2080 ,p_suffix VARCHAR2
2081 ,p_vendor_id NUMBER
2082 ,p_work_telephone VARCHAR2
2083 ,p_request_id NUMBER
2084 ,p_program_application_id NUMBER
2085 ,p_program_id NUMBER
2086 ,p_program_update_date DATE
2087 ,p_a_cat VARCHAR2
2088 ,p_a1 VARCHAR2
2089 ,p_a2 VARCHAR2
2090 ,p_a3 VARCHAR2
2091 ,p_a4 VARCHAR2
2092 ,p_a5 VARCHAR2
2093 ,p_a6 VARCHAR2
2094 ,p_a7 VARCHAR2
2095 ,p_a8 VARCHAR2
2096 ,p_a9 VARCHAR2
2097 ,p_a10 VARCHAR2
2098 ,p_a11 VARCHAR2
2099 ,p_a12 VARCHAR2
2100 ,p_a13 VARCHAR2
2101 ,p_a14 VARCHAR2
2102 ,p_a15 VARCHAR2
2103 ,p_a16 VARCHAR2
2104 ,p_a17 VARCHAR2
2105 ,p_a18 VARCHAR2
2106 ,p_a19 VARCHAR2
2107 ,p_a20 VARCHAR2
2108 ,p_a21 VARCHAR2
2109 ,p_a22 VARCHAR2
2110 ,p_a23 VARCHAR2
2111 ,p_a24 VARCHAR2
2112 ,p_a25 VARCHAR2
2113 ,p_a26 VARCHAR2
2114 ,p_a27 VARCHAR2
2115 ,p_a28 VARCHAR2
2116 ,p_a29 VARCHAR2
2117 ,p_a30 VARCHAR2
2118 ,p_last_update_date DATE
2119 ,p_last_updated_by NUMBER
2120 ,p_last_update_login NUMBER
2121 ,p_created_by NUMBER
2122 ,p_creation_date DATE
2123 ,p_i_cat VARCHAR2
2124 ,p_i1 VARCHAR2
2125 ,p_i2 VARCHAR2
2126 ,p_i3 VARCHAR2
2127 ,p_i4 VARCHAR2
2128 ,p_i5 VARCHAR2
2129 ,p_i6 VARCHAR2
2130 ,p_i7 VARCHAR2
2131 ,p_i8 VARCHAR2
2132 ,p_i9 VARCHAR2
2133 ,p_i10 VARCHAR2
2134 ,p_i11 VARCHAR2
2135 ,p_i12 VARCHAR2
2136 ,p_i13 VARCHAR2
2137 ,p_i14 VARCHAR2
2138 ,p_i15 VARCHAR2
2139 ,p_i16 VARCHAR2
2140 ,p_i17 VARCHAR2
2141 ,p_i18 VARCHAR2
2142 ,p_i19 VARCHAR2
2143 ,p_i20 VARCHAR2
2144 ,p_i21 VARCHAR2
2145 ,p_i22 VARCHAR2
2146 ,p_i23 VARCHAR2
2147 ,p_i24 VARCHAR2
2148 ,p_i25 VARCHAR2
2149 ,p_i26 VARCHAR2
2150 ,p_i27 VARCHAR2
2151 ,p_i28 VARCHAR2
2152 ,p_i29 VARCHAR2
2153 ,p_i30 VARCHAR2
2154 ,p_app_ass_status_type_id NUMBER
2155 ,p_emp_ass_status_type_id NUMBER
2156 ,p_system_person_type in VARCHAR2
2157 ,p_s_system_person_type VARCHAR2
2158 ,p_hire_date DATE
2159 ,p_s_hire_date DATE
2160 ,p_s_date_of_birth DATE
2161 ,p_status in out nocopy VARCHAR2
2162 ,p_new_primary_id in out nocopy NUMBER
2163 ,p_update_primary in out nocopy VARCHAR2
2164 ,p_legislation_code VARCHAR2
2165 ,p_vacancy_id IN OUT NOCOPY NUMBER
2166 ,p_session_date date
2167 ,p_end_of_time date
2168 ,p_work_schedule VARCHAR2
2169 ,p_correspondence_language VARCHAR2
2170 ,p_student_status VARCHAR2
2171 ,p_fte_capacity NUMBER
2172 ,p_on_military_service VARCHAR2
2173 ,p_second_passport_exists VARCHAR2
2174 ,p_background_check_status VARCHAR2
2175 ,p_background_date_check DATE
2176 ,p_blood_type VARCHAR2
2177 ,p_last_medical_test_date DATE
2178 ,p_last_medical_test_by VARCHAR2
2179 ,p_rehire_recommendation VARCHAR2
2180 ,p_rehire_reason VARCHAR2
2181 ,p_resume_exists VARCHAR2
2182 ,p_resume_last_updated DATE
2183 ,p_office_number VARCHAR2
2184 ,p_internal_location VARCHAR2
2185 ,p_mailstop VARCHAR2
2186 ,p_honors VARCHAR2
2187 ,p_pre_name_adjunct VARCHAR2
2188 ,p_hold_applicant_date_until DATE
2189 ,p_benefit_group_id NUMBER
2190 ,p_receipt_of_death_cert_date DATE
2191 ,p_coord_ben_med_pln_no VARCHAR2
2192 ,p_coord_ben_no_cvg_flag VARCHAR2
2193 ,p_uses_tobacco_flag VARCHAR2
2194 ,p_dpdnt_adoption_date DATE
2195 ,p_dpdnt_vlntry_svce_flag VARCHAR2
2196 ,p_date_of_death DATE
2197 ,p_original_date_of_hire DATE
2198 ,p_adjusted_svc_date DATE
2199 ,p_s_adjusted_svc_date DATE
2200 ,p_town_of_birth VARCHAR2
2201 ,p_region_of_birth VARCHAR2
2202 ,p_country_of_birth VARCHAR2
2203 ,p_global_person_id VARCHAR2
2204 ,p_npw_number IN OUT NOCOPY VARCHAR2
2205 ,p_current_npw_flag VARCHAR2
2206
2207 ) is
2208 l_sys_per_type varchar2(150);
2209 l_current_apl varchar2(150);
2210 l_table HR_EMPLOYEE_APPLICANT_API.t_ApplTable;
2211 begin
2212 l_sys_per_type := p_system_person_type;
2213 l_current_apl := p_current_applicant_flag;
2214
2215 update_row1(p_rowid
2216 ,p_person_id
2217 ,p_effective_start_date
2218 ,p_effective_end_date
2219 ,p_business_group_id
2220 ,p_person_type_id
2221 ,p_last_name
2222 ,p_start_date
2223 ,p_applicant_number
2224 ,p_comment_id
2225 -- # 2264569:
2226 ,l_current_apl
2227 --
2228 ,p_current_emp_or_apl_flag
2229 ,p_current_employee_flag
2230 ,p_date_employee_data_verified
2231 ,p_date_of_birth
2232 ,p_email_address
2233 ,p_employee_number
2234 ,p_expense_check_send_to_addr
2235 ,p_first_name
2236 ,p_full_name
2237 ,p_known_as
2238 ,p_marital_status
2239 ,p_middle_names
2240 ,p_nationality
2241 ,p_national_identifier
2242 ,p_previous_last_name
2243 ,p_registered_disabled_flag
2244 ,p_sex
2245 ,p_title
2246 ,p_suffix
2247 ,p_vendor_id
2248 ,p_work_telephone
2249 ,p_request_id
2250 ,p_program_application_id
2251 ,p_program_id
2252 ,p_program_update_date
2253 ,p_a_cat
2254 ,p_a1
2255 ,p_a2
2256 ,p_a3
2257 ,p_a4
2258 ,p_a5
2259 ,p_a6
2260 ,p_a7
2261 ,p_a8
2262 ,p_a9
2263 ,p_a10
2264 ,p_a11
2265 ,p_a12
2266 ,p_a13
2267 ,p_a14
2268 ,p_a15
2269 ,p_a16
2270 ,p_a17
2271 ,p_a18
2272 ,p_a19
2273 ,p_a20
2274 ,p_a21
2275 ,p_a22
2276 ,p_a23
2277 ,p_a24
2278 ,p_a25
2279 ,p_a26
2280 ,p_a27
2281 ,p_a28
2282 ,p_a29
2283 ,p_a30
2284 ,p_last_update_date
2285 ,p_last_updated_by
2286 ,p_last_update_login
2287 ,p_created_by
2288 ,p_creation_date
2289 ,p_i_cat
2290 ,p_i1
2291 ,p_i2
2292 ,p_i3
2293 ,p_i4
2294 ,p_i5
2295 ,p_i6
2296 ,p_i7
2297 ,p_i8
2298 ,p_i9
2299 ,p_i10
2300 ,p_i11
2301 ,p_i12
2302 ,p_i13
2303 ,p_i14
2304 ,p_i15
2305 ,p_i16
2306 ,p_i17
2307 ,p_i18
2308 ,p_i19
2309 ,p_i20
2310 ,p_i21
2311 ,p_i22
2312 ,p_i23
2313 ,p_i24
2314 ,p_i25
2315 ,p_i26
2316 ,p_i27
2317 ,p_i28
2318 ,p_i29
2319 ,p_i30
2320 ,p_app_ass_status_type_id
2321 ,p_emp_ass_status_type_id
2322 -- # 2264569
2323 ,l_sys_per_type
2324 ,p_s_system_person_type
2325 ,p_hire_date
2326 ,p_s_hire_date
2327 ,p_s_date_of_birth
2328 ,p_status
2329 ,p_new_primary_id
2330 ,p_update_primary
2331 ,p_legislation_code
2332 ,p_vacancy_id
2333 ,p_session_date
2334 ,p_end_of_time
2335 ,p_work_schedule
2336 ,p_correspondence_language
2337 ,p_student_status
2338 ,p_fte_capacity
2339 ,p_on_military_service
2340 ,p_second_passport_exists
2341 ,p_background_check_status
2342 ,p_background_date_check
2343 ,p_blood_type
2344 ,p_last_medical_test_date
2345 ,p_last_medical_test_by
2346 ,p_rehire_recommendation
2347 ,p_rehire_reason
2348 ,p_resume_exists
2349 ,p_resume_last_updated
2350 ,p_office_number
2351 ,p_internal_location
2352 ,p_mailstop
2353 ,p_honors
2354 ,p_pre_name_adjunct
2355 ,p_hold_applicant_date_until
2356 ,p_benefit_group_id
2357 ,p_receipt_of_death_cert_date
2358 ,p_coord_ben_med_pln_no
2359 ,p_coord_ben_no_cvg_flag
2360 ,p_uses_tobacco_flag
2361 ,p_dpdnt_adoption_date
2362 ,p_dpdnt_vlntry_svce_flag
2363 ,p_date_of_death
2364 ,p_original_date_of_hire
2365 ,p_adjusted_svc_date
2366 ,p_s_adjusted_svc_date
2367 ,p_town_of_birth
2368 ,p_region_of_birth
2369 ,p_country_of_birth
2370 ,p_global_person_id
2371 ,p_npw_number
2372 ,p_current_npw_flag
2373 -- #2264569
2374 ,l_table
2375 ,null
2376 ,null
2377 ,null);
2378 end update_row1;
2379 -- End new code for bug # 2264569 ************************
2380 --
2381 procedure update_row1(p_rowid VARCHAR2
2382 ,p_person_id NUMBER
2383 ,p_effective_start_date DATE
2384 ,p_effective_end_date DATE
2385 ,p_business_group_id NUMBER
2386 ,p_person_type_id NUMBER
2387 ,p_last_name VARCHAR2
2388 ,p_start_date DATE
2389 ,p_applicant_number IN OUT NOCOPY VARCHAR2
2390 ,p_comment_id NUMBER
2391 -- *** Start commented code for bug 2264569 *****
2392 --,p_current_applicant_flag VARCHAR2
2393 -- *** End commented code for bug 2264569 *******
2394 --
2395 -- *** Start new code for bug 2264569 ***********
2396 -- should be in out parameter
2397 ,p_current_applicant_flag IN OUT NOCOPY VARCHAR2
2398 -- *** End new code for bug 2264569 *************
2399 ,p_current_emp_or_apl_flag VARCHAR2
2400 ,p_current_employee_flag VARCHAR2
2401 ,p_date_employee_data_verified DATE
2402 ,p_date_of_birth DATE
2403 ,p_email_address VARCHAR2
2404 ,p_employee_number IN OUT NOCOPY VARCHAR2
2405 ,p_expense_check_send_to_addr VARCHAR2
2406 ,p_first_name VARCHAR2
2407 ,p_full_name VARCHAR2
2408 ,p_known_as VARCHAR2
2409 ,p_marital_status VARCHAR2
2410 ,p_middle_names VARCHAR2
2411 ,p_nationality VARCHAR2
2412 ,p_national_identifier VARCHAR2
2413 ,p_previous_last_name VARCHAR2
2414 ,p_registered_disabled_flag VARCHAR2
2415 ,p_sex VARCHAR2
2416 ,p_title VARCHAR2
2417 ,p_suffix VARCHAR2
2418 ,p_vendor_id NUMBER
2419 ,p_work_telephone VARCHAR2
2420 ,p_request_id NUMBER
2421 ,p_program_application_id NUMBER
2422 ,p_program_id NUMBER
2423 ,p_program_update_date DATE
2424 ,p_a_cat VARCHAR2
2425 ,p_a1 VARCHAR2
2426 ,p_a2 VARCHAR2
2427 ,p_a3 VARCHAR2
2428 ,p_a4 VARCHAR2
2429 ,p_a5 VARCHAR2
2430 ,p_a6 VARCHAR2
2431 ,p_a7 VARCHAR2
2432 ,p_a8 VARCHAR2
2433 ,p_a9 VARCHAR2
2434 ,p_a10 VARCHAR2
2435 ,p_a11 VARCHAR2
2436 ,p_a12 VARCHAR2
2437 ,p_a13 VARCHAR2
2438 ,p_a14 VARCHAR2
2439 ,p_a15 VARCHAR2
2440 ,p_a16 VARCHAR2
2441 ,p_a17 VARCHAR2
2442 ,p_a18 VARCHAR2
2443 ,p_a19 VARCHAR2
2444 ,p_a20 VARCHAR2
2445 ,p_a21 VARCHAR2
2446 ,p_a22 VARCHAR2
2447 ,p_a23 VARCHAR2
2448 ,p_a24 VARCHAR2
2449 ,p_a25 VARCHAR2
2450 ,p_a26 VARCHAR2
2451 ,p_a27 VARCHAR2
2452 ,p_a28 VARCHAR2
2453 ,p_a29 VARCHAR2
2454 ,p_a30 VARCHAR2
2455 ,p_last_update_date DATE
2456 ,p_last_updated_by NUMBER
2457 ,p_last_update_login NUMBER
2458 ,p_created_by NUMBER
2459 ,p_creation_date DATE
2460 ,p_i_cat VARCHAR2
2461 ,p_i1 VARCHAR2
2462 ,p_i2 VARCHAR2
2463 ,p_i3 VARCHAR2
2464 ,p_i4 VARCHAR2
2465 ,p_i5 VARCHAR2
2466 ,p_i6 VARCHAR2
2467 ,p_i7 VARCHAR2
2468 ,p_i8 VARCHAR2
2469 ,p_i9 VARCHAR2
2470 ,p_i10 VARCHAR2
2471 ,p_i11 VARCHAR2
2472 ,p_i12 VARCHAR2
2473 ,p_i13 VARCHAR2
2474 ,p_i14 VARCHAR2
2475 ,p_i15 VARCHAR2
2476 ,p_i16 VARCHAR2
2477 ,p_i17 VARCHAR2
2478 ,p_i18 VARCHAR2
2479 ,p_i19 VARCHAR2
2480 ,p_i20 VARCHAR2
2481 ,p_i21 VARCHAR2
2482 ,p_i22 VARCHAR2
2483 ,p_i23 VARCHAR2
2484 ,p_i24 VARCHAR2
2485 ,p_i25 VARCHAR2
2486 ,p_i26 VARCHAR2
2487 ,p_i27 VARCHAR2
2488 ,p_i28 VARCHAR2
2489 ,p_i29 VARCHAR2
2490 ,p_i30 VARCHAR2
2491 ,p_app_ass_status_type_id NUMBER
2492 ,p_emp_ass_status_type_id NUMBER
2493 -- *** Start commented code for bug 2264569 ***
2494 --,p_system_person_type VARCHAR2
2495 -- *** End commented code for bug 2264569******
2496 --
2497 -- Start new code for bug 2264569 *************
2498 -- should be in out parameter
2499 ,p_system_person_type IN OUT NOCOPY VARCHAR2
2500 -- End new code for bug 2264569 **************
2501 --
2502 ,p_s_system_person_type VARCHAR2
2503 ,p_hire_date DATE
2504 ,p_s_hire_date DATE
2505 ,p_s_date_of_birth DATE
2506 ,p_status in out nocopy VARCHAR2
2507 ,p_new_primary_id in out nocopy NUMBER
2508 ,p_update_primary in out nocopy VARCHAR2
2509 ,p_legislation_code VARCHAR2
2510 ,p_vacancy_id IN OUT NOCOPY NUMBER
2511 ,p_session_date date
2512 ,p_end_of_time date
2513 ,p_work_schedule VARCHAR2
2514 ,p_correspondence_language VARCHAR2
2515 ,p_student_status VARCHAR2
2516 ,p_fte_capacity NUMBER
2517 ,p_on_military_service VARCHAR2
2518 ,p_second_passport_exists VARCHAR2
2519 ,p_background_check_status VARCHAR2
2520 ,p_background_date_check DATE
2521 ,p_blood_type VARCHAR2
2522 ,p_last_medical_test_date DATE
2523 ,p_last_medical_test_by VARCHAR2
2524 ,p_rehire_recommendation VARCHAR2
2525 ,p_rehire_reason VARCHAR2
2526 ,p_resume_exists VARCHAR2
2527 ,p_resume_last_updated DATE
2528 ,p_office_number VARCHAR2
2529 ,p_internal_location VARCHAR2
2530 ,p_mailstop VARCHAR2
2531 ,p_honors VARCHAR2
2532 ,p_pre_name_adjunct VARCHAR2
2533 ,p_hold_applicant_date_until DATE
2534 ,p_benefit_group_id NUMBER
2535 ,p_receipt_of_death_cert_date DATE
2536 ,p_coord_ben_med_pln_no VARCHAR2
2537 ,p_coord_ben_no_cvg_flag VARCHAR2
2538 ,p_uses_tobacco_flag VARCHAR2
2539 ,p_dpdnt_adoption_date DATE
2540 ,p_dpdnt_vlntry_svce_flag VARCHAR2
2541 ,p_date_of_death DATE
2542 ,p_original_date_of_hire DATE
2543 ,p_adjusted_svc_date DATE
2544 ,p_s_adjusted_svc_date DATE
2545 ,p_town_of_birth VARCHAR2
2546 ,p_region_of_birth VARCHAR2
2547 ,p_country_of_birth VARCHAR2
2548 ,p_global_person_id VARCHAR2
2549 ,p_npw_number IN OUT NOCOPY VARCHAR2
2550 ,p_current_npw_flag VARCHAR2
2551 -- Start new code for bug 2264569 ****************************
2552 -- added pl/sql table
2553 ,p_tab IN OUT NOCOPY HR_EMPLOYEE_APPLICANT_API.t_ApplTable
2554 -- End new code for bug 2264569 ******************************
2555 ,p_order_name IN VARCHAR2
2556 ,p_global_name IN VARCHAR2
2557 ,p_local_name IN VARCHAR2
2558 ) is
2559 --
2560 l_period_of_service_id number; -- Period of Service id.
2561 l_back2back boolean;
2562 l_employ_emp_apl varchar2(1); -- Are we employing an EMP_APL?
2563 l_fire_warning varchar2(1); -- If set Y return to form displaying warning.
2564 l_num_appls NUMBER; -- Number of applicants.
2565 l_num_accepted_appls NUMBER; -- Number of accepted spplicant assignments
2566 l_set_of_books_id NUMBER; -- Required for GL.
2567 v_dummy NUMBER; -- For cursor fetch.
2568 l_npw_number per_all_people_f.npw_number%type;
2569 --
2570 l_warn_ee VARCHAR2(1) := 'N';
2571 --
2572 l_max_ele number;--added for bug 6600075
2573
2574 cursor get_pay_proposal
2575 is
2576 select PAY_PROPOSAL_ID
2577 from per_pay_proposals
2578 where change_date = p_s_hire_date
2579 and assignment_id = (select assignment_id
2580 from per_assignments_f
2581 where person_id = p_person_id
2582 and primary_flag = 'Y'
2583 and effective_start_date = p_hire_date
2584 and assignment_type = 'E'
2585 );
2586 --
2587 --
2588 /* BEGIN OF WWBUG 1975359 */
2589 cursor c1 is
2590 select party_id
2591 from per_all_people_f
2592 where person_id = p_person_id
2593 and p_effective_start_date
2594 between effective_start_date
2595 and effective_end_date; /* Fix for Bug 7442246 */
2596 --
2597 l_party_id number;
2598 --
2599 cursor c_person is
2600 select *
2601 from per_all_people_f
2602 where person_id = p_person_id
2603 and p_effective_start_date
2604 between effective_start_date
2605 and effective_end_date;
2606 --
2607 l_person per_all_people_f%rowtype;
2608 --
2609 /* END OF WWBUG 1975359 */
2610 --
2611 -- **** Start new code for bug 2264569 ******************************
2612 cursor c_apl_flag(cp_person_type varchar2) is
2613 select current_applicant_flag
2614 from per_startup_person_types
2615 where system_person_type = cp_person_type;
2616 -- **** End new code for bug 2264569 *******************************
2617 --
2618 --bug no 5546586 starts here
2619 cursor email_address is
2620 select email_address
2621 from per_all_people_f
2622 where rowid = p_rowid;
2623 --bug no 5546586 ends here
2624
2625 -- call to ethnicity
2626 cursor c_person_enthn is
2627 select business_group_id,per_information1
2628 from per_all_people_f
2629 where person_id = p_person_id
2630 and p_effective_start_date
2631 between effective_start_date
2632 and effective_end_date;
2633
2634 l_bgid number;
2635 l_per_info1 varchar2(150);
2636 -- call to ethnicity
2637
2638
2639 -- bug no 9248094 Starts here
2640
2641 -- Start changes for bug 9879782
2642 l_asg_id number;
2643 l_apl_id number;
2644
2645 cursor c_get_asg_apl_ids is
2646 SELECT assignment_id, application_id
2647 FROM per_all_assignments_f
2648 WHERE person_id = p_person_id
2649 AND primary_flag = 'Y'
2650 AND assignment_type = 'E'
2651 AND p_session_date BETWEEN effective_start_date AND effective_end_date;
2652 -- End changes for bug 9879782
2653
2654 l_msg varchar2(2000);
2655
2656
2657 --ER FPT
2658 l_chk_future_asg varchar2(1) := 'N';
2659 l_chk_future_pt varchar2(1) := 'N';
2660
2661 cursor chk_future_asg is
2662 select 'Y' from per_all_assignments_f
2663 where person_id = p_person_id
2664 and effective_start_date >= p_hire_date
2665 and assignment_type = 'A';
2666
2667 cursor chk_future_pt is
2668 select 'Y' from per_person_type_usages_f
2669 where person_id = p_person_id
2670 and effective_start_date >= p_hire_date;
2671 --ER FPT
2672
2673 begin
2674
2675 open chk_future_asg;
2676 fetch chk_future_asg into l_chk_future_asg;
2677 close chk_future_asg;
2678
2679 open chk_future_pt;
2680 fetch chk_future_pt into l_chk_future_pt;
2681 close chk_future_pt;
2682
2683 -- If future person types or future asg updates exists move to proc update_row2
2684
2685 if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y')
2686 and ((l_chk_future_pt = 'Y' ) or (l_chk_future_asg = 'Y')) then
2687
2688 hr_utility.set_location('Future Person Type or Future asg changes exists',10);
2689 update_row2(p_rowid
2690 ,p_person_id
2691 ,p_effective_start_date
2692 ,p_effective_end_date
2693 ,p_business_group_id
2694 ,p_person_type_id
2695 ,p_last_name
2696 ,p_start_date
2697 ,p_applicant_number
2698 ,p_comment_id
2699 -- # 2264569:
2700 ,p_current_applicant_flag
2701 --
2702 ,p_current_emp_or_apl_flag
2703 ,p_current_employee_flag
2704 ,p_date_employee_data_verified
2705 ,p_date_of_birth
2706 ,p_email_address
2707 ,p_employee_number
2708 ,p_expense_check_send_to_addr
2709 ,p_first_name
2710 ,p_full_name
2711 ,p_known_as
2712 ,p_marital_status
2713 ,p_middle_names
2714 ,p_nationality
2715 ,p_national_identifier
2716 ,p_previous_last_name
2717 ,p_registered_disabled_flag
2718 ,p_sex
2719 ,p_title
2720 ,p_suffix
2721 ,p_vendor_id
2722 ,p_work_telephone
2723 ,p_request_id
2724 ,p_program_application_id
2725 ,p_program_id
2726 ,p_program_update_date
2727 ,p_a_cat
2728 ,p_a1
2729 ,p_a2
2730 ,p_a3
2731 ,p_a4
2732 ,p_a5
2733 ,p_a6
2734 ,p_a7
2735 ,p_a8
2736 ,p_a9
2737 ,p_a10
2738 ,p_a11
2739 ,p_a12
2740 ,p_a13
2741 ,p_a14
2742 ,p_a15
2743 ,p_a16
2744 ,p_a17
2745 ,p_a18
2746 ,p_a19
2747 ,p_a20
2748 ,p_a21
2749 ,p_a22
2750 ,p_a23
2751 ,p_a24
2752 ,p_a25
2753 ,p_a26
2754 ,p_a27
2755 ,p_a28
2756 ,p_a29
2757 ,p_a30
2758 ,p_last_update_date
2759 ,p_last_updated_by
2760 ,p_last_update_login
2761 ,p_created_by
2762 ,p_creation_date
2763 ,p_i_cat
2764 ,p_i1
2765 ,p_i2
2766 ,p_i3
2767 ,p_i4
2768 ,p_i5
2769 ,p_i6
2770 ,p_i7
2771 ,p_i8
2772 ,p_i9
2773 ,p_i10
2774 ,p_i11
2775 ,p_i12
2776 ,p_i13
2777 ,p_i14
2778 ,p_i15
2779 ,p_i16
2780 ,p_i17
2781 ,p_i18
2782 ,p_i19
2783 ,p_i20
2784 ,p_i21
2785 ,p_i22
2786 ,p_i23
2787 ,p_i24
2788 ,p_i25
2789 ,p_i26
2790 ,p_i27
2791 ,p_i28
2792 ,p_i29
2793 ,p_i30
2794 ,p_app_ass_status_type_id
2795 ,p_emp_ass_status_type_id
2796 -- # 2264569
2797 ,p_system_person_type
2798 ,p_s_system_person_type
2799 ,p_hire_date
2800 ,p_s_hire_date
2801 ,p_s_date_of_birth
2802 ,p_status
2803 ,p_new_primary_id
2804 ,p_update_primary
2805 ,p_legislation_code
2806 ,p_vacancy_id
2807 ,p_session_date
2808 ,p_end_of_time
2809 ,p_work_schedule
2810 ,p_correspondence_language
2811 ,p_student_status
2812 ,p_fte_capacity
2813 ,p_on_military_service
2814 ,p_second_passport_exists
2815 ,p_background_check_status
2816 ,p_background_date_check
2817 ,p_blood_type
2818 ,p_last_medical_test_date
2819 ,p_last_medical_test_by
2820 ,p_rehire_recommendation
2821 ,p_rehire_reason
2822 ,p_resume_exists
2823 ,p_resume_last_updated
2824 ,p_office_number
2825 ,p_internal_location
2826 ,p_mailstop
2827 ,p_honors
2828 ,p_pre_name_adjunct
2829 ,p_hold_applicant_date_until
2830 ,p_benefit_group_id
2831 ,p_receipt_of_death_cert_date
2832 ,p_coord_ben_med_pln_no
2833 ,p_coord_ben_no_cvg_flag
2834 ,p_uses_tobacco_flag
2835 ,p_dpdnt_adoption_date
2836 ,p_dpdnt_vlntry_svce_flag
2837 ,p_date_of_death
2838 ,p_original_date_of_hire
2839 ,p_adjusted_svc_date
2840 ,p_s_adjusted_svc_date
2841 ,p_town_of_birth
2842 ,p_region_of_birth
2843 ,p_country_of_birth
2844 ,p_global_person_id
2845 ,p_npw_number
2846 ,p_current_npw_flag
2847 -- #2264569
2848 ,p_tab
2849 ,null
2850 ,null
2851 ,null);
2852
2853
2854 return;
2855
2856 end if;
2857
2858
2859 if p_date_of_death is not null and trunc(p_date_of_death) > trunc(sysdate) then
2860 fnd_message.set_name('PER','PER_289974_TCA_PERSON');
2861 fnd_message.set_token('PROCEDURE','per_people12_pkg.update_row1');
2862 fnd_message.set_token('STEP','1');
2863 l_msg := fnd_message.get_string('AR','HZ_API_NO_FUTURE_DATE_ALLOWED');
2864 l_msg := replace(l_msg,'&COLUMN',' DATE_OF_DEATH ');
2865 fnd_message.set_token('ERROR', l_msg);
2866 fnd_message.raise_error;
2867 end if;
2868
2869 -- bug no 9248094 ends here
2870 --
2871 -- Bug 3091465. The system_person_type at the person-level should
2872 -- never be CWK; this information is only available from the PTU
2873 -- records. Reset the person type. Note, this situation should
2874 -- never occur; this is simply a safety net.
2875 --
2876 IF p_system_person_type = 'CWK' THEN
2877 p_system_person_type := p_s_system_person_type;
2878 END IF;
2879
2880 --
2881 -- p_status has the Value of where the code should start on re-entry.
2882 -- on startup = 'BEGIN'( First time called from form)
2883 -- other values depend on what meesages have been returned to the client
2884 -- and the re-entry point on return from the client.
2885 --
2886 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',5);
2887 --
2888 /* BEGIN OF WWBUG 1975359 */
2889 open c1;
2890 --
2891 fetch c1 into l_party_id;
2892 --
2893 close c1;
2894 --bug no 5546586 starts here
2895
2896 open email_address;
2897 --
2898 fetch email_address into per_hrtca_merge.g_old_email_address;
2899 --
2900 close email_address;
2901 --bug no 5546586 starts here
2902
2903 /* BEGIN OF WWBUG 1975359 */
2904 --
2905 if p_status = 'BEGIN' then
2906 --
2907 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',10);
2908 -- Test to see if the hire_date_has changed
2909 -- Providing Person type has not and it is emp.
2910 -- Or that it has changed to EMP
2911 --
2912 if (p_hire_date <> p_s_hire_date)
2913 and (p_s_hire_date is not null)
2914 and (((p_system_person_type = p_s_system_person_type)
2915 and p_system_person_type in('EMP','EMP_APL'))
2916 or ((p_system_person_type = 'EMP'
2917 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
2918 or (p_system_person_type = 'EMP_APL'
2919 and p_s_system_person_type = 'APL')
2920 or (p_system_person_type = 'EMP'
2921 and p_s_system_person_type = 'EMP_APL'))) then
2922 -- get the period_of_service_id
2923 -- 303729 if person is a supervisor
2924 -- test whether change to hire_date would invalidate this action
2925 --
2926 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',15);
2927 --
2928 if ((p_hire_date <> p_s_hire_date)
2929 and (p_system_person_type = p_s_system_person_type))
2930 then
2931 per_people12_pkg.check_not_supervisor(p_person_id
2932 ,p_hire_date
2933 ,p_s_hire_date);
2934 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',20);
2935 end if;
2936 begin
2937 select pps.period_of_service_id
2938 into l_period_of_service_id
2939 from per_periods_of_service pps
2940 where pps.person_id = p_person_id
2941 and pps.date_start = p_s_hire_date;
2942 --
2943 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',25);
2944 exception
2945 when no_data_found then
2946 --
2947 -- If no data found and a previous hire date existed
2948 -- then raise an error;
2949 --
2950 if p_s_hire_date is not null then
2951 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2952 hr_utility.set_message_token('PROCEDURE','Update_row');
2953 hr_utility.raise_error;
2954 end if;
2955 end;
2956 --
2957 -- check the integrity of the date change.
2958 -- Date may come in between a person type change.
2959 --
2960 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',30);
2961 hr_date_chk.check_hire_ref_int(p_person_id
2962 ,p_business_group_id
2963 ,l_period_of_service_id
2964 ,p_s_hire_date
2965 ,p_system_person_type
2966 ,p_hire_date);
2967 -- VT 12/05/96 bug #418637
2968 -- check the existence of the recurring element entries
2969 --
2970 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',35);
2971 per_people12_pkg.check_recur_ent(p_person_id,p_hire_date,
2972 p_s_hire_date,l_warn_ee);
2973 end if;
2974 --
2975 -- check session date and effective_start_date for differences
2976 -- if any exists then ensure the person record is correct
2977 -- i.e duplicate datetrack functionality as it currently uses
2978 -- a global version of session date to update the rows (not good)
2979 --
2980 -- VT 08/13/96
2981 if p_session_date <> p_effective_start_date then
2982 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',40);
2983 per_people9_pkg.update_old_person_row(p_person_id =>p_person_id
2984 ,p_session_date => p_session_date
2985 ,p_effective_start_date=>p_effective_start_date);
2986 end if;
2987 --
2988 -- get the Employee and applicant numbers if necessary
2989 -- only returns values depending on values of
2990 -- p_current_applicant_flag, p_current_applicant_flag
2991 -- and whether p_employee_number and p_applicant_number
2992 -- are null.
2993 --
2994 -- VT #970014 08/19/99
2995 --adhunter #2544613 comment out call completely, generate number is called from PERWSEPI.pld
2996 --on PRE-UPDATE always
2997 -- if p_current_employee_flag = 'Y' and
2998 -- p_current_applicant_flag is null and
2999 -- p_employee_number is not null then
3000 -- null;
3001 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',45);
3002 -- else
3003 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',50);
3004 -- hr_person.generate_number(p_current_employee_flag
3005 -- ,p_current_applicant_flag
3006 -- ,null --p_current_npw_flag
3007 -- ,p_national_identifier
3008 -- ,p_business_group_id
3009 -- ,p_person_id
3010 -- ,p_employee_number
3011 -- ,p_applicant_number
3012 -- ,l_npw_number);
3013 -- end if;
3014 --
3015 -- Test current numbers are not used by
3016 -- the system already.
3017 --
3018 hr_person.validate_unique_number(p_person_id =>p_person_id
3019 , p_business_group_id => p_business_group_id
3020 , p_employee_number => p_employee_number
3021 , p_applicant_number => p_applicant_number
3022 , p_npw_number => null --p_npw_number
3023 , p_current_employee => p_current_employee_flag
3024 , p_current_applicant => p_current_applicant_flag
3025 , p_current_npw => null --p_current_npw_flag
3026 );
3027 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',55);
3028 -- VT 12/05/96 bug #418637
3029 if l_warn_ee = 'Y' then
3030 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',60);
3031 p_status := 'RECUR_ENT_CHK'; -- Set status to next reentry point.
3032 else
3033 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',65);
3034 p_status := 'VACANCY_CHECK'; -- Set status to next possible reentry point.
3035 end if;
3036 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',70);
3037
3038 --Start of fix for Bug 2167668
3039
3040 IF p_start_date > p_hold_applicant_date_until THEN
3041 hr_utility.set_message('800', 'PER_289796_HOLD_UNTIL_DATE');
3042 hr_utility.set_message_token('HOLD_DATE', p_start_date );
3043 hr_utility.raise_error;
3044 END IF;
3045
3046 -- End of fix for Bug 2167668
3047 end if; -- End the First in section
3048 --
3049 -- VT 12/05/96 bug #418637
3050 if p_status = 'RECUR_ENT_CHK' then
3051 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',75);
3052 return;
3053 end if; -- End of RECUR_ENT_CHK
3054 --
3055 --
3056 -- Start of Person type changes.
3057 --
3058 -- Has the Person type changed to become that of an applicant?
3059 --
3060 if (p_system_person_type ='APL'
3061 and p_s_system_person_type = 'OTHER')
3062 or (p_system_person_type = 'APL_EX_APL'
3063 and p_s_system_person_type = 'EX_APL')
3064 or (p_system_person_type = 'EMP_APL'
3065 and p_s_system_person_type = 'EMP')
3066 or (p_system_person_type = 'EX_EMP_APL'
3067 and p_s_system_person_type = 'EX_EMP') then
3068 --
3069 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',80);
3070 NULL;
3071 -- 3652025 >> this process is replaced by call to
3072 -- hr_applicant_internal.create_applicant_anytime() procedure.
3073 -- called directly from PERWSEPI.pld
3074 --
3075 -- Ensure no future person_type_changes.
3076 --
3077 -- if hr_person.chk_future_person_type(p_s_system_person_type
3078 -- ,p_person_id
3079 -- ,p_business_group_id
3080 -- ,p_effective_start_date) then
3081 -- fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
3082 -- app_exception.raise_exception;
3083 --end if;
3084 --
3085 -- Ensure there are no future applicant assignments
3086 --
3087 --per_people3_pkg.check_future_apl(p_person_id => p_person_id
3088 -- ,p_hire_date => greatest(p_hire_date
3089 --,p_effective_start_date));
3090 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',85);
3091 --
3092 -- Insert the default applicant row and applicant
3093 -- assignment.
3094 --
3095 -- VT 08/13/96
3096 --per_people9_pkg.insert_applicant_rows(p_person_id => p_person_id
3097 -- ,p_effective_start_date => p_effective_start_date
3098 -- ,p_effective_end_date => p_effective_end_date
3099 -- ,p_business_group_id =>p_business_group_id
3100 -- ,p_app_ass_status_type_id => p_app_ass_status_type_id
3101 -- ,p_request_id => p_request_id
3102 -- ,p_program_application_id => p_program_application_id
3103 -- ,p_program_id => p_program_id
3104 -- ,p_program_update_date => p_program_update_date
3105 -- ,p_last_update_date => p_last_update_date
3106 -- ,p_last_updated_by => p_last_updated_by
3107 -- ,p_last_update_login => p_last_update_login
3108 -- ,p_created_by => p_created_by
3109 -- ,p_creation_date => p_creation_date);
3110 --
3111 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',90);
3112 --
3113 -- PTU Changes
3114
3115 --hr_per_type_usage_internal.maintain_person_type_usage
3116 -- (p_effective_date => p_effective_start_date
3117 -- ,p_person_id => p_person_id
3118 -- ,p_person_type_id => p_person_type_id
3119 -- );
3120 --
3121 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',92);
3122 -- PTU Changes
3123 -- <<
3124 -- Has the Person type changed to become that of an employee
3125 -- when the previous type is not a current applicant?
3126 --
3127 elsif (p_system_person_type = 'EMP'
3128 and ( p_s_system_person_type = 'OTHER'
3129 or p_s_system_person_type = 'EX_EMP'
3130 or p_s_system_person_type = 'EX_APL')) then /* Bug 523924 */
3131 -- or p_s_system_person_type = 'EX_EMP')) then
3132 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',95);
3133 --
3134 -- Ensure no future person_type_changes.
3135 --
3136 if hr_person.chk_future_person_type(p_s_system_person_type
3137 ,p_person_id
3138 ,p_business_group_id
3139 --changes for bug no 6070935
3140 -- ,p_effective_start_date) then
3141 ,p_session_date) then
3142 --changes for bug no 6070935
3143 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
3144 app_exception.raise_exception;
3145 end if;
3146 --
3147 if p_s_system_person_type = 'EX_EMP'
3148 then
3149 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',100);
3150 --
3151 -- Bug 3154253 stars here.
3152 -- Passed earlier of p_ession_date and p_hire_date
3153 -- (p_effective_start_date) to the check_hire procedure.
3154 --
3155 if p_session_date < p_effective_start_date then
3156 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',101);
3157 per_people12_pkg.check_rehire(p_person_id, p_session_date);
3158 else
3159 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',102);
3160 per_people12_pkg.check_rehire(p_person_id, p_hire_date);
3161 end if;
3162 --
3163 -- bug 3154253 ends here.
3164 --
3165 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',105);
3166 end if;
3167 per_people12_pkg.check_future_changes(p_person_id
3168 ,p_effective_start_date);
3169 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',110);
3170 --
3171 -- Ensure there are no future applicant assignments
3172 --
3173 /* per_people3_pkg.check_future_apl(p_person_id => p_person_id
3174 ,p_hire_date => greatest(p_hire_date
3175 p_effective_start_date));* commented for bug 5403222*/
3176 --fix for bug 6600075
3177 l_max_ele := p_tab.COUNT;
3178
3179 if l_max_ele > 0 then
3180 per_people3_pkg.check_future_apl(p_person_id => p_person_id
3181 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
3182 ,p_table=>p_tab);
3183 else
3184 per_people3_pkg.check_future_apl(p_person_id => p_person_id
3185 ,p_hire_date => greatest(p_hire_date,p_effective_start_date));
3186 end if;
3187
3188 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',115);
3189 --
3190 -- Insert the default period_of service and assignment
3191 -- rows.
3192 --
3193 -- VT 08/13/96
3194 per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
3195 ,p_effective_start_date => p_effective_start_date
3196 ,p_effective_end_date => p_effective_end_date
3197 ,p_business_group_id =>p_business_group_id
3198 ,p_emp_ass_status_type_id => p_emp_ass_status_type_id
3199 ,p_employee_number => p_employee_number
3200 ,p_request_id => p_request_id
3201 ,p_program_application_id => p_program_application_id
3202 ,p_program_id => p_program_id
3203 ,p_program_update_date => p_program_update_date
3204 ,p_last_update_date => p_last_update_date
3205 ,p_last_updated_by => p_last_updated_by
3206 ,p_last_update_login => p_last_update_login
3207 ,p_created_by => p_created_by
3208 ,p_creation_date => p_creation_date
3209 ,p_adjusted_svc_date => p_adjusted_svc_date);
3210 --
3211 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',120);
3212
3213 -- PTU Changes
3214
3215 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
3216 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_effective_start_date);
3217 if p_s_system_person_type in ('EX_EMP','EX_EMP_APL') -- Bug 3637893
3218 and p_system_person_type = 'EMP'
3219 -- and p_session_date = p_effective_start_date then
3220 and l_back2back then
3221 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1201);
3222 hr_per_type_usage_internal.maintain_person_type_usage
3223 (p_effective_date => p_effective_start_date
3224 ,p_person_id => p_person_id
3225 ,p_person_type_id => p_person_type_id
3226 ,p_datetrack_update_mode => 'CORRECTION'
3227 );
3228 else
3229 hr_per_type_usage_internal.maintain_person_type_usage
3230 (p_effective_date => p_effective_start_date
3231 ,p_person_id => p_person_id
3232 ,p_person_type_id => p_person_type_id
3233 );
3234 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1202);
3235 end if;
3236
3237 -- PTU Changes
3238
3239 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',121);
3240
3241 -- Has the Person become an Employee or Employee applicant from being an
3242 -- applicant or employee applicant?
3243 --
3244 elsif ((p_system_person_type = 'EMP'
3245 and p_s_system_person_type in ('APL','APL_EX_APL','EX_EMP_APL'))
3246 or (p_system_person_type = 'EMP_APL'
3247 and p_s_system_person_type in ('APL','EX_EMP_APL')) /* Bug 732598 */
3248 or (p_system_person_type = 'EMP'
3249 and p_s_system_person_type = 'EMP_APL')) then
3250 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',125);
3251 --
3252 -- Ensure no future person_type_changes.
3253 --
3254 if hr_person.chk_future_person_type(p_s_system_person_type
3255 ,p_person_id
3256 ,p_business_group_id
3257 ,p_effective_start_date) then
3258 fnd_message.set_name('PAY','HR_7193_PER_FUT_TYPE_EXISTS');
3259 hr_utility.raise_error;
3260 end if;
3261 --
3262 -- Ensure there are no future applicant assignments
3263 --
3264 /* per_people3_pkg.check_future_apl(p_person_id => p_person_id
3265 ,p_hire_date => greatest(p_hire_date
3266 ,p_effective_start_date));* commented for bug 5403222*/
3267 --fix for bug 6600075
3268 l_max_ele := p_tab.COUNT;
3269
3270 if l_max_ele > 0 then
3271 per_people3_pkg.check_future_apl(p_person_id => p_person_id
3272 ,p_hire_date => greatest(p_hire_date,p_effective_start_date)
3273 ,p_table=>p_tab);
3274 else
3275 per_people3_pkg.check_future_apl(p_person_id => p_person_id
3276 ,p_hire_date => greatest(p_hire_date,p_effective_start_date));
3277 end if;
3278
3279 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',130);
3280 --
3281 -- Check if the person have open term_assignment records. These can be
3282 -- found by checking if the person have a periods_of_service with
3283 -- no FPD and a value for ATD. Bug 2881076
3284 per_people12_pkg.check_rehire(p_person_id
3285 ,p_hire_date);
3286 --
3287 -- Check that the change is valid.
3288 --
3289 if p_status = 'VACANCY_CHECK' then
3290 loop
3291 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',135);
3292 exit when p_status = 'BOOKINGS_EXIST';
3293 --
3294 -- Check each vacancy,if it is oversubscribed
3295 -- l_fire_warning = 'Y', return to client
3296 -- displaying relevant message.
3297 -- on return l_vacancy_id starts the cursor at the
3298 -- relevant point.
3299 --
3300 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',140);
3301 per_people3_pkg.vacancy_chk(p_person_id => p_person_id
3302 ,p_fire_warning => l_fire_warning
3303 ,p_vacancy_id => p_vacancy_id
3304 -- **** Start new code for bug 2264569 ****************
3305 ,p_table => p_tab -- #2381925
3306 -- **** End new code for bug 2264569 *****************
3307 );
3308 if l_fire_warning = 'Y' then
3309 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',145);
3310 return;
3311 elsif l_fire_warning = 'N' then
3312 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',150);
3313 p_status := 'BOOKINGS_EXIST'; -- Set next possible re-entry point.
3314 end if;
3315 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',155);
3316 end loop;
3317 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',160);
3318 end if; -- End of VACANCY_CHECK
3319 --
3320 if p_status = 'BOOKINGS_EXIST' then
3321 -- VT 09/18/96 #288087, #380280 , #2172590
3322 if (per_people3_pkg.chk_events_exist(p_person_id =>p_person_id
3323 ,p_business_group_id =>p_business_group_id
3324 ,p_hire_date => greatest(p_hire_date,p_session_date))) then
3325 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',165);
3326 return;
3327 else
3328 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',170);
3329 -- **** Start commented code for bug 2264569 *************************
3330 --p_status := 'GET_APPLS'; -- Set next possible re-entry point.
3331 -- **** End commented code for bug 2264569 ***************************
3332 -- **** Start new code for bug 2264569 ***************************
3333 p_status := 'CHOOSE_VAC'; -- Set next possible re-entry point.
3334 -- **** End new code for bug 2264569 *********************************
3335 end if;
3336 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',175);
3337 end if;
3338 if p_status = 'END_BOOKINGS' then
3339 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',180);
3340 hrhirapl.end_bookings(p_person_id
3341 , p_business_group_id
3342 , p_hire_date);
3343 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',185);
3344 --
3345 -- **** START commented code for bug 2264569 *************************** --
3346 --p_status := 'GET_APPLS'; -- Set next possible re-entry point.
3347 -- **** End commented code for bug 2264569 ************************
3348 -- **** Start new code for bug 2264569 ************************
3349 p_status := 'CHOOSE_VAC'; -- Set next possible re-entry point.
3350 -- **** END new code for bug 2264569 ********************************
3351 --
3352 end if;
3353 --
3354 -- **** START commented code for bug 2264569 *******************************
3355 --
3356 -- Removed references to 'end_unaccepted' and 'multiple_contracts'
3357 -- Get_appls has been moved to the client side.
3358 --
3359 -- if p_status='GET_APPLS' then
3360 --
3361 -- Get all the accepted applicants
3362 --
3363 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',190);
3364 -- per_people3_pkg.get_accepted_appls(p_person_id => p_person_id
3365 -- ,p_num_accepted_appls => l_num_accepted_appls
3366 -- ,p_new_primary_id =>p_new_primary_id);
3367 --
3368 -- Get all current applicant assignments.
3369 --
3370 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',195);
3371 -- per_people3_pkg.get_all_current_appls(p_person_id => p_person_id
3372 -- ,p_num_appls => l_num_appls);
3373 --
3374 -- if p_system_person_type = 'EMP_APL' then
3375 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',200);
3376 --
3377 -- If we have got this far then there must be > 0 Accepted
3378 -- applications,therefore check p_system_person_type if EMP_APL
3379 -- and number of accepted is equal to number of current assignments
3380 -- then there is an error. Otherwise go around end_accepted
3381 -- to multiple contracts.
3382 --
3383 -- if l_num_accepted_appls = l_num_appls then
3384 -- hr_utility.set_message('801','HR_6791_EMP_APL_NO_ASG');
3385 -- hr_utility.raise_error;
3386 -- else
3387 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',205);
3388 -- p_status := 'MULTIPLE_CONTRACTS';-- Set next re-entry point.
3389 -- end if;
3390 -- --
3391 -- Number of accepted does not equal number of current then
3392 -- end_accepted.
3393 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',210);
3394 --
3395 -- elsif l_num_accepted_appls <> l_num_appls then
3396 -- hr_utility.set_message('801','HR_EMP_UNACCEPTED_APPL');
3397 -- p_status := 'END_UNACCEPTED'; -- next code re-entry,
3398 -- return;
3399 -- --
3400 -- Otherwise ignore end_accepted.
3401 --
3402 -- else
3403 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',215);
3404 -- p_status := 'MULTIPLE_CONTRACTS'; -- next code re-entry.
3405 -- end if;
3406 -- end if; -- End of GET_APPLS
3407 --
3408 -- if p_status = 'END_UNACCEPTED' then
3409 --
3410 -- End the unaccepted assignments.
3411 --
3412 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',220);
3413 -- hrhirapl.end_unaccepted_app_assign(p_person_id
3414 -- ,p_business_group_id
3415 -- ,p_legislation_code
3416 -- ,p_session_date);
3417 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',225);
3418 -- p_status := 'MULTIPLE_CONTRACTS';
3419 -- end if; -- End of END_UNACCEPTED
3420 -- --
3421 -- -- Test to see if multiple contracts are a possibility.
3422 -- --
3423 --hr_utility.set_location('update_row - b4 MULTIPLE_CONTRACTS',1);
3424 -- if p_status = 'MULTIPLE_CONTRACTS' then -- MULTIPLE_CONTRACTS
3425 -- if l_num_accepted_appls >1 then
3426 -- hr_utility.set_message('801','HR_EMP_MULTIPLE_CONTRACTS');
3427 -- return;
3428 -- else
3429 -- p_status := 'CHOOSE_VAC'; -- next code re-entry.
3430 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',230);
3431 -- end if;
3432 --hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',235);
3433 -- end if; -- End of MULTIPLE_CONTRACTS
3434 --
3435 -- **** END commented code for bug 2264569 ****************************
3436 --
3437 -- Choose whether to change the Primary assignment
3438 -- and which vacancy is to be the primary if so.
3439 --
3440 hr_utility.set_location('update_row - b4 CHOOSE_VAC',1);
3441 if p_status = 'CHOOSE_VAC' then
3442 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',240);
3443 return;
3444 end if; --End of CHOOSE_VAC
3445 --
3446 -- Can now hire the Person
3447 -- Note HIRE status can only be set from client form
3448 -- as interaction is generally required.
3449 --
3450 hr_utility.set_location('update_row - b4 HIRE',1);
3451 -- +-------------------------------------------------------------------+
3452 -- +--------- BEGIN: Hire process -------------------------------------+
3453 -- +-------------------------------------------------------------------+
3454 if p_status = 'HIRE' then
3455 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',245);
3456
3457 -- bug fix 2824664:
3458 if p_update_primary = 'Y'
3459 and future_pactid_exists(p_person_id, p_effective_start_date)
3460 then
3461 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',246);
3462 fnd_message.set_name('PAY','HR_6591_ASS_ACTIONS_EXIST');
3463 hr_utility.raise_error;
3464 end if;
3465 -- end bug fix 2824664
3466 --
3467 -- If new is Emp and old was Emp_apl
3468 -- then l_emp_emp_apl is set to Y
3469 --
3470 if p_system_person_type = 'EMP'
3471 and p_s_system_person_type = 'EMP_APL' then
3472 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',250);
3473 l_employ_emp_apl := 'Y';
3474 else
3475 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',255);
3476 l_employ_emp_apl := 'N';
3477 end if;
3478 --
3479 -- Run the employ_applicant stored procedure
3480 --
3481 hr_utility.set_location('update_row - b4 hrhirapl',1);
3482 -- **** Start new code for bug 2264569 *****************************
3483 -- End date chosen unaccepted applicant assignments
3484 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',257);
3485 hrhirapl.end_unaccepted_app_assign(p_person_id
3486 ,p_business_group_id
3487 ,p_legislation_code
3488 ,p_session_date
3489 ,p_tab);
3490 hr_utility.set_location('update_row - b4 hrhirapl',2);
3491
3492 -- **** End new code for bug 2264569 *****************************
3493
3494 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',259);
3495 hrhirapl.employ_applicant(p_person_id
3496 ,p_business_group_id
3497 ,p_legislation_code
3498 ,p_new_primary_id
3499 ,p_emp_ass_status_type_id
3500 ,p_last_updated_by
3501 ,p_last_update_login
3502 ,p_effective_start_date
3503 ,p_end_of_time
3504 ,p_last_update_date
3505 ,p_update_primary
3506 ,p_employee_number
3507 ,l_set_of_books_id
3508 ,l_employ_emp_apl
3509 ,p_adjusted_svc_date
3510 ,p_session_date -- Bug 3564129
3511 -- **** Start new code for bug 2264569 ******
3512 ,p_tab
3513 -- **** End new code for bug 2264569 *******
3514 );
3515 hr_utility.set_location('update_row - after hrhirapl',2);
3516 hr_utility.set_location('manage PTU records',3);
3517 if p_system_person_type = 'EMP' then
3518 --
3519 -- PTU : Following Code has been added
3520 -- hr_per_type_usage_internal.maintain_ptu(
3521 -- p_action => 'HIRE_APL',
3522 -- p_person_id => p_person_id,
3523 -- p_actual_termination_date => p_effective_start_date-1);
3524 --
3525 -- **** START new code for bug 2264569 ******************************************
3526 -- Update the system person type to EMP_APL if user is keeping active APPLS.
3527 if hr_employee_applicant_api.retain_exists(p_tab) then
3528 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
3529 p_system_person_type := 'EMP_APL';
3530 open c_apl_flag(p_system_person_type);
3531 fetch c_apl_flag into p_current_applicant_flag;
3532 close c_apl_flag;
3533 hr_utility.trace(' current applicant_flag : '||p_current_applicant_flag);
3534 end if;
3535 -- **** END new code for bug 2264569 *******************************************
3536
3537 -- Bug 3637893 Starts
3538 /* hr_per_type_usage_internal.maintain_person_type_usage
3539 (p_effective_date => p_effective_start_date
3540 ,p_person_id => p_person_id
3541 ,p_person_type_id => p_person_type_id
3542 );
3543 */
3544 l_back2back := per_periods_of_service_pkg_v2.IsBackToBackContract
3545 ( p_person_id => p_person_id, p_hire_date_of_current_pds => p_effective_start_date);
3546 if p_s_system_person_type in ('EX_EMP','EX_EMP_APL')
3547 --and p_system_person_type = 'EMP' --Commented for Bug#15848881
3548 and p_system_person_type in ('EMP','EMP_APL') --Added for Bug#15848881
3549 -- and p_session_date = p_effective_start_date then
3550 and l_back2back then
3551 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1211);
3552 hr_per_type_usage_internal.maintain_person_type_usage
3553 (p_effective_date => p_effective_start_date
3554 ,p_person_id => p_person_id
3555 ,p_person_type_id => p_person_type_id
3556 ,p_datetrack_update_mode => 'CORRECTION'
3557 );
3558 else
3559 hr_per_type_usage_internal.maintain_person_type_usage
3560 (p_effective_date => p_effective_start_date
3561 ,p_person_id => p_person_id
3562 ,p_person_type_id => p_person_type_id
3563 );
3564 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1212);
3565 end if;
3566 -- Bug 3637893 Ends
3567 --
3568 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
3569 -- **** Start new code for bug 2264569 **********************************
3570 if NOT hr_employee_applicant_api.retain_exists(p_tab) then
3571 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',262);
3572 -- **** End new code for bug 2264569 **********************************
3573 hr_per_type_usage_internal.maintain_person_type_usage
3574 (p_effective_date => p_effective_start_date
3575 ,p_person_id => p_person_id
3576 ,p_person_type_id => hr_person_type_usage_info.get_default_person_type_id
3577 (p_business_group_id
3578 ,'EX_APL')
3579 );
3580 -- **** Start new code for bug 2264569 **********************************
3581 end if;
3582
3583 -- **** End new code for bug 2264569 **********************************
3584 -- PTU : End of changes
3585 --
3586 -- call to ethnicity
3587 open c_person_enthn;
3588 fetch c_person_enthn into l_bgid,l_per_info1;
3589 close c_person_enthn;
3590
3591 hr_utility.set_location('l_bgid call enthn pkg :'||l_bgid,262);
3592 hr_utility.set_location('p_s_system_person_type enthn pkg :'||p_s_system_person_type,262);
3593 hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
3594
3595 if hr_api.return_legislation_code (l_bgid) ='US' and
3596 p_s_system_person_type in ('APL','EX_EMP_APL') and l_per_info1 is not null then
3597
3598 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 call enthn pkg',262);
3599
3600 per_us_eth_orig_mig_pkg.irc_upgrade_ethnic_origin(p_person_id,'Y');
3601 end if;
3602
3603
3604 ---- call to ethnicity
3605
3606 end if; -- End of hire
3607 -- +-------------------------------------------------------------------+
3608 -- +----------- END: Hire process -------------------------------------+
3609 -- +-------------------------------------------------------------------+
3610 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',265);
3611 end if; -- End of HIRE.
3612 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',270);
3613 end if; -- Of Person type change checks.
3614 --
3615 -- changed p_rowid => null to p_rowid => p_rowid
3616 --
3617 ben_dt_trgr_handle.person(p_rowid => p_rowid
3618 ,p_business_group_id => p_business_group_id
3619 ,p_person_id => p_person_id
3620 ,p_effective_start_date => p_effective_start_date
3621 ,p_effective_end_date => p_effective_end_date
3622 ,p_date_of_birth => p_date_of_birth
3623 ,p_date_of_death => p_date_of_death
3624 ,p_marital_status => p_marital_status
3625 ,p_on_military_service => p_on_military_service
3626 ,p_registered_disabled_flag => p_registered_disabled_flag
3627 ,p_sex => p_sex
3628 ,p_student_status => p_student_status
3629 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
3630 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
3631 ,p_uses_tobacco_flag => p_uses_tobacco_flag
3632 ,p_benefit_group_id => p_benefit_group_id
3633 ,p_per_information10 => p_i10
3634 ,p_original_date_of_hire => p_original_date_of_hire
3635 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
3636 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
3637 ,p_attribute1 => p_a1
3638 ,p_attribute2 =>p_a2
3639 ,p_attribute3 =>p_a3
3640 ,p_attribute4 =>p_a4
3641 ,p_attribute5 =>p_a5
3642 ,p_attribute6 =>p_a6
3643 ,p_attribute7 =>p_a7
3644 ,p_attribute8 =>p_a8
3645 ,p_attribute9 =>p_a9
3646 ,p_attribute10 =>p_a10
3647 ,p_attribute11 =>p_a11
3648 ,p_attribute12 =>p_a12
3649 ,p_attribute13 =>p_a13
3650 ,p_attribute14 =>p_a14
3651 ,p_attribute15 =>p_a15
3652 ,p_attribute16 =>p_a16
3653 ,p_attribute17 =>p_a17
3654 ,p_attribute18 =>p_a18
3655 ,p_attribute19 =>p_a19
3656 ,p_attribute20 =>p_a20
3657 ,p_attribute21 =>p_a21
3658 ,p_attribute22 =>p_a22
3659 ,p_attribute23 =>p_a23
3660 ,p_attribute24 =>p_a24
3661 ,p_attribute25 =>p_a25
3662 ,p_attribute26 =>p_a26
3663 ,p_attribute27 =>p_a27
3664 ,p_attribute28 =>p_a28
3665 ,p_attribute29 =>p_a29
3666 ,p_attribute30 =>p_a30
3667 );
3668 --
3669 if l_party_id is null then
3670
3671 /*
3672 ** We tried to get the party_id at the start of this process however
3673 ** the person may not have had one. If they have undergone a change in
3674 ** person type they may very well have one by now so we'll try and get
3675 ** the current party_id from their person record (this will have been
3676 ** set when the TCAparty was created) if we don't currently have the value.
3677 **
3678 ** Ideally we should get the party_id returned from the PTU maintenance
3679 ** code where it would have been derived but this is not an ideal world
3680 ** so we won't.
3681 */
3682 open c1;
3683 --
3684 fetch c1 into l_party_id;
3685 --
3686 close c1;
3687
3688 end if;
3689
3690 hr_utility.set_location('update_row - b4 update',1);
3691 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',272);
3692 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_person_type_id,272);
3693 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_s_system_person_type,272);
3694 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_system_person_type,272);
3695 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||hr_person_type_usage_info.get_default_person_type_id(
3696 p_business_group_id,p_system_person_type),272);
3697
3698 -- Bug 6196362 Starts.
3699 -- update per_people_f ppf
3700 update per_all_people_f ppf
3701 -- Bug 6196362 Ends.
3702 set ppf.person_id = p_person_id
3703 ,ppf.effective_start_date = p_effective_start_date
3704 ,ppf.effective_end_date = p_effective_end_date
3705 ,ppf.business_group_id = p_business_group_id
3706 -- ,ppf.person_type_id = p_person_type_id
3707 --,ppf.person_type_id =hr_person_type_usage_info.get_default_person_type_id( p_business_group_id ,p_system_person_type) bug 6848958
3708 ,ppf.person_type_id =decode( p_system_person_type,'CWK',
3709 hr_person_type_usage_info.get_default_person_type_id(
3710 p_business_group_id,'OTHER'),
3711 hr_person_type_usage_info.get_default_person_type_id(
3712 p_business_group_id,p_system_person_type)) -- fix for bug6848958
3713 ,ppf.last_name = p_last_name
3714 ,ppf.start_date = p_start_date
3715 ,ppf.applicant_number = p_applicant_number
3716 ,ppf.comment_id = p_comment_id
3717 ,ppf.current_applicant_flag = p_current_applicant_flag
3718 ,ppf.current_emp_or_apl_flag = p_current_emp_or_apl_flag
3719 ,ppf.current_employee_flag = p_current_employee_flag
3720 ,ppf.date_employee_data_verified = p_date_employee_data_verified
3721 ,ppf.date_of_birth = p_date_of_birth
3722 ,ppf.email_address = p_email_address
3723 ,ppf.employee_number = p_employee_number
3724 ,ppf.expense_check_send_to_address = p_expense_check_send_to_addr
3725 ,ppf.first_name = p_first_name
3726 ,ppf.full_name = p_full_name
3727 ,ppf.known_as = p_known_as
3728 ,ppf.marital_status = p_marital_status
3729 ,ppf.middle_names = p_middle_names
3730 ,ppf.nationality = p_nationality
3731 ,ppf.national_identifier = p_national_identifier
3732 ,ppf.previous_last_name = p_previous_last_name
3733 ,ppf.registered_disabled_flag = p_registered_disabled_flag
3734 ,ppf.sex = p_sex
3735 ,ppf.title = p_title
3736 ,ppf.suffix = p_suffix
3737 ,ppf.vendor_id = p_vendor_id
3738 -- ,ppf.work_telephone = p_work_telephone
3739 ,ppf.request_id = p_request_id
3740 ,ppf.program_application_id = p_program_application_id
3741 ,ppf.program_id = p_program_id
3742 ,ppf.program_update_date = p_program_update_date
3743 ,ppf.attribute_category = p_a_cat
3744 ,ppf.attribute1 = p_a1
3745 ,ppf.attribute2 = p_a2
3746 ,ppf.attribute3 = p_a3
3747 ,ppf.attribute4 = p_a4
3748 ,ppf.attribute5 = p_a5
3749 ,ppf.attribute6 = p_a6
3750 ,ppf.attribute7 = p_a7
3751 ,ppf.attribute8 = p_a8
3752 ,ppf.attribute9 = p_a9
3753 ,ppf.attribute10 = p_a10
3754 ,ppf.attribute11 = p_a11
3755 ,ppf.attribute12 = p_a12
3756 ,ppf.attribute13 = p_a13
3757 ,ppf.attribute14 = p_a14
3758 ,ppf.attribute15 = p_a15
3759 ,ppf.attribute16 = p_a16
3760 ,ppf.attribute17 = p_a17
3761 ,ppf.attribute18 = p_a18
3762 ,ppf.attribute19 = p_a19
3763 ,ppf.attribute20 = p_a20
3764 ,ppf.attribute21 = p_a21
3765 ,ppf.attribute22 = p_a22
3766 ,ppf.attribute23 = p_a23
3767 ,ppf.attribute24 = p_a24
3768 ,ppf.attribute25 = p_a25
3769 ,ppf.attribute26 = p_a26
3770 ,ppf.attribute27 = p_a27
3771 ,ppf.attribute28 = p_a28
3772 ,ppf.attribute29 = p_a29
3773 ,ppf.attribute30 = p_a30
3774 ,ppf.last_update_date = p_last_update_date
3775 ,ppf.last_updated_by = p_last_updated_by
3776 ,ppf.last_update_login = p_last_update_login
3777 ,ppf.created_by = p_created_by
3778 ,ppf.creation_date = p_creation_date
3779 ,ppf.per_information_category = p_i_cat
3780 ,ppf.per_information1 = p_i1
3781 ,ppf.per_information2 = p_i2
3782 ,ppf.per_information3 = p_i3
3783 ,ppf.per_information4 = p_i4
3784 ,ppf.per_information5 = p_i5
3785 ,ppf.per_information6 = p_i6
3786 ,ppf.per_information7 = p_i7
3787 ,ppf.per_information8 = p_i8
3788 ,ppf.per_information9 = p_i9
3789 ,ppf.per_information10 = p_i10
3790 ,ppf.per_information11 = p_i11
3791 ,ppf.per_information12 = p_i12
3792 ,ppf.per_information13 = p_i13
3793 ,ppf.per_information14 = p_i14
3794 ,ppf.per_information15 = p_i15
3795 ,ppf.per_information16 = p_i16
3796 ,ppf.per_information17 = p_i17
3797 ,ppf.per_information18 = p_i18
3798 ,ppf.per_information19 = p_i19
3799 ,ppf.per_information20 = p_i20
3800 ,ppf.per_information21 = p_i21
3801 ,ppf.per_information22 = p_i22
3802 ,ppf.per_information23 = p_i23
3803 ,ppf.per_information24 = p_i24
3804 ,ppf.per_information25 = p_i25
3805 ,ppf.per_information26 = p_i26
3806 ,ppf.per_information27 = p_i27
3807 ,ppf.per_information28 = p_i28
3808 ,ppf.per_information29 = p_i29
3809 ,ppf.per_information30 = p_i30
3810 ,ppf.work_schedule = p_work_schedule
3811 ,ppf.correspondence_language = p_correspondence_language
3812 ,ppf.student_status = p_student_status
3813 ,ppf.fte_capacity = p_fte_capacity
3814 ,ppf.on_military_service = p_on_military_service
3815 ,ppf.second_passport_exists = p_second_passport_exists
3816 ,ppf.background_check_status = p_background_check_status
3817 ,ppf.background_date_check = p_background_date_check
3818 ,ppf.blood_type = p_blood_type
3819 ,ppf.last_medical_test_date = p_last_medical_test_date
3820 ,ppf.last_medical_test_by = p_last_medical_test_by
3821 ,ppf.rehire_recommendation = p_rehire_recommendation
3822 ,ppf.rehire_reason = p_rehire_reason
3823 ,ppf.resume_exists = p_resume_exists
3824 ,ppf.resume_last_updated = p_resume_last_updated
3825 ,ppf.office_number = p_office_number
3826 ,ppf.internal_location = p_internal_location
3827 ,ppf.mailstop = p_mailstop
3828 ,ppf.honors = p_honors
3829 ,ppf.pre_name_adjunct = p_pre_name_adjunct
3830 ,ppf.hold_applicant_date_until = p_hold_applicant_date_until
3831 ,ppf.benefit_group_id = p_benefit_group_id
3832 ,ppf.receipt_of_death_cert_date = p_receipt_of_death_cert_date
3833 ,ppf.coord_ben_med_pln_no = p_coord_ben_med_pln_no
3834 ,ppf.coord_ben_no_cvg_flag = p_coord_ben_no_cvg_flag
3835 ,ppf.uses_tobacco_flag = p_uses_tobacco_flag
3836 ,ppf.dpdnt_adoption_date = p_dpdnt_adoption_date
3837 ,ppf.dpdnt_vlntry_svce_flag = p_dpdnt_vlntry_svce_flag
3838 ,ppf.date_of_death = p_date_of_death
3839 ,ppf.original_date_of_hire = p_original_date_of_hire
3840 ,ppf.town_of_birth = p_town_of_birth
3841 ,ppf.region_of_birth = p_region_of_birth
3842 ,ppf.country_of_birth = p_country_of_birth
3843 ,ppf.global_person_id = p_global_person_id
3844 ,ppf.party_id = l_party_id
3845 ,ppf.npw_number = p_npw_number
3846 ,ppf.current_npw_flag = p_current_npw_flag
3847 ,ppf.order_name = p_order_name -- #3889584
3848 ,ppf.global_name = p_global_name
3849 ,ppf.local_name = p_local_name
3850 where ppf.rowid = p_rowid;
3851 --
3852 if sql%rowcount <1 then
3853 hr_utility.set_message(801,'HR_6001_ALL_MANDATORY_FIELD');
3854 hr_utility.set_message_token('MISSING_FIELD','rowid is'||p_rowid);
3855 hr_utility.raise_error;
3856 end if;
3857 --
3858
3859 -- Start of Fix #2447513
3860 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',275);
3861 --End of Fix
3862
3863 -- Tests required post-update
3864 --
3865 /* BEGIN OF WWBUG 1975359 */
3866 --
3867 open c_person;
3868 --
3869 fetch c_person into l_person;
3870 --
3871 close c_person;
3872 --
3873 per_hrtca_merge.update_tca_person(p_Rec => l_person);
3874 --
3875 hr_utility.set_location('update_row - after update',1);
3876 --
3877 /* END OF WWBUG 1975359 */
3878 --
3879 -- HR/WF Synchronization call
3880 --
3881 /* -- this now called later in prog so called after ptu. Bug 3297591
3882 per_hrwf_synch.per_per_wf(p_rec => l_person,
3883 p_action => 'UPDATE');
3884 */
3885 --
3886 -- Has the Date of Birth changed?
3887 --
3888 if p_date_of_birth is null and
3889 p_s_date_of_birth is not null then
3890 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',280);
3891 per_people12_pkg.check_birth_date(p_person_id);
3892 end if;
3893 if p_date_of_birth <> p_s_date_of_birth then
3894 --
3895 -- Run the assignment_link_usages and Element_entry
3896 -- code for Change of Personal qualifying conditions.
3897 --
3898 --
3899 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',282);
3900 per_people3_pkg.run_alu_ee(p_alu_mode => 'CHANGE_PQC'
3901 ,p_business_group_id=>p_business_group_id
3902 ,p_person_id =>p_person_id
3903 ,p_old_start =>p_s_hire_date
3904 ,p_start_date => p_last_update_date
3905 );
3906 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',285);
3907 end if;
3908 --
3909 hr_utility.set_location('update_row - after update',2);
3910 --
3911 -- test if hire_date has changed. and system person type has not.
3912 --
3913 if (((p_current_employee_flag = 'Y')
3914 and (p_hire_date <> p_s_hire_date)
3915 and (p_system_person_type = p_s_system_person_type)))
3916 or (nvl(p_adjusted_svc_date,hr_general.end_of_time) -- #1573563
3917 <> nvl(p_s_adjusted_svc_date,
3918 hr_general.end_of_time)
3919 -- and (p_s_system_person_type not in ('EX_EMP','EX_EMP_APL')) -- #2060744
3920 --
3921 -- Verify person has been employee before modifying the POS
3922 --
3923 and Hr_General2.is_person_type(p_person_id, 'EMP',p_s_hire_date) --#2472146
3924 ) then
3925 --
3926 -- Update the period of service for the employee
3927 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',290);
3928 --
3929 per_people3_pkg.update_period(p_person_id =>p_person_id
3930 ,p_hire_date => p_s_hire_date
3931 ,p_new_hire_date =>p_hire_date
3932 ,p_adjusted_svc_date => p_adjusted_svc_date);
3933 --
3934 hr_utility.set_location('update_row - after update',3);
3935 --
3936 -- Update the hire records i.e
3937 -- assignment etc.
3938 --
3939 --
3940 /*--- If condition is added for the bug 5907880 */
3941 if (((p_current_employee_flag = 'Y')
3942 and (p_hire_date <> p_s_hire_date)
3943 and (p_system_person_type = p_s_system_person_type))) then
3944 /*--- End changes for the bug 5907880 */
3945 hr_utility.set_location('update_row - after update',4);
3946 hr_date_chk.update_hire_records(p_person_id
3947 ,p_applicant_number
3948 ,p_hire_date
3949 ,p_s_hire_date
3950 ,p_last_updated_by
3951 ,p_last_update_login);
3952 End if;
3953 --
3954 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',295);
3955 -- Commented, as this action is being done in
3956 -- hr_change_start_date_api.update_pay_proposal (pehirapi.pkb)
3957 -- open get_pay_proposal;
3958 -- fetch get_pay_proposal into v_dummy;
3959 -- if get_pay_proposal%FOUND
3960 -- then
3961 -- close get_pay_proposal;
3962 -- begin
3963 -- update per_pay_proposals
3964 -- set change_date = p_hire_date
3965 -- where change_date = p_s_hire_date
3966 -- and assignment_id = (select assignment_id
3967 -- from per_assignments_f
3968 -- where person_id = p_person_id
3969 -- and primary_flag = 'Y'
3970 -- and effective_start_date = p_hire_date
3971 -- and assignment_type = 'E'
3972 -- );
3973 -- --
3974 -- if sql%ROWCOUNT <> 1
3975 -- then
3976 -- raise NO_DATA_FOUND;
3977 -- end if;
3978 -- exception
3979 -- when NO_DATA_FOUND then
3980 -- hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
3981 -- hr_utility.set_message_token('PROCEDURE','Update_row');
3982 -- hr_utility.set_message_token('STEP','4');
3983 -- hr_utility.raise_error;
3984 -- end;
3985 -- else
3986 -- hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',300);
3987 -- close get_pay_proposal;
3988 -- end if;
3989 hr_utility.set_location('update_row - after update',5);
3990 --
3991 -- Update PTU records to reflect hire date change.
3992 --
3993 -- PTU changes: following has been added
3994 --
3995 -- hr_per_type_usage_internal.maintain_ptu(
3996 -- p_action =>'HIRE DATE',
3997 -- p_person_id => p_person_id,
3998 -- p_date_start => p_hire_date,
3999 -- p_old_date_start => p_s_hire_date);
4000 --
4001 hr_per_type_usage_internal.change_hire_date_ptu
4002 (p_date_start => p_hire_date
4003 ,p_old_date_start => p_s_hire_date
4004 ,p_person_id => p_person_id
4005 ,p_system_person_type => 'EMP'
4006 );
4007 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',305);
4008 --
4009 -- PTU : end of changes
4010 --
4011 --
4012 -- Run the assignment_link_usages and Element_entry
4013 -- code for Assignment Criteria.
4014 --
4015 per_people3_pkg.run_alu_ee(p_alu_mode => 'ASG_CRITERIA'
4016 ,p_business_group_id=>p_business_group_id
4017 ,p_person_id =>p_person_id
4018 ,p_old_start =>p_s_hire_date
4019 ,p_start_date => p_hire_date);
4020 --
4021 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',310);
4022 --
4023 end if;
4024 --
4025 -- 1766066, contact start date enh. start
4026 --
4027 if (((p_hire_date is not null
4028 and p_s_hire_date is not null
4029 and p_hire_date < p_s_hire_date)
4030 or (p_hire_date is not null
4031 and p_s_hire_date is null))
4032 and (NVL(p_current_npw_flag,'N') <> 'Y') -- 3813870
4033 ) then
4034 maintain_coverage(p_person_id => p_person_id
4035 ,p_type => 'EMP'
4036 );
4037 end if;
4038 --
4039 -- 1766066 end
4040 --
4041 -- call synch process here - Bug 3297591.
4042 --
4043 --/*
4044 per_hrwf_synch.per_per_wf(p_rec => l_person,
4045 p_action => 'UPDATE');
4046 --*/
4047 --
4048 -- Start changes for bug 9879782
4049 if p_s_system_person_type in ('APL','EMP_APL') and p_system_person_type IN ('EMP','EMP_APL') then
4050
4051 open c_get_asg_apl_ids;
4052 fetch c_get_asg_apl_ids into l_asg_id, l_apl_id;
4053 close c_get_asg_apl_ids;
4054
4055 per_fnd_attachment_pkg.adjust_irec_docs (
4056 p_person_id => P_person_id,
4057 p_assignment_id => l_asg_id,
4058 p_application_id => l_apl_id,
4059 p_old_hire_date => p_session_date,
4060 p_hire_date => p_session_date,
4061 p_transaction_type => 'COREHR',
4062 p_transaction_status => 'COMPLETE',
4063 p_status => 'I',
4064 p_adjustment_type => 'COMMIT');
4065 end if;
4066 -- End changes for bug 9879782
4067
4068 p_status := 'END'; -- Status required to end update loop on server
4069 hr_utility.set_location('Leaving PER_PEOPLE12_PKG.update_row1',315);
4070 --
4071 end update_row1;
4072 --
4073 procedure check_future_changes(p_person_id NUMBER
4074 ,p_effective_start_date DATE)
4075 is
4076 --
4077 l_dummy VARCHAR2(1);
4078 --
4079 cursor future_exists
4080 is
4081 select '1'
4082 from sys.dual
4083 where exists (
4084 select 'future assignment exists'
4085 from per_people_f ppf
4086 where ppf.person_id = p_person_id
4087 and ppf.effective_start_date > p_effective_start_date
4088 );
4089 -- ER FPT
4090 cursor fpt_future_exists
4091 is
4092 select 'Y'
4093 from per_person_type_usages_f ptu, per_person_types ppt
4094 where ptu.person_id = p_person_id
4095 and ptu.person_type_id = ppt.person_type_id
4096 and ptu.effective_start_date > p_effective_start_date
4097 and ppt.system_person_type in ('EMP','CWK');
4098
4099 begin
4100 -- ER FPT
4101 if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
4102 open fpt_future_exists;
4103 fetch fpt_future_exists into l_dummy;
4104 if fpt_future_exists%found then
4105 fnd_message.set_name('PAY','HR_7510_PER_FUT_CHANGE');
4106 app_exception.raise_exception;
4107 end if;
4108 close fpt_future_exists;
4109 else
4110 open future_exists;
4111 fetch future_exists into l_dummy;
4112 if future_exists%found then
4113 fnd_message.set_name('PAY','HR_7510_PER_FUT_CHANGE');
4114 app_exception.raise_exception;
4115 end if;
4116 close future_exists;
4117 end if;
4118 end;
4119 --
4120 procedure check_not_supervisor(p_person_id NUMBER
4121 ,p_new_hire_date DATE
4122 ,p_old_hire_date DATE)
4123 is
4124 l_dummy VARCHAR2(1);
4125 --
4126 cursor supervisor
4127 is
4128 select 'Y'
4129 from per_assignments_f paf
4130 where paf.assignment_type = 'E'
4131 and paf.supervisor_id = p_person_id
4132 and p_new_hire_date > paf.effective_start_date
4133 and paf.effective_end_date >= p_old_hire_date ;
4134 --
4135 begin
4136 open supervisor;
4137 fetch supervisor into l_dummy;
4138 if supervisor%FOUND then
4139 close supervisor;
4140 fnd_message.set_name('PAY','HR_51031_INV_HIRE_CHG_IS_SUPER');
4141 app_exception.raise_exception;
4142 end if;
4143 close supervisor;
4144 end;
4145 --
4146 --
4147 procedure check_rehire(p_person_id NUMBER
4148 ,p_start_date DATE)
4149 IS
4150 cursor old_pps_exists
4151 is
4152 select 1
4153 from per_periods_of_service pps
4154 where pps.person_id = p_person_id
4155 and pps.actual_termination_date is not null;
4156 --
4157 -- 70.11 nvl(pps.final_process_date,p_start_date)+1 < p_start_date;
4158 --
4159 cursor pps_not_ended
4160 is
4161 --
4162 -- 115.67 (START)
4163 --
4164 --select pps.final_process_date
4165 select pps.last_standard_process_date,
4166 pps.final_process_date
4167 --
4168 -- 115.67 (END)
4169 --
4170 from per_periods_of_service pps
4171 where pps.person_id = p_person_id
4172 and pps.date_start = (select max(date_start)
4173 from per_periods_of_service pps1
4174 where pps1.person_id = pps.person_id
4175 and pps1.date_start <p_start_date
4176 )
4177 and nvl(pps.final_process_date,p_start_date) >= p_start_date;
4178 --
4179 v_dummy INTEGER;
4180 v_dummy_fpd date;
4181 --
4182 -- 115.67 (START)
4183 --
4184 v_dummy_lspd DATE;
4185 l_rule_value pay_legislation_rules.rule_mode%TYPE;
4186 l_rule_found BOOLEAN;
4187 l_legislation_code pay_legislation_rules.legislation_code%TYPE;
4188 --
4189 -- Cursor to get legislation code
4190 --
4191 CURSOR csr_per_legislation IS
4192 SELECT bus.legislation_code
4193 FROM per_people_f per
4194 ,per_business_groups bus
4195 WHERE per.person_id = p_person_id
4196 AND per.business_group_id+0 = bus.business_group_id
4197 AND p_start_date BETWEEN per.effective_start_date
4198 AND per.effective_end_date;
4199 --
4200 l_proc VARCHAR2(50);
4201 --
4202 -- 115.67 (END)
4203 --
4204 begin
4205 l_proc := 'per_people12_pkg.check_rehire';
4206 hr_utility.set_location('Entering '||l_proc,5);
4207 --
4208 -- 115.67 (START)
4209 --
4210 --
4211 -- Get legislation
4212 --
4213 OPEN csr_per_legislation;
4214 FETCH csr_per_legislation INTO l_legislation_code;
4215 CLOSE csr_per_legislation;
4216 --
4217 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
4218 ,l_legislation_code
4219 ,l_rule_value
4220 ,l_rule_found
4221 );
4222 --
4223 hr_utility.set_location(l_proc,10);
4224 --
4225 -- 115.67 (END)
4226 --
4227 --
4228 -- Check if old PPS row exists
4229 --
4230 open old_pps_exists;
4231 fetch old_pps_exists into v_dummy;
4232 if old_pps_exists%FOUND
4233 then
4234 close old_pps_exists;
4235 --
4236 hr_utility.set_location(l_proc,15);
4237 --
4238 -- if yes then check last PPS
4239 -- has had it's FPD closed down and that the FPD + 1
4240 -- is less than current hire date
4241 -- if not error;
4242 --
4243 open pps_not_ended;
4244 --
4245 -- 115.67 (START)
4246 --
4247 --fetch pps_not_ended into v_dummy_fpd;
4248 fetch pps_not_ended into v_dummy_lspd,v_dummy_fpd;
4249 --
4250 -- 115.67 (END)
4251 --
4252 if pps_not_ended%FOUND then
4253 --
4254 -- 115.67 (START)
4255 --
4256 --
4257 hr_utility.set_location(l_proc,20);
4258 --
4259 if (not(l_rule_found)
4260 OR
4261 (l_rule_found AND nvl(l_rule_value,'N') = 'N'))
4262 then
4263 --
4264 -- old behaviour as rehire before fpd is not enabled
4265 --
4266 hr_utility.set_location(l_proc,25);
4267 --
4268 -- 115.67 (END)
4269 --
4270 close pps_not_ended;
4271 if v_dummy_fpd is null then
4272 hr_utility.set_message('800','HR_51032_EMP_PREV_FPD_OPEN');
4273 else
4274 hr_utility.set_message('800','PER_289308_FUTURE_ENDED_FPD');
4275 end if;
4276 hr_utility.raise_error;
4277 --
4278 -- 115.67 (START)
4279 --
4280 else
4281 --
4282 hr_utility.set_location(l_proc,30);
4283 --
4284 -- Rehire before FPD allowed
4285 --
4286 if v_dummy_fpd is null then
4287 close pps_not_ended;
4288 hr_utility.set_message('800','HR_449756_FPD_PREV_PDS');
4289 hr_utility.raise_error;
4290 end if;
4291 if v_dummy_lspd >= p_start_date then
4292 close pps_not_ended;
4293 hr_utility.set_message('800','HR_449759_REHIRE_AFTER_LSPD');
4294 hr_utility.raise_error;
4295 end if;
4296 end if;
4297 --
4298 -- 115.67 (END)
4299 --
4300 end if;
4301 close pps_not_ended;
4302 else
4303 close old_pps_exists;
4304 end if;
4305 hr_utility.set_location('Leaving '||l_proc,50);
4306 end;
4307 --
4308 procedure check_birth_date(p_person_id NUMBER)
4309 is
4310 v_dummy NUMBER;
4311 --
4312 -- Cursor to check if any employee assignments have
4313 -- Payroll id set.
4314 --
4315 cursor get_payroll
4316 is
4317 select asg.assignment_id
4318 from per_assignments_f asg
4319 where asg.person_id = p_person_id
4320 and asg.payroll_id is not null;
4321 begin
4322 open get_payroll;
4323 fetch get_payroll into v_dummy;
4324 --
4325 -- If a row exists, flag an error to stop
4326 -- Date of birth being nulled when emp on payroll.
4327 --
4328 if get_payroll%FOUND
4329 then
4330 close get_payroll;
4331 hr_utility.set_message('801','HR_7950_PPM_NULL_DOB');
4332 hr_utility.raise_error;
4333 else
4334 close get_payroll;
4335 end if;
4336 end;
4337 -- VT 12/05/96 bug #418637 new procedure
4338 procedure check_recur_ent(p_person_id NUMBER,
4339 p_start_date DATE,
4340 p_old_date DATE,
4341 p_warn_raise IN OUT NOCOPY VARCHAR2)
4342 is
4343 --
4344 l_warn VARCHAR2(1);
4345 l_earlier_date DATE;
4346 l_later_date DATE;
4347 --
4348 begin
4349 l_warn := p_warn_raise;
4350 if p_start_date > p_old_date then
4351 l_earlier_date := p_old_date;
4352 l_later_date := p_start_date;
4353 else
4354 l_earlier_date := p_start_date;
4355 l_later_date := p_old_date;
4356 end if;
4357 begin
4358 select 'Y'
4359 into l_warn
4360 from dual
4361 where exists
4362 (select null
4363 from pay_element_entries_f ee,
4364 pay_element_links_f el,
4365 pay_element_types_f et
4366 where ee.assignment_id in
4367 (select assignment_id
4368 from per_assignments_f asg
4369 where asg.person_id = p_person_id
4370 and asg.effective_start_date between l_earlier_date and l_later_date)
4371 and ee.element_link_id = el.element_link_id
4372 and el.element_type_id = et.element_type_id
4373 and et.processing_type = 'R');
4374 exception when NO_DATA_FOUND then null;
4375 end;
4376 p_warn_raise := l_warn;
4377 end;
4378
4379 --
4380 --Added procedures for bug 1766066. Only maintain_coverage is declared in header
4381 --
4382 PROCEDURE ins_or_upd_precursor_row
4383 (p_person_id in number
4384 ,p_cov_date_start in date) is
4385 --
4386 cursor csr_per_details(c_person_id number) is
4387 select *
4388 from per_all_people_f
4389 where person_id = c_person_id
4390 order by effective_start_date asc;
4391 --
4392 cursor csr_per_exists(c_person_id number, c_effective_date date) is
4393 select 'Y'
4394 from dual
4395 where exists(
4396 select 1
4397 from per_all_people_f per
4398 where per.person_id = c_person_id
4399 and c_effective_date between per.effective_start_date and per.effective_end_date
4400 );
4401 --
4402 cursor csr_per_type(c_person_id number, c_effective_date date) is
4403 select ppt.system_person_type
4404 from per_person_types ppt,
4405 per_all_people_f ppf
4406 where ppf.person_id = c_person_id
4407 and ppf.person_type_id = ppt.person_type_id;
4408 --
4409 cursor csr_address(c_person_id number, c_date_from date) is
4410 select address_id
4411 from per_addresses
4412 where person_id = c_person_id
4413 and date_from = c_date_from;
4414 --
4415 cursor csr_get_other(c_business_group_id number) is
4416 select ppt.person_type_id
4417 from per_person_types ppt
4418 where ppt.business_group_id = c_business_group_id
4419 and ppt.default_flag = 'Y'
4420 and ppt.active_flag = 'Y'
4421 and ppt.system_person_type = 'OTHER';
4422 --
4423 l_proc varchar2(100) := 'per_people12_pkg.ins_or_upd_precursor_row';
4424 l_per_rec per_all_people_f%rowtype;
4425 l_system_person_type varchar2(60);
4426 l_dummy varchar2(10);
4427 l_cov_date_start date;
4428 l_ptu_nextval number;
4429 l_object_version_number number;
4430 --
4431 begin
4432 hr_utility.set_location('Entering '||l_proc,1);
4433 hr_utility.set_location('person_id: '||p_person_id,2);
4434 open csr_per_exists(p_person_id, p_cov_date_start);
4435 fetch csr_per_exists into l_dummy;
4436 if csr_per_exists%found then
4437 hr_utility.set_location(l_proc,5);
4438 close csr_per_exists; --no need to create precursor row since person exists as of cov.s.d.
4439 else
4440 hr_utility.set_location(l_proc,10);
4441 close csr_per_exists;
4442 --
4443 open csr_per_details(p_person_id);
4444 fetch csr_per_details into l_per_rec; -- fetch once to get earliest record only
4445 close csr_per_details;
4446 --
4447 open csr_per_type(p_person_id, l_per_rec.effective_start_date);
4448 fetch csr_per_type into l_system_person_type;
4449 close csr_per_type;
4450 --
4451
4452 for l_address_id in csr_address(p_person_id, l_per_rec.effective_start_date)
4453 loop
4454 update per_addresses
4455 set date_from = p_cov_date_start
4456 where address_id = l_address_id.address_id;
4457 end loop;
4458 --
4459 if l_system_person_type = 'OTHER' then --first DT record is "OTHER" so simply extend back
4460 hr_utility.set_location(l_proc,15);
4461 update per_all_people_f
4462 set effective_start_date = p_cov_date_start,
4463 start_date = p_cov_date_start
4464 where person_id = p_person_id
4465 and effective_start_date = l_per_rec.effective_start_date;
4466 --
4467 -- Fox for bug 3390731 starts here.
4468 --
4469 update per_all_people_f
4470 set start_date = p_cov_date_start
4471 where person_id = p_person_id;
4472 --
4473 -- Fix for bug 3390731 ends here.
4474 --
4475 update per_person_type_usages_f ptu
4476 set ptu.effective_start_date = p_cov_date_start
4477 where ptu.person_id = p_person_id
4478 and ptu.person_type_id in (select ppt.person_type_id
4479 from per_person_types ppt
4480 where ppt.system_person_type = 'OTHER');
4481 else
4482 hr_utility.set_location(l_proc,20);
4483 -- now change some of the fields before inserting precursor row
4484 l_per_rec.applicant_number := null;
4485 l_per_rec.employee_number := null;
4486 l_per_rec.current_employee_flag := null;
4487 l_per_rec.current_applicant_flag := null;
4488 l_per_rec.current_emp_or_apl_flag := null;
4489 l_per_rec.rehire_authorizor := null;
4490 l_per_rec.effective_end_date := l_per_rec.effective_start_date - 1;
4491 l_per_rec.effective_start_date := p_cov_date_start;
4492 l_per_rec.start_date := p_cov_date_start;
4493 open csr_get_other(l_per_rec.business_group_id);
4494 fetch csr_get_other into l_per_rec.person_type_id;
4495 close csr_get_other;
4496 --
4497 insert into per_all_people_f(
4498 person_id,
4499 effective_start_date,
4500 effective_end_date,
4501 business_group_id,
4502 person_type_id,
4503 last_name,
4504 start_date,
4505 comment_id,
4506 current_applicant_flag,
4507 current_emp_or_apl_flag,
4508 current_employee_flag,
4509 date_of_birth,
4510 first_name,
4511 full_name,
4512 middle_names,
4513 sex,
4514 title,
4515 pre_name_adjunct,
4516 suffix,
4517 national_identifier,
4518 attribute_category,
4519 attribute1,
4520 attribute2,
4521 attribute3,
4522 attribute4,
4523 attribute5,
4524 attribute6,
4525 attribute7,
4526 attribute8,
4527 attribute9,
4528 attribute10,
4529 attribute11,
4530 attribute12,
4531 attribute13,
4532 attribute14,
4533 attribute15,
4534 attribute16,
4535 attribute17,
4536 attribute18,
4537 attribute19,
4538 attribute20,
4539 attribute21,
4540 attribute22,
4541 attribute23,
4542 attribute24,
4543 attribute25,
4544 attribute26,
4545 attribute27,
4546 attribute28,
4547 attribute29,
4548 attribute30,
4549 per_information_category,
4550 per_information1,
4551 per_information2,
4552 per_information3,
4553 per_information4,
4554 per_information5,
4555 per_information6,
4556 per_information7,
4557 per_information8,
4558 per_information9,
4559 per_information10,
4560 per_information11,
4561 per_information12,
4562 per_information13,
4563 per_information14,
4564 per_information15,
4565 per_information16,
4566 per_information17,
4567 per_information18,
4568 per_information19,
4569 per_information20,
4570 per_information21,
4571 per_information22,
4572 per_information23,
4573 per_information24,
4574 per_information25,
4575 per_information26,
4576 per_information27,
4577 per_information28,
4578 per_information29,
4579 per_information30,
4580 known_as
4581 )
4582 values(
4583 l_per_rec.person_id,
4584 l_per_rec.effective_start_date,
4585 l_per_rec.effective_end_date,
4586 l_per_rec.business_group_id,
4587 l_per_rec.person_type_id,
4588 l_per_rec.last_name,
4589 l_per_rec.start_date,
4590 l_per_rec.comment_id,
4591 l_per_rec.current_applicant_flag,
4592 l_per_rec.current_emp_or_apl_flag,
4593 l_per_rec.current_employee_flag,
4594 l_per_rec.date_of_birth,
4595 l_per_rec.first_name,
4596 l_per_rec.full_name,
4597 l_per_rec.middle_names,
4598 l_per_rec.sex,
4599 l_per_rec.title,
4600 l_per_rec.pre_name_adjunct,
4601 l_per_rec.suffix,
4602 l_per_rec.national_identifier,
4603 l_per_rec.attribute_category,
4604 l_per_rec.attribute1,
4605 l_per_rec.attribute2,
4606 l_per_rec.attribute3,
4607 l_per_rec.attribute4,
4608 l_per_rec.attribute5,
4609 l_per_rec.attribute6,
4610 l_per_rec.attribute7,
4611 l_per_rec.attribute8,
4612 l_per_rec.attribute9,
4613 l_per_rec.attribute10,
4614 l_per_rec.attribute11,
4615 l_per_rec.attribute12,
4616 l_per_rec.attribute13,
4617 l_per_rec.attribute14,
4618 l_per_rec.attribute15,
4619 l_per_rec.attribute16,
4620 l_per_rec.attribute17,
4621 l_per_rec.attribute18,
4622 l_per_rec.attribute19,
4623 l_per_rec.attribute20,
4624 l_per_rec.attribute21,
4625 l_per_rec.attribute22,
4626 l_per_rec.attribute23,
4627 l_per_rec.attribute24,
4628 l_per_rec.attribute25,
4629 l_per_rec.attribute26,
4630 l_per_rec.attribute27,
4631 l_per_rec.attribute28,
4632 l_per_rec.attribute29,
4633 l_per_rec.attribute30,
4634 l_per_rec.per_information_category,
4635 l_per_rec.per_information1,
4636 l_per_rec.per_information2,
4637 l_per_rec.per_information3,
4638 l_per_rec.per_information4,
4639 l_per_rec.per_information5,
4640 l_per_rec.per_information6,
4641 l_per_rec.per_information7,
4642 l_per_rec.per_information8,
4643 l_per_rec.per_information9,
4644 l_per_rec.per_information10,
4645 l_per_rec.per_information11,
4646 l_per_rec.per_information12,
4647 l_per_rec.per_information13,
4648 l_per_rec.per_information14,
4649 l_per_rec.per_information15,
4650 l_per_rec.per_information16,
4651 l_per_rec.per_information17,
4652 l_per_rec.per_information18,
4653 l_per_rec.per_information19,
4654 l_per_rec.per_information20,
4655 l_per_rec.per_information21,
4656 l_per_rec.per_information22,
4657 l_per_rec.per_information23,
4658 l_per_rec.per_information24,
4659 l_per_rec.per_information25,
4660 l_per_rec.per_information26,
4661 l_per_rec.per_information27,
4662 l_per_rec.per_information28,
4663 l_per_rec.per_information29,
4664 l_per_rec.per_information30,
4665 l_per_rec.known_as
4666 );
4667 --
4668 -- Fox for bug 3390731 starts here.
4669 -- Update the start_date to the minimum of effective_start_date,
4670 -- in this case it is l_per_rec.start_date.
4671 --
4672 update per_all_people_f
4673 set start_date = l_per_rec.start_date
4674 where person_id = l_per_rec.person_id;
4675 --
4676 -- Fix for bug 3390731 ends here.
4677 --
4678 select per_person_type_usages_s.nextval into l_ptu_nextval
4679 from sys.dual;
4680 l_object_version_number := 1;
4681 --
4682 insert into per_person_type_usages_f(
4683 PERSON_TYPE_USAGE_ID,
4684 PERSON_ID,
4685 PERSON_TYPE_ID,
4686 EFFECTIVE_START_DATE,
4687 EFFECTIVE_END_DATE,
4688 OBJECT_VERSION_NUMBER
4689 )
4690 values
4691 (l_ptu_nextval,
4692 l_per_rec.person_id,
4693 l_per_rec.person_type_id,
4694 l_per_rec.effective_start_date,
4695 l_per_rec.effective_end_date,
4696 l_object_version_number
4697 );
4698 --
4699 end if;
4700 end if;
4701 hr_utility.set_location('Leaving '||l_proc,25);
4702 end ins_or_upd_precursor_row;
4703 --
4704 --
4705 PROCEDURE maintain_coverage
4706 (p_person_id in number
4707 ,p_type in varchar2) is
4708 --
4709 cursor csr_get_contacts(c_person_id number) is
4710 select contact_person_id, min(nvl(date_start,hr_api.g_sot)) date_start
4711 from per_contact_relationships
4712 where person_id = c_person_id
4713 group by contact_person_id;
4714 --
4715 cursor csr_get_person(c_contact_person_id number) is
4716 select ctr.person_id, min(nvl(ctr.date_start,hr_api.g_sot)) date_start
4717 from per_contact_relationships ctr
4718 ,per_person_type_usages_f ptu
4719 ,per_person_types ppt
4720 where ctr.contact_person_id = c_contact_person_id
4721 and ctr.person_id = ptu.person_id
4722 and ptu.person_type_id = ppt.person_type_id
4723 and ppt.system_person_type = 'EMP'
4724 group by ctr.person_id;
4725 --
4726 cursor csr_last_hire_date(c_person_id number) is
4727 select max(date_start)
4728 from per_periods_of_service
4729 where person_id = c_person_id;
4730 --
4731 l_proc varchar2(100) := 'per_people12_pkg.maintain_coverage';
4732 l_cov_date_start date;
4733 l_person_id number;
4734 l_contact_person_id number;
4735 l_ctr_date_start date;
4736 l_pds_date_start date;
4737 --
4738 begin
4739 if p_type = 'EMP' then
4740 hr_utility.set_location(l_proc,5);
4741 open csr_last_hire_date(p_person_id);
4742 fetch csr_last_hire_date into l_pds_date_start;
4743 close csr_last_hire_date;
4744 --
4745 for l_cov_rec --contact_person_id, date_start
4746 in csr_get_contacts(p_person_id)
4747 loop
4748 if l_cov_rec.date_start > l_pds_date_start then
4749 l_cov_date_start := l_cov_rec.date_start;
4750 else
4751 l_cov_date_start := l_pds_date_start;
4752 end if;
4753 hr_utility.set_location(l_proc,15);
4754 ins_or_upd_precursor_row(l_cov_rec.contact_person_id, l_cov_date_start);
4755 end loop;
4756 elsif p_type = 'CONT' then
4757 hr_utility.set_location(l_proc,20);
4758 for l_cov_rec1 --person_id, date_start
4759 in csr_get_person(p_person_id)
4760 loop
4761 open csr_last_hire_date(l_cov_rec1.person_id);
4762 fetch csr_last_hire_date into l_pds_date_start;
4763 close csr_last_hire_date;
4764 --
4765 if l_cov_rec1.date_start > l_pds_date_start then
4766 l_cov_date_start := l_cov_rec1.date_start;
4767 else
4768 l_cov_date_start := l_pds_date_start;
4769 end if;
4770 hr_utility.set_location(l_proc,30);
4771 ins_or_upd_precursor_row(p_person_id, l_cov_date_start);
4772 end loop;
4773 end if;
4774 hr_utility.set_location('Leaving '||l_proc,40);
4775 end maintain_coverage;
4776 --
4777 --
4778 END PER_PEOPLE12_PKG;