1 package body per_fr_termination as
2 /* $Header: pefrterm.pkb 120.4 2007/04/30 11:08:36 abhaduri noship $ */
3 --
4 g_package varchar2(30) := 'per_fr_termination';
5 g_person_id number;
6 g_validation number := 0;
7 --
8 /* --------------------------------------------------------------------
9 Local procedure to retrieve the Person ID from the period of service ID
10 -------------------------------------------------------------------- */
11 procedure initiate(p_period_of_service_id number) is
12 --
13 cursor c_get_pds is
14 select person_id
15 from per_periods_of_service
16 where period_of_service_id = p_period_of_service_id;
17 --
18 l_proc varchar2(72) := g_package || '.intiate';
19 begin
20 hr_utility.set_location(l_proc,10);
21 open c_get_pds;
22 fetch c_get_pds into g_person_id;
23 close c_get_pds;
24 end initiate;
25 --
26 /* --------------------------------------------------------------------
27 Local function to determine whether the Leaving Reason has a corresponding
28 value in FR_ENDING_REASON and CONTRACT_END_REASON
29 -------------------------------------------------------------------- */
30 function validate_leaving_reason
31 (p_lookup_type varchar2
32 ,p_lookup_code varchar2) return boolean is
33 --
34 l_dummy varchar2(1);
35 --
36 cursor c_get_lkp is
37 select null
38 from hr_lookups
39 where lookup_type = p_lookup_type
40 and lookup_code = p_lookup_code;
41 --
42 l_proc varchar2(72) := g_package || '.validate_leaving_reason';
43 begin
44 hr_utility.set_location(l_proc,10);
45 open c_get_lkp;
46 fetch c_get_lkp into l_dummy;
47 if c_get_lkp%found then
48 close c_get_lkp;
49 hr_utility.set_location(l_proc,20);
50 return TRUE;
51 else
52 close c_get_lkp;
53 hr_utility.set_location(l_proc,30);
54 return FALSE;
55 end if;
56 end validate_leaving_reason;
57 --
58 /* --------------------------------------------------------------------
59 Local procedure to perform validation of French specific columns.
60 -------------------------------------------------------------------- */
61 procedure validate
62 (p_actual_termination_date date
63 ,p_actual_termination_date_o date
64 ,p_notice_period_start_date date
65 ,p_notice_period_start_date_o date
66 ,p_notice_period_end_date date
67 ,p_notice_period_end_date_o date
68 ,p_last_day_worked varchar2
69 ,p_last_day_worked_o varchar2
70 ,p_final_process_date date -- for bug#5191942
71 ) is
72 --
73 l_exists varchar2(1);
74 --
75 cursor c_future_contracts is
76 select null
77 from per_contracts_f
78 where person_id = g_person_id
79 and effective_start_date > p_actual_termination_date + 1
80 and substrb(status,1,2) = 'A-';
81 --
82 l_nps_changed boolean :=
83 (p_notice_period_start_date is not null
84 and (p_notice_period_start_date_o is null
85 or (p_notice_period_start_date <> p_notice_period_start_date_o
86 and p_notice_period_start_date_o is not null
87 )));
88 l_npe_changed boolean :=
89 (p_notice_period_end_date is not null
90 and (p_notice_period_end_date_o is null
91 or (p_notice_period_end_date <> p_notice_period_end_date_o
92 and p_notice_period_end_date_o is not null
93 )));
94 l_lwd_changed boolean :=
95 (p_last_day_worked is not null
96 and (p_last_day_worked_o is null
97 or (p_last_day_worked <> p_last_day_worked_o
98 and p_last_day_worked_o is not null
99 )));
100 --
101 l_proc varchar2(72) := g_package || '.validate';
102 begin
103 hr_utility.set_location(l_proc,10);
104 --
105 -- Added for bug#5191942
106 -- if final process date is on or before actual termination date
107 -- then raise an error
108 hr_utility.set_location(l_proc, 10);
109 /*if p_final_process_date is not null
110 and p_actual_termination_date is not null then
111 if p_final_process_date < p_actual_termination_date
112 or p_final_process_date = p_actual_termination_date then
113 --
114 hr_utility.set_message(800, 'PER_75099_FINAL_CLOSE_ATD');
115 hr_utility.raise_error;
116 --
117 end if;
118 end if;*/
119 --
120 -- If either the NPS or NPE have changed and if either is NOT NULL then
121 -- validate that NPS is not after NPE
122 --
123 hr_utility.set_location(l_proc,20);
124 if l_nps_changed or l_npe_changed then
125 if p_notice_period_start_date > p_notice_period_end_date then
126 hr_utility.set_message(800,'PER_75013_NOTICE_PERIOD_DATES');
127 hr_utility.raise_error;
128 end if;
129 end if;
130 --
131 -- If NPS has changed or LWD has changed then ensure that
132 -- Notice period start is not later than LWD
133 --
134 hr_utility.set_location(l_proc,30);
135 if l_nps_changed or l_lwd_changed then
136 if p_notice_period_start_date > p_last_day_worked then
137 hr_utility.set_message(800,'PER_75014_NPS_LWD_DATES');
138 hr_utility.raise_error;
139 end if;
140 end if;
141 --
142 -- If NPE has changed or LWD has changed then ensure that
143 -- Notice period end is not earlier than LWD
144 --
145 hr_utility.set_location(l_proc,40);
146 if l_npe_changed or l_lwd_changed then
147 if p_notice_period_end_date < p_last_day_worked then
148 hr_utility.set_message(800,'PER_75015_NPE_LWD_DATES');
149 hr_utility.raise_error;
150 end if;
151 end if;
152 --
153 -- If NPE is not null then NPS must also be not null
154 --
155 hr_utility.set_location(l_proc,50);
156 if p_notice_period_start_date is null
157 and p_notice_period_end_date is not null then
158 hr_utility.set_message(800,'PER_75016_NPS_NPE_DATES');
159 hr_utility.raise_error;
160 end if;
161 --
162 -- If ATD is not null then it must be on or after NPS and
163 -- on or before NPE
164 --
165 hr_utility.set_location(l_proc,60);
166 if p_actual_termination_date is not null
167 and p_notice_period_start_date is not null
168 and p_notice_period_end_date is not null then
169 if p_notice_period_start_date > p_actual_termination_date
170 or p_notice_period_end_date < p_actual_termination_date then
171 hr_utility.set_message(800,'PER_75017_NPS_ATD_DATES');
172 hr_utility.raise_error;
173 end if;
174 end if;
175 --
176 -- If ATD is not null and LWD is not null then LWD must be on or
177 -- before ATD
178 --
179 hr_utility.set_location(l_proc,70);
180 if p_actual_termination_date is not null
181 and p_last_day_worked is not null then
182 if p_last_day_worked > p_actual_termination_date then
183 hr_utility.set_message(800,'PER_75018_LWD_ATD_DATES');
184 hr_utility.raise_error;
185 end if;
186 end if;
187 --
188 -- If ATD is not null then LWD must be entered
189 --
190 hr_utility.set_location(l_proc,80);
191 if p_actual_termination_date is not null
192 and p_last_day_worked is null then
193 hr_utility.set_message(800,'PER_75019_LWD_ATD_NULL');
194 hr_utility.raise_error;
195 end if;
196 --
197 hr_utility.set_location(l_proc,90);
198 end validate;
199 --
200 /* -------------------------------------------------------------------------
201 Local procedure to delete and create the element entry
202 FR_LAST_DAY_WORKED on LWD.
203 This is called from Terminate and Revers.
204 In the case of Reverse only the delete is performed.
205 ----------------------------------------------------------------------- */
206 procedure last_day_worked_entry
207 (p_last_day_worked date
208 ,p_actual_termination_date date) is
209 --
210 l_assignment_id number;
211 l_effective_date date;
212 l_element_type_id number;
213 l_element_link_id number;
214 l_element_entry_id number;
215 l_effective_start_date date;
216 l_effective_end_date date;
217 l_element_name pay_element_types_f.element_name%Type;
218 l_input_value_id Number;
219 --
220 cursor c_get_asg is
221 select assignment_id
222 from per_all_assignments_f
223 where person_id = g_person_id
224 and l_effective_date
225 between effective_start_date and effective_end_date
226 and primary_flag = 'Y';
227 --
228 cursor c_element is
229 select element_type_id, element_name
230 from pay_element_types_f
231 where l_effective_date
232 between effective_start_date and effective_end_date
233 and element_name IN ('FR_LAST_DAY_WORKED', 'FR_TERMINATION_INFORMATION')
234 and legislation_code = 'FR';
235 --
236
237 cursor c_input_value is
238 select input_value_id
239 from pay_input_values_f
240 where l_effective_date
241 between effective_start_date and effective_end_date
242 and legislation_code = 'FR'
243 and element_type_id = l_element_type_id
244 and name = 'Proration';
245
246 cursor c_entry is
247 select element_entry_id
248 from pay_element_entries_f ee
249 , pay_element_links_f l
250 where ee.assignment_id = l_assignment_id
251 and l.element_type_id = l_element_type_id
252 and l.element_link_id = ee.element_link_id
253 and p_actual_termination_date
254 between ee.effective_start_date and ee.effective_end_date
255 and p_actual_termination_date
256 between l.effective_start_date and l.effective_end_date;
257 --
258 l_proc varchar2(72) := g_package || '.last_day_worked_entry';
259 begin
260 hr_utility.set_location(l_proc,10);
261 --
262 -- Get the FR_LAST_DAY_WORKED element type ID as of Actual Termination Date
263 --
264 l_effective_date := p_actual_termination_date;
265 open c_element;
266 LOOP
267 fetch c_element into l_element_type_id, l_element_name;
268 EXIT WHEN c_element%NOTFOUND;
269 --
270 -- Get assignment ID of the primary assignment
271 --
272 if l_element_type_id is not null then
273 open c_get_asg;
274 fetch c_get_asg into l_assignment_id;
275 close c_get_asg;
276 --
277 -- Remove any existing element entry effective as of ATD
278 --
279 hr_utility.set_location(l_proc,20);
280 open c_entry;
281 fetch c_entry into l_element_entry_id;
282 if c_entry%found then
283 hr_entry_api.delete_element_entry
284 ('ZAP'
285 ,p_actual_termination_date
286 ,l_element_entry_id);
287 close c_entry;
288 else
289 close c_entry;
290 end if;
291 --
292 -- Now, if called from Terminate process, create new element entry
293 -- effective as of LWD
294 --
295 hr_utility.set_location(l_proc,30);
296 if p_last_day_worked is not null then
297 l_effective_date := p_last_day_worked;
298 open c_get_asg;
299 fetch c_get_asg into l_assignment_id;
300 close c_get_asg;
301 --
302 l_element_link_id :=
303 hr_entry_api.get_link(l_assignment_id
304 ,l_element_type_id
305 ,p_last_day_worked);
306 --
307 l_effective_start_date := p_last_day_worked;
308 --
309 if l_element_link_id is not null then
310 hr_entry_api.insert_element_entry
311 (p_effective_start_date => l_effective_start_date
312 ,p_effective_end_date => l_effective_end_date
313 ,p_element_entry_id => l_element_entry_id
314 ,p_assignment_id => l_assignment_id
315 ,p_element_link_id => l_element_link_id
316 ,p_creator_type => 'F'
317 ,p_entry_type => 'E');
318
319 --
320 -- Force DT change when ever entry is created on day after last day worked.
321 -- A dummy input value is changed so that proration will occur.
322 --
323 IF l_element_name = 'FR_LAST_DAY_WORKED' THEN
324
325 hr_utility.set_location('l_elemen_type_id'||l_element_type_id, 100);
326 hr_utility.set_location('l_assignment_id'||l_assignment_id, 100);
327 hr_utility.set_location('l_element_link_id'||l_element_link_id, 100);
328 Open c_input_value;
329 Fetch c_input_value into l_input_value_id;
330 Close c_input_value;
331 hr_utility.set_location('l_input_value_id'||l_input_value_id, 100);
332 hr_utility.set_location('LWD'|| p_last_day_worked, 100);
333 hr_utility.set_location('ATD'|| p_actual_termination_date, 100);
334 IF l_input_value_id IS NOT NULL THEN
335 hr_utility.set_location('DT Updating EE ', 100);
336 IF p_last_day_worked < p_actual_termination_date THEN
337 hr_utility.set_location('Updating EE with LWD date='|| (p_last_day_worked + 1), 100);
338 hr_entry_api.update_element_entry
339 (p_dt_update_mode => 'UPDATE'
340 ,p_session_date => p_last_day_worked + 1
341 ,p_element_entry_id => l_element_entry_id
342 ,p_comment_id => Null
343 ,p_input_value_id1 => l_input_value_id
344 ,p_entry_value1 => '2'
345 );
346 END IF;
347 -- Bug #2884200
348 hr_utility.set_location('Updating EE with ATD date='||(p_actual_termination_date + 1), 100);
349 -- Bug#6003309
350 hr_utility.set_location('Checking if ATD = FPD', 100);
351 IF p_actual_termination_date <> l_effective_end_date THEN
352 hr_entry_api.update_element_entry
353 (p_dt_update_mode => 'UPDATE'
354 ,p_session_date => p_actual_termination_date + 1
355 ,p_element_entry_id => l_element_entry_id
356 ,p_comment_id => Null
357 ,p_input_value_id1 => l_input_value_id
358 ,p_entry_value1 => '3'
359 );
360 END IF;
361 END IF; -- Input value id is not null
362 END IF; -- element is 'FR_LAST_DAY_WORKED'
363 end if;
364 end if; -- if p_last_day_worked is not null
365 end if; -- if l_element_type_id is not null
366 END LOOP;
367 CLOSE c_element;
368 --
369 hr_utility.set_location(l_proc,40);
370 --
371 end last_day_worked_entry;
372 --
373 /* -------------------------------------------------------------------------
374 local procedure to correct the assignment ending on ATD setting the
375 leaving reason to that on the period of service
376 ----------------------------------------------------------------------- */
377 procedure correct_assignment(p_period_of_service_id number
378 ,p_actual_termination_date date
379 ,p_leaving_reason varchar2) is
380 --
381 l_proc varchar2(72) := g_package||'.correct_assignment';
382 --
383 l_object_version_number number;
384 l_concatenated_segments varchar2(2000);
385 l_soft_coding_keyflex_id number;
386 l_comment_id number;
387 l_effective_start_date date;
388 l_effective_end_date date;
389 l_no_managers_warning boolean;
390 l_other_manager_warning boolean;
391 l_effective_date date;
392 l_update_mode varchar2(30);
393 l_exists varchar2(30);
394 l_contract_id number;
395 l_active_contracts number;
396 l_all_contracts number;
397 l_leaving_reason varchar2(30);
398 --
399 cursor c_get_asg is
400 select a.assignment_id
401 , a.primary_flag
402 , a.object_version_number
403 , a.soft_coding_keyflex_id
404 , scl.segment4
405 , scl.segment2
406 from per_all_assignments_f a
407 , hr_soft_coding_keyflex scl
408 , per_assignment_status_types ast
409 where a.period_of_service_id = p_period_of_service_id
410 and p_actual_termination_date = a.effective_end_date
411 and a.assignment_status_type_id = ast.assignment_status_type_id
412 and ast.per_system_status <> 'TERM_ASSIGN'
413 and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+);
414 --
415 begin
416 hr_utility.set_location(l_proc,10);
417 --
418 -- Process assignments ending on Actual Termination Date - set End Reason
419 --
420 if p_leaving_reason is null or
421 not validate_leaving_reason('FR_ENDING_REASON',p_leaving_reason) then
422 l_leaving_reason := null;
423 else
424 l_leaving_reason := p_leaving_reason;
425 end if;
426 --
427 for a in c_get_asg loop
428 hr_utility.trace(a.assignment_id);
429 hr_utility.trace(p_period_of_service_id);
430 hr_utility.trace(p_actual_termination_date);
431 l_object_version_number := a.object_version_number;
432 hr_assignment_api.update_emp_asg
433 (P_VALIDATE => FALSE
434 ,P_EFFECTIVE_DATE => p_actual_termination_date
435 ,P_DATETRACK_UPDATE_MODE => 'CORRECTION'
436 ,P_ASSIGNMENT_ID => a.assignment_id
437 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
438 ,P_SEGMENT2 => a.segment2
439 ,P_SEGMENT4 => l_leaving_reason
440 ,P_CONCATENATED_SEGMENTS => l_concatenated_segments
441 ,P_SOFT_CODING_KEYFLEX_ID => l_soft_coding_keyflex_id
442 ,P_COMMENT_ID => l_comment_id
443 ,P_EFFECTIVE_START_DATE => l_effective_start_date
444 ,P_EFFECTIVE_END_DATE => l_effective_end_date
445 ,P_NO_MANAGERS_WARNING => l_no_managers_warning
446 ,P_OTHER_MANAGER_WARNING => l_other_manager_warning);
447 end loop;
448 --
449 hr_utility.set_location(l_proc,20);
450 end correct_assignment;
451 --
452
453 --
454 /* -------------------------------------------------------------------------
455 public procedure called from HR_EX_EMPLOYEE_BK1.ACTUAL_TERMINATION_EMP_A hook
456 to process assignments after they have been terminated to set leaving reason on
457 the record ending on ATD
458 ----------------------------------------------------------------------- */
459 procedure actual_termination(p_period_of_service_id number
460 ,p_actual_termination_date date) is
461 --
462 l_proc varchar2(72) := g_package||'.actual_termination';
463 l_leaving_reason varchar2(30);
464 --
465 cursor c_get_pds is
466 select leaving_reason
467 from per_periods_of_service
468 where period_of_service_id = p_period_of_service_id;
469 --
470 begin
471
472 --
473 /* Added for GSI Bug 5472781 */
474 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
475 hr_utility.set_location('Leaving : '||l_proc , 10);
476 return;
477 END IF;
478 --
479 hr_utility.set_location(l_proc,10);
480 hr_utility.trace('Actually Terminating');
481 open c_get_pds;
482 fetch c_get_pds into l_leaving_reason;
483 close c_get_pds;
484 --
485 correct_assignment(p_period_of_service_id
486 ,p_actual_termination_date
487 ,l_leaving_reason);
488 hr_utility.set_location(l_proc,20);
489 end;
490 --
491 /* -------------------------------------------------------------------------
492 External procedure called from Termination API Row Handler After Update
493 hook call for France.
494 N.B. The Termination process as called from the form will call this procedure
495 3 times:
496 1. to update HR attributes
497 2. to maintain ATD
498 3. to maintain FPD
499
500 In 1. the ATD if changing is not available
501 In 2. the DDF segment changes are not available (both old and new values
502 are the same)
503 In 3. no hook processing is required.
504
505 Hence it is necessary to detect programatically then termination is happening
506 as opposed to just an update to the HR attributes (i.e DDF, Leaving Reason).
507 ----------------------------------------------------------------------- */
508 procedure termination
509 (p_period_of_service_id number
510 ,p_actual_termination_date date
511 ,p_leaving_reason varchar2
512 ,p_pds_information8 varchar2
513 ,p_pds_information9 varchar2
514 ,p_pds_information10 varchar2
515 ,p_actual_termination_date_o date
516 ,p_leaving_reason_o varchar2
517 ,p_pds_information8_o varchar2
518 ,p_pds_information9_o varchar2
519 ,p_pds_information10_o varchar2
520 ,p_final_process_date date -- for bug#5191942
521 ) is
522 --
523 l_proc varchar2(72) := g_package||'.termination';
524 --
525 l_notice_period_start_date date :=
526 fnd_date.canonical_to_date(p_pds_information8);
527 l_notice_period_end_date date :=
528 fnd_date.canonical_to_date(p_pds_information9);
529 l_last_day_worked date :=
530 fnd_date.canonical_to_date(p_pds_information10);
531 --
532 l_notice_period_start_date_o date :=
533 fnd_date.canonical_to_date(p_pds_information8_o);
534 l_notice_period_end_date_o date :=
535 fnd_date.canonical_to_date(p_pds_information9_o);
536 l_last_day_worked_o date :=
537 fnd_date.canonical_to_date(p_pds_information10_o);
538 --
539 l_object_version_number number;
540 l_concatenated_segments varchar2(2000);
541 l_soft_coding_keyflex_id number;
542 l_comment_id number;
543 l_effective_start_date date;
544 l_effective_end_date date;
545 l_no_managers_warning boolean;
546 l_other_manager_warning boolean;
547 l_effective_date date;
548 l_update_mode varchar2(30);
549 l_exists varchar2(30);
550 l_contract_id number;
551 l_active_contracts number;
552 l_all_contracts number;
553 l_leaving_reason varchar2(30);
554 --
555 cursor c_get_asg is
556 select a.assignment_id
557 , a.primary_flag
558 , a.object_version_number
559 , a.soft_coding_keyflex_id
560 , scl.segment4
561 , scl.segment2
562 from per_all_assignments_f a
563 , hr_soft_coding_keyflex scl
564 where a.period_of_service_id = p_period_of_service_id
565 and p_actual_termination_date = a.effective_end_date
566 and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+);
567 --
568 cursor c_get_ctr is
569 select contract_id
570 , person_id
571 , object_version_number
572 , reference
573 , type
574 , status
575 ,effective_start_date
576 ,effective_end_date
577 from per_contracts_f c
578 where person_id = g_person_id
579 and l_effective_date between effective_start_date and effective_end_date
580 and substrb(status,1,2) = 'A-';
581 --
582 l_atd_changed boolean :=
583 (p_actual_termination_date is not null and
584 p_actual_termination_date_o is null);
585 l_leaving_reason_changed boolean :=
586 (p_leaving_reason is not null and
587 p_leaving_reason_o is not null and
588 p_leaving_reason <> p_leaving_reason_o)
589 or
590 (p_leaving_reason is null and
591 p_leaving_reason_o is not null)
592 or
593 (p_leaving_reason_o is null and
594 p_leaving_reason is not null);
595 l_lwd_changed boolean :=
596 (l_last_day_worked is not null
597 and (l_last_day_worked_o is null
598 or (l_last_day_worked <> l_last_day_worked_o
599 and l_last_day_worked_o is not null
600 )));
601
602 begin
603 --
604 /* Added for GSI Bug 5472781 */
605 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
606 hr_utility.set_location('Leaving : '||l_proc , 10);
607 return;
608 END IF;
609 --
610 hr_utility.set_location(l_proc,10);
611 --
612 -- Initiate Person ID package variable
613 --
614 initiate(p_period_of_service_id);
615 --
616 -- Perform validation of DDF segments
617 --
618 validate(p_actual_termination_date
619 ,p_actual_termination_date_o
620 ,l_notice_period_start_date
621 ,l_notice_period_start_date_o
622 ,l_notice_period_end_date
623 ,l_notice_period_end_date_o
624 ,l_last_day_worked
625 ,l_last_day_worked_o
626 ,p_final_process_date);
627 --
628 -- If Terminating (ATD not null and ATD_Old is null)
629 -- then change contract to change status to Inactive on day after
630 -- ATD
631 --
632 -- This needs to take into account future changes to contract
633 --
634 hr_utility.set_location(l_proc,20);
635 if l_atd_changed then
636 l_effective_date := p_actual_termination_date + 1;
637 for c in c_get_ctr loop
638 l_object_version_number := c.object_version_number;
639 --
640 if c.effective_start_date = l_effective_date then
641 l_update_mode := 'CORRECTION';
642 elsif c.effective_end_date < hr_general.end_of_time then
643 l_update_mode := 'UPDATE_CHANGE_INSERT';
644 else
645 l_update_mode := 'UPDATE';
646 end if;
647 --
648 -- Create or update contract on day after ATD
649 --
650 hr_utility.trace(c.contract_id||' '||l_update_mode);
651 hr_contract_api.update_contract
652 (P_VALIDATE => FALSE
653 ,P_CONTRACT_ID => c.contract_id
654 ,P_EFFECTIVE_START_DATE => l_effective_start_date
655 ,P_EFFECTIVE_END_DATE => l_effective_end_date
656 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
657 ,P_PERSON_ID => c.person_id
658 ,P_REFERENCE => c.reference
659 ,P_TYPE => c.type
660 ,P_STATUS => 'T-TERMINATION'
661 ,P_EFFECTIVE_DATE => p_actual_termination_date+1
662 ,P_DATETRACK_MODE => l_update_mode);
663 --
664 end loop;
665 end if; -- if l_atd_changed
666 --
667 -- Now handle changes of Leaving Reason
668 --
669 -- If the Leaving Reason changes and ATD is not changing
670 -- the Leaving Reason is not null then default the Leaving Reason onto
671 -- the Assignment (SCL.SEGMENT4)
672 --
673 -- The record that will be updated will be the one that ends on ATD.
674 --
675 -- For this to be possible there must be the same lookup code as the Leaving
676 -- Reason in FR_ENDING_REASON
677 --
678 -- If such values do not exist then a NULL will be copied onto the SCL
679 --
680 -- N.B. This processing occurs only when ATD is set (i.e. a change of leaving
681 -- reason after termination )
682 -- It is therefore safe to assume that there will be Assignment
683 -- records ending on the ATD. These records will have a datetrack CORRECTION.
684 --
685 hr_utility.set_location(l_proc,30);
686 if p_actual_termination_date is not null and
687 (not l_atd_changed) and l_leaving_reason_changed then
688 --
689 correct_assignment(p_period_of_service_id
690 ,p_actual_termination_date
691 ,p_leaving_reason);
692 end if;
693 --
694 -- Process contracts ending on Actual Termination Date - set End Reason
695 --
696 -- If the Leaving Reason changes or ATD changes and
697 -- the Leaving Reason is not null then default the Leaving Reason onto
698 -- the Contract (END_REASON)
699 --
700 -- The record that will be updated will be the one that ends on ATD.
701 --
702 -- For this to be possible there must be the same lookup code as the Leaving
703 -- Reason in CONTRACT_END_REASON
704 --
705 -- If such values do not exist then a NULL will be copied onto the contract
706 --
707 -- N.B. This processing occurs only when ATD is set (i.e. a change of leaving
708 -- reason after termination )
709 -- It is therefore safe to assume that there will be Contract
710 -- records ending on the ATD. These records will have a datetrack CORRECTION.
711 --
712 if p_actual_termination_date is not null and
713 ((l_atd_changed and p_leaving_reason is not null) or
714 (l_leaving_reason_changed )) then
715 hr_utility.set_location(l_proc,40);
716 if p_leaving_reason is null or
717 not validate_leaving_reason('CONTRACT_END_REASON',p_leaving_reason) then
718 l_leaving_reason := null;
719 else
720 l_leaving_reason := p_leaving_reason;
721 end if;
722 --
723 l_effective_date := p_actual_termination_date;
724 for c in c_get_ctr loop
725 if p_actual_termination_date = c.effective_end_date then
726 hr_utility.trace(c.contract_id);
727 l_object_version_number := c.object_version_number;
728 hr_contract_api.update_contract
729 (P_VALIDATE => FALSE
730 ,P_CONTRACT_ID => c.contract_id
731 ,P_EFFECTIVE_START_DATE => l_effective_start_date
732 ,P_EFFECTIVE_END_DATE => l_effective_end_date
733 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
734 ,P_PERSON_ID => c.person_id
735 ,P_REFERENCE => c.reference
736 ,P_TYPE => c.type
737 ,P_STATUS => c.status
738 ,P_END_REASON => l_leaving_reason
739 ,P_EFFECTIVE_DATE => p_actual_termination_date
740 ,P_DATETRACK_MODE => 'CORRECTION');
741 end if; -- p_actual_termination_date = c.effective_end_date
742 end loop;
743 end if; -- if p_actual_termination_date is not null
744 --
745 -- Now handle creation of Last Day Worked Element Entry
746 -- The element should be created if ATD is not null and LWD is not null
747 -- and either LWD has been updated or termination is taking place.
748 --
749 hr_utility.set_location(l_proc,50);
750 if p_actual_termination_date is not null
751 and l_last_day_worked is not null
752 and (l_lwd_changed or l_atd_changed) then
753 last_day_worked_entry(l_last_day_worked
754 ,p_actual_termination_date);
755 end if; -- if p_actual_termination_date is not null
756 --
757 hr_utility.set_location(l_proc,60);
758 end termination;
759 --
760 procedure reverse
761 (p_period_of_service_id number
762 ,p_actual_termination_date date
763 ,p_leaving_reason varchar2) is
764 --
765 l_proc varchar2(72) := g_package||'.reverse';
766 --
767 l_object_version_number number;
768 l_concatenated_segments varchar2(2000);
769 l_soft_coding_keyflex_id number;
770 l_comment_id number;
771 l_effective_start_date date;
772 l_effective_end_date date;
773 l_no_managers_warning boolean;
774 l_other_manager_warning boolean;
775 l_status varchar2(30);
776 --
777 l_effective_date date;
778 --
779 cursor c_get_asg is
780 select a.object_version_number
781 , a.assignment_id
782 , a.soft_coding_keyflex_id
783 , scl.segment2
784 , decode(effective_end_date
785 ,p_actual_termination_date,p_actual_termination_date
786 ,p_actual_termination_date+1) effective_date
787 from per_all_assignments_f a
788 , hr_soft_coding_keyflex scl
789 where period_of_service_id = p_period_of_service_id
790 and (effective_end_date = p_actual_termination_date
791 or effective_start_date = p_actual_termination_date+1
792 or (p_actual_termination_date >= effective_start_date and
793 effective_end_date = to_date('47121231','YYYYMMDD'))
794 )
795 and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;
796 --
797 cursor c_get_ctr1 is
798 select contract_id
799 , person_id
800 , object_version_number
801 , reference
802 , type
803 , status
804 ,effective_start_date
805 ,effective_end_date
806 from per_contracts_f c
807 where person_id =
808 (select person_id
809 from per_periods_of_service
810 where period_of_service_id = p_period_of_service_id)
811 and p_actual_termination_date = effective_end_date;
812 --
813 cursor c_get_ctr2 (p_contract_id number
814 ,p_effective_date date) is
815 select contract_id
816 , person_id
817 , object_version_number
818 , reference
819 , type
820 , status
821 ,effective_start_date
822 ,effective_end_date
823 from per_contracts_f c
824 where contract_id = p_contract_id
825 and effective_start_date = p_effective_date;
826 --
827 ctr2 c_get_ctr2%rowtype;
828 --
829 begin
830 hr_utility.set_location(l_proc,10);
831 initiate(p_period_of_service_id);
832 --
833 -- On reversing a terination update the assignment record that ended
834 -- at ATD to set the ending reason to NULL
835 --
836 for a in c_get_asg loop
837 l_object_version_number := a.object_version_number;
838 hr_assignment_api.update_emp_asg
839 (P_VALIDATE => FALSE
840 ,P_EFFECTIVE_DATE => a.effective_date
841 ,P_DATETRACK_UPDATE_MODE => 'CORRECTION'
842 ,P_ASSIGNMENT_ID => a.assignment_id
843 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
844 ,P_SEGMENT2 => a.segment2
845 ,P_SEGMENT4 => null
846 ,P_CONCATENATED_SEGMENTS => l_concatenated_segments
847 ,P_SOFT_CODING_KEYFLEX_ID => l_soft_coding_keyflex_id
848 ,P_COMMENT_ID => l_comment_id
849 ,P_EFFECTIVE_START_DATE => l_effective_start_date
850 ,P_EFFECTIVE_END_DATE => l_effective_end_date
851 ,P_NO_MANAGERS_WARNING => l_no_managers_warning
852 ,P_OTHER_MANAGER_WARNING => l_other_manager_warning);
853 end loop;
854 hr_utility.set_location(l_proc,20);
855 /*
856 --
857 -- On reversing a terination update the contract record that started
858 -- on the day after ATD, set status back to Active
859 --
860 l_effective_date := p_actual_termination_date + 1;
861 for c in c_get_ctr loop
862 l_object_version_number := c.object_version_number;
863 if c.effective_start_date = l_effective_date then
864 --
865 -- Find the status of the previous contract, if found set it on the
866 -- contract record starting on ATD+1
867 --
868 open c_get_status(c.contract_id,c.effective_start_date-1);
869 fetch c_get_status into l_status;
870 if c_get_status%found then
871 close c_get_status;
872 --
873 hr_contract_api.update_contract
874 (P_VALIDATE => FALSE
875 ,P_CONTRACT_ID => c.contract_id
876 ,P_EFFECTIVE_START_DATE => l_effective_start_date
877 ,P_EFFECTIVE_END_DATE => l_effective_end_date
878 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
879 ,P_PERSON_ID => c.person_id
880 ,P_REFERENCE => c.reference
881 ,P_TYPE => c.type
882 ,P_STATUS => l_status
883 ,P_END_REASON => null
884 ,P_EFFECTIVE_DATE => l_effective_date
885 ,P_DATETRACK_MODE => 'CORRECTION');
886 else
887 close c_get_status;
888 end if;
889 end if; -- if c.effective_start_date = l_effective_date
890 end loop;
891 hr_utility.set_location(l_proc,30);
892 */
893 --
894 --
895 -- On reversing a terination update the contract record that ended
896 -- at ATD to set the ending reason to NULL
897 --
898 for c in c_get_ctr1 loop
899 l_object_version_number := c.object_version_number;
900 hr_contract_api.update_contract
901 (P_VALIDATE => FALSE
902 ,P_CONTRACT_ID => c.contract_id
903 ,P_EFFECTIVE_START_DATE => l_effective_start_date
904 ,P_EFFECTIVE_END_DATE => l_effective_end_date
905 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
906 ,P_PERSON_ID => c.person_id
907 ,P_REFERENCE => c.reference
908 ,P_TYPE => c.type
909 ,P_STATUS => c.status
910 ,P_END_REASON => null
911 ,P_EFFECTIVE_DATE => p_actual_termination_date
912 ,P_DATETRACK_MODE => 'CORRECTION');
913 --
914 l_status := c.status;
915 --
916 --
917 -- On reversing a terination update the contract record that started
918 -- on the day after ATD, set status back to Active
919 --
920 open c_get_ctr2(c.contract_id,p_actual_termination_date+1);
921 fetch c_get_ctr2 into ctr2;
922 if c_get_ctr2%found then
923 close c_get_ctr2;
924 --
925 l_object_version_number := ctr2.object_version_number;
926 hr_contract_api.update_contract
927 (P_VALIDATE => FALSE
928 ,P_CONTRACT_ID => ctr2.contract_id
929 ,P_EFFECTIVE_START_DATE => l_effective_start_date
930 ,P_EFFECTIVE_END_DATE => l_effective_end_date
931 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
932 ,P_PERSON_ID => ctr2.person_id
933 ,P_REFERENCE => ctr2.reference
934 ,P_TYPE => ctr2.type
935 ,P_STATUS => c.status
936 ,P_END_REASON => null
937 ,P_EFFECTIVE_DATE => p_actual_termination_date+1
938 ,P_DATETRACK_MODE => 'CORRECTION');
939 else
940 close c_get_ctr2;
941 end if;
942 end loop;
943 hr_utility.set_location(l_proc,40);
944 --
945 -- Delete the FR_LAST_DAY_WORKED element entry that exists on ATD
946 --
947 last_day_worked_entry(null
948 ,p_actual_termination_date);
949
950 hr_utility.set_location(l_proc,50);
951 end reverse;
952 --
953
954 FUNCTION npil_earnings_base_12months (p_assignment_id in Number,
955 p_last_day_worked in Date) Return Number
956 IS
957 l_bal_date_to Date;
958 l_bal_date_from Date;
959 l_rolling_balance Number;
960 BEGIN
961 -- Get the last day of the month prior to last working day
962 l_bal_date_to := LAST_DAY(ADD_MONTHS(p_last_day_worked,-1)) ;
963 -- function calculates over last 12 months
964 -- 1st day of 12 calendar months prior to above date
965 l_bal_date_from := TRUNC(ADD_MONTHS(l_bal_date_to+1,-12), 'MONTH');
966 -- Get rolling balances
967 l_rolling_balance := PAY_FR_GENERAL.FR_ROLLING_BALANCE
968 (p_assignment_id,
969 'FR_NOTICE_PERIOD_IN_LIEU_EARNINGS_BASE',
970 l_bal_date_from,
971 l_bal_date_to);
972
973 RETURN l_rolling_balance;
974 END;
975
976 end per_fr_termination;
[Home] [Help]