[Home] [Help]
PACKAGE BODY: APPS.PER_PEOPLE_V15_PKG
Source
1 PACKAGE BODY PER_PEOPLE_V15_PKG as
2 /* $Header: peper15t.pkb 120.0 2005/05/31 13:42:21 appldev noship $ */
3 --
4 PROCEDURE Update_Row(X_Rowid VARCHAR2,
5 X_Person_Id NUMBER,
6 X_Effective_Start_Date IN OUT NOCOPY DATE,
7 X_Effective_End_Date IN OUT NOCOPY DATE,
8 X_Business_Group_Id NUMBER,
9 X_Person_Type_Id NUMBER,
10 X_Last_Name VARCHAR2,
11 X_Start_Date DATE,
12 X_Hire_date DATE,
13 X_S_Hire_Date DATE,
14 X_Period_of_service_id NUMBER,
15 X_Termination_Date DATE,
16 X_S_Termination_Date DATE,
17 X_Applicant_Number VARCHAR2,
18 X_Comment_Id NUMBER,
19 X_Current_Applicant_Flag VARCHAR2,
20 X_Current_Emp_Or_Apl_Flag VARCHAR2,
21 X_Current_Employee_Flag VARCHAR2,
22 X_Date_Employee_Data_Verified DATE,
23 X_Date_Of_Birth DATE,
24 X_Email_Address VARCHAR2,
25 X_Employee_Number VARCHAR2,
26 X_Expense_Check_To_Address VARCHAR2,
27 X_First_Name VARCHAR2,
28 X_Full_Name VARCHAR2,
29 X_Known_As VARCHAR2,
30 X_Marital_Status VARCHAR2,
31 X_Middle_Names VARCHAR2,
32 X_Nationality VARCHAR2,
33 X_National_Identifier VARCHAR2,
34 X_Previous_Last_Name VARCHAR2,
35 X_Registered_Disabled_Flag VARCHAR2,
36 X_Sex VARCHAR2,
37 X_Title VARCHAR2,
38 X_Vendor_Id NUMBER,
39 X_Work_Telephone VARCHAR2,
40 X_Attribute_Category VARCHAR2,
41 X_Attribute1 VARCHAR2,
42 X_Attribute2 VARCHAR2,
43 X_Attribute3 VARCHAR2,
44 X_Attribute4 VARCHAR2,
45 X_Attribute5 VARCHAR2,
46 X_Attribute6 VARCHAR2,
47 X_Attribute7 VARCHAR2,
48 X_Attribute8 VARCHAR2,
49 X_Attribute9 VARCHAR2,
50 X_Attribute10 VARCHAR2,
51 X_Attribute11 VARCHAR2,
52 X_Attribute12 VARCHAR2,
53 X_Attribute13 VARCHAR2,
54 X_Attribute14 VARCHAR2,
55 X_Attribute15 VARCHAR2,
56 X_Attribute16 VARCHAR2,
57 X_Attribute17 VARCHAR2,
58 X_Attribute18 VARCHAR2,
59 X_Attribute19 VARCHAR2,
60 X_Attribute20 VARCHAR2,
61 X_Attribute21 VARCHAR2,
62 X_Attribute22 VARCHAR2,
63 X_Attribute23 VARCHAR2,
64 X_Attribute24 VARCHAR2,
65 X_Attribute25 VARCHAR2,
66 X_Attribute26 VARCHAR2,
67 X_Attribute27 VARCHAR2,
68 X_Attribute28 VARCHAR2,
69 X_Attribute29 VARCHAR2,
70 X_Attribute30 VARCHAR2,
71 X_Last_Update_Date DATE,
72 X_Last_Updated_By NUMBER,
73 X_Last_Update_Login NUMBER,
74 X_Current_Application_Id NUMBER DEFAULT NULL
75 ) IS
76 --
77 -- 3889584: We need to derive the person names when updating the person record
78 -- This cursor allows the retrieval of all other name components that
79 -- are needed when calling the derive person names function
80 --
81 cursor csr_get_person_details is
82 select *
83 from per_all_people_f
84 where rowid = X_rowid;
85 --
86 l_person_rec csr_get_person_details%rowtype;
87 l_full_name per_all_people_f.full_name%type;
88 l_order_name per_all_people_f.order_name%type;
89 l_global_name per_all_people_f.global_name%type;
90 l_local_name per_all_people_f.local_name%type;
91 l_dup_name varchar(10);
92 --
93 BEGIN
94 l_dup_name := 'N';
95 if X_Current_Application_Id = 810
96 then
97 per_people_v15_pkg.check_person_changes(X_person_id
98 ,X_hire_date
99 ,X_s_hire_Date
100 ,X_current_application_id );
101 end if;
102 if X_termination_date is not null
103 and X_S_termination_date is null
104 and X_current_employee_flag = 'Y'
105 then
106 per_people_v15_pkg.check_person_changes(X_person_id
107 ,X_termination_date
108 ,X_Hire_date
109 ,X_current_application_id );
110 X_effective_end_date := X_termination_date;
111 elsif x_hire_date <> X_s_hire_date then
112 X_effective_start_date := X_hire_date;
113 end if;
114 --
115 -- >> 3889584: Derive person names before updating record
116 open csr_get_person_details;
117 fetch csr_get_person_details into l_person_rec;
118 close csr_get_person_details;
119 --
120 hr_person_name.derive_person_names
121 (p_format_name => NULL, -- generate all names
122 p_business_group_id => x_business_group_id,
123 p_person_id => l_person_rec.person_id,
124 p_first_name => x_first_name,
125 p_middle_names => x_middle_names,
126 p_last_name => x_last_name,
127 p_known_as => x_known_as,
128 p_title => x_title,
129 p_suffix => l_person_rec.suffix,
130 p_pre_name_adjunct => l_person_rec.pre_name_adjunct,
131 p_date_of_birth => x_date_of_birth,
132 p_previous_last_name => x_previous_last_name,
133 p_email_address => x_email_address ,
134 p_employee_number => X_Employee_Number,
135 p_applicant_number => x_applicant_number,
136 p_npw_number => l_person_rec.npw_number,
137 p_per_information1 => l_person_rec.per_information1 ,
138 p_per_information2 => l_person_rec.per_information2 ,
139 p_per_information3 => l_person_rec.per_information3 ,
140 p_per_information4 => l_person_rec.per_information4 ,
141 p_per_information5 => l_person_rec.per_information5 ,
142 p_per_information6 => l_person_rec.per_information6 ,
143 p_per_information7 => l_person_rec.per_information7 ,
144 p_per_information8 => l_person_rec.per_information8 ,
145 p_per_information9 => l_person_rec.per_information9 ,
146 p_per_information10 => l_person_rec.per_information10 ,
147 p_per_information11 => l_person_rec.per_information11 ,
148 p_per_information12 => l_person_rec.per_information12 ,
149 p_per_information13 => l_person_rec.per_information13 ,
150 p_per_information14 => l_person_rec.per_information14 ,
151 p_per_information15 => l_person_rec.per_information15 ,
152 p_per_information16 => l_person_rec.per_information16 ,
153 p_per_information17 => l_person_rec.per_information17 ,
154 p_per_information18 => l_person_rec.per_information18 ,
155 p_per_information19 => l_person_rec.per_information19 ,
156 p_per_information20 => l_person_rec.per_information20 ,
157 p_per_information21 => l_person_rec.per_information21 ,
158 p_per_information22 => l_person_rec.per_information22 ,
159 p_per_information23 => l_person_rec.per_information23 ,
160 p_per_information24 => l_person_rec.per_information24 ,
161 p_per_information25 => l_person_rec.per_information25 ,
162 p_per_information26 => l_person_rec.per_information26 ,
163 p_per_information27 => l_person_rec.per_information27 ,
164 p_per_information28 => l_person_rec.per_information28 ,
165 p_per_information29 => l_person_rec.per_information29 ,
166 p_per_information30 => l_person_rec.per_information30 ,
167 p_attribute1 => x_attribute1 ,
168 p_attribute2 => x_attribute2 ,
169 p_attribute3 => x_attribute3 ,
170 p_attribute4 => x_attribute4 ,
171 p_attribute5 => x_attribute5 ,
172 p_attribute6 => x_attribute6 ,
173 p_attribute7 => x_attribute7 ,
174 p_attribute8 => x_attribute8 ,
175 p_attribute9 => x_attribute9 ,
176 p_attribute10 => x_attribute10 ,
177 p_attribute11 => x_attribute11 ,
178 p_attribute12 => x_attribute12 ,
179 p_attribute13 => x_attribute13 ,
180 p_attribute14 => x_attribute14 ,
181 p_attribute15 => x_attribute15 ,
182 p_attribute16 => x_attribute16 ,
183 p_attribute17 => x_attribute17 ,
184 p_attribute18 => x_attribute18 ,
185 p_attribute19 => x_attribute19 ,
186 p_attribute20 => x_attribute20 ,
187 p_attribute21 => x_attribute21 ,
188 p_attribute22 => x_attribute22 ,
189 p_attribute23 => x_attribute23,
190 p_attribute24 => x_attribute24,
191 p_attribute25 => x_attribute25,
192 p_attribute26 => x_attribute26,
193 p_attribute27 => x_attribute27,
194 p_attribute28 => x_attribute28,
195 p_attribute29 => x_attribute29,
196 p_attribute30 => x_attribute30,
197 p_full_name => l_full_name,
198 p_order_name => l_order_name,
199 p_global_name => l_global_name,
200 p_local_name => l_local_name,
201 p_duplicate_flag => l_dup_name
202 );
203 -- << 3889584
204 UPDATE PER_PEOPLE_F
205 SET
206 person_id = X_Person_Id,
207 effective_start_date = X_Effective_Start_Date,
208 effective_end_date = X_Effective_End_Date,
209 business_group_id = X_Business_Group_Id,
210 person_type_id = X_Person_Type_Id,
211 last_name = X_Last_Name,
212 start_date = X_Start_Date,
213 applicant_number = X_Applicant_Number,
214 comment_id = X_Comment_Id,
215 current_applicant_flag = X_Current_Applicant_Flag,
216 current_emp_or_apl_flag = X_Current_Emp_Or_Apl_Flag,
217 current_employee_flag = X_Current_Employee_Flag,
218 date_employee_data_verified = X_Date_Employee_Data_Verified,
219 date_of_birth = X_Date_Of_Birth,
220 email_address = X_Email_Address,
221 employee_number = X_Employee_Number,
222 expense_check_send_to_address = X_Expense_Check_To_Address,
223 first_name = X_First_Name,
224 full_name = l_full_name, -- 3889584: X_Full_Name,
225 known_as = X_Known_As,
226 marital_status = X_Marital_Status,
227 middle_names = X_Middle_Names,
228 nationality = X_Nationality,
229 national_identifier = X_National_Identifier,
230 previous_last_name = X_Previous_Last_Name,
231 registered_disabled_flag = X_Registered_Disabled_Flag,
232 sex = X_Sex,
233 title = X_Title,
234 vendor_id = X_Vendor_Id,
235 -- work_telephone = X_Work_Telephone,
236 attribute_category = X_Attribute_Category,
237 attribute1 = X_Attribute1,
238 attribute2 = X_Attribute2,
239 attribute3 = X_Attribute3,
240 attribute4 = X_Attribute4,
241 attribute5 = X_Attribute5,
242 attribute6 = X_Attribute6,
243 attribute7 = X_Attribute7,
244 attribute8 = X_Attribute8,
245 attribute9 = X_Attribute9,
246 attribute10 = X_Attribute10,
247 attribute11 = X_Attribute11,
248 attribute12 = X_Attribute12,
249 attribute13 = X_Attribute13,
250 attribute14 = X_Attribute14,
251 attribute15 = X_Attribute15,
252 attribute16 = X_Attribute16,
253 attribute17 = X_Attribute17,
254 attribute18 = X_Attribute18,
255 attribute19 = X_Attribute19,
256 attribute20 = X_Attribute20,
257 attribute21 = X_Attribute21,
258 attribute22 = X_Attribute22,
259 attribute23 = X_Attribute23,
260 attribute24 = X_Attribute24,
261 attribute25 = X_Attribute25,
262 attribute26 = X_Attribute26,
263 attribute27 = X_Attribute27,
264 attribute28 = X_Attribute28,
265 attribute29 = X_Attribute29,
266 attribute30 = X_Attribute30,
267 order_name = l_order_name, -- 3889584
268 global_name = l_global_name,
269 local_name = l_local_name,
270 last_update_date = X_Last_Update_Date,
271 last_updated_by = X_Last_Updated_By,
272 last_update_login = X_Last_Update_Login
273 WHERE rowid = X_rowid;
274 if (SQL%NOTFOUND) then
275 RAISE NO_DATA_FOUND;
276 end if;
277 if X_termination_date is not null
278 and X_S_termination_date is null
279 and X_current_employee_flag = 'Y'
280 then
281 terminate_employment(X_period_of_service_id
282 ,X_person_id
283 ,X_Rowid
284 ,X_Business_group_id
285 ,X_termination_date);
286 elsif X_hire_date <> X_s_hire_date
287 then
288 update_employee_rows(X_period_of_service_id
289 ,X_person_id
290 ,X_hire_date
291 ,X_s_hire_date);
292 end if;
293 END Update_Row;
294 --
295 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
296 BEGIN
297 DELETE FROM PER_PEOPLE_F
298 WHERE rowid = X_Rowid;
299 if (SQL%NOTFOUND) then
300 RAISE NO_DATA_FOUND;
301 end if;
302 END Delete_Row;
303 --
304 procedure terminate_employment(X_period_of_service_id NUMBER
305 ,X_person_id NUMBER
306 ,X_Rowid VARCHAR2
307 ,X_Business_group_id NUMBER
308 ,X_termination_Date DATE)
309 IS
310
311 cursor period
312 is
313 select actual_termination_date
314 from per_periods_of_service
315 where period_of_service_id = X_period_of_service_id
316 for update of actual_termination_date;
317 --
318 cursor assignment
319 is
320 select effective_end_date
321 from per_assignments_f
322 where period_of_service_id = X_period_of_service_id
323 and effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
324 for update of effective_end_date;
325 --
326 cursor ex_status is
327 select ppt.person_type_id
328 , pst.current_applicant_flag
329 , pst.current_emp_or_apl_flag
330 , pst.current_employee_flag
331 from per_person_types ppt
332 , per_startup_person_types pst
333 where ppt.business_group_id = X_business_group_id -- Perf Fix 3648655
334 and pst.system_person_type = ppt.system_person_type
335 and ppt.active_flag = 'Y'
336 and ppt.default_flag = 'Y'
337 and pst.system_person_type = 'EX_EMP';
338 cursor person
339 is
340 select *
341 from per_people_f
342 where rowid = chartorowid(X_Rowid);
343 --
344 per_rec person%ROWTYPE;
345 l_person_type_id number;
346 l_current_emp_or_apl_flag VARCHAR2(1);
347 l_current_applicant_flag VARCHAR2(1);
348 l_current_employee_flag VARCHAR2(1);
349 l_dummy_date DATE;
350 l_rowid VARCHAR2(18);
351 l_period_of_service_id NUMBER;
352 --
353 begin
354 open period;
355 fetch period into l_dummy_date;
356 close period;
357 begin
358 update per_periods_of_service
359 set actual_termination_date = X_termination_date
360 , final_process_date = X_termination_date
361 , last_standard_process_date = X_termination_date
362 where period_of_service_id = X_period_of_service_id;
363 end;
364 --
365 open assignment;
366 loop
367 fetch assignment into l_dummy_date;
368 exit when assignment%notfound;
369 end loop;
370 close assignment;
371 begin
372 update per_assignments_f
373 set effective_end_date = X_termination_date
374 where period_of_service_id = X_period_of_service_id
375 and effective_end_date = to_date('31-12-4712','DD-MM-YYYY');
376 end;
377 --
378 open ex_status;
379 fetch ex_status into l_person_type_id,l_current_emp_or_apl_flag
380 ,l_current_applicant_flag,l_current_employee_flag;
381
382 close ex_status;
383 open person;
384 fetch person into per_rec;
385 close person;
386 --
387 PER_PEOPLE_V14_PKG.Insert_Row(
388 X_Rowid => l_rowid,
389 X_Person_Id => Per_rec.Person_Id,
390 X_Party_Id => Per_rec.Party_Id,
391 X_Effective_Start_Date => X_termination_date + 1,
392 X_Effective_End_Date => to_date('31-12-4712','DD-MM-YYYY'),
393 X_Business_Group_Id => Per_rec.Business_Group_Id,
394 X_Person_Type_Id => l_person_type_id,
395 X_Last_Name => Per_rec.Last_Name,
396 X_Start_Date => Per_rec.Start_Date,
397 X_Applicant_Number => Per_rec.Applicant_Number,
398 X_Comment_Id => Per_rec.Comment_Id,
399 X_Current_Applicant_Flag=> l_current_applicant_flag,
400 X_Current_Emp_Or_Apl_Flag=> l_current_emp_or_apl_flag,
401 X_Current_Employee_Flag=> l_current_employee_flag,
402 X_Date_Employee_Data_Verified=> Per_rec.Date_Employee_Data_Verified,
403 X_Date_Of_Birth => Per_rec.Date_Of_Birth,
404 X_Email_Address => Per_rec.Email_Address,
405 X_Employee_Number => Per_rec.Employee_Number,
406 X_Expense_Check_To_Address=> Per_rec.expense_check_send_to_address,
407 X_First_Name => Per_rec.First_Name,
408 X_Full_Name => Per_rec.Full_Name,
409 X_Known_As => Per_rec.Known_As,
410 X_Marital_Status => Per_rec.Marital_Status,
411 X_Middle_Names => Per_rec.Middle_Names,
412 X_Nationality => Per_rec.Nationality,
413 X_National_Identifier => Per_rec.National_Identifier,
414 X_Previous_Last_Name => Per_rec.Previous_Last_Name,
415 X_Registered_Disabled_Flag=> Per_rec.Registered_Disabled_Flag,
416 X_Sex => Per_rec.Sex,
417 X_Title => Per_rec.Title,
418 X_Vendor_Id => Per_rec.Vendor_Id,
419 X_Work_Telephone => Per_rec.Work_Telephone,
420 X_Attribute_Category => Per_rec.Attribute_Category,
421 X_Attribute1 => Per_rec.Attribute1,
422 X_Attribute2 => Per_rec.Attribute2,
423 X_Attribute3 => Per_rec.Attribute3,
424 X_Attribute4 => Per_rec.Attribute4,
425 X_Attribute5 => Per_rec.Attribute5,
426 X_Attribute6 => Per_rec.Attribute6,
427 X_Attribute7 => Per_rec.Attribute7,
428 X_Attribute8 => Per_rec.Attribute8,
429 X_Attribute9 => Per_rec.Attribute9,
430 X_Attribute10 => Per_rec.Attribute10,
431 X_Attribute11 => Per_rec.Attribute11,
432 X_Attribute12 => Per_rec.Attribute12,
433 X_Attribute13 => Per_rec.Attribute13,
434 X_Attribute14 => Per_rec.Attribute14,
435 X_Attribute15 => Per_rec.Attribute15,
436 X_Attribute16 => Per_rec.Attribute16,
437 X_Attribute17 => Per_rec.Attribute17,
438 X_Attribute18 => Per_rec.Attribute18,
439 X_Attribute19 => Per_rec.Attribute19,
440 X_Attribute20 => Per_rec.Attribute20,
441 X_Attribute21 => Per_rec.Attribute21,
442 X_Attribute22 => Per_rec.Attribute22,
443 X_Attribute23 => Per_rec.Attribute23,
444 X_Attribute24 => Per_rec.Attribute24,
445 X_Attribute25 => Per_rec.Attribute25,
446 X_Attribute26 => Per_rec.Attribute26,
447 X_Attribute27 => Per_rec.Attribute27,
448 X_Attribute28 => Per_rec.Attribute28,
449 X_Attribute29 => Per_rec.Attribute29,
450 X_Attribute30 => Per_rec.Attribute30,
451 X_Per_Information_Category => Per_rec.Per_information_category,
452 X_Per_Information1 => Per_rec.Per_Information1,
453 X_Per_Information2 => Per_rec.Per_Information2,
454 X_Per_Information3 => Per_rec.Per_Information3,
455 X_Per_Information4 => Per_rec.Per_Information4,
456 X_Per_Information5 => Per_rec.Per_Information5,
457 X_Per_Information6 => Per_rec.Per_Information6,
458 X_Per_Information7 => Per_rec.Per_Information7,
459 X_Per_Information8 => Per_rec.Per_Information8,
460 X_Per_Information9 => Per_rec.Per_Information9,
461 X_Per_Information10 => Per_rec.Per_Information10,
462 X_Per_Information11 => Per_rec.Per_Information11,
463 X_Per_Information12 => Per_rec.Per_Information12,
464 X_Per_Information13 => Per_rec.Per_Information13,
465 X_Per_Information14 => Per_rec.Per_Information14,
466 X_Per_Information15 => Per_rec.Per_Information15,
467 X_Per_Information16 => Per_rec.Per_Information16,
468 X_Per_Information17 => Per_rec.Per_Information17,
469 X_Per_Information18 => Per_rec.Per_Information18,
470 X_Per_Information19 => Per_rec.Per_Information19,
471 X_Per_Information20 => Per_rec.Per_Information20,
472 X_Per_Information21 => Per_rec.Per_Information21,
473 X_Per_Information22 => Per_rec.Per_Information22,
474 X_Per_Information23 => Per_rec.Per_Information23,
475 X_Per_Information24 => Per_rec.Per_Information24,
476 X_Per_Information25 => Per_rec.Per_Information25,
477 X_Per_Information26 => Per_rec.Per_Information26,
478 X_Per_Information27 => Per_rec.Per_Information27,
479 X_Per_Information28 => Per_rec.Per_Information28,
480 X_Per_Information29 => Per_rec.Per_Information29,
481 X_Per_Information30 => Per_rec.Per_Information30,
482 X_Last_Update_Date => Per_rec.Last_Update_Date,
483 X_Last_Updated_By => Per_rec.Last_Updated_By,
484 X_Last_Update_Login => Per_rec.Last_Update_Login,
485 X_Created_By => Per_rec.Created_By,
486 X_Creation_Date => Per_rec.Creation_Date,
487 X_Period_Of_Service_Id => l_period_of_service_id,
488 X_Suffix => Per_rec.suffix,
489 X_Pre_Name_Adjunct => Per_rec.pre_name_adjunct,
490 X_Order_Name => Per_rec.order_name, -- #3889584
491 X_Global_Name => Per_rec.global_name,
492 X_Local_Name => Per_rec.local_name);
493
494 --
495 end;
496 --
497 procedure update_employee_rows(X_period_of_service_id NUMBER
498 ,X_person_id NUMBER
499 ,X_hire_date DATE
500 ,X_s_hire_date DATE) IS
501 --
502 cursor period
503 is
504 select date_start
505 from per_periods_of_service
506 where period_of_service_id = X_period_of_service_id
507 for update of date_start;
508 --
509 cursor assignment is
510 select effective_start_date
511 from per_assignments_f
512 where period_of_service_id = X_period_of_service_id
513 and effective_start_date = X_s_hire_date
514 and person_id = X_person_id
515 for Update of effective_start_date;
516 --
517 cursor address is
518 select date_from
519 from per_addresses pa
520 where person_id = X_person_id
521 and date_from = X_s_hire_Date
522 for update of date_from;
523 --
524 l_dummy_date date;
525 BEGIN
526 open period;
527 fetch period into l_dummy_date;
528 close period;
529 begin
530 update per_periods_of_service
531 set date_start = X_hire_date
532 where period_of_service_id = X_period_of_service_id
533 and date_start = X_s_hire_date;
534 -- VT 01/29/97 #417906 added exception
535 exception
536 when no_data_found then null;
537 end;
538 --
539 open assignment;
540 loop
541 fetch assignment into l_dummy_date;
542 exit when assignment%notfound;
543 end loop;
544 close assignment;
545 begin
546 update per_assignments_f
547 set effective_start_date = X_hire_date
548 where period_of_service_id = X_period_of_service_id
549 and effective_start_date = X_s_hire_date;
550 -- VT 01/29/97 #417906 added exception
551 exception
552 when no_data_found then null;
553 --if SQL%ROWCOUNT <1
554 --then
555 -- raise no_data_found;
556 --end if;
557 end;
558 --
559 open address;
560 loop
561 fetch address into l_dummy_date;
562 exit when address%notfound;
563 end loop;
564 close address;
565 --
566 begin
567 update per_addresses
568 set date_from = X_hire_date
569 where person_id = X_person_id
570 and date_from = X_S_hire_date;
571 -- VT 01/29/97 #417906 added exception
572 exception
573 when no_data_found then null;
574 end;
575 END update_employee_rows;
576 --
577 procedure cancel_termination(X_period_of_service_id NUMBER
578 ,X_Person_id NUMBER
579 ,X_termination_date DATE
580 ,X_s_termination_date DATE )
581 IS
582 cursor person
583 is select person_id
584 from per_people_f
585 where person_id = X_Person_Id
586 for update of Person_Id;
587 --
588 cursor assignment
589 is
590 select effective_end_date
591 from per_assignments_f
592 where person_id = X_person_id
593 and effective_end_date = X_s_termination_date
594 for update of effective_end_date;
595 --
596 cursor period
597 is
598 select actual_termination_date
599 from per_periods_of_service
600 where period_of_service_id = X_period_of_service_id
601 for update of actual_termination_date;
602 --
603 l_dummy_date VARCHAR2(30);
604 begin
605 --
606 -- Lock assignments and update the end dates.
607 --
608 open assignment;
609 loop
610 fetch assignment into l_dummy_date;
611 exit when assignment%NOTFOUND;
612 update per_assignments_f
613 set effective_end_date = to_date('31-12-4712','DD-MM-YYYY')
614 where current of assignment;
615 if SQL%ROWCOUNT <1 then
616 raise NO_DATA_FOUND;
617 end if;
618 end loop;
619 close assignment;
620 --
621 -- Lock and update the Period of Service row
622 --
623 open period;
624 loop
625 fetch period into l_dummy_date;
626 exit when period%NOTFOUND;
627 update per_periods_of_service
628 set actual_termination_date = null
629 , last_standard_process_date = null
630 , final_process_date = null
631 where current of period;
632 end loop;
633 close period;
634 --
635 -- Lock Person rows
636 --
637 open person;
638 loop
639 fetch person into l_dummy_date;
640 exit when person%NOTFOUND;
641 end loop;
642 --
643 -- Reverse Date-track change by deleting current row
644 -- and updating Previous row.
645 --
646 delete from per_people_f
647 where effective_start_date = X_s_termination_date + 1
648 and person_id = X_person_id;
649 --
650 update per_people_f
651 set effective_end_date = to_date ('31-12-4712','DD-MM-YYYY')
652 where effective_end_date = X_s_termination_date
653 and person_id = X_person_id;
654 end cancel_termination;
655 --
656 procedure rehire(X_Person_Id NUMBER
657 ,X_Rowid VARCHAR2
658 ,X_Business_group_id NUMBER
659 ,X_Set_Of_Books_Id NUMBER
660 ,X_Hire_Date DATE)
661 IS
662 cursor person
663 is select *
664 from per_people_f
665 where person_id = X_Person_Id
666 and X_Hire_date between
667 effective_start_Date and effective_end_date
668 for update of effective_end_date;
669 --
670 per_rec person%rowtype;
671 --
672 cursor person_type is
673 select ppt.person_type_id
674 , pst.current_employee_flag
675 , pst.current_applicant_flag
676 , pst.current_emp_or_apl_flag
677 , to_date('31/12/4712','DD/MM/YYYY') effective_end_date
678 from per_person_types ppt
679 , per_startup_person_types pst
680 where ppt.business_group_id =X_Business_group_id -- Perf Fix 3648655
681 and ppt.system_person_type = pst.system_person_type
682 and ppt.system_person_type = 'EMP'
683 and ppt.default_flag = 'Y'
684 and pst.default_flag = 'Y';
685 type_rec person_type%rowtype;
686 --
687 cursor ass_type is
688 select past.assignment_status_type_id
689 from per_assignment_status_types past
690 , per_ass_status_type_amends pasa
691 , per_business_groups pbg
692 where pasa.assignment_status_type_id(+) = past.assignment_status_type_id
693 and pasa.business_group_id(+) = X_Business_Group_id
694 and pbg.business_group_id = X_Business_Group_id
695 and nvl(past.business_group_id,X_Business_Group_id) = X_Business_Group_id
696 and nvl(past.legislation_code, pbg.legislation_code) = pbg.legislation_code
697 and nvl(pasa.active_flag,past.active_flag) = 'Y'
698 and nvl(pasa.default_flag,past.default_flag) = 'Y'
699 and nvl(pasa.per_system_status,past.per_system_status) = 'ACTIVE_ASSIGN';
700 ass_rec ass_type%rowtype;
701 --
702 l_rowid ROWID;
703 l_ass_id NUMBER;
704 l_ass_seq VARCHAR2(30);
705 l_primary_flag VARCHAR2(1);
706 l_ass_number VARCHAR2(15);
707 l_period_of_service_id NUMBER;
708 --
709 begin
710 open person;
711 fetch person into per_rec;
712 update per_people_f
713 set effective_end_date = X_Hire_date - 1
714 where current of person;
715 close person;
716 --
717 open person_type;
718 fetch person_type into type_rec;
719 close person_type;
720 per_people_v14_pkg.insert_row(l_Rowid
721 ,per_rec.Person_Id
722 ,per_rec.Party_Id
723 ,X_Hire_date
724 ,type_rec.effective_end_date
725 ,per_rec.Business_Group_Id
726 ,type_rec.Person_Type_Id
727 ,per_rec.Last_Name
728 ,per_rec.Start_Date
729 ,per_rec.Applicant_Number
730 ,per_rec.Comment_Id
731 ,type_rec.Current_Applicant_Flag
732 ,type_rec.Current_Emp_Or_Apl_Flag
733 ,type_rec.Current_Employee_Flag
734 ,per_rec.Date_Employee_Data_Verified
735 ,per_rec.Date_Of_Birth
736 ,per_rec.Email_Address
737 ,per_rec.Employee_Number
738 ,per_rec.expense_check_send_to_address
739 ,per_rec.First_Name
740 ,per_rec.Full_Name
741 ,per_rec.Known_As
742 ,per_rec.Marital_Status
743 ,per_rec.Middle_Names
744 ,per_rec.Nationality
745 ,per_rec.National_Identifier
746 ,per_rec.Previous_Last_Name
747 ,per_rec.Registered_Disabled_Flag
748 ,per_rec.Sex
749 ,per_rec.Title
750 ,per_rec.Vendor_Id
751 ,per_rec.Work_Telephone
752 ,per_rec.Attribute_Category
753 ,per_rec.Attribute1
754 ,per_rec.Attribute2
755 ,per_rec.Attribute3
756 ,per_rec.Attribute4
757 ,per_rec.Attribute5
758 ,per_rec.Attribute6
759 ,per_rec.Attribute7
760 ,per_rec.Attribute8
761 ,per_rec.Attribute9
762 ,per_rec.Attribute10
763 ,per_rec.Attribute11
764 ,per_rec.Attribute12
765 ,per_rec.Attribute13
766 ,per_rec.Attribute14
767 ,per_rec.Attribute15
768 ,per_rec.Attribute16
769 ,per_rec.Attribute17
770 ,per_rec.Attribute18
771 ,per_rec.Attribute19
772 ,per_rec.Attribute20
773 ,per_rec.Attribute21
774 ,per_rec.Attribute22
775 ,per_rec.Attribute23
776 ,per_rec.Attribute24
777 ,per_rec.Attribute25
778 ,per_rec.Attribute26
779 ,per_rec.Attribute27
780 ,per_rec.Attribute28
781 ,per_rec.Attribute29
782 ,per_rec.Attribute30
783 ,per_rec.Per_Information_Category
784 ,per_rec.Per_Information1
785 ,per_rec.Per_Information2
786 ,per_rec.Per_Information3
787 ,per_rec.Per_Information4
788 ,per_rec.Per_Information5
789 ,per_rec.Per_Information6
790 ,per_rec.Per_Information7
791 ,per_rec.Per_Information8
792 ,per_rec.Per_Information9
793 ,per_rec.Per_Information10
794 ,per_rec.Per_Information11
795 ,per_rec.Per_Information12
796 ,per_rec.Per_Information13
797 ,per_rec.Per_Information14
798 ,per_rec.Per_Information15
799 ,per_rec.Per_Information16
800 ,per_rec.Per_Information17
801 ,per_rec.Per_Information18
802 ,per_rec.Per_Information19
803 ,per_rec.Per_Information20
804 ,per_rec.Per_Information21
805 ,per_rec.Per_Information22
806 ,per_rec.Per_Information23
807 ,per_rec.Per_Information24
808 ,per_rec.Per_Information25
809 ,per_rec.Per_Information26
810 ,per_rec.Per_Information27
811 ,per_rec.Per_Information28
812 ,per_rec.Per_Information29
813 ,per_rec.Per_Information30
814 ,per_rec.Last_Update_Date
815 ,per_rec.Last_Updated_By
816 ,per_rec.Last_Update_Login
817 ,per_rec.Created_By
818 ,per_rec.Creation_Date
819 ,per_rec.order_name -- #3889584
820 ,per_rec.global_name
821 ,per_rec.local_name
822 ,l_period_of_service_id);
823 --
824 PER_ASSIGNMENTS_V7_PKG.insert_row(X_Rowid =>l_rowid
825 ,X_Assignment_Id =>l_ass_id
826 ,X_Effective_Start_Date =>X_Hire_Date
827 ,X_Effective_End_Date =>
828 type_rec.effective_end_date
829 ,X_Business_Group_Id => X_business_group_id
830 ,X_Recruiter_Id =>NULL
831 ,X_Grade_Id =>NULL
832 ,X_Position_Id =>NULL
833 ,X_Job_Id =>NULL
834 ,X_Assignment_Status_Type_Id =>
835 ass_rec.Assignment_Status_Type_Id
836 ,X_Payroll_Id =>NULL
837 ,X_Location_Id =>NULL
838 ,X_Person_Referred_By_Id =>NULL
839 ,X_Supervisor_Id =>NULL
840 ,X_Special_Ceiling_Step_Id =>NULL
841 ,X_Person_Id => X_person_id
842 ,X_Employee_Number => per_rec.employee_number
843 ,X_Recruitment_Activity_Id =>NULL
844 ,X_Source_Organization_Id =>NULL
845 ,X_Organization_Id => X_business_group_id
846 ,X_People_Group_Id =>NULL
847 ,X_Soft_Coding_Keyflex_Id =>NULL
848 ,X_Vacancy_Id =>NULL
849 ,X_Pay_Basis_Id =>NULL
850 ,X_Assignment_Sequence =>l_ass_seq
851 ,X_Assignment_Type => 'E'
852 ,X_Primary_Flag => l_primary_flag
853 ,X_Application_Id =>NULL
854 ,X_Assignment_Number => l_ass_number
855 ,X_Change_Reason =>NULL
856 ,X_Comment_Id =>NULL
857 ,X_Date_Probation_End =>NULL
858 ,X_Default_Code_Comb_Id =>NULL
859 ,X_Employment_Category =>NULL
860 ,X_Frequency =>NULL
861 ,X_Internal_Address_Line =>NULL
862 ,X_Manager_Flag =>NULL
863 ,X_Normal_Hours =>NULL
864 ,X_Perf_Review_Period =>NULL
865 ,X_Perf_Review_Period_Frequency =>NULL
866 ,X_Period_Of_Service_Id =>l_period_of_service_id
867 ,X_Probation_Period =>NULL
868 ,X_Probation_Unit =>NULL
869 ,X_Sal_Review_Period =>NULL
870 ,X_Sal_Review_Period_Frequency =>NULL
871 ,X_set_of_books_id =>X_Set_Of_Books_Id
872 ,X_Source_Type =>NULL
873 ,X_Time_Normal_Finish =>NULL
874 ,X_Time_Normal_Start =>NULL
875 ,X_Ass_Attribute_Category =>NULL
876 ,X_Ass_Attribute1 =>NULL
877 ,X_Ass_Attribute2 =>NULL
878 ,X_Ass_Attribute3 =>NULL
879 ,X_Ass_Attribute4 =>NULL
880 ,X_Ass_Attribute5 =>NULL
881 ,X_Ass_Attribute6 =>NULL
882 ,X_Ass_Attribute7 =>NULL
883 ,X_Ass_Attribute8 =>NULL
884 ,X_Ass_Attribute9 =>NULL
885 ,X_Ass_Attribute10 =>NULL
886 ,X_Ass_Attribute11 =>NULL
887 ,X_Ass_Attribute12 =>NULL
888 ,X_Ass_Attribute13 =>NULL
889 ,X_Ass_Attribute14 =>NULL
890 ,X_Ass_Attribute15 =>NULL
891 ,X_Ass_Attribute16 =>NULL
892 ,X_Ass_Attribute17 =>NULL
893 ,X_Ass_Attribute18 =>NULL
894 ,X_Ass_Attribute19 =>NULL
895 ,X_Ass_Attribute20 =>NULL
896 ,X_Ass_Attribute21 =>NULL
897 ,X_Ass_Attribute22 =>NULL
898 ,X_Ass_Attribute23 =>NULL
899 ,X_Ass_Attribute24 =>NULL
900 ,X_Ass_Attribute25 =>NULL
901 ,X_Ass_Attribute26 =>NULL
902 ,X_Ass_Attribute27 =>NULL
903 ,X_Ass_Attribute28 =>NULL
904 ,X_Ass_Attribute29 =>NULL
905 ,X_Ass_Attribute30 =>NULL
906 ,X_Last_Update_Date =>NULL
907 ,X_Last_Updated_By =>NULL
908 ,X_Last_Update_Login =>NULL
909 ,X_Created_By =>NULL
910 ,X_Creation_Date =>NULL
911 ,X_Title =>NULL
912 );
913 end;
914 procedure check_future_person_types(p_system_person_type VARCHAR2
915 ,p_person_id IN INTEGER
916 ,p_business_group_id IN INTEGER
917 ,p_effective_start_date IN DATE)
918 IS
919 cursor future_exists
920 IS
921 select 'Y'
922 from per_people_f ppf
923 ,per_person_types ppt
924 ,per_startup_person_types pst
925 where ppf.person_type_id = ppt.person_type_id
926 and ppf.person_id = p_person_id
927 and ppf.business_group_id +0 = ppt.business_group_id+0
928 and ppf.business_group_id +0 = p_business_group_id
929 and p_effective_start_date < ppf.effective_start_date
930 and ppt.system_person_type <> pst.system_person_type
931 and pst.system_person_type = p_system_person_type;
932 --
933 l_future_exists VARCHAR2(1);
934 begin
935 open future_exists;
936 fetch future_exists into l_future_exists;
937 if future_exists%FOUND then
938 close future_exists;
939 fnd_message.set_name('PER','HR_7193_PER_FUT_TYPE_EXISTS');
940 app_exception.raise_exception;
941 else
942 close future_exists;
943 end if;
944 end;
945 --
946 procedure check_person_changes(p_person_id NUMBER
947 ,p_hire_date DATE
948 ,p_s_hire_Date DATE
949 ,p_current_application_id NUMBER DEFAULT NULL)
950 IS
951 cursor get_future_changes
952 is
953 select 'Y'
954 from per_people_f p
955 where p.effective_start_date > p_hire_date
956 and p.person_id = p_person_id;
957 --
958 cursor check_person
959 is
960 select 'Y'
961 from per_people_f p
962 where p.effective_start_date > p_hire_date - 1
963 and p.effective_start_date <> p_s_hire_date
964 and p.person_id = p_person_id;
965 --
966 l_dummy VARCHAR2(1);
967 cursor check_assignment_changes
968 is
969 select assignment_id
970 from per_assignments_f paf
971 , per_periods_of_service pos
972 where paf.person_id = pos.person_id
973 and paf.period_of_service_id = pos.period_of_service_id
974 and pos.date_start = p_s_hire_date
975 and pos.person_id = p_person_id;
976 --
977 l_assignment_id NUMBER;
978 Begin
979 if p_current_application_id = 810
980 then
981 open get_future_changes;
982 fetch get_future_changes into l_dummy;
983 if get_future_changes%FOUND then
984 close get_future_changes;
985 fnd_message.set_name('PER','HR_7510_PER_FUT_CHANGE');
986 app_exception.raise_exception;
987 end if;
988 close get_future_changes;
989 else
990 open check_person;
991 fetch check_person into l_dummy;
992 if check_person%FOUND then
993 close check_person;
994 fnd_message.set_name('PER','HR_6841_EMP_REF_DATE_CHG');
995 app_exception.raise_exception;
996 else
997 close check_person;
998 end if;
999 end if;
1000 if p_hire_date > p_s_hire_date
1001 then
1002 open check_assignment_changes;
1003 loop
1004 fetch check_assignment_changes into l_assignment_id;
1005 exit when check_assignment_changes%NOTFOUND;
1006 per_assignments_V7_pkg.get_future_changes(l_assignment_id,p_hire_date,NULL);
1007 per_assignments_V7_pkg.get_previous_changes(l_assignment_id,p_hire_date,NULL);
1008 end loop;
1009 close check_assignment_changes;
1010 end if;
1011 end check_person_changes;
1012 END PER_PEOPLE_V15_PKG;