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