[Home] [Help]
PACKAGE BODY: APPS.HR_EX_EMPLOYEE_INTERNAL
Source
1 Package Body hr_ex_employee_internal as
2 /* $Header: peexebsi.pkb 120.5.12010000.2 2008/09/10 08:51:14 ppentapa ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_ex_employee_internal.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------< terminate_employee (overloaded) >---------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure terminate_employee
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_period_of_service_id in number
16 ,p_object_version_number in out nocopy number
17 ,p_person_type_id in number default hr_api.g_number
18 ,p_assignment_status_type_id in number default hr_api.g_number
19 ,p_termination_accepted_person in number default hr_api.g_number
20 ,p_accepted_termination_date in date default hr_api.g_date
21 ,p_actual_termination_date in date default hr_api.g_date
22 ,p_final_process_date in out nocopy date
23 ,p_last_standard_process_date in out nocopy date
24 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
25 ,p_comments in varchar2 default hr_api.g_varchar2
26 ,p_notified_termination_date in date default hr_api.g_date
27 ,p_projected_termination_date in date default hr_api.g_date
28 ,p_adjusted_svc_date in date default hr_api.g_date
29 ,p_attribute_category in varchar2 default hr_api.g_varchar2
30 ,p_attribute1 in varchar2 default hr_api.g_varchar2
31 ,p_attribute2 in varchar2 default hr_api.g_varchar2
32 ,p_attribute3 in varchar2 default hr_api.g_varchar2
33 ,p_attribute4 in varchar2 default hr_api.g_varchar2
34 ,p_attribute5 in varchar2 default hr_api.g_varchar2
35 ,p_attribute6 in varchar2 default hr_api.g_varchar2
36 ,p_attribute7 in varchar2 default hr_api.g_varchar2
37 ,p_attribute8 in varchar2 default hr_api.g_varchar2
38 ,p_attribute9 in varchar2 default hr_api.g_varchar2
39 ,p_attribute10 in varchar2 default hr_api.g_varchar2
40 ,p_attribute11 in varchar2 default hr_api.g_varchar2
41 ,p_attribute12 in varchar2 default hr_api.g_varchar2
42 ,p_attribute13 in varchar2 default hr_api.g_varchar2
43 ,p_attribute14 in varchar2 default hr_api.g_varchar2
44 ,p_attribute15 in varchar2 default hr_api.g_varchar2
45 ,p_attribute16 in varchar2 default hr_api.g_varchar2
46 ,p_attribute17 in varchar2 default hr_api.g_varchar2
47 ,p_attribute18 in varchar2 default hr_api.g_varchar2
48 ,p_attribute19 in varchar2 default hr_api.g_varchar2
49 ,p_attribute20 in varchar2 default hr_api.g_varchar2
50 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
51 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
52 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
53 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
54 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
55 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
56 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
57 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
58 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
59 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
60 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
61 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
62 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
63 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
64 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
65 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
66 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
67 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
68 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
69 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
70 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
71 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
72 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
73 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
74 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
75 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
76 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
77 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
78 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
79 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
80 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
81 ,p_supervisor_warning out nocopy boolean
82 ,p_event_warning out nocopy boolean
83 ,p_interview_warning out nocopy boolean
84 ,p_review_warning out nocopy boolean
85 ,p_recruiter_warning out nocopy boolean
86 ,p_asg_future_changes_warning out nocopy boolean
87 ,p_entries_changed_warning out nocopy varchar2
88 ,p_pay_proposal_warning out nocopy boolean
89 ,p_dod_warning out nocopy boolean
90 ,p_org_now_no_manager_warning out nocopy boolean
91 ,p_addl_rights_warning out nocopy boolean -- fix 1370960
92 ) is
93 --
94 l_alu_change_warning VARCHAR2(1) := 'N';
95 --
96 begin
97 terminate_employee
98 (p_validate => p_validate
99 ,p_effective_date => p_effective_date
100 ,p_period_of_service_id => p_period_of_service_id
101 ,p_object_version_number => p_object_version_number
102 ,p_person_type_id => p_person_type_id
103 ,p_assignment_status_type_id => p_assignment_status_type_id
104 ,p_termination_accepted_person => p_termination_accepted_person
105 ,p_accepted_termination_date => p_accepted_termination_date
106 ,p_actual_termination_date => p_actual_termination_date
107 ,p_final_process_date => p_final_process_date
108 ,p_last_standard_process_date => p_last_standard_process_date
109 ,p_leaving_reason => p_leaving_reason
110 ,p_comments => p_comments
111 ,p_notified_termination_date => p_notified_termination_date
112 ,p_projected_termination_date => p_projected_termination_date
113 ,p_adjusted_svc_date => p_adjusted_svc_date
114 ,p_attribute_category => p_attribute_category
115 ,p_attribute1 => p_attribute1
116 ,p_attribute2 => p_attribute2
117 ,p_attribute3 => p_attribute3
118 ,p_attribute4 => p_attribute4
119 ,p_attribute5 => p_attribute5
120 ,p_attribute6 => p_attribute6
121 ,p_attribute7 => p_attribute7
122 ,p_attribute8 => p_attribute8
123 ,p_attribute9 => p_attribute9
124 ,p_attribute10 => p_attribute10
125 ,p_attribute11 => p_attribute11
126 ,p_attribute12 => p_attribute12
127 ,p_attribute13 => p_attribute13
128 ,p_attribute14 => p_attribute14
129 ,p_attribute15 => p_attribute15
130 ,p_attribute16 => p_attribute16
131 ,p_attribute17 => p_attribute17
132 ,p_attribute18 => p_attribute18
133 ,p_attribute19 => p_attribute19
134 ,p_attribute20 => p_attribute20
135 ,p_pds_information_category => p_pds_information_category
136 ,p_pds_information1 => p_pds_information1
137 ,p_pds_information2 => p_pds_information2
138 ,p_pds_information3 => p_pds_information3
139 ,p_pds_information4 => p_pds_information4
140 ,p_pds_information5 => p_pds_information5
141 ,p_pds_information6 => p_pds_information6
142 ,p_pds_information7 => p_pds_information7
143 ,p_pds_information8 => p_pds_information8
144 ,p_pds_information9 => p_pds_information9
145 ,p_pds_information10 => p_pds_information10
146 ,p_pds_information11 => p_pds_information11
147 ,p_pds_information12 => p_pds_information12
148 ,p_pds_information13 => p_pds_information13
149 ,p_pds_information14 => p_pds_information14
150 ,p_pds_information15 => p_pds_information15
151 ,p_pds_information16 => p_pds_information16
152 ,p_pds_information17 => p_pds_information17
153 ,p_pds_information18 => p_pds_information18
154 ,p_pds_information19 => p_pds_information19
155 ,p_pds_information20 => p_pds_information20
156 ,p_pds_information21 => p_pds_information21
157 ,p_pds_information22 => p_pds_information22
158 ,p_pds_information23 => p_pds_information23
159 ,p_pds_information24 => p_pds_information24
160 ,p_pds_information25 => p_pds_information25
161 ,p_pds_information26 => p_pds_information26
162 ,p_pds_information27 => p_pds_information27
163 ,p_pds_information28 => p_pds_information28
164 ,p_pds_information29 => p_pds_information29
165 ,p_pds_information30 => p_pds_information30
166 ,p_supervisor_warning => p_supervisor_warning
167 ,p_event_warning => p_event_warning
168 ,p_interview_warning => p_interview_warning
169 ,p_review_warning => p_review_warning
170 ,p_recruiter_warning => p_recruiter_warning
171 ,p_asg_future_changes_warning => p_asg_future_changes_warning
172 ,p_entries_changed_warning => p_entries_changed_warning
173 ,p_pay_proposal_warning => p_pay_proposal_warning
174 ,p_dod_warning => p_dod_warning
175 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
176 ,p_addl_rights_warning => p_addl_rights_warning
177 ,p_alu_change_warning => l_alu_change_warning
178 );
179 end terminate_employee;
180 --
181 --
182 -- ----------------------------------------------------------------------------
183 -- |-------------------------< terminate_employee >---------------------------|
184 -- ----------------------------------------------------------------------------
185 --
186 procedure terminate_employee
187 (p_validate in boolean default false
188 ,p_effective_date in date
189 ,p_period_of_service_id in number
190 ,p_object_version_number in out nocopy number
191 ,p_person_type_id in number default hr_api.g_number
192 ,p_assignment_status_type_id in number default hr_api.g_number
193 ,p_termination_accepted_person in number default hr_api.g_number
194 ,p_accepted_termination_date in date default hr_api.g_date
195 ,p_actual_termination_date in date default hr_api.g_date
196 ,p_final_process_date in out nocopy date
197 ,p_last_standard_process_date in out nocopy date
198 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
199 ,p_comments in varchar2 default hr_api.g_varchar2
200 ,p_notified_termination_date in date default hr_api.g_date
201 ,p_projected_termination_date in date default hr_api.g_date
202 ,p_adjusted_svc_date in date default hr_api.g_date
203 ,p_attribute_category in varchar2 default hr_api.g_varchar2
204 ,p_attribute1 in varchar2 default hr_api.g_varchar2
205 ,p_attribute2 in varchar2 default hr_api.g_varchar2
206 ,p_attribute3 in varchar2 default hr_api.g_varchar2
207 ,p_attribute4 in varchar2 default hr_api.g_varchar2
208 ,p_attribute5 in varchar2 default hr_api.g_varchar2
209 ,p_attribute6 in varchar2 default hr_api.g_varchar2
210 ,p_attribute7 in varchar2 default hr_api.g_varchar2
211 ,p_attribute8 in varchar2 default hr_api.g_varchar2
212 ,p_attribute9 in varchar2 default hr_api.g_varchar2
213 ,p_attribute10 in varchar2 default hr_api.g_varchar2
214 ,p_attribute11 in varchar2 default hr_api.g_varchar2
215 ,p_attribute12 in varchar2 default hr_api.g_varchar2
216 ,p_attribute13 in varchar2 default hr_api.g_varchar2
217 ,p_attribute14 in varchar2 default hr_api.g_varchar2
218 ,p_attribute15 in varchar2 default hr_api.g_varchar2
219 ,p_attribute16 in varchar2 default hr_api.g_varchar2
220 ,p_attribute17 in varchar2 default hr_api.g_varchar2
221 ,p_attribute18 in varchar2 default hr_api.g_varchar2
222 ,p_attribute19 in varchar2 default hr_api.g_varchar2
223 ,p_attribute20 in varchar2 default hr_api.g_varchar2
224 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
225 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
226 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
227 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
228 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
229 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
230 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
231 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
232 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
233 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
234 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
235 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
236 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
237 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
238 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
239 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
240 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
241 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
242 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
243 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
244 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
245 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
246 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
247 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
248 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
249 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
250 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
251 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
252 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
253 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
254 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
255 ,p_supervisor_warning out nocopy boolean
256 ,p_event_warning out nocopy boolean
257 ,p_interview_warning out nocopy boolean
258 ,p_review_warning out nocopy boolean
259 ,p_recruiter_warning out nocopy boolean
260 ,p_asg_future_changes_warning out nocopy boolean
261 ,p_entries_changed_warning out nocopy varchar2
262 ,p_pay_proposal_warning out nocopy boolean
263 ,p_dod_warning out nocopy boolean
264 ,p_org_now_no_manager_warning out nocopy boolean
265 ,p_addl_rights_warning out nocopy boolean -- fix 1370960
266 --
267 -- 120.2 (START)
268 --
269 ,p_alu_change_warning out nocopy varchar2
270 --
271 -- 120.2 (END)
272 --
273 ) is
274
275 --
276 -- 120.2 (START)
277 --
278 l_alu_change_warning varchar2(1) := 'N';
279 --
280 -- 120.2 (END)
281 --
282 l_cur_pds ben_pps_ler.g_pps_ler_rec;
283 l_new_pds ben_pps_ler.g_pps_ler_rec;
284
285 l_last_std_process_date_in date;
286 l_last_std_process_date_out date;
287
288 dummy number := 0; -- fix 1370960
289 l_proc varchar2(100) := g_package||'.terminate_employee';
290
291 --
292 -- 120.2 (START)
293 --
294 l_atd_new number := 1;
295 l_lspd_new number := 1;
296 --
297 -- 120.2 (END)
298 --
299 cursor csr_get_pds_details is
300 select person_id
301 ,business_group_id
302 ,date_start
303 ,actual_termination_date
304 ,leaving_reason
305 ,adjusted_svc_date
306 ,attribute1
307 ,attribute2
308 ,attribute3
309 ,attribute4
310 ,attribute5
311 ,final_process_date
312 ,period_of_service_id
313 from per_periods_of_service
314 where period_of_service_id = p_period_of_service_id;
315 -- fix 1370960
316 cursor csr_roles_to_terminate( p_person_id in number ) is
317 select
318 role_id
319 , object_version_number
320 , end_date
321 from per_roles
322 where person_id = p_person_id
323 and p_actual_termination_date
324 between start_date
325 and nvl(end_date, hr_api.g_eot);
326
327 cursor csr_chk_addl_rights( p_person_id in number) is
328 select role_id
329 from per_roles
330 where person_id = p_person_id
331 and EMP_RIGHTS_FLAG = 'Y'
332 and nvl(end_of_rights_date, hr_api.g_eot) > p_actual_termination_date;
333 -- end fix 1370960
334
335 --
336 --START WWBUG 2130950 HR/WF Synchronization --tpapired
337 l_pds_rec per_periods_of_service%rowtype;
338 cursor l_pds_cur is
339 select *
340 from per_periods_of_service
341 where period_of_service_id = p_period_of_service_id;
342 --END WWBUG 2130950 HR/WF Synchronization --tpapired
343 --
344 --
345 -- 120.2 (START)
346 --
347 CURSOR csr_asg_rec (cp_person_id IN NUMBER,
348 cp_period_of_service_id IN NUMBER,
349 cp_old_fpd IN DATE) IS
350 SELECT assignment_id
351 , assignment_status_type_id
352 , business_group_id
353 , effective_start_date
354 , effective_end_date
355 , payroll_id
356 , object_version_number
357 FROM per_assignments_f asg
358 WHERE asg.person_id = cp_person_id
359 AND asg.period_of_service_id = cp_period_of_service_id
360 AND cp_old_fpd BETWEEN asg.effective_start_date
361 AND asg.effective_end_date;
362 --
363 -- 120.2 (END)
364 --
365
366 -- fix for bug6892097
367 -- added this new cursor to check for future asg changes when atd=fpd.
368 -- this check has been implemented here because it is complex to handle this in
369 -- hr_assignment_internal package . (while terminating the person a new record with atd + 1 will be
370 -- created and then deleted when atd=fpd )
371 cursor csr_get_asg_end_date(p_fpd_date1 date) is
372 select max(asg.effective_end_date)
373 from per_all_assignments_f asg
374 where asg.period_of_service_id = p_period_of_service_id
375 and asg.effective_start_date > p_fpd_date1;
376 l_max_asg_date1 date;
377 -- end of bug6892097
378 --
379 begin
380
381 hr_utility.set_location('Entering '||l_proc,10);
382 /*
383 ** We are processing a termination using the combined internel API. To
384 ** enable correct processing of OAB life events we need to mask the
385 ** PDS life event processing from the PDS row handler and make a
386 ** single call from this internal API.
387 */
388 g_mask_pds_ler := TRUE;
389 /*
390 ** We need to get the details currently on the PDS record so that
391 ** we know if the person has already been partially or fully terminated.
392 ** If partial termination (ATD set but FPD not set) then don't call
393 ** actual_termination_emp API. If full termination (ATD and FPD both set)
394 ** then don't call either termination API and just update the details.
395 */
396 open l_pds_cur;
397 fetch l_pds_cur into l_pds_rec;
398 close l_pds_cur;
399
400 open csr_get_pds_details;
401 fetch csr_get_pds_details
402 into l_cur_pds;
403 close csr_get_pds_details;
404
405 hr_utility.set_location('LSPD '||to_char(p_last_standard_process_date,
406 'dd-mon-yyyy'),20);
407 begin
408 /*
409 ** Set global variable so that the ATD is available to
410 ** localization hooks during validation of flex info. (requested by
411 ** FR locz.
412 */
413 g_actual_termination_date := p_actual_termination_date;
414 /*
415 ** Save the non-termination related PDS information....
416 */
417 hr_periods_of_service_api.update_pds_details
418 (p_effective_date => p_effective_date
419 ,p_period_of_service_id => p_period_of_service_id
420 ,p_termination_accepted_person => p_termination_accepted_person
421 ,p_accepted_termination_date => p_accepted_termination_date
422 --
423 -- 120.2 (START)
424 --
425 ,p_actual_termination_date => p_actual_termination_date
426 ,p_last_standard_process_date => p_last_standard_process_date
427 ,p_final_process_date => p_final_process_date
428 --
429 -- 120.2 (END)
430 --
431 ,p_object_version_number => p_object_version_number
432 ,p_comments => p_comments
433 ,p_leaving_reason => p_leaving_reason
434 ,p_notified_termination_date => p_notified_termination_date
435 ,p_projected_termination_date => p_projected_termination_date
436 ,p_attribute_category => p_attribute_category
437 ,p_attribute1 => p_attribute1
438 ,p_attribute2 => p_attribute2
439 ,p_attribute3 => p_attribute3
440 ,p_attribute4 => p_attribute4
441 ,p_attribute5 => p_attribute5
442 ,p_attribute6 => p_attribute6
443 ,p_attribute7 => p_attribute7
444 ,p_attribute8 => p_attribute8
445 ,p_attribute9 => p_attribute9
446 ,p_attribute10 => p_attribute10
447 ,p_attribute11 => p_attribute11
448 ,p_attribute12 => p_attribute12
449 ,p_attribute13 => p_attribute13
450 ,p_attribute14 => p_attribute14
451 ,p_attribute15 => p_attribute15
452 ,p_attribute16 => p_attribute16
453 ,p_attribute17 => p_attribute17
454 ,p_attribute18 => p_attribute18
455 ,p_attribute19 => p_attribute19
456 ,p_attribute20 => p_attribute20
457 ,p_pds_information_category => p_pds_information_category
458 ,p_pds_information1 => p_pds_information1
459 ,p_pds_information2 => p_pds_information2
460 ,p_pds_information3 => p_pds_information3
461 ,p_pds_information4 => p_pds_information4
462 ,p_pds_information5 => p_pds_information5
463 ,p_pds_information6 => p_pds_information6
464 ,p_pds_information7 => p_pds_information7
465 ,p_pds_information8 => p_pds_information8
466 ,p_pds_information9 => p_pds_information9
467 ,p_pds_information10 => p_pds_information10
468 ,p_pds_information11 => p_pds_information11
469 ,p_pds_information12 => p_pds_information12
470 ,p_pds_information13 => p_pds_information13
471 ,p_pds_information14 => p_pds_information14
472 ,p_pds_information15 => p_pds_information15
473 ,p_pds_information16 => p_pds_information16
474 ,p_pds_information17 => p_pds_information17
475 ,p_pds_information18 => p_pds_information18
476 ,p_pds_information19 => p_pds_information19
477 ,p_pds_information20 => p_pds_information20
478 ,p_pds_information21 => p_pds_information21
479 ,p_pds_information22 => p_pds_information22
480 ,p_pds_information23 => p_pds_information23
481 ,p_pds_information24 => p_pds_information24
482 ,p_pds_information25 => p_pds_information25
483 ,p_pds_information26 => p_pds_information26
484 ,p_pds_information27 => p_pds_information27
485 ,p_pds_information28 => p_pds_information28
486 ,p_pds_information29 => p_pds_information29
487 ,p_pds_information30 => p_pds_information30
488 --
489 -- 120.2 (START)
490 --
491 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
492 ,p_asg_future_changes_warning => p_asg_future_changes_warning
493 ,p_entries_changed_warning => p_entries_changed_warning
494 --
495 -- 120.2 (END)
496 --
497 );
498 --
499 -- 120.2 (START)
500 --
501 -- For all associated assignments invoke PAY POG processing.
502 for lr_asg_rec in csr_asg_rec(l_pds_rec.person_id,
503 p_period_of_service_id,
504 l_pds_rec.final_process_date)
505 loop
506 --
507 -- If the FPD has been made null, invoke PAY POG processing to handle this
508 --
509 if (l_pds_rec.final_process_date is not null and
510 p_final_process_date is null)
511 then
512 -- Invoke pay pog processing
513 pay_pog_all_assignments_pkg.after_delete
514 (p_effective_date => p_effective_date
515 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
516 ,p_validation_start_date => lr_asg_rec.effective_start_date
517 ,p_validation_end_date => lr_asg_rec.effective_end_date
518 ,p_assignment_id => lr_asg_rec.assignment_id
519 ,p_effective_end_date => hr_api.g_eot
520 ,p_effective_start_date => lr_asg_rec.effective_start_date
521 ,p_object_version_number => lr_asg_rec.object_version_number
522 ,p_org_now_no_manager_warning => null
523 ,p_applicant_rank_o => null
524 ,p_application_id_o => null
525 ,p_assignment_category_o => null
526 ,p_assignment_number_o => null
527 ,p_assignment_sequence_o => null
528 ,p_assignment_status_type_id_o => null
529 ,p_assignment_type_o => null
530 ,p_ass_attribute1_o => null
531 ,p_ass_attribute10_o => null
532 ,p_ass_attribute11_o => null
533 ,p_ass_attribute12_o => null
534 ,p_ass_attribute13_o => null
535 ,p_ass_attribute14_o => null
536 ,p_ass_attribute15_o => null
537 ,p_ass_attribute16_o => null
538 ,p_ass_attribute17_o => null
539 ,p_ass_attribute18_o => null
540 ,p_ass_attribute19_o => null
541 ,p_ass_attribute2_o => null
542 ,p_ass_attribute20_o => null
543 ,p_ass_attribute21_o => null
544 ,p_ass_attribute22_o => null
545 ,p_ass_attribute23_o => null
546 ,p_ass_attribute24_o => null
547 ,p_ass_attribute25_o => null
548 ,p_ass_attribute26_o => null
549 ,p_ass_attribute27_o => null
550 ,p_ass_attribute28_o => null
551 ,p_ass_attribute29_o => null
552 ,p_ass_attribute3_o => null
553 ,p_ass_attribute30_o => null
554 ,p_ass_attribute4_o => null
555 ,p_ass_attribute5_o => null
556 ,p_ass_attribute6_o => null
557 ,p_ass_attribute7_o => null
558 ,p_ass_attribute8_o => null
559 ,p_ass_attribute9_o => null
560 ,p_ass_attribute_category_o => null
561 ,p_bargaining_unit_code_o => null
562 ,p_business_group_id_o => lr_asg_rec.business_group_id
563 ,p_cagr_grade_def_id_o => null
564 ,p_cagr_id_flex_num_o => null
565 ,p_change_reason_o => null
566 ,p_collective_agreement_id_o => null
567 ,p_comment_id_o => null
568 ,p_contract_id_o => null
569 ,p_date_probation_end_o => null
570 ,p_default_code_comb_id_o => null
571 ,p_effective_end_date_o => l_pds_rec.final_process_date
572 ,p_effective_start_date_o => lr_asg_rec.effective_start_date
573 ,p_employee_category_o => null
574 ,p_employment_category_o => null
575 ,p_establishment_id_o => null
576 ,p_frequency_o => null
577 ,p_grade_id_o => null
578 ,p_hourly_salaried_code_o => null
579 ,p_internal_address_line_o => null
580 ,p_job_id_o => null
581 ,p_job_post_source_name_o => null
582 ,p_labour_union_member_flag_o => null
583 ,p_location_id_o => null
584 ,p_manager_flag_o => null
585 ,p_normal_hours_o => null
586 ,p_notice_period_o => null
587 ,p_notice_period_uom_o => null
588 ,p_object_version_number_o => null
589 ,p_organization_id_o => null
590 ,p_payroll_id_o => lr_asg_rec.payroll_id
591 ,p_pay_basis_id_o => null
592 ,p_people_group_id_o => null
593 ,p_perf_review_period_o => null
594 ,p_perf_review_period_frequen_o => null
595 ,p_period_of_service_id_o => null
596 ,p_person_id_o => null
597 ,p_person_referred_by_id_o => null
598 ,p_placement_date_start_o => null
599 ,p_position_id_o => null
600 ,p_posting_content_id_o => null
601 ,p_primary_flag_o => null
602 ,p_probation_period_o => null
603 ,p_probation_unit_o => null
604 ,p_program_application_id_o => null
605 ,p_program_id_o => null
606 ,p_program_update_date_o => null
607 ,p_project_title_o => null
608 ,p_recruiter_id_o => null
609 ,p_recruitment_activity_id_o => null
610 ,p_request_id_o => null
611 ,p_sal_review_period_o => null
612 ,p_sal_review_period_frequen_o => null
613 ,p_set_of_books_id_o => null
614 ,p_soft_coding_keyflex_id_o => null
615 ,p_source_organization_id_o => null
616 ,p_source_type_o => null
617 ,p_special_ceiling_step_id_o => null
618 ,p_supervisor_id_o => null
619 ,p_time_normal_finish_o => null
620 ,p_time_normal_start_o => null
621 ,p_title_o => null
622 ,p_vacancy_id_o => null
623 ,p_vendor_assignment_number_o => null
624 ,p_vendor_employee_number_o => null
625 ,p_vendor_id_o => null
626 ,p_work_at_home_o => null
627 ,p_grade_ladder_pgm_id_o => null
628 ,p_supervisor_assignment_id_o => null
629 ,p_vendor_site_id_o => null
630 ,p_po_header_id_o => null
631 ,p_po_line_id_o => null
632 ,p_projected_assignment_end_o => null
633 );
634 end if; -- FPD has been nulled from not null
635 --
636 -- If the FPD has been changed
637 --
638 if (l_pds_rec.final_process_date is not null and
639 p_final_process_date is not null and
640 l_pds_rec.final_process_date <> p_final_process_date)
641 then
642 -- Invoke pay pog processing
643 pay_pog_all_assignments_pkg.after_delete
644 (p_effective_date => p_effective_date
645 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
646 ,p_validation_start_date => lr_asg_rec.effective_start_date
647 ,p_validation_end_date => lr_asg_rec.effective_end_date
648 ,p_assignment_id => lr_asg_rec.assignment_id
649 ,p_effective_end_date => lr_asg_rec.effective_end_date
650 ,p_effective_start_date => lr_asg_rec.effective_start_date
651 ,p_object_version_number => lr_asg_rec.object_version_number
652 ,p_org_now_no_manager_warning => null
653 ,p_applicant_rank_o => null
654 ,p_application_id_o => null
655 ,p_assignment_category_o => null
656 ,p_assignment_number_o => null
657 ,p_assignment_sequence_o => null
658 ,p_assignment_status_type_id_o => null
659 ,p_assignment_type_o => null
660 ,p_ass_attribute1_o => null
661 ,p_ass_attribute10_o => null
662 ,p_ass_attribute11_o => null
663 ,p_ass_attribute12_o => null
664 ,p_ass_attribute13_o => null
665 ,p_ass_attribute14_o => null
666 ,p_ass_attribute15_o => null
667 ,p_ass_attribute16_o => null
668 ,p_ass_attribute17_o => null
669 ,p_ass_attribute18_o => null
670 ,p_ass_attribute19_o => null
671 ,p_ass_attribute2_o => null
672 ,p_ass_attribute20_o => null
673 ,p_ass_attribute21_o => null
674 ,p_ass_attribute22_o => null
675 ,p_ass_attribute23_o => null
676 ,p_ass_attribute24_o => null
677 ,p_ass_attribute25_o => null
678 ,p_ass_attribute26_o => null
679 ,p_ass_attribute27_o => null
680 ,p_ass_attribute28_o => null
681 ,p_ass_attribute29_o => null
682 ,p_ass_attribute3_o => null
683 ,p_ass_attribute30_o => null
684 ,p_ass_attribute4_o => null
685 ,p_ass_attribute5_o => null
686 ,p_ass_attribute6_o => null
687 ,p_ass_attribute7_o => null
688 ,p_ass_attribute8_o => null
689 ,p_ass_attribute9_o => null
690 ,p_ass_attribute_category_o => null
691 ,p_bargaining_unit_code_o => null
692 ,p_business_group_id_o => lr_asg_rec.business_group_id
693 ,p_cagr_grade_def_id_o => null
694 ,p_cagr_id_flex_num_o => null
695 ,p_change_reason_o => null
696 ,p_collective_agreement_id_o => null
697 ,p_comment_id_o => null
698 ,p_contract_id_o => null
699 ,p_date_probation_end_o => null
700 ,p_default_code_comb_id_o => null
701 ,p_effective_end_date_o => l_pds_rec.final_process_date
702 ,p_effective_start_date_o => lr_asg_rec.effective_start_date
703 ,p_employee_category_o => null
704 ,p_employment_category_o => null
705 ,p_establishment_id_o => null
706 ,p_frequency_o => null
707 ,p_grade_id_o => null
708 ,p_hourly_salaried_code_o => null
709 ,p_internal_address_line_o => null
710 ,p_job_id_o => null
711 ,p_job_post_source_name_o => null
712 ,p_labour_union_member_flag_o => null
713 ,p_location_id_o => null
714 ,p_manager_flag_o => null
715 ,p_normal_hours_o => null
716 ,p_notice_period_o => null
717 ,p_notice_period_uom_o => null
718 ,p_object_version_number_o => null
719 ,p_organization_id_o => null
720 ,p_payroll_id_o => lr_asg_rec.payroll_id
721 ,p_pay_basis_id_o => null
722 ,p_people_group_id_o => null
723 ,p_perf_review_period_o => null
724 ,p_perf_review_period_frequen_o => null
725 ,p_period_of_service_id_o => null
726 ,p_person_id_o => null
727 ,p_person_referred_by_id_o => null
728 ,p_placement_date_start_o => null
729 ,p_position_id_o => null
730 ,p_posting_content_id_o => null
731 ,p_primary_flag_o => null
732 ,p_probation_period_o => null
733 ,p_probation_unit_o => null
734 ,p_program_application_id_o => null
735 ,p_program_id_o => null
736 ,p_program_update_date_o => null
737 ,p_project_title_o => null
738 ,p_recruiter_id_o => null
739 ,p_recruitment_activity_id_o => null
740 ,p_request_id_o => null
741 ,p_sal_review_period_o => null
742 ,p_sal_review_period_frequen_o => null
743 ,p_set_of_books_id_o => null
744 ,p_soft_coding_keyflex_id_o => null
745 ,p_source_organization_id_o => null
746 ,p_source_type_o => null
747 ,p_special_ceiling_step_id_o => null
748 ,p_supervisor_id_o => null
749 ,p_time_normal_finish_o => null
750 ,p_time_normal_start_o => null
751 ,p_title_o => null
752 ,p_vacancy_id_o => null
753 ,p_vendor_assignment_number_o => null
754 ,p_vendor_employee_number_o => null
755 ,p_vendor_id_o => null
756 ,p_work_at_home_o => null
757 ,p_grade_ladder_pgm_id_o => null
758 ,p_supervisor_assignment_id_o => null
759 ,p_vendor_site_id_o => null
760 ,p_po_header_id_o => null
761 ,p_po_line_id_o => null
762 ,p_projected_assignment_end_o => null
763 );
764 end if; -- FPD has changed
765 --
766 end loop; -- Loop through assignments
767 --
768 -- 120.2 (END)
769 --
770 /*
771 ** Process actual termination date if it's set for the first time....
772 */
773 if ( l_pds_rec.actual_termination_date is null
774 and p_actual_termination_date is not null)
775 OR
776 ( l_pds_rec.last_standard_process_date is null
777 and p_last_standard_process_date is not null)
778 then
779 l_last_std_process_date_in := p_last_standard_process_date;
780 --
781 -- 120.2 (START)
782 --
783 if l_pds_rec.actual_termination_date is null then
784 l_atd_new := 1;
785 else
786 l_atd_new := 0;
787 end if;
788 if l_pds_rec.last_standard_process_date is null then
789 l_lspd_new := 1;
790 else
791 l_lspd_new := 0;
792 end if;
793 --
794 -- 120.2 (END)
795 --
796 -- added for bug6892097
797 if p_actual_termination_date = p_final_process_date then
798 open csr_get_asg_end_date(p_actual_termination_date);
799 fetch csr_get_asg_end_date into l_max_asg_date1;
800 close csr_get_asg_end_date;
801 end if ;
802
803 -- end of bug6892097
804 --
805 hr_ex_employee_api.actual_termination_emp
806 (p_effective_date => p_effective_date
807 ,p_period_of_service_id => p_period_of_service_id
808 ,p_object_version_number => p_object_version_number
809 ,p_actual_termination_date => p_actual_termination_date
810 ,p_last_standard_process_date => l_last_std_process_date_in
811 ,p_person_type_id => p_person_type_id
812 ,p_assignment_status_type_id => p_assignment_status_type_id
813 ,p_leaving_reason => p_leaving_reason
814 --
815 -- 120.2 (START)
816 --
817 ,p_atd_new => l_atd_new
818 ,p_lspd_new => l_lspd_new
819 --
820 -- 120.2 (END)
821 --
822 ,p_attribute_category => p_attribute_category
823 ,p_attribute1 => p_attribute1
824 ,p_attribute2 => p_attribute2
825 ,p_attribute3 => p_attribute3
826 ,p_attribute4 => p_attribute4
827 ,p_attribute5 => p_attribute5
828 ,p_attribute6 => p_attribute6
829 ,p_attribute7 => p_attribute7
830 ,p_attribute8 => p_attribute8
831 ,p_attribute9 => p_attribute9
832 ,p_attribute10 => p_attribute10
833 ,p_attribute11 => p_attribute11
834 ,p_attribute12 => p_attribute12
835 ,p_attribute13 => p_attribute13
836 ,p_attribute14 => p_attribute14
837 ,p_attribute15 => p_attribute15
838 ,p_attribute16 => p_attribute16
839 ,p_attribute17 => p_attribute17
840 ,p_attribute18 => p_attribute18
841 ,p_attribute19 => p_attribute19
842 ,p_attribute20 => p_attribute20
843 ,p_pds_information_category => p_pds_information_category
844 ,p_pds_information1 => p_pds_information1
845 ,p_pds_information2 => p_pds_information2
846 ,p_pds_information3 => p_pds_information3
847 ,p_pds_information4 => p_pds_information4
848 ,p_pds_information5 => p_pds_information5
849 ,p_pds_information6 => p_pds_information6
850 ,p_pds_information7 => p_pds_information7
851 ,p_pds_information8 => p_pds_information8
852 ,p_pds_information9 => p_pds_information9
853 ,p_pds_information10 => p_pds_information10
854 ,p_pds_information11 => p_pds_information11
855 ,p_pds_information12 => p_pds_information12
856 ,p_pds_information13 => p_pds_information13
857 ,p_pds_information14 => p_pds_information14
858 ,p_pds_information15 => p_pds_information15
859 ,p_pds_information16 => p_pds_information16
860 ,p_pds_information17 => p_pds_information17
861 ,p_pds_information18 => p_pds_information18
862 ,p_pds_information19 => p_pds_information19
863 ,p_pds_information20 => p_pds_information20
864 ,p_pds_information21 => p_pds_information21
865 ,p_pds_information22 => p_pds_information22
866 ,p_pds_information23 => p_pds_information23
867 ,p_pds_information24 => p_pds_information24
868 ,p_pds_information25 => p_pds_information25
869 ,p_pds_information26 => p_pds_information26
870 ,p_pds_information27 => p_pds_information27
871 ,p_pds_information28 => p_pds_information28
872 ,p_pds_information29 => p_pds_information29
873 ,p_pds_information30 => p_pds_information30
874 ,p_last_std_process_date_out => l_last_std_process_date_out
875 ,p_supervisor_warning => p_supervisor_warning
876 ,p_event_warning => p_event_warning
877 ,p_interview_warning => p_interview_warning
878 ,p_review_warning => p_review_warning
879 ,p_recruiter_warning => p_recruiter_warning
880 ,p_asg_future_changes_warning => p_asg_future_changes_warning
881 ,p_entries_changed_warning => p_entries_changed_warning
882 ,p_pay_proposal_warning => p_pay_proposal_warning
883 ,p_dod_warning => p_dod_warning
884 --
885 -- 120.2 (START)
886 --
887 ,p_alu_change_warning => l_alu_change_warning
888 --
889 -- 120.2 (END)
890 --
891 );
892
893 p_last_standard_process_date := l_last_std_process_date_out;
894 --
895 -- 120.2 (START)
896 --
897 p_alu_change_warning := l_alu_change_warning;
898 --
899 -- 120.2 (END)
900 --
901
902 -- fix 1370960
903 -- Terminate the roles
904 hr_utility.set_location('l_cur_pds.person_id' || l_cur_pds.person_id, 960);
905 for roles_rec in csr_roles_to_terminate( l_cur_pds.person_id )
906 loop
907 per_supplementary_role_api.update_supplementary_role(
908 p_effective_date => p_effective_date
909 ,p_role_id => roles_rec.role_id
910 ,p_object_version_number => roles_rec.object_version_number
911 ,p_end_date => p_actual_termination_date
912 ,p_old_end_date => roles_rec.end_date
913 );
914 end loop;
915
916 -- Raise a warning if extra rights are there for the person
917 open csr_chk_addl_rights( l_cur_pds.person_id );
918 fetch csr_chk_addl_rights into dummy;
919 if csr_chk_addl_rights%found then
920 p_addl_rights_warning := TRUE;
921 else
922 p_addl_rights_warning := FALSE;
923 end if;
924 close csr_chk_addl_rights;
925 -- end fix 1370960
926 end if;
927
928 /*
929 ** If it's set process final process date....
930 */
931 if l_pds_rec.final_process_date is null
932 and p_final_process_date is not null
933 then
934 hr_ex_employee_api.final_process_emp
935 (p_period_of_service_id => p_period_of_service_id
936 ,p_object_version_number => p_object_version_number
937 ,p_final_process_date => p_final_process_date
938 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
939 ,p_asg_future_changes_warning => p_asg_future_changes_warning
940 ,p_entries_changed_warning => p_entries_changed_warning
941 );
942 end if;
943 --
944 -- fix for bug6892097
945 hr_utility.set_location('p_asg_future ', 99);
946 if p_asg_future_changes_warning = FALSE then
947 hr_utility.set_location('p_asg_future ', 100);
948 if l_max_asg_date1 is not null then
949 hr_utility.set_location('p_asg_future ', 101);
950 p_asg_future_changes_warning := TRUE;
951
952 end if;
953 end if;
954 -- fix for bug6892097
955
956 /*
957 ** Now perform the PDS life event processing....
958 */
959 l_new_pds.person_id := l_cur_pds.person_id;
960 l_new_pds.business_group_id := l_cur_pds.business_group_id;
961 l_new_pds.date_start := l_cur_pds.date_start;
962 l_new_pds.actual_termination_date := p_actual_termination_date;
963 l_new_pds.leaving_reason := p_leaving_reason;
964 l_new_pds.adjusted_svc_date := p_adjusted_svc_date;
965 l_new_pds.attribute1 := p_attribute1;
966 l_new_pds.attribute2 := p_attribute2;
967 l_new_pds.attribute3 := p_attribute3;
968 l_new_pds.attribute4 := p_attribute4;
969 l_new_pds.attribute5 := p_attribute5;
970 l_new_pds.final_process_date := p_final_process_date;
971 l_new_pds.period_of_service_id := p_period_of_service_id;
972
973 ben_pps_ler.ler_chk(p_old => l_cur_pds
974 ,p_new => l_new_pds
975 ,p_event => 'UPDATING'
976 ,p_effective_date => p_effective_date);
977 --
978 --START WWBUG 2130950 HR/WF Synchronization --tpapired
979
980 /* Bug 5504659
981 open l_pds_cur;
982 fetch l_pds_cur into l_pds_rec;
983 close l_pds_cur;
984 per_hrwf_synch.per_pds_wf(
985 p_rec => l_pds_rec,
986 p_date => p_actual_termination_date,
987 p_action => 'TERMINATION');
988 Note : added p_date_start to test, earlier code does not work*/
989
990 --
991 --END WWBUG 2130950 HR/WF Synchronization --tpapired
992 --
993 hr_utility.set_location('Leaving '||l_proc,100);
994 exception
995 when others then
996 g_mask_pds_ler := FALSE;
997 raise;
998 end;
999 end terminate_employee;
1000 --
1001 -- ----------------------------------------------------------------------------
1002 -- |-----------------------< reverse_terminate_employee >-------------------------|
1003 -- ----------------------------------------------------------------------------
1004
1005 procedure reverse_terminate_employee
1006 (p_person_id in number
1007 ,p_actual_termination_date in date
1008 ,p_clear_details in varchar2
1009 ) is
1010
1011 --
1012 -- Declare cursors and local variables
1013 --
1014 l_proc varchar2(72) := g_package||'reverse_terminate_employee';
1015
1016 begin
1017 hr_utility.set_location('Entering: hrempter.cancel_termination'|| l_proc, 5);
1018
1019 --
1020 -- Process Logic
1021 --
1022 hrempter.cancel_termination(p_person_id
1023 ,p_actual_termination_date
1024 ,p_clear_details);
1025 hr_utility.set_location('After: hrempter.cancel_termination'|| l_proc, 10);
1026
1027 exception
1028 when others then
1029 raise;
1030 end reverse_terminate_employee;
1031 --
1032 -- ----------------------------------------------------------------------------
1033 -- |-------------------------< RETURN_TERM_TYPE >---------------------------|
1034 -- ----------------------------------------------------------------------------
1035 --
1036 Function return_term_type (p_LEAVING_REASON IN VARCHAR2,
1037 P_BUSINESS_GROUP_ID IN NUMBER default null)
1038 return varchar2 IS
1039
1040 -- this cursor will be used to return the Status type code available globally
1041 cursor csr_shared_types is
1042 select SHARED_TYPE_CODE from
1043 per_shared_types
1044 where system_type_cd = p_LEAVING_REASON
1045 and lookup_type='LEAV_REAS';
1046
1047 -- this cursor will be used to return the Status type code for the specific business group.
1048 cursor csr_shared_types_bg is
1049 select SHARED_TYPE_CODE from
1050 per_shared_types
1051 where system_type_cd = p_LEAVING_REASON
1052 and lookup_type='LEAV_REAS'
1053 and business_group_id=p_business_group_id;
1054
1055
1056 TERM_TYPE VARCHAR2(30);
1057
1058 begin
1059
1060 if P_BUSINESS_GROUP_ID is not null then
1061
1062 OPEN csr_shared_types_bg ;
1063 FETCH csr_shared_types_bg INTO TERM_TYPE;
1064 CLOSE csr_shared_types_bg;
1065
1066 else
1067
1068 OPEN csr_shared_types ;
1069 FETCH csr_shared_types INTO TERM_TYPE;
1070 CLOSE csr_shared_types;
1071
1072 end if;
1073
1074
1075 hr_utility.set_location('l_proc'||TERM_TYPE, 2);
1076 hr_utility.set_location('l_proc', 30);
1077
1078 return TERM_TYPE;
1079
1080
1081 end return_term_type;
1082
1083 end hr_ex_employee_internal;