[Home] [Help]
PACKAGE BODY: APPS.HR_EX_EMPLOYEE_INTERNAL
Source
1 Package Body hr_ex_employee_internal as
2 /* $Header: peexebsi.pkb 120.7 2011/11/15 13:40:59 jkvallab 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 --bug 12696936, had to change the defintion of this cursor,see bug for more info
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 asg.assignment_id as assignment_id
351 , asg.assignment_status_type_id as assignment_status_type_id
352 , asg.business_group_id as business_group_id
353 , max(effective_start_date) as effective_start_date
354 , max(effective_end_date) as effective_end_date
355 , asg.payroll_id as payroll_id
356 , asg.object_version_number as object_version_number
357 FROM per_assignments_f asg,
358 per_assignment_status_types ast
359 WHERE asg.person_id = cp_person_id
360 AND asg.period_of_service_id = cp_period_of_service_id
361 AND ast.per_system_status = 'TERM_ASSIGN'
362 AND (asg.business_group_id = ast.business_group_id OR
363 (asg.business_group_id is null OR ast.business_group_id is null))
364 AND ast.assignment_status_type_id = asg.assignment_status_type_id
365 group by asg.assignment_id
366 , asg.assignment_status_type_id
367 , asg.business_group_id
368 , asg.payroll_id
369 , asg.object_version_number;
370 --
371 -- 120.2 (END)
372 --
373
374 -- fix for bug6892097
375 -- added this new cursor to check for future asg changes when atd=fpd.
376 -- this check has been implemented here because it is complex to handle this in
377 -- hr_assignment_internal package . (while terminating the person a new record with atd + 1 will be
378 -- created and then deleted when atd=fpd )
379 cursor csr_get_asg_end_date(p_fpd_date1 date) is
380 select max(asg.effective_end_date)
381 from per_all_assignments_f asg
382 where asg.period_of_service_id = p_period_of_service_id
383 and asg.effective_start_date > p_fpd_date1;
384 l_max_asg_date1 date;
385 -- end of bug6892097
386 --
387 begin
388
389 hr_utility.set_location('Entering '||l_proc,10);
390 /*
391 ** We are processing a termination using the combined internel API. To
392 ** enable correct processing of OAB life events we need to mask the
393 ** PDS life event processing from the PDS row handler and make a
394 ** single call from this internal API.
395 */
396 g_mask_pds_ler := TRUE;
397 /*
398 ** We need to get the details currently on the PDS record so that
399 ** we know if the person has already been partially or fully terminated.
400 ** If partial termination (ATD set but FPD not set) then don't call
401 ** actual_termination_emp API. If full termination (ATD and FPD both set)
402 ** then don't call either termination API and just update the details.
403 */
404 open l_pds_cur;
405 fetch l_pds_cur into l_pds_rec;
406 close l_pds_cur;
407
408 open csr_get_pds_details;
409 fetch csr_get_pds_details
410 into l_cur_pds;
411 close csr_get_pds_details;
412
413 hr_utility.set_location('LSPD '||to_char(p_last_standard_process_date,
414 'dd-mon-yyyy'),20);
415 begin
416 /*
417 ** Set global variable so that the ATD is available to
418 ** localization hooks during validation of flex info. (requested by
419 ** FR locz.
420 */
421 g_actual_termination_date := p_actual_termination_date;
422 /*
423 ** Save the non-termination related PDS information....
424 */
425 hr_periods_of_service_api.update_pds_details
426 (p_effective_date => p_effective_date
427 ,p_period_of_service_id => p_period_of_service_id
428 ,p_termination_accepted_person => p_termination_accepted_person
429 ,p_accepted_termination_date => p_accepted_termination_date
430 --
431 -- 120.2 (START)
432 --
433 ,p_actual_termination_date => p_actual_termination_date
434 ,p_last_standard_process_date => p_last_standard_process_date
435 ,p_final_process_date => p_final_process_date
436 --
437 -- 120.2 (END)
438 --
439 ,p_object_version_number => p_object_version_number
440 ,p_comments => p_comments
441 ,p_leaving_reason => p_leaving_reason
442 ,p_notified_termination_date => p_notified_termination_date
443 ,p_projected_termination_date => p_projected_termination_date
444 ,p_attribute_category => p_attribute_category
445 ,p_attribute1 => p_attribute1
446 ,p_attribute2 => p_attribute2
447 ,p_attribute3 => p_attribute3
448 ,p_attribute4 => p_attribute4
449 ,p_attribute5 => p_attribute5
450 ,p_attribute6 => p_attribute6
451 ,p_attribute7 => p_attribute7
452 ,p_attribute8 => p_attribute8
453 ,p_attribute9 => p_attribute9
454 ,p_attribute10 => p_attribute10
455 ,p_attribute11 => p_attribute11
456 ,p_attribute12 => p_attribute12
457 ,p_attribute13 => p_attribute13
458 ,p_attribute14 => p_attribute14
459 ,p_attribute15 => p_attribute15
460 ,p_attribute16 => p_attribute16
461 ,p_attribute17 => p_attribute17
462 ,p_attribute18 => p_attribute18
463 ,p_attribute19 => p_attribute19
464 ,p_attribute20 => p_attribute20
465 ,p_pds_information_category => p_pds_information_category
466 ,p_pds_information1 => p_pds_information1
467 ,p_pds_information2 => p_pds_information2
468 ,p_pds_information3 => p_pds_information3
469 ,p_pds_information4 => p_pds_information4
470 ,p_pds_information5 => p_pds_information5
471 ,p_pds_information6 => p_pds_information6
472 ,p_pds_information7 => p_pds_information7
473 ,p_pds_information8 => p_pds_information8
474 ,p_pds_information9 => p_pds_information9
475 ,p_pds_information10 => p_pds_information10
476 ,p_pds_information11 => p_pds_information11
477 ,p_pds_information12 => p_pds_information12
478 ,p_pds_information13 => p_pds_information13
479 ,p_pds_information14 => p_pds_information14
480 ,p_pds_information15 => p_pds_information15
481 ,p_pds_information16 => p_pds_information16
482 ,p_pds_information17 => p_pds_information17
483 ,p_pds_information18 => p_pds_information18
484 ,p_pds_information19 => p_pds_information19
485 ,p_pds_information20 => p_pds_information20
486 ,p_pds_information21 => p_pds_information21
487 ,p_pds_information22 => p_pds_information22
488 ,p_pds_information23 => p_pds_information23
489 ,p_pds_information24 => p_pds_information24
490 ,p_pds_information25 => p_pds_information25
491 ,p_pds_information26 => p_pds_information26
492 ,p_pds_information27 => p_pds_information27
493 ,p_pds_information28 => p_pds_information28
494 ,p_pds_information29 => p_pds_information29
495 ,p_pds_information30 => p_pds_information30
496 --
497 -- 120.2 (START)
498 --
499 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
500 ,p_asg_future_changes_warning => p_asg_future_changes_warning
501 ,p_entries_changed_warning => p_entries_changed_warning
502 --
503 -- 120.2 (END)
504 --
505 );
506 --
507 -- 120.2 (START)
508 --
509 -- For all associated assignments invoke PAY POG processing.
510 for lr_asg_rec in csr_asg_rec(l_pds_rec.person_id,
511 p_period_of_service_id,
512 l_pds_rec.final_process_date)
513 loop
514 --
515 -- If the FPD has been made null, invoke PAY POG processing to handle this
516 --
517 if (l_pds_rec.final_process_date is not null and
518 p_final_process_date is null)
519 then
520 -- Invoke pay pog processing
521 pay_pog_all_assignments_pkg.after_delete
522 (p_effective_date => p_effective_date
523 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
524 ,p_validation_start_date => lr_asg_rec.effective_start_date
525 ,p_validation_end_date => lr_asg_rec.effective_end_date
526 ,p_assignment_id => lr_asg_rec.assignment_id
527 ,p_effective_end_date => hr_api.g_eot
528 ,p_effective_start_date => lr_asg_rec.effective_start_date
529 ,p_object_version_number => lr_asg_rec.object_version_number
530 ,p_org_now_no_manager_warning => null
531 ,p_applicant_rank_o => null
532 ,p_application_id_o => null
533 ,p_assignment_category_o => null
534 ,p_assignment_number_o => null
535 ,p_assignment_sequence_o => null
536 ,p_assignment_status_type_id_o => null
537 ,p_assignment_type_o => null
538 ,p_ass_attribute1_o => null
539 ,p_ass_attribute10_o => null
540 ,p_ass_attribute11_o => null
541 ,p_ass_attribute12_o => null
542 ,p_ass_attribute13_o => null
543 ,p_ass_attribute14_o => null
544 ,p_ass_attribute15_o => null
545 ,p_ass_attribute16_o => null
546 ,p_ass_attribute17_o => null
547 ,p_ass_attribute18_o => null
548 ,p_ass_attribute19_o => null
549 ,p_ass_attribute2_o => null
550 ,p_ass_attribute20_o => null
551 ,p_ass_attribute21_o => null
552 ,p_ass_attribute22_o => null
553 ,p_ass_attribute23_o => null
554 ,p_ass_attribute24_o => null
555 ,p_ass_attribute25_o => null
556 ,p_ass_attribute26_o => null
557 ,p_ass_attribute27_o => null
558 ,p_ass_attribute28_o => null
559 ,p_ass_attribute29_o => null
560 ,p_ass_attribute3_o => null
561 ,p_ass_attribute30_o => null
562 ,p_ass_attribute4_o => null
563 ,p_ass_attribute5_o => null
564 ,p_ass_attribute6_o => null
565 ,p_ass_attribute7_o => null
566 ,p_ass_attribute8_o => null
567 ,p_ass_attribute9_o => null
568 ,p_ass_attribute_category_o => null
569 ,p_bargaining_unit_code_o => null
570 ,p_business_group_id_o => lr_asg_rec.business_group_id
571 ,p_cagr_grade_def_id_o => null
572 ,p_cagr_id_flex_num_o => null
573 ,p_change_reason_o => null
574 ,p_collective_agreement_id_o => null
575 ,p_comment_id_o => null
576 ,p_contract_id_o => null
577 ,p_date_probation_end_o => null
578 ,p_default_code_comb_id_o => null
579 ,p_effective_end_date_o => l_pds_rec.final_process_date
580 ,p_effective_start_date_o => lr_asg_rec.effective_start_date
581 ,p_employee_category_o => null
582 ,p_employment_category_o => null
583 ,p_establishment_id_o => null
584 ,p_frequency_o => null
585 ,p_grade_id_o => null
586 ,p_hourly_salaried_code_o => null
587 ,p_internal_address_line_o => null
588 ,p_job_id_o => null
589 ,p_job_post_source_name_o => null
590 ,p_labour_union_member_flag_o => null
591 ,p_location_id_o => null
592 ,p_manager_flag_o => null
593 ,p_normal_hours_o => null
594 ,p_notice_period_o => null
595 ,p_notice_period_uom_o => null
596 ,p_object_version_number_o => null
597 ,p_organization_id_o => null
598 ,p_payroll_id_o => lr_asg_rec.payroll_id
599 ,p_pay_basis_id_o => null
600 ,p_people_group_id_o => null
601 ,p_perf_review_period_o => null
602 ,p_perf_review_period_frequen_o => null
603 ,p_period_of_service_id_o => null
604 ,p_person_id_o => null
605 ,p_person_referred_by_id_o => null
606 ,p_placement_date_start_o => null
607 ,p_position_id_o => null
608 ,p_posting_content_id_o => null
609 ,p_primary_flag_o => null
610 ,p_probation_period_o => null
611 ,p_probation_unit_o => null
612 ,p_program_application_id_o => null
613 ,p_program_id_o => null
614 ,p_program_update_date_o => null
615 ,p_project_title_o => null
616 ,p_recruiter_id_o => null
617 ,p_recruitment_activity_id_o => null
618 ,p_request_id_o => null
619 ,p_sal_review_period_o => null
620 ,p_sal_review_period_frequen_o => null
621 ,p_set_of_books_id_o => null
622 ,p_soft_coding_keyflex_id_o => null
623 ,p_source_organization_id_o => null
624 ,p_source_type_o => null
625 ,p_special_ceiling_step_id_o => null
626 ,p_supervisor_id_o => null
627 ,p_time_normal_finish_o => null
628 ,p_time_normal_start_o => null
629 ,p_title_o => null
630 ,p_vacancy_id_o => null
631 ,p_vendor_assignment_number_o => null
632 ,p_vendor_employee_number_o => null
633 ,p_vendor_id_o => null
634 ,p_work_at_home_o => null
635 ,p_grade_ladder_pgm_id_o => null
636 ,p_supervisor_assignment_id_o => null
637 ,p_vendor_site_id_o => null
638 ,p_po_header_id_o => null
639 ,p_po_line_id_o => null
640 ,p_projected_assignment_end_o => null
641 );
642 end if; -- FPD has been nulled from not null
643 --
644 -- If the FPD has been changed
645 --
646 if (l_pds_rec.final_process_date is not null and
647 p_final_process_date is not null and
648 l_pds_rec.final_process_date <> p_final_process_date)
649 then
650 -- Invoke pay pog processing
651 pay_pog_all_assignments_pkg.after_delete
652 (p_effective_date => p_effective_date
653 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
654 ,p_validation_start_date => lr_asg_rec.effective_start_date
655 ,p_validation_end_date => lr_asg_rec.effective_end_date
656 ,p_assignment_id => lr_asg_rec.assignment_id
657 ,p_effective_end_date => lr_asg_rec.effective_end_date
658 ,p_effective_start_date => lr_asg_rec.effective_start_date
659 ,p_object_version_number => lr_asg_rec.object_version_number
660 ,p_org_now_no_manager_warning => null
661 ,p_applicant_rank_o => null
662 ,p_application_id_o => null
663 ,p_assignment_category_o => null
664 ,p_assignment_number_o => null
665 ,p_assignment_sequence_o => null
666 ,p_assignment_status_type_id_o => null
667 ,p_assignment_type_o => null
668 ,p_ass_attribute1_o => null
669 ,p_ass_attribute10_o => null
670 ,p_ass_attribute11_o => null
671 ,p_ass_attribute12_o => null
672 ,p_ass_attribute13_o => null
673 ,p_ass_attribute14_o => null
674 ,p_ass_attribute15_o => null
675 ,p_ass_attribute16_o => null
676 ,p_ass_attribute17_o => null
677 ,p_ass_attribute18_o => null
678 ,p_ass_attribute19_o => null
679 ,p_ass_attribute2_o => null
680 ,p_ass_attribute20_o => null
681 ,p_ass_attribute21_o => null
682 ,p_ass_attribute22_o => null
683 ,p_ass_attribute23_o => null
684 ,p_ass_attribute24_o => null
685 ,p_ass_attribute25_o => null
686 ,p_ass_attribute26_o => null
687 ,p_ass_attribute27_o => null
688 ,p_ass_attribute28_o => null
689 ,p_ass_attribute29_o => null
690 ,p_ass_attribute3_o => null
691 ,p_ass_attribute30_o => null
692 ,p_ass_attribute4_o => null
693 ,p_ass_attribute5_o => null
694 ,p_ass_attribute6_o => null
695 ,p_ass_attribute7_o => null
696 ,p_ass_attribute8_o => null
697 ,p_ass_attribute9_o => null
698 ,p_ass_attribute_category_o => null
699 ,p_bargaining_unit_code_o => null
700 ,p_business_group_id_o => lr_asg_rec.business_group_id
701 ,p_cagr_grade_def_id_o => null
702 ,p_cagr_id_flex_num_o => null
703 ,p_change_reason_o => null
704 ,p_collective_agreement_id_o => null
705 ,p_comment_id_o => null
706 ,p_contract_id_o => null
707 ,p_date_probation_end_o => null
708 ,p_default_code_comb_id_o => null
709 ,p_effective_end_date_o => l_pds_rec.final_process_date
710 ,p_effective_start_date_o => lr_asg_rec.effective_start_date
711 ,p_employee_category_o => null
712 ,p_employment_category_o => null
713 ,p_establishment_id_o => null
714 ,p_frequency_o => null
715 ,p_grade_id_o => null
716 ,p_hourly_salaried_code_o => null
717 ,p_internal_address_line_o => null
718 ,p_job_id_o => null
719 ,p_job_post_source_name_o => null
720 ,p_labour_union_member_flag_o => null
721 ,p_location_id_o => null
722 ,p_manager_flag_o => null
723 ,p_normal_hours_o => null
724 ,p_notice_period_o => null
725 ,p_notice_period_uom_o => null
726 ,p_object_version_number_o => null
727 ,p_organization_id_o => null
728 ,p_payroll_id_o => lr_asg_rec.payroll_id
729 ,p_pay_basis_id_o => null
730 ,p_people_group_id_o => null
731 ,p_perf_review_period_o => null
732 ,p_perf_review_period_frequen_o => null
733 ,p_period_of_service_id_o => null
734 ,p_person_id_o => null
735 ,p_person_referred_by_id_o => null
736 ,p_placement_date_start_o => null
737 ,p_position_id_o => null
738 ,p_posting_content_id_o => null
739 ,p_primary_flag_o => null
740 ,p_probation_period_o => null
741 ,p_probation_unit_o => null
742 ,p_program_application_id_o => null
743 ,p_program_id_o => null
744 ,p_program_update_date_o => null
745 ,p_project_title_o => null
746 ,p_recruiter_id_o => null
747 ,p_recruitment_activity_id_o => null
748 ,p_request_id_o => null
749 ,p_sal_review_period_o => null
750 ,p_sal_review_period_frequen_o => null
751 ,p_set_of_books_id_o => null
752 ,p_soft_coding_keyflex_id_o => null
753 ,p_source_organization_id_o => null
754 ,p_source_type_o => null
755 ,p_special_ceiling_step_id_o => null
756 ,p_supervisor_id_o => null
757 ,p_time_normal_finish_o => null
758 ,p_time_normal_start_o => null
759 ,p_title_o => null
760 ,p_vacancy_id_o => null
761 ,p_vendor_assignment_number_o => null
762 ,p_vendor_employee_number_o => null
763 ,p_vendor_id_o => null
764 ,p_work_at_home_o => null
765 ,p_grade_ladder_pgm_id_o => null
766 ,p_supervisor_assignment_id_o => null
767 ,p_vendor_site_id_o => null
768 ,p_po_header_id_o => null
769 ,p_po_line_id_o => null
770 ,p_projected_assignment_end_o => null
771 );
772 end if; -- FPD has changed
773 --
774 end loop; -- Loop through assignments
775 --
776 -- 120.2 (END)
777 --
778 /*
779 ** Process actual termination date if it's set for the first time....
780 */
781 if ( l_pds_rec.actual_termination_date is null
782 and p_actual_termination_date is not null)
783 OR
784 ( l_pds_rec.last_standard_process_date is null
785 and p_last_standard_process_date is not null)
786 then
787 l_last_std_process_date_in := p_last_standard_process_date;
788 --
789 -- 120.2 (START)
790 --
791 if l_pds_rec.actual_termination_date is null then
792 l_atd_new := 1;
793 else
794 l_atd_new := 0;
795 end if;
796 if l_pds_rec.last_standard_process_date is null then
797 l_lspd_new := 1;
798 else
799 l_lspd_new := 0;
800 end if;
801 --
802 -- 120.2 (END)
803 --
804 -- added for bug6892097
805 if p_actual_termination_date = p_final_process_date then
806 open csr_get_asg_end_date(p_actual_termination_date);
807 fetch csr_get_asg_end_date into l_max_asg_date1;
808 close csr_get_asg_end_date;
809 end if ;
810
811 -- end of bug6892097
812 --
813 hr_ex_employee_api.actual_termination_emp
814 (p_effective_date => p_effective_date
815 ,p_period_of_service_id => p_period_of_service_id
816 ,p_object_version_number => p_object_version_number
817 ,p_actual_termination_date => p_actual_termination_date
818 ,p_last_standard_process_date => l_last_std_process_date_in
819 ,p_person_type_id => p_person_type_id
820 ,p_assignment_status_type_id => p_assignment_status_type_id
821 ,p_leaving_reason => p_leaving_reason
822 --
823 -- 120.2 (START)
824 --
825 ,p_atd_new => l_atd_new
826 ,p_lspd_new => l_lspd_new
827 --
828 -- 120.2 (END)
829 --
830 ,p_attribute_category => p_attribute_category
831 ,p_attribute1 => p_attribute1
832 ,p_attribute2 => p_attribute2
833 ,p_attribute3 => p_attribute3
834 ,p_attribute4 => p_attribute4
835 ,p_attribute5 => p_attribute5
836 ,p_attribute6 => p_attribute6
837 ,p_attribute7 => p_attribute7
838 ,p_attribute8 => p_attribute8
839 ,p_attribute9 => p_attribute9
840 ,p_attribute10 => p_attribute10
841 ,p_attribute11 => p_attribute11
842 ,p_attribute12 => p_attribute12
843 ,p_attribute13 => p_attribute13
844 ,p_attribute14 => p_attribute14
845 ,p_attribute15 => p_attribute15
846 ,p_attribute16 => p_attribute16
847 ,p_attribute17 => p_attribute17
848 ,p_attribute18 => p_attribute18
849 ,p_attribute19 => p_attribute19
850 ,p_attribute20 => p_attribute20
851 ,p_pds_information_category => p_pds_information_category
852 ,p_pds_information1 => p_pds_information1
853 ,p_pds_information2 => p_pds_information2
854 ,p_pds_information3 => p_pds_information3
855 ,p_pds_information4 => p_pds_information4
856 ,p_pds_information5 => p_pds_information5
857 ,p_pds_information6 => p_pds_information6
858 ,p_pds_information7 => p_pds_information7
859 ,p_pds_information8 => p_pds_information8
860 ,p_pds_information9 => p_pds_information9
861 ,p_pds_information10 => p_pds_information10
862 ,p_pds_information11 => p_pds_information11
863 ,p_pds_information12 => p_pds_information12
864 ,p_pds_information13 => p_pds_information13
865 ,p_pds_information14 => p_pds_information14
866 ,p_pds_information15 => p_pds_information15
867 ,p_pds_information16 => p_pds_information16
868 ,p_pds_information17 => p_pds_information17
869 ,p_pds_information18 => p_pds_information18
870 ,p_pds_information19 => p_pds_information19
871 ,p_pds_information20 => p_pds_information20
872 ,p_pds_information21 => p_pds_information21
873 ,p_pds_information22 => p_pds_information22
874 ,p_pds_information23 => p_pds_information23
875 ,p_pds_information24 => p_pds_information24
876 ,p_pds_information25 => p_pds_information25
877 ,p_pds_information26 => p_pds_information26
878 ,p_pds_information27 => p_pds_information27
879 ,p_pds_information28 => p_pds_information28
880 ,p_pds_information29 => p_pds_information29
881 ,p_pds_information30 => p_pds_information30
882 ,p_last_std_process_date_out => l_last_std_process_date_out
883 ,p_supervisor_warning => p_supervisor_warning
884 ,p_event_warning => p_event_warning
885 ,p_interview_warning => p_interview_warning
886 ,p_review_warning => p_review_warning
887 ,p_recruiter_warning => p_recruiter_warning
888 ,p_asg_future_changes_warning => p_asg_future_changes_warning
889 ,p_entries_changed_warning => p_entries_changed_warning
890 ,p_pay_proposal_warning => p_pay_proposal_warning
891 ,p_dod_warning => p_dod_warning
892 --
893 -- 120.2 (START)
894 --
895 ,p_alu_change_warning => l_alu_change_warning
896 --
897 -- 120.2 (END)
898 --
899 );
900
901 p_last_standard_process_date := l_last_std_process_date_out;
902 --
903 -- 120.2 (START)
904 --
905 p_alu_change_warning := l_alu_change_warning;
906 --
907 -- 120.2 (END)
908 --
909
910 -- fix 1370960
911 -- Terminate the roles
912 hr_utility.set_location('l_cur_pds.person_id' || l_cur_pds.person_id, 960);
913 for roles_rec in csr_roles_to_terminate( l_cur_pds.person_id )
914 loop
915 per_supplementary_role_api.update_supplementary_role(
916 p_effective_date => p_effective_date
917 ,p_role_id => roles_rec.role_id
918 ,p_object_version_number => roles_rec.object_version_number
919 ,p_end_date => p_actual_termination_date
920 ,p_old_end_date => roles_rec.end_date
921 );
922 end loop;
923
924 -- Raise a warning if extra rights are there for the person
925 open csr_chk_addl_rights( l_cur_pds.person_id );
926 fetch csr_chk_addl_rights into dummy;
927 if csr_chk_addl_rights%found then
928 p_addl_rights_warning := TRUE;
929 else
930 p_addl_rights_warning := FALSE;
931 end if;
932 close csr_chk_addl_rights;
933 -- end fix 1370960
934 end if;
935
936 /*
937 ** If it's set process final process date....
938 */
939 if l_pds_rec.final_process_date is null
940 and p_final_process_date is not null
941 then
942 hr_ex_employee_api.final_process_emp
943 (p_period_of_service_id => p_period_of_service_id
944 ,p_object_version_number => p_object_version_number
945 ,p_final_process_date => p_final_process_date
946 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
947 ,p_asg_future_changes_warning => p_asg_future_changes_warning
948 ,p_entries_changed_warning => p_entries_changed_warning
949 );
950 end if;
951 --
952 -- fix for bug6892097
953 hr_utility.set_location('p_asg_future ', 99);
954 if p_asg_future_changes_warning = FALSE then
955 hr_utility.set_location('p_asg_future ', 100);
956 if l_max_asg_date1 is not null then
957 hr_utility.set_location('p_asg_future ', 101);
958 p_asg_future_changes_warning := TRUE;
959
960 end if;
961 end if;
962 -- fix for bug6892097
963
964 /*
965 ** Now perform the PDS life event processing....
966 */
967 l_new_pds.person_id := l_cur_pds.person_id;
968 l_new_pds.business_group_id := l_cur_pds.business_group_id;
969 l_new_pds.date_start := l_cur_pds.date_start;
970 l_new_pds.actual_termination_date := p_actual_termination_date;
971 l_new_pds.leaving_reason := p_leaving_reason;
972 l_new_pds.adjusted_svc_date := p_adjusted_svc_date;
973 l_new_pds.attribute1 := p_attribute1;
974 l_new_pds.attribute2 := p_attribute2;
975 l_new_pds.attribute3 := p_attribute3;
976 l_new_pds.attribute4 := p_attribute4;
977 l_new_pds.attribute5 := p_attribute5;
978 l_new_pds.final_process_date := p_final_process_date;
979 l_new_pds.period_of_service_id := p_period_of_service_id;
980
981 ben_pps_ler.ler_chk(p_old => l_cur_pds
982 ,p_new => l_new_pds
983 ,p_event => 'UPDATING'
984 ,p_effective_date => p_effective_date);
985 --
986 --START WWBUG 2130950 HR/WF Synchronization --tpapired
987
988 /* Bug 5504659
989 open l_pds_cur;
990 fetch l_pds_cur into l_pds_rec;
991 close l_pds_cur;
992 per_hrwf_synch.per_pds_wf(
993 p_rec => l_pds_rec,
994 p_date => p_actual_termination_date,
995 p_action => 'TERMINATION');
996 Note : added p_date_start to test, earlier code does not work*/
997
998 --
999 --END WWBUG 2130950 HR/WF Synchronization --tpapired
1000 --
1001 hr_utility.set_location('Leaving '||l_proc,100);
1002 exception
1003 when others then
1004 g_mask_pds_ler := FALSE;
1005 raise;
1006 end;
1007 end terminate_employee;
1008 --
1009 -- ----------------------------------------------------------------------------
1010 -- |-----------------------< reverse_terminate_employee >-------------------------|
1011 -- ----------------------------------------------------------------------------
1012
1013 procedure reverse_terminate_employee
1014 (p_person_id in number
1015 ,p_actual_termination_date in date
1016 ,p_clear_details in varchar2
1017 ) is
1018
1019 --
1020 -- Declare cursors and local variables
1021 --
1022 l_proc varchar2(72) := g_package||'reverse_terminate_employee';
1023
1024 begin
1025 hr_utility.set_location('Entering: hrempter.cancel_termination'|| l_proc, 5);
1026
1027 --
1028 -- Process Logic
1029 --
1030 hrempter.cancel_termination(p_person_id
1031 ,p_actual_termination_date
1032 ,p_clear_details);
1033 hr_utility.set_location('After: hrempter.cancel_termination'|| l_proc, 10);
1034
1035 exception
1036 when others then
1037 raise;
1038 end reverse_terminate_employee;
1039 --
1040 -- ----------------------------------------------------------------------------
1041 -- |-------------------------< RETURN_TERM_TYPE >---------------------------|
1042 -- ----------------------------------------------------------------------------
1043 --
1044 Function return_term_type (p_LEAVING_REASON IN VARCHAR2,
1045 P_BUSINESS_GROUP_ID IN NUMBER default null)
1046 return varchar2 IS
1047
1048 -- this cursor will be used to return the Status type code available globally
1049 cursor csr_shared_types is
1050 select SHARED_TYPE_CODE from
1051 per_shared_types
1052 where system_type_cd = p_LEAVING_REASON
1053 and lookup_type='LEAV_REAS';
1054
1055 -- this cursor will be used to return the Status type code for the specific business group.
1056 cursor csr_shared_types_bg is
1057 select SHARED_TYPE_CODE from
1058 per_shared_types
1059 where system_type_cd = p_LEAVING_REASON
1060 and lookup_type='LEAV_REAS'
1061 and business_group_id=p_business_group_id;
1062
1063
1064 TERM_TYPE VARCHAR2(30);
1065
1066 begin
1067
1068 if P_BUSINESS_GROUP_ID is not null then
1069
1070 OPEN csr_shared_types_bg ;
1071 FETCH csr_shared_types_bg INTO TERM_TYPE;
1072 CLOSE csr_shared_types_bg;
1073
1074 else
1075
1076 OPEN csr_shared_types ;
1077 FETCH csr_shared_types INTO TERM_TYPE;
1078 CLOSE csr_shared_types;
1079
1080 end if;
1081
1082
1083 hr_utility.set_location('l_proc'||TERM_TYPE, 2);
1084 hr_utility.set_location('l_proc', 30);
1085
1086 return TERM_TYPE;
1087
1088
1089 end return_term_type;
1090
1091 end hr_ex_employee_internal;