[Home] [Help]
PACKAGE BODY: APPS.HREMPTER
Source
1 PACKAGE BODY hrempter AS
2 /* $Header: peempter.pkb 120.18.12010000.1 2008/07/28 04:36:06 appldev ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hrempter (BODY)
24
25 Description : This package declares procedures required to
26 terminate and cancel the termination of an employee.
27
28
29 Change List
30 -----------
31
32 Version Date Author ER/CR No. Description of Change
33 -------+---------+----------+---------+--------------------------
34 70.0 11-FEB-93 SZWILLIA Date Created
35 70.1 18-FEB-93 SZWILLIA Corrected deletes from
36 PER_BOOKINGS and PER_EVENTS.
37 Also, added future actions
38 check to cancellations.
39 Added messages
40 70.2 11-MAR-93 NKHAN Added 'exit' to end
41 70.3 26-MAR-93 SZWILLIA Addition of maintain_entries_asg
42 to cancel_termination.
43 70.4 04-MAY-93 SZWILLIA Check on employee_shutdown
44 modified to deal with FPD on
45 the same date as ATD - AFTER
46 termination.
47 80.1 15-OCT-93 JRHODES Added check_cobra_benefits
48 80.2 29-OCT-93 JHOBBS B258 Corrected cursor rec_entries in
49 terminate_entries_and_alus so that it
50 only selects recurring entries.
51 80.3 03-NOV-93 JHOBBS Altered terminate_entries_and_alus so
52 that nonrecurring entries are shortened
53 if they exist past the final process
54 date. Pay proposals are also now
55 removed if there are no entries for it.
56 80.4 01-JUN-94 JHOBBS G847 Corrected check_for_future_actions so
57 that it can only return one row if
58 future actions exist. The new SQL
59 should be more efficient.
60 #218334 Added check_for_future_person_type
61 which is used to validate the
62 cancellation of a termination. It
63 checks to see if there are future
64 person types which are not EX_EMP. If
65 the cancellation was allowed to take
66 place overlapping periods of service
67 etc ... would be created.
68 70.13 16-JUN-94 PSHERGILL fix 220466 added ATTRIBUTE21..30 and
69 ASS_ATTRIBUTE21..30
70 70.15 14-JUL-94 SZWILLIA #225892 Problem which came to light on 7.1.3,
71 related to PL/SQL bug number 225900.
72 Explicit OPEN of cursor prior to
73 cursor for loop causes oracle error
74 when terminating from PERPETEM. However
75 PERPETEM, although trapping the oracle
76 error was not failing.
77 70.16 23-NOV-94 RFINE G1725 Suppressed index on business_group_id
78 70.17 20-APR-95 TMathers 265290,269193,271941 Fixed these WWBUGS
79 Added code to check for Future
80 Person rows and insert/deletes to
81 per_person_list_changes. Also added
82 flag to allow only some of Term
83 details to be deleted.
84 70.18 25-APR-95 TMathers 276096 Added legislation code to terminate_alus
85 and terminate employee so that
86 If legislation is 'US' and Last_standard
87 process is null the package will
88 not fall over.
89 70.19 12-MAY-95 TMathers 281104 added rest of fields save notified
90 and projected to clear_details update.
91 70.20 03-JUL-95 RFine 288341 Fixed error in check put in revision
92 70.18. The lack of an NVL meant that
93 leg_code <> 'US' was failing when it
94 was null. Removed the check in
95 question, as it is now unnecessary.
96 70.21 07-JUL-95 RFine 289454 Allow cancel termination to succeed if
97 future person type changes are to
98 EX_EMP_APL as well as to EX_EMP,
99 providing both types don't exist for
100 the same person. This allows
101 terminations to be cancelled for
102 Employee-Applicants.
103 70.22 24-JUL-95 TMathers 294069 Update LAST_UPDATE_DATE on
104 pay_element_entries_f with
105 sysdate.
106 70.23 26-JUL-95 RFine 291763 Added missing column PAY_BASIS_ID to
107 the SQL statement which inserts the new
108 term_assign row in per_assignments_f.
109 It was missing, with the effective
110 result that terminating the person also
111 cleared the assignments' pay basis.
112 70.24 29-AUG-95 RFine 270356 Get the default TERM_ASSIGN status
113 immediately before inserting a
114 TERM_ASSIGN assignment record, if one
115 hasn't already been passed in.
116 70.25 19-Sep-95 TMathers 307957 Added and Assignment_type = 'E'
117 to update cursor of assignments.
118 70.27 31-Oct-95 TMathers 314277 Changed updates and checks for
119 delete_entries to use the p_term_date
120 a la termination rule instead of
121 p_final_process_date.
122 70.28 23-Jan-96 MSwanson 317302 Add 'not exists' to insert on
123 per_person_list_changes and update
124 where record does exist.
125 70.29 08-Mar-96 VTreiger 314277 Changed the logic of closing down
126 the non-recurring element
127 entries when the final process
128 date is entered before the end of
129 the payroll period.
130 334654 Setting the employee assignment
131 334681 status to TERM_ASSIGN is not allowed
132 if there are certain types of future
133 non-recurring element entries which
134 have not been processed in a payroll
135 run.
136 311763 Delete of future spinal point
137 placements.
138 70.30 13-Mar-1996 VTreiger Restored code in terminate employee
139 procedure to state from 70.28
140 70.31 18-Mar-1996 Vtreiger Fixed a bug in post_update for
141 terminate_employee procedure.
142 70.32 15-Apr-1996 VTreiger 354874 Included employment category in the
143 explicit insert for per_assignments_f.
144 70.33 15-May-1996 VTreiger 364214 Included additional criteria for
145 function check_for_future_actions
146 70.34 22-May-1996 VTreiger 364214 Restored back to version 70.32.
147 70.35 04-Jun-1996 VTreiger 364214 Added function check_for_compl_actions.
148 This function returns :
149 Y - if there are any assignment
150 actions after the Final Process Date.
151 W - if there exists any completed assignment
152 action after Actual Termination Date but
153 before Final Process Date.
154 N - in all other cases.
155 New function call is used in procedures :
156 terminate_employee,employee_shutdown,
157 cancel_termination.
158 70.36 28-Jun-1996 VTreiger 375157 Commented out nocopy removal of pay proposals
159 when non-recurring entries are processed
160 while employee termination.
161 70.37 11-Oct-1996 VTreiger 441206 Suffix value is saved at termination.
162 70.38 17-Oct-1996 VTreiger 306710 Changed procedure terminate_entries_and
163 alus to return the value of entries
164 changed warning as 'N' or 'Y' or 'S'.
165 398699 Commented out nocopy contents of procedure
166 check_cobra_benefits to return false
167 always.
168 70.39 01-Nov-1996 VTreiger 306710 Added procedure terminate_entries_
169 and_alus overload.
170 70.40 03-APR-1998 DShannon 519738 Added calls to delete_covered_dependents and
171 delete_beneficiaries when entry is terminated
172 - for both recurring and non-recurring entries
173 110.3 30-APR-1998 SASmith Changes made due to date tracking of assignment_budget_
174 values_f.
175 Addition of processing in :
176 1. delete_assign_fpd - include deletion/update of
177 assignment_budget_values_f
178 2. cancel_termination - inclusing of update to assignment_budget
179 values_f when the termination is cancelled.
180 115.1 16-OCT-1998 CCarter New parameters added to insert into per_people_f
181 in the terminate employee procedure for OAB.
182 115.5 19-Jul-1999 mmillmor 895018 removed some bad code and altered to use
183 internal security procedures in terminate
184 115.6 27-Sep-1999 alogue Changed insert into per_People to per_all_people_f
185 inside terminate_employee.
186 115.8 27-OCT-1999 rmonge Added code fixes for bug 920233 and 969491
187 Bug fix 920233 resolves the problem
188 with tax records not reversed when reverse
189 termination process is done on a terminated
190 employee, and bug 969491
191 fixes tax records not end dated when the
192 employee is terminated.
193 115.9 06-MAR-2000 Rvydyana Leapfroged version created for v115.6
194 with backported changes for 1176101
195 115.10 06-MAR-2000 Rvydyana Leapfroged version of v115.8 with code fix
196 for 1176101 ie. Added call to
197 ben_dt_trg_handle in cancel termination.
198 115.11 16-NOV-1999 I Harding 1076054 Added extra attributes to insert into
199 PER_ASSIGNMENTS_F during the post update
200 work following a termination of an
201 employee. Requested by John Rhodes
202 following French Phase 1.
203 115.12 13-MAR-2000 CSimpson Added town_of_birth, region_of_birth,
204 country_of_birth and global_person_id attribs
205 into per_all_people_f insert in terminate_employee.
206 115.13 26-Jul-2000 mmillmor 996015 Added code to delete pay proposals at the same
207 time as element entries
208 115.14 17-Aug-2000 VTreiger 1363723 Commented out nocopy copying of ATTRIBUTEx columns
209 from EMP record to EX_EMP record.
210 115.15 04-Aug-2000 Dscully 1348553 Fixed end dating of tax records.
211 115.16 12-Sep-2000 MReid 889806 Excluded Magnetic Reports from check for
212 future dated completed actions.
213 115.17 15-SEP-00 GPERRY Added hook points to benefits code whenerever
214 per_periods_of_service is inserted or updated.
215 Fixed WWBUG 1390173
216 115.18 15-SEP-00 GPERRY Leapfrog of 115.16 with fix for
217 WWBUG 1390173.
218 115.19 06-OCT-00 TMATHERS 11.5.3 incompatibility, 1348553 is patchset B for
219 payroll, so need to back out nocopy that change to work
220 woth base 11.5.3.
221 115.20 06-OCT-00 TMATHERS Leapfrog of 115.18 including AND requiring fix
222 for 1348553, new paramater to
223 pay_us_update_tax_rec_pkg
224 115.21 19-OCT-00 GPERRY Fixed WWBUG 1408379.
225 Added support for life event triggers for OAB.
226 115.22 23-OCT-00 SBIRNAGE Added an extra line to two where clauses to fix bug
227 1406063.
228 115.23 29-MAR-01 vshukhat Bug 1711085. Commented out nocopy code that disables
229 last_standard_process for US legislature.
230 115.24 09-APR-01 Reverted back some previous changes.
231 115.25 09-MAY-01 MGettins Reverse out nocopy changes for bug 1363723
232 115.26 10-jul-01 rvydyana TAR 1660650.999
233 115.27 06-Jul-01 pbodla - Bug 1877018 : added final_process_date
234 before calling ben_pps_ler.ler_chk
235 to detect potential life events.
236 - Bug 1854968 : Pass old actual termination
237 date to ben_dt_trgr_handle.periods_of_service
238 call.
239 115.28 02-oct-01 vsjain Added notice_period, notice_period_uom,
240 work_at_home,job_post_source_name,
241 employee_category to terminate employee proc
242 115.29 LEAPFROG VERSION BASED ON 115.27
243 115.30 05-Oct-01 mbocutt 1570258 Prevent delete of events which are 'I'nterview
244 as these may be linked to reviews and pay
245 proposals.
246 115.31 30-Oct-01 mbocutt 1271513 Change cancel terminate code to correctly
247 handle future dated assignments on reverse
248 termination.
249 115.32 01-Nov-01 mbocutt Added som emissed variable declarations.
250 115.33 05-NOV-01 kmullapu Modified INSERT INTO PER_ALL_PEOPLE_F in
251 terminate_employee to include
252 PER_INFORMATION 21 TO 30
253 115.35 22-AUG-02 adhunter correct gscc warning. change msg name
254 HR_6519_CANC_ACTIONS_EXIST
255 to HR_6519_EMP_CANC_ACTIONS_EXIST
256 115.38 05-DEC-02 pkakar added nocopy to parameters
257 115.39 16-DEC-02 dcasemor 2711532 Added predicate to exclude BEE
258 payroll actions to:
259 - check_for_compl_actions
260 - check_for_future_actions
261 115.40 10-FEB-02 vramanai 2784295 Removed the code which requires LSPD
262 to be not null for non-US legislations
263 115.42 3-Mar-03 mbocutt 2820633 Changes to reverse term code so that the
264 assignment update made during termination on
265 ATD is removed if there are no differences
266 between it and the previous DT instance of
267 the assignment. Modified behaviour introduced
268 in fix for bug 1271513.
269 115.43 11-Mar-03 mbocutt 2843882 The above fix did not work when ATD=FPD. Adjus
270 ted fix to handle this case.
271 115.44 Sept-2003 mbocutt Ex-person security enhancements.
272 Remove call to copy_to_person_list_changes and
273 other direct refs to PER_PERSON_LIST_CHANGES.
274 This file is now dependent on other
275 security changes delivered in Nov 2003 FP.
276 115.45 17-Oct-2003 smparame 3077943 Procedure Cancel_termination modified.
277 l_final_process_date is set to
278 p_actual_termination_date + 1 if it is equal
279 to p_actual_termination_date before resetting
280 assignment records.
281 115.46 19-FEB-04 smparame 3446768 Cursor rec_entries in terminate_entries_and_alus
282 procedure modified to increase performance.
283 115.47 11-MAR-04 adudekul 3100620. In proc cancel_termination,
284 changed warning message HR_6519_EMP_CANC_ACTIONS_EXIST
285 to PER_449053_EMP_TERM_FUT_ERROR.
286 115.48 11-MAR-04 adudekul 3100620. Modified proc check_for_compl_actions.
287 115.49 02-APR-04 smparame 3077943. Changes made to the 3077943 are reverted.
288 115.50 04-Apr-04 bsubrama 1370960. Added code to revert the roles when
289 termination is reversed.
290 115.51 23-Aug-2004 njaladi 3735333. Modified proc terminate_employee
291 and delete_assign_atd for better performance.
292 115.52 16-SEP-2004 adudekul 3889294. In procedure cancel_termination, added
293 code to manipulate the PTU records, legislation
294 specific actions and hr workflow sync code.
295 115.53 14-SEP-2004 smparame 3983715 Cursor rec_entries in
296 terminate_entries_and_alus procedure
297 modified to increase performance.
298 115.54 16-Jun-2005 ghshanka 4436297 cursor 'future_person_types'
299 in the funtion 'check_for_future_person_type' is
300 modified to query from per_person_type_usages_f table.
301 115.55 27-jun-2005 njaladi Backed out changes done in 115.54 and this version
302 is same as 115.53.
303 115.58 13-Jan-2006 ghshanka 4919804 Modified the procedure cancel_termination to call
304 the procedure pay_pog_all_assignments_pkg.after_delete
305 115.58 16-jan-2006 ghshanka 4919804 Modified the call to pay_pog_all_assignments_pkg.after_delete
306 by passing the end of time value to p_effective_end_date parameter.
307 115.58 17-Jan-2006 ghshanka 4919804 Modified the procedure cancel_termination to call
308 the procedure pay_pog_all_assignments_pkg.after_delete when
309 Finalprocessdate is not null.
310 115.59 22-Mar-2006 LSilveir 4449472 Overloaded terminate_entries_and_alus and added
311 new ALU validation into delete_alus.
312 115.60 04-Apr-2006 ghshanka 4457651 modified the function check_for_future_person_type
313 by adding a new cursor to check for COMBINATION of
314 future person types of type 'CWK and EMP'.
315 115.61 24-Apr-2006 asgugupt 5152164 modified the procedure CANCEL_TERMINATION
316 115.62 09-May-2006 ghshanka 5152164 modified the procedure CANCEL_TERMINATION
317 115.63 19-May-2006 ggnanagu Added the call to adjust_salary_proposals
318 in per_saladmin_utility to fix bug 5200269
319 115.65 14-JUN-2006 avarri 4371218 Modified cancel_termination to fix 4371218
320 115.66 17-JUL-2006 agolechh 4308892 This version of hrempter calls pay_element_entry_api
321 instead of performing direct DML statements on
322 pay_element_entries_f.
323 115.67 26-Jul-2006 thabara 5368246 Modified terminate_entries_and_alus and
324 delete_alus to end date ALUs with final
325 process date regardless of the
326 termination rule.
327 115.68 20-Sep-2006 SNukala 5507290 Modified cancel_termination and added call to
328 maintain EX-EMP incase of canceling termination
329 Leaving reason 'Retirement' type ex-employee.
330 115.69 10-AUG-2007 pchowdav 6313195 Reverted back the changes done for
331 bug 4371218.
332 115.71 07-FEB-2008 ckesanap 6801103 Modified cursor rec_entries in delete_entries(). Also
333 added a condition - to call pay_element_entry_api in
334 DELETE mode only if p_term_date is not equal to
335 max_effective_end_date for the record.
336 ================================================================= */
337 g_package varchar2(33) := ' hrempter.'; -- Global package name
338
339 --
340 -- ====================== delete_assign_atd =========================
341 --
342 PROCEDURE delete_assign_atd(p_assignment_id NUMBER
343 ,p_actual_termination_date DATE)
344 IS
345 --
346 l_proc varchar2(72):=g_package||'delete_assign_atd';
347 begin
348 hr_utility.trace('Entered delete_assign_atd for assign '||p_assignment_id);
349 --
350 hr_utility.set_location(l_proc,1);
351 -- Remove booking and event where the event is for the assignment
352 --
353 --
354 -- 3735333 Modified the query of per_bookings for better performance.
355 --
356 --
357 DELETE per_bookings pb
358 WHERE event_id in ( SELECT event_id
359 FROM per_events pev
360 WHERE pev.assignment_id = p_assignment_id
361 AND pev.date_start > p_actual_termination_date
362 );
363 --
364 hr_utility.set_location(l_proc,2);
365 DELETE per_events pev
366 WHERE pev.assignment_id = p_assignment_id
367 AND pev.date_start > p_actual_termination_date
368 AND pev.event_or_interview = 'E';
369 --
370 hr_utility.set_location(l_proc,3);
371 DELETE per_letter_request_lines lrl
372 WHERE lrl.assignment_id = p_assignment_id
373 AND lrl.date_from > p_actual_termination_date;
374 --
375 end delete_assign_atd;
376 --
377 --
378 -- ====================== delete_assign_fpd =========================
379 --
380 PROCEDURE delete_assign_fpd(p_assignment_id NUMBER
381 ,p_final_process_date DATE)
382 IS
383 --
384 l_proc varchar2(72):=g_package||'delete_assign_fpd';
385 --
386 -- Start of Fix for WWBUG 1408379
387 --
388 l_old ben_abv_ler.g_abv_ler_rec;
389 l_new ben_abv_ler.g_abv_ler_rec;
390 --
391 cursor c1 is
392 select *
393 from per_assignment_budget_values_f abv
394 where abv.assignment_id = p_assignment_id
395 and p_final_process_date
396 between abv.effective_start_date
397 and abv.effective_end_date;
398 --
399 l_c1 c1%rowtype;
400 --
401 -- End of Fix for WWBUG 1408379
402 --
403 begin
404 hr_utility.trace('Entered delete_assign_fpf for assign '||p_assignment_id);
405 --
406 hr_utility.set_location(l_proc,1);
407 UPDATE per_secondary_ass_statuses sas
408 SET sas.end_date = p_final_process_date
409 WHERE sas.assignment_id = p_assignment_id
410 AND sas.end_date IS NULL;
411 --
412 hr_utility.set_location(l_proc,5);
413 DELETE per_secondary_ass_statuses sas
414 WHERE sas.assignment_id = p_assignment_id
415 AND sas.start_date > p_final_process_date;
416 --
417 hr_utility.set_location(l_proc,10);
418 UPDATE pay_personal_payment_methods_f ppm
419 SET ppm.effective_end_date = p_final_process_date
420 WHERE ppm.assignment_id = p_assignment_id
421 AND p_final_process_date
422 BETWEEN ppm.effective_start_date
423 AND ppm.effective_end_date;
424 --
425 hr_utility.set_location(l_proc,15);
426 DELETE pay_personal_payment_methods_f ppm
427 WHERE ppm.assignment_id = p_assignment_id
428 AND ppm.effective_start_date > p_final_process_date;
429 --
430 hr_utility.set_location(l_proc,20);
431 UPDATE pay_cost_allocations_f pca
432 SET pca.effective_end_date = p_final_process_date
433 WHERE pca.assignment_id = p_assignment_id
434 AND p_final_process_date
435 BETWEEN pca.effective_start_date
436 AND pca.effective_end_date;
437 --
438 hr_utility.set_location(l_proc,25);
439 DELETE pay_cost_allocations_f pca
440 WHERE pca.assignment_id = p_assignment_id
441 AND pca.effective_start_date > p_final_process_date;
442 --
443 hr_utility.set_location(l_proc,30);
444 UPDATE per_spinal_point_placements_f spp
445 SET spp.effective_end_date = p_final_process_date
446 WHERE spp.assignment_id = p_assignment_id
447 AND p_final_process_date
448 BETWEEN spp.effective_start_date
449 AND spp.effective_end_date;
450 --
451 -- VT 03/06/96 bug #311763
452 hr_utility.set_location(l_proc,35);
453 DELETE per_spinal_point_placements_f spp
454 WHERE spp.assignment_id = p_assignment_id
455 AND spp.effective_start_date > p_final_process_date;
456 --
457
458 -- Due to date tracking of assignment_budget_values.
459 -- SASmith 30-APR-1998
460
461 hr_utility.set_location(l_proc,40);
462 DELETE per_assignment_budget_values_f abv
463 WHERE abv.assignment_id = p_assignment_id
464 AND abv.effective_start_date > p_final_process_date;
465 --
466 hr_utility.set_location(l_proc,45);
467 --
468 -- Start of FIX for WWBUG 1408379
469 --
470 open c1;
471 --
472 loop
473 --
474 fetch c1 into l_c1;
475 exit when c1%notfound;
476 --
477 l_old.assignment_id := l_c1.assignment_id;
478 l_old.business_group_id := l_c1.business_group_id;
479 l_old.value := l_c1.value;
480 l_old.assignment_budget_value_id := l_c1.assignment_budget_value_id;
481 l_old.effective_start_date := l_c1.effective_start_date;
482 l_old.effective_end_date := l_c1.effective_end_date;
483 l_new.assignment_id := l_c1.assignment_id;
484 l_new.business_group_id := l_c1.business_group_id;
485 l_new.value := l_c1.value;
486 l_new.assignment_budget_value_id := l_c1.assignment_budget_value_id;
487 l_new.effective_start_date := l_c1.effective_start_date;
488 l_new.effective_end_date := p_final_process_date;
489 --
490 update per_assignment_budget_values_f abv
491 set abv.effective_end_date = p_final_process_date
492 where abv.assignment_budget_value_id = l_c1.assignment_budget_value_id
493 and abv.effective_start_date = l_c1.effective_start_date
494 and abv.effective_end_date = l_c1.effective_end_date;
495 --
496 ben_abv_ler.ler_chk(p_old => l_old,
497 p_new => l_new,
498 p_effective_date => l_c1.effective_start_date);
499 --
500 end loop;
501 --
502 close c1;
503 --
504 -- End of FIX for WWBUG 1408379
505 --
506 end delete_assign_fpd;
507 --
508 --
509 -- ====================== delete_employee_atd ==========================
510 --
511 --
512 PROCEDURE delete_employee_atd(p_person_id NUMBER
513 ,p_actual_termination_date DATE)
514 IS
515 --
516 l_proc varchar2(72):=g_package||'delete_employee_atd';
517 begin
518 hr_utility.trace('Entered delete_employee_atd for person '||p_person_id);
519 --
520 hr_utility.set_location(l_proc,1);
521 DELETE per_absence_attendances paa
522 WHERE paa.person_id = p_person_id
523 AND paa.date_start > p_actual_termination_date;
524 --
525 hr_utility.set_location(l_proc,5);
526 -- Remove bookings on employee events
527 DELETE per_bookings pb
528 WHERE pb.person_id = p_person_id
529 AND EXISTS (SELECT ''
530 FROM per_events pev
531 WHERE pev.date_start > p_actual_termination_date
532 AND pev.emp_or_apl = 'E'
533 AND pev.event_or_interview = 'E'
534 AND pb.event_id = pev.event_id);
535 --
536 hr_utility.set_location(l_proc,7);
537 -- Remove bookings as an interviewer for either employee or applicant
538 -- interviews
539 DELETE per_bookings pb
540 WHERE pb.person_id = p_person_id
541 AND EXISTS (SELECT ''
542 FROM per_events pev
543 WHERE pev.event_id = pb.event_id
544 AND pev.date_start > p_actual_termination_date
545 AND pev.event_or_interview = 'I'
546 AND pev.assignment_id IS NOT NULL
547 AND pev.assignment_id NOT IN (SELECT assignment_id
548 FROM per_assignments_f
549 WHERE person_id = p_person_id
550 )
551 );
552 --
553 hr_utility.set_location(l_proc,10);
554 DELETE per_letter_request_lines lrl
555 WHERE lrl.person_id = p_person_id
556 AND lrl.date_from > p_actual_termination_date;
557 --
558 end delete_employee_atd;
559 --
560 --
561 -- ====================== delete_de_assign ==========================
562 --
563 --
564 PROCEDURE delete_de_assign(p_assignment_id NUMBER
565 ,p_delete_date DATE )
566
567 IS
568 --
569 l_proc varchar2(72):=g_package||'delete_de_assign';
570 --
571 begin
572 hr_utility.trace('Entered delete_de_assign for '||p_assignment_id);
573 --
574 hr_utility.set_location(l_proc,1);
575 DELETE per_assignments_f ass
576 WHERE ass.assignment_id = p_assignment_id
577 AND ass.effective_start_date > p_delete_date;
578 --
579 end delete_de_assign;
580 --
581 --
582 -- ======================= get_max_end_date =========================
583 --
584 --
585 FUNCTION get_max_end_date(p_assignment_id NUMBER) return DATE
586 IS
587 --
588 l_max_end_date DATE;
589 --
590 l_proc varchar2(72):=g_package||'get_max_end_date';
591 begin
592 hr_utility.trace('Entered get_max_end_date for '||p_assignment_id);
593 --
594 hr_utility.set_location(l_proc,1);
595 SELECT max(ass.effective_end_date)
596 INTO l_max_end_date
597 FROM per_assignments_f ass
598 WHERE ass.assignment_id = p_assignment_id;
599 --
600 return l_max_end_date;
601 --
602 end get_max_end_date;
603 --
604 --
605 -- ====================== check_for_future_actions ===================
606 --
607 --
608 FUNCTION check_for_future_actions(p_person_id NUMBER
609 ,p_action_date DATE) RETURN VARCHAR2
610 IS
611 --
612 l_action_chk VARCHAR2(1) := 'N';
613 --
614 l_proc varchar2(72):=g_package||'check_for_future_actions';
615 begin
616 hr_utility.trace('Entered check_for_future_actions for '||p_person_id);
617 --
618 hr_utility.set_location(l_proc,1);
619 -- VT 05/15/96 added criteria for action_status and action_type
620 -- AND pac.action_status = 'C'
621 -- AND pac.action_type IN ('R','Q')
622 -- VT 05/22/96 restored back to version 70.32
623 SELECT 'Y'
624 INTO l_action_chk
625 FROM sys.dual
626 WHERE exists
627 (SELECT null
628 FROM pay_payroll_actions pac,
629 pay_assignment_actions act,
630 per_assignments_f asg
631 WHERE asg.person_id = p_person_id
632 AND act.assignment_id = asg.assignment_id
633 AND pac.payroll_action_id = act.payroll_action_id
634 AND pac.action_type <> 'BEE'
635 AND pac.effective_date > p_action_date);
636 --
637 hr_utility.set_location(l_proc,5);
638 return l_action_chk;
639 --
640 exception when NO_DATA_FOUND then null;
641 return 'N';
642 --
643 end check_for_future_actions;
644 --
645 --
646 -- ====================== check_for_compl_actions ===================
647 --
648 -- VT 06/04/96 #364214 added new function to check for future
649 -- completed actions
650 FUNCTION check_for_compl_actions(p_person_id NUMBER
651 ,p_act_date DATE
652 ,p_lsp_date DATE
653 ,p_fpr_date DATE) RETURN VARCHAR2
654 IS
655 --
656 l_action_chk VARCHAR2(1) := 'N';
657 l_action_date DATE;
658 --
659 l_proc varchar2(72):=g_package||'check_for_compl_actions';
660 begin
661 hr_utility.trace('Entered check_for_compl_actions for '||p_person_id);
662 --
663 IF p_lsp_date IS NOT NULL THEN
664 --
665 -- For bug 3100620. Added = in the following check.
666 --
667 IF p_act_date IS NOT NULL AND p_lsp_date >= p_act_date THEN
668 l_action_date := p_lsp_date;
669 ELSE
670 l_action_date := NULL;
671 END IF;
672 ELSE
673 l_action_date := p_act_date;
674 END IF;
675 hr_utility.set_location(l_proc,1);
676 BEGIN
677 SELECT 'Y'
678 INTO l_action_chk
679 FROM sys.dual
680 WHERE exists
681 (SELECT null
682 FROM pay_payroll_actions pac,
683 pay_assignment_actions act,
684 per_assignments_f asg
685 WHERE asg.person_id = p_person_id
686 AND act.assignment_id = asg.assignment_id
687 AND pac.payroll_action_id = act.payroll_action_id
688 AND pac.action_type NOT IN ('X','BEE') -- Bug 889806, 2711532
689 AND pac.effective_date > p_fpr_date);
690 exception when NO_DATA_FOUND then null;
691 END;
692 --
693 hr_utility.set_location(l_proc,5);
694 IF l_action_chk = 'N' THEN
695 BEGIN
696 SELECT 'W'
697 INTO l_action_chk
698 FROM sys.dual
699 WHERE exists
700 (SELECT null
701 FROM pay_payroll_actions pac,
702 pay_assignment_actions act,
703 per_assignments_f asg
704 WHERE asg.person_id = p_person_id
705 AND act.assignment_id = asg.assignment_id
706 AND pac.payroll_action_id = act.payroll_action_id
707 AND pac.action_status = 'C'
708 AND pac.action_type <> 'BEE'
709 AND (pac.effective_date BETWEEN l_action_date AND p_fpr_date));
710 --
711 hr_utility.set_location(l_proc,7);
712 exception when NO_DATA_FOUND then null;
713 END;
714 END IF;
715 return l_action_chk;
716 --
717 end check_for_compl_actions;
718 --
719 -- ====================== check_for_future_person_records ===================
720 --
721 --
722 FUNCTION check_for_future_person_rows(p_person_id NUMBER
723 ,p_action_date DATE) RETURN VARCHAR2
724 IS
725 --
726 l_action_chk VARCHAR2(1) := 'N';
727 --
728 l_proc varchar2(72):=g_package||'check_for_future_person_rows';
729 begin
730 --
731 hr_utility.set_location(l_proc,5);
732 --
733 SELECT 'Y'
734 INTO l_action_chk
735 FROM sys.dual
736 WHERE exists
737 (select '1'
738 from per_people_f ppf
739 where ppf.person_id = p_person_id
740 and p_action_date < ppf.effective_start_date);
741 --
742 hr_utility.set_location(l_proc,10);
743 --
744 return l_action_chk;
745 --
746 exception when NO_DATA_FOUND then null;
747 --
748 return 'N';
749 --
750 end check_for_future_person_rows;
751 --
752 --
753 -- ====================== check_for_future_person_type ===================
754 --
755 --
756 FUNCTION check_for_future_person_type(p_person_id NUMBER
757 ,p_action_date DATE) RETURN VARCHAR2
758 IS
759 --
760 CURSOR future_person_types IS
761 SELECT ppt.system_person_type
762 FROM per_people_f ppf,
763 per_person_types ppt
764 WHERE ppf.person_type_id = ppt.person_type_id
765 AND ppf.person_id = p_person_id
766 AND ppf.business_group_id + 0 = ppt.business_group_id + 0
767 AND p_action_date < ppf.effective_start_date;
768 --
769 --bug 4457651
770 --
771 L_TYPE VARCHAR2(100);
772 l_bus_id per_all_people_f.business_group_id %type;
773
774 CURSOR fptypes_for_cwk1 IS
775 SELECT ppt.system_person_type
776 FROM
777 per_person_types ppt ,
778 per_person_type_usages_f pptf
779 WHERE
780 pptf.person_id = p_person_id
781 AND p_action_date < pptf.effective_start_date
782 AND pptf.person_type_id = ppt.person_type_id
783 and ppt.business_group_id=l_bus_id
784 and ppt.system_person_type like 'CWK';
785
786 cursor bus_grp_id is
787 select business_group_id
788 from per_people_f ppf
789 where person_id=p_person_id
790 and p_action_date < ppf.effective_start_date;
791 --
792 -- bug 4457651
793
794 fpt_rec future_person_types%ROWTYPE;
795 fpt_rec1 fptypes_for_cwk1%ROWTYPE;
796 l_action_chk VARCHAR2(1) := 'N';
797 l_ex_emp VARCHAR2(1) := 'N';
798 l_ex_emp_apl VARCHAR2(1) := 'N';
799 --
800 l_proc varchar2(72):=g_package||'check_for_future_person_type';
801 begin
802 --
803 hr_utility.set_location(l_proc, 10);
804 --
805 -- #289454 Allow cancel termination to succeed if future person type changes
806 -- are to EX_EMP_APL as well as to EX_EMP, providing both types don't exist
807 -- for the same person. This allows terminations to be cancelled for
808 -- Employee-Applicants.
809 --
810 FOR fpt_rec IN future_person_types LOOP
811 --
812 hr_utility.set_location(l_proc, 20);
813 --
814 IF fpt_rec.system_person_type = 'EX_EMP' THEN
815 --
816 hr_utility.set_location(l_proc, 30);
817 --
818 l_ex_emp := 'Y';
819 ELSIF fpt_rec.system_person_type = 'EX_EMP_APL' THEN
820 --
821 hr_utility.set_location(l_proc, 40);
822 --
823 l_ex_emp_apl := 'Y';
824 ELSE
825 --
826 -- Found a person type which will prohibit the cancel termination.
827 -- Can bale out now.
828 --
829 hr_utility.set_location(l_proc, 50);
830 --
831 l_action_chk := 'Y';
832 exit;
833 END IF;
834 --
835 -- Now check if we've found both types: that's an error too.
836 --
837 IF l_ex_emp = 'Y' AND l_ex_emp_apl = 'Y' THEN
838 --
839 hr_utility.set_location(l_proc, 60);
840 --
841 l_action_chk := 'Y';
842 exit;
843 END IF;
844 END LOOP;
845 --
846 -- bug 4457651
847 --
848 open bus_grp_id ;
849 fetch bus_grp_id into l_bus_id ;
850 close bus_grp_id ;
851
852 hr_utility.set_location('thisis the bus id '||l_bus_id,100);
853 hr_utility.set_location('this is the action date '||p_action_date,110);
854 hr_utility.set_location('this is personid value '||p_person_id,115);
855
856 open fptypes_for_cwk1;
857 loop
858 fetch fptypes_for_cwk1 into l_type;
859 exit when fptypes_for_cwk1%notfound;
860 hr_utility.set_location('this isthe value of'||L_TYPE, 140);
861
862 if (l_type = 'CWK' and l_ex_emp='Y')
863 THEN
864 l_action_chk := 'Y';
865 END IF;
866 end loop;
867 close fptypes_for_cwk1;
868 --
869 -- bug 4457651
870 --
871 hr_utility.set_location(l_proc, 70);
872 --
873 return l_action_chk;
874 --
875 end check_for_future_person_type;
876 --
877 -- ====================== check_cobra_benefits =========================
878 --
879 -- If the FPD has been entered a check is required to ensure that any
880 -- COBRA Coverage Benefits do exist after the proposed Final Process Date.
881 --
882 FUNCTION check_cobra_benefits(p_person_id NUMBER
883 ,p_final_process_date DATE) RETURN BOOLEAN
884 IS
885 --
886 l_cobra_benefits_exist VARCHAR2(1) := 'N';
887 --
888 l_proc varchar2(72):=g_package||'check_cobra_benefits';
889 begin
890 hr_utility.set_location(l_proc,10);
891 -- VT 10/18/96 bug #398699 commented out
892 -- begin
893 -- select 'Y'
894 -- into l_cobra_benefits_exist
895 -- from sys.dual
896 -- where exists
897 -- (select null
898 -- from per_assignments_f a
899 -- , per_cobra_cov_enrollments e
900 -- , per_cobra_coverage_benefits_f b
901 -- where a.person_id = p_person_id
902 -- and p_final_process_date between
903 -- a.effective_start_date and a.effective_end_date
904 -- and e.assignment_id = a.assignment_id
905 -- and e.cobra_coverage_enrollment_id
906 -- = b.cobra_coverage_enrollment_id
907 -- and b.effective_end_date > p_final_process_date);
908 -- exception
909 -- when no_data_found then null;
910 -- end;
911 --
912 return (l_cobra_benefits_exist = 'Y');
913 end check_cobra_benefits;
914 --
915 --
916 -- =================== terminate_entries_and_alus overload ============
917 -- VT 11/01/96 #306710 procedure overload
918 PROCEDURE terminate_entries_and_alus(p_assignment_id NUMBER,
919 p_actual_term_date DATE,
920 p_last_standard_date DATE,
921 p_final_process_date DATE,
922 p_legislation_code VARCHAR2 DEFAULT
923 NULL)
924 IS
925 --
926 l_entries_changed_ov VARCHAR2(1) := 'N';
927 BEGIN
928 terminate_entries_and_alus(p_assignment_id
929 ,p_actual_term_date
930 ,p_last_standard_date
931 ,p_final_process_date
932 ,p_legislation_code
933 ,l_entries_changed_ov);
934 END terminate_entries_and_alus;
935 --
936 -- 115.59 (START)
937 --
938 -- =================== terminate_entries_and_alus overload ===================
939 --
940 PROCEDURE terminate_entries_and_alus(p_assignment_id NUMBER,
941 p_actual_term_date DATE,
942 p_last_standard_date DATE,
943 p_final_process_date DATE,
944 p_legislation_code VARCHAR2 DEFAULT
945 NULL,
946 p_entries_changed_warning
947 IN OUT NOCOPY VARCHAR2)
948 IS
949 --
950 l_alu_change_warning_ovl VARCHAR2(1) := 'N';
951 --
952 BEGIN
953 --
954 terminate_entries_and_alus(p_assignment_id
955 ,p_actual_term_date
956 ,p_last_standard_date
957 ,p_final_process_date
958 ,p_legislation_code
959 ,p_entries_changed_warning
960 ,l_alu_change_warning_ovl);
961 --
962 END terminate_entries_and_alus;
963 --
964 -- 115.59 (END)
965 --
966 -- ========================= terminate_entries_and_alus ======================
967 --
968 PROCEDURE terminate_entries_and_alus(p_assignment_id NUMBER,
969 p_actual_term_date DATE,
970 p_last_standard_date DATE,
971 p_final_process_date DATE,
972 p_legislation_code VARCHAR2 DEFAULT
973 NULL,
974 p_entries_changed_warning
975 --
976 -- 115.59 (START)
977 --
978 --IN OUT NOCOPY VARCHAR2)
979 IN OUT NOCOPY VARCHAR2,
980 p_alu_change_warning
981 IN OUT NOCOPY VARCHAR2)
982 --
983 -- 115.59 (END)
984 --
985 IS
986 --
987 -- VT 10/07/96 bug #306710
988 -- p_entries_changed_warning = N no entries changed
989 -- p_entries_changed_warning = Y non_salary entries changed
990 -- p_entries_changed_warning = S salary entries changed
991 --
992 l_entries_changed VARCHAR2(1) := 'N';
993 l_cur_entries VARCHAR2(1) := 'N';
994 l_cur_alus VARCHAR2(1) := 'N';
995 l_proc varchar2(72):=g_package||'terminate_entries_and_alus';
996 --
997 PROCEDURE delete_entries(p_assignment_id NUMBER,
998 p_term_date DATE,
999 p_term_rule VARCHAR2,
1000 p_final_process_date DATE,
1001 p_entries_changed_warning IN OUT
1002 VARCHAR2) IS
1003 --
1004 -- Cursor to return all recurring element entries for the assignment that
1005 -- are for an element with the correct post termination rule and also exists
1006 -- after the termination date
1007
1008 -- bug fix 3446768. Cursor modified to increase
1009 -- performance.
1010 -- bug fix 3983715. Cursor modified to improve execution
1011 -- time.cursor split into two.
1012 -- bug fix 4308892. Cursor modified to return min ee.effective_start_date,
1013 -- which is required for element entry API call.
1014 -- 6801103. Modified the effective_end_date condition.
1015 CURSOR rec_entries(p_assignment_id NUMBER,
1016 p_term_date DATE
1017 ) IS
1018 SELECT ee.element_entry_id,
1019 ee.element_link_id,
1020 ee.element_type_id,
1021 MIN(ee.effective_start_date) effective_start_date,
1022 MAX(ee.effective_end_date) effective_end_date
1023 FROM pay_element_entries_f ee
1024 WHERE ee.assignment_id = p_assignment_id
1025 AND ee.effective_end_date >= p_term_date
1026 AND ee.entry_type = 'E'
1027 GROUP BY ee.element_entry_id, ee.element_link_id,ee.element_type_id;
1028
1029 CURSOR chk_element_type_csr(p_element_type_id number,
1030 p_term_rule varchar2 ) IS
1031 SELECT 'Y'
1032 FROM pay_element_types_f et
1033 WHERE et.element_type_id = p_element_type_id
1034 AND et.post_termination_rule = p_term_rule
1035 AND et.processing_type = 'R';
1036 --
1037 -- Cursor to return all nonrecurring element entries for the assignment that
1038 -- are for an element with the correct post termination rule and also exists
1039 -- after the termination date
1040 -- bug fix 4308892. Cursor modified to return min ee.effective_start_date and
1041 -- max ee.effective_end_date which are required for element entry API call.
1042 CURSOR nonrec_entries(p_assignment_id NUMBER,
1043 p_term_date DATE,
1044 p_term_rule VARCHAR2) IS
1045 SELECT ee.element_entry_id,
1046 ee.element_link_id,
1047 min(ee.effective_start_date) effective_start_date,
1048 max(ee.effective_end_date) effective_end_date
1049 FROM pay_element_entries_f ee
1050 WHERE ee.assignment_id = p_assignment_id
1051 AND ee.effective_end_date > p_term_date
1052 AND ((ee.entry_type <> 'E')
1053 OR (ee.entry_type = 'E'
1054 AND EXISTS (SELECT NULL
1055 FROM pay_element_links_f el,
1056 pay_element_types_f et
1057 WHERE el.element_link_id = ee.element_link_id
1058 AND et.element_type_id = el.element_type_id
1059 AND et.processing_type = 'N')))
1060 AND EXISTS (SELECT NULL
1061 FROM pay_element_links_f el,
1062 pay_element_types_f et
1063 WHERE el.element_link_id = ee.element_link_id
1064 AND et.element_type_id = el.element_type_id
1065 AND et.post_termination_rule = p_term_rule)
1066 GROUP BY ee.element_entry_id, ee.element_link_id;
1067 --
1068 -- VT #553177 12/11/97
1069 -- Cursor to return the current period end for
1070 -- nonrecurring element entry
1071 CURSOR period_end (p_asg_id in NUMBER,
1072 p_start_date in DATE) IS
1073 SELECT ptp.end_date
1074 FROM per_time_periods ptp,
1075 per_all_assignments_f paaf
1076 WHERE paaf.assignment_id = p_asg_id
1077 AND p_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1078 AND paaf.payroll_id = ptp.payroll_id
1079 AND p_start_date BETWEEN ptp.start_date AND ptp.end_date;
1080 --
1081 CURSOR get_pay_proposals(p_assignment_id number
1082 ,p_term_date date
1083 ,p_term_rule varchar2) is
1084 select ppp.pay_proposal_id
1085 , ppp.multiple_components
1086 from per_pay_proposals ppp
1087 , per_all_assignments_f asg
1088 , per_pay_bases ppb
1089 , pay_element_types_f pet
1090 , pay_input_values_f piv
1091 where ppp.assignment_id=p_assignment_id
1092 and asg.assignment_id=p_assignment_id
1093 and ppp.change_date between asg.effective_start_date and asg.effective_end_date
1094 and ppb.pay_basis_id=asg.pay_basis_id
1095 and ppb.input_value_id=piv.input_value_id
1096 and ppp.change_date between piv.effective_start_date and piv.effective_end_date
1097 and piv.element_type_id=pet.element_type_id
1098 and ppp.change_date between pet.effective_start_date and pet.effective_end_date
1099 and pet.post_termination_rule = p_term_rule
1100 and ppp.change_date>p_term_date;
1101
1102 -- Local Constants
1103 c_eot constant date := to_date('31/12/4712','DD/MM/YYYY');
1104 l_last_update_date constant date := trunc(sysdate);
1105 --
1106 -- Local variables
1107 l_element_type_id NUMBER;
1108 -- VT 02/27/96 variable to check current value of process_in_run_flag
1109 -- VT 02/27/96 bug #334654,#334681
1110 l_process_in_run_flag VARCHAR2(1);
1111 -- VT 10/07/96 bug #306710
1112 l_entries_changed_warning VARCHAR2(1) := 'N';
1113 --
1114 l_y_chng number := 0;
1115 l_s_chng number := 0;
1116 l_ret_chng VARCHAR2(1) := 'N';
1117 --
1118 l_current_period_end DATE;
1119 l_type_exists varchar2(1);
1120 --
1121 -- Bug fix 4308892
1122 -- Variables added for element entry API calls...
1123 l_ee_object_version_number number := null;
1124 l_ee_effective_start_date date := null;
1125 l_ee_effective_end_date date := null;
1126 l_delete_warning boolean;
1127 --
1128 l_proc varchar2(72):=g_package||'delete_entries';
1129 BEGIN
1130 -- VT 10/07/96 bug #306710 added new loops for element entries
1131 FOR rec_entry IN rec_entries(p_assignment_id,
1132 p_term_date
1133 )
1134 LOOP
1135
1136 -- Bug fix 3983715.
1137 -- Cursor to check whether the type exists.
1138
1139 OPEN chk_element_type_csr( rec_entry.element_type_id,p_term_rule);
1140 FETCH chk_element_type_csr INTO l_type_exists;
1141 CLOSE chk_element_type_csr;
1142
1143 IF nvl(l_type_exists,'N') = 'Y' THEN
1144
1145 l_type_exists := 'N';
1146
1147 SELECT el.element_type_id
1148 INTO l_element_type_id
1149 FROM pay_element_links_f el
1150 WHERE el.element_link_id = rec_entry.element_link_id
1151 AND rec_entry.effective_end_date BETWEEN el.effective_start_date
1152 AND el.effective_end_date;
1153 hr_entry.chk_element_entry_open(l_element_type_id,
1154 p_term_date,
1155 p_term_date,
1156 rec_entry.effective_end_date,
1157 p_assignment_id);
1158 BEGIN
1159 l_ret_chng := 'N';
1160 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1161 (SELECT 'Y' FROM pay_element_entry_values_f eev
1162 WHERE eev.element_entry_id = rec_entry.element_entry_id
1163 AND eev.effective_start_date > p_term_date);
1164 EXCEPTION
1165 WHEN NO_DATA_FOUND THEN NULL;
1166 END;
1167 IF l_ret_chng = 'Y' THEN
1168 l_y_chng := l_y_chng + 1;
1169 END IF;
1170 BEGIN
1171 l_ret_chng := 'N';
1172 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1173 (SELECT 'Y' FROM pay_element_entries_f ee
1174 WHERE ee.element_entry_id = rec_entry.element_entry_id
1175 AND ee.effective_start_date > p_term_date);
1176 EXCEPTION
1177 WHEN NO_DATA_FOUND THEN NULL;
1178 END;
1179 IF l_ret_chng = 'Y' THEN
1180 l_y_chng := l_y_chng + 1;
1181 END IF;
1182 BEGIN
1183 l_ret_chng := 'N';
1184 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1185 (SELECT 'Y' FROM pay_element_entry_values_f eev
1186 WHERE eev.element_entry_id = rec_entry.element_entry_id
1187 AND eev.effective_end_date > p_term_date);
1188 EXCEPTION
1189 WHEN NO_DATA_FOUND THEN NULL;
1190 END;
1191 IF l_ret_chng = 'Y' THEN
1192 l_y_chng := l_y_chng + 1;
1193 END IF;
1194 BEGIN
1195 l_ret_chng := 'N';
1196 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1197 (SELECT 'Y' FROM pay_element_entries_f ee
1198 WHERE ee.element_entry_id = rec_entry.element_entry_id
1199 AND ee.effective_end_date > p_term_date);
1200 EXCEPTION
1201 WHEN NO_DATA_FOUND THEN NULL;
1202 END;
1203 IF l_ret_chng = 'Y' THEN
1204 l_y_chng := l_y_chng + 1;
1205 END IF;
1206 BEGIN
1207 l_ret_chng := 'N';
1208 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1209 (SELECT 'Y' FROM pay_element_entries_f ee
1210 WHERE ee.element_entry_id = rec_entry.element_entry_id
1211 AND ee.creator_type = 'SP'
1212 AND ee.effective_start_date > p_term_date);
1213 EXCEPTION
1214 WHEN NO_DATA_FOUND THEN NULL;
1215 END;
1216 IF l_ret_chng = 'Y' THEN
1217 l_s_chng := l_s_chng + 1;
1218 END IF;
1219 BEGIN
1220 l_ret_chng := 'N';
1221 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1222 (SELECT 'Y' FROM pay_element_entries_f ee
1223 WHERE ee.element_entry_id = rec_entry.element_entry_id
1224 AND ee.creator_type = 'SP'
1225 AND ee.effective_end_date > p_term_date);
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN NULL;
1228 END;
1229 IF l_ret_chng = 'Y' THEN
1230 l_s_chng := l_s_chng + 1;
1231 END IF;
1232 END IF; -- if l_type_exists
1233 END LOOP; -- FOR rec_entry IN ...
1234 --
1235 IF (l_y_chng + l_s_chng) = 0 THEN
1236 FOR nonrec_entry IN nonrec_entries(p_assignment_id,
1237 p_term_date,
1238 p_term_rule) LOOP
1239 l_process_in_run_flag := 'N';
1240 SELECT el.element_type_id, et.process_in_run_flag
1241 INTO l_element_type_id, l_process_in_run_flag
1242 FROM pay_element_links_f el, pay_element_types_f et
1243 WHERE el.element_link_id = nonrec_entry.element_link_id
1244 AND et.element_type_id = el.element_type_id
1245 AND nonrec_entry.effective_end_date BETWEEN el.effective_start_date
1246 AND el.effective_end_date
1247 /* Bug 1406063 */
1248 AND nonrec_entry.effective_end_date BETWEEN et.effective_start_date
1249 AND et.effective_end_date;
1250 /* End Bug 1406063 */
1251 hr_entry.chk_element_entry_open(l_element_type_id,
1252 nonrec_entry.effective_start_date,
1253 nonrec_entry.effective_start_date,
1254 nonrec_entry.effective_end_date,
1255 p_assignment_id);
1256 IF nonrec_entry.effective_start_date > p_term_date THEN
1257 IF l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') THEN
1258 hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1259 hr_utility.raise_error;
1260 EXIT;
1261 ELSE
1262 BEGIN
1263 l_ret_chng := 'N';
1264 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1265 (SELECT 'Y' FROM pay_element_entry_values_f eev
1266 WHERE eev.element_entry_id = nonrec_entry.element_entry_id);
1267 EXCEPTION
1268 WHEN NO_DATA_FOUND THEN NULL;
1269 END;
1270 IF l_ret_chng = 'Y' THEN
1271 l_y_chng := l_y_chng + 1;
1272 END IF;
1273 BEGIN
1274 l_ret_chng := 'N';
1275 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1276 (SELECT 'Y' FROM pay_element_entries_f ee
1277 WHERE ee.element_entry_id = nonrec_entry.element_entry_id);
1278 EXCEPTION
1279 WHEN NO_DATA_FOUND THEN NULL;
1280 END;
1281 IF l_ret_chng = 'Y' THEN
1282 l_y_chng := l_y_chng + 1;
1283 END IF;
1284 BEGIN
1285 l_ret_chng := 'N';
1286 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1287 (SELECT 'Y' FROM pay_element_entries_f ee
1288 WHERE ee.element_entry_id = nonrec_entry.element_entry_id
1289 AND ee.creator_type = 'SP');
1290 EXCEPTION
1291 WHEN NO_DATA_FOUND THEN NULL;
1292 END;
1293 IF l_ret_chng = 'Y' THEN
1294 l_s_chng := l_s_chng + 1;
1295 END IF;
1296 END IF;
1297 ELSIF nonrec_entry.effective_start_date <= p_term_date AND
1298 nonrec_entry.effective_end_date > nvl(p_term_date,c_eot) THEN
1299 IF p_final_process_date IS NULL THEN
1300 BEGIN
1301 l_ret_chng := 'N';
1302 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1303 (SELECT 'Y' FROM pay_element_entry_values_f eev
1304 WHERE eev.element_entry_id = nonrec_entry.element_entry_id
1305 AND eev.effective_end_date = nonrec_entry.effective_end_date);
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN NULL;
1308 END;
1309 IF l_ret_chng = 'Y' THEN
1310 l_y_chng := l_y_chng + 1;
1311 END IF;
1312 ELSE
1313 IF p_term_rule IN ('A','L','F') THEN
1314 BEGIN
1315 l_ret_chng := 'N';
1316 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1317 (SELECT 'Y' FROM pay_element_entries_f ee
1318 WHERE ee.element_entry_id = nonrec_entry.element_entry_id
1319 AND ee.effective_end_date = nonrec_entry.effective_end_date);
1320 EXCEPTION
1321 WHEN NO_DATA_FOUND THEN NULL;
1322 END;
1323 IF l_ret_chng = 'Y' THEN
1324 l_y_chng := l_y_chng + 1;
1325 END IF;
1326 BEGIN
1327 l_ret_chng := 'N';
1328 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1329 (SELECT 'Y' FROM pay_element_entries_f ee
1330 WHERE ee.element_entry_id = nonrec_entry.element_entry_id
1331 AND ee.creator_type = 'SP'
1332 AND ee.effective_end_date = nonrec_entry.effective_end_date);
1333 EXCEPTION
1334 WHEN NO_DATA_FOUND THEN NULL;
1335 END;
1336 IF l_ret_chng = 'Y' THEN
1337 l_s_chng := l_s_chng + 1;
1338 END IF;
1339 BEGIN
1340 l_ret_chng := 'N';
1341 SELECT 'Y' INTO l_ret_chng FROM dual WHERE EXISTS
1342 (SELECT 'Y' FROM pay_element_entry_values_f eev
1343 WHERE eev.element_entry_id = nonrec_entry.element_entry_id
1344 AND eev.effective_end_date = nonrec_entry.effective_end_date);
1345 EXCEPTION
1346 WHEN NO_DATA_FOUND THEN NULL;
1347 END;
1348 IF l_ret_chng = 'Y' THEN
1349 l_y_chng := l_y_chng + 1;
1350 END IF;
1351 END IF;
1352 END IF;
1353 END IF;
1354 END LOOP; -- FOR nonrec_entry IN ...
1355 END IF;
1356 -- VT 10/14/96 bug #306710
1357 -- Warning message
1358 --
1359 l_entries_changed_warning := 'N';
1360 p_entries_changed_warning := l_entries_changed_warning;
1361 IF l_s_chng > 0 THEN
1362 l_entries_changed_warning := 'S';
1363 p_entries_changed_warning := l_entries_changed_warning;
1364 ELSE
1365 IF l_y_chng > 0 THEN
1366 l_entries_changed_warning := 'Y';
1367 p_entries_changed_warning := l_entries_changed_warning;
1368 END IF;
1369 END IF;
1370 --
1371 hr_utility.set_location(l_proc,5);
1372 --
1373 -- Process all recurrring entries for the assignment
1374 FOR rec_entry IN rec_entries(p_assignment_id,
1375 p_term_date
1376 )
1377 LOOP
1378 --
1379 hr_utility.set_location(l_proc,10);
1380 --
1381 -- Bug fix 3983715.
1382 -- Cursor to check whether the type exists.
1383
1384 OPEN chk_element_type_csr( rec_entry.element_type_id,p_term_rule);
1385 FETCH chk_element_type_csr INTO l_type_exists;
1386 CLOSE chk_element_type_csr;
1387
1388 IF nvl(l_type_exists,'N') = 'Y' THEN
1389
1390 l_type_exists := 'N';
1391
1392 -- Find the element type id for the entry for use in checking if there
1393 -- are open periods
1394
1395 SELECT el.element_type_id
1396 INTO l_element_type_id
1397 FROM pay_element_links_f el
1398 WHERE el.element_link_id = rec_entry.element_link_id
1399 AND rec_entry.effective_end_date BETWEEN el.effective_start_date
1400 AND el.effective_end_date;
1401 --
1402 hr_utility.set_location(l_proc,15);
1403 --
1404 -- Make sure delete doew not overlap with a closed period
1405 hr_entry.chk_element_entry_open(l_element_type_id,
1406 p_term_date,
1407 p_term_date,
1408 rec_entry.effective_end_date,
1409 p_assignment_id);
1410 --
1411 hr_utility.set_location(l_proc,20);
1412 --
1413 -- Delete covered_dependents and beneficiaries for the recurring
1414 -- element entries
1415 --
1416
1417 hr_entry.delete_covered_dependants(
1418 p_element_entry_id => rec_entry.element_entry_id,
1419 p_end_date => p_term_date,
1420 p_validation_start_date => p_term_date);
1421
1422
1423 hr_entry.delete_beneficiaries(
1424 p_element_entry_id => rec_entry.element_entry_id,
1425 p_end_date => p_term_date,
1426 p_validation_start_date => p_term_date);
1427 --
1428 -- Bugfix 4308892
1429 -- Replace DELETE statements with equivalent calls to
1430 -- pay_element_entry_api.delete_element_entry
1431 --
1432 if rec_entry.effective_start_date > p_term_date then
1433 --
1434 hr_utility.set_location(l_proc,21);
1435 /*
1436 ** Original code, below, performs a delete from PAY_ELEMENT_ENTRIES_F
1437 ** and PAY_ELEMENT_ENTRY_VALUES_F where the entry effective start
1438 ** date is greater than the termination date (i.e. the entry starts
1439 ** after the employee is terminated). Therefore the entry, and its
1440 ** values, can be completely removed.
1441 ** The DML statements can be replaced with a single call to
1442 ** pay_element_entry_api.delete_element_entry using the 'ZAP' mode.
1443 **
1444 --
1445 DELETE FROM pay_element_entry_values_f eev
1446 WHERE eev.element_entry_id = rec_entry.element_entry_id
1447 AND eev.effective_start_date > p_term_date;
1448 --
1449 hr_utility.set_location(l_proc,25);
1450 --
1451 DELETE FROM pay_element_entries_f ee
1452 WHERE ee.element_entry_id = rec_entry.element_entry_id
1453 AND ee.effective_start_date > p_term_date;
1454 **
1455 */
1456 --
1457 select object_version_number
1458 into l_ee_object_version_number
1459 from pay_element_entries_f
1460 where element_entry_id = rec_entry.element_entry_Id
1461 and effective_start_date = rec_entry.effective_start_date;
1462 --
1463 pay_element_entry_api.delete_element_entry(
1464 p_validate => false,
1465 p_datetrack_delete_mode => 'ZAP',
1466 p_effective_date => rec_entry.effective_start_date,
1467 p_element_entry_id => rec_entry.element_entry_id,
1468 p_object_version_number => l_ee_object_version_number,
1469 p_effective_start_date => l_ee_effective_start_date,
1470 p_effective_end_date => l_ee_effective_end_date,
1471 p_delete_warning => l_delete_warning
1472 );
1473 --
1474 ELSIF rec_entry.effective_end_date <> p_term_date THEN -- 6801103
1475 --
1476 hr_utility.set_location(l_proc,30);
1477 --
1478 /*
1479 ** Original code, below, updates PAY_ELEMENT_ENTRIES_F and
1480 ** PAY_ELEMENT_ENTRY_VALUES_F, setting the effective_end_date
1481 ** of the entry end date to the termination date where the
1482 ** entry end date falls some point after the termination date.
1483 ** The DML statements can be replaced with a single call to
1484 ** pay_element_entry_api.delete_element_entry using the 'DELETE' mode.
1485 **
1486 --
1487 UPDATE pay_element_entry_values_f eev
1488 SET eev.effective_end_date = p_term_date
1489 WHERE eev.element_entry_id = rec_entry.element_entry_id
1490 AND eev.effective_end_date > p_term_date;
1491 --
1492 hr_utility.set_location(l_proc,35);
1493 --
1494 UPDATE pay_element_entries_f ee
1495 SET ee.effective_end_date = p_term_date
1496 , ee.last_update_date = l_last_update_date
1497 WHERE ee.element_entry_id = rec_entry.element_entry_id
1498 AND ee.effective_end_date > p_term_date;
1499 **
1500 */
1501 --
1502 select object_version_number
1503 into l_ee_object_version_number
1504 from pay_element_entries_f
1505 where element_entry_id = rec_entry.element_entry_Id
1506 and effective_start_date = rec_entry.effective_start_date;
1507 --
1508 l_ee_effective_end_date := rec_entry.effective_end_date;
1509 --
1510 pay_element_entry_api.delete_element_entry(
1511 p_validate => false,
1512 p_datetrack_delete_mode => 'DELETE',
1513 p_effective_date => p_term_date,
1514 p_element_entry_id => rec_entry.element_entry_id,
1515 p_object_version_number => l_ee_object_version_number,
1516 p_effective_start_date => l_ee_effective_start_date,
1517 p_effective_end_date => l_ee_effective_end_date,
1518 p_delete_warning => l_delete_warning
1519 );
1520 --
1521 end if;
1522 --
1523 END IF; -- l_type_exists
1524 END LOOP; -- FOR rec_entry IN ...
1525 --
1526 hr_utility.set_location(l_proc,40);
1527 --
1528 -- Process all non-recurrring entries for the assignment
1529 FOR nonrec_entry IN nonrec_entries(p_assignment_id,
1530 p_term_date,
1531 p_term_rule) LOOP
1532 --
1533 hr_utility.set_location(l_proc,45);
1534 --
1535 l_process_in_run_flag := 'N';
1536 -- Find the element type id for the entry for use in checking if there
1537 -- are open periods
1538 -- VT 03/01/96 changed to fix bug #334654,#334681
1539 SELECT el.element_type_id, et.process_in_run_flag
1540 INTO l_element_type_id, l_process_in_run_flag
1541 FROM pay_element_links_f el, pay_element_types_f et
1542 WHERE el.element_link_id = nonrec_entry.element_link_id
1543 AND et.element_type_id = el.element_type_id
1544 AND nonrec_entry.effective_end_date BETWEEN el.effective_start_date
1545 AND el.effective_end_date
1546 /* Bug 1406063 */
1547 AND nonrec_entry.effective_end_date BETWEEN et.effective_start_date
1548 AND et.effective_end_date;
1549 /* End Bug 1406063 */
1550 --
1551 hr_utility.set_location(l_proc,50);
1552 --
1553 -- Make sure delete doew not overlap with a closed period
1554 hr_entry.chk_element_entry_open(l_element_type_id,
1555 nonrec_entry.effective_start_date,
1556 nonrec_entry.effective_start_date,
1557 nonrec_entry.effective_end_date,
1558 p_assignment_id);
1559 --
1560 -- Nonrecurring entry starts after its termination date so it must be
1561 -- removed.
1562 if nonrec_entry.effective_start_date > p_term_date then
1563 -- VT 03/01/96 additional check to fix bug #334654,#334681
1564 if l_process_in_run_flag = 'Y' and p_term_rule IN ('A','L') then
1565 hr_utility.set_message(801,'HR_51208_EMP_ASS_NO_TERM_EE');
1566 hr_utility.raise_error;
1567 exit;
1568 else
1569 --
1570 hr_utility.set_location(l_proc,53);
1571 --
1572
1573 -- Delete covered_dependents and beneficiaries for the non recurring
1574 -- element entries that start after the termination date.
1575 --
1576
1577 hr_entry.delete_covered_dependants(
1578 p_element_entry_id => nonrec_entry.element_entry_id,
1579 p_end_date => p_term_date,
1580 p_validation_start_date => p_term_date);
1581
1582
1583 hr_entry.delete_beneficiaries(
1584 p_element_entry_id => nonrec_entry.element_entry_id,
1585 p_end_date => p_term_date,
1586 p_validation_start_date => p_term_date);
1587
1588
1589
1590 --
1591 hr_utility.set_location(l_proc,55);
1592 --
1593 -- TAR 1660650.999
1594 -- Changed where clause
1595
1596 DELETE FROM pay_run_results rr
1597 WHERE rr.source_type = 'E'
1598 AND rr.source_id = nonrec_entry.element_entry_id
1599 AND rr.status not like 'P%';
1600 --
1601 hr_utility.set_location(l_proc,60);
1602 -- Bugfix 4308892
1603 -- Replace DELETE statements with equivalent calls to
1604 -- pay_element_entry_api.delete_element_entry
1605 --
1606 /*
1607 ** Original code, below, performs a delete from PAY_ELEMENT_ENTRIES_F
1608 ** and PAY_ELEMENT_ENTRY_VALUES_F since the entry effective start
1609 ** date is greater than the termination date (i.e. the entry starts
1610 ** after the employee is terminated). Therefore the entry, and its
1611 ** values, can be completely removed.
1612 ** The DML statements can be replaced with a single call to
1613 ** pay_element_entry_api.delete_element_entry using the 'ZAP' mode.
1614 **
1615 DELETE FROM pay_element_entry_values_f eev
1616 WHERE eev.element_entry_id = nonrec_entry.element_entry_id;
1617 --
1618 hr_utility.set_location(l_proc,65);
1619 --
1620 DELETE FROM pay_element_entries_f ee
1621 WHERE ee.element_entry_id = nonrec_entry.element_entry_id;
1622 **
1623 */
1624 --
1625 select object_version_number
1626 into l_ee_object_version_number
1627 from pay_element_entries_f
1628 where element_entry_id = nonrec_entry.element_entry_Id
1629 and effective_start_date = nonrec_entry.effective_start_date;
1630 --
1631 pay_element_entry_api.delete_element_entry(
1632 p_validate => false,
1633 p_datetrack_delete_mode => 'ZAP',
1634 p_effective_date => nonrec_entry.effective_start_date,
1635 p_element_entry_id => nonrec_entry.element_entry_id,
1636 p_object_version_number => l_ee_object_version_number,
1637 p_effective_start_date => l_ee_effective_start_date,
1638 p_effective_end_date => l_ee_effective_end_date,
1639 p_delete_warning => l_delete_warning
1640 );
1641 --
1642 --
1643 end if;
1644 -- Nonrecurring entry straddles its termination date so it can still
1645 -- exist. If the final process date is less than the nonrecurring end date
1646 -- then the end date is updated to that of the final process date.
1647 --
1648 -- 31-oct-1995 WWBUG 314277 changed test for p_final_process_date
1649 -- to be p_term_date as this agrees with the relevant termination rule.
1650 elsif nonrec_entry.effective_start_date <= p_term_date and
1651 nonrec_entry.effective_end_date > nvl(p_term_date,
1652 c_eot) then
1653 -- VT 03/05/96 additional check to fix bug #314277
1654 IF p_final_process_date IS NULL THEN
1655
1656 --
1657 hr_utility.set_location(l_proc,67);
1658 --
1659
1660 -- Delete covered_dependents and beneficiaries for the non recurring
1661 -- element entries that have no final process date
1662 --
1663
1664 hr_entry.delete_covered_dependants(
1665 p_element_entry_id => nonrec_entry.element_entry_id,
1666 p_end_date => p_term_date,
1667 p_validation_start_date => p_term_date);
1668
1669
1670 hr_entry.delete_beneficiaries(
1671 p_element_entry_id => nonrec_entry.element_entry_id,
1672 p_end_date => p_term_date,
1673 p_validation_start_date => p_term_date);
1674
1675 --
1676 hr_utility.set_location(l_proc,70);
1677 --
1678 -- 31-oct-1995 WWBUG 314277 changed update to p_final_process_date
1679 -- to be p_term_date as this agrees with the relevant termination rule.
1680 -- VT #553177 12/10/97
1681 null;
1682 -- UPDATE pay_element_entry_values_f eev
1683 -- SET eev.effective_end_date = p_term_date
1684 -- WHERE eev.element_entry_id = nonrec_entry.element_entry_id
1685 -- AND eev.effective_end_date = nonrec_entry.effective_end_date;
1686 --
1687 hr_utility.set_location(l_proc,75);
1688 --
1689 -- 31-oct-1995 WWBUG 314277 changed update to p_final_process_date
1690 -- to be p_term_date as this agrees with the relevant termination rule.
1691 -- VT 03/05/96 commented out to fix bug #314277
1692 -- UPDATE pay_element_entries_f ee
1693 -- SET ee.last_update_date = p_term_date
1694 -- WHERE ee.element_entry_id = nonrec_entry.element_entry_id
1695 -- AND ee.effective_end_date = nonrec_entry.effective_end_date;
1696 --
1697 ELSE
1698 -- VT 01/03/96 bug #314277 effective end date is to be set to
1699 -- p_final_process_date when p_final_process_date is not null.
1700 --
1701 hr_utility.set_location(l_proc,80);
1702 --
1703 -- VT #553177 12/11/97
1704 l_current_period_end := null;
1705 OPEN period_end(p_assignment_id, nonrec_entry.effective_start_date);
1706 FETCH period_end INTO l_current_period_end;
1707 CLOSE period_end;
1708 --
1709 IF p_term_rule IN ('A','L','F')
1710 AND l_current_period_end IS NOT NULL
1711 AND p_final_process_date <= l_current_period_end THEN
1712 --
1713 --
1714 hr_utility.set_location(l_proc,83);
1715 --
1716
1717 -- Delete covered_dependents and beneficiaries for the non-recurring
1718 -- element entries which have a final process date.
1719 --
1720
1721 hr_entry.delete_covered_dependants(
1722 p_element_entry_id => nonrec_entry.element_entry_id,
1723 p_end_date => p_term_date,
1724 p_validation_start_date => p_final_process_date);
1725
1726
1727 hr_entry.delete_beneficiaries(
1728 p_element_entry_id => nonrec_entry.element_entry_id,
1729 p_end_date => p_term_date,
1730 p_validation_start_date => p_final_process_date);
1731
1732 --
1733 -- Bugfix 4308892
1734 -- Replace DELETE statements with equivalent calls to
1735 -- pay_element_entry_api.delete_element_entry
1736 --
1737 /*
1738 ** Original code, below, updates PAY_ELEMENT_ENTRIES_F and
1739 ** PAY_ELEMENT_ENTRY_VALUES_F, setting the effective_end_date
1740 ** of the entry end date to the termination date where the
1741 ** entry end date falls some point after the termination date.
1742 ** The DML statements can be replaced with a single call to
1743 ** pay_element_entry_api.delete_element_entry using the 'DELETE' mode.
1744 **
1745 UPDATE pay_element_entries_f ee
1746 SET ee.effective_end_date = p_final_process_date
1747 , ee.last_update_date = l_last_update_date
1748 WHERE ee.element_entry_id = nonrec_entry.element_entry_id
1749 AND ee.effective_end_date = nonrec_entry.effective_end_date;
1750 UPDATE pay_element_entry_values_f eev
1751 SET eev.effective_end_date = p_final_process_date
1752 WHERE eev.element_entry_id = nonrec_entry.element_entry_id
1753 AND eev.effective_end_date = nonrec_entry.effective_end_date;
1754 **
1755 */
1756 --
1757 select object_version_number
1758 into l_ee_object_version_number
1759 from pay_element_entries_f
1760 where element_entry_id = nonrec_entry.element_entry_Id
1761 and effective_start_date = nonrec_entry.effective_start_date;
1762 --
1763 pay_element_entry_api.delete_element_entry(
1764 p_validate => false,
1765 p_datetrack_delete_mode => 'DELETE',
1766 p_effective_date => p_final_process_date,
1767 p_element_entry_id => nonrec_entry.element_entry_id,
1768 p_object_version_number => l_ee_object_version_number,
1769 p_effective_start_date => l_ee_effective_start_date,
1770 p_effective_end_date => l_ee_effective_end_date,
1771 p_delete_warning => l_delete_warning
1772 );
1773 --
1774 hr_utility.set_location(l_proc,85);
1775 --
1776 END IF;
1777 END IF;
1778 end if;
1779 --
1780 hr_utility.set_location(l_proc,90);
1781 --
1782 -- VT #375157 06/28/96
1783 -- Remove any pay proposals for which there are no element entries.
1784 --delete from per_pay_proposals pp
1785 --where pp.assignment_id = p_assignment_id
1786 -- and not exists
1787 -- (select null
1788 -- from pay_element_entries_f ee
1789 -- where ee.assignment_id = pp.assignment_id
1790 -- and ee.entry_type = 'SP'
1791 -- and ee.creator_id = pp.pay_proposal_id);
1792 --
1793 END LOOP; -- FOR nonrec_entry IN ...
1794 --
1795 hr_utility.set_location(l_proc,100);
1796 --
1797 for pay_rec in get_pay_proposals(p_assignment_id,p_term_date,p_term_rule) LOOP
1798 --
1799 hr_utility.set_location(l_proc,110);
1800 --
1801 if pay_rec.multiple_components='Y' then
1802 DELETE FROM per_pay_proposal_components
1803 WHERE pay_proposal_id=pay_rec.pay_proposal_id;
1804 end if;
1805 --
1806 DELETE FROM per_pay_proposals
1807 WHERE pay_proposal_id=pay_rec.pay_proposal_id;
1808 --
1809 hr_utility.set_location(l_proc,120);
1810 --
1811 end loop;
1812 --
1813 hr_utility.set_location(l_proc,130);
1814 --
1815 END delete_entries;
1816 --
1817 -- Bug 5368246.
1818 -- We delete or end date ALUs only when the final process date
1819 -- is specified, ie. only when the assignment is end dated.
1820 -- Therefore, we no longer check the term rule, and p_term_date
1821 -- is always set to the final process date.
1822 --
1823 PROCEDURE delete_alus(p_assignment_id NUMBER,
1824 p_term_date DATE,
1825 --p_term_rule VARCHAR2,
1826 --
1827 -- 115.59 (START)
1828 --
1829 --p_changes IN OUT NOCOPY VARCHAR2) IS
1830 p_changes IN OUT NOCOPY VARCHAR2,
1831 p_alu_change_warning IN OUT NOCOPY VARCHAR2) IS
1832 --
1833 -- 115.59 (END)
1834 --
1835 --
1836 l_alu_change VARCHAR2(1) := 'N';
1837 l_alu_del VARCHAR2(1) := 'N';
1838 l_alu_upd VARCHAR2(1) := 'N';
1839 l_proc varchar2(72):=g_package||'delete_alus';
1840 --
1841 --
1842 -- 115.59 (START)
1843 --
1844 CURSOR csr_alu_actions IS
1845 SELECT NULL
1846 FROM pay_payroll_actions ppa
1847 ,pay_assignment_actions paa
1848 WHERE ppa.payroll_action_id = paa.payroll_action_id
1849 AND ppa.action_type IN ('R','Q','V','B')
1850 AND paa.assignment_id = p_assignment_id
1851 AND NVL(ppa.date_earned,ppa.effective_date) > p_term_date;
1852 --
1853 l_dummy VARCHAR2(1);
1854 --
1855 -- 115.59 (END)
1856 --
1857 BEGIN
1858 --
1859 --
1860 -- 115.59 (START)
1861 --
1862 OPEN csr_alu_actions;
1863 FETCH csr_alu_actions INTO l_dummy;
1864 IF csr_alu_actions%FOUND THEN
1865 p_alu_change_warning := 'Y';
1866 ELSE
1867 p_alu_change_warning := 'N';
1868 END IF;
1869 CLOSE csr_alu_actions;
1870 --
1871 -- 115.59 (END)
1872 --
1873 --
1874 hr_utility.set_location(l_proc,1);
1875 --
1876 DELETE FROM pay_assignment_link_usages_f alu
1877 WHERE alu.assignment_id = p_assignment_id
1878 AND alu.effective_start_date > p_term_date;
1879 --
1880 if sql%found then
1881 l_alu_del := 'Y';
1882 end if;
1883 --
1884 hr_utility.set_location(l_proc,2);
1885 --
1886 UPDATE pay_assignment_link_usages_f alu
1887 SET alu.effective_end_date = p_term_date
1888 WHERE alu.assignment_id = p_assignment_id
1889 AND alu.effective_end_date > p_term_date;
1890 --
1891 if sql%found then
1892 l_alu_upd := 'Y';
1893 end if;
1894
1895 IF l_alu_del = 'Y' OR l_alu_upd = 'Y' THEN
1896 l_alu_change := 'Y';
1897 p_changes := l_alu_change;
1898 END IF;
1899 --
1900 END delete_alus;
1901 --
1902 -- Main code starts here
1903 BEGIN -- PROCEDURE terminate_entries_and_alus
1904 --
1905 hr_utility.set_location(l_proc,1);
1906 --
1907 -- Validates the combination of dates passed. The valid combinations are
1908 --
1909 -- Actual (if legislation US)
1910 -- Actual Last Standard
1911 -- Actual Last Standard Final Process
1912 -- Actual Final Process(if legislation US)
1913 -- Final Process
1914 --
1915 -- 288341. Removed unnecessary clause "p_legislation_code <> 'US'", which
1916 -- was causing the check to fail when p_legislation_code was NULL.
1917 -- RMF 03.07.95.
1918 --
1919 -- Bug 1711085. VS. 29-Mar-01. Removed references to US legislation
1920 -- in association with last_standard_process.
1921 --
1922 -- Bug 2784295 : commenting the code which requires LSPD to be not null
1923 -- for non-US legislations
1924 IF p_actual_term_date IS NOT NULL THEN
1925 /* (p_legislation_code = 'US' or
1926 p_last_standard_date IS NOT NULL) THEN */
1927 --
1928 -- Valid combination of parameters
1929 NULL;
1930 --
1931 ELSIF
1932 p_final_process_date IS NOT NULL AND
1933 p_actual_term_date IS NULL AND
1934 p_last_standard_date IS NULL THEN
1935 --
1936 -- Valid combination of parameters
1937 NULL;
1938 --
1939 ELSE
1940 --
1941 hr_utility.set_location(l_proc,2);
1942 --
1943 -- All other parameter combinations invalid
1944 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
1945 hr_utility.set_message_token('PROCEDURE','TERMINATE_ENTRIES');
1946 hr_utility.set_message_token('STEP', '1');
1947 hr_utility.raise_error;
1948 --
1949 END IF; -- p_actual_term_date IS NOT NULL ...
1950 l_entries_changed := 'N';
1951 l_cur_entries := 'N';
1952 l_cur_alus := 'N';
1953 --
1954 hr_utility.set_location(l_proc,3);
1955 --
1956 -- Shut down all element entries and ALU's for element types which have
1957 -- a post termination rule of 'Actual Termination'
1958 IF p_actual_term_date IS NOT NULL THEN
1959 --
1960 hr_utility.set_location(l_proc,4);
1961 --
1962 l_cur_entries := 'N';
1963 delete_entries(p_assignment_id,
1964 p_actual_term_date,
1965 'A',
1966 p_final_process_date,
1967 l_cur_entries);
1968 if l_cur_entries = 'S' then
1969 l_entries_changed := 'S';
1970 else
1971 if l_entries_changed = 'N' and l_cur_entries = 'Y' then
1972 l_entries_changed := 'Y';
1973 end if;
1974 end if;
1975 --
1976 hr_utility.set_location(l_proc,5);
1977 --
1978 --
1979 -- Bug 5368246.
1980 -- We delete alus only when final process date is specified,
1981 -- hence commented out the followings.
1982 /******
1983 l_cur_alus := 'N';
1984 delete_alus(p_assignment_id,
1985 p_actual_term_date,
1986 'A',
1987 l_cur_alus,
1988 p_alu_change_warning);
1989 ******/
1990 --
1991 END IF; -- IF p_actual_term_date ...
1992 --
1993 hr_utility.set_location(l_proc,6);
1994 --
1995 -- Shut down all element entries and ALU's for element types which have a
1996 -- post termination rule of 'Last Standard Process'
1997 IF p_last_standard_date IS NOT NULL THEN
1998 --
1999 hr_utility.set_location(l_proc,7);
2000 --
2001 l_cur_entries := 'N';
2002 delete_entries(p_assignment_id,
2003 p_last_standard_date,
2004 'L',
2005 p_final_process_date,
2006 l_cur_entries);
2007 if l_cur_entries = 'S' then
2008 l_entries_changed := 'S';
2009 else
2010 if l_entries_changed = 'N' and l_cur_entries = 'Y' then
2011 l_entries_changed := 'Y';
2012 end if;
2013 end if;
2014 --
2015 hr_utility.set_location(l_proc,8);
2016 --
2017 --
2018 -- Bug 5368246.
2019 -- We delete alus only when final process date is specified,
2020 -- hence commented out the followings.
2021 /******
2022 l_cur_alus := 'N';
2023 delete_alus(p_assignment_id,
2024 p_last_standard_date,
2025 'L',
2026 l_cur_alus,
2027 p_alu_change_warning);
2028 ******/
2029 --
2030 END IF; -- IF p_last_standard_date ...
2031 --
2032 hr_utility.set_location(l_proc,9);
2033 --
2034 -- Shut down all element entries and ALU's for element types which have a
2035 -- post termination rule of 'Final Close'
2036 IF p_final_process_date IS NOT NULL THEN
2037 l_cur_entries := 'N';
2038 delete_entries(p_assignment_id,
2039 p_final_process_date,
2040 'A',
2041 p_final_process_date,
2042 l_cur_entries);
2043 if l_cur_entries = 'S' then
2044 l_entries_changed := 'S';
2045 else
2046 if l_entries_changed = 'N' and l_cur_entries = 'Y' then
2047 l_entries_changed := 'Y';
2048 end if;
2049 end if;
2050 l_cur_entries := 'N';
2051 delete_entries(p_assignment_id,
2052 p_final_process_date,
2053 'L',
2054 p_final_process_date,
2055 l_cur_entries);
2056 if l_cur_entries = 'S' then
2057 l_entries_changed := 'S';
2058 else
2059 if l_entries_changed = 'N' and l_cur_entries = 'Y' then
2060 l_entries_changed := 'Y';
2061 end if;
2062 end if;
2063 --
2064 hr_utility.set_location(l_proc,10);
2065 --
2066 l_cur_entries := 'N';
2067 delete_entries(p_assignment_id,
2068 p_final_process_date,
2069 'F',
2070 p_final_process_date,
2071 l_cur_entries);
2072 if l_cur_entries = 'S' then
2073 l_entries_changed := 'S';
2074 else
2075 if l_entries_changed = 'N' and l_cur_entries = 'Y' then
2076 l_entries_changed := 'Y';
2077 end if;
2078 end if;
2079 --
2080 hr_utility.set_location(l_proc,11);
2081 --
2082 l_cur_alus := 'N';
2083 --
2084 -- Bug 5368246.
2085 -- When final process date is specified, we delete or end date
2086 -- all of the alus regardless of the termination rule.
2087 --
2088 delete_alus(p_assignment_id,
2089 p_final_process_date,
2090 --'F',
2091 l_cur_alus,
2092 p_alu_change_warning);
2093 --
2094 END IF; -- IF p_final_process_date ...
2095 --
2096 -- VT 10/07/96 bug #306710 return value
2097 p_entries_changed_warning := l_entries_changed;
2098 END terminate_entries_and_alus;
2099 --
2100 --
2101 -- ====================== terminate_employee ========================
2102 --
2103 PROCEDURE terminate_employee(p_trigger VARCHAR2
2104 ,p_business_group_id NUMBER
2105 ,p_person_id NUMBER
2106 ,p_assignment_status_type_id NUMBER
2107 ,p_actual_termination_date DATE
2108 ,p_last_standard_process_date DATE
2109 ,p_final_process_date DATE)
2110 IS
2111 --
2112 l_current_applicant_flag VARCHAR2(30);
2113 l_current_employee_flag VARCHAR2(30);
2114 l_current_emp_or_apl_flag VARCHAR2(30);
2115 l_person_type_id NUMBER;
2116 l_assignment_status_type_id NUMBER;
2117 l_max_end_date DATE;
2118 l_effective_end_date DATE;
2119 l_per_system_status VARCHAR2(30);
2120 l_action_chk VARCHAR2(1) := 'N';
2121 l_legislation_code VARCHAR2(30);
2122 --
2123 FPD_FLAG BOOLEAN;
2124 TA_FLAG BOOLEAN;
2125 --
2126 -- VT 10/07/96 bug #306710
2127 l_entries_changed VARCHAR2(1) := 'N';
2128 l_ent_loop VARCHAR2(1) := 'N';
2129 --
2130 CURSOR c_employee IS
2131 SELECT *
2132 FROM per_people_f pp
2133 WHERE pp.person_id = p_person_id
2134 FOR UPDATE;
2135 --
2136 -- Added AND assignment_type = 'E' 307957 tm 19-sep-1995
2137 --
2138 CURSOR c_assignment IS
2139 SELECT *
2140 FROM per_assignments_f ass
2141 WHERE ass.person_id = p_person_id
2142 AND p_actual_termination_date
2143 BETWEEN ass.effective_start_date
2144 AND ass.effective_end_date
2145 AND assignment_type = 'E'
2146 FOR UPDATE;
2147 --
2148 -- Added for US legsilation specific code.
2149 --
2150 cursor legislation is
2151 select legislation_code
2152 from per_business_groups
2153 where business_group_id = p_business_group_id;
2154 --
2155 l_proc varchar2(72):=g_package||'terminate_employee';
2156 begin
2157 hr_utility.trace('Entered terminate_employee for '||p_person_id);
2158 --
2159 open legislation;
2160 fetch legislation into l_legislation_code;
2161 close legislation;
2162 --
2163 hr_utility.set_location(l_proc,1);
2164 if p_trigger = 'PRE_UPDATE' then
2165 hr_utility.set_location(l_proc,2);
2166 --
2167 -- Added for US legsilation specific code.
2168 --
2169 -- Bug 1711085. VS. Removed reference to US legislature.
2170 -- if (l_legislation_code <> 'US') then
2171 if (p_actual_termination_date IS NOT NULL AND
2172 p_last_standard_process_date IS NOT NULL)
2173 then null;
2174 else
2175 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2176 hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2177 hr_utility.set_message_token('STEP',2);
2178 hr_utility.raise_error;
2179 end if;
2180 --
2181 hr_utility.set_location(l_proc,5);
2182 if (p_actual_termination_date <= p_last_standard_process_date
2183 AND p_last_standard_process_date <=
2184 nvl(p_final_process_date,to_date('31/12/4712','DD/MM/YYYY')))
2185 then null;
2186 else
2187 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2188 hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2189 hr_utility.set_message_token('STEP',5);
2190 hr_utility.raise_error;
2191 end if;
2192 -- end if;
2193 --
2194 hr_utility.set_location(l_proc,7);
2195 l_action_chk := hrempter.check_for_future_person_rows(p_person_id
2196 ,p_actual_termination_date);
2197 --
2198 if l_action_chk = 'Y' then
2199 hr_utility.set_message(801,'HR_7440_TERM_FUT_ROWS_EXST');
2200 hr_utility.raise_error;
2201 end if;
2202 --
2203 hr_utility.set_location(l_proc,10);
2204 SELECT current_employee_flag
2205 INTO l_current_employee_flag
2206 FROM per_people_f pp
2207 WHERE pp.person_id = p_person_id
2208 AND p_actual_termination_date + 1
2209 BETWEEN pp.effective_start_date
2210 AND pp.effective_end_date;
2211 --
2212 hr_utility.set_location(l_proc,15);
2213 if l_current_employee_flag = 'Y' then null;
2214 else hr_utility.set_message(801,'HR_6513_EMP_TERMINATED');
2215 hr_utility.raise_error;
2216 end if;
2217 --
2218 hr_utility.set_location(l_proc,20);
2219 -- VT 06/04/96 changed function call
2220 l_action_chk := hrempter.check_for_compl_actions(p_person_id
2221 ,p_actual_termination_date
2222 ,p_last_standard_process_date
2223 ,p_final_process_date);
2224 IF l_action_chk = 'W' THEN
2225 hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2226 hr_utility.set_warning;
2227 END IF;
2228 --
2229 if l_action_chk = 'Y' then
2230 hr_utility.set_message(801,'HR_6516_EMP_TERM_ACTIONS_EXIST');
2231 hr_utility.raise_error;
2232 end if;
2233 --
2234 -----------------------------------------------------------------
2235 -- If the FPD is not null then check that COBRA Benefits do not
2236 -- exist after the FPD
2237 --
2238 hr_utility.set_location(l_proc,23);
2239 if p_final_process_date IS NOT NULL then
2240 if hrempter.check_cobra_benefits
2241 (p_person_id
2242 ,p_final_process_date) then
2243 hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
2244 hr_utility.raise_error;
2245 end if;
2246 end if;
2247 --
2248 hr_utility.set_location(l_proc,30);
2249 FOR c_emp_rec IN c_employee LOOP
2250 --
2251 if (c_emp_rec.effective_start_date <= p_actual_termination_date
2252 AND p_actual_termination_date <= c_emp_rec.effective_end_date) then
2253 hr_utility.set_location(l_proc,35);
2254 -- Bug 3735333 - Removed the suppression of business group index.
2255 --
2256 SELECT person_type_id
2257 , c_emp_rec.current_applicant_flag
2258 , null
2259 , c_emp_rec.current_applicant_flag
2260 INTO l_person_type_id
2261 , l_current_applicant_flag
2262 , l_current_employee_flag
2263 , l_current_emp_or_apl_flag
2264 FROM per_person_types
2265 WHERE business_group_id = p_business_group_id --#3735333
2266 AND default_flag = 'Y'
2267 AND active_flag = 'Y'
2268 AND ((c_emp_rec.current_applicant_flag = 'Y'
2269 AND system_person_type = 'EX_EMP_APL')
2270 OR (c_emp_rec.current_applicant_flag IS NULL
2271 AND system_person_type = 'EX_EMP'));
2272 --
2273 hr_utility.set_location(l_proc,40);
2274 INSERT INTO PER_ALL_PEOPLE_F
2275 (person_id
2276 ,effective_start_date
2277 ,effective_end_date
2278 ,business_group_id
2279 ,person_type_id
2280 ,last_name
2281 ,start_date
2282 ,applicant_number
2283 ,comment_id
2284 ,current_applicant_flag
2285 ,current_emp_or_apl_flag
2286 ,current_employee_flag
2287 ,date_employee_data_verified
2288 ,date_of_birth
2289 ,email_address
2290 ,employee_number
2291 ,expense_check_send_to_address
2292 ,first_name
2293 ,full_name
2294 ,known_as
2295 ,marital_status
2296 ,middle_names
2297 ,nationality
2298 ,national_identifier
2299 ,previous_last_name
2300 ,registered_disabled_flag
2301 ,sex
2302 ,title
2303 ,suffix
2304 ,vendor_id
2305 -- ,work_telephone
2306 ,request_id
2307 ,program_application_id
2308 ,program_id
2309 ,program_update_date
2310 ,attribute_category
2311 ,attribute1
2312 ,attribute2
2313 ,attribute3
2314 ,attribute4
2315 ,attribute5
2316 ,attribute6
2317 ,attribute7
2318 ,attribute8
2319 ,attribute9
2320 ,attribute10
2321 ,attribute11
2322 ,attribute12
2323 ,attribute13
2324 ,attribute14
2325 ,attribute15
2326 ,attribute16
2327 ,attribute17
2328 ,attribute18
2329 ,attribute19
2330 ,attribute20
2331 ,attribute21
2332 ,attribute22
2333 ,attribute23
2334 ,attribute24
2335 ,attribute25
2336 ,attribute26
2337 ,attribute27
2338 ,attribute28
2339 ,attribute29
2340 ,attribute30
2341 ,last_update_date
2342 ,last_updated_by
2343 ,last_update_login
2344 ,created_by
2345 ,creation_date
2346 ,per_information_category
2347 ,per_information1
2348 ,per_information2
2349 ,per_information3
2350 ,per_information4
2351 ,per_information5
2352 ,per_information6
2353 ,per_information7
2354 ,per_information8
2355 ,per_information9
2356 ,per_information10
2357 ,per_information11
2358 ,per_information12
2359 ,per_information13
2360 ,per_information14
2361 ,per_information15
2362 ,per_information16
2363 ,per_information17
2364 ,per_information18
2365 ,per_information19
2366 ,per_information20
2367 ,per_information21
2368 ,per_information22
2369 ,per_information23
2370 ,per_information24
2371 ,per_information25
2372 ,per_information26
2373 ,per_information27
2374 ,per_information28
2375 ,per_information29
2376 ,per_information30
2377 ,work_schedule
2378 ,correspondence_language
2379 ,student_status
2380 ,fte_capacity
2381 ,on_military_service
2382 ,second_passport_exists
2383 ,background_check_status
2384 ,background_date_check
2385 ,blood_type
2386 ,last_medical_test_date
2387 ,last_medical_test_by
2388 ,rehire_recommendation
2389 ,rehire_reason
2390 ,resume_exists
2391 ,resume_last_updated
2392 ,office_number
2393 ,internal_location
2394 ,mailstop
2395 ,honors
2396 ,pre_name_adjunct
2397 ,hold_applicant_date_until
2398 ,benefit_group_id
2399 ,receipt_of_death_cert_date
2400 ,coord_ben_med_pln_no
2401 ,coord_ben_no_cvg_flag
2402 ,uses_tobacco_flag
2403 ,dpdnt_adoption_date
2404 ,dpdnt_vlntry_svce_flag
2405 ,date_of_death
2406 ,original_date_of_hire
2407 ,town_of_birth
2408 ,region_of_birth
2409 ,country_of_birth
2410 ,global_person_id
2411 )
2412 VALUES
2413 ( c_emp_rec.person_id
2414 , p_actual_termination_date + 1
2415 , c_emp_rec.effective_end_date
2416 , c_emp_rec.business_group_id
2417 , l_person_type_id
2418 , c_emp_rec.last_name
2419 , c_emp_rec.start_date
2420 , c_emp_rec.applicant_number
2421 , c_emp_rec.comment_id
2422 , l_current_applicant_flag
2423 , l_current_emp_or_apl_flag
2424 , l_current_employee_flag
2425 , c_emp_rec.date_employee_data_verified
2426 , c_emp_rec.date_of_birth
2427 , c_emp_rec.email_address
2428 , c_emp_rec.employee_number
2429 , c_emp_rec.expense_check_send_to_address
2430 , c_emp_rec.first_name
2431 , c_emp_rec.full_name
2432 , c_emp_rec.known_as
2433 , c_emp_rec.marital_status
2434 , c_emp_rec.middle_names
2435 , c_emp_rec.nationality
2436 , c_emp_rec.national_identifier
2437 , c_emp_rec.previous_last_name
2438 , c_emp_rec.registered_disabled_flag
2439 , c_emp_rec.sex
2440 , c_emp_rec.title
2441 , c_emp_rec.suffix
2442 , c_emp_rec.vendor_id
2443 -- , c_emp_rec.work_telephone
2444 , c_emp_rec.request_id
2445 , c_emp_rec.program_application_id
2446 , c_emp_rec.program_id
2447 , c_emp_rec.program_update_date
2448 , c_emp_rec.attribute_category
2449 , c_emp_rec.attribute1
2450 , c_emp_rec.attribute2
2451 , c_emp_rec.attribute3
2452 , c_emp_rec.attribute4
2453 , c_emp_rec.attribute5
2454 , c_emp_rec.attribute6
2455 , c_emp_rec.attribute7
2456 , c_emp_rec.attribute8
2457 , c_emp_rec.attribute9
2458 , c_emp_rec.attribute10
2459 , c_emp_rec.attribute11
2460 , c_emp_rec.attribute12
2461 , c_emp_rec.attribute13
2462 , c_emp_rec.attribute14
2463 , c_emp_rec.attribute15
2464 , c_emp_rec.attribute16
2465 , c_emp_rec.attribute17
2466 , c_emp_rec.attribute18
2467 , c_emp_rec.attribute19
2468 , c_emp_rec.attribute20
2469 , c_emp_rec.attribute21
2470 , c_emp_rec.attribute22
2471 , c_emp_rec.attribute23
2472 , c_emp_rec.attribute24
2473 , c_emp_rec.attribute25
2474 , c_emp_rec.attribute26
2475 , c_emp_rec.attribute27
2476 , c_emp_rec.attribute28
2477 , c_emp_rec.attribute29
2478 , c_emp_rec.attribute30
2479 , SYSDATE
2480 , -1
2481 , -1
2482 , c_emp_rec.created_by
2483 , c_emp_rec.creation_date
2484 , c_emp_rec.per_information_category
2485 , c_emp_rec.per_information1
2486 , c_emp_rec.per_information2
2487 , c_emp_rec.per_information3
2488 , c_emp_rec.per_information4
2489 , c_emp_rec.per_information5
2490 , c_emp_rec.per_information6
2491 , c_emp_rec.per_information7
2492 , c_emp_rec.per_information8
2493 , c_emp_rec.per_information9
2494 , c_emp_rec.per_information10
2495 , c_emp_rec.per_information11
2496 , c_emp_rec.per_information12
2497 , c_emp_rec.per_information13
2498 , c_emp_rec.per_information14
2499 , c_emp_rec.per_information15
2500 , c_emp_rec.per_information16
2501 , c_emp_rec.per_information17
2502 , c_emp_rec.per_information18
2503 , c_emp_rec.per_information19
2504 , c_emp_rec.per_information20
2505 , c_emp_rec.per_information21
2506 , c_emp_rec.per_information22
2507 , c_emp_rec.per_information23
2508 , c_emp_rec.per_information24
2509 , c_emp_rec.per_information25
2510 , c_emp_rec.per_information26
2511 , c_emp_rec.per_information27
2512 , c_emp_rec.per_information28
2513 , c_emp_rec.per_information29
2514 , c_emp_rec.per_information30
2515 , c_emp_rec.work_schedule
2516 , c_emp_rec.correspondence_language
2517 , c_emp_rec.student_status
2518 , c_emp_rec.fte_capacity
2519 , c_emp_rec.on_military_service
2520 , c_emp_rec.second_passport_exists
2521 , c_emp_rec.background_check_status
2522 , c_emp_rec.background_date_check
2523 , c_emp_rec.blood_type
2524 , c_emp_rec.last_medical_test_date
2525 , c_emp_rec.last_medical_test_by
2526 , c_emp_rec.rehire_recommendation
2527 , c_emp_rec.rehire_reason
2528 , c_emp_rec.resume_exists
2529 , c_emp_rec.resume_last_updated
2530 , c_emp_rec.office_number
2531 , c_emp_rec.internal_location
2532 , c_emp_rec.mailstop
2533 , c_emp_rec.honors
2534 , c_emp_rec.pre_name_adjunct
2535 , c_emp_rec.hold_applicant_date_until
2536 , c_emp_rec.benefit_group_id
2537 , c_emp_rec.receipt_of_death_cert_date
2538 , c_emp_rec.coord_ben_med_pln_no
2539 , c_emp_rec.coord_ben_no_cvg_flag
2540 , c_emp_rec.uses_tobacco_flag
2541 , c_emp_rec.dpdnt_adoption_date
2542 , c_emp_rec.dpdnt_vlntry_svce_flag
2543 , c_emp_rec.date_of_death
2544 , c_emp_rec.original_date_of_hire
2545 , c_emp_rec.town_of_birth
2546 , c_emp_rec.region_of_birth
2547 , c_emp_rec.country_of_birth
2548 , c_emp_rec.global_person_id
2549 );
2550 --
2551 hr_utility.set_location(l_proc,45);
2552 UPDATE per_people_f pp
2553 SET pp.effective_end_date = p_actual_termination_date
2554 WHERE CURRENT OF c_employee;
2555 --
2556 end if;
2557 --
2558 END LOOP;
2559 --
2560 elsif p_trigger = 'POST_UPDATE' then
2561 --
2562 --
2563 hr_utility.set_location(l_proc,50);
2564 FOR c_ass_rec IN c_assignment LOOP
2565 --
2566 hr_utility.set_location(l_proc,55);
2567 l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
2568 --
2569 hr_utility.set_location(l_proc,60);
2570 FPD_FLAG := (p_final_process_date IS NOT NULL AND
2571 p_final_process_date < l_max_end_date);
2572 --
2573 hr_utility.set_location(l_proc,65);
2574 SELECT per_system_status
2575 INTO l_per_system_status
2576 FROM per_assignment_status_types
2577 WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
2578 --
2579 hr_utility.set_location(l_proc,70);
2580 TA_FLAG := (l_per_system_status = 'TERM_ASSIGN' AND
2581 c_ass_rec.effective_start_date < p_actual_termination_date);
2582 --
2583 if NOT TA_FLAG then
2584 hr_utility.set_location(l_proc,75);
2585 hrempter.delete_assign_atd(c_ass_rec.assignment_id
2586 ,p_actual_termination_date);
2587 end if;
2588 --
2589 if FPD_FLAG then
2590 hr_utility.set_location(l_proc,80);
2591 hrempter.delete_assign_fpd(c_ass_rec.assignment_id
2592 ,p_final_process_date);
2593 end if;
2594 --
2595 hr_utility.set_location(l_proc,85);
2596 --
2597 -- Added for US legsilation specific code.
2598 -- Legislation code will change valid values
2599 -- checking for the termination.
2600 --
2601 -- VT 10/07/96 bug #306710 new parameter
2602 l_ent_loop := 'N';
2603 hrempter.terminate_entries_and_alus(c_ass_rec.assignment_id
2604 ,p_actual_termination_date
2605 ,p_last_standard_process_date
2606 ,p_final_process_date
2607 ,l_legislation_code
2608 ,l_ent_loop);
2609 if l_ent_loop = 'S' then
2610 l_entries_changed := 'S';
2611 end if;
2612 if l_ent_loop = 'Y' and l_entries_changed = 'N' then
2613 l_entries_changed := 'Y';
2614 end if;
2615 --
2616 -- Fix for bug: 969491
2617 -- When the employee is terminated the tax records tables have to
2618 -- updated to reflect the final process date. This is done when the
2619 -- termination process has a FINAL PROCESS DATE.
2620 -- Adding an extra verification to make sure the tax records are reverse
2621 -- only for US legislation or Ceridian Customers. This extra
2622 -- verification is done because this package peempter.pkb is part of
2623 -- the CORE HR code and UK customers do not use these TAX tables.
2624 -- fix for bug: 1348553 et al.
2625 -- we always call the terminate_tax_records proc since we need to erase
2626 -- future changes if the termination date is set w/o a final process date.
2627 --
2628 if hr_general.chk_geocodes_installed = 'Y' then
2629 pay_us_update_tax_rec_pkg.terminate_emp_tax_records
2630 (c_ass_rec.assignment_id
2631 ,p_final_process_date
2632 ,p_actual_termination_date);
2633 end if; /* Check if geocodes is installed */
2634 --
2635 hr_utility.set_location(l_proc,1000);
2636 if (FPD_FLAG AND TA_FLAG) OR
2637 (p_actual_termination_date = p_final_process_date) then
2638 --
2639 hr_utility.set_location(l_proc,90);
2640 hrempter.delete_de_assign(c_ass_rec.assignment_id
2641 ,p_final_process_date);
2642 --
2643 hr_utility.set_location(l_proc,95);
2644 UPDATE per_assignments_f
2645 SET effective_end_date = p_final_process_date
2646 WHERE CURRENT OF c_assignment;
2647 --
2648 hr_utility.set_location(l_proc,1200);
2649 elsif NOT TA_FLAG then
2650 hr_utility.set_location(l_proc,95);
2651 if FPD_FLAG then
2652 l_effective_end_date := p_final_process_date;
2653 else
2654 l_effective_end_date := l_max_end_date;
2655 end if;
2656 --
2657 hr_utility.set_location(l_proc,100);
2658 hrempter.delete_de_assign(c_ass_rec.assignment_id
2659 ,p_actual_termination_date);
2660 --
2661 hr_utility.set_location(l_proc,105);
2662 --
2663 -- #270356. There was a possible problem here, if the user passes
2664 -- a null assignment status type, and a final process date which is
2665 -- is later than the actual termination date. Under these
2666 -- circumstances, the following insert is needed to create a new
2667 -- Terminated Assignment record, and fails because
2668 -- ASSIGNMENT_STATUS_TYPE_ID is mandatory. Fix is to get the default
2669 -- TERM_ASSIGN status immediately before the insert, if one hasn't
2670 -- already been passed in. The code looks for the default status in
2671 -- both the per_assignment_status_types table and the
2672 -- per_ass_status_type_amends table.
2673 --
2674 if p_assignment_status_type_id is null then
2675 select stt.assignment_status_type_id
2676 into l_assignment_status_type_id
2677 from per_assignment_status_types stt,
2678 per_ass_status_type_amends sta
2679 where nvl(sta.per_system_status,stt.per_system_status) =
2680 'TERM_ASSIGN'
2681 and stt.assignment_status_type_id =
2682 sta.assignment_status_type_id (+)
2683 and sta.business_group_id(+) = c_ass_rec.BUSINESS_GROUP_ID
2684 and nvl(stt.business_group_id, c_ass_rec.BUSINESS_GROUP_ID) =
2685 c_ass_rec.BUSINESS_GROUP_ID
2686 and nvl(sta.active_flag,stt.active_flag) = 'Y'
2687 and nvl(sta.default_flag, stt.default_flag) = 'Y';
2688 end if;
2689 --
2690 hr_utility.set_location(l_proc,108);
2691 -- VT 04/12/96 bug #354874 added EMPLOYMENT_CATEGORY processing
2692 INSERT INTO per_assignments_f
2693 ( ASSIGNMENT_ID
2694 , EFFECTIVE_START_DATE
2695 , EFFECTIVE_END_DATE
2696 , BUSINESS_GROUP_ID
2697 , RECRUITER_ID
2698 , GRADE_ID
2699 , POSITION_ID
2700 , JOB_ID
2701 , ASSIGNMENT_STATUS_TYPE_ID
2702 , PAYROLL_ID
2703 , LOCATION_ID
2704 , SUPERVISOR_ID
2705 , SPECIAL_CEILING_STEP_ID
2706 , PERSON_ID
2707 , ORGANIZATION_ID
2708 , PEOPLE_GROUP_ID
2709 , SOFT_CODING_KEYFLEX_ID
2710 , VACANCY_ID
2711 , ASSIGNMENT_SEQUENCE
2712 , ASSIGNMENT_TYPE
2713 , PRIMARY_FLAG
2714 , APPLICATION_ID
2715 , ASSIGNMENT_NUMBER
2716 , CHANGE_REASON
2717 , COMMENT_ID
2718 , DATE_PROBATION_END
2719 , DEFAULT_CODE_COMB_ID
2720 , EMPLOYMENT_CATEGORY
2721 , FREQUENCY
2722 , INTERNAL_ADDRESS_LINE
2723 , MANAGER_FLAG
2724 , NORMAL_HOURS
2725 , PERIOD_OF_SERVICE_ID
2726 , PROBATION_PERIOD
2727 , PROBATION_UNIT
2728 , SET_OF_BOOKS_ID
2729 , TIME_NORMAL_FINISH
2730 , TIME_NORMAL_START
2731 , PAY_BASIS_ID
2732 , REQUEST_ID
2733 , BARGAINING_UNIT_CODE
2734 , LABOUR_UNION_MEMBER_FLAG
2735 , HOURLY_SALARIED_CODE
2736 , PROGRAM_APPLICATION_ID
2737 , PROGRAM_ID
2738 , PROGRAM_UPDATE_DATE
2739 , LAST_UPDATE_DATE
2740 , LAST_UPDATED_BY
2741 , LAST_UPDATE_LOGIN
2742 , CREATED_BY
2743 , CREATION_DATE
2744 , ASS_ATTRIBUTE_CATEGORY
2745 , ASS_ATTRIBUTE1
2746 , ASS_ATTRIBUTE2
2747 , ASS_ATTRIBUTE3
2748 , ASS_ATTRIBUTE4
2749 , ASS_ATTRIBUTE5
2750 , ASS_ATTRIBUTE6
2751 , ASS_ATTRIBUTE7
2752 , ASS_ATTRIBUTE8
2753 , ASS_ATTRIBUTE9
2754 , ASS_ATTRIBUTE10
2755 , ASS_ATTRIBUTE11
2756 , ASS_ATTRIBUTE12
2757 , ASS_ATTRIBUTE13
2758 , ASS_ATTRIBUTE14
2759 , ASS_ATTRIBUTE15
2760 , ASS_ATTRIBUTE16
2761 , ASS_ATTRIBUTE17
2762 , ASS_ATTRIBUTE18
2763 , ASS_ATTRIBUTE19
2764 , ASS_ATTRIBUTE20
2765 , ASS_ATTRIBUTE21
2766 , ASS_ATTRIBUTE22
2767 , ASS_ATTRIBUTE23
2768 , ASS_ATTRIBUTE24
2769 , ASS_ATTRIBUTE25
2770 , ASS_ATTRIBUTE26
2771 , ASS_ATTRIBUTE27
2772 , ASS_ATTRIBUTE28
2773 , ASS_ATTRIBUTE29
2774 , ASS_ATTRIBUTE30
2775 , CAGR_GRADE_DEF_ID
2776 , CAGR_ID_FLEX_NUM
2777 , COLLECTIVE_AGREEMENT_ID
2778 , CONTRACT_ID
2779 , ESTABLISHMENT_ID
2780 , OBJECT_VERSION_NUMBER
2781 , PERF_REVIEW_PERIOD
2782 , PERF_REVIEW_PERIOD_FREQUENCY
2783 , PERSON_REFERRED_BY_ID
2784 , RECRUITMENT_ACTIVITY_ID
2785 , SAL_REVIEW_PERIOD
2786 , SAL_REVIEW_PERIOD_FREQUENCY
2787 , SOURCE_ORGANIZATION_ID
2788 , SOURCE_TYPE
2789 , TITLE
2790 , NOTICE_PERIOD
2791 , NOTICE_PERIOD_UOM
2792 , WORK_AT_HOME
2793 , EMPLOYEE_CATEGORY
2794 , JOB_POST_SOURCE_NAME )
2795 VALUES
2796 ( c_ass_rec.ASSIGNMENT_ID
2797 , p_actual_termination_date + 1
2798 , l_effective_end_date
2799 , c_ass_rec.BUSINESS_GROUP_ID
2800 , c_ass_rec.RECRUITER_ID
2801 , c_ass_rec.GRADE_ID
2802 , c_ass_rec.POSITION_ID
2803 , c_ass_rec.JOB_ID
2804 , nvl (p_assignment_status_type_id, l_assignment_status_type_id)
2805 , c_ass_rec.PAYROLL_ID
2806 , c_ass_rec.LOCATION_ID
2807 , c_ass_rec.SUPERVISOR_ID
2808 , c_ass_rec.SPECIAL_CEILING_STEP_ID
2809 , c_ass_rec.PERSON_ID
2810 , c_ass_rec.ORGANIZATION_ID
2811 , c_ass_rec.PEOPLE_GROUP_ID
2812 , c_ass_rec.SOFT_CODING_KEYFLEX_ID
2813 , c_ass_rec.VACANCY_ID
2814 , c_ass_rec.ASSIGNMENT_SEQUENCE
2815 , c_ass_rec.ASSIGNMENT_TYPE
2816 , c_ass_rec.PRIMARY_FLAG
2817 , c_ass_rec.APPLICATION_ID
2818 , c_ass_rec.ASSIGNMENT_NUMBER
2819 , c_ass_rec.CHANGE_REASON
2820 , c_ass_rec.COMMENT_ID
2821 , c_ass_rec.DATE_PROBATION_END
2822 , c_ass_rec.DEFAULT_CODE_COMB_ID
2823 , c_ass_rec.EMPLOYMENT_CATEGORY
2824 , c_ass_rec.FREQUENCY
2825 , c_ass_rec.INTERNAL_ADDRESS_LINE
2826 , c_ass_rec.MANAGER_FLAG
2827 , c_ass_rec.NORMAL_HOURS
2828 , c_ass_rec.PERIOD_OF_SERVICE_ID
2829 , c_ass_rec.PROBATION_PERIOD
2830 , c_ass_rec.PROBATION_UNIT
2831 , c_ass_rec.SET_OF_BOOKS_ID
2832 , c_ass_rec.TIME_NORMAL_FINISH
2833 , c_ass_rec.TIME_NORMAL_START
2834 , c_ass_rec.PAY_BASIS_ID
2835 , c_ass_rec.REQUEST_ID
2836 , c_ass_rec.BARGAINING_UNIT_CODE
2837 , c_ass_rec.LABOUR_UNION_MEMBER_FLAG
2838 , c_ass_rec.HOURLY_SALARIED_CODE
2839 , c_ass_rec.PROGRAM_APPLICATION_ID
2840 , c_ass_rec.PROGRAM_ID
2841 , c_ass_rec.PROGRAM_UPDATE_DATE
2842 , SYSDATE
2843 , -1
2844 , -1
2845 , c_ass_rec.CREATED_BY
2846 , c_ass_rec.CREATION_DATE
2847 , c_ass_rec.ASS_ATTRIBUTE_CATEGORY
2848 , c_ass_rec.ASS_ATTRIBUTE1
2849 , c_ass_rec.ASS_ATTRIBUTE2
2850 , c_ass_rec.ASS_ATTRIBUTE3
2851 , c_ass_rec.ASS_ATTRIBUTE4
2852 , c_ass_rec.ASS_ATTRIBUTE5
2853 , c_ass_rec.ASS_ATTRIBUTE6
2854 , c_ass_rec.ASS_ATTRIBUTE7
2855 , c_ass_rec.ASS_ATTRIBUTE8
2856 , c_ass_rec.ASS_ATTRIBUTE9
2857 , c_ass_rec.ASS_ATTRIBUTE10
2858 , c_ass_rec.ASS_ATTRIBUTE11
2859 , c_ass_rec.ASS_ATTRIBUTE12
2860 , c_ass_rec.ASS_ATTRIBUTE13
2861 , c_ass_rec.ASS_ATTRIBUTE14
2862 , c_ass_rec.ASS_ATTRIBUTE15
2863 , c_ass_rec.ASS_ATTRIBUTE16
2864 , c_ass_rec.ASS_ATTRIBUTE17
2865 , c_ass_rec.ASS_ATTRIBUTE18
2866 , c_ass_rec.ASS_ATTRIBUTE19
2867 , c_ass_rec.ASS_ATTRIBUTE20
2868 , c_ass_rec.ASS_ATTRIBUTE21
2869 , c_ass_rec.ASS_ATTRIBUTE22
2870 , c_ass_rec.ASS_ATTRIBUTE23
2871 , c_ass_rec.ASS_ATTRIBUTE24
2872 , c_ass_rec.ASS_ATTRIBUTE25
2873 , c_ass_rec.ASS_ATTRIBUTE26
2874 , c_ass_rec.ASS_ATTRIBUTE27
2875 , c_ass_rec.ASS_ATTRIBUTE28
2876 , c_ass_rec.ASS_ATTRIBUTE29
2877 , c_ass_rec.ASS_ATTRIBUTE30
2878 , c_ass_rec.CAGR_GRADE_DEF_ID
2879 , c_ass_rec.CAGR_ID_FLEX_NUM
2880 , c_ass_rec.COLLECTIVE_AGREEMENT_ID
2881 , c_ass_rec.CONTRACT_ID
2882 , c_ass_rec.ESTABLISHMENT_ID
2883 , c_ass_rec.OBJECT_VERSION_NUMBER
2884 , c_ass_rec.PERF_REVIEW_PERIOD
2885 , c_ass_rec.PERF_REVIEW_PERIOD_FREQUENCY
2886 , c_ass_rec.PERSON_REFERRED_BY_ID
2887 , c_ass_rec.RECRUITMENT_ACTIVITY_ID
2888 , c_ass_rec.SAL_REVIEW_PERIOD
2889 , c_ass_rec.SAL_REVIEW_PERIOD_FREQUENCY
2890 , c_ass_rec.SOURCE_ORGANIZATION_ID
2891 , c_ass_rec.SOURCE_TYPE
2892 , c_ass_rec.TITLE
2893 , c_ass_rec.NOTICE_PERIOD
2894 , c_ass_rec.NOTICE_PERIOD_UOM
2895 , c_ass_rec.WORK_AT_HOME
2896 , c_ass_rec.EMPLOYEE_CATEGORY
2897 , c_ass_rec.JOB_POST_SOURCE_NAME);
2898 --
2899 hr_utility.set_location(l_proc,110);
2900 UPDATE per_assignments_f ass
2901 SET ass.effective_end_date = p_actual_termination_date
2902 WHERE CURRENT OF c_assignment;
2903 --
2904 end if;
2905 --
2906 hr_utility.set_location(l_proc,1300);
2907 if NOT TA_FLAG then
2908 hr_utility.set_location(l_proc,115);
2909 hrempter.delete_employee_atd(p_person_id
2910 ,p_actual_termination_date);
2911 end if;
2912 --
2913 END LOOP;
2914 -- VT 10/14/96 bug #306710
2915 IF l_entries_changed IN ('S','Y') THEN
2916 IF l_entries_changed = 'S' THEN
2917 hr_utility.set_warning;
2918 fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
2919 END IF;
2920 IF l_entries_changed = 'Y' THEN
2921 hr_utility.set_warning;
2922 fnd_message.set_name('PAY','HR_7016_ASS_ENTRIES_CHANGED');
2923 END IF;
2924 END IF;
2925 --
2926 --
2927 else hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
2928 hr_utility.set_message_token('PROCEDURE','TERMINATE_EMPLOYEE');
2929 hr_utility.set_message_token('STEP',1);
2930 hr_utility.raise_error;
2931 --
2932 end if;
2933 --
2934 end terminate_employee;
2935 --
2936 --
2937 -- ====================== employee_shutdown ==========================
2938 --
2939 --
2940 PROCEDURE employee_shutdown(p_trigger VARCHAR2
2941 ,p_person_id NUMBER
2942 ,p_final_process_date DATE)
2943 IS
2944 --
2945 l_current_employee_flag VARCHAR2(30);
2946 l_max_end_date DATE;
2947 l_actual_termination_date DATE;
2948 l_action_chk VARCHAR2(1) := 'N';
2949 l_last_standard_process_date DATE := NULL;
2950 --
2951 l_entries_changed VARCHAR2(1) := 'N';
2952 l_ent_loop VARCHAR2(1) := 'N';
2953 --
2954 CURSOR c_assignment IS
2955 SELECT ass.assignment_id
2956 FROM per_assignments_f ass
2957 WHERE ass.person_id = p_person_id
2958 AND p_final_process_date
2959 BETWEEN ass.effective_start_date
2960 AND ass.effective_end_date
2961 FOR UPDATE;
2962 --
2963 l_proc varchar2(72):=g_package||'employee_shutdown';
2964 begin
2965 hr_utility.trace('Entered employee_shutdown for '||p_person_id);
2966 --
2967 hr_utility.set_location(l_proc,1);
2968 if p_trigger = 'PRE_UPDATE' then
2969 --
2970 hr_utility.set_location(l_proc,2);
2971 SELECT actual_termination_date
2972 INTO l_actual_termination_date
2973 FROM per_periods_of_service
2974 WHERE person_id = p_person_id
2975 AND final_process_date IS NULL
2976 AND p_final_process_date BETWEEN date_start
2977 AND to_date('31/12/4712','DD/MM/YYYY');
2978 --
2979 hr_utility.set_location(l_proc,3);
2980 SELECT current_employee_flag
2981 INTO l_current_employee_flag
2982 FROM per_people_f pp
2983 WHERE pp.person_id = p_person_id
2984 AND ((p_final_process_date = l_actual_termination_date
2985 AND p_final_process_date + 1 BETWEEN pp.effective_start_date
2986 AND pp.effective_end_date)
2987 OR (p_final_process_date > l_actual_termination_date
2988 AND p_final_process_date
2989 BETWEEN pp.effective_start_date
2990 AND pp.effective_end_date));
2991 --
2992 if l_current_employee_flag = 'Y' then
2993 hr_utility.set_message(801,'HR_6514_EMP_NOT_TERMINATED');
2994 hr_utility.raise_error;
2995 end if;
2996 --
2997 hr_utility.set_location(l_proc,5);
2998 -- VT 06/04/96 changed function call
2999 l_action_chk := hrempter.check_for_compl_actions(p_person_id
3000 ,l_actual_termination_date
3001 ,l_last_standard_process_date
3002 ,p_final_process_date);
3003 --
3004 IF l_action_chk = 'W' THEN
3005 hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3006 hr_utility.set_warning;
3007 END IF;
3008 --
3009 if l_action_chk = 'Y' then
3010 hr_utility.set_message(801,'HR_6517_EMP_FPD_ACTIONS_EXIST');
3011 hr_utility.raise_error;
3012 end if;
3013 --
3014 -----------------------------------------------------------------
3015 -- If the FPD is not null then check that COBRA Benefits do not
3016 -- exist after the FPD
3017 --
3018 hr_utility.set_location(l_proc,7);
3019 if hrempter.check_cobra_benefits
3020 (p_person_id
3021 ,p_final_process_date) then
3022 hr_utility.set_message(801,'HR_6968_EMP_COBRA_BENS_EXIST');
3023 hr_utility.raise_error;
3024 end if;
3025 --
3026 elsif p_trigger = 'POST_UPDATE' then
3027 --
3028 --
3029 hr_utility.set_location(l_proc,15);
3030 FOR c_ass_rec IN c_assignment LOOP
3031 --
3032 hr_utility.set_location(l_proc,20);
3033 l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3034 --
3035 if p_final_process_date < l_max_end_date then
3036 hr_utility.set_location(l_proc,25);
3037 hrempter.delete_assign_fpd(c_ass_rec.assignment_id
3038 ,p_final_process_date);
3039 --
3040 hr_utility.set_location(l_proc,30);
3041 -- VT 10/07/96 bug #306710 new parameter
3042 l_ent_loop := 'N';
3043 hrempter.terminate_entries_and_alus(c_ass_rec.assignment_id
3044 ,null
3045 ,null
3046 ,p_final_process_date
3047 ,null
3048 ,l_ent_loop);
3049 if l_ent_loop = 'S' then
3050 l_entries_changed := 'S';
3051 end if;
3052 if l_ent_loop = 'Y' and l_entries_changed = 'N' then
3053 l_entries_changed := 'Y';
3054 end if;
3055 --
3056 hr_utility.set_location(l_proc,35);
3057 hrempter.delete_de_assign(c_ass_rec.assignment_id
3058 ,p_final_process_date);
3059 --
3060 hr_utility.set_location(l_proc,40);
3061 UPDATE per_assignments_f ass
3062 SET ass.effective_end_date = p_final_process_date
3063 WHERE CURRENT OF c_assignment;
3064 end if;
3065 --
3066 -- bug 1348553 - if you set the atd, and later set the fpd, we need to
3067 -- terminate the tax records
3068 if hr_general.chk_geocodes_installed = 'Y' then
3069 pay_us_update_tax_rec_pkg.terminate_emp_tax_records
3070 (c_ass_rec.assignment_id
3071 ,p_final_process_date);
3072 end if; /* Check if geocodes is installed */
3073 END LOOP;
3074 -- VT 10/14/96 bug #306710
3075 IF l_entries_changed IN ('S','Y') THEN
3076 IF l_entries_changed = 'S' THEN
3077 hr_utility.set_warning;
3078 fnd_message.set_name('PAY','HR_7442_ASS_SAL_ENT_CHANGED');
3079 END IF;
3080 IF l_entries_changed = 'Y' THEN
3081 hr_utility.set_warning;
3082 fnd_message.set_name('PAY','HR_7016_ASS_ENTRIES_CHANGED');
3083 END IF;
3084 END IF;
3085 --
3086 --
3087 else hr_utility.set_location(l_proc,1);
3088 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
3089 hr_utility.set_message_token('PROCEDURE','EMPLOYEE_SHUTDOWN');
3090 hr_utility.set_message_token('STEP',1);
3091 hr_utility.raise_error;
3092 --
3093 end if;
3094 --
3095 end employee_shutdown;
3096 --
3097 -- ======================== Compare_assignments ========================
3098 --
3099 FUNCTION compareAssignments(p_assignment_id1 NUMBER,
3100 p_effective_date1 DATE,
3101 p_assignment_id2 NUMBER,
3102 p_effective_date2 DATE)
3103 return BOOLEAN IS
3104
3105 cursor c_full_assignment(c_assignment_id number,
3106 c_effective_date date) is
3107 select *
3108 from per_all_assignments_f
3109 where assignment_id = c_assignment_id
3110 and c_effective_date between effective_start_date
3111 and effective_end_date;
3112
3113 l_asg1 c_full_assignment%rowtype;
3114 l_asg2 c_full_assignment%rowtype;
3115
3116 l_return boolean := FALSE;
3117
3118 l_proc varchar2(90) := g_package||'compareAssignment';
3119
3120 begin
3121
3122 hr_utility.set_location('Entering : '||l_proc,10);
3123
3124 open c_full_assignment(p_assignment_id1, p_effective_date1);
3125 fetch c_full_assignment into l_asg1;
3126 close c_full_assignment;
3127
3128 open c_full_assignment(p_assignment_id2, p_effective_date2);
3129 fetch c_full_assignment into l_asg2;
3130 close c_full_assignment;
3131
3132 if ( nvl(l_asg1.RECRUITER_ID,hr_api.g_number)
3133 = nvl(l_asg2.RECRUITER_ID,hr_api.g_number)
3134 and nvl(l_asg1.GRADE_ID,hr_api.g_number)
3135 = nvl(l_asg2.GRADE_ID,hr_api.g_number)
3136 and nvl(l_asg1.POSITION_ID,hr_api.g_number)
3137 = nvl(l_asg2.POSITION_ID,hr_api.g_number)
3138 and nvl(l_asg1.JOB_ID,hr_api.g_number)
3139 = nvl(l_asg2.JOB_ID,hr_api.g_number)
3140 and nvl(l_asg1.ASSIGNMENT_STATUS_TYPE_ID,hr_api.g_number)
3141 = nvl(l_asg2.ASSIGNMENT_STATUS_TYPE_ID,hr_api.g_number)
3142 and nvl(l_asg1.PAYROLL_ID,hr_api.g_number)
3143 = nvl(l_asg2.PAYROLL_ID,hr_api.g_number)
3144 and nvl(l_asg1.LOCATION_ID,hr_api.g_number)
3145 = nvl(l_asg2.LOCATION_ID,hr_api.g_number)
3146 and nvl(l_asg1.PERSON_REFERRED_BY_ID,hr_api.g_number)
3147 = nvl(l_asg2.PERSON_REFERRED_BY_ID,hr_api.g_number)
3148 and nvl(l_asg1.SUPERVISOR_ID,hr_api.g_number)
3149 = nvl(l_asg2.SUPERVISOR_ID,hr_api.g_number)
3150 and nvl(l_asg1.SPECIAL_CEILING_STEP_ID,hr_api.g_number)
3151 = nvl(l_asg2.SPECIAL_CEILING_STEP_ID,hr_api.g_number)
3152 and nvl(l_asg1.PERSON_ID,hr_api.g_number)
3153 = nvl(l_asg2.PERSON_ID,hr_api.g_number)
3154 and nvl(l_asg1.RECRUITMENT_ACTIVITY_ID,hr_api.g_number)
3155 = nvl(l_asg2.RECRUITMENT_ACTIVITY_ID,hr_api.g_number)
3156 and nvl(l_asg1.SOURCE_ORGANIZATION_ID,hr_api.g_number)
3157 = nvl(l_asg2.SOURCE_ORGANIZATION_ID,hr_api.g_number)
3158 and nvl(l_asg1.ORGANIZATION_ID,hr_api.g_number)
3159 = nvl(l_asg2.ORGANIZATION_ID,hr_api.g_number)
3160 and nvl(l_asg1.PEOPLE_GROUP_ID,hr_api.g_number)
3161 = nvl(l_asg2.PEOPLE_GROUP_ID,hr_api.g_number)
3162 and nvl(l_asg1.SOFT_CODING_KEYFLEX_ID,hr_api.g_number)
3163 = nvl(l_asg2.SOFT_CODING_KEYFLEX_ID,hr_api.g_number)
3164 and nvl(l_asg1.VACANCY_ID,hr_api.g_number)
3165 = nvl(l_asg2.VACANCY_ID,hr_api.g_number)
3166 and nvl(l_asg1.PAY_BASIS_ID,hr_api.g_number)
3167 = nvl(l_asg2.PAY_BASIS_ID,hr_api.g_number)
3168 and nvl(l_asg1.ASSIGNMENT_SEQUENCE,hr_api.g_number)
3169 = nvl(l_asg2.ASSIGNMENT_SEQUENCE,hr_api.g_number)
3170 and nvl(l_asg1.ASSIGNMENT_TYPE, hr_api.g_varchar2)
3171 = nvl(l_asg2.ASSIGNMENT_TYPE, hr_api.g_varchar2)
3172 and nvl(l_asg1.PRIMARY_FLAG, hr_api.g_varchar2)
3173 = nvl(l_asg2.PRIMARY_FLAG, hr_api.g_varchar2)
3174 and nvl(l_asg1.APPLICATION_ID,hr_api.g_number)
3175 = nvl(l_asg2.APPLICATION_ID,hr_api.g_number)
3176 and nvl(l_asg1.ASSIGNMENT_NUMBER,hr_api.g_number)
3177 = nvl(l_asg2.ASSIGNMENT_NUMBER,hr_api.g_number)
3178 and nvl(l_asg1.CHANGE_REASON, hr_api.g_varchar2)
3179 = nvl(l_asg2.CHANGE_REASON, hr_api.g_varchar2)
3180 and nvl(l_asg1.COMMENT_ID,hr_api.g_number)
3181 = nvl(l_asg2.COMMENT_ID,hr_api.g_number)
3182 and nvl(l_asg1.DATE_PROBATION_END, hr_api.g_date)
3183 = nvl(l_asg2.DATE_PROBATION_END, hr_api.g_date)
3184 and nvl(l_asg1.DEFAULT_CODE_COMB_ID,hr_api.g_number)
3185 = nvl(l_asg2.DEFAULT_CODE_COMB_ID,hr_api.g_number)
3186 and nvl(l_asg1.EMPLOYMENT_CATEGORY, hr_api.g_varchar2)
3187 = nvl(l_asg2.EMPLOYMENT_CATEGORY, hr_api.g_varchar2)
3188 and nvl(l_asg1.FREQUENCY, hr_api.g_varchar2)
3189 = nvl(l_asg2.FREQUENCY, hr_api.g_varchar2)
3190 and nvl(l_asg1.INTERNAL_ADDRESS_LINE, hr_api.g_varchar2)
3191 = nvl(l_asg2.INTERNAL_ADDRESS_LINE, hr_api.g_varchar2)
3192 and nvl(l_asg1.MANAGER_FLAG, hr_api.g_varchar2)
3193 = nvl(l_asg2.MANAGER_FLAG, hr_api.g_varchar2)
3194 and nvl(l_asg1.NORMAL_HOURS,hr_api.g_number)
3195 = nvl(l_asg2.NORMAL_HOURS,hr_api.g_number)
3196 and nvl(l_asg1.PERF_REVIEW_PERIOD,hr_api.g_number)
3197 = nvl(l_asg2.PERF_REVIEW_PERIOD,hr_api.g_number)
3198 and nvl(l_asg1.PERF_REVIEW_PERIOD_FREQUENCY, hr_api.g_varchar2)
3199 = nvl(l_asg2.PERF_REVIEW_PERIOD_FREQUENCY, hr_api.g_varchar2)
3200 and nvl(l_asg1.PERIOD_OF_SERVICE_ID,hr_api.g_number)
3201 = nvl(l_asg2.PERIOD_OF_SERVICE_ID,hr_api.g_number)
3202 and nvl(l_asg1.PROBATION_PERIOD,hr_api.g_number)
3203 = nvl(l_asg2.PROBATION_PERIOD,hr_api.g_number)
3204 and nvl(l_asg1.PROBATION_UNIT, hr_api.g_varchar2)
3205 = nvl(l_asg2.PROBATION_UNIT, hr_api.g_varchar2)
3206 and nvl(l_asg1.SAL_REVIEW_PERIOD,hr_api.g_number)
3207 = nvl(l_asg2.SAL_REVIEW_PERIOD,hr_api.g_number)
3208 and nvl(l_asg1.SAL_REVIEW_PERIOD_FREQUENCY, hr_api.g_varchar2)
3209 = nvl(l_asg2.SAL_REVIEW_PERIOD_FREQUENCY, hr_api.g_varchar2)
3210 and nvl(l_asg1.SET_OF_BOOKS_ID,hr_api.g_number)
3211 = nvl(l_asg2.SET_OF_BOOKS_ID,hr_api.g_number)
3212 and nvl(l_asg1.SOURCE_TYPE, hr_api.g_varchar2)
3213 = nvl(l_asg2.SOURCE_TYPE, hr_api.g_varchar2)
3214 and nvl(l_asg1.TIME_NORMAL_FINISH, hr_api.g_varchar2)
3215 = nvl(l_asg2.TIME_NORMAL_FINISH, hr_api.g_varchar2)
3216 and nvl(l_asg1.TIME_NORMAL_START, hr_api.g_varchar2)
3217 = nvl(l_asg2.TIME_NORMAL_START, hr_api.g_varchar2)
3218 and nvl(l_asg1.ASS_ATTRIBUTE_CATEGORY, hr_api.g_varchar2)
3219 = nvl(l_asg2.ASS_ATTRIBUTE_CATEGORY, hr_api.g_varchar2)
3220 and nvl(l_asg1.ASS_ATTRIBUTE1, hr_api.g_varchar2)
3221 = nvl(l_asg2.ASS_ATTRIBUTE1, hr_api.g_varchar2)
3222 and nvl(l_asg1.ASS_ATTRIBUTE2, hr_api.g_varchar2)
3223 = nvl(l_asg2.ASS_ATTRIBUTE2, hr_api.g_varchar2)
3224 and nvl(l_asg1.ASS_ATTRIBUTE3, hr_api.g_varchar2)
3225 = nvl(l_asg2.ASS_ATTRIBUTE3, hr_api.g_varchar2)
3226 and nvl(l_asg1.ASS_ATTRIBUTE4, hr_api.g_varchar2)
3227 = nvl(l_asg2.ASS_ATTRIBUTE4, hr_api.g_varchar2)
3228 and nvl(l_asg1.ASS_ATTRIBUTE5, hr_api.g_varchar2)
3229 = nvl(l_asg2.ASS_ATTRIBUTE5, hr_api.g_varchar2)
3230 and nvl(l_asg1.ASS_ATTRIBUTE6, hr_api.g_varchar2)
3231 = nvl(l_asg2.ASS_ATTRIBUTE6, hr_api.g_varchar2)
3232 and nvl(l_asg1.ASS_ATTRIBUTE7, hr_api.g_varchar2)
3233 = nvl(l_asg2.ASS_ATTRIBUTE7, hr_api.g_varchar2)
3234 and nvl(l_asg1.ASS_ATTRIBUTE8, hr_api.g_varchar2)
3235 = nvl(l_asg2.ASS_ATTRIBUTE8, hr_api.g_varchar2)
3236 and nvl(l_asg1.ASS_ATTRIBUTE9, hr_api.g_varchar2)
3237 = nvl(l_asg2.ASS_ATTRIBUTE9, hr_api.g_varchar2)
3238 and nvl(l_asg1.ASS_ATTRIBUTE10, hr_api.g_varchar2)
3239 = nvl(l_asg2.ASS_ATTRIBUTE10, hr_api.g_varchar2)
3240 and nvl(l_asg1.ASS_ATTRIBUTE11, hr_api.g_varchar2)
3241 = nvl(l_asg2.ASS_ATTRIBUTE11, hr_api.g_varchar2)
3242 and nvl(l_asg1.ASS_ATTRIBUTE12, hr_api.g_varchar2)
3243 = nvl(l_asg2.ASS_ATTRIBUTE12, hr_api.g_varchar2)
3244 and nvl(l_asg1.ASS_ATTRIBUTE13, hr_api.g_varchar2)
3245 = nvl(l_asg2.ASS_ATTRIBUTE13, hr_api.g_varchar2)
3246 and nvl(l_asg1.ASS_ATTRIBUTE14, hr_api.g_varchar2)
3247 = nvl(l_asg2.ASS_ATTRIBUTE14, hr_api.g_varchar2)
3248 and nvl(l_asg1.ASS_ATTRIBUTE15, hr_api.g_varchar2)
3249 = nvl(l_asg2.ASS_ATTRIBUTE15, hr_api.g_varchar2)
3250 and nvl(l_asg1.ASS_ATTRIBUTE16, hr_api.g_varchar2)
3251 = nvl(l_asg2.ASS_ATTRIBUTE16, hr_api.g_varchar2)
3252 and nvl(l_asg1.ASS_ATTRIBUTE17, hr_api.g_varchar2)
3253 = nvl(l_asg2.ASS_ATTRIBUTE17, hr_api.g_varchar2)
3254 and nvl(l_asg1.ASS_ATTRIBUTE18, hr_api.g_varchar2)
3255 = nvl(l_asg2.ASS_ATTRIBUTE18, hr_api.g_varchar2)
3256 and nvl(l_asg1.ASS_ATTRIBUTE19, hr_api.g_varchar2)
3257 = nvl(l_asg2.ASS_ATTRIBUTE19, hr_api.g_varchar2)
3258 and nvl(l_asg1.ASS_ATTRIBUTE20, hr_api.g_varchar2)
3259 = nvl(l_asg2.ASS_ATTRIBUTE20, hr_api.g_varchar2)
3260 and nvl(l_asg1.ASS_ATTRIBUTE21, hr_api.g_varchar2)
3261 = nvl(l_asg2.ASS_ATTRIBUTE21, hr_api.g_varchar2)
3262 and nvl(l_asg1.ASS_ATTRIBUTE22, hr_api.g_varchar2)
3263 = nvl(l_asg2.ASS_ATTRIBUTE22, hr_api.g_varchar2)
3264 and nvl(l_asg1.ASS_ATTRIBUTE23, hr_api.g_varchar2)
3265 = nvl(l_asg2.ASS_ATTRIBUTE23, hr_api.g_varchar2)
3266 and nvl(l_asg1.ASS_ATTRIBUTE24, hr_api.g_varchar2)
3267 = nvl(l_asg2.ASS_ATTRIBUTE24, hr_api.g_varchar2)
3268 and nvl(l_asg1.ASS_ATTRIBUTE25, hr_api.g_varchar2)
3269 = nvl(l_asg2.ASS_ATTRIBUTE25, hr_api.g_varchar2)
3270 and nvl(l_asg1.ASS_ATTRIBUTE26, hr_api.g_varchar2)
3271 = nvl(l_asg2.ASS_ATTRIBUTE26, hr_api.g_varchar2)
3272 and nvl(l_asg1.ASS_ATTRIBUTE27, hr_api.g_varchar2)
3273 = nvl(l_asg2.ASS_ATTRIBUTE27, hr_api.g_varchar2)
3274 and nvl(l_asg1.ASS_ATTRIBUTE28, hr_api.g_varchar2)
3275 = nvl(l_asg2.ASS_ATTRIBUTE28, hr_api.g_varchar2)
3276 and nvl(l_asg1.ASS_ATTRIBUTE29, hr_api.g_varchar2)
3277 = nvl(l_asg2.ASS_ATTRIBUTE29, hr_api.g_varchar2)
3278 and nvl(l_asg1.ASS_ATTRIBUTE30, hr_api.g_varchar2)
3279 = nvl(l_asg2.ASS_ATTRIBUTE30, hr_api.g_varchar2)
3280 and nvl(l_asg1.TITLE, hr_api.g_varchar2)
3281 = nvl(l_asg2.TITLE, hr_api.g_varchar2)
3282 and nvl(l_asg1.BARGAINING_UNIT_CODE, hr_api.g_varchar2)
3283 = nvl(l_asg2.BARGAINING_UNIT_CODE, hr_api.g_varchar2)
3284 and nvl(l_asg1.LABOUR_UNION_MEMBER_FLAG, hr_api.g_varchar2)
3285 = nvl(l_asg2.LABOUR_UNION_MEMBER_FLAG, hr_api.g_varchar2)
3286 and nvl(l_asg1.HOURLY_SALARIED_CODE, hr_api.g_varchar2)
3287 = nvl(l_asg2.HOURLY_SALARIED_CODE, hr_api.g_varchar2)
3288 and nvl(l_asg1.CONTRACT_ID,hr_api.g_number)
3289 = nvl(l_asg2.CONTRACT_ID,hr_api.g_number)
3290 and nvl(l_asg1.COLLECTIVE_AGREEMENT_ID,hr_api.g_number)
3291 = nvl(l_asg2.COLLECTIVE_AGREEMENT_ID,hr_api.g_number)
3292 and nvl(l_asg1.CAGR_ID_FLEX_NUM,hr_api.g_number)
3293 = nvl(l_asg2.CAGR_ID_FLEX_NUM,hr_api.g_number)
3294 and nvl(l_asg1.CAGR_GRADE_DEF_ID,hr_api.g_number)
3295 = nvl(l_asg2.CAGR_GRADE_DEF_ID,hr_api.g_number)
3296 and nvl(l_asg1.ESTABLISHMENT_ID,hr_api.g_number)
3297 = nvl(l_asg2.ESTABLISHMENT_ID,hr_api.g_number)
3298 and nvl(l_asg1.NOTICE_PERIOD,hr_api.g_number)
3299 = nvl(l_asg2.NOTICE_PERIOD,hr_api.g_number)
3300 and nvl(l_asg1.NOTICE_PERIOD_UOM, hr_api.g_varchar2)
3301 = nvl(l_asg2.NOTICE_PERIOD_UOM, hr_api.g_varchar2)
3302 and nvl(l_asg1.EMPLOYEE_CATEGORY, hr_api.g_varchar2)
3303 = nvl(l_asg2.EMPLOYEE_CATEGORY, hr_api.g_varchar2)
3304 and nvl(l_asg1.WORK_AT_HOME, hr_api.g_varchar2)
3305 = nvl(l_asg2.WORK_AT_HOME, hr_api.g_varchar2)
3306 and nvl(l_asg1.JOB_POST_SOURCE_NAME, hr_api.g_varchar2)
3307 = nvl(l_asg2.JOB_POST_SOURCE_NAME, hr_api.g_varchar2)
3308 and nvl(l_asg1.POSTING_CONTENT_ID,hr_api.g_number)
3309 = nvl(l_asg2.POSTING_CONTENT_ID,hr_api.g_number)
3310 and nvl(l_asg1.PERIOD_OF_PLACEMENT_DATE_START, hr_api.g_date)
3311 = nvl(l_asg2.PERIOD_OF_PLACEMENT_DATE_START, hr_api.g_date)
3312 and nvl(l_asg1.VENDOR_ID,hr_api.g_number)
3313 = nvl(l_asg2.VENDOR_ID,hr_api.g_number)
3314 and nvl(l_asg1.VENDOR_EMPLOYEE_NUMBER, hr_api.g_varchar2)
3315 = nvl(l_asg2.VENDOR_EMPLOYEE_NUMBER, hr_api.g_varchar2)
3316 and nvl(l_asg1.VENDOR_ASSIGNMENT_NUMBER, hr_api.g_varchar2)
3317 = nvl(l_asg2.VENDOR_ASSIGNMENT_NUMBER, hr_api.g_varchar2)
3318 and nvl(l_asg1.ASSIGNMENT_CATEGORY, hr_api.g_varchar2)
3319 = nvl(l_asg2.ASSIGNMENT_CATEGORY, hr_api.g_varchar2)
3320 and nvl(l_asg1.PROJECT_TITLE, hr_api.g_varchar2)
3321 = nvl(l_asg2.PROJECT_TITLE, hr_api.g_varchar2)
3322 and nvl(l_asg1.APPLICANT_RANK,hr_api.g_number)
3323 = nvl(l_asg2.APPLICANT_RANK,hr_api.g_number)
3324 ) then
3325 hr_utility.set_location(l_proc, 20);
3326 l_return := TRUE;
3327 end if;
3328 hr_utility.set_location('Leaving : '||l_proc, 30);
3329 return l_return;
3330 end; /* compareAssignments */
3331 --
3332 -- ======================= cancel_termination ==========================
3333 --
3334 --
3335 PROCEDURE cancel_termination(p_person_id NUMBER
3336 ,p_actual_termination_date DATE
3337 ,p_clear_details VARCHAR2 DEFAULT 'N')
3338 IS
3339 --
3340 --
3341 l_final_process_date DATE;
3342 l_last_standard_process_date DATE;
3343 l_per_system_status VARCHAR2(30);
3344 l_max_end_date DATE;
3345 l_effective_end_date DATE;
3346 l_action_chk VARCHAR2(1) := 'N';
3347 l_asg_status_type_id NUMBER;
3348 l_new_effective_end_date DATE;
3349 --
3350 l_period_of_service_id number;
3351 l_business_group_id number;
3352 l_date_start DATE;
3353 l_old_leaving_reason varchar2(60);
3354 l_proc_call varchar2(500);
3355 l_package_name varchar2(50);
3356 l_procedure_name varchar2(50);
3357 l_leg_code varchar2(150);
3358 l_dummy varchar2(1);
3359 --
3360 FPD_FLAG BOOLEAN;
3361 --
3362 -- start of bug 4919804
3363 /*
3364 CURSOR c_assignment IS
3365 SELECT assignment_id
3366 , assignment_status_type_id
3367 , business_group_id
3368 FROM per_assignments_f ass
3369 WHERE ass.person_id = p_person_id
3370 AND ass.effective_end_date = p_actual_termination_date
3371 FOR UPDATE;
3372 */
3373 -- commmented the cursor and redeclared the cursor c_assignment
3374
3375 CURSOR c_assignment IS
3376 SELECT assignment_id
3377 , assignment_status_type_id
3378 , business_group_id
3379 , effective_start_date
3380 , effective_end_date
3381 , payroll_id
3382 , object_version_number
3383 FROM per_assignments_f ass
3384 WHERE ass.person_id = p_person_id
3385 AND ass.effective_end_date = p_actual_termination_date
3386 FOR UPDATE;
3387 --
3388 l_effective_end_of_time date;
3389 l_session_date date;
3390 l_effective_end_date1 date;
3391 l_effective_start_date1 date;
3392 l_object_version_number number :=null;
3393 -- end of bug 4919804
3394
3395 cursor c1 is
3396 select *
3397 from per_periods_of_service
3398 where person_id = p_person_id
3399 and actual_termination_date = p_actual_termination_date;
3400 --
3401 -- fix 1370960
3402 cursor csr_roles is
3403 select role_id
3404 ,object_version_number
3405 ,old_end_date from
3406 per_roles
3407 where person_id = p_person_id
3408 and end_date = p_actual_termination_date
3409 for update nowait;
3410 -- fix 1370960 end
3411
3412 l_c1 c1%rowtype;
3413 --
3414 --
3415 -- START WWBUG fix for 1390173
3416 --
3417 l_old ben_pps_ler.g_pps_ler_rec;
3418 l_new ben_pps_ler.g_pps_ler_rec;
3419 --
3420 --
3421 -- END WWBUG fix for 1390173
3422 --
3423 --
3424 -- Start of Fix for WWBUG 1408379
3425 --
3426 cursor c2(p_assignment_id number) is
3427 select *
3428 from per_assignment_budget_values_f
3429 where assignment_id = p_assignment_id
3430 and effective_end_date = l_final_process_date;
3431 --
3432 l_old_abv ben_abv_ler.g_abv_ler_rec;
3433 l_new_abv ben_abv_ler.g_abv_ler_rec;
3434 l_c2 c2%rowtype;
3435 --
3436 -- End of Fix for WWBUG 1408379
3437 --
3438 -- following cursors added for bug fix 3889294.
3439 --
3440 cursor csr_leg_code(p_business_group_id number) is
3441 select legislation_code
3442 from per_business_groups
3443 where business_group_id = p_business_group_id;
3444 --
3445 cursor csr_leg_pkg(p_pkg_name varchar2) is
3446 select '1'
3447 from user_objects
3448 where object_name = p_pkg_name
3449 and object_type = 'PACKAGE';
3450 --
3451 l_proc varchar2(72):=g_package||'cancel_termination';
3452 --
3453 begin
3454 --
3455 hr_utility.trace('Entering cancel_termination for '||p_person_id);
3456 --
3457 hr_utility.set_location(l_proc,1);
3458 --
3459 begin
3460 --
3461 SELECT pos.period_of_service_id
3462 , pos.business_group_id
3463 , pos.leaving_reason
3464 , pos.date_start
3465 , pos.final_process_date
3466 , pos.last_standard_process_date
3467 INTO l_period_of_service_id
3468 , l_business_group_id
3469 , l_old_leaving_reason
3470 , l_date_start
3471 , l_final_process_date
3472 , l_last_standard_process_date
3473 FROM per_periods_of_service pos
3474 WHERE pos.person_id = p_person_id
3475 AND pos.actual_termination_date = p_actual_termination_date;
3476 --
3477 exception when NO_DATA_FOUND then
3478 hr_utility.set_message(801,'HR_6515_EMP_NO_CANCEL');
3479 hr_utility.raise_error;
3480 --
3481 end;
3482 --
3483 --- newly added code to cancel termination of contracts start
3484 /* UPDATE per_contracts_f pc
3485 SET pc.effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
3486 WHERE pc.person_id = p_person_id
3487 AND p_actual_termination_date
3488 BETWEEN pc.effective_start_date
3489 AND pc.effective_end_date;
3490 --
3491 DELETE from per_contracts_f pc
3492 WHERE pc.person_id = p_person_id
3493 AND pc.effective_start_date > p_actual_termination_date;*/
3494 --- newly added code to cancel termination of contracts end
3495 --
3496 -- hr_utility.set_location(l_proc,2);
3497 l_action_chk := hrempter.check_for_future_person_type(p_person_id
3498 ,p_actual_termination_date);
3499 --
3500 if l_action_chk = 'Y' then
3501 hr_utility.set_message(801,'HR_7122_EMP_CNCL_TERM_INVLD');
3502 hr_utility.raise_error;
3503 end if;
3504 --
3505 hr_utility.set_location(l_proc,99);
3506 -- VT 06/04/96 changed function call
3507 l_action_chk := hrempter.check_for_compl_actions(p_person_id
3508 ,p_actual_termination_date
3509 ,l_last_standard_process_date
3510 ,l_final_process_date);
3511 --
3512 IF l_action_chk = 'W' THEN
3513 --
3514 -- Fix for bug 3100620 starts here. message is changed.
3515 --
3516 -- hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3517 hr_utility.set_message(800,'PER_449053_EMP_TERM_FUT_ERROR');
3518 --
3519 -- Fix for bug 3100620 ends here.
3520 --
3521 hr_utility.set_warning;
3522 END IF;
3523 --
3524 if l_action_chk = 'Y' then
3525 hr_utility.set_message(801,'HR_6519_EMP_CANC_ACTIONS_EXIST');
3526 hr_utility.raise_error;
3527 end if;
3528 --
3529 hr_utility.set_location(l_proc,3);
3530 FPD_FLAG := (l_final_process_date IS NOT NULL);
3531 --
3532 hr_utility.set_location(l_proc,4);
3533 UPDATE per_all_people_f pp
3534 SET pp.effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
3535 WHERE pp.person_id = p_person_id
3536 AND p_actual_termination_date
3537 BETWEEN pp.effective_start_date
3538 AND pp.effective_end_date;
3539 --
3540 hr_utility.set_location(l_proc,6);
3541 DELETE per_all_people_f pp
3542 WHERE pp.person_id = p_person_id
3543 AND pp.effective_start_date > p_actual_termination_date;
3544 --
3545 hr_utility.set_location(l_proc,8);
3546 --
3547 -- WWBUG # - CERN want to keep old details
3548 -- was a feature of rel 9
3549 if (p_clear_details = 'N') then
3550 --
3551 --
3552 -- START WWBUG fix for 1390173
3553 --
3554 open c1;
3555 fetch c1 into l_c1;
3556 if c1%found then
3557 --
3558 l_old.PERSON_ID := l_c1.person_id;
3559 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
3560 l_old.DATE_START := l_c1.date_start;
3561 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
3562 l_old.LEAVING_REASON := l_c1.leaving_reason;
3563 l_old.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
3564 l_old.ATTRIBUTE1 := l_c1.attribute1;
3565 l_old.ATTRIBUTE2 := l_c1.attribute2;
3566 l_old.ATTRIBUTE3 := l_c1.attribute3;
3567 l_old.ATTRIBUTE4 := l_c1.attribute4;
3568 l_old.ATTRIBUTE5 := l_c1.attribute5;
3569 l_old.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3570 l_new.PERSON_ID := l_c1.person_id;
3571 l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
3572 l_new.DATE_START := l_c1.date_start;
3573 l_new.ACTUAL_TERMINATION_DATE := null;
3574 l_new.LEAVING_REASON := null;
3575 l_new.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
3576 l_new.ATTRIBUTE1 := l_c1.attribute1;
3577 l_new.ATTRIBUTE2 := l_c1.attribute2;
3578 l_new.ATTRIBUTE3 := l_c1.attribute3;
3579 l_new.ATTRIBUTE4 := l_c1.attribute4;
3580 l_new.ATTRIBUTE5 := l_c1.attribute5;
3581 l_new.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3582 --
3583 ben_pps_ler.ler_chk(p_old => l_old
3584 ,p_new => l_new
3585 ,p_event => 'UPDATING'
3586 ,p_effective_date => l_c1.date_start);
3587 --
3588 end if;
3589 close c1;
3590 --
3591 --
3592 -- END WWBUG fix for 1390173
3593 --
3594 UPDATE per_periods_of_service pos
3595 SET pos.actual_termination_date = null
3596 , pos.last_standard_process_date = null
3597 , pos.final_process_date = null
3598 , pos.termination_accepted_person_id = null
3599 , pos.leaving_reason = null
3600 , pos.accepted_termination_date = null
3601 WHERE pos.person_id = p_person_id
3602 AND pos.actual_termination_date = p_actual_termination_date;
3603 else
3604 --
3605 --
3606 -- START WWBUG fix for 1390173
3607 --
3608 open c1;
3609 fetch c1 into l_c1;
3610 if c1%found then
3611 --
3612 l_old.PERSON_ID := l_c1.person_id;
3613 l_old.BUSINESS_GROUP_ID := l_c1.business_group_id;
3614 l_old.DATE_START := l_c1.date_start;
3615 l_old.ACTUAL_TERMINATION_DATE := l_c1.actual_termination_date;
3616 l_old.LEAVING_REASON := l_c1.leaving_reason;
3617 l_old.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
3618 l_old.ATTRIBUTE1 := l_c1.attribute1;
3619 l_old.ATTRIBUTE2 := l_c1.attribute2;
3620 l_old.ATTRIBUTE3 := l_c1.attribute3;
3621 l_old.ATTRIBUTE4 := l_c1.attribute4;
3622 l_old.ATTRIBUTE5 := l_c1.attribute5;
3623 l_old.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3624 l_new.PERSON_ID := l_c1.person_id;
3625 l_new.BUSINESS_GROUP_ID := l_c1.business_group_id;
3626 l_new.DATE_START := l_c1.date_start;
3627 l_new.ACTUAL_TERMINATION_DATE := null;
3628 l_new.LEAVING_REASON := null;
3629 l_new.ADJUSTED_SVC_DATE := l_c1.adjusted_svc_date;
3630 l_new.ATTRIBUTE1 := l_c1.attribute1;
3631 l_new.ATTRIBUTE2 := l_c1.attribute2;
3632 l_new.ATTRIBUTE3 := l_c1.attribute3;
3633 l_new.ATTRIBUTE4 := l_c1.attribute4;
3634 l_new.ATTRIBUTE5 := l_c1.attribute5;
3635 l_new.FINAL_PROCESS_DATE := l_c1.FINAL_PROCESS_DATE;
3636 --
3637 ben_pps_ler.ler_chk(p_old => l_old
3638 ,p_new => l_new
3639 ,p_event => 'UPDATING'
3640 ,p_effective_date => l_c1.date_start);
3641 --
3642 end if;
3643 close c1;
3644 --
3645 --
3646 -- END WWBUG fix for 1390173
3647 --
3648 UPDATE per_periods_of_service pos
3649 SET pos.actual_termination_date = null
3650 , pos.last_standard_process_date = null
3651 , pos.final_process_date = null
3652 , pos.termination_accepted_person_id = null
3653 , pos.leaving_reason = null
3654 , pos.notified_termination_date = null
3655 , pos.projected_termination_date = null
3656 , pos.accepted_termination_date = null
3657 WHERE pos.person_id = p_person_id
3658 AND pos.actual_termination_date = p_actual_termination_date;
3659 --
3660 end if;
3661 --
3662 --
3663 --
3664 -- FIX to WWBUG 1176101
3665 --
3666 ben_dt_trgr_handle.periods_of_service
3667 (p_rowid => null
3668 ,p_person_id => p_person_id
3669 ,p_pds_atd => null
3670 ,p_pds_leaving_reason => null
3671 -- Bug 1854968
3672 ,p_pds_old_atd => l_old.actual_termination_date
3673 ,p_pds_fpd => null);
3674 --
3675 --
3676 --
3677 hr_utility.set_location(l_proc,12);
3678
3679 FOR c_ass_rec IN c_assignment LOOP
3680 --
3681 hr_utility.set_location(l_proc,14);
3682 SELECT per_system_status
3683 INTO l_per_system_status
3684 FROM per_assignment_status_types
3685 WHERE assignment_status_type_id = c_ass_rec.assignment_status_type_id;
3686 --
3687 --
3688 -- Note the assignment_status_id from the record which ended on ATD
3689 -- so that we can set the other records for this assignment which
3690 -- are currently TERM_ASSIGN back to the appropriate ACTIVE_ASG status.
3691 --
3692 l_asg_status_type_id := c_ass_rec.assignment_status_type_id;
3693 --
3694 hr_utility.set_location(l_proc,16);
3695 l_max_end_date := hrempter.get_max_end_date(c_ass_rec.assignment_id);
3696 --
3697 --if l_per_system_status <> 'TERM_ASSIGN' then
3698 -- bug 5152164 commented out the above if condition and removed the condition
3699 -- added in previous fix for the same bug
3700 --
3701 hr_utility.set_location(l_proc,18);
3702 if FPD_FLAG then
3703 hr_utility.set_location(l_proc,20);
3704 if l_max_end_date <> l_final_process_date then
3705 l_effective_end_date := l_max_end_date;
3706 else
3707 hr_utility.set_location(l_proc,22);
3708 l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
3709 end if;
3710 else
3711 hr_utility.set_location(l_proc,24);
3712 l_effective_end_date := l_max_end_date;
3713 end if;
3714 --
3715 hr_utility.set_location(l_proc,26);
3716 --
3717 -- Open out the last dated assignment record to the end of time or
3718 -- max_end_date based on above logic.
3719 --
3720 -- start of bug fix 4919804
3721 l_effective_end_of_time := to_date('31/12/4712','DD/MM/YYYY');
3722 l_object_version_number := c_ass_rec.object_version_number;
3723 l_session_date :=hr_general.effective_date();
3724 l_effective_end_date1 := c_ass_rec.effective_end_date;
3725 l_effective_start_date1:=c_ass_rec.effective_start_date;
3726 -- end of bug fix 4919804
3727
3728 UPDATE per_assignments_f ass
3729 SET ass.effective_end_date = l_effective_end_date
3730 WHERE assignment_id = c_ass_rec.assignment_id
3731 AND effective_end_date = l_max_end_date;
3732
3733 -- added the following call as per the bug 4919804
3734
3735 -- bug 5152164 added the following if condition
3736 --
3737 if l_per_system_status <> 'TERM_ASSIGN' then
3738
3739 pay_pog_all_assignments_pkg.after_delete
3740 (p_effective_date => l_session_date
3741 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
3742 ,p_validation_start_date => c_ass_rec.effective_START_DATE
3743 ,p_validation_end_date => c_ass_rec.effective_end_date
3744 ,P_ASSIGNMENT_ID => c_ass_rec.assignment_id
3745 ,P_EFFECTIVE_END_DATE => l_effective_end_of_time
3746 ,P_EFFECTIVE_START_DATE => c_ass_rec.effective_START_DATE
3747 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3748 ,P_ORG_NOW_NO_MANAGER_WARNING => null
3749 ,P_APPLICANT_RANK_O => null
3750 ,P_APPLICATION_ID_O => null
3751 ,P_ASSIGNMENT_CATEGORY_O => null
3752 , P_ASSIGNMENT_NUMBER_O => null
3753 ,P_ASSIGNMENT_SEQUENCE_O => null
3754 ,P_ASSIGNMENT_STATUS_TYPE_ID_O => null
3755 ,P_ASSIGNMENT_TYPE_O => null
3756 ,P_ASS_ATTRIBUTE1_O => null
3757 ,P_ASS_ATTRIBUTE10_O => null
3758 ,P_ASS_ATTRIBUTE11_O => null
3759 ,P_ASS_ATTRIBUTE12_O => null
3760 ,P_ASS_ATTRIBUTE13_O => null
3761 ,P_ASS_ATTRIBUTE14_O => null
3762 ,P_ASS_ATTRIBUTE15_O => null
3763 ,P_ASS_ATTRIBUTE16_O => null
3764 ,P_ASS_ATTRIBUTE17_O => null
3765 ,P_ASS_ATTRIBUTE18_O => null
3766 ,P_ASS_ATTRIBUTE19_O => null
3767 ,P_ASS_ATTRIBUTE2_O => null
3768 ,P_ASS_ATTRIBUTE20_O => null
3769 ,P_ASS_ATTRIBUTE21_O => null
3770 ,P_ASS_ATTRIBUTE22_O => null
3771 ,P_ASS_ATTRIBUTE23_O => null
3772 ,P_ASS_ATTRIBUTE24_O => null
3773 ,P_ASS_ATTRIBUTE25_O => null
3774 ,P_ASS_ATTRIBUTE26_O => null
3775 ,P_ASS_ATTRIBUTE27_O => null
3776 ,P_ASS_ATTRIBUTE28_O => null
3777 ,P_ASS_ATTRIBUTE29_O => null
3778 ,P_ASS_ATTRIBUTE3_O => null
3779 ,P_ASS_ATTRIBUTE30_O =>null
3780 ,P_ASS_ATTRIBUTE4_O =>null
3781 ,P_ASS_ATTRIBUTE5_O => null
3782 ,P_ASS_ATTRIBUTE6_O => null
3783 ,P_ASS_ATTRIBUTE7_O => null
3784 ,P_ASS_ATTRIBUTE8_O => null
3785 ,P_ASS_ATTRIBUTE9_O => null
3786 ,P_ASS_ATTRIBUTE_CATEGORY_O => null
3787 ,P_BARGAINING_UNIT_CODE_O => null
3788 ,P_BUSINESS_GROUP_ID_O => c_ass_rec.business_group_id
3789 ,P_CAGR_GRADE_DEF_ID_O => null
3790 ,P_CAGR_ID_FLEX_NUM_O => null
3791 ,P_CHANGE_REASON_O => null
3792 ,P_COLLECTIVE_AGREEMENT_ID_O => null
3793 ,P_COMMENT_ID_O => null
3794 ,P_CONTRACT_ID_O => null
3795 ,P_DATE_PROBATION_END_O => null
3796 ,P_DEFAULT_CODE_COMB_ID_O => null
3797 ,P_EFFECTIVE_END_DATE_O => l_effective_end_date1
3798 ,P_EFFECTIVE_START_DATE_O => l_effective_start_date1
3799 ,P_EMPLOYEE_CATEGORY_O => null
3800 ,P_EMPLOYMENT_CATEGORY_O => null
3801 ,P_ESTABLISHMENT_ID_O => null
3802 ,P_FREQUENCY_O => null
3803 ,P_GRADE_ID_O => null
3804 ,P_HOURLY_SALARIED_CODE_O => null
3805 ,P_INTERNAL_ADDRESS_LINE_O => null
3806 ,P_JOB_ID_O => null
3807 ,P_JOB_POST_SOURCE_NAME_O => null
3808 ,P_LABOUR_UNION_MEMBER_FLAG_O => null
3809 ,P_LOCATION_ID_O => null
3810 ,P_MANAGER_FLAG_O => null
3811 ,P_NORMAL_HOURS_O => null
3812 ,P_NOTICE_PERIOD_O => null
3813 ,P_NOTICE_PERIOD_UOM_O => null
3814 ,P_OBJECT_VERSION_NUMBER_O => null
3815 ,P_ORGANIZATION_ID_O => null
3816 ,P_PAYROLL_ID_O => c_ass_rec.payroll_id
3817 ,P_PAY_BASIS_ID_O => null
3818 ,P_PEOPLE_GROUP_ID_O => null
3819 ,P_PERF_REVIEW_PERIOD_O => null
3820 ,P_PERF_REVIEW_PERIOD_FREQUEN_O => null
3821 ,P_PERIOD_OF_SERVICE_ID_O => null
3822 ,P_PERSON_ID_O => null
3823 ,P_PERSON_REFERRED_BY_ID_O => null
3824 ,P_PLACEMENT_DATE_START_O => null
3825 ,P_POSITION_ID_O => null
3826 ,P_POSTING_CONTENT_ID_O => null
3827 ,P_PRIMARY_FLAG_O => null
3828 ,P_PROBATION_PERIOD_O => null
3829 ,P_PROBATION_UNIT_O => null
3830 ,P_PROGRAM_APPLICATION_ID_O => null
3831 ,P_PROGRAM_ID_O => null
3832 ,P_PROGRAM_UPDATE_DATE_O => null
3833 ,P_PROJECT_TITLE_O => null
3834 ,P_RECRUITER_ID_O => null
3835 ,P_RECRUITMENT_ACTIVITY_ID_O => null
3836 ,P_REQUEST_ID_O => null
3837 ,P_SAL_REVIEW_PERIOD_O => null
3838 ,P_SAL_REVIEW_PERIOD_FREQUEN_O => null
3839 ,P_SET_OF_BOOKS_ID_O => null
3840 ,P_SOFT_CODING_KEYFLEX_ID_O => null
3841 ,P_SOURCE_ORGANIZATION_ID_O => null
3842 ,P_SOURCE_TYPE_O => null
3843 ,P_SPECIAL_CEILING_STEP_ID_O => null
3844 ,P_SUPERVISOR_ID_O => null
3845 ,P_TIME_NORMAL_FINISH_O => null
3846 ,P_TIME_NORMAL_START_O => null
3847 ,P_TITLE_O => null
3848 ,P_VACANCY_ID_O => null
3849 ,P_VENDOR_ASSIGNMENT_NUMBER_O => null
3850 ,P_VENDOR_EMPLOYEE_NUMBER_O => null
3851 ,P_VENDOR_ID_O => null
3852 ,P_WORK_AT_HOME_O => null
3853 ,P_GRADE_LADDER_PGM_ID_O => null
3854 ,P_SUPERVISOR_ASSIGNMENT_ID_O => null
3855 ,P_VENDOR_SITE_ID_O => null
3856 ,P_PO_HEADER_ID_O => null
3857 ,P_PO_LINE_ID_O => null
3858 ,P_PROJECTED_ASSIGNMENT_END_O => null
3859 );
3860
3861 end if;
3862 --bug 5152164 end of fix
3863 --
3864 -- end of bug 4919804
3865 --
3866 -- We want to keep most of the assignment records after the ATD so
3867 -- update them all to the same assignment_status as the record
3868 -- which ends on ATD.
3869 --
3870 -- The fix for bug 1271513 has been modified slightly. We update
3871 -- all assignments which start after ATD so that we preserve future
3872 -- assignment changes. Since the terminate code created the assignment
3873 -- change starting on ATD+1 we will try and delete it. It will get
3874 -- deleted provided it is identical to the assignment which ends on
3875 -- ATD (the records will be considered identical if all columns between
3876 -- the two records are the same with the exception of effective dates,
3877 -- AOL WHO and OVN columns.
3878 --
3879 -- First update all future assignments to set the assignment status...
3880 --
3881 UPDATE per_assignments_f ass
3882 SET ass.assignment_status_type_id = l_asg_status_type_id
3883 WHERE assignment_id = c_ass_rec.assignment_id
3884 AND effective_start_date >= p_actual_termination_date;
3885 --
3886 -- ... if the ATD and FPD are different compare the record ending on ATD
3887 -- and one starting on ATD+1 if they are identical delete the one starting
3888 -- on ATD+1 and open the other to the end date of the deleted record.
3889 --
3890 if (compareAssignments(p_assignment_id1 => c_ass_rec.assignment_id,
3891 p_effective_date1 => p_actual_termination_date,
3892 p_assignment_id2 => c_ass_rec.assignment_id,
3893 p_effective_date2 => p_actual_termination_date+1)
3894 and p_actual_termination_date <> nvl(l_final_process_date,
3895 hr_general.end_of_time))
3896 then
3897 hr_utility.set_location(l_proc,27);
3898 --
3899 -- The assignments are identical....
3900 --
3901 -- ...get the end date of the assignment created by the termination
3902 -- and which starts the day after the ATD...
3903 --
3904 select effective_end_date
3905 into l_new_effective_end_date
3906 from per_all_assignments_f
3907 where assignment_id = c_ass_rec.assignment_id
3908 and effective_start_date = p_actual_termination_date+1;
3909 --
3910 -- ...now update the assignment ending on ATD to have the selected
3911 -- end date...
3912 --
3913 update per_all_assignments_f
3914 set effective_end_date = l_new_effective_end_date
3915 where current of c_assignment;
3916 --
3917 -- ...now delete the assignment which starts on ATD+1...
3918 --
3919 delete from per_all_assignments_f
3920 where assignment_id = c_ass_rec.assignment_id
3921 and effective_start_date = p_actual_termination_date +1;
3922 end if;
3923 -- end if;
3924 --
3925 if FPD_FLAG then
3926 --
3927 -- added the following call as per the bug 4919804
3928 pay_pog_all_assignments_pkg.after_delete
3929 (p_effective_date => l_session_date
3930 ,p_datetrack_mode => 'DELETE_NEXT_CHANGE'
3931 ,p_validation_start_date => c_ass_rec.effective_START_DATE
3932 ,p_validation_end_date => c_ass_rec.effective_end_date
3933 ,P_ASSIGNMENT_ID => c_ass_rec.assignment_id
3934 ,P_EFFECTIVE_END_DATE => l_effective_end_of_time
3935 ,P_EFFECTIVE_START_DATE => c_ass_rec.effective_START_DATE
3936 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
3937 ,P_ORG_NOW_NO_MANAGER_WARNING => null
3938 ,P_APPLICANT_RANK_O => null
3939 ,P_APPLICATION_ID_O => null
3940 ,P_ASSIGNMENT_CATEGORY_O => null
3941 , P_ASSIGNMENT_NUMBER_O => null
3942 ,P_ASSIGNMENT_SEQUENCE_O => null
3943 ,P_ASSIGNMENT_STATUS_TYPE_ID_O => null
3944 ,P_ASSIGNMENT_TYPE_O => null
3945 ,P_ASS_ATTRIBUTE1_O => null
3946 ,P_ASS_ATTRIBUTE10_O => null
3947 ,P_ASS_ATTRIBUTE11_O => null
3948 ,P_ASS_ATTRIBUTE12_O => null
3949 ,P_ASS_ATTRIBUTE13_O => null
3950 ,P_ASS_ATTRIBUTE14_O => null
3951 ,P_ASS_ATTRIBUTE15_O => null
3952 ,P_ASS_ATTRIBUTE16_O => null
3953 ,P_ASS_ATTRIBUTE17_O => null
3954 ,P_ASS_ATTRIBUTE18_O => null
3955 ,P_ASS_ATTRIBUTE19_O => null
3956 ,P_ASS_ATTRIBUTE2_O => null
3957 ,P_ASS_ATTRIBUTE20_O => null
3958 ,P_ASS_ATTRIBUTE21_O => null
3959 ,P_ASS_ATTRIBUTE22_O => null
3960 ,P_ASS_ATTRIBUTE23_O => null
3961 ,P_ASS_ATTRIBUTE24_O => null
3962 ,P_ASS_ATTRIBUTE25_O => null
3963 ,P_ASS_ATTRIBUTE26_O => null
3964 ,P_ASS_ATTRIBUTE27_O => null
3965 ,P_ASS_ATTRIBUTE28_O => null
3966 ,P_ASS_ATTRIBUTE29_O => null
3967 ,P_ASS_ATTRIBUTE3_O => null
3968 ,P_ASS_ATTRIBUTE30_O =>null
3969 ,P_ASS_ATTRIBUTE4_O =>null
3970 ,P_ASS_ATTRIBUTE5_O => null
3971 ,P_ASS_ATTRIBUTE6_O => null
3972 ,P_ASS_ATTRIBUTE7_O => null
3973 ,P_ASS_ATTRIBUTE8_O => null
3974 ,P_ASS_ATTRIBUTE9_O => null
3975 ,P_ASS_ATTRIBUTE_CATEGORY_O => null
3976 ,P_BARGAINING_UNIT_CODE_O => null
3977 ,P_BUSINESS_GROUP_ID_O => c_ass_rec.business_group_id
3978 ,P_CAGR_GRADE_DEF_ID_O => null
3979 ,P_CAGR_ID_FLEX_NUM_O => null
3980 ,P_CHANGE_REASON_O => null
3981 ,P_COLLECTIVE_AGREEMENT_ID_O => null
3982 ,P_COMMENT_ID_O => null
3983 ,P_CONTRACT_ID_O => null
3984 ,P_DATE_PROBATION_END_O => null
3985 ,P_DEFAULT_CODE_COMB_ID_O => null
3986 ,P_EFFECTIVE_END_DATE_O => l_final_process_date
3987 ,P_EFFECTIVE_START_DATE_O => l_effective_start_date1
3988 ,P_EMPLOYEE_CATEGORY_O => null
3989 ,P_EMPLOYMENT_CATEGORY_O => null
3990 ,P_ESTABLISHMENT_ID_O => null
3991 ,P_FREQUENCY_O => null
3992 ,P_GRADE_ID_O => null
3993 ,P_HOURLY_SALARIED_CODE_O => null
3994 ,P_INTERNAL_ADDRESS_LINE_O => null
3995 ,P_JOB_ID_O => null
3996 ,P_JOB_POST_SOURCE_NAME_O => null
3997 ,P_LABOUR_UNION_MEMBER_FLAG_O => null
3998 ,P_LOCATION_ID_O => null
3999 ,P_MANAGER_FLAG_O => null
4000 ,P_NORMAL_HOURS_O => null
4001 ,P_NOTICE_PERIOD_O => null
4002 ,P_NOTICE_PERIOD_UOM_O => null
4003 ,P_OBJECT_VERSION_NUMBER_O => null
4004 ,P_ORGANIZATION_ID_O => null
4005 ,P_PAYROLL_ID_O => c_ass_rec.payroll_id
4006 ,P_PAY_BASIS_ID_O => null
4007 ,P_PEOPLE_GROUP_ID_O => null
4008 ,P_PERF_REVIEW_PERIOD_O => null
4009 ,P_PERF_REVIEW_PERIOD_FREQUEN_O => null
4010 ,P_PERIOD_OF_SERVICE_ID_O => null
4011 ,P_PERSON_ID_O => null
4012 ,P_PERSON_REFERRED_BY_ID_O => null
4013 ,P_PLACEMENT_DATE_START_O => null
4014 ,P_POSITION_ID_O => null
4015 ,P_POSTING_CONTENT_ID_O => null
4016 ,P_PRIMARY_FLAG_O => null
4017 ,P_PROBATION_PERIOD_O => null
4018 ,P_PROBATION_UNIT_O => null
4019 ,P_PROGRAM_APPLICATION_ID_O => null
4020 ,P_PROGRAM_ID_O => null
4021 ,P_PROGRAM_UPDATE_DATE_O => null
4022 ,P_PROJECT_TITLE_O => null
4023 ,P_RECRUITER_ID_O => null
4024 ,P_RECRUITMENT_ACTIVITY_ID_O => null
4025 ,P_REQUEST_ID_O => null
4026 ,P_SAL_REVIEW_PERIOD_O => null
4027 ,P_SAL_REVIEW_PERIOD_FREQUEN_O => null
4028 ,P_SET_OF_BOOKS_ID_O => null
4029 ,P_SOFT_CODING_KEYFLEX_ID_O => null
4030 ,P_SOURCE_ORGANIZATION_ID_O => null
4031 ,P_SOURCE_TYPE_O => null
4032 ,P_SPECIAL_CEILING_STEP_ID_O => null
4033 ,P_SUPERVISOR_ID_O => null
4034 ,P_TIME_NORMAL_FINISH_O => null
4035 ,P_TIME_NORMAL_START_O => null
4036 ,P_TITLE_O => null
4037 ,P_VACANCY_ID_O => null
4038 ,P_VENDOR_ASSIGNMENT_NUMBER_O => null
4039 ,P_VENDOR_EMPLOYEE_NUMBER_O => null
4040 ,P_VENDOR_ID_O => null
4041 ,P_WORK_AT_HOME_O => null
4042 ,P_GRADE_LADDER_PGM_ID_O => null
4043 ,P_SUPERVISOR_ASSIGNMENT_ID_O => null
4044 ,P_VENDOR_SITE_ID_O => null
4045 ,P_PO_HEADER_ID_O => null
4046 ,P_PO_LINE_ID_O => null
4047 ,P_PROJECTED_ASSIGNMENT_END_O => null
4048 );
4049 --
4050 -- end of bug 4919804
4051 --
4052 hr_utility.set_location(l_proc,30);
4053 if l_max_end_date <> l_final_process_date then
4054 null;
4055 else
4056 hr_utility.set_location(l_proc,32);
4057 l_effective_end_date := to_date('31/12/4712','DD/MM/YYYY');
4058 --
4059 hr_utility.set_location(l_proc,34);
4060 UPDATE per_secondary_ass_statuses sas
4061 SET sas.end_date = null
4062 WHERE sas.assignment_id = c_ass_rec.assignment_id
4063 AND sas.end_date = l_final_process_date;
4064 --
4065 hr_utility.set_location(l_proc,36);
4066 UPDATE pay_personal_payment_methods_f ppm
4067 SET ppm.effective_end_date = l_effective_end_date
4068 WHERE ppm.assignment_id = c_ass_rec.assignment_id
4069 AND ppm.effective_end_date = l_final_process_date;
4070 --
4071 hr_utility.set_location(l_proc,38);
4072 UPDATE pay_cost_allocations_f pca
4073 SET pca.effective_end_date = l_effective_end_date
4074 WHERE pca.assignment_id = c_ass_rec.assignment_id
4075 AND pca.effective_end_date = l_final_process_date;
4076 --
4077 hr_utility.set_location(l_proc,40);
4078 UPDATE per_spinal_point_placements_f spp
4079 SET spp.effective_end_date = l_effective_end_date
4080 WHERE spp.assignment_id = c_ass_rec.assignment_id
4081 AND spp.effective_end_date = l_final_process_date;
4082 --
4083 --
4084 -- Adding code to update the date tracked tax tables to resolve bug
4085 -- 920233.
4086 -- Adding an extra verification to make sure the tax records are reverse
4087 -- only for US legislation. This extra verification is done because
4088 -- this package peempter.pkb is part of the CORE HR code and UK
4089 -- customer do not use these TAX tables. Only Customers with HR/CERIDIAN
4090 -- use this TAX tables.
4091
4092 if hr_general.chk_geocodes_installed ='Y' then
4093 hr_utility.set_location(l_proc,41);
4094 pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records
4095 (c_ass_rec.assignment_id
4096 ,l_final_process_date);
4097
4098 end if; /* verification chk_geocodes_installed */
4099 --
4100 -- SASmith 30-APR-1998
4101 -- Due to date tracking of assignment_budget_values
4102
4103 hr_utility.set_location(l_proc,45);
4104 --
4105 -- Start of Fix for WWBUG 1408379
4106 --
4107 open c2(c_ass_rec.assignment_id);
4108 --
4109 loop
4110 --
4111 fetch c2 into l_c2;
4112 exit when c2%notfound;
4113 --
4114 l_old_abv.assignment_id := l_c2.assignment_id;
4115 l_old_abv.business_group_id := l_c2.business_group_id;
4116 l_old_abv.value := l_c2.value;
4117 l_old_abv.assignment_budget_value_id := l_c2.assignment_budget_value_id;
4118 l_old_abv.effective_start_date := l_c2.effective_start_date;
4119 l_old_abv.effective_end_date := l_c2.effective_end_date;
4120 l_new_abv.assignment_id := l_c2.assignment_id;
4121 l_new_abv.business_group_id := l_c2.business_group_id;
4122 l_new_abv.value := l_c2.value;
4123 l_new_abv.assignment_budget_value_id := l_c2.assignment_budget_value_id;
4124 l_new_abv.effective_start_date := l_c2.effective_start_date;
4125 l_new_abv.effective_end_date := l_effective_end_date;
4126 --
4127 update per_assignment_budget_values_f abv
4128 set abv.effective_end_date = l_effective_end_date
4129 where abv.assignment_id = c_ass_rec.assignment_id
4130 and abv.assignment_budget_value_id = l_c2.assignment_budget_value_id
4131 and abv.effective_end_date = l_final_process_date;
4132 --
4133 ben_abv_ler.ler_chk(p_old => l_old_abv,
4134 p_new => l_new_abv,
4135 p_effective_date => l_c2.effective_start_date);
4136 --
4137 end loop;
4138 --
4139 close c2;
4140 --
4141 -- End of Fix for WWBUG 1408379
4142 --
4143 end if;
4144 --
4145 end if;
4146 --
4147 -- open up element entries closed down by the termination
4148 --
4149 hr_utility.set_location(l_proc,42);
4150 hrentmnt.maintain_entries_asg(c_ass_rec.assignment_id
4151 ,c_ass_rec.business_group_id
4152 ,'CNCL_TERM'
4153 ,p_actual_termination_date
4154 ,l_last_standard_process_date
4155 ,l_final_process_date
4156 ,'DELETE_NEXT_CHANGE'
4157 ,null
4158 ,null);
4159 --
4160 per_saladmin_utility.adjust_pay_proposals(p_assignment_id =>c_ass_rec.assignment_id);
4161 --
4162 END LOOP;
4163 --
4164 -- fix 1370960
4165 for roles_rec in csr_roles
4166 loop
4167 per_supplementary_role_api.update_supplementary_role(
4168 p_effective_date => p_actual_termination_date
4169 ,p_role_id => roles_rec.role_id
4170 ,p_object_version_number => roles_rec.object_version_number
4171 ,p_end_date => roles_rec.old_end_date
4172 ,p_old_end_date => null
4173 );
4174 end loop;
4175 -- 1370960 end
4176 --
4177 -- Fix for bug 3889294 starts here. Code added to perform legislation
4178 -- specific actions if exists.
4179 --
4180 open csr_leg_code(l_business_group_id);
4181 fetch csr_leg_code into l_leg_code;
4182 --
4183 if csr_leg_code%found then
4184 --
4185 -- If one exists then we must check whether there exists a legislation
4186 -- specific Validate_Delete procedure. This should be named in the format
4187 -- PER_XX_TERMINATION.REVERSE
4188 -- If it does exist then construct an anonymous PL/SQL block to call
4189 -- the procedure.
4190 --
4191 hr_utility.set_location(l_proc,43);
4192 --
4193 l_package_name := 'PER_'||l_leg_code||'_TERMINATION';
4194 l_procedure_name := 'REVERSE';
4195 --
4196 -- Close Cursor added a part of fix for bug 1858597
4197 --
4198 close csr_leg_code;
4199 --
4200 -- Check package exists
4201 --
4202 open csr_leg_pkg(l_package_name);
4203 fetch csr_leg_pkg into l_dummy;
4204 --
4205 if csr_leg_pkg%found then
4206 --
4207 hr_utility.set_location(l_proc,44);
4208 --
4209 close csr_leg_pkg;
4210 --
4211 -- Added as part of fix for bug 1858597
4212 --
4213 EXECUTE IMMEDIATE 'BEGIN '||l_package_name||'.'||l_procedure_name||
4214 '(:P_PERIOD_OF_SERVICE_ID,'||':P_ACTUAL_TERMINATION_DATE,'||
4215 ':P_LEAVING_REASON); END;'
4216 USING l_period_of_service_id
4217 ,p_actual_termination_date
4218 ,l_old_leaving_reason;
4219 --
4220 end if;
4221 --
4222 end if;
4223 --
4224 -- Update the PTU records.
4225 --
4226 hr_utility.set_location(l_proc,65);
4227 --
4228 if l_old_leaving_reason = 'R' then
4229 --
4230 hr_utility.set_location(l_proc,66);
4231 --
4232 hr_per_type_usage_internal.cancel_person_type_usage
4233 (p_effective_date => p_actual_termination_date+1
4234 ,p_person_id => p_person_id
4235 ,p_system_person_type => 'RETIREE');
4236 -- end if; fix Bug 2048953
4237 --
4238 -- Added for Bug 5507290. Need to cancel EX_EMP PTU record
4239 -- aswell along with RETIREE
4240 --
4241 hr_utility.set_location(l_proc,67);
4242 --
4243 hr_per_type_usage_internal.cancel_person_type_usage
4244 (p_effective_date => p_actual_termination_date+1
4245 ,p_person_id => p_person_id
4246 ,p_system_person_type => 'EX_EMP');
4247 --
4248 -- End of Bug 5507290.
4249 --
4250 else
4251 hr_utility.set_location('REV_TERM '||l_proc,68);
4252 hr_per_type_usage_internal.cancel_person_type_usage
4253 (p_effective_date => p_actual_termination_date+1
4254 ,p_person_id => p_person_id
4255 ,p_system_person_type => 'EX_EMP');
4256 end if;
4257 --
4258 -- call Hr workflow sync code.
4259 --
4260 hr_utility.set_location(l_proc,70);
4261 --
4262 per_hrwf_synch_cover.per_pds_wf(
4263 p_person_id => p_person_id,
4264 p_date => null,
4265 p_date_start => l_date_start,
4266 p_action => 'REVERSE TERMINATION');
4267 --
4268 -- Fix for bug 3889294 ends here.
4269 --
4270 end cancel_termination;
4271 --
4272 --
4273 end hrempter;