[Home] [Help]
PACKAGE BODY: APPS.HR_EX_EMPLOYEE_API
Source
1 Package Body hr_ex_employee_api as
2 /* $Header: peexeapi.pkb 120.8.12000000.2 2007/08/10 10:44:22 pchowdav ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_ex_employee_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< pre_term_check >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure pre_term_check(p_status IN OUT NOCOPY VARCHAR2
13 ,p_business_group_id IN NUMBER
14 ,p_person_id IN NUMBER
15 ,p_session_date IN DATE
16 )is
17 --
18 --
19 v_dummy VARCHAR2(1);
20 l_proc varchar2(45) := g_package||'pre_term_check';
21 --
22 begin
23 if p_status = 'SUPERVISOR' then
24 begin
25 hr_utility.set_location(l_proc,10);
26 Select 'X'
27 into v_dummy
28 from sys.dual
29 where exists (select 'Assignments Exist'
30 from per_assignments_f paf
31 where paf.supervisor_id = p_person_id
32 and paf.business_group_id + 0 = p_business_group_id
33 and p_session_date <= paf.effective_end_date);
34 --
35 -- Bug 2492106. check this person is supervisor
36 -- on after the p_effective_date.
37 --
38 p_status := 'WARNING';
39 return;
40 exception
41 when no_data_found then
42 return;
43 end;
44 elsif p_status = 'EVENT' then
45 begin
46 hr_utility.set_location(l_proc,30);
47 select 'X'
48 into v_dummy
49 from sys.dual
50 where exists ( select 'Events exist'
51 from per_events pe
52 , per_bookings pb
53 where pe.business_group_id = pb.business_group_id
54 and (pb.business_group_id = p_business_group_id OR
55 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
56 and pe.event_id = pb.event_id
57 and pe.event_or_interview = 'E'
58 and pb.person_id = p_person_id
59 and pe.date_start > p_session_date
60 );
61 p_status := 'WARNING';
62 return;
63 exception
64 when no_data_found then
65 return;
66 end;
67 end if;
68 if p_status = 'INTERVIEW' then
69 begin
70 hr_utility.set_location(l_proc,40);
71 select 'X'
72 into v_dummy
73 from sys.dual
74 where exists(select 'Interview rows exist'
75 from per_events pe
76 where pe.business_group_id + 0 = p_business_group_id
77 and pe.event_or_interview = 'I'
78 and pe.internal_contact_person_id = p_person_id
79 and pe.date_start > p_session_date
80 )
81 OR
82 exists(select 'Interview rows exist'
83 from per_events pe
84 ,per_bookings pb
85 where pe.business_group_id = pb.business_group_id
86 and (pb.business_group_id = p_business_group_id OR
87 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
88 and pe.event_id = pb.event_id
89 and pe.event_or_interview = 'I'
90 and pb.person_id = p_person_id
91 and pe.date_start > p_session_date
92 );
93 p_status := 'WARNING';
94 return;
95 exception
96 when no_data_found then
97 return;
98 end;
99 end if;
100 if p_status = 'REVIEW' then
101 begin
102 hr_utility.set_location(l_proc,50);
103 select 'X'
104 into v_dummy
105 from sys.dual
106 where exists ( select 'Perf Review rows exist'
107 from per_performance_reviews ppr
108 where ppr.person_id = p_person_id
109 and review_date > p_session_date
110 );
111 p_status := 'WARNING';
112 return;
113 exception
114 when no_data_found then
115 return;
116 end;
117 end if;
118 if p_status = 'RECRUITER' then
119 begin
120 hr_utility.set_location(l_proc,60);
121 select 'X'
122 into v_dummy
123 from sys.dual
124 where exists (select 'Recruiter for vacancy'
125 from per_vacancies pv
126 where
127 -- Fix for Bug 3446782 starts here. this condition is taken
128 -- care in view definition.
129 /*(pv.business_group_id = p_business_group_id OR
130 nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
131 and */
132 pv.recruiter_id = p_person_id
133 and nvl(pv.date_to, p_session_date) >= p_session_date);
134 p_status := 'WARNING';
135 return;
136 exception
137 when no_data_found then
138 return;
139 end;
140 end if;
141 end pre_term_check;
142 --
143 -- 120.2 (START)
144 --
145 -- ----------------------------------------------------------------------------
146 -- |-------------------< actual_termination_emp (Overload)>-------------------|
147 -- ----------------------------------------------------------------------------
148 --
149 procedure actual_termination_emp
150 (p_validate in boolean default false
151 ,p_effective_date in date
152 ,p_period_of_service_id in number
153 ,p_object_version_number in out nocopy number
154 ,p_actual_termination_date in date
155 ,p_last_standard_process_date in out nocopy date
156 ,p_person_type_id in number default hr_api.g_number
157 ,p_assignment_status_type_id in number default hr_api.g_number
158 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
159 ,p_supervisor_warning out nocopy boolean
160 ,p_event_warning out nocopy boolean
161 ,p_interview_warning out nocopy boolean
162 ,p_review_warning out nocopy boolean
163 ,p_recruiter_warning out nocopy boolean
164 ,p_asg_future_changes_warning out nocopy boolean
165 ,p_entries_changed_warning out nocopy varchar2
166 ,p_pay_proposal_warning out nocopy boolean
167 ,p_dod_warning out nocopy boolean
168 ) is
169
170 l_last_std_process_date_in date;
171 l_last_std_process_date_out date;
172 l_alu_change_warning varchar2(1) := 'N';
173
174 l_proc varchar2(100) := g_package||'.actual_termination_emp';
175
176 begin
177
178 hr_utility.set_location('Entering '||l_proc,10);
179
180 if p_last_standard_process_date is null
181 then
182 /* No LSPD has been passed. To preserve the previous
183 ** behaviour we need to pass g_date to the latest ATE API.
184 */
185 l_last_std_process_date_in := hr_api.g_date;
186 else
187 /* Otherwise pass through the value provided.
188 */
189 l_last_std_process_date_in := p_last_standard_process_date;
190 end if;
191
192 actual_termination_emp(
193 p_validate => p_validate
194 ,p_effective_date => p_effective_date
195 ,p_period_of_service_id => p_period_of_service_id
196 ,p_object_version_number => p_object_version_number
197 ,p_actual_termination_date => p_actual_termination_date
198 ,p_last_standard_process_date => l_last_std_process_date_in
199 ,p_person_type_id => p_person_type_id
200 ,p_assignment_status_type_id => p_assignment_status_type_id
201 ,p_leaving_reason => p_leaving_reason
202 ,p_last_std_process_date_out => l_last_std_process_date_out
203 ,p_supervisor_warning => p_supervisor_warning
204 ,p_event_warning => p_event_warning
205 ,p_interview_warning => p_interview_warning
206 ,p_review_warning => p_review_warning
207 ,p_recruiter_warning => p_recruiter_warning
208 ,p_asg_future_changes_warning => p_asg_future_changes_warning
209 ,p_entries_changed_warning => p_entries_changed_warning
210 ,p_pay_proposal_warning => p_pay_proposal_warning
211 ,p_dod_warning => p_dod_warning
212 ,p_alu_change_warning => l_alu_change_warning);
213
214 p_last_standard_process_date := l_last_std_process_date_out;
215
216 hr_utility.set_location('Leaving '||l_proc,40);
217
218 end actual_termination_emp;
219 --
220 -- 120.2 (END)
221 --
222 -- ----------------------------------------------------------------------------
223 -- |-------------------< actual_termination_emp (Overload)>-------------------|
224 -- ----------------------------------------------------------------------------
225 --
226 procedure actual_termination_emp
227 (p_validate in boolean default false
228 ,p_effective_date in date
229 ,p_period_of_service_id in number
230 ,p_object_version_number in out nocopy number
231 ,p_actual_termination_date in date
232 ,p_last_standard_process_date in out nocopy date
233 ,p_person_type_id in number default hr_api.g_number
234 ,p_assignment_status_type_id in number default hr_api.g_number
235 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
236 --
237 -- 120.2 (START)
238 --
239 ,p_atd_new in number default hr_api.g_true_num
240 ,p_lspd_new in number default hr_api.g_true_num
241 --
242 -- 120.2 (END)
243 --
244 ,p_supervisor_warning out nocopy boolean
245 ,p_event_warning out nocopy boolean
246 ,p_interview_warning out nocopy boolean
247 ,p_review_warning out nocopy boolean
248 ,p_recruiter_warning out nocopy boolean
249 ,p_asg_future_changes_warning out nocopy boolean
250 ,p_entries_changed_warning out nocopy varchar2
251 ,p_pay_proposal_warning out nocopy boolean
252 ,p_dod_warning out nocopy boolean
253 --
254 -- 120.2 (START)
255 --
256 ,p_alu_change_warning out nocopy varchar2
257 --
258 -- 120.2 (END)
259 --
260 ) is
261
262 l_last_std_process_date_in date;
263 l_last_std_process_date_out date;
264
265 l_proc varchar2(100) := g_package||'.actual_termination_emp';
266
267 begin
268
269 hr_utility.set_location('Entering '||l_proc,10);
270
271 if p_last_standard_process_date is null
272 then
273 /* No LSPD has been passed. To preserve the previous
274 ** behaviour we need to pass g_date to the latest ATE API.
275 */
276 l_last_std_process_date_in := hr_api.g_date;
277 else
278 /* Otherwise pass through the value provided.
279 */
280 l_last_std_process_date_in := p_last_standard_process_date;
281 end if;
282
283 actual_termination_emp(
284 p_validate => p_validate
285 ,p_effective_date => p_effective_date
286 ,p_period_of_service_id => p_period_of_service_id
287 ,p_object_version_number => p_object_version_number
288 ,p_actual_termination_date => p_actual_termination_date
289 ,p_last_standard_process_date => l_last_std_process_date_in
290 ,p_person_type_id => p_person_type_id
291 ,p_assignment_status_type_id => p_assignment_status_type_id
292 ,p_leaving_reason => p_leaving_reason
293 --
294 -- 120.2 (START)
295 --
296 ,p_atd_new => p_atd_new
297 ,p_lspd_new => p_lspd_new
298 --
299 -- 120.2 (END)
300 --
301
302 ,p_last_std_process_date_out => l_last_std_process_date_out
303 ,p_supervisor_warning => p_supervisor_warning
304 ,p_event_warning => p_event_warning
305 ,p_interview_warning => p_interview_warning
306 ,p_review_warning => p_review_warning
307 ,p_recruiter_warning => p_recruiter_warning
308 ,p_asg_future_changes_warning => p_asg_future_changes_warning
309 ,p_entries_changed_warning => p_entries_changed_warning
310 ,p_pay_proposal_warning => p_pay_proposal_warning
311 --
312 -- 120.2 (START)
313 --
314 --,p_dod_warning => p_dod_warning);
315 ,p_dod_warning => p_dod_warning
316 ,p_alu_change_warning => p_alu_change_warning);
317 --
318 -- 120.2 (END)
319 --
320
321 p_last_standard_process_date := l_last_std_process_date_out;
322
323 hr_utility.set_location('Leaving '||l_proc,40);
324
325 end actual_termination_emp;
326
327 --
328 -- 120.2 (START)
329 --
330 -- ----------------------------------------------------------------------------
331 -- |----------------< actual_termination_emp (overload) >---------------------|
332 -- ----------------------------------------------------------------------------
333 --
334 procedure actual_termination_emp
335 (p_validate in boolean default false
336 ,p_effective_date in date
337 ,p_period_of_service_id in number
338 ,p_object_version_number in out nocopy number
339 ,p_actual_termination_date in date
340 ,p_last_standard_process_date in date default hr_api.g_date
341 ,p_person_type_id in number default hr_api.g_number
342 ,p_assignment_status_type_id in number default hr_api.g_number
343 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
344 ,p_attribute_category in varchar2 default hr_api.g_varchar2
345 ,p_attribute1 in varchar2 default hr_api.g_varchar2
346 ,p_attribute2 in varchar2 default hr_api.g_varchar2
347 ,p_attribute3 in varchar2 default hr_api.g_varchar2
348 ,p_attribute4 in varchar2 default hr_api.g_varchar2
349 ,p_attribute5 in varchar2 default hr_api.g_varchar2
350 ,p_attribute6 in varchar2 default hr_api.g_varchar2
351 ,p_attribute7 in varchar2 default hr_api.g_varchar2
352 ,p_attribute8 in varchar2 default hr_api.g_varchar2
353 ,p_attribute9 in varchar2 default hr_api.g_varchar2
354 ,p_attribute10 in varchar2 default hr_api.g_varchar2
355 ,p_attribute11 in varchar2 default hr_api.g_varchar2
356 ,p_attribute12 in varchar2 default hr_api.g_varchar2
357 ,p_attribute13 in varchar2 default hr_api.g_varchar2
358 ,p_attribute14 in varchar2 default hr_api.g_varchar2
359 ,p_attribute15 in varchar2 default hr_api.g_varchar2
360 ,p_attribute16 in varchar2 default hr_api.g_varchar2
361 ,p_attribute17 in varchar2 default hr_api.g_varchar2
362 ,p_attribute18 in varchar2 default hr_api.g_varchar2
363 ,p_attribute19 in varchar2 default hr_api.g_varchar2
364 ,p_attribute20 in varchar2 default hr_api.g_varchar2
365 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
366 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
367 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
368 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
369 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
370 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
371 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
372 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
373 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
374 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
375 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
376 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
377 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
378 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
379 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
380 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
381 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
382 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
383 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
384 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
385 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
386 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
387 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
388 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
389 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
390 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
391 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
392 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
393 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
394 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
395 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
396 ,p_last_std_process_date_out out nocopy date
397 ,p_supervisor_warning out nocopy boolean
398 ,p_event_warning out nocopy boolean
399 ,p_interview_warning out nocopy boolean
400 ,p_review_warning out nocopy boolean
401 ,p_recruiter_warning out nocopy boolean
402 ,p_asg_future_changes_warning out nocopy boolean
403 ,p_entries_changed_warning out nocopy varchar2
404 ,p_pay_proposal_warning out nocopy boolean
405 ,p_dod_warning out nocopy boolean
406 ) is
407 --
408 l_alu_change_warning VARCHAR2(1);
409 --
410 begin
411 --
412 actual_termination_emp
413 (p_validate => p_validate
414 ,p_effective_date => p_effective_date
415 ,p_period_of_service_id => p_period_of_service_id
416 ,p_object_version_number => p_object_version_number
417 ,p_actual_termination_date => p_actual_termination_date
418 ,p_last_standard_process_date => p_last_standard_process_date
419 ,p_person_type_id => p_person_type_id
420 ,p_assignment_status_type_id => p_assignment_status_type_id
421 ,p_leaving_reason => p_leaving_reason
422 ,p_attribute_category => p_attribute_category
423 ,p_attribute1 => p_attribute1
427 ,p_attribute5 => p_attribute5
424 ,p_attribute2 => p_attribute2
425 ,p_attribute3 => p_attribute3
426 ,p_attribute4 => p_attribute4
428 ,p_attribute6 => p_attribute6
429 ,p_attribute7 => p_attribute7
430 ,p_attribute8 => p_attribute8
431 ,p_attribute9 => p_attribute9
432 ,p_attribute10 => p_attribute10
433 ,p_attribute11 => p_attribute11
434 ,p_attribute12 => p_attribute12
435 ,p_attribute13 => p_attribute13
436 ,p_attribute14 => p_attribute14
437 ,p_attribute15 => p_attribute15
438 ,p_attribute16 => p_attribute16
439 ,p_attribute17 => p_attribute17
440 ,p_attribute18 => p_attribute18
441 ,p_attribute19 => p_attribute19
442 ,p_attribute20 => p_attribute20
443 ,p_pds_information_category => p_pds_information_category
444 ,p_pds_information1 => p_pds_information1
445 ,p_pds_information2 => p_pds_information2
446 ,p_pds_information3 => p_pds_information3
447 ,p_pds_information4 => p_pds_information4
448 ,p_pds_information5 => p_pds_information5
449 ,p_pds_information6 => p_pds_information6
450 ,p_pds_information7 => p_pds_information7
451 ,p_pds_information8 => p_pds_information8
452 ,p_pds_information9 => p_pds_information9
453 ,p_pds_information10 => p_pds_information10
454 ,p_pds_information11 => p_pds_information11
455 ,p_pds_information12 => p_pds_information12
456 ,p_pds_information13 => p_pds_information13
457 ,p_pds_information14 => p_pds_information14
458 ,p_pds_information15 => p_pds_information15
459 ,p_pds_information16 => p_pds_information16
460 ,p_pds_information17 => p_pds_information17
461 ,p_pds_information18 => p_pds_information18
462 ,p_pds_information19 => p_pds_information19
463 ,p_pds_information20 => p_pds_information20
464 ,p_pds_information21 => p_pds_information21
465 ,p_pds_information22 => p_pds_information22
466 ,p_pds_information23 => p_pds_information23
467 ,p_pds_information24 => p_pds_information24
468 ,p_pds_information25 => p_pds_information25
469 ,p_pds_information26 => p_pds_information26
470 ,p_pds_information27 => p_pds_information27
471 ,p_pds_information28 => p_pds_information28
472 ,p_pds_information29 => p_pds_information29
473 ,p_pds_information30 => p_pds_information30
474 ,p_last_std_process_date_out => p_last_std_process_date_out
475 ,p_supervisor_warning => p_supervisor_warning
476 ,p_event_warning => p_event_warning
477 ,p_interview_warning => p_interview_warning
478 ,p_review_warning => p_review_warning
479 ,p_recruiter_warning => p_recruiter_warning
480 ,p_asg_future_changes_warning => p_asg_future_changes_warning
481 ,p_entries_changed_warning => p_entries_changed_warning
482 ,p_pay_proposal_warning => p_pay_proposal_warning
483 ,p_dod_warning => p_dod_warning
484 ,p_alu_change_warning => l_alu_change_warning
485 );
486 --
487 end actual_termination_emp;
488 --
489 -- 120.2 (END)
490 --
491
492 --
493 -- 70.2 change a start.
494 --
495 -- ----------------------------------------------------------------------------
496 -- |-----------------------< actual_termination_emp >-------------------------|
497 -- ----------------------------------------------------------------------------
498 --
499 procedure actual_termination_emp
500 (p_validate in boolean default false
501 ,p_effective_date in date
502 ,p_period_of_service_id in number
503 ,p_object_version_number in out nocopy number
504 ,p_actual_termination_date in date
505 ,p_last_standard_process_date in date default hr_api.g_date
506 ,p_person_type_id in number default hr_api.g_number
507 ,p_assignment_status_type_id in number default hr_api.g_number
508 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
509 --
510 -- 120.2 (START)
511 --
512 ,p_atd_new in number default hr_api.g_true_num
513 ,p_lspd_new in number default hr_api.g_true_num
514 --
515 -- 120.2 (END)
516 --
517 ,p_attribute_category in varchar2 default hr_api.g_varchar2
518 ,p_attribute1 in varchar2 default hr_api.g_varchar2
519 ,p_attribute2 in varchar2 default hr_api.g_varchar2
520 ,p_attribute3 in varchar2 default hr_api.g_varchar2
521 ,p_attribute4 in varchar2 default hr_api.g_varchar2
522 ,p_attribute5 in varchar2 default hr_api.g_varchar2
523 ,p_attribute6 in varchar2 default hr_api.g_varchar2
524 ,p_attribute7 in varchar2 default hr_api.g_varchar2
528 ,p_attribute11 in varchar2 default hr_api.g_varchar2
525 ,p_attribute8 in varchar2 default hr_api.g_varchar2
526 ,p_attribute9 in varchar2 default hr_api.g_varchar2
527 ,p_attribute10 in varchar2 default hr_api.g_varchar2
529 ,p_attribute12 in varchar2 default hr_api.g_varchar2
530 ,p_attribute13 in varchar2 default hr_api.g_varchar2
531 ,p_attribute14 in varchar2 default hr_api.g_varchar2
532 ,p_attribute15 in varchar2 default hr_api.g_varchar2
533 ,p_attribute16 in varchar2 default hr_api.g_varchar2
534 ,p_attribute17 in varchar2 default hr_api.g_varchar2
535 ,p_attribute18 in varchar2 default hr_api.g_varchar2
536 ,p_attribute19 in varchar2 default hr_api.g_varchar2
537 ,p_attribute20 in varchar2 default hr_api.g_varchar2
538 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
539 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
540 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
541 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
542 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
543 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
544 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
545 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
546 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
547 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
548 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
549 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
550 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
551 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
552 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
553 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
554 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
555 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
556 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
557 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
558 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
559 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
560 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
561 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
562 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
563 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
564 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
565 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
566 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
567 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
568 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
569 ,p_last_std_process_date_out out nocopy date
570 ,p_supervisor_warning out nocopy boolean
571 ,p_event_warning out nocopy boolean
572 ,p_interview_warning out nocopy boolean
573 ,p_review_warning out nocopy boolean
574 ,p_recruiter_warning out nocopy boolean
575 ,p_asg_future_changes_warning out nocopy boolean
576 ,p_entries_changed_warning out nocopy varchar2
577 ,p_pay_proposal_warning out nocopy boolean
578 ,p_dod_warning out nocopy boolean
579 --
580 -- 120.2 (START)
581 --
582 ,p_alu_change_warning out nocopy varchar2
583 --
584 -- 120.2 (END)
585 --
586 ) is
587 --
588 -- Declare cursors and local variables
589 --
590 -- Out variables
591 --
592 l_asg_future_changes_warning boolean := FALSE;
593 l_entries_changed_warning varchar2(1) := 'N';
594 l_event_warning boolean := FALSE;
595 l_interview_warning boolean := FALSE;
596 l_last_standard_process_date per_periods_of_service.last_standard_process_date%TYPE;
597 l_last_std_process_date_in per_periods_of_service.last_standard_process_date%TYPE;
598 l_pds_object_version_number per_assignments_f.object_version_number%TYPE;
599 l_ovn per_assignments_f.object_version_number%TYPE := p_object_version_number;
600 l_recruiter_warning boolean := FALSE;
601 l_review_warning boolean := FALSE;
602 l_supervisor_warning boolean := FALSE;
603 l_dod_warning boolean := FALSE;
604 --
605 -- 120.2 (START)
606 --
607 l_alu_change_warning varchar2(1) := 'N';
608 --
609 -- 120.2 (END)
610 --
611 --
612 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
613 l_business_group_id per_assignments_f.business_group_id%TYPE;
614 l_comment_id per_assignments_f.comment_id%TYPE;
615 l_cr_asg_future_changes_warn boolean := FALSE;
616 l_cr_entries_changed_warn varchar2(1) := 'N';
620 l_current_employee_flag varchar2(1);
617 l_pay_proposal_warn boolean := FALSE;
618 l_current_applicant_flag varchar2(1);
619 l_current_emp_or_apl_flag varchar2(1);
621 l_dob_null_warning boolean;
622 l_effective_date date;
623 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
624 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
625 l_employee_number per_all_people_f.employee_number%TYPE;
626 l_applicant_number per_people_f.applicant_number%TYPE;
627 l_npw_number per_people_f.npw_number%TYPE;
628 l_exists varchar2(1);
629 l_full_name per_all_people_f.full_name%TYPE;
630 l_legislation_code per_business_groups.legislation_code%TYPE;
631 l_max_tpe_end_date per_assignments_f.effective_end_date%TYPE;
632 l_name_combination_warning boolean;
633 l_orig_hire_warning boolean;
634 l_per_object_version_number per_assignments_f.object_version_number%TYPE;
635 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
636 l_person_id per_all_people_f.person_id%TYPE;
637 l_person_type_id per_all_people_f.person_type_id%TYPE;
638 l_person_type_id1 per_all_people_f.person_type_id%TYPE;
639 l_proc varchar2(72)
640 := g_package || 'actual_termination_emp';
641 l_system_person_type per_person_types.system_person_type%TYPE;
642 l_system_person_type1 per_person_types.system_person_type%TYPE;
643 l_per_effective_start_date per_people_f.effective_start_date%TYPE;
644 l_datetrack_mode varchar2(30);
645 l_validation_start_date date;
646 l_validation_end_date date;
647 l_actual_termination_date date;
648 l_status varchar2(11);
649 l_current_dod date;
650 l_date_of_death date;
651 l_ptu_object_version_number per_person_type_usages_f.object_version_number%TYPE;
652 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
653 l_action_chk VARCHAR2(1) := 'N';
654
655 l_saved_atd per_periods_of_service.actual_termination_date%TYPE;
656 l_saved_lspd per_periods_of_service.last_standard_process_date%TYPE;
657 --
658
659 cursor csr_future_per_changes is
660 select null
661 from per_all_people_f per
662 where per.person_id = l_person_id
663 and per.effective_start_date > l_actual_termination_date;
664 --
665 cursor csr_get_asgs_to_terminate is
666 select asg.assignment_id
667 , asg.object_version_number
668 from per_assignments_f asg
669 where asg.period_of_service_id = p_period_of_service_id
670 and l_actual_termination_date + 1 between asg.effective_start_date
671 and asg.effective_end_date
672 order by asg.primary_flag;
673 --
674 cursor csr_get_derived_details is
675 select bus.business_group_id
676 , bus.legislation_code
677 , per.person_id
678 , per.employee_number
679 , per.effective_start_date
680 , per.object_version_number
681 , pet.system_person_type
682 , per.npw_number
683 , pds.actual_termination_date
684 , pds.last_standard_process_date
685 from per_all_people_f per
686 , per_business_groups bus
687 , per_periods_of_service pds
688 , per_person_types pet
689 where pds.period_of_service_id = p_period_of_service_id
690 and bus.business_group_id = pds.business_group_id
691 and per.person_id = pds.person_id
692 and l_actual_termination_date between per.effective_start_date
693 and per.effective_end_date
694 and pet.person_type_id = per.person_type_id;
695 --
696 cursor csr_get_max_tpe_end_date is
697 select max(tpe.end_date)
698 from per_time_periods tpe
699 ,per_assignments_f asg
700 where asg.period_of_service_id = p_period_of_service_id
701 and l_actual_termination_date between asg.effective_start_date
702 and asg.effective_end_date
703 and asg.payroll_id is not null
704 and tpe.payroll_id = asg.payroll_id
705 and l_actual_termination_date between tpe.start_date
706 and tpe.end_date;
707 --
708 --
709 cursor csr_date_of_death is
710 select date_of_death
711 from per_all_people_f
712 where person_id = l_person_id;
713 --
714 -- Fix for bug 3829474 starts here.
715 --
716 l_pds_rec per_periods_of_service%rowtype;
717 --
718 cursor l_pds_cur is
719 select *
720 from per_periods_of_service
721 where period_of_service_id = p_period_of_service_id;
722 --
723 -- Fix for bug 3829474 ends here.
724 --
725 --
726 -- Fix for 4371218 starts here
727 --
728 -- new contract cursor start
729 /* cursor l_ctc_cur is
730 select contract_id
731 ,reference
732 ,type
733 ,object_version_number
734 from per_contracts_f
735 where person_id = l_person_id;*/
736
737 -- 2 cursors for GOLD bug 5465050
738
742 FROM_PERSON_ID = p_gold_src_person_id;
739 cursor chk_pre_term_src_gold(p_src_bg_id number,p_gold_src_person_id number) is
740 select * from hr_person_deployments where
741 FROM_BUSINESS_GROUP_ID = p_src_bg_id and
743
744 cursor chk_pre_term_dest_gold(p_dest_bg_id number,p_gold_dest_person_id number) is
745 select * from hr_person_deployments where
746 TO_BUSINESS_GROUP_ID = p_dest_bg_id and
747 TO_PERSON_ID = p_gold_dest_person_id;
748
749 --
750 -- new contract cursor end
751 /* l_contract_id number;
752 l_ctc_ovn per_contracts_f.object_version_number%TYPE;
753 l_ctc_status varchar2(30);*/
754
755 -- 2 parameters for GOLD bug 5465050
756
757 l_src_bg_rec hr_person_deployments%ROWTYPE;
758 l_dest_bg_rec hr_person_deployments%ROWTYPE;
759 --
760 --
761
762 --
763 begin
764 hr_utility.set_location('Entering:'|| l_proc, 10);
765 --
766 -- Issue a savepoint.
767 --
768 savepoint actual_termination_emp;
769 --
770 -- Initialise local varaibles
771 --
772 l_assignment_status_type_id := p_assignment_status_type_id;
773 l_last_standard_process_date := trunc(p_last_standard_process_date);
774 l_last_std_process_date_in := l_last_standard_process_date;
775 l_pds_object_version_number := p_object_version_number;
776 l_person_type_id := p_person_type_id;
777 l_actual_termination_date := trunc(p_actual_termination_date);
778 l_effective_date := trunc(p_effective_date);
779 l_applicant_number := hr_api.g_varchar2;
780 --
781 hr_utility.set_location(l_proc, 20);
782 --
783 -- Validation in addition to Table Handlers
784 --
785 -- Check period of service and get business group details for validation.
786 --
787 hr_api.mandatory_arg_error
788 (p_api_name => l_proc
789 ,p_argument => 'period_of_service_id'
790 ,p_argument_value => p_period_of_service_id
791 );
792 --
793 hr_api.mandatory_arg_error
794 (p_api_name => l_proc
795 ,p_argument => 'actual_termination_date'
796 ,p_argument_value => l_actual_termination_date
797 );
798 --
799 -- Bug number 4900409 - validating actual termination date cannot be future date
800 -- if leaving reason is 'D' Deceased
801
802 hr_utility.set_location(' Check Actual termination date for Deceased Leaving Reason ' , 25);
803 hr_utility.set_location( ' p_leaving_reason ' ||p_leaving_reason , 25);
804 hr_utility.set_location( ' p_actual_termination_date' ||p_leaving_reason , 25);
805
806 IF p_leaving_reason = 'D' THEN
807 IF p_actual_termination_date > SYSDATE THEN
808
809 fnd_message.set_name('PER','PER_449766_NO_FUT_ACTUAL_TERM');
810 fnd_message.raise_error;
811
812 END IF;
813 END IF;
814
815 hr_utility.set_location(l_proc, 30);
816 --
817 open csr_get_derived_details;
818 fetch csr_get_derived_details
819 into l_business_group_id
820 , l_legislation_code
821 , l_person_id
822 , l_employee_number
823 , l_per_effective_start_date
824 , l_per_object_version_number
825 , l_system_person_type
826 , l_npw_number
827 , l_saved_atd
828 , l_saved_lspd;
829 --
830 if csr_get_derived_details%NOTFOUND
831 then
832 --
833 hr_utility.set_location(l_proc, 40);
834 --
835 close csr_get_derived_details;
836 --
837 hr_utility.set_message(801,'HR_6537_EMP_DATE_START'); --Bug 3929991.
838 hr_utility.raise_error;
839 end if;
840 --
841 close csr_get_derived_details;
842
843 -- 5465050 Start
844
845 -- chk in src bg for termination
846 for l_src_bg_rec in chk_pre_term_src_gold(l_business_group_id,l_person_id) LOOP
847 if (l_src_bg_rec.PERMANENT = 'N'
848 and l_src_bg_rec.status = 'ACTIVE')
849 -- Commented for bug 5607315
850 /* and (p_actual_termination_date between l_src_bg_rec.START_DATE
851 and nvl(l_src_bg_rec.END_DATE,hr_api.g_eot))) */
852 then
853
854 hr_utility.set_message(800, 'HR_449770_GLD_SRC_BG');
855 hr_utility.raise_error;
856
857 end if;
858 end LOOP;
859
860 -- for dest bg check
861
862 for l_dest_bg_rec in chk_pre_term_dest_gold(l_business_group_id,l_person_id) LOOP
863 if (l_dest_bg_rec.PERMANENT = 'N'
864 and l_dest_bg_rec.status = 'ACTIVE')
865 -- Commented for bug 5607315
866 /* and (p_actual_termination_date between l_dest_bg_rec.START_DATE
867 and nvl(l_dest_bg_rec.END_DATE,hr_api.g_eot))) */
868 then
869
870 hr_utility.set_message(800, 'HR_449771_GLD_DEST_BG');
871 hr_utility.raise_error;
872
873 end if;
874 end LOOP;
875
876 -- 5465050 End
877
878
879 --
880 -- 120.2 (START)
881 --
882 if p_atd_new = 1 then
883 l_saved_atd := null;
884 end if;
885 if p_lspd_new = 1 then
886 l_saved_lspd := null;
887 end if;
888 --
889 -- 120.2 (END)
890 --
891 --
892 -- Start of API User Hook for the before hook of actual_termination
893 --
894 begin
898 ,p_object_version_number => p_object_version_number
895 hr_ex_employee_bk1.actual_termination_emp_b
896 (p_effective_date => l_effective_date
897 ,p_period_of_service_id => p_period_of_service_id
899 ,p_actual_termination_date => l_actual_termination_date
900 ,p_last_standard_process_date => l_last_standard_process_date
901 ,p_person_type_id => p_person_type_id
902 ,p_assignment_status_type_id => p_assignment_status_type_id
903 ,p_business_group_id => l_business_group_id
904 ,p_attribute_category => p_attribute_category
905 ,p_attribute1 => p_attribute1
906 ,p_attribute2 => p_attribute2
907 ,p_attribute3 => p_attribute3
908 ,p_attribute4 => p_attribute4
909 ,p_attribute5 => p_attribute5
910 ,p_attribute6 => p_attribute6
911 ,p_attribute7 => p_attribute7
912 ,p_attribute8 => p_attribute8
913 ,p_attribute9 => p_attribute9
914 ,p_attribute10 => p_attribute10
915 ,p_attribute11 => p_attribute11
916 ,p_attribute12 => p_attribute12
917 ,p_attribute13 => p_attribute13
918 ,p_attribute14 => p_attribute14
919 ,p_attribute15 => p_attribute15
920 ,p_attribute16 => p_attribute16
921 ,p_attribute17 => p_attribute17
922 ,p_attribute18 => p_attribute18
923 ,p_attribute19 => p_attribute19
924 ,p_attribute20 => p_attribute20
925 ,p_pds_information_category => p_pds_information_category
926 ,p_pds_information1 => p_pds_information1
927 ,p_pds_information2 => p_pds_information2
928 ,p_pds_information3 => p_pds_information3
929 ,p_pds_information4 => p_pds_information4
930 ,p_pds_information5 => p_pds_information5
931 ,p_pds_information6 => p_pds_information6
932 ,p_pds_information7 => p_pds_information7
933 ,p_pds_information8 => p_pds_information8
934 ,p_pds_information9 => p_pds_information9
935 ,p_pds_information10 => p_pds_information10
936 ,p_pds_information11 => p_pds_information11
937 ,p_pds_information12 => p_pds_information12
938 ,p_pds_information13 => p_pds_information13
939 ,p_pds_information14 => p_pds_information14
940 ,p_pds_information15 => p_pds_information15
941 ,p_pds_information16 => p_pds_information16
942 ,p_pds_information17 => p_pds_information17
943 ,p_pds_information18 => p_pds_information18
944 ,p_pds_information19 => p_pds_information19
945 ,p_pds_information20 => p_pds_information20
946 ,p_pds_information21 => p_pds_information21
947 ,p_pds_information22 => p_pds_information22
948 ,p_pds_information23 => p_pds_information23
949 ,p_pds_information24 => p_pds_information24
950 ,p_pds_information25 => p_pds_information25
951 ,p_pds_information26 => p_pds_information26
952 ,p_pds_information27 => p_pds_information27
953 ,p_pds_information28 => p_pds_information28
954 ,p_pds_information29 => p_pds_information29
955 ,p_pds_information30 => p_pds_information30
956 );
957 exception
958 when hr_api.cannot_find_prog_unit then
959 hr_api.cannot_find_prog_unit_error
960 (p_module_name => 'ACTUAL_TERMINATION_EMP',
961 p_hook_type => 'BP'
962 );
963 end;
964 --
965 -- End of API User Hook for the before hook of actual_termination
966 --
967 hr_utility.set_location(l_proc, 50);
968 hr_utility.set_location('Saved ATD: '||l_saved_atd, 51);
969 hr_utility.set_location('Saved LSPD: '||l_saved_lspd, 51);
970 hr_utility.set_location('Passed LSPD: '||l_last_standard_process_date, 51);
971 --
972 -- Determine if we are setting LSPD as a separate call to the API. i.e. ATD
973 -- has already been saved with a null LSPD,
974 --
975 if l_saved_atd is not null and
976 l_saved_lspd is null and
977 l_last_standard_process_date is not null then
978 --
979 -- We are processing a save to LSPD as a separate call to this API.
980 --
981 if p_last_standard_process_date = hr_api.g_date
982 then
983 hr_utility.set_location(l_proc, 60);
984 --
985 -- Last standard process date is the default value i.e.
986 -- it was not passed to the API => derive it.
987 --
988 -- Find the max tpe end date of any payrolls that are assigned.
989 --
990 open csr_get_max_tpe_end_date;
991 fetch csr_get_max_tpe_end_date
992 into l_max_tpe_end_date;
993 --
994 if csr_get_max_tpe_end_date%NOTFOUND
995 then
996 --
997 hr_utility.set_location(l_proc, 70);
998 --
999 close csr_get_max_tpe_end_date;
1003 --
1000 --
1001 -- As the cursor should always return at least a null value, this
1002 -- should never happen!
1004 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1005 hr_utility.set_message_token('PROCEDURE', l_proc);
1006 hr_utility.set_message_token('STEP','175');
1007 hr_utility.raise_error;
1008 end if;
1009 --
1010 close csr_get_max_tpe_end_date;
1011 --
1012 hr_utility.set_location(l_proc, 80);
1013 --
1014 if l_max_tpe_end_date is not null
1015 then
1016 --
1017 hr_utility.set_location(l_proc, 90);
1018 --
1019 -- A time period end date has been found, so set the last standard
1020 -- process date to that.
1021 --
1022 l_last_standard_process_date := l_max_tpe_end_date;
1023 else
1024 --
1025 hr_utility.set_location(l_proc, 100);
1026 --
1027 -- Either there was not an assignment assigned to a payroll, or
1028 -- there was no time period for that payroll as of the actual
1029 -- termination date. It doesn't matter which as we will default
1030 -- the LSPD to the ATD.
1031 --
1032 l_last_standard_process_date := l_actual_termination_date;
1033 end if;
1034
1035 end if;
1036 --
1037 -- Save PDS update
1038 --
1039 per_pds_upd.upd
1040 (p_period_of_service_id => p_period_of_service_id
1041 ,p_last_standard_process_date => l_last_standard_process_date
1042 ,p_object_version_number => l_pds_object_version_number
1043 ,p_effective_date => l_last_standard_process_date + 1
1044 ,p_validate => p_validate
1045 ,p_attribute_category => p_attribute_category
1046 ,p_attribute1 => p_attribute1
1047 ,p_attribute2 => p_attribute2
1048 ,p_attribute3 => p_attribute3
1049 ,p_attribute4 => p_attribute4
1050 ,p_attribute5 => p_attribute5
1051 ,p_attribute6 => p_attribute6
1052 ,p_attribute7 => p_attribute7
1053 ,p_attribute8 => p_attribute8
1054 ,p_attribute9 => p_attribute9
1055 ,p_attribute10 => p_attribute10
1056 ,p_attribute11 => p_attribute11
1057 ,p_attribute12 => p_attribute12
1058 ,p_attribute13 => p_attribute13
1059 ,p_attribute14 => p_attribute14
1060 ,p_attribute15 => p_attribute15
1061 ,p_attribute16 => p_attribute16
1062 ,p_attribute17 => p_attribute17
1063 ,p_attribute18 => p_attribute18
1064 ,p_attribute19 => p_attribute19
1065 ,p_attribute20 => p_attribute20
1066 ,p_pds_information_category => p_pds_information_category
1067 ,p_pds_information1 => p_pds_information1
1068 ,p_pds_information2 => p_pds_information2
1069 ,p_pds_information3 => p_pds_information3
1070 ,p_pds_information4 => p_pds_information4
1071 ,p_pds_information5 => p_pds_information5
1072 ,p_pds_information6 => p_pds_information6
1073 ,p_pds_information7 => p_pds_information7
1074 ,p_pds_information8 => p_pds_information8
1075 ,p_pds_information9 => p_pds_information9
1076 ,p_pds_information10 => p_pds_information10
1077 ,p_pds_information11 => p_pds_information11
1078 ,p_pds_information12 => p_pds_information12
1079 ,p_pds_information13 => p_pds_information13
1080 ,p_pds_information14 => p_pds_information14
1081 ,p_pds_information15 => p_pds_information15
1082 ,p_pds_information16 => p_pds_information16
1083 ,p_pds_information17 => p_pds_information17
1084 ,p_pds_information18 => p_pds_information18
1085 ,p_pds_information19 => p_pds_information19
1086 ,p_pds_information20 => p_pds_information20
1087 ,p_pds_information21 => p_pds_information21
1088 ,p_pds_information22 => p_pds_information22
1089 ,p_pds_information23 => p_pds_information23
1090 ,p_pds_information24 => p_pds_information24
1091 ,p_pds_information25 => p_pds_information25
1092 ,p_pds_information26 => p_pds_information26
1093 ,p_pds_information27 => p_pds_information27
1094 ,p_pds_information28 => p_pds_information28
1095 ,p_pds_information29 => p_pds_information29
1096 ,p_pds_information30 => p_pds_information30
1097 );
1098 --
1099 -- Maintain EEs for each assignment.
1100 --
1101 for csr_rec in csr_get_asgs_to_terminate
1102 loop
1103
1104 hrempter.terminate_entries_and_alus
1105 (p_assignment_id => csr_rec.assignment_id
1106 ,p_actual_term_date => l_saved_atd
1107 ,p_last_standard_date => l_last_standard_process_date
1108 ,p_final_process_date => null
1112 -- 120.2 (START)
1109 ,p_legislation_code => l_legislation_code
1110 ,p_entries_changed_warning => l_cr_entries_changed_warn
1111 --
1113 --
1114 ,p_alu_change_warning => l_alu_change_warning
1115 --
1116 -- 120.2 (END)
1117 --
1118 );
1119 --
1120 -- Set entries changed warning using the precedence of 'S', then 'Y', then
1121 -- 'N'.
1122 --
1123 if l_cr_entries_changed_warn = 'S' or
1124 l_entries_changed_warning = 'S' then
1125 --
1126 hr_utility.set_location(l_proc, 110);
1127 --
1128 l_entries_changed_warning := 'S';
1129 --
1130 elsif l_cr_entries_changed_warn = 'Y' or
1131 l_entries_changed_warning = 'Y' then
1132 --
1133 hr_utility.set_location(l_proc, 120);
1134 --
1135 l_entries_changed_warning := 'Y';
1136
1137 else
1138 --
1139 hr_utility.set_location(l_proc, 130);
1140 --
1141 l_entries_changed_warning := 'N';
1142
1143 end if;
1144
1145 end loop;
1146
1147 else
1148
1149 -- The saved ATD is null therefore this is the first call to this
1150 -- API for termination so process a full termination.
1151 --
1152 -- Check that the corresponding person is of 'employee' system person type.
1153 --
1154 if l_system_person_type <> 'EMP' and
1155 l_system_person_type <> 'EMP_APL'
1156 then
1157 --
1158 hr_utility.set_location(l_proc, 140);
1159 --
1160 hr_utility.set_message(801,'HR_51005_ASG_INV_PER_TYPE');
1161 hr_utility.raise_error;
1162 end if;
1163 --
1164 -- PTU changes: must maintain default of correct "ex-emp" type on
1165 -- per_all_people_f
1166 --
1167 if l_system_person_type = 'EMP' then
1168 l_system_person_type1 := 'EX_EMP';
1169 elsif l_system_person_type = 'EMP_APL' then
1170 l_system_person_type1 := 'EX_EMP_APL';
1171 end if;
1172 --
1173 l_person_type_id1 := hr_person_type_usage_info.get_default_person_type_id
1174 (l_business_group_id,
1175 l_system_person_type1);
1176 --
1177 -- PTU : End of Changes
1178 --
1179 hr_utility.set_location(l_proc, 150);
1180 --
1181 -- Check that there are not any future changes to the person.
1182 --
1183 open csr_future_per_changes;
1184 fetch csr_future_per_changes
1185 into l_exists;
1186 --
1187 if csr_future_per_changes%FOUND
1188 then
1189 --
1190 hr_utility.set_location(l_proc, 160);
1191 --
1192 close csr_future_per_changes;
1193 --
1194 hr_utility.set_message(801,'HR_7957_PDS_INV_ATT_FUTURE');
1195 hr_utility.raise_error;
1196 end if;
1197 --
1198 hr_utility.set_location(l_proc, 170);
1199 --
1200 close csr_future_per_changes;
1201 --
1202 -- Process Logic
1203 --
1204 -- PTU changes: person_type_id must be of 'EX_EMP' so validate or derive the default
1205 --
1206 per_per_bus.chk_person_type
1207 (p_person_type_id => l_person_type_id
1208 ,p_business_group_id => l_business_group_id
1209 ,p_expected_sys_type => 'EX_EMP'
1210 );
1211 --
1212 hr_utility.set_location(l_proc, 180);
1213 --
1214 -- If p_assignment_status_type_id is g_number then derive it's default value,
1215 -- otherwise validate it.
1216 --
1217 hr_utility.set_location(l_proc||' asg stat type : '||l_assignment_status_type_id||' per bg id '||l_business_group_id||' leg code '||l_legislation_code,900);
1218 --
1219 per_asg_bus1.chk_assignment_status_type
1220 (p_assignment_status_type_id => l_assignment_status_type_id
1221 ,p_business_group_id => l_business_group_id
1222 ,p_legislation_code => l_legislation_code
1223 ,p_expected_system_status => 'TERM_ASSIGN'
1224 );
1225 --
1226 hr_utility.set_location(l_proc, 190);
1227 --
1228 -- Validate/derive the last standard process date.
1229 --
1230 --
1231 hr_utility.set_location(l_proc, 200);
1232 --
1233 -- At the end of the following code we will either have a valid date
1234 -- for l_last_standard_process_date or it will be NULL. It can only be
1235 -- NULL if explicitly passed as such to the API.
1236 --
1237 if l_last_standard_process_date is not null and
1238 l_last_standard_process_date <> hr_api.g_date
1239 then
1240 --
1241 hr_utility.set_location(l_proc, 210);
1242 --
1243 -- Check that the last standard process date is on or after the actual
1244 -- termination date.
1245 --
1246 if not l_last_standard_process_date >= l_actual_termination_date
1247 then
1248 --
1249 hr_utility.set_location(l_proc, 220);
1250 --
1251 hr_utility.set_message(801,'HR_7505_PDS_INV_LSP_ATT_DT');
1252 hr_utility.raise_error;
1253 end if;
1257 --
1254 elsif l_last_standard_process_date = hr_api.g_date then
1255 --
1256 hr_utility.set_location(l_proc, 230);
1258 -- Last standard process date is the default value i.e.
1259 -- it was not passed to the API => derive it.
1260 --
1261 -- Find the max tpe end date of any payrolls that are assigned.
1262 --
1263 open csr_get_max_tpe_end_date;
1264 fetch csr_get_max_tpe_end_date
1265 into l_max_tpe_end_date;
1266 --
1267 if csr_get_max_tpe_end_date%NOTFOUND
1268 then
1269 --
1270 hr_utility.set_location(l_proc, 240);
1271 --
1272 close csr_get_max_tpe_end_date;
1273 --
1274 -- As the cursor should always return at least a null value, this
1275 -- should never happen!
1276 --
1277 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1278 hr_utility.set_message_token('PROCEDURE', l_proc);
1279 hr_utility.set_message_token('STEP','175');
1280 hr_utility.raise_error;
1281 end if;
1282 --
1283 close csr_get_max_tpe_end_date;
1284 --
1285 hr_utility.set_location(l_proc, 250);
1286 --
1287 if l_max_tpe_end_date is not null
1288 then
1289 --
1290 hr_utility.set_location(l_proc, 260);
1291 --
1292 -- A time period end date has been found, so set the last standard
1293 -- process date to that.
1294 --
1295 l_last_standard_process_date := l_max_tpe_end_date;
1296 else
1297 --
1298 hr_utility.set_location(l_proc, 270);
1299 --
1300 -- Either there was not an assignment assigned to a payroll, or
1301 -- there was no time period for that payroll as of the actual
1302 -- termination date. It doesn't matter which as we will default
1303 -- the LSPD to the ATD.
1304 --
1305 l_last_standard_process_date := l_actual_termination_date;
1306 end if;
1307 end if;
1308 --
1309 --2478758 implement check for payroll actions
1310 --
1311 l_action_chk := hr_ex_employee_api.check_for_compl_actions
1312 (p_person_id => l_person_id
1313 ,p_act_date => l_actual_termination_date
1314 ,p_lsp_date => l_last_standard_process_date
1315 ,p_fpr_date => null --not known to this API
1316 );
1317 --
1318 -- Bug# 2958006 Start Here
1319 -- Description : Added warning message for terminating an employee
1320 -- with future payroll actions exists before final processing date
1321 -- Bug# 3086210 Modified the Warning message.
1322 --
1323 --
1324 IF l_action_chk = 'W' THEN
1325 hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR'); -- Modified from PER_289973 TO PER_449053
1326 hr_utility.set_warning;
1327 END IF;
1328 --
1329 --
1330 --Bug# 2958006 End Here
1331 --
1332 if l_action_chk = 'Y' then
1333 hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
1334 hr_utility.raise_error;
1335 end if;
1336 --
1337 hr_utility.set_location(l_proc, 280);
1338 --
1339 -- Lock the person record in PER_PEOPLE_F ready for UPDATE at a later point.
1340 -- (Note: This is necessary because calling the table handlers in locking
1341 -- ladder order invokes an error in per_pds_upd.upd due to the person
1342 -- being modified by the per_per_upd.upd table handler.)
1343 --
1344 l_datetrack_mode := 'UPDATE';
1345 --
1346 per_per_shd.lck
1347 (p_effective_date => l_actual_termination_date + 1
1348 ,p_datetrack_mode => l_datetrack_mode
1349 ,p_person_id => l_person_id
1350 ,p_object_version_number => l_per_object_version_number
1351 ,p_validation_start_date => l_validation_start_date
1352 ,p_validation_end_date => l_validation_end_date
1353 );
1354
1355 hr_utility.set_location(l_proc, 290);
1356 --
1357 -- Update actual termination date and last standard process date in
1358 -- periods of service table.
1359
1360 per_pds_upd.upd
1361 (p_period_of_service_id => p_period_of_service_id
1362 ,p_actual_termination_date => l_actual_termination_date
1363 ,p_last_standard_process_date => l_last_standard_process_date
1364 ,p_leaving_reason => p_leaving_reason
1365 ,p_object_version_number => l_pds_object_version_number
1366 ,p_effective_date => p_actual_termination_date + 1
1367 ,p_attribute_category => p_attribute_category
1368 ,p_attribute1 => p_attribute1
1369 ,p_attribute2 => p_attribute2
1370 ,p_attribute3 => p_attribute3
1371 ,p_attribute4 => p_attribute4
1372 ,p_attribute5 => p_attribute5
1373 ,p_attribute6 => p_attribute6
1374 ,p_attribute7 => p_attribute7
1375 ,p_attribute8 => p_attribute8
1376 ,p_attribute9 => p_attribute9
1377 ,p_attribute10 => p_attribute10
1378 ,p_attribute11 => p_attribute11
1382 ,p_attribute15 => p_attribute15
1379 ,p_attribute12 => p_attribute12
1380 ,p_attribute13 => p_attribute13
1381 ,p_attribute14 => p_attribute14
1383 ,p_attribute16 => p_attribute16
1384 ,p_attribute17 => p_attribute17
1385 ,p_attribute18 => p_attribute18
1386 ,p_attribute19 => p_attribute19
1387 ,p_attribute20 => p_attribute20
1388 ,p_pds_information_category => p_pds_information_category
1389 ,p_pds_information1 => p_pds_information1
1390 ,p_pds_information2 => p_pds_information2
1391 ,p_pds_information3 => p_pds_information3
1392 ,p_pds_information4 => p_pds_information4
1393 ,p_pds_information5 => p_pds_information5
1394 ,p_pds_information6 => p_pds_information6
1395 ,p_pds_information7 => p_pds_information7
1396 ,p_pds_information8 => p_pds_information8
1397 ,p_pds_information9 => p_pds_information9
1398 ,p_pds_information10 => p_pds_information10
1399 ,p_pds_information11 => p_pds_information11
1400 ,p_pds_information12 => p_pds_information12
1401 ,p_pds_information13 => p_pds_information13
1402 ,p_pds_information14 => p_pds_information14
1403 ,p_pds_information15 => p_pds_information15
1404 ,p_pds_information16 => p_pds_information16
1405 ,p_pds_information17 => p_pds_information17
1406 ,p_pds_information18 => p_pds_information18
1407 ,p_pds_information19 => p_pds_information19
1408 ,p_pds_information20 => p_pds_information20
1409 ,p_pds_information21 => p_pds_information21
1410 ,p_pds_information22 => p_pds_information22
1411 ,p_pds_information23 => p_pds_information23
1412 ,p_pds_information24 => p_pds_information24
1413 ,p_pds_information25 => p_pds_information25
1414 ,p_pds_information26 => p_pds_information26
1415 ,p_pds_information27 => p_pds_information27
1416 ,p_pds_information28 => p_pds_information28
1417 ,p_pds_information29 => p_pds_information29
1418 ,p_pds_information30 => p_pds_information30
1419 ,p_validate => p_validate
1420 );
1421 --
1422 if p_leaving_reason = 'D' then
1423 open csr_date_of_death;
1424 fetch csr_date_of_death into l_current_dod;
1425 if l_current_dod is null then
1426 l_date_of_death := p_actual_termination_date;
1427 l_dod_warning := TRUE;
1428 else
1429 l_date_of_death := l_current_dod;
1430 end if;
1431 close csr_date_of_death;
1432 end if;
1433 --
1434 if l_dod_warning = TRUE then
1435 hr_utility.set_location(l_proc, 300);
1436 else
1437 hr_utility.set_location(l_proc,310);
1438 end if;
1439 --
1440 -- Update person type in person table.
1441 --
1442 hr_utility.set_location(l_proc, 320);
1443 per_per_upd.upd
1444 (p_person_id => l_person_id
1445 ,p_effective_start_date => l_effective_start_date
1446 ,p_effective_end_date => l_effective_end_date
1447 ,p_person_type_id => l_person_type_id1
1448 ,p_comment_id => l_comment_id
1449 ,p_current_applicant_flag => l_current_applicant_flag
1450 ,p_current_emp_or_apl_flag => l_current_emp_or_apl_flag
1451 ,p_current_employee_flag => l_current_employee_flag
1452 ,p_employee_number => l_employee_number
1453 ,p_applicant_number => l_applicant_number
1454 ,p_full_name => l_full_name
1455 ,p_object_version_number => l_per_object_version_number
1456 ,p_effective_date => l_actual_termination_date + 1
1457 ,p_datetrack_mode => 'UPDATE'
1458 ,p_date_of_death => l_date_of_death
1459 ,p_validate => p_validate
1460 ,p_name_combination_warning => l_name_combination_warning
1461 ,p_dob_null_warning => l_dob_null_warning
1462 ,p_orig_hire_warning => l_orig_hire_warning
1463 ,p_npw_number => l_npw_number
1464 );
1465 --
1466 hr_utility.set_location(l_proc, 330);
1467 --
1468 -- Fix for 4371218 starts here (Terminate contracts)
1469 --
1470 /* for ctc_rec in l_ctc_cur
1471 loop
1472 l_ctc_ovn := ctc_rec.object_version_number;
1473 --
1474 select hrl.lookup_code into l_ctc_status
1475 from hr_lookups hrl
1476 where hrl.lookup_type = 'CONTRACT_STATUS'
1477 and hrl.lookup_code = 'T-TERMINATION'
1478 and hrl.application_id = 800 AND hrl.enabled_flag = 'Y';
1479 --
1480 per_ctc_upd.upd
1481 (p_contract_id => ctc_rec.contract_id
1482 ,p_reference => ctc_rec.reference
1483 ,p_type => ctc_rec.type
1484 ,p_status => l_ctc_status --ctc_rec.status
1485 ,p_effective_start_date => l_effective_start_date
1486 ,p_effective_end_date => l_effective_end_date
1487 ,p_effective_date => l_actual_termination_date + 1
1488 ,p_object_version_number => l_ctc_ovn
1489 ,p_datetrack_mode => 'UPDATE'
1490 );
1491 end loop;*/
1492 hr_utility.set_location(l_proc, 335);
1496 --
1493 --
1494 -- end of contracts termination
1495 --
1497 -- Fix for 4371218 ens here (Terminate contracts)
1498 --
1499 -- Terminate the assignments, ensuring that the non-primaries are
1500 -- processed before the primary (implemented via 'order by primary_flag'
1501 -- clause in cursor declaration).
1502 --
1503 for csr_rec in csr_get_asgs_to_terminate
1504 loop
1505 --
1506 hr_utility.set_location(l_proc, 340);
1507 --
1508 hr_assignment_internal.actual_term_emp_asg_sup
1509 (p_assignment_id => csr_rec.assignment_id
1510 ,p_object_version_number => csr_rec.object_version_number
1511 ,p_actual_termination_date => l_actual_termination_date
1512 ,p_last_standard_process_date => l_last_standard_process_date
1513 ,p_assignment_status_type_id => l_assignment_status_type_id
1514 ,p_effective_start_date => l_effective_start_date
1515 ,p_effective_end_date => l_effective_end_date
1516 ,p_asg_future_changes_warning => l_cr_asg_future_changes_warn
1517 ,p_entries_changed_warning => l_cr_entries_changed_warn
1518 ,p_pay_proposal_warning => l_pay_proposal_warn
1519 --
1520 -- 120.2 (START)
1521 --
1522 ,p_alu_change_warning => l_alu_change_warning
1523 --
1524 -- 120.2 (END)
1525 --
1526 );
1527 --
1528 hr_utility.set_location(l_proc, 350);
1529 --
1530 -- Set entries changed warning using the precedence of 'S', then 'Y', then
1531 -- 'N'.
1532 --
1533 if l_cr_entries_changed_warn = 'S' or
1534 l_entries_changed_warning = 'S' then
1535 --
1536 hr_utility.set_location(l_proc, 360);
1537 --
1538 l_entries_changed_warning := 'S';
1539 --
1540 elsif l_cr_entries_changed_warn = 'Y' or
1541 l_entries_changed_warning = 'Y' then
1542 --
1543 hr_utility.set_location(l_proc, 370);
1544 --
1545 l_entries_changed_warning := 'Y';
1546
1547 else
1548 --
1549 hr_utility.set_location(l_proc, 380);
1550 --
1551 l_entries_changed_warning := 'N';
1552
1553 end if;
1554 --
1555 hr_utility.set_location(l_proc, 390);
1556 --
1557 -- Set future changes warning.
1558 --
1559 if l_cr_asg_future_changes_warn or l_asg_future_changes_warning
1560 then
1561 --
1562 hr_utility.set_location(l_proc, 400);
1563 --
1564 l_asg_future_changes_warning := TRUE;
1565
1566 end if;
1567
1568 end loop;
1569 --
1570 hr_utility.set_location(l_proc, 410);
1571 --
1572 -- Added code to support the following Out warning parameters.
1573 --
1574 l_status := 'SUPERVISOR';
1575 pre_term_check(l_status,
1576 l_business_group_id,
1577 l_person_id,
1578 l_actual_termination_date);
1579 if l_status = 'WARNING' then
1580 p_supervisor_warning := TRUE;
1581 else
1582 p_supervisor_warning := FALSE;
1583 end if;
1584 --
1585 l_status := 'EVENT';
1586 pre_term_check(l_status,
1587 l_business_group_id,
1588 l_person_id,
1589 l_actual_termination_date);
1590 if l_status = 'WARNING' then
1591 p_event_warning := TRUE;
1592 else
1593 p_event_warning := FALSE;
1594 end if;
1595 --
1596 l_status := 'INTERVIEW';
1597 pre_term_check(l_status,
1598 l_business_group_id,
1599 l_person_id,
1600 l_actual_termination_date);
1601 if l_status = 'WARNING' then
1602 p_interview_warning := TRUE;
1603 else
1604 p_interview_warning := FALSE;
1605 end if;
1606 --
1607 l_status := 'REVIEW';
1608 pre_term_check(l_status,
1609 l_business_group_id,
1610 l_person_id,
1611 l_actual_termination_date);
1612 if l_status = 'WARNING' then
1613 p_review_warning := TRUE;
1614 else
1615 p_review_warning := FALSE;
1616 end if;
1617 --
1618 l_status := 'RECRUITER';
1619 pre_term_check(l_status,
1620 l_business_group_id,
1621 l_person_id,
1622 l_actual_termination_date);
1623 if l_status = 'WARNING' then
1624 p_recruiter_warning := TRUE;
1625 else
1626 p_recruiter_warning := FALSE;
1627 end if;
1628 --
1629 --
1630 -- Now maintain any PTU records if they exists. We are terminating
1631 -- possibly with a leaving_reason of 'R' so pass required information
1632 -- to maintain_ptu procedure
1633 --
1634
1635 -- PTU : Following Code has been added (l_person_type_id holds validated flavour of EX_EMP)
1636
1637 hr_per_type_usage_internal.maintain_person_type_usage
1638 (p_effective_date => p_actual_termination_date +1
1639 ,p_person_id => l_person_id
1643 --
1640 ,p_person_type_id => l_person_type_id
1641 ,p_datetrack_update_mode => 'UPDATE'
1642 );
1644 if p_leaving_reason = 'R'
1645 then
1646
1647 hr_utility.set_location('actual_termination_emp',420);
1648
1649 hr_per_type_usage_internal.create_person_type_usage
1650 (p_person_id => l_person_id
1651 ,p_person_type_id =>
1652 hr_person_type_usage_info.get_default_person_type_id
1653 (p_business_group_id => l_business_group_id
1654 ,p_system_person_type => 'RETIREE')
1655 ,p_effective_date => p_actual_termination_date+1
1656 ,p_person_type_usage_id => l_person_type_usage_id
1657 ,p_object_version_number=> l_ptu_object_version_number
1658 ,p_effective_start_date => l_effective_start_date
1659 ,p_effective_end_date => l_effective_end_date);
1660
1661 hr_utility.set_location('actual_termination_emp',430);
1662
1663 end if;
1664
1665 end if;
1666 --
1667 -- Fix for bug 3829474 starts here.
1668 --
1669 hr_utility.set_location('actual_termination_emp',435);
1670 --
1671
1672 /* Bug 5504659
1673 open l_pds_cur;
1674 fetch l_pds_cur into l_pds_rec;
1675 close l_pds_cur;
1676 per_hrwf_synch.per_pds_wf(
1677 p_rec => l_pds_rec,
1678 p_date => p_actual_termination_date,
1679 p_action => 'TERMINATION');
1680 Note : added p_date_start to test, earlier code does not work*/
1681
1682 --
1683 hr_utility.set_location('actual_termination_emp',440);
1684 --
1685 -- Fix for bug 3829474 ends here.
1686 --
1687 -- Start of API User Hook for the after hook of actual_termination
1688 -- Local vars are passed in for all OUT parms because the hook needs to
1689 -- be placed before the validate check and therefore before the code that
1690 -- sets all out parms.
1691 --
1692 begin
1693 hr_ex_employee_bk1.actual_termination_emp_a
1694 (p_effective_date => l_effective_date
1695 ,p_period_of_service_id => p_period_of_service_id
1696 ,p_object_version_number => l_pds_object_version_number
1697 ,p_actual_termination_date => l_actual_termination_date
1698 ,p_last_standard_process_date => l_last_std_process_date_in
1699 ,p_person_type_id => p_person_type_id
1700 ,p_assignment_status_type_id => p_assignment_status_type_id
1701 ,p_attribute_category => p_attribute_category
1702 ,p_attribute1 => p_attribute1
1703 ,p_attribute2 => p_attribute2
1704 ,p_attribute3 => p_attribute3
1705 ,p_attribute4 => p_attribute4
1706 ,p_attribute5 => p_attribute5
1707 ,p_attribute6 => p_attribute6
1708 ,p_attribute7 => p_attribute7
1709 ,p_attribute8 => p_attribute8
1710 ,p_attribute9 => p_attribute9
1711 ,p_attribute10 => p_attribute10
1712 ,p_attribute11 => p_attribute11
1713 ,p_attribute12 => p_attribute12
1714 ,p_attribute13 => p_attribute13
1715 ,p_attribute14 => p_attribute14
1716 ,p_attribute15 => p_attribute15
1717 ,p_attribute16 => p_attribute16
1718 ,p_attribute17 => p_attribute17
1719 ,p_attribute18 => p_attribute18
1720 ,p_attribute19 => p_attribute19
1721 ,p_attribute20 => p_attribute20
1722 ,p_pds_information_category => p_pds_information_category
1723 ,p_pds_information1 => p_pds_information1
1724 ,p_pds_information2 => p_pds_information2
1725 ,p_pds_information3 => p_pds_information3
1726 ,p_pds_information4 => p_pds_information4
1727 ,p_pds_information5 => p_pds_information5
1728 ,p_pds_information6 => p_pds_information6
1729 ,p_pds_information7 => p_pds_information7
1730 ,p_pds_information8 => p_pds_information8
1731 ,p_pds_information9 => p_pds_information9
1732 ,p_pds_information10 => p_pds_information10
1733 ,p_pds_information11 => p_pds_information11
1734 ,p_pds_information12 => p_pds_information12
1735 ,p_pds_information13 => p_pds_information13
1736 ,p_pds_information14 => p_pds_information14
1737 ,p_pds_information15 => p_pds_information15
1738 ,p_pds_information16 => p_pds_information16
1739 ,p_pds_information17 => p_pds_information17
1740 ,p_pds_information18 => p_pds_information18
1741 ,p_pds_information19 => p_pds_information19
1742 ,p_pds_information20 => p_pds_information20
1743 ,p_pds_information21 => p_pds_information21
1744 ,p_pds_information22 => p_pds_information22
1745 ,p_pds_information23 => p_pds_information23
1746 ,p_pds_information24 => p_pds_information24
1747 ,p_pds_information25 => p_pds_information25
1751 ,p_pds_information29 => p_pds_information29
1748 ,p_pds_information26 => p_pds_information26
1749 ,p_pds_information27 => p_pds_information27
1750 ,p_pds_information28 => p_pds_information28
1752 ,p_pds_information30 => p_pds_information30
1753 ,p_last_std_process_date_out => l_last_standard_process_date
1754 ,p_supervisor_warning => l_supervisor_warning
1755 ,p_event_warning => l_event_warning
1756 ,p_interview_warning => l_interview_warning
1757 ,p_review_warning => l_review_warning
1758 ,p_recruiter_warning => l_recruiter_warning
1759 ,p_asg_future_changes_warning => l_asg_future_changes_warning
1760 ,p_entries_changed_warning => l_entries_changed_warning
1761 ,p_pay_proposal_warning => l_pay_proposal_warn
1762 ,p_dod_warning => l_dod_warning
1763 ,p_business_group_id => l_business_group_id
1764 );
1765 exception
1766 when hr_api.cannot_find_prog_unit then
1767 hr_api.cannot_find_prog_unit_error
1768 (p_module_name => 'ACTUAL_TERMINATION_EMP',
1769 p_hook_type => 'AP'
1770 );
1771 --
1772 -- End of API User Hook for the after hook of actual_termination
1773 --
1774 end;
1775 --
1776 -- When in validation only mode raise the Validate_Enabled exception
1777 --
1778 if p_validate then
1779 raise hr_api.validate_enabled;
1780 end if;
1781 --
1782 -- Set all output arguments
1783 --
1784 p_asg_future_changes_warning := l_asg_future_changes_warning;
1785 p_entries_changed_warning := l_entries_changed_warning;
1786 p_pay_proposal_warning := l_pay_proposal_warn;
1787 p_dod_warning := l_dod_warning;
1788 --
1789 -- 120.2 (START)
1790 --
1791 p_alu_change_warning := l_alu_change_warning;
1792 --
1793 -- 120.2 (END)
1794 --
1795 p_last_std_process_date_out := l_last_standard_process_date;
1796 p_object_version_number := l_pds_object_version_number;
1797 --
1798 --
1799 hr_utility.set_location(' Leaving:'||l_proc, 440);
1800 exception
1801 when hr_api.validate_enabled then
1802 --
1803 -- As the Validate_Enabled exception has been raised
1804 -- we must rollback to the savepoint
1805 --
1806 ROLLBACK TO actual_termination_emp;
1807 --
1808 -- Only set output warning arguments
1809 -- (Any key or derived arguments must be set to null
1810 -- when validation only mode is being used.)
1811 --
1812 p_asg_future_changes_warning := l_asg_future_changes_warning;
1813 p_entries_changed_warning := l_entries_changed_warning;
1814 p_pay_proposal_warning := l_pay_proposal_warn;
1815 p_dod_warning := l_dod_warning;
1816 --
1817 -- p_object_version_number and p_last_standard_process_date
1818 -- should return their IN values, they still hold their IN values
1819 -- so do nothing here.
1820 --
1821 --
1822 when others then
1823 --
1824 -- A validation or unexpected error has occurred
1825 --
1826 -- Added as part of fix to bug 632479
1827 --
1828 ROLLBACK TO actual_termination_emp;
1829 --
1830 -- set in out parameters and set out parameters
1831 --
1832 p_object_version_number := l_ovn;
1833 p_last_std_process_date_out := null;
1834 p_supervisor_warning := false;
1835 p_event_warning := false;
1836 p_interview_warning := false;
1837 p_review_warning := false;
1838 p_recruiter_warning := false;
1839 p_asg_future_changes_warning := false;
1840 p_entries_changed_warning := null;
1841 p_pay_proposal_warning := false;
1842 p_dod_warning := false;
1843 raise;
1844 --
1845 -- End of fix.
1846 --
1847 end actual_termination_emp;
1848 --
1849 -- 70.2 change a end.
1850 --
1851 -- 70.2 change b start.
1852 --
1853 -- ----------------------------------------------------------------------------
1854 -- |-------------------------< final_process_emp >----------------------------|
1855 -- ----------------------------------------------------------------------------
1856 --
1857 procedure final_process_emp
1858 (p_validate in boolean default false
1859 ,p_period_of_service_id in number
1860 ,p_object_version_number in out nocopy number
1861 ,p_final_process_date in out nocopy date
1862 ,p_org_now_no_manager_warning out nocopy boolean
1863 ,p_asg_future_changes_warning out nocopy boolean
1864 ,p_entries_changed_warning out nocopy varchar2
1865 ) is
1866 --
1867 -- Declare cursors and local variables
1868 --
1869 -- Out variables
1870 --
1871 l_asg_future_changes_warning boolean := FALSE;
1872 l_entries_changed_warning varchar2(1) := 'N';
1873 l_final_process_date per_periods_of_service.final_process_date%TYPE;
1874 l_temp_date per_periods_of_service.final_process_date%TYPE := p_final_process_date;
1875 l_ovn per_periods_of_service.object_version_number%type := p_object_version_number;
1876 l_org_now_no_manager_warning boolean := FALSE;
1877 l_pds_object_version_number per_periods_of_service.object_version_number%TYPE;
1878 --
1882 l_cr_org_now_no_manager_warn boolean := FALSE;
1879 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
1880 l_cr_asg_future_changes_warn boolean := FALSE;
1881 l_cr_entries_changed_warn varchar2(1) := 'N';
1883 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1884 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1885 l_exists varchar2(1);
1886 l_last_standard_process_date per_periods_of_service.last_standard_process_date%TYPE;
1887 l_legislation_code per_business_groups.legislation_code%TYPE;
1888 l_object_version_number per_periods_of_service.object_version_number%TYPE;
1889 l_person_id per_all_people_f.person_id%TYPE;
1890 l_proc varchar2(72) := g_package || 'final_process_emp';
1891 l_exemppet_eff_date date;
1892 l_action_chk VARCHAR2(1) := 'N';
1893 --
1894 cursor csr_get_derived_details is
1895 select bus.legislation_code
1896 , pds.actual_termination_date
1897 , pds.last_standard_process_date
1898 , pds.person_id
1899 , pds.object_version_number
1900 from per_business_groups bus
1901 , per_periods_of_service pds
1902 where pds.period_of_service_id = p_period_of_service_id
1903 and bus.business_group_id = pds.business_group_id;
1904 --
1905 cursor csr_get_asgs_to_final_proc is
1906 select asg.assignment_id
1907 , asg.object_version_number
1908 , asg.primary_flag
1909 from per_all_assignments_f asg
1910 where asg.period_of_service_id = p_period_of_service_id
1911 and l_final_process_date between asg.effective_start_date
1912 and asg.effective_end_date
1913 and exists (
1914 select 'X'
1915 from per_all_assignments_f a1
1916 where asg.assignment_id = a1.assignment_id
1917 and l_final_process_date+1 between a1.effective_start_date
1918 and a1.effective_end_date)
1919 order by asg.primary_flag;
1920 --
1921 cursor csr_valid_ex_emp is
1922 select null
1923 from per_all_people_f per
1924 , per_person_type_usages_f ptu
1925 , per_person_types pet
1926 where per.person_id = l_person_id
1927 and l_exemppet_eff_date between per.effective_start_date
1928 and per.effective_end_date
1929 and per.person_id = ptu.person_id
1930 and l_exemppet_eff_date between ptu.effective_start_date
1931 and ptu.effective_end_date
1932 and pet.person_type_id = ptu.person_type_id
1933 and pet.system_person_type = 'EX_EMP';
1934 --
1935 -- Fix for bug 3829474 starts here.
1936 --
1937 l_pds_rec per_periods_of_service%rowtype;
1938 --
1939 cursor l_pds_cur is
1940 select *
1941 from per_periods_of_service
1942 where period_of_service_id = p_period_of_service_id;
1943 --
1944 -- Fix for bug 3829474 ends here.
1945 --
1946 begin
1947 hr_utility.set_location('Entering:'|| l_proc, 1);
1948 --
1949 -- l_pds_object_version_number := p_object_version_number;
1950 l_final_process_date := trunc(p_final_process_date);
1951 --
1952 -- Issue a savepoint.
1953 --
1954 savepoint final_process_emp;
1955 --
1956 -- Start of API User Hook for the before hook of final_process_emp.
1957 --
1958 begin
1959 hr_ex_employee_bk2.final_process_emp_b
1960 (p_period_of_service_id => p_period_of_service_id
1961 ,p_object_version_number => p_object_version_number
1962 ,p_final_process_date => l_final_process_date
1963 );
1964 exception
1965 when hr_api.cannot_find_prog_unit then
1966 hr_api.cannot_find_prog_unit_error
1967 (p_module_name => 'FINAL_PROCESS_EMP',
1968 p_hook_type => 'BP'
1969 );
1970 end;
1971 --
1972 hr_utility.set_location(l_proc, 10);
1973 --
1974 -- Validation in addition to Table Handlers
1975 --
1976 -- Check period of service.
1977 --
1978 hr_api.mandatory_arg_error
1979 (p_api_name => l_proc
1980 ,p_argument => 'period_of_service_id'
1981 ,p_argument_value => p_period_of_service_id
1982 );
1983 --
1984 -- Check Object version number.
1985 --
1986 hr_api.mandatory_arg_error
1987 (p_api_name => l_proc
1988 ,p_argument => 'object_version_number'
1989 ,p_argument_value => p_object_version_number
1990 );
1991 --
1992 hr_utility.set_location(l_proc, 20);
1993 --
1994 open csr_get_derived_details;
1995 fetch csr_get_derived_details
1996 into l_legislation_code
1997 ,l_actual_termination_date
1998 ,l_last_standard_process_date
1999 ,l_person_id
2000 ,l_object_version_number;
2001 --
2002 if csr_get_derived_details%NOTFOUND
2003 then
2004 --
2005 hr_utility.set_location(l_proc, 30);
2006 --
2007 close csr_get_derived_details;
2008 --
2009 hr_utility.set_message(801,'HR_7391_ASG_INV_PERIOD_OF_SERV');
2010 hr_utility.raise_error;
2011 end if;
2012 --
2013 close csr_get_derived_details;
2014 --
2018 then
2015 -- Validate the derived OVN with passed OVN.
2016
2017 if l_object_version_number <> p_object_version_number
2019
2020 hr_utility.set_message(801,'HR_7155_OBJECT_INVALID');
2021 hr_utility.raise_error;
2022 end if;
2023
2024 hr_utility.set_location(l_proc, 40);
2025 --
2026 -- Check that the actual termination date has already been set.
2027 --
2028 if l_actual_termination_date is null
2029 then
2030 --
2031 hr_utility.set_location(l_proc, 50);
2032 --
2033 hr_utility.set_message(801,'HR_51007_ASG_INV_NOT_ACT_TERM');
2034 hr_utility.raise_error;
2035 end if;
2036 --
2037 -- Check if the final process date is set
2038 --
2039 if l_legislation_code = 'US'
2040 and p_final_process_date is null
2041 then
2042 --
2043 -- Default the FPD to the LSPD
2044 --
2045 l_final_process_date := l_actual_termination_date;
2046 --
2047 -- Add one day to the last standard process date to get the
2048 -- validation date
2049 --
2050 -- Set the EX Employee effective date to the FPD + 1
2051 --
2052 -- Note: Since the FPD equals the ATD then the employee
2053 -- has not been Ex Employee for at least one day
2054 --
2055 l_exemppet_eff_date := l_final_process_date+1;
2056 --
2057 elsif p_final_process_date is null
2058 then
2059 --
2060 -- Default the FPD to the LSPD
2061 --
2062 l_final_process_date := l_last_standard_process_date;
2063 --
2064 -- Add one day to the last standard process date to get the
2065 -- validation date
2066 --
2067 -- Set the EX Employee effective date to the FPD + 1
2068 --
2069 -- Note: Since the FPD equals the LSPD then the employee
2070 -- has not been Ex Employee for at least one day
2071 --
2072 l_exemppet_eff_date := l_final_process_date+1;
2073 --
2074 elsif p_final_process_date = l_actual_termination_date then
2075 --
2076 l_final_process_date := p_final_process_date;
2077 --
2078 -- Set the EX Employee effective date to the FPD + 1
2079 --
2080 -- Note: Since the FPD equals the ATD then the employee
2081 -- has not been Ex Employee for at least one day
2082 --
2083 l_exemppet_eff_date := p_final_process_date+1;
2084 --
2085 else
2086 --
2087 l_final_process_date := p_final_process_date;
2088 --
2089 l_exemppet_eff_date := p_final_process_date;
2090 --
2091 end if;
2092 --
2093 hr_utility.set_location(l_proc, 90);
2094 --
2095 -- Check that the corresponding person is of EX_EMP system person type.
2096 --
2097 open csr_valid_ex_emp;
2098 fetch csr_valid_ex_emp
2099 into l_exists;
2100 --
2101 if csr_valid_ex_emp%NOTFOUND
2102 then
2103 --
2104 hr_utility.set_location(l_proc, 100);
2105 --
2106 close csr_valid_ex_emp;
2107 --
2108 hr_utility.set_message(801,'HR_51008_ASG_INV_EX_EMP_TYPE');
2109 hr_utility.raise_error;
2110 --
2111 end if;
2112 --
2113 hr_utility.set_location(l_proc, 110);
2114 --
2115 close csr_valid_ex_emp;
2116 --
2117 --2478758 implement check for payroll actions
2118 --
2119 l_action_chk := hr_ex_employee_api.check_for_compl_actions
2120 (p_person_id => l_person_id
2121 ,p_act_date => l_actual_termination_date
2122 ,p_lsp_date => l_last_standard_process_date
2123 ,p_fpr_date => l_final_process_date
2124 );
2125 --
2126 IF l_action_chk = 'N' THEN
2127 --if this check suceeds, erase any possible warning from ATD check.
2128 hr_utility.clear_warning;
2129 END IF;
2130 IF l_action_chk = 'W' THEN
2131 --
2132 -- Fix for bug 3100620 starts here. changed the warning message.
2133 --
2134 --hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2135 hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
2136 --
2137 -- Fix for bug 3100620 ends here.
2138 --
2139 hr_utility.set_warning;
2140 END IF;
2141 --
2142 if l_action_chk = 'Y' then
2143 hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
2144 hr_utility.raise_error;
2145 end if;
2146 --
2147 -- Check that there are no COBRA benefits after the final process date.
2148 --
2149 -- Not implemented yet due to outstanding issues.
2150 --
2151 -- Update final process date in periods of service table.
2152 --
2153 per_pds_upd.upd
2154 (p_period_of_service_id => p_period_of_service_id
2155 ,p_final_process_date => l_final_process_date
2156 ,p_object_version_number => l_object_version_number
2157 ,p_effective_date => l_final_process_date
2158 ,p_validate => p_validate
2159 );
2160
2161 --
2162 hr_utility.set_location(l_proc, 120);
2163 --
2164 -- Final process the assignments, ensuring that the non-primaries are
2165 -- processed before the primary (implemented via 'order by primary_flag'
2166 -- clause in cursor declaration).
2167 --
2168
2169 --
2170
2171
2172 for csr_rec in csr_get_asgs_to_final_proc
2173 loop
2174 --
2175 hr_utility.set_location(l_proc, 130);
2179 (p_assignment_id => csr_rec.assignment_id
2176 hr_utility.set_location('assignment_id '||to_char(csr_rec.assignment_id),131);
2177 --
2178 hr_assignment_internal.final_process_emp_asg_sup
2180 ,p_object_version_number => csr_rec.object_version_number
2181 ,p_actual_termination_date => l_actual_termination_date
2182 ,p_final_process_date => l_final_process_date
2183 ,p_effective_start_date => l_effective_start_date
2184 ,p_effective_end_date => l_effective_end_date
2185 ,p_org_now_no_manager_warning => l_cr_org_now_no_manager_warn
2186 ,p_asg_future_changes_warning => l_cr_asg_future_changes_warn
2187 ,p_entries_changed_warning => l_cr_entries_changed_warn
2188 );
2189 --
2190 hr_utility.set_location(l_proc, 140);
2191 --
2192 -- Set entries changed warning using the precedence of 'S', then 'Y', then
2193 -- 'N'.
2194 --
2195 if l_cr_entries_changed_warn = 'S'
2196 or l_entries_changed_warning = 'S'
2197 then
2198 --
2199 hr_utility.set_location(l_proc, 150);
2200 --
2201 l_entries_changed_warning := 'S';
2202 --
2203 elsif l_cr_entries_changed_warn = 'Y'
2204 or l_entries_changed_warning = 'Y'
2205 then
2206 --
2207 hr_utility.set_location(l_proc, 160);
2208 --
2209 l_entries_changed_warning := 'Y';
2210 --
2211 else
2212 --
2213 hr_utility.set_location(l_proc, 165);
2214 --
2215 l_entries_changed_warning := 'N';
2216 --
2217 end if;
2218 --
2219 hr_utility.set_location(l_proc, 170);
2220 --
2221 -- Set future changes warning.
2222 --
2223 if l_cr_asg_future_changes_warn
2224 then
2225 --
2226 hr_utility.set_location(l_proc, 180);
2227 --
2228 l_asg_future_changes_warning := TRUE;
2229 end if;
2230 --
2231 -- Set org now no manager warning.
2232 --
2233 if l_cr_org_now_no_manager_warn
2234 then
2235 --
2236 hr_utility.set_location(l_proc, 190);
2237 --
2238 l_org_now_no_manager_warning := TRUE;
2239 end if;
2240 end loop;
2241 --
2242 hr_utility.set_location(l_proc, 195);
2243 --
2244 -- Fix for bug 3829474 starts here.
2245 --
2246
2247 /* Bug 5504659
2248 open l_pds_cur;
2249 fetch l_pds_cur into l_pds_rec;
2250 close l_pds_cur;
2251 per_hrwf_synch.per_pds_wf(
2252 p_rec => l_pds_rec,
2253 p_date => l_final_process_date,
2254 p_action => 'TERMINATION');
2255 Note : added p_date_start to test, earlier code does not work*/
2256
2257
2258 --
2259 -- Fix for bug 3829474 ends here.
2260 --
2261 hr_utility.set_location(l_proc, 200);
2262 --
2263 -- Start of API User Hook for the after hook of final_process_emp.
2264 --
2265 begin
2266 hr_ex_employee_bk2.final_process_emp_a
2267 (p_period_of_service_id => p_period_of_service_id
2268 ,p_object_version_number => l_object_version_number
2269 ,p_final_process_date => l_final_process_date
2270 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2271 ,p_asg_future_changes_warning => l_asg_future_changes_warning
2272 ,p_entries_changed_warning => l_entries_changed_warning
2273 );
2274 exception
2275 when hr_api.cannot_find_prog_unit then
2276 hr_api.cannot_find_prog_unit_error
2277 (p_module_name => 'FINAL_PROCESS_EMP',
2278 p_hook_type => 'AP'
2279 );
2280 end;
2281 --
2282 -- End of API User Hook for the after hook of create_secondary_apl_asg.
2283 --
2284 --
2285 -- When in validation only mode raise the Validate_Enabled exception
2286 --
2287 if p_validate then
2288 raise hr_api.validate_enabled;
2289 end if;
2290 --
2291 -- Set all output arguments
2292 --
2293 p_final_process_date := l_final_process_date;
2294 p_asg_future_changes_warning := l_asg_future_changes_warning;
2295 p_entries_changed_warning := l_entries_changed_warning;
2296 p_object_version_number := l_object_version_number;
2297 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2298 --
2299 hr_utility.set_location(' Leaving:'||l_proc, 400);
2300 exception
2301 when hr_api.validate_enabled then
2302 --
2303 -- As the Validate_Enabled exception has been raised
2304 -- we must rollback to the savepoint
2305 --
2306 ROLLBACK TO final_process_emp;
2307 --
2308 -- Only set output warning arguments
2309 -- (Any key or derived arguments must be set to null
2310 -- when validation only mode is being used.)
2311 --
2312 p_asg_future_changes_warning := l_asg_future_changes_warning;
2313 p_entries_changed_warning := l_entries_changed_warning;
2314 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2315 --
2316 when others then
2317 --
2318 -- A validation or unexpected error has occurred
2319 --
2320 -- Added as part of fix to bug 632479
2321 --
2322 ROLLBACK TO final_process_emp;
2323 --
2327 p_final_process_date := l_temp_date;
2324 -- set in out parameters and set out parameters
2325 --
2326 p_object_version_number := l_ovn;
2328 p_org_now_no_manager_warning := false;
2329 p_asg_future_changes_warning := false;
2330 p_entries_changed_warning := null;
2331 raise;
2332 --
2333 -- End of fix.
2334 --
2335 end final_process_emp;
2336 --
2337 -- 70.2 change b end.
2338 --
2339 -- ----------------------------------------------------------------------------
2340 -- |-----------------------< update_term_details_emp >------------------------|
2341 -- ----------------------------------------------------------------------------
2342 --
2343 procedure update_term_details_emp
2344 (p_validate in boolean default false
2345 ,p_effective_date in date
2346 ,p_period_of_service_id in number
2347 ,p_object_version_number in out nocopy number
2348 ,p_termination_accepted_person in number default hr_api.g_number
2349 ,p_accepted_termination_date in date default hr_api.g_date
2350 ,p_comments in varchar2 default hr_api.g_varchar2
2351 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
2352 ,p_notified_termination_date in date default hr_api.g_date
2353 ,p_projected_termination_date in date default hr_api.g_date
2354 ) is
2355 --
2356 -- Declare cursors and local variables
2357 --
2358
2359 l_proc varchar2(72) := g_package||'update_term_details_emp';
2360 l_object_version_number
2361 number := p_object_version_number;
2362 l_ovn number := p_object_version_number;
2363 l_validate boolean := false;
2364 begin
2365 hr_utility.set_location('Entering:'|| l_proc, 5);
2366 --
2367 -- Issue a savepoint.
2368 --
2369 savepoint update_term_details_emp;
2370 hr_utility.set_location(l_proc, 6);
2371 --
2372 --
2373 -- Start of API User Hook for the before hook of final_process_emp.
2374 --
2375 begin
2376 --
2377 hr_ex_employee_bk3.update_term_details_emp_b
2378 (p_effective_date => p_effective_date
2379 ,p_period_of_service_id => p_period_of_service_id
2380 ,p_object_version_number => p_object_version_number
2381 ,p_termination_accepted_person => p_termination_accepted_person
2382 ,p_accepted_termination_date => p_accepted_termination_date
2383 ,p_comments => p_comments
2384 ,p_leaving_reason => p_leaving_reason
2385 ,p_notified_termination_date => p_notified_termination_date
2386 ,p_projected_termination_date => p_projected_termination_date
2387 );
2388 exception
2389 when hr_api.cannot_find_prog_unit then
2390 hr_api.cannot_find_prog_unit_error
2391 (p_module_name => 'UPDATE_TERM_DETAILS_EMP',
2392 p_hook_type => 'BP'
2393 );
2394 end;
2395 -- Process Logic
2396 --
2397 per_pds_upd.upd
2398 (p_period_of_service_id => p_period_of_service_id
2399 ,p_termination_accepted_person => p_termination_accepted_person
2400 ,p_accepted_termination_date => p_accepted_termination_date
2401 ,p_comments => p_comments
2402 ,p_leaving_reason => p_leaving_reason
2403 ,p_notified_termination_date => p_notified_termination_date
2404 ,p_projected_termination_date => p_projected_termination_date
2405 ,p_object_version_number => p_object_version_number
2406 ,p_effective_date => p_effective_date
2407 ,p_validate => l_validate
2408 );
2409 --
2410 hr_utility.set_location(l_proc, 7);
2411 --
2412 -- When in validation only mode raise the Validate_Enabled exception
2413 --
2414 --
2415 -- Start of API User Hook for the after hook of final_process_emp.
2416 --
2417 begin
2418 hr_ex_employee_bk3.update_term_details_emp_a
2419 (p_effective_date => p_effective_date
2420 ,p_period_of_service_id => p_period_of_service_id
2421 ,p_object_version_number => p_object_version_number
2422 ,p_termination_accepted_person => p_termination_accepted_person
2423 ,p_accepted_termination_date => p_accepted_termination_date
2424 ,p_comments => p_comments
2425 ,p_leaving_reason => p_leaving_reason
2426 ,p_notified_termination_date => p_notified_termination_date
2427 ,p_projected_termination_date => p_projected_termination_date
2428 );
2429 exception
2430 when hr_api.cannot_find_prog_unit then
2431 hr_api.cannot_find_prog_unit_error
2432 (p_module_name => 'UPDATE_TERM_DETAILS_EMP',
2433 p_hook_type => 'AP'
2434 );
2435 end;
2436 --
2437 if p_validate then
2438 raise hr_api.validate_enabled;
2439 end if;
2440 --
2441 hr_utility.set_location(' Leaving:'||l_proc, 8);
2442 --
2443 exception
2444 when hr_api.validate_enabled then
2445 --
2446 -- As the Validate_Enabled exception has been raised
2447 -- we must rollback to the savepoint
2448 --
2449 ROLLBACK TO update_term_details_emp;
2450 --
2451 -- Only set output warning arguments
2452 -- (Any key or derived arguments must be set to null
2456 --
2453 -- when validation only mode is being used.)
2454 --
2455 p_object_version_number := l_object_version_number;
2457 when others then
2458 --
2459 -- A validation or unexpected error has occurred
2460 --
2461 -- Added as part of fix to bug 632479
2462 --
2463 ROLLBACK TO update_term_details_emp;
2464 --
2465 -- set in out parameters and set out parameters
2466 --
2467 p_object_version_number := l_ovn;
2468 raise;
2469 --
2470 -- End of fix.
2471 --
2472 end update_term_details_emp;
2473 --
2474 -- ----------------------------------------------------------------------------
2475 -- |-----------------------< check_for_compl_actions >-------------------------|
2476 -- ----------------------------------------------------------------------------
2477 FUNCTION check_for_compl_actions(p_person_id NUMBER
2478 ,p_act_date DATE
2479 ,p_lsp_date DATE
2480 ,p_fpr_date DATE
2481 ) RETURN VARCHAR2 IS
2482 --
2483 l_action_chk VARCHAR2(1) := 'N';
2484 l_action_date DATE;
2485 --
2486 l_proc varchar2(72):=g_package||'check_for_compl_actions';
2487 begin
2488 hr_utility.trace('Entered check_for_compl_actions for '||p_person_id);
2489 hr_utility.trace('ATD: '||p_act_date);
2490 hr_utility.trace('LSP: '||p_lsp_date);
2491 hr_utility.trace('FPD: '||p_fpr_date);
2492 --
2493 IF p_lsp_date IS NOT NULL THEN
2494 IF p_act_date IS NOT NULL AND p_lsp_date >= p_act_date THEN
2495 l_action_date := p_lsp_date;
2496 ELSE
2497 l_action_date := null;
2498 END IF;
2499 ELSE
2500 l_action_date := p_act_date;
2501 END IF;
2502 hr_utility.set_location(l_proc,1);
2503 BEGIN
2504 SELECT 'Y'
2505 INTO l_action_chk
2506 FROM dual
2507 WHERE exists
2508 (SELECT null
2509 FROM pay_payroll_actions pac,
2510 pay_assignment_actions act,
2511 per_assignments_f asg
2512 WHERE asg.person_id = p_person_id
2513 AND act.assignment_id = asg.assignment_id
2514 AND pac.payroll_action_id = act.payroll_action_id
2515 AND pac.action_type NOT IN ('X','BEE')
2516 AND pac.effective_date > nvl(p_fpr_date,hr_api.g_eot));
2517 EXCEPTION
2518 when NO_DATA_FOUND then null;
2519 END;
2520 --
2521 hr_utility.set_location(l_proc,5);
2522 IF l_action_chk = 'N' THEN
2523 BEGIN
2524 SELECT 'W'
2525 INTO l_action_chk
2526 FROM dual
2527 WHERE exists
2528 (SELECT null
2529 FROM pay_payroll_actions pac,
2530 pay_assignment_actions act,
2531 per_assignments_f asg
2532 WHERE asg.person_id = p_person_id
2533 AND act.assignment_id = asg.assignment_id
2534 AND pac.payroll_action_id = act.payroll_action_id
2535 AND pac.action_type <> 'BEE'
2536 AND pac.action_status = 'C'
2537 AND ( (p_fpr_date is null
2538 AND pac.effective_date >= l_action_date)
2539 OR (p_fpr_date is not null
2540 AND (pac.effective_date >= l_action_date
2541 AND pac.effective_date <= p_fpr_date))));
2542 --
2543 hr_utility.set_location(l_proc,7);
2544 EXCEPTION
2545 when NO_DATA_FOUND then null;
2546 END;
2547 END IF;
2548 RETURN l_action_chk;
2549 end check_for_compl_actions;
2550 --
2551 -- ----------------------------------------------------------------------------
2552 -- |-----------------------< reverse_terminate_employee >-------------------------|
2553 -- ----------------------------------------------------------------------------
2554 procedure reverse_terminate_employee
2555 (p_validate in boolean default false
2556 ,p_person_id in number
2557 ,p_actual_termination_date in date
2558 ,p_clear_details in varchar2
2559 ) is
2560 --
2561 -- Declare cursors and local variables
2562 --
2563
2564 l_proc varchar2(72) := g_package||'reverse_terminate_employee';
2565 begin
2566 hr_utility.set_location('Entering:'|| l_proc, 5);
2567 --
2568 -- Issue a savepoint.
2569 --
2570 savepoint reverse_terminate_employee;
2571 --
2572 --
2573 -- Start of API User Hook for the before hook of reverse_terminate_employee.
2574 --
2575 begin
2576 --
2577 hr_ex_employee_bk4.reverse_terminate_employee_b
2578 ( p_person_id => p_person_id
2579 ,p_actual_termination_date => p_actual_termination_date
2580 ,p_clear_details => p_clear_details
2581 );
2582 exception
2583 when hr_api.cannot_find_prog_unit then
2584 hr_api.cannot_find_prog_unit_error
2585 (p_module_name => 'REVERSE_TERMINATE_EMPLOYEE',
2586 p_hook_type => 'BP'
2587 );
2588 end;
2589 --
2590 -- Process Logic
2591 --
2592 hr_utility.set_location('Before:hr_ex_employee_internal.reverse_terminate_employee'|| l_proc, 10);
2593 hr_ex_employee_internal.reverse_terminate_employee
2594 (p_person_id
2595 ,p_actual_termination_date
2596 ,p_clear_details);
2597 --
2598 hr_utility.set_location('After:hr_ex_employee_internal.reverse_terminate_employee'|| l_proc, 15);
2599 -- When in validation only mode raise the Validate_Enabled exception
2600 --
2601 --
2605 hr_ex_employee_bk4.reverse_terminate_employee_a
2602 -- Start of API User Hook for the after hook of reverse_terminate_employee.
2603 --
2604 begin
2606 ( p_person_id => p_person_id
2607 ,p_actual_termination_date => p_actual_termination_date
2608 ,p_clear_details => p_clear_details
2609 );
2610 exception
2611 when hr_api.cannot_find_prog_unit then
2612 hr_api.cannot_find_prog_unit_error
2613 (p_module_name => 'REVERSE_TERMINATE_EMPLOYEE',
2614 p_hook_type => 'AP'
2615 );
2616 end;
2617
2618 if p_validate then
2619 raise hr_api.validate_enabled;
2620 end if;
2621 --
2622 hr_utility.set_location(' Leaving:'||l_proc, 20);
2623 --
2624 exception
2625 when hr_api.validate_enabled then
2626 --
2627 -- As the Validate_Enabled exception has been raised
2628 -- we must rollback to the savepoint
2629 --
2630 ROLLBACK TO reverse_terminate_employee;
2631 --
2632 -- Only set output warning arguments
2633 -- (Any key or derived arguments must be set to null
2634 -- when validation only mode is being used.)
2635 --
2636 --
2637 when others then
2638 --
2639 --
2640 ROLLBACK TO reverse_terminate_employee;
2641 --
2642 -- set in out parameters and set out parameters
2643 --
2644 raise;
2645 --
2646 end reverse_terminate_employee;
2647 --
2648 end hr_ex_employee_api;