[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE_V7_PKG
Source
1 PACKAGE BODY PER_PEOPLE_V7_PKG as
2 /* $Header: peper07t.pkb 120.7.12020000.2 2013/01/18 07:31:38 srannama ship $ */
3 --
4 --bug no 6028006 starts here
5 --g_package varchar2(18) := ' PER_PEOPLE_V7_PKG.';
6 g_package varchar2(18) := 'PER_PEOPLE_V7_PKG.';
7 --bug no 6028006 ends here
8 g_debug boolean; -- debug flag
9 --
10 PROCEDURE Update_Row(X_Rowid VARCHAR2,
11 X_Person_Id NUMBER,
12 X_Effective_Start_Date IN OUT NOCOPY DATE,
13 X_Effective_End_Date IN OUT NOCOPY DATE,
14 X_Business_Group_Id NUMBER,
15 X_Person_Type_Id NUMBER,
16 X_Last_Name VARCHAR2,
17 X_Start_Date IN OUT NOCOPY DATE,
18 X_Hire_date DATE,
19 X_S_Hire_Date DATE,
20 X_Period_of_service_id NUMBER,
21 X_Termination_Date DATE,
22 X_S_Termination_Date DATE,
23 X_Applicant_Number VARCHAR2,
24 X_Comment_Id NUMBER,
25 X_Current_Applicant_Flag VARCHAR2,
26 X_Current_Emp_Or_Apl_Flag VARCHAR2,
27 X_Current_Employee_Flag VARCHAR2,
28 X_Date_Employee_Data_Verified DATE,
29 X_Date_Of_Birth DATE,
30 X_Email_Address VARCHAR2,
31 X_Employee_Number VARCHAR2,
32 X_Expense_Check_To_Address VARCHAR2,
33 X_First_Name VARCHAR2,
34 X_Full_Name VARCHAR2,
35 X_Known_As VARCHAR2,
36 X_Marital_Status VARCHAR2,
37 X_Middle_Names VARCHAR2,
38 X_Nationality VARCHAR2,
39 X_National_Identifier VARCHAR2,
40 X_Previous_Last_Name VARCHAR2,
41 X_Registered_Disabled_Flag VARCHAR2,
42 X_Sex VARCHAR2,
43 X_Title VARCHAR2,
44 X_Vendor_Id NUMBER,
45 X_Work_Telephone VARCHAR2,
46 X_Attribute_Category VARCHAR2,
47 X_Attribute1 VARCHAR2,
48 X_Attribute2 VARCHAR2,
49 X_Attribute3 VARCHAR2,
50 X_Attribute4 VARCHAR2,
51 X_Attribute5 VARCHAR2,
52 X_Attribute6 VARCHAR2,
53 X_Attribute7 VARCHAR2,
54 X_Attribute8 VARCHAR2,
55 X_Attribute9 VARCHAR2,
56 X_Attribute10 VARCHAR2,
57 X_Attribute11 VARCHAR2,
58 X_Attribute12 VARCHAR2,
59 X_Attribute13 VARCHAR2,
60 X_Attribute14 VARCHAR2,
61 X_Attribute15 VARCHAR2,
62 X_Attribute16 VARCHAR2,
63 X_Attribute17 VARCHAR2,
64 X_Attribute18 VARCHAR2,
65 X_Attribute19 VARCHAR2,
66 X_Attribute20 VARCHAR2,
67 X_Attribute21 VARCHAR2,
68 X_Attribute22 VARCHAR2,
69 X_Attribute23 VARCHAR2,
70 X_Attribute24 VARCHAR2,
71 X_Attribute25 VARCHAR2,
72 X_Attribute26 VARCHAR2,
73 X_Attribute27 VARCHAR2,
74 X_Attribute28 VARCHAR2,
75 X_Attribute29 VARCHAR2,
76 X_Attribute30 VARCHAR2,
77 X_Per_Information_Category VARCHAR2,
78 X_Per_Information1 VARCHAR2,
79 X_Per_Information2 VARCHAR2,
80 X_Per_Information3 VARCHAR2,
81 X_Per_Information4 VARCHAR2,
82 X_Per_Information5 VARCHAR2,
83 X_Per_Information6 VARCHAR2,
84 X_Per_Information7 VARCHAR2,
85 X_Per_Information8 VARCHAR2,
86 X_Per_Information9 VARCHAR2,
87 X_Per_Information10 VARCHAR2,
88 X_Per_Information11 VARCHAR2,
89 X_Per_Information12 VARCHAR2,
90 X_Per_Information13 VARCHAR2,
91 X_Per_Information14 VARCHAR2,
92 X_Per_Information15 VARCHAR2,
93 X_Per_Information16 VARCHAR2,
94 X_Per_Information17 VARCHAR2,
95 X_Per_Information18 VARCHAR2,
96 X_Per_Information19 VARCHAR2,
97 X_Per_Information20 VARCHAR2,
98 X_Per_Information21 VARCHAR2,
99 X_Per_Information22 VARCHAR2,
100 X_Per_Information23 VARCHAR2,
101 X_Per_Information24 VARCHAR2,
102 X_Per_Information25 VARCHAR2,
103 X_Per_Information26 VARCHAR2,
104 X_Per_Information27 VARCHAR2,
105 X_Per_Information28 VARCHAR2,
106 X_Per_Information29 VARCHAR2,
107 X_Per_Information30 VARCHAR2,
108 X_Last_Update_Date DATE,
109 X_Last_Updated_By NUMBER,
110 X_Last_Update_Login NUMBER,
111 X_Current_Application_Id NUMBER, --DEFAULT NULL
112 X_Order_Name VARCHAR2,
113 X_Global_Name VARCHAR2,
114 X_Local_Name VARCHAR2
115 ) IS
116 --
117 l_old_work_telephone per_phones.phone_number%TYPE;
118 l_phn_object_version_number per_phones.object_version_number%TYPE;
119 l_phone_id per_phones.phone_id%TYPE;
120 --
121 cursor csr_phones is
122 select phone_number, phone_id, phn.object_version_number
123 from per_phones phn
124 --
125 -- Removed fnd_sessions_f table in From Clause
126 -- becuase it is not used in the data retrieval at all.
127 -- fnd_sessions f
128 --
129 where phn.parent_id = x_person_id
130 and phn.parent_table = 'PER_ALL_PEOPLE_F'
131 and phn.phone_type = 'W1'
132 and x_start_date between phn.date_from and
133 nvl(phn.date_to,x_start_date);
134 /* BEGIN OF WWBUG 1975359 */
135 cursor c1 is
136 select party_id
137 from per_all_people_f
138 where person_id = x_person_id;
139 --
140 l_party_id number;
141 --
142 cursor c_person is
143 select *
144 from per_all_people_f
145 where person_id = x_person_id
146 and x_effective_start_date
147 between effective_start_date
148 and effective_end_date;
149 --
150 l_person per_all_people_f%rowtype;
151 --
152 /* END OF WWBUG 1975359 */
153 BEGIN
154 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',10);
155 if X_Current_Application_Id = 810
156 then
157 per_people_v7_pkg.check_person_changes(X_person_id
158 ,X_hire_date
159 ,X_s_hire_Date
160 ,X_current_application_id );
161 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',20);
162 end if;
163 if X_termination_date is not null
164 and X_S_termination_date is null
165 and X_current_employee_flag = 'Y'
166 then
167 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',30);
168 per_people_v7_pkg.check_person_changes(X_person_id
169 ,X_termination_date
170 ,X_Hire_date
171 ,X_current_application_id );
172 X_effective_end_date := X_termination_date;
173 elsif x_hire_date <> X_s_hire_date then
174 X_effective_start_date := X_hire_date;
175 X_start_date := X_hire_date;
176 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',40);
177 end if;
178 /* BEGIN OF WWBUG 1975359 */
179 open c1;
180 --
181 fetch c1 into l_party_id;
182 --
183 close c1;
184 /* END OF WWBUG 1975359 */
185 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',50);
186 UPDATE PER_PEOPLE_F
187 SET
188 person_id = X_Person_Id,
189 party_id = l_Party_Id,
190 effective_start_date = X_Effective_Start_Date,
191 effective_end_date = X_Effective_End_Date,
192 business_group_id = X_Business_Group_Id,
193 person_type_id = -- X_Person_Type_Id,
194 hr_person_type_usage_info.get_default_person_type_id(X_Person_Type_Id),
195 last_name = X_Last_Name,
196 start_date = X_Start_Date,
197 applicant_number = X_Applicant_Number,
198 comment_id = X_Comment_Id,
199 current_applicant_flag = X_Current_Applicant_Flag,
200 current_emp_or_apl_flag = X_Current_Emp_Or_Apl_Flag,
201 current_employee_flag = X_Current_Employee_Flag,
202 date_employee_data_verified = X_Date_Employee_Data_Verified,
203 date_of_birth = X_Date_Of_Birth,
204 email_address = X_Email_Address,
205 employee_number = X_Employee_Number,
206 expense_check_send_to_address = X_Expense_Check_To_Address,
207 first_name = X_First_Name,
208 full_name = X_Full_Name,
209 known_as = X_Known_As,
210 marital_status = X_Marital_Status,
211 middle_names = X_Middle_Names,
212 nationality = X_Nationality,
213 national_identifier = X_National_Identifier,
214 previous_last_name = X_Previous_Last_Name,
215 registered_disabled_flag = X_Registered_Disabled_Flag,
216 sex = X_Sex,
217 title = X_Title,
218 vendor_id = X_Vendor_Id,
219 -- The update of the work_telephone data is now done on the PER_PHONES table.
220 -- work_telephone = X_Work_Telephone,
221 attribute_category = X_Attribute_Category,
222 attribute1 = X_Attribute1,
223 attribute2 = X_Attribute2,
224 attribute3 = X_Attribute3,
225 attribute4 = X_Attribute4,
226 attribute5 = X_Attribute5,
227 attribute6 = X_Attribute6,
228 attribute7 = X_Attribute7,
229 attribute8 = X_Attribute8,
230 attribute9 = X_Attribute9,
231 attribute10 = X_Attribute10,
232 attribute11 = X_Attribute11,
233 attribute12 = X_Attribute12,
234 attribute13 = X_Attribute13,
235 attribute14 = X_Attribute14,
236 attribute15 = X_Attribute15,
237 attribute16 = X_Attribute16,
238 attribute17 = X_Attribute17,
239 attribute18 = X_Attribute18,
240 attribute19 = X_Attribute19,
241 attribute20 = X_Attribute20,
242 attribute21 = X_Attribute21,
243 attribute22 = X_Attribute22,
244 attribute23 = X_Attribute23,
245 attribute24 = X_Attribute24,
246 attribute25 = X_Attribute25,
247 attribute26 = X_Attribute26,
248 attribute27 = X_Attribute27,
249 attribute28 = X_Attribute28,
250 attribute29 = X_Attribute29,
251 attribute30 = X_Attribute30,
252 per_information_category = X_Per_Information_Category,
253 per_information1 = X_Per_Information1,
254 per_information2 = X_Per_Information2,
255 per_information3 = X_Per_Information3,
256 per_information4 = X_Per_Information4,
257 per_information5 = X_Per_Information5,
258 per_information6 = X_Per_Information6,
259 per_information7 = X_Per_Information7,
260 per_information8 = X_Per_Information8,
261 per_information9 = X_Per_Information9,
262 per_information10 = X_Per_Information10,
263 per_information11 = X_Per_Information11,
264 per_information12 = X_Per_Information12,
265 per_information13 = X_Per_Information13,
266 per_information14 = X_Per_Information14,
267 per_information15 = X_Per_Information15,
268 per_information16 = X_Per_Information16,
269 per_information17 = X_Per_Information17,
270 per_information18 = X_Per_Information18,
271 per_information19 = X_Per_Information19,
272 per_information20 = X_Per_Information20,
273 per_information21 = X_Per_Information21,
274 per_information22 = X_Per_Information22,
275 per_information23 = X_Per_Information23,
276 per_information24 = X_Per_Information24,
277 per_information25 = X_Per_Information25,
278 per_information26 = X_Per_Information26,
279 per_information27 = X_Per_Information27,
280 per_information28 = X_Per_Information28,
281 per_information29 = X_Per_Information29,
282 per_information30 = X_Per_Information30,
283 order_name = X_Order_Name, -- #3889584
284 global_name = X_Global_Name,
285 local_name = X_Local_Name,
286 last_update_date = X_Last_Update_Date,
287 last_updated_by = X_Last_Updated_By,
288 last_update_login = X_Last_Update_Login
289 WHERE rowid = X_rowid;
290 if (SQL%NOTFOUND) then
291 RAISE NO_DATA_FOUND;
292 end if;
293 --
294 -- #2221990
295 -- This should be added later depending on the type of transaction
296 -- generated.
297 --
298 -- PTU : Start of Changes
299 --
300 -- hr_per_type_usage_internal.maintain_person_type_usage
301 -- (p_effective_date => X_effective_Start_Date
302 -- ,p_person_id => X_Person_Id
303 -- ,p_person_type_id => X_Person_Type_Id
304 -- ,p_datetrack_update_mode=> 'CORRECTION'
305 -- );
306 --
307 hr_utility.set_location('per_people_v7_pkg.update_row',55);
308 -- PTU : End of Changes
309
310 /* BEGIN OF WWBUG 1975359 */
311 --
312 open c_person;
313 --
314 fetch c_person into l_person;
315 --
316 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',60);
317 close c_person;
318 --
319 per_hrtca_merge.update_tca_person(p_Rec => l_person);
320 --
321 hr_utility.set_location('update_row - after update',1);
322 --
323 /* END OF WWBUG 1975359 */
324 --
325 -- HR/WF Synchronization call
326 --
327 per_hrwf_synch.per_per_wf(p_rec => l_person,
328 p_action => 'UPDATE');
329 --
330 --
331 -- Beginning of logic for the update of the phones table
332 --
333 -- Firstly, find the number, ovn and id for the old work_telephone (if it exists)
334 --
335 open csr_phones;
336 fetch csr_phones into l_old_work_telephone,
337 l_phone_id,
338 l_phn_object_version_number;
339 close csr_phones;
340 --
341 -- If old entry is null and new entry is not null then just use the create
342 -- phone B.P.
343 --
344 if (l_old_work_telephone is null and
345 x_work_telephone is not null) then
346 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',70);
347 hr_phone_api.create_phone
348 (p_date_from => x_start_date
349 ,p_date_to => null
350 ,p_phone_type => 'W1'
351 ,p_phone_number => x_work_telephone
352 ,p_parent_id => x_person_id
353 ,p_parent_table => 'PER_ALL_PEOPLE_F'
354 ,p_validate => FALSE
355 ,p_effective_date => x_start_date
356 ,p_object_version_number => l_phn_object_version_number --out
357 ,p_phone_id => l_phone_id --out
358 );
359 --
360 -- If old entry is not null and corrected entry is null then delete the phone.
361 --
362 elsif l_old_work_telephone is not null and x_work_telephone is null then
363 hr_phone_api.delete_phone(FALSE, l_phone_id, l_phn_object_version_number);
364 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',80);
365 --
366 -- If old and corrected entries are both not null then update the row with
367 -- no changes to start and to dates.
368 --
369 elsif l_old_work_telephone is not null and x_work_telephone is not null then
370 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',90);
371 --
372 -- 115.22: only one row is maintained in per_phones.
373 -- when changing the phone, date_to should remain null. This keeps the
374 -- row "open" until the "end of time"
375 if ( l_old_work_telephone <> x_work_telephone) then
376 -- Bug 5554383 : Added IF condition.
377 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',95);
378 hr_phone_api.update_phone
379 (p_phone_id => l_phone_id,
380 p_phone_number => x_work_telephone,
381 p_date_to => null, -- 1020074 x_termination_date,
382 p_object_version_number => l_phn_object_version_number,
383 p_effective_date => x_effective_start_date); -- 1020074 x_start_date
384 end if;
385 end if;
386 --
387 -- End of logic for the update of the phones table
388 --
389 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',100);
390 --
391 if X_termination_date is not null
392 and X_S_termination_date is null
393 and X_current_employee_flag = 'Y'
394 then
395 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',110);
396 terminate_employment(X_period_of_service_id
397 ,X_person_id
398 ,X_Rowid
399 ,X_Business_group_id
400 ,X_termination_date);
401 elsif X_hire_date <> X_s_hire_date
402 then
403 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',120);
404 update_employee_rows(X_period_of_service_id
405 ,X_person_id
406 ,X_hire_date
407 ,X_s_hire_date);
408 end if;
409 hr_utility.set_location('Entering : per_people_v7_pkg.update_row',130);
410 END Update_Row;
411 --
412 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
413 BEGIN
414 DELETE FROM PER_PEOPLE_F
415 WHERE rowid = X_Rowid;
416 if (SQL%NOTFOUND) then
417 RAISE NO_DATA_FOUND;
418 end if;
419 END Delete_Row;
420 --
421 -- #2975471
422 -- ------------------------------------------------------------------ +
423 -- ---------------------<< create_EX_EMP >>-------------------------- |
424 -- ------------------------------------------------------------------ +
425 procedure create_EX_EMP( p_person_rec per_all_people_f%ROWTYPE
426 ,p_pos_id NUMBER
427 ,p_person_id NUMBER
428 ,p_Business_group_id NUMBER
429 ,p_start_date DATE
430 ,p_end_date DATE)
431 IS
432 --
433 c_proc_name varchar2(100) := 'PER_PEOPLE_V7_PKG.create_EX_EMP';
434 --
435 cursor csr_ex_status is
436 select ppt.person_type_id
437 , pst.current_applicant_flag
438 , pst.current_emp_or_apl_flag
439 , pst.current_employee_flag
440 from per_person_types ppt
441 , per_startup_person_types pst
442 where ppt.business_group_id = p_business_group_id -- Bug fix 3645987
443 and pst.system_person_type = ppt.system_person_type
444 and ppt.active_flag = 'Y'
445 and ppt.default_flag = 'Y'
446 and pst.system_person_type = 'EX_EMP';
447 --
448 l_person_type_id number;
449 l_current_emp_or_apl_flag VARCHAR2(1);
450 l_current_applicant_flag VARCHAR2(1);
451 l_current_employee_flag VARCHAR2(1);
452 l_dummy_date DATE;
453 l_period_of_service_id NUMBER;
454 l_rowid VARCHAR2(18);
455 l_person_id per_all_people_f.person_id%TYPE;
456
457 --
458 begin
459 hr_utility.set_location('Entering: '||c_proc_name,10);
460 hr_utility.trace('p_start_date = '||to_char(p_start_date,'DD-MON-YYYY'));
461
462 l_person_id := p_person_id;
463 l_period_of_service_id := p_pos_id;
464
465 open csr_ex_status;
466 fetch csr_ex_status into l_person_type_id,l_current_emp_or_apl_flag
467 ,l_current_applicant_flag,l_current_employee_flag;
468 close csr_ex_status;
469
470 INSERT INTO PER_ALL_PEOPLE_F(
471 person_id,
472 effective_start_date,
473 effective_end_date,
474 business_group_id,
475 person_type_id,
476 last_name,
477 start_date,
478 applicant_number,
479 comment_id,
480 current_applicant_flag,
481 current_emp_or_apl_flag,
482 current_employee_flag,
483 date_employee_data_verified,
484 date_of_birth,
485 email_address,
486 employee_number,
487 expense_check_send_to_address,
488 first_name,
489 full_name,
490 known_as,
491 marital_status,
492 middle_names,
493 nationality,
494 national_identifier,
495 previous_last_name,
496 registered_disabled_flag,
497 sex,
498 title,
499 vendor_id,
500 attribute_category,
501 attribute1,
502 attribute2,
503 attribute3,
504 attribute4,
505 attribute5,
506 attribute6,
507 attribute7,
508 attribute8,
509 attribute9,
510 attribute10,
511 attribute11,
512 attribute12,
513 attribute13,
514 attribute14,
515 attribute15,
516 attribute16,
517 attribute17,
518 attribute18,
519 attribute19,
520 attribute20,
521 attribute21,
522 attribute22,
523 attribute23,
524 attribute24,
525 attribute25,
526 attribute26,
527 attribute27,
528 attribute28,
529 attribute29,
530 attribute30,
531 per_information_category,
532 per_information1,
533 per_information2,
534 per_information3,
535 per_information4,
536 per_information5,
537 per_information6,
538 per_information7,
539 per_information8,
540 per_information9,
541 per_information10,
542 per_information11,
543 per_information12,
544 per_information13,
545 per_information14,
546 per_information15,
547 per_information16,
548 per_information17,
549 per_information18,
550 per_information19,
551 per_information20,
552 per_information21,
553 per_information22,
554 per_information23,
555 per_information24,
556 per_information25,
557 per_information26,
558 per_information27,
559 per_information28,
560 per_information29,
561 per_information30,
562 last_update_date,
563 last_updated_by,
564 last_update_login,
565 created_by,
566 creation_date,
567 town_of_birth,
568 region_of_birth,
569 country_of_birth,
570 global_person_id,
571 party_id,
572 blood_type,
573 correspondence_language,
574 honors,
575 pre_name_adjunct,
576 rehire_authorizor,
577 rehire_recommendation,
578 resume_exists,
579 resume_last_updated,
580 second_passport_exists,
581 student_status,
582 suffix,
583 date_of_death,
584 uses_tobacco_flag,
585 fast_path_employee,
586 fte_capacity,
587 order_name,
588 global_name,
589 local_name
590 ) VALUES (
591 l_Person_Id,
592 p_start_date,
593 p_end_date,
594 p_person_rec.Business_Group_Id,
595 hr_person_type_usage_info.get_default_person_type_id(l_person_type_id),
596 p_person_rec.Last_Name,
597 p_person_rec.Start_Date,
598 p_person_rec.Applicant_Number,
599 p_person_rec.Comment_Id,
600 l_Current_Applicant_Flag,
601 l_Current_Emp_Or_Apl_Flag,
602 l_Current_Employee_Flag,
603 p_person_rec.Date_Employee_Data_Verified,
604 p_person_rec.Date_Of_Birth,
605 p_person_rec.Email_Address,
606 p_person_rec.Employee_Number,
607 p_person_rec.expense_check_send_to_address,
608 p_person_rec.First_Name,
609 p_person_rec.Full_Name,
610 p_person_rec.Known_As,
611 p_person_rec.Marital_Status,
612 p_person_rec.Middle_Names,
613 p_person_rec.Nationality,
614 p_person_rec.National_Identifier,
615 p_person_rec.Previous_Last_Name,
616 p_person_rec.Registered_Disabled_Flag,
617 p_person_rec.Sex,
618 p_person_rec.Title,
619 p_person_rec.Vendor_Id,
620 p_person_rec.Attribute_Category,
621 p_person_rec.Attribute1,
622 p_person_rec.Attribute2,
623 p_person_rec.Attribute3,
624 p_person_rec.Attribute4,
625 p_person_rec.Attribute5,
626 p_person_rec.Attribute6,
627 p_person_rec.Attribute7,
628 p_person_rec.Attribute8,
629 p_person_rec.Attribute9,
630 p_person_rec.Attribute10,
631 p_person_rec.Attribute11,
632 p_person_rec.Attribute12,
633 p_person_rec.Attribute13,
634 p_person_rec.Attribute14,
635 p_person_rec.Attribute15,
636 p_person_rec.Attribute16,
637 p_person_rec.Attribute17,
638 p_person_rec.Attribute18,
639 p_person_rec.Attribute19,
640 p_person_rec.Attribute20,
641 p_person_rec.Attribute21,
642 p_person_rec.Attribute22,
643 p_person_rec.Attribute23,
644 p_person_rec.Attribute24,
645 p_person_rec.Attribute25,
646 p_person_rec.Attribute26,
647 p_person_rec.Attribute27,
648 p_person_rec.Attribute28,
649 p_person_rec.Attribute29,
650 p_person_rec.Attribute30,
651 p_person_rec.per_information_category,
652 p_person_rec.per_information1,
653 p_person_rec.per_information2,
654 p_person_rec.per_information3,
655 p_person_rec.per_information4,
656 p_person_rec.per_information5,
657 p_person_rec.per_information6,
658 p_person_rec.per_information7,
659 p_person_rec.per_information8,
660 p_person_rec.per_information9,
661 p_person_rec.per_information10,
662 p_person_rec.per_information11,
663 p_person_rec.per_information12,
664 p_person_rec.per_information13,
665 p_person_rec.per_information14,
666 p_person_rec.per_information15,
667 p_person_rec.per_information16,
668 p_person_rec.per_information17,
669 p_person_rec.per_information18,
670 p_person_rec.per_information19,
671 p_person_rec.per_information20,
672 p_person_rec.per_information21,
673 p_person_rec.per_information22,
674 p_person_rec.per_information23,
675 p_person_rec.per_information24,
676 p_person_rec.per_information25,
677 p_person_rec.per_information26,
678 p_person_rec.per_information27,
679 p_person_rec.per_information28,
680 p_person_rec.per_information29,
681 p_person_rec.per_information30,
682 p_person_rec.Last_Update_Date,
683 p_person_rec.Last_Updated_By,
684 p_person_rec.Last_Update_Login,
685 p_person_rec.Created_By,
686 p_person_rec.Creation_Date,
687 p_person_rec.town_of_birth,
688 p_person_rec.region_of_birth,
689 p_person_rec.country_of_birth,
690 p_person_rec.global_person_id,
691 p_person_rec.party_id,
692 p_person_rec.blood_type,
693 p_person_rec.correspondence_language,
694 p_person_rec.honors,
695 p_person_rec.pre_name_adjunct,
696 p_person_rec.rehire_authorizor,
697 p_person_rec.rehire_recommendation,
698 p_person_rec.resume_exists,
699 p_person_rec.resume_last_updated,
700 p_person_rec.second_passport_exists,
701 p_person_rec.student_status,
702 p_person_rec.suffix,
703 p_person_rec.date_of_death,
704 p_person_rec.uses_tobacco_flag,
705 p_person_rec.fast_path_employee,
706 p_person_rec.fte_capacity,
707 p_person_rec.order_name,
708 p_person_rec.global_name,
709 p_person_rec.local_name);
710
711 hr_utility.set_location('Leaving: '||c_proc_name,99);
712
713 end create_EX_EMP;
714 --
715 --
716
717 procedure terminate_employment(X_period_of_service_id NUMBER
718 ,X_person_id NUMBER
719 ,X_Rowid VARCHAR2
720 ,X_Business_group_id NUMBER
721 ,X_termination_Date DATE)
722 IS
723
724 cursor period
725 is
726 select actual_termination_date
727 from per_periods_of_service
728 where period_of_service_id = X_period_of_service_id
729 for update of actual_termination_date;
730 --
731 cursor assignment
732 is
733 select effective_end_date
734 from per_assignments_f
735 where period_of_service_id = X_period_of_service_id
736 and effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
737 for update of effective_end_date;
738 --
739 cursor ex_status is
740 select ppt.person_type_id
741 , pst.current_applicant_flag
742 , pst.current_emp_or_apl_flag
743 , pst.current_employee_flag
744 from per_person_types ppt
745 , per_startup_person_types pst
746 where ppt.business_group_id = X_business_group_id --bug fix 3645987
747 and pst.system_person_type = ppt.system_person_type
748 and ppt.active_flag = 'Y'
749 and ppt.default_flag = 'Y'
750 and pst.system_person_type = 'EX_EMP';
751 cursor person
752 is
753 select *
754 from per_people_f
755 where rowid = chartorowid(X_Rowid);
756 --
757 l_proc varchar2(20) := 'terminate_employment';
758 --
759 per_rec person%ROWTYPE;
760 l_person_type_id number;
761 l_current_emp_or_apl_flag VARCHAR2(1);
762 l_current_applicant_flag VARCHAR2(1);
763 l_current_employee_flag VARCHAR2(1);
764 l_dummy_date DATE;
765 l_rowid VARCHAR2(18);
766 l_period_of_service_id NUMBER;
767 --
768 -- START WWBUG 1390173 fix
769 --
770 l_old ben_pps_ler.g_pps_ler_rec;
771 l_new ben_pps_ler.g_pps_ler_rec;
772 --
773 -- END WWBUG 1390173 fix
774 --
775 cursor c1 is
776 select *
777 from per_periods_of_service
778 where period_of_service_id = x_period_of_service_id;
779 --
780 l_c1 c1%rowtype;
781 --
782 begin
783 g_debug := hr_utility.debug_enabled; -- get debug status
784 IF g_debug THEN
785 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
786 END IF;
787
788 open period;
789 fetch period into l_dummy_date;
790 close period;
791 --
792 -- START WWBUG 1390173 fix
793 --
794 IF g_debug THEN
795 hr_utility.set_location('Entering: '|| g_package || l_proc, 10);
796 END IF;
797 open c1;
798 fetch c1 into l_c1;
799 if c1%found then
800 --
801 l_old.PERSON_ID := l_c1.person_id;
802 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
803 l_old.DATE_START := l_c1.date_start;
804 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
805 l_old.LEAVING_REASON := l_c1.leaving_reason;
806 l_old.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
807 l_old.ATTRIBUTE1 := l_c1.attribute1;
808 l_old.ATTRIBUTE2 := l_c1.attribute2;
809 l_old.ATTRIBUTE3 := l_c1.attribute3;
810 l_old.ATTRIBUTE4 := l_c1.attribute4;
811 l_old.ATTRIBUTE5 := l_c1.attribute5;
812 l_old.final_process_date := l_c1.final_process_date;
813 l_new.PERSON_ID := l_c1.person_id;
814 l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
815 l_new.DATE_START := l_c1.date_start;
816 l_new.ACTUAL_TERMINATION_DATE := x_termination_date;
817 l_new.LEAVING_REASON := l_c1.leaving_reason;
818 l_new.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
819 l_new.ATTRIBUTE1 := l_c1.attribute1;
820 l_new.ATTRIBUTE2 := l_c1.attribute2;
821 l_new.ATTRIBUTE3 := l_c1.attribute3;
822 l_new.ATTRIBUTE4 := l_c1.attribute4;
823 l_new.ATTRIBUTE5 := l_c1.attribute5;
824 l_new.final_process_date := X_termination_date;
825 --
826 ben_pps_ler.ler_chk(p_old => l_old
827 ,p_new => l_new
828 ,p_event => 'UPDATING'
829 ,p_effective_date => l_c1.date_start);
830 --
831 end if;
832 close c1;
833 --
834 -- END WWBUG 1390173 fix
835 --
836 --START HR/WF Synchronization --tpapired
837 IF g_debug THEN
838 hr_utility.set_location('Entering: '|| g_package || l_proc, 20);
839 END IF;
840 per_hrwf_synch.per_pds_wf(
841 p_rec => l_c1,
842 p_date => X_termination_date,
843 p_action => 'TERMINATION');
844 --
845 --END HR/WF Synchronization --tpapired
846 --
847 begin
848 update per_periods_of_service
849 set actual_termination_date = X_termination_date
850 , final_process_date = X_termination_date
851 , last_standard_process_date = X_termination_date
852 where period_of_service_id = X_period_of_service_id;
853 end;
854 --
855
856 IF g_debug THEN
857 hr_utility.set_location('Entering: '|| g_package || l_proc, 30);
858 END IF;
859
860 open assignment;
861 loop
862 fetch assignment into l_dummy_date;
863 exit when assignment%notfound;
864 end loop;
865 close assignment;
866 begin
867 update per_assignments_f
868 set effective_end_date = X_termination_date
869 where period_of_service_id = X_period_of_service_id
870 and effective_end_date = to_date('31-12-4712','DD-MM-YYYY');
871 end;
872 --
873 IF g_debug THEN
874 hr_utility.set_location('Entering: '|| g_package || l_proc, 40);
875 END IF;
876
877 open ex_status;
878 fetch ex_status into l_person_type_id,l_current_emp_or_apl_flag
879 ,l_current_applicant_flag,l_current_employee_flag;
880
881 close ex_status;
882 open person;
883 fetch person into per_rec;
884 close person;
885 --
886 PER_PEOPLE_V14_PKG.Insert_Row(
887 X_Rowid => l_rowid,
888 X_Person_Id => Per_rec.Person_Id,
889 X_Party_Id => Per_rec.Party_Id,
890 X_Effective_Start_Date => X_termination_date + 1,
891 X_Effective_End_Date => to_date('31-12-4712','DD-MM-YYYY'),
892 X_Business_Group_Id => Per_rec.Business_Group_Id,
893 X_Person_Type_Id => l_person_type_id,
894 X_Last_Name => Per_rec.Last_Name,
895 X_Start_Date => Per_rec.Start_Date,
896 X_Applicant_Number => Per_rec.Applicant_Number,
897 X_Comment_Id => Per_rec.Comment_Id,
898 X_Current_Applicant_Flag=> l_current_applicant_flag,
899 X_Current_Emp_Or_Apl_Flag=> l_current_emp_or_apl_flag,
900 X_Current_Employee_Flag=> l_current_employee_flag,
901 X_Date_Employee_Data_Verified=> Per_rec.Date_Employee_Data_Verified,
902 X_Date_Of_Birth => Per_rec.Date_Of_Birth,
903 X_Email_Address => Per_rec.Email_Address,
904 X_Employee_Number => Per_rec.Employee_Number,
905 X_Expense_Check_To_Address=> Per_rec.expense_check_send_to_address,
906 X_First_Name => Per_rec.First_Name,
907 X_Full_Name => Per_rec.Full_Name,
908 X_Known_As => Per_rec.Known_As,
909 X_Marital_Status => Per_rec.Marital_Status,
910 X_Middle_Names => Per_rec.Middle_Names,
911 X_Nationality => Per_rec.Nationality,
912 X_National_Identifier => Per_rec.National_Identifier,
913 X_Previous_Last_Name => Per_rec.Previous_Last_Name,
914 X_Registered_Disabled_Flag=> Per_rec.Registered_Disabled_Flag,
915 X_Sex => Per_rec.Sex,
916 X_Title => Per_rec.Title,
917 X_Vendor_Id => Per_rec.Vendor_Id,
918 --
919 -- 115.22 : We need to ensure the per_phones table does not create rows.
920 -- Phone changes are handled by the "update_phone" api
921 --
922 X_Work_Telephone => null, -- 1020074 Per_rec.Work_Telephone,
923 X_Attribute_Category => Per_rec.Attribute_Category,
924 X_Attribute1 => Per_rec.Attribute1,
925 X_Attribute2 => Per_rec.Attribute2,
926 X_Attribute3 => Per_rec.Attribute3,
927 X_Attribute4 => Per_rec.Attribute4,
928 X_Attribute5 => Per_rec.Attribute5,
929 X_Attribute6 => Per_rec.Attribute6,
930 X_Attribute7 => Per_rec.Attribute7,
931 X_Attribute8 => Per_rec.Attribute8,
932 X_Attribute9 => Per_rec.Attribute9,
933 X_Attribute10 => Per_rec.Attribute10,
934 X_Attribute11 => Per_rec.Attribute11,
935 X_Attribute12 => Per_rec.Attribute12,
936 X_Attribute13 => Per_rec.Attribute13,
937 X_Attribute14 => Per_rec.Attribute14,
938 X_Attribute15 => Per_rec.Attribute15,
939 X_Attribute16 => Per_rec.Attribute16,
940 X_Attribute17 => Per_rec.Attribute17,
941 X_Attribute18 => Per_rec.Attribute18,
942 X_Attribute19 => Per_rec.Attribute19,
943 X_Attribute20 => Per_rec.Attribute20,
944 X_Attribute21 => Per_rec.Attribute21,
945 X_Attribute22 => Per_rec.Attribute22,
946 X_Attribute23 => Per_rec.Attribute23,
947 X_Attribute24 => Per_rec.Attribute24,
948 X_Attribute25 => Per_rec.Attribute25,
949 X_Attribute26 => Per_rec.Attribute26,
950 X_Attribute27 => Per_rec.Attribute27,
951 X_Attribute28 => Per_rec.Attribute28,
952 X_Attribute29 => Per_rec.Attribute29,
953 X_Attribute30 => Per_rec.Attribute30,
954 X_Per_Information_Category => Per_rec.Per_Information_Category,
955 X_Per_Information1 => Per_rec.Per_Information1,
956 X_Per_Information2 => Per_rec.Per_Information2,
957 X_Per_Information3 => Per_rec.Per_Information3,
958 X_Per_Information4 => Per_rec.Per_Information4,
959 X_Per_Information5 => Per_rec.Per_Information5,
960 X_Per_Information6 => Per_rec.Per_Information6,
961 X_Per_Information7 => Per_rec.Per_Information7,
962 X_Per_Information8 => Per_rec.Per_Information8,
963 X_Per_Information9 => Per_rec.Per_Information9,
964 X_Per_Information10 => Per_rec.Per_Information10,
965 X_Per_Information11 => Per_rec.Per_Information11,
966 X_Per_Information12 => Per_rec.Per_Information12,
967 X_Per_Information13 => Per_rec.Per_Information13,
968 X_Per_Information14 => Per_rec.Per_Information14,
969 X_Per_Information15 => Per_rec.Per_Information15,
970 X_Per_Information16 => Per_rec.Per_Information16,
971 X_Per_Information17 => Per_rec.Per_Information17,
972 X_Per_Information18 => Per_rec.Per_Information18,
973 X_Per_Information19 => Per_rec.Per_Information19,
974 X_Per_Information20 => Per_rec.Per_Information20,
975 X_Per_Information21 => Per_rec.Per_Information21,
976 X_Per_Information22 => Per_rec.Per_Information22,
977 X_Per_Information23 => Per_rec.Per_Information23,
978 X_Per_Information24 => Per_rec.Per_Information24,
979 X_Per_Information25 => Per_rec.Per_Information25,
980 X_Per_Information26 => Per_rec.Per_Information26,
981 X_Per_Information27 => Per_rec.Per_Information27,
982 X_Per_Information28 => Per_rec.Per_Information28,
983 X_Per_Information29 => Per_rec.Per_Information29,
984 X_Per_Information30 => Per_rec.Per_Information30,
985 X_Last_Update_Date => Per_rec.Last_Update_Date,
986 X_Last_Updated_By => Per_rec.Last_Updated_By,
987 X_Last_Update_Login => Per_rec.Last_Update_Login,
988 X_Created_By => Per_rec.Created_By,
989 X_Creation_Date => Per_rec.Creation_Date,
990 X_Period_Of_Service_Id => l_period_of_service_id,
991 X_town_of_birth => Per_rec.town_of_birth,
992 X_region_of_birth => Per_rec.region_of_birth,
993 X_country_of_birth => Per_rec.country_of_birth,
994 X_global_person_id => Per_rec.global_person_id,
995 X_Order_Name => Per_rec.order_Name, -- #3889584
996 X_Global_Name => Per_rec.global_name,
997 X_Local_Name => Per_rec.local_name);
998
999 --
1000 -- add maintain_ptu for TERM
1001 --
1002 -- hr_per_type_usage_internal.maintain_ptu(
1003 -- p_validate => TRUE,
1004 -- p_action => 'TERM',
1005 -- p_person_id => Per_rec.person_id,
1006 -- p_period_of_service_id => l_period_of_service_id,
1007 -- p_actual_termination_date => X_termination_date,
1008 -- p_business_group_id => Per_rec.Business_group_id,
1009 -- p_date_start => Per_rec.start_date);
1010 --
1011 --
1012 -- #2221990
1013 -- This procedure has already been called within PER_PEOPLE_V14_PKG
1014 -- thus it is not necessary.
1015 --
1016 -- PTU : Start of Changes
1017 --
1018 -- hr_per_type_usage_internal.maintain_person_type_usage
1019 -- (p_effective_date => X_termination_date
1020 -- ,p_person_id => Per_rec.person_id
1021 -- ,p_person_type_id =>
1022 -- hr_person_type_usage_info.get_default_person_type_id
1023 -- (p_business_group_id => Per_rec.Business_group_id
1024 -- ,p_system_person_type => 'EX_EMP')
1025 -- );
1026 --
1027 -- PTU : End of Changes
1028 IF g_debug THEN
1029 hr_utility.set_location('Leaving: '|| g_package || l_proc, 50);
1030 END IF;
1031
1032 end;
1033 --
1034 procedure update_employee_rows(X_period_of_service_id NUMBER
1035 ,X_person_id NUMBER
1036 ,X_hire_date DATE
1037 ,X_s_hire_date DATE) IS
1038 --
1039 cursor period is
1040 select date_start
1041 from per_periods_of_service
1042 where period_of_service_id = X_period_of_service_id
1043 for update of date_start;
1044 --
1045 cursor assignment is
1046 select effective_start_date
1047 from per_assignments_f
1048 where period_of_service_id = X_period_of_service_id
1049 and effective_start_date = X_s_hire_date
1050 and person_id = X_person_id
1051 for update of effective_start_date;
1052 --
1053 cursor address is
1054 select date_from
1055 from per_addresses pa
1056 where person_id = X_person_id
1057 and date_from = X_s_hire_Date
1058 for update of date_from;
1059 --
1060 l_dummy_date date;
1061 --
1062 -- START WWBUG 1390173 fix
1063 --
1064 l_old ben_pps_ler.g_pps_ler_rec;
1065 l_new ben_pps_ler.g_pps_ler_rec;
1066 --
1067 -- END WWBUG 1390173 fix
1068 --
1069 cursor c1 is
1070 select *
1071 from per_periods_of_service
1072 where period_of_service_id = x_period_of_service_id
1073 and date_start = x_s_hire_date;
1074 --
1075 -- Fix for WWBUG 1408379
1076 --
1077 l_old_add ben_add_ler.g_add_ler_rec;
1078 l_new_add ben_add_ler.g_add_ler_rec;
1079 --
1080 -- End of Fix for WWBUG 1408379
1081 --
1082 l_c1 c1%rowtype;
1083 --
1084 cursor c2 is
1085 select *
1086 from per_addresses
1087 where person_id = X_person_id
1088 and date_from = X_S_hire_date;
1089 --
1090 l_c2 c2%rowtype;
1091 l_row_found boolean := false;
1092 --
1093 l_proc varchar2(20) := 'update_employee_rows';
1094 --
1095 BEGIN
1096 g_debug := hr_utility.debug_enabled; -- get debug status
1097 IF g_debug THEN
1098 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1099 END IF;
1100
1101 open period;
1102 fetch period into l_dummy_date;
1103 close period;
1104 begin
1105 --
1106 -- START WWBUG 1390173 fix
1107 --
1108 IF g_debug THEN
1109 hr_utility.set_location('Entering: '|| g_package || l_proc, 10);
1110 END IF;
1111
1112 open c1;
1113 fetch c1 into l_c1;
1114 if c1%found then
1115 --
1116 l_old.PERSON_ID := l_c1.person_id;
1117 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
1118 l_old.DATE_START := l_c1.date_start;
1119 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
1120 l_old.LEAVING_REASON := l_c1.leaving_reason;
1121 l_old.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
1122 l_old.ATTRIBUTE1 := l_c1.attribute1;
1123 l_old.ATTRIBUTE2 := l_c1.attribute2;
1124 l_old.ATTRIBUTE3 := l_c1.attribute3;
1125 l_old.ATTRIBUTE4 := l_c1.attribute4;
1126 l_old.ATTRIBUTE5 := l_c1.attribute5;
1127 l_new.PERSON_ID := l_c1.person_id;
1128 l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
1129 l_new.DATE_START := x_hire_date;
1130 l_new.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
1131 l_new.LEAVING_REASON := l_c1.leaving_reason;
1132 l_new.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
1133 l_new.ATTRIBUTE1 := l_c1.attribute1;
1134 l_new.ATTRIBUTE2 := l_c1.attribute2;
1135 l_new.ATTRIBUTE3 := l_c1.attribute3;
1136 l_new.ATTRIBUTE4 := l_c1.attribute4;
1137 l_new.ATTRIBUTE5 := l_c1.attribute5;
1138 l_new.final_process_date := l_c1.final_process_date;
1139 --
1140 ben_pps_ler.ler_chk(p_old => l_old
1141 ,p_new => l_new
1142 ,p_event => 'UPDATING'
1143 ,p_effective_date => x_hire_date);
1144 --
1145 end if;
1146 close c1;
1147 --
1148 -- END WWBUG 1390173 fix
1149 --
1150 --
1151 update per_periods_of_service
1152 set date_start = X_hire_date
1153 where period_of_service_id = X_period_of_service_id
1154 and date_start = X_s_hire_date;
1155 -- VT 01/29/97 #417906 added exception
1156 exception
1157 when no_data_found then null;
1158 end;
1159 --
1160 IF g_debug THEN
1161 hr_utility.set_location('Entering: '|| g_package || l_proc, 20);
1162 END IF;
1163
1164 open assignment;
1165 loop
1166 fetch assignment into l_dummy_date;
1167 exit when assignment%notfound;
1168 end loop;
1169 close assignment;
1170 begin
1171 update per_assignments_f
1172 set effective_start_date = X_hire_date
1173 where period_of_service_id = X_period_of_service_id
1174 and effective_start_date = X_s_hire_date;
1175 -- VT 01/29/97 #417906 added exception
1176 exception
1177 when others then null;
1178 --if SQL%ROWCOUNT <1
1179 --then
1180 -- raise no_data_found;
1181 --end if;
1182 end;
1183 --
1184 IF g_debug THEN
1185 hr_utility.set_location('Entering: '|| g_package || l_proc, 30);
1186 END IF;
1187
1188 open address;
1189 loop
1190 fetch address into l_dummy_date;
1191 exit when address%notfound;
1192 end loop;
1193 close address;
1194 --
1195 open c2;
1196 --
1197 fetch c2 into l_c2;
1198 if c2%found then
1199 --
1200 l_row_found := true;
1201 --
1202 end if;
1203 --
1204 close c2;
1205 --
1206 begin
1207 --
1208 update per_addresses
1209 set date_from = X_hire_date
1210 where person_id = X_person_id
1211 and date_from = X_S_hire_date;
1212 -- VT 01/29/97 #417906 added exception
1213 exception
1214 when no_data_found then null;
1215 end;
1216 --
1217 if l_row_found then
1218 --
1219 -- Fix for WWBUG 1408379
1220 --
1221 l_old_add.person_id := l_c2.person_id;
1222 l_old_add.business_group_id := l_c2.business_group_id;
1223 l_old_add.date_from := l_c2.date_from;
1224 l_old_add.date_to := l_c2.date_to;
1225 l_old_add.primary_flag := l_c2.primary_flag;
1226 l_old_add.postal_code := l_c2.postal_code;
1227 l_old_add.region_2 := l_c2.region_2;
1228 l_old_add.address_type := l_c2.address_type;
1229 l_old_add.address_id := l_c2.address_id;
1230 l_new_add.person_id := l_c2.person_id;
1231 l_new_add.business_group_id := l_c2.business_group_id;
1232 l_new_add.date_from := x_hire_date;
1233 l_new_add.date_to := l_c2.date_to;
1234 l_new_add.primary_flag := l_c2.primary_flag;
1235 l_new_add.postal_code := l_c2.postal_code;
1236 l_new_add.region_2 := l_c2.region_2;
1237 l_new_add.address_type := l_c2.address_type;
1238 l_new_add.address_id := l_c2.address_id;
1239 --
1240 ben_add_ler.ler_chk(p_old => l_old_add,
1241 p_new => l_new_add,
1242 p_effective_date => l_new_add.date_from);
1243 --
1244 -- End of Fix for WWBUG 1408379
1245 --
1246 end if;
1247 --
1248 IF g_debug THEN
1249 hr_utility.set_location('leaving: '|| g_package || l_proc, 40);
1250 END IF;
1251
1252
1253 END update_employee_rows;
1254 --
1255 --
1256 -- ENHANCEMENT BUG: 2293945. Starts here.
1257 --
1258 -- PRCEDURE NAME : modify_hire_date
1259 --
1260 -- PURPOSE : To allow user to modify the Hire_Date of the employment.
1261 -- To correct the personal and assignment information
1262 -- according to the new Hire_Date.
1263 --
1264 -- PARAMETERS : X_Person_Id - Unique Id of the person.
1265 -- X_Hire_Date - Modified Hire Date.
1266 -- X_S_Hire_Date - Original Hire Date.
1267 -- X_Person_Type_Id - Unique key of Person Type Usage table
1268 -- for this person.
1269 -- X_Period_Of_Service_Id - Unique key of Period of Service
1270 -- table for this person.
1271 --
1272 Procedure modify_hire_date(X_Person_Id NUMBER
1273 ,X_Hire_Date DATE
1274 ,X_S_Hire_Date DATE
1275 ,X_System_Person_Type VARCHAR2
1276 ,X_Period_Of_Service_Id NUMBER )
1277 IS
1278 --
1279 l_dummy_date DATE;
1280 l_dummy_num NUMBER(20);
1281 l_earlier_date DATE;
1282 l_assignment_type VARCHAR2(1);
1283 l_later_date DATE;
1284 l_Work_Telephone per_phones.phone_number%TYPE;
1285 l_Phone_Id per_phones.phone_id%TYPE;
1286 l_Phone_OVN per_phones.object_version_number%TYPE;
1287 --
1288 l_business_group_id per_all_people_f.business_group_id%TYPE;
1289 --
1290
1291 l_proc varchar2(16) := 'modify_hire_date';
1292 --
1293 cursor ex_emp_rec
1294 is select Effective_End_Date
1295 from per_people_f
1296 where person_id = X_Person_Id
1297 and nvl(Effective_End_Date,X_S_Hire_Date) = (X_S_Hire_Date - 1)
1298 and nvl(Current_Employee_Flag,'N') <> 'Y'
1299 for update of Effective_End_Date;
1300 --
1301 cursor emp_rec
1302 is select person_id
1303 from per_people_f
1304 where person_id = X_Person_Id
1305 and effective_start_date = X_S_Hire_Date
1306 for update of Person_Id;
1307 --
1308 cursor assignment is
1309 select effective_start_date
1310 from per_assignments_f
1311 where period_of_service_id = X_period_of_service_id
1312 and effective_start_date = X_s_hire_date
1313 and person_id = X_person_id
1314 for update of effective_start_date;
1315 --
1316 cursor period is
1317 select date_start, business_group_id
1318 from per_periods_of_service
1319 where Period_of_service_Id = X_Period_of_service_Id
1320 and date_start = X_S_Hire_Date
1321 and actual_termination_date is null
1322 for update of date_start;
1323 --
1324 cursor address is
1325 select address_id,date_from
1326 from per_addresses pa
1327 where person_id = X_person_id
1328 and date_from = X_s_hire_Date
1329 and primary_flag = 'Y'
1330 for update of date_from;
1331 --
1332 cursor is_valid_addr(lc_addr_id NUMBER,lc_date_from date) is
1333 select date_from
1334 from per_addresses pa
1335 where person_id = X_Person_Id
1336 and address_id <> lc_addr_id
1337 and primary_flag = 'Y'
1338 and lc_date_from between date_from and nvl(date_to,hr_api.g_eot);
1339 --
1340 cursor phones is
1341 select phone_id,phone_number,object_version_number
1342 from per_phones
1343 where parent_table = 'PER_ALL_PEOPLE_F'
1344 and parent_id = X_Person_Id
1345 and date_from = X_S_Hire_Date
1346 and phone_type = 'W1';
1347 --
1348 cursor csr_latestATD(cp_date_start date) is
1349 select max(actual_termination_date)
1350 from per_periods_of_service
1351 where date_start < cp_date_start
1352 and person_id = X_person_id;
1353 --
1354 cursor csr_person(cp_date DATE, cp_bg_id NUMBER) is
1355 select * from per_all_people_f
1356 where business_group_id = cp_bg_id
1357 and person_id = X_person_id
1358 and nvl(Effective_End_Date,cp_date) = cp_date;
1359 --
1360 l_atd date; -- 2975471
1361 l_date_start date;
1362 l_person_rec per_all_people_f%ROWTYPE;
1363 --
1364 BEGIN
1365 g_debug := hr_utility.debug_enabled; -- get debug status
1366 IF g_debug THEN
1367 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1368 END IF;
1369
1370 --
1371 -- 2975471: Ensure new hire date is greater than last termination date
1372 open period;
1373 fetch period into l_date_start, l_business_group_id;
1374 close period;
1375
1376 IF g_debug THEN
1377 hr_utility.set_location('Entering: '|| g_package || l_proc, 10);
1378 END IF;
1379
1380 if l_date_start is not null then
1381 open csr_latestATD(l_date_start);
1382 fetch csr_latestATD into l_atd;
1383 close csr_latestATD;
1384 if (l_atd is not null)
1385 and (X_Hire_date <= l_atd)
1386 then
1387 hr_utility.set_message(800,'HR_6841_EMP_REF_DATE_CHG');
1388 hr_utility.raise_error;
1389 end if;
1390 end if;
1391 --
1392 -- this call is replaced by previous stmts
1393 -- check_person_changes(p_person_id => X_Person_Id
1394 -- ,p_hire_date => X_Hire_Date
1395 -- ,p_s_hire_Date => X_S_Hire_Date);
1396 -- end #2975471
1397
1398 IF g_debug THEN
1399 hr_utility.set_location('Entering: '|| g_package || l_proc, 20);
1400 END IF;
1401
1402 open ex_emp_rec;
1403 fetch ex_emp_rec into l_dummy_date;
1404 if ex_emp_rec%found then
1405 --
1406 update per_people_f
1407 set Effective_End_Date = X_Hire_Date - 1
1408 where Effective_End_Date = l_dummy_date
1409 and person_id = X_Person_Id
1410 and nvl(Current_Employee_Flag,'N') <> 'Y';
1411 --
1412 end if;
1413 close ex_emp_rec;
1414 --
1415
1416 IF g_debug THEN
1417 hr_utility.set_location('Entering: '|| g_package || l_proc, 30);
1418 END IF;
1419
1420 open emp_rec;
1421 fetch emp_rec into l_dummy_num;
1422 if emp_rec%found then
1423 --
1424 -- Added updation of start date for bug fix 5525333
1425 update per_people_f
1426 set effective_start_date = X_Hire_Date,
1427 start_date = X_hire_Date
1428 where effective_start_date = X_S_Hire_Date
1429 and person_id = X_person_id;
1430 --
1431 if SQL%ROWCOUNT < 1 then
1432 raise no_data_found;
1433 end if;
1434 --
1435 end if;
1436 close emp_rec;
1437 --
1438
1439 IF g_debug THEN
1440 hr_utility.set_location('Entering: '|| g_package || l_proc, 40);
1441 END IF;
1442
1443 if (X_s_hire_date > X_hire_date) then
1444 l_later_date := X_s_hire_date;
1445 l_earlier_date := X_hire_date;
1446 else
1447 l_later_date := X_hire_date;
1448 l_earlier_date := X_s_hire_date;
1449 end if;
1450 l_assignment_type := 'E';
1451 --
1452 hr_date_chk.check_for_ass_chg(p_person_id => X_Person_id
1453 ,p_earlier_date => l_earlier_date
1454 ,p_later_date => l_later_date
1455 ,p_assignment_type => l_assignment_type
1456 ,p_s_start_date => X_S_Hire_Date
1457 ,p_start_date => X_Hire_Date);
1458 --
1459 open assignment;
1460 loop
1461 fetch assignment into l_dummy_date;
1462 exit when assignment%notfound;
1463 end loop;
1464 close assignment;
1465 --
1466 update per_assignments_f
1467 set effective_start_date = X_hire_date
1468 where period_of_service_id = X_period_of_service_id
1469 and effective_start_date = X_s_hire_date;
1470 --
1471
1472 IF g_debug THEN
1473 hr_utility.set_location('Entering: '|| g_package || l_proc, 50);
1474 END IF;
1475
1476
1477 open address;
1478 fetch address into l_dummy_num,l_dummy_date;
1479 if address%found then
1480 --
1481 open is_valid_addr(l_dummy_num,X_Hire_Date);
1482 fetch is_valid_addr into l_dummy_date;
1483 if (is_valid_addr%notfound) then
1484 --
1485 update per_addresses
1486 set date_from = X_Hire_Date
1487 where person_id = X_Person_Id
1488 and date_from = X_S_Hire_Date
1489 and address_id = l_dummy_num;
1490 --
1491 close is_valid_addr;
1492 --
1493 else
1494 --
1495 close address;
1496 close is_valid_addr;
1497 hr_utility.set_message(801,'HR_6510_PER_PRIMARY_ADDRESS');
1498 hr_utility.raise_error;
1499 --
1500 end if;
1501 end if;
1502 close address;
1503 --
1504
1505 IF g_debug THEN
1506 hr_utility.set_location('Entering: '|| g_package || l_proc, 60);
1507 END IF;
1508
1509 if SQL%ROWCOUNT < 1 then
1510 raise no_data_found;
1511 end if;
1512 --
1513 hr_per_type_usage_internal.change_hire_date_ptu(p_date_start => X_Hire_date
1514 ,p_old_date_start => X_S_Hire_date
1515 ,p_person_id => X_Person_Id
1516 ,p_system_person_type => X_System_Person_Type);
1517 --
1518 -- #2975471
1519 --
1520 if l_atd is not null and ((l_atd + 1) = X_S_Hire_Date) then -- back-to-back hire?
1521 --hr_utility.set_message(801,'HR_51811_EMP_CONTIG_POS');
1522 --hr_utility.raise_error;
1523
1524 IF g_debug THEN
1525 hr_utility.set_location('Entering: '|| g_package || l_proc, 70);
1526 END IF;
1527
1528
1529 open csr_person(l_atd, l_business_group_id);
1530 fetch csr_person into l_person_rec;
1531 close csr_person;
1532 --
1533 -- update per_all_people_f
1534 create_EX_EMP( p_person_rec => l_person_rec
1535 ,p_pos_id => X_Period_of_service_Id
1536 ,p_person_id => X_person_id
1537 ,p_Business_group_id => l_business_group_id
1538 ,p_start_date => l_atd + 1
1539 ,p_end_date => X_Hire_Date - 1);
1540
1541 -- update PTU table
1542 hr_per_type_usage_internal.maintain_person_type_usage
1543 (p_effective_date => l_atd + 1
1544 ,p_person_id => X_Person_id
1545 ,p_datetrack_update_mode => 'UPDATE_CHANGE_INSERT'
1546 ,p_person_type_id => hr_person_type_usage_info.get_default_person_type_id
1547 (l_business_group_id
1548 ,'EX_EMP')
1549 );
1550 --
1551 end if;
1552 --
1553
1554 IF g_debug THEN
1555 hr_utility.set_location('Entering: '|| g_package || l_proc, 80);
1556 END IF;
1557
1558 if l_date_start is not null then
1559 --
1560 update per_periods_of_service
1561 set date_start = X_Hire_Date
1562 where Period_of_service_Id = X_Period_of_service_Id
1563 and date_start = X_S_Hire_Date
1564 and actual_termination_date is null;
1565 --
1566 end if;
1567 --
1568
1569 IF g_debug THEN
1570 hr_utility.set_location('Entering: '|| g_package || l_proc, 90);
1571 END IF;
1572
1573 open phones;
1574 fetch phones into l_Phone_Id,l_Work_Telephone,l_Phone_OVN;
1575 if (phones%found) then
1576 hr_phone_api.update_phone
1577 (p_phone_id => l_Phone_Id,
1578 p_phone_number => l_work_telephone,
1579 p_date_to => null,
1580 p_object_version_number => l_Phone_OVN,
1581 p_date_from => X_Hire_date,
1582 p_effective_date => X_Hire_Date);
1583 end if;
1584 close phones;
1585 --
1586
1587 IF g_debug THEN
1588 hr_utility.set_location('Leaving: '|| g_package || l_proc, 100);
1589 END IF;
1590
1591
1592 End modify_hire_date;
1593 --
1594 -- BUG: 2293945. Ends here.
1595 --
1596 procedure cancel_termination(X_period_of_service_id NUMBER
1597 ,X_Person_id NUMBER
1598 ,X_termination_date DATE
1599 ,X_s_termination_date DATE )
1600 IS
1601 cursor person
1602 --changes for bug 5857638 here
1603 --is select person_id
1604 is select *
1605 --changes for bug 5857638 here
1606 from per_people_f
1607 where person_id = X_Person_Id
1608 for update of Person_Id;
1609 --
1610 cursor assignment
1611 is
1612 select effective_end_date
1613 from per_assignments_f
1614 where person_id = X_person_id
1615 and effective_end_date = X_s_termination_date
1616 for update of effective_end_date;
1617 --
1618 --START HR/WF Synchronization --tpapired
1619 cursor l_pds_cur is
1620 select *
1621 from per_periods_of_service
1622 where person_id = x_person_id
1623 and rownum = 1;
1624 l_pds_rec l_pds_cur%rowtype;
1625
1626 --END HR/WF Synchronization --tpapired
1627 --
1628 cursor period
1629 is
1630 select actual_termination_date
1631 from per_periods_of_service
1632 where period_of_service_id = X_period_of_service_id
1633 for update of actual_termination_date;
1634 --
1635 l_dummy_date VARCHAR2(30);
1636 l_business_group_id number(15);
1637 --changes for bug 5857638 here
1638 l_person_record per_people_f%rowtype;
1639 l_EFFECTIVE_START_DATE per_people_f.EFFECTIVE_START_DATE%type;
1640 l_CURRENT_EMP_OR_APL_FLAG per_people_f.CURRENT_EMP_OR_APL_FLAG%type;
1641 l_CURRENT_EMPLOYEE_FLAG per_people_f.CURRENT_EMPLOYEE_FLAG%type;
1642 l_PERSON_TYPE_ID per_people_f.PERSON_TYPE_ID%type;
1643 --
1644 l_proc varchar2(20) := 'cancel_termination';
1645 --
1646
1647 --changes for bug 5857638 here
1648 begin
1649 g_debug := hr_utility.debug_enabled; -- get debug status
1650 IF g_debug THEN
1651 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1652 END IF;
1653
1654
1655 --
1656 -- Lock assignments and update the end dates.
1657 --
1658 open assignment;
1659 loop
1660 fetch assignment into l_dummy_date;
1661 exit when assignment%NOTFOUND;
1662 update per_assignments_f
1663 set effective_end_date = hr_general.end_of_time
1664 where current of assignment;
1665 if SQL%ROWCOUNT <1 then
1666 raise NO_DATA_FOUND;
1667 end if;
1668 end loop;
1669 close assignment;
1670 --
1671 -- Lock and update the Period of Service row
1672 --
1673 IF g_debug THEN
1674 hr_utility.set_location('Entering: '|| g_package || l_proc, 10);
1675 END IF;
1676 open period;
1677 loop
1678 fetch period into l_dummy_date;
1679 exit when period%NOTFOUND;
1680 update per_periods_of_service
1681 set actual_termination_date = null
1682 , last_standard_process_date = null
1683 , final_process_date = null
1684 where current of period;
1685 end loop;
1686 close period;
1687 --
1688 -- Lock Person rows
1689 --
1690 IF g_debug THEN
1691 hr_utility.set_location('Entering: '|| g_package || l_proc, 20);
1692 END IF;
1693 open person;
1694 loop
1695 ----changes for bug 5857638 here
1696 -- fetch person into l_dummy_date;
1697 fetch person into l_person_record;
1698 if l_person_record.effective_end_date=X_s_termination_date then
1699 hr_utility.set_location('Entering : per_people_v7_pkg.cancel termination',25);
1700 l_EFFECTIVE_START_DATE :=l_person_record.EFFECTIVE_START_DATE;
1701 l_CURRENT_EMP_OR_APL_FLAG :=l_person_record.CURRENT_EMP_OR_APL_FLAG;
1702 l_CURRENT_EMPLOYEE_FLAG :=l_person_record.CURRENT_EMPLOYEE_FLAG;
1703 l_PERSON_TYPE_ID :=l_person_record.PERSON_TYPE_ID;
1704 end if;
1705 --changes for bug 5857638 here
1706 exit when person%NOTFOUND;
1707 end loop;
1708 --
1709 -- Reverse Date-track change by deleting current row
1710 -- and updating Previous row.
1711 --
1712 IF g_debug THEN
1713 hr_utility.set_location('Entering: '|| g_package || l_proc, 30);
1714 END IF;
1715 delete from per_people_f
1716 --changes for bug 5857638 here
1717 -- where effective_start_date = X_s_termination_date + 1
1718 where effective_end_date = X_s_termination_date
1719 --changes for bug 5857638 here
1720 and person_id = X_person_id;
1721 IF g_debug THEN
1722 hr_utility.set_location('Entering: '|| g_package || l_proc, 40);
1723 END IF;
1724 --
1725 update per_people_f
1726 -- set effective_end_date = hr_general.end_of_time
1727 --changes for bug 5857638 here
1728 set EFFECTIVE_START_DATE =l_EFFECTIVE_START_DATE,
1729 CURRENT_EMP_OR_APL_FLAG =l_CURRENT_EMP_OR_APL_FLAG,
1730 CURRENT_EMPLOYEE_FLAG =l_CURRENT_EMPLOYEE_FLAG,
1731 PERSON_TYPE_ID =l_PERSON_TYPE_ID
1732 -- where effective_end_date = X_s_termination_date
1733 where effective_start_date = X_s_termination_date+1
1734 --changes for bug 5857638 here
1735 and person_id = X_person_id;
1736 --
1737 -- These changes are part of version 115.16. They are not necessary
1738 -- since approach changed. (version 115.22)
1739 --
1740 -- Start of Bug No. 1020074 Start.
1741 --
1742 -- While terminating an employee, it creates another row in per_all_people_f
1743 -- and per_phines, and while canceling the termination, it deletes the second
1744 -- row and updates the date_to of first row (reverse Date-track change) in
1745 -- per_all_people_f but not in per_phones table. Modified the code so that while
1746 -- canceling the termination, it removes the second row and updates the
1747 -- date_to (reverse Date-track change) of work_phone in per_phones table too.
1748 --
1749 -- delete from per_phones
1750 -- where parent_id = X_person_id
1751 -- and parent_table = 'PER_ALL_PEOPLE_F'
1752 -- and phone_type = 'W1'
1753 -- and date_from =X_s_termination_date + 1;
1754 --
1755 -- update per_phones
1756 -- set date_to = null
1757 -- where parent_id = X_person_id
1758 -- and parent_table = 'PER_ALL_PEOPLE_F'
1759 -- and phone_type = 'W1'
1760 -- and date_to =X_s_termination_date;
1761 --
1762 -- End of Bug No. 1020074.
1763 --
1764 -- End of changes 1020074 version 115.22
1765 --
1766 --
1767 -- add maintain_ptu for REV_TERM
1768 --
1769 -- hr_per_type_usage_internal.maintain_ptu(
1770 -- p_validate => TRUE,
1771 -- p_action => 'REV_TERM',
1772 -- p_person_id => X_person_id,
1773 -- p_period_of_service_id => X_period_of_service_id,
1774 -- p_actual_termination_date => X_s_termination_date);
1775 --
1776
1777 -- PTU : Start of Changes
1778 -- #2287055: modified effective_date and system_person_type
1779 -- parameters value.
1780 --
1781 IF g_debug THEN
1782 hr_utility.set_location('Entering: '|| g_package || l_proc, 50);
1783 END IF;
1784 hr_per_type_usage_internal.cancel_person_type_usage
1785 (p_effective_date => X_s_termination_date+1
1786 ,p_person_id => X_person_id
1787 ,p_system_person_type => 'EX_EMP'
1788 );
1789
1790 -- PTU : End of Changes
1791 --START HR/WF Synchronization --tpapired
1792 IF g_debug THEN
1793 hr_utility.set_location('Entering: '|| g_package || l_proc, 60);
1794 END IF;
1795
1796 open l_pds_cur;
1797 fetch l_pds_cur into l_pds_rec;
1798 close l_pds_cur;
1799 per_hrwf_synch.per_pds_wf(
1800 p_rec => l_pds_rec,
1801 p_date => null,
1802 p_action => 'REVERSE TERMINATION');
1803 --
1804 --END HR/WF Synchronization --tpapired
1805 --
1806 IF g_debug THEN
1807 hr_utility.set_location('Leaving: '|| g_package || l_proc, 70);
1808 END IF;
1809 end cancel_termination;
1810 --
1811 procedure rehire(X_Person_Id NUMBER
1812 ,X_Rowid VARCHAR2
1813 ,X_Business_group_id NUMBER
1814 ,X_Set_Of_Books_Id NUMBER
1815 ,X_Hire_Date DATE)
1816 IS
1817 cursor person
1818 is select *
1819 from per_people_f
1820 where person_id = X_Person_Id
1821 and X_Hire_date between
1822 effective_start_Date and effective_end_date
1823 for update of effective_end_date;
1824 --
1825 per_rec person%rowtype;
1826 --
1827 cursor c_all_person is
1828 select *
1829 from per_all_people_f
1830 where person_id = x_person_id
1831 and x_hire_date
1832 between effective_start_date
1833 and effective_end_date;
1834 per_all_rec c_all_person%rowtype;
1835 --START HR/WF Synchronization --tpapired
1836 cursor l_pds_cur is
1837 select *
1838 from per_periods_of_service
1839 where person_id = x_person_id
1840 and rownum = 1;
1841 l_pds_rec l_pds_cur%rowtype;
1842
1843 --END HR/WF Synchronization --tpapired
1844 --
1845 cursor person_type is
1846 select ppt.person_type_id
1847 , pst.current_employee_flag
1848 , pst.current_applicant_flag
1849 , pst.current_emp_or_apl_flag
1850 , to_date('31/12/4712','DD/MM/YYYY') effective_end_date
1851 from per_person_types ppt
1852 , per_startup_person_types pst
1853 where ppt.business_group_id =X_Business_group_id --Bug fix 3645987
1854 and ppt.system_person_type = pst.system_person_type
1855 and ppt.system_person_type = 'EMP'
1856 and ppt.default_flag = 'Y'
1857 and pst.default_flag = 'Y';
1858 type_rec person_type%rowtype;
1859 --
1860 cursor ass_type is
1861 select past.assignment_status_type_id
1862 from per_assignment_status_types past
1863 , per_ass_status_type_amends pasa
1864 , per_business_groups pbg
1865 where pasa.assignment_status_type_id(+) = past.assignment_status_type_id
1866 and pasa.business_group_id(+) = X_Business_Group_id
1867 and pbg.business_group_id = X_Business_Group_id
1868 and nvl(past.business_group_id,X_Business_Group_id) = X_Business_Group_id
1869 and nvl(past.legislation_code, pbg.legislation_code) = pbg.legislation_code
1870 and nvl(pasa.active_flag,past.active_flag) = 'Y'
1871 and nvl(pasa.default_flag,past.default_flag) = 'Y'
1872 and nvl(pasa.per_system_status,past.per_system_status) = 'ACTIVE_ASSIGN';
1873 ass_rec ass_type%rowtype;
1874 --
1875 l_rowid ROWID;
1876 l_ass_id NUMBER;
1877 l_ass_seq VARCHAR2(30);
1878 l_primary_flag VARCHAR2(1);
1879 --l_ass_number VARCHAR2(15);
1880 l_ass_number VARCHAR2(30);
1881 l_period_of_service_id NUMBER;
1882 --
1883 l_proc varchar2(10) := 'rehire';
1884 --
1885
1886 --
1887 begin
1888 g_debug := hr_utility.debug_enabled; -- get debug status
1889 IF g_debug THEN
1890 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
1891 END IF;
1892
1893 open person;
1894 fetch person into per_rec;
1895
1896 -- Start of Fix for Bug 2438403
1897 -- update per_people_f
1898 -- set effective_end_date = X_Hire_date - 1
1899 -- where current of person;
1900 -- End of Fix for Bug 2438403
1901
1902 close person;
1903 --
1904 open person_type;
1905 fetch person_type into type_rec;
1906 close person_type;
1907
1908 --
1909 -- Start of Fix for Bug 2438403
1910 --
1911 if (per_periods_of_service_pkg_v2.IsBackToBackContract
1912 (p_person_id => X_Person_Id,
1913 p_hire_date_of_current_pds => X_HIRE_DATE)) then
1914
1915 IF g_debug THEN
1916 hr_utility.set_location('Entering: '|| g_package || l_proc, 10);
1917 END IF;
1918
1919 UPDATE PER_ALL_PEOPLE_F
1920 SET effective_start_date=X_hire_date,
1921 effective_end_date= type_rec.effective_end_date,
1922 business_group_id= per_rec.Business_Group_Id,
1923 person_type_id= hr_person_type_usage_info.get_default_person_type_id(type_rec.Person_Type_Id),
1924 last_name= per_rec.Last_Name,
1925 start_date= per_rec.Start_Date,
1926 applicant_number= per_rec.Applicant_Number,
1927 comment_id= per_rec.Comment_Id,
1928 current_applicant_flag= type_rec.Current_Applicant_Flag,
1929 current_emp_or_apl_flag= type_rec.Current_Emp_Or_Apl_Flag,
1930 current_employee_flag= type_rec.Current_Employee_Flag,
1931 date_employee_data_verified= per_rec.Date_Employee_Data_Verified,
1932 date_of_birth= per_rec.Date_Of_Birth,
1933 email_address= per_rec.Email_Address,
1934 employee_number= per_rec.Employee_Number,
1935 expense_check_send_to_address= per_rec.expense_check_send_to_address,
1936 first_name= per_rec.First_Name,
1937 full_name= per_rec.Full_Name,
1938 known_as= per_rec.Known_As,
1939 marital_status= per_rec.Marital_Status,
1940 middle_names= per_rec.Middle_Names,
1941 nationality= per_rec.Nationality,
1942 national_identifier= per_rec.National_Identifier,
1943 previous_last_name= per_rec.Previous_Last_Name,
1944 registered_disabled_flag= per_rec.Registered_Disabled_Flag,
1945 sex= per_rec.Sex,
1946 title= per_rec.Title,
1947 vendor_id= per_rec.Vendor_Id,
1948 attribute_category= per_rec.Attribute_Category,
1949 attribute1= per_rec.Attribute1,
1950 attribute2= per_rec.Attribute2,
1951 attribute3= per_rec.Attribute3,
1952 attribute4= per_rec.Attribute4,
1953 attribute5= per_rec.Attribute5,
1954 attribute6= per_rec.Attribute6,
1955 attribute7= per_rec.Attribute7,
1956 attribute8= per_rec.Attribute8,
1957 attribute9= per_rec.Attribute9,
1958 attribute10= per_rec.Attribute10,
1959 attribute11= per_rec.Attribute11,
1960 attribute12= per_rec.Attribute12,
1961 attribute13= per_rec.Attribute13,
1962 attribute14= per_rec.Attribute14,
1963 attribute15= per_rec.Attribute15,
1964 attribute16= per_rec.Attribute16,
1965 attribute17= per_rec.Attribute17,
1966 attribute18= per_rec.Attribute18,
1967 attribute19= per_rec.Attribute19,
1968 attribute20= per_rec.Attribute20,
1969 attribute21= per_rec.Attribute21,
1970 attribute22= per_rec.Attribute22,
1971 attribute23= per_rec.Attribute23,
1972 attribute24= per_rec.Attribute24,
1973 attribute25= per_rec.Attribute25,
1974 attribute26= per_rec.Attribute26,
1975 attribute27= per_rec.Attribute27,
1976 attribute28= per_rec.Attribute28,
1977 attribute29= per_rec.Attribute29,
1978 attribute30= per_rec.Attribute30,
1979 per_information_category= per_rec.per_information_category,
1980 per_information1= per_rec.per_information1,
1981 per_information2= per_rec.per_information2,
1982 per_information3= per_rec.per_information3,
1983 per_information4= per_rec.per_information4,
1984 per_information5= per_rec.per_information5,
1985 per_information6= per_rec.per_information6,
1986 per_information7= per_rec.per_information7,
1987 per_information8= per_rec.per_information8,
1988 per_information9= per_rec.per_information9,
1989 per_information10= per_rec.per_information10,
1990 per_information11= per_rec.per_information11,
1991 per_information12= per_rec.per_information12,
1992 per_information13= per_rec.per_information13,
1993 per_information14= per_rec.per_information14,
1994 per_information15= per_rec.per_information15,
1995 per_information16= per_rec.per_information16,
1996 per_information17= per_rec.per_information17,
1997 per_information18= per_rec.per_information18,
1998 per_information19= per_rec.per_information19,
1999 per_information20= per_rec.per_information20,
2000 per_information21= per_rec.per_information21,
2001 per_information22= per_rec.per_information22,
2002 per_information23= per_rec.per_information23,
2003 per_information24= per_rec.per_information24,
2004 per_information25= per_rec.per_information25,
2005 per_information26= per_rec.per_information26,
2006 per_information27= per_rec.per_information27,
2007 per_information28= per_rec.per_information28,
2008 per_information29= per_rec.per_information29,
2009 per_information30= per_rec.per_information30,
2010 last_update_date= per_rec.Last_Update_Date,
2011 last_updated_by= per_rec.Last_Updated_By,
2012 last_update_login= per_rec.Last_Update_Login,
2013 created_by= per_rec.Created_By,
2014 creation_date= per_rec.Creation_Date,
2015 town_of_birth= per_rec.town_of_birth,
2016 region_of_birth= per_rec.region_of_birth,
2017 country_of_birth= per_rec.country_of_birth,
2018 global_person_id= per_rec.global_person_id,
2019 party_id= per_rec.party_id,
2020 blood_type= per_rec.blood_type,
2021 correspondence_language= per_rec.correspondence_language,
2022 honors= per_rec.honors,
2023 pre_name_adjunct= per_rec.pre_name_adjunct,
2024 rehire_authorizor= per_rec.rehire_authorizor,
2025 rehire_recommendation= per_rec.rehire_recommendation,
2026 resume_exists= per_rec.resume_exists,
2027 resume_last_updated= per_rec.resume_last_updated,
2028 second_passport_exists= per_rec.second_passport_exists,
2029 student_status= per_rec.student_status,
2030 suffix= per_rec.suffix,
2031 date_of_death= per_rec.date_of_death,
2032 uses_tobacco_flag= per_rec.uses_tobacco_flag,
2033 fast_path_employee= per_rec.fast_path_employee,
2034 fte_capacity = per_rec.fte_capacity,
2035 order_name = per_rec.order_name, -- #3889584
2036 global_name = per_rec.global_name,
2037 local_name = per_rec.local_name
2038 where person_id = per_rec.person_id
2039 and X_HIRE_DATE between effective_start_date
2040 and effective_end_date;
2041
2042
2043 IF g_debug THEN
2044 hr_utility.set_location('Entering: '|| g_package || l_proc, 20);
2045 END IF;
2046
2047
2048 IF (type_rec.Current_Employee_Flag IS NOT NULL)
2049 THEN
2050 Per_People_V14_Pkg.insert_period_of_service(X_Person_id => X_Person_Id
2051 ,X_Business_Group_Id => X_Business_Group_id
2052 ,X_Date_Start => X_HIRE_DATE
2053 ,X_Period_Of_Service_Id => l_period_of_service_id
2054 );
2055 END IF;
2056
2057 IF g_debug THEN
2058 hr_utility.set_location('Entering: '|| g_package || l_proc, 30);
2059 END IF;
2060
2061
2062 hr_per_type_usage_internal.maintain_person_type_usage
2063 (p_effective_date => X_HIRE_DATE
2064 ,p_person_id => X_Person_Id
2065 ,p_person_type_id => type_rec.Person_Type_Id
2066 ,p_datetrack_update_mode =>'CORRECTION'
2067 );
2068
2069
2070 -- End of Fix for Bug 2438403
2071 else
2072
2073 IF g_debug THEN
2074 hr_utility.set_location('Entering: '|| g_package || l_proc, 40);
2075 END IF;
2076
2077 open person;
2078 fetch person into per_rec;
2079 update per_people_f
2080 set effective_end_date = X_Hire_date - 1
2081 where current of person;
2082 close person;
2083
2084 IF g_debug THEN
2085 hr_utility.set_location('Entering: '|| g_package || l_proc, 50);
2086 END IF;
2087
2088
2089 per_people_v14_pkg.insert_row(l_Rowid
2090 ,per_rec.Person_Id
2091 ,per_rec.Party_Id
2092 ,X_Hire_date
2093 ,type_rec.effective_end_date
2094 ,per_rec.Business_Group_Id
2095 ,type_rec.Person_Type_Id
2096 ,per_rec.Last_Name
2097 ,per_rec.Start_Date
2098 ,per_rec.Applicant_Number
2099 ,per_rec.Comment_Id
2100 ,type_rec.Current_Applicant_Flag
2101 ,type_rec.Current_Emp_Or_Apl_Flag
2102 ,type_rec.Current_Employee_Flag
2103 ,per_rec.Date_Employee_Data_Verified
2104 ,per_rec.Date_Of_Birth
2105 ,per_rec.Email_Address
2106 ,per_rec.Employee_Number
2107 ,per_rec.expense_check_send_to_address
2108 ,per_rec.First_Name
2109 ,per_rec.Full_Name
2110 ,per_rec.Known_As
2111 ,per_rec.Marital_Status
2112 ,per_rec.Middle_Names
2113 ,per_rec.Nationality
2114 ,per_rec.National_Identifier
2115 ,per_rec.Previous_Last_Name
2116 ,per_rec.Registered_Disabled_Flag
2117 ,per_rec.Sex
2118 ,per_rec.Title
2119 ,per_rec.Vendor_Id
2120 --
2121 -- 115.22: We need to ensure no rows get created in per_phones
2122 -- table.
2123 --
2124 ,null -- #1020074 per_rec.Work_Telephone
2125 ,per_rec.Attribute_Category
2126 ,per_rec.Attribute1
2127 ,per_rec.Attribute2
2128 ,per_rec.Attribute3
2129 ,per_rec.Attribute4
2130 ,per_rec.Attribute5
2131 ,per_rec.Attribute6
2132 ,per_rec.Attribute7
2133 ,per_rec.Attribute8
2134 ,per_rec.Attribute9
2135 ,per_rec.Attribute10
2136 ,per_rec.Attribute11
2137 ,per_rec.Attribute12
2138 ,per_rec.Attribute13
2139 ,per_rec.Attribute14
2140 ,per_rec.Attribute15
2141 ,per_rec.Attribute16
2142 ,per_rec.Attribute17
2143 ,per_rec.Attribute18
2144 ,per_rec.Attribute19
2145 ,per_rec.Attribute20
2146 ,per_rec.Attribute21
2147 ,per_rec.Attribute22
2148 ,per_rec.Attribute23
2149 ,per_rec.Attribute24
2150 ,per_rec.Attribute25
2151 ,per_rec.Attribute26
2152 ,per_rec.Attribute27
2153 ,per_rec.Attribute28
2154 ,per_rec.Attribute29
2155 ,per_rec.Attribute30
2156 ,per_rec.Per_Information_Category
2157 ,per_rec.Per_Information1
2158 ,per_rec.Per_Information2
2159 ,per_rec.Per_Information3
2160 ,per_rec.Per_Information4
2161 ,per_rec.Per_Information5
2162 ,per_rec.Per_Information6
2163 ,per_rec.Per_Information7
2164 ,per_rec.Per_Information8
2165 ,per_rec.Per_Information9
2166 ,per_rec.Per_Information10
2167 ,per_rec.Per_Information11
2168 ,per_rec.Per_Information12
2169 ,per_rec.Per_Information13
2170 ,per_rec.Per_Information14
2171 ,per_rec.Per_Information15
2172 ,per_rec.Per_Information16
2173 ,per_rec.Per_Information17
2174 ,per_rec.Per_Information18
2175 ,per_rec.Per_Information19
2176 ,per_rec.Per_Information20
2177 ,per_rec.Per_Information21
2178 ,per_rec.Per_Information22
2179 ,per_rec.Per_Information23
2180 ,per_rec.Per_Information24
2181 ,per_rec.Per_Information25
2182 ,per_rec.Per_Information26
2183 ,per_rec.Per_Information27
2184 ,per_rec.Per_Information28
2185 ,per_rec.Per_Information29
2186 ,per_rec.Per_Information30
2187 ,per_rec.Last_Update_Date
2188 ,per_rec.Last_Updated_By
2189 ,per_rec.Last_Update_Login
2190 ,per_rec.Created_By
2191 ,per_rec.Creation_Date
2192 ,per_rec.order_name -- #3889584
2193 ,per_rec.global_name
2194 ,per_rec.local_name
2195 ,l_period_of_service_id
2196 ,per_rec.town_of_birth
2197 ,per_rec.region_of_birth
2198 ,per_rec.country_of_birth
2199 ,per_rec.global_person_id);
2200 --
2201 end if; --backtoback
2202 --
2203
2204 IF g_debug THEN
2205 hr_utility.set_location('Entering: '|| g_package || l_proc, 60);
2206 END IF;
2207
2208 PER_ASSIGNMENTS_V7_PKG.insert_row(X_Rowid =>l_rowid
2209 ,X_Assignment_Id =>l_ass_id
2210 ,X_Effective_Start_Date =>X_Hire_Date
2211 ,X_Effective_End_Date =>
2212 type_rec.effective_end_date
2213 ,X_Business_Group_Id => X_business_group_id
2214 ,X_Recruiter_Id =>NULL
2215 ,X_Grade_Id =>NULL
2216 ,X_Position_Id =>NULL
2217 ,X_Job_Id =>NULL
2218 ,X_Assignment_Status_Type_Id =>
2219 ass_rec.Assignment_Status_Type_Id
2220 ,X_Payroll_Id =>NULL
2221 ,X_Location_Id =>NULL
2222 ,X_Person_Referred_By_Id =>NULL
2223 ,X_Supervisor_Id =>NULL
2224 ,X_Special_Ceiling_Step_Id =>NULL
2225 ,X_Person_Id => X_person_id
2226 ,X_Employee_Number => per_rec.employee_number
2227 ,X_Recruitment_Activity_Id =>NULL
2228 ,X_Source_Organization_Id =>NULL
2229 ,X_Organization_Id => X_business_group_id
2230 ,X_People_Group_Id =>NULL
2231 ,X_Soft_Coding_Keyflex_Id =>NULL
2232 ,X_Vacancy_Id =>NULL
2233 ,X_Pay_Basis_Id =>NULL
2234 ,X_Assignment_Sequence =>l_ass_seq
2235 ,X_Assignment_Type => 'E'
2236 ,X_Primary_Flag => l_primary_flag
2237 ,X_Application_Id =>NULL
2238 ,X_Assignment_Number => l_ass_number
2239 ,X_Change_Reason =>NULL
2240 ,X_Comment_Id =>NULL
2241 ,X_Date_Probation_End =>NULL
2242 ,X_Default_Code_Comb_Id =>NULL
2243 ,X_Employment_Category =>NULL
2244 ,X_Frequency =>NULL
2245 ,X_Internal_Address_Line =>NULL
2246 ,X_Manager_Flag =>NULL
2247 ,X_Normal_Hours =>NULL
2248 ,X_Perf_Review_Period =>NULL
2249 ,X_Perf_Review_Period_Frequency =>NULL
2250 ,X_Period_Of_Service_Id =>l_period_of_service_id
2251 ,X_Probation_Period =>NULL
2252 ,X_Probation_Unit =>NULL
2253 ,X_Sal_Review_Period =>NULL
2254 ,X_Sal_Review_Period_Frequency =>NULL
2255 ,X_set_of_books_id =>X_Set_Of_Books_Id
2256 ,X_Source_Type =>NULL
2257 ,X_Time_Normal_Finish =>NULL
2258 ,X_Time_Normal_Start =>NULL
2259 ,X_Ass_Attribute_Category =>NULL
2260 ,X_Ass_Attribute1 =>NULL
2261 ,X_Ass_Attribute2 =>NULL
2262 ,X_Ass_Attribute3 =>NULL
2263 ,X_Ass_Attribute4 =>NULL
2264 ,X_Ass_Attribute5 =>NULL
2265 ,X_Ass_Attribute6 =>NULL
2266 ,X_Ass_Attribute7 =>NULL
2267 ,X_Ass_Attribute8 =>NULL
2268 ,X_Ass_Attribute9 =>NULL
2269 ,X_Ass_Attribute10 =>NULL
2270 ,X_Ass_Attribute11 =>NULL
2271 ,X_Ass_Attribute12 =>NULL
2272 ,X_Ass_Attribute13 =>NULL
2273 ,X_Ass_Attribute14 =>NULL
2274 ,X_Ass_Attribute15 =>NULL
2275 ,X_Ass_Attribute16 =>NULL
2276 ,X_Ass_Attribute17 =>NULL
2277 ,X_Ass_Attribute18 =>NULL
2278 ,X_Ass_Attribute19 =>NULL
2279 ,X_Ass_Attribute20 =>NULL
2280 ,X_Ass_Attribute21 =>NULL
2281 ,X_Ass_Attribute22 =>NULL
2282 ,X_Ass_Attribute23 =>NULL
2283 ,X_Ass_Attribute24 =>NULL
2284 ,X_Ass_Attribute25 =>NULL
2285 ,X_Ass_Attribute26 =>NULL
2286 ,X_Ass_Attribute27 =>NULL
2287 ,X_Ass_Attribute28 =>NULL
2288 ,X_Ass_Attribute29 =>NULL
2289 ,X_Ass_Attribute30 =>NULL
2290 ,X_Last_Update_Date =>NULL
2291 ,X_Last_Updated_By =>NULL
2292 ,X_Last_Update_Login =>NULL
2293 ,X_Created_By =>NULL
2294 ,X_Creation_Date =>NULL
2295 ,X_Title =>NULL
2296 );
2297 --START HR/WF Synchronization --tpapired
2298
2299 IF g_debug THEN
2300 hr_utility.set_location('Entering: '|| g_package || l_proc, 70);
2301 END IF;
2302
2303 open l_pds_cur;
2304 fetch l_pds_cur into l_pds_rec;
2305 close l_pds_cur;
2306 per_hrwf_synch.per_pds_wf(
2307 p_rec => l_pds_rec,
2308 p_date => null,
2309 p_action => 'REVERSE TERMINATION');
2310 --
2311 --END HR/WF Synchronization --tpapired
2312 --
2313 IF g_debug THEN
2314 hr_utility.set_location('Leaving: '|| g_package || l_proc, 80);
2315 END IF;
2316
2317
2318 end;
2319 procedure check_future_person_types(p_system_person_type VARCHAR2
2320 ,p_person_id IN INTEGER
2321 ,p_business_group_id IN INTEGER
2322 ,p_effective_start_date IN DATE)
2323 IS
2324
2325 -- ER FPT
2326 cursor fpt_future_exists
2327 IS
2328 select 'Y'
2329 from per_person_type_usages_f ptu
2330 ,per_person_types ppt
2331 ,per_all_people_f per
2332 where per.person_id = p_person_id
2333 and per.person_id = ptu.person_id
2334 and per.business_group_id = p_business_group_id
2335 and ptu.effective_start_date > p_effective_start_date
2336 and ptu.person_type_id = ppt.person_type_id
2337 and ppt.system_person_type in ('EMP','CWK');
2338 -- ER FPT
2339
2340 cursor future_exists
2341 IS
2342 select 'Y'
2343 from per_people_f ppf
2344 ,per_person_types ppt
2345 ,per_startup_person_types pst
2346 where ppf.person_type_id = ppt.person_type_id
2347 and ppf.person_id = p_person_id
2348 and ppf.business_group_id +0 = ppt.business_group_id+0
2349 and ppf.business_group_id +0 = p_business_group_id
2350 and p_effective_start_date < ppf.effective_start_date
2351 and ppt.system_person_type <> pst.system_person_type
2352 and pst.system_person_type = p_system_person_type;
2353 --
2354 l_future_exists VARCHAR2(1);
2355 --
2356 l_proc varchar2(25) := 'check_future_person_types';
2357 --
2358
2359 begin
2360 g_debug := hr_utility.debug_enabled; -- get debug status
2361 IF g_debug THEN
2362 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
2363 END IF;
2364
2365 -- ER FPT
2366 open future_exists;
2367 fetch future_exists into l_future_exists;
2368 if future_exists%FOUND then
2369 if (nvl(fnd_profile.value('HR_ALLOW_FPT_UPDATES'),'N') = 'Y') then
2370 open fpt_future_exists;
2371 fetch fpt_future_exists into l_future_exists;
2372 if fpt_future_exists%found then
2373 fnd_message.set_name('PER','HR_7193_PER_FUT_TYPE_EXISTS');
2374 app_exception.raise_exception;
2375 end if;
2376 close fpt_future_exists;
2377 else
2378 fnd_message.set_name('PER','HR_7193_PER_FUT_TYPE_EXISTS');
2379 app_exception.raise_exception;
2380 end if;
2381 end if;
2382 close future_exists;
2383 -- ER FPT
2384
2385 IF g_debug THEN
2386 hr_utility.set_location('Leaving: '|| g_package || l_proc, 10);
2387 END IF;
2388
2389 end;
2390 --
2391 procedure check_person_changes(p_person_id NUMBER
2392 ,p_hire_date DATE
2393 ,p_s_hire_Date DATE
2394 ,p_current_application_id NUMBER )
2395 IS
2396 cursor get_future_changes
2397 is
2398 select 'Y'
2399 from per_people_f p
2400 where p.effective_start_date > p_hire_date
2401 and p.person_id = p_person_id;
2402 --
2403 cursor check_person
2404 is
2405 select 'Y'
2406 from per_people_f p
2407 where p.effective_start_date > p_hire_date - 1
2408 and p.effective_start_date <> p_s_hire_date
2409 and p.person_id = p_person_id;
2410 --
2411 l_dummy VARCHAR2(1);
2412 cursor check_assignment_changes
2413 is
2414 select assignment_id
2415 from per_assignments_f paf
2416 , per_periods_of_service pos
2417 where paf.person_id = pos.person_id
2418 and paf.period_of_service_id = pos.period_of_service_id
2419 and pos.date_start = p_s_hire_date
2420 and pos.person_id = p_person_id;
2421 --
2422 l_assignment_id NUMBER;
2423 --
2424 l_proc varchar2(20) := 'check_person_changes';
2425 --
2426
2427 Begin
2428
2429 g_debug := hr_utility.debug_enabled; -- get debug status
2430 IF g_debug THEN
2431 hr_utility.set_location('Entering: '|| g_package || l_proc, 5);
2432 END IF;
2433
2434
2435 if p_current_application_id = 810
2436 then
2437 open get_future_changes;
2438 fetch get_future_changes into l_dummy;
2439 if get_future_changes%FOUND then
2440 close get_future_changes;
2441 fnd_message.set_name('PER','HR_7510_PER_FUT_CHANGE');
2442 app_exception.raise_exception;
2443 end if;
2444 close get_future_changes;
2445 else
2446
2447 IF g_debug THEN
2448 hr_utility.set_location('Entering: '|| g_package || l_proc, 10);
2449 END IF;
2450
2451 open check_person;
2452 fetch check_person into l_dummy;
2453 if check_person%FOUND then
2454 close check_person;
2455 fnd_message.set_name('PER','HR_6841_EMP_REF_DATE_CHG');
2456 app_exception.raise_exception;
2457 else
2458 close check_person;
2459 end if;
2460 end if;
2461 if p_hire_date > p_s_hire_date
2462 then
2463
2464 IF g_debug THEN
2465 hr_utility.set_location('Entering: '|| g_package || l_proc, 20);
2466 END IF;
2467
2468 open check_assignment_changes;
2469 loop
2470 fetch check_assignment_changes into l_assignment_id;
2471 exit when check_assignment_changes%NOTFOUND;
2472 per_assignments_V7_pkg.get_future_changes(l_assignment_id,p_hire_date,NULL);
2473 per_assignments_V7_pkg.get_previous_changes(l_assignment_id,p_hire_date,NULL);
2474 end loop;
2475 close check_assignment_changes;
2476 end if;
2477
2478 IF g_debug THEN
2479 hr_utility.set_location('Leaving: '|| g_package || l_proc, 30);
2480 END IF;
2481
2482
2483 end check_person_changes;
2484 END PER_PEOPLE_V7_PKG;