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