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