[Home] [Help]
PACKAGE BODY: APPS.HR_PERIODS_OF_SERVICE_API
Source
1 Package Body hr_periods_of_service_api as
2 /* $Header: pepdsapi.pkb 120.9.12000000.2 2007/04/19 06:06:25 pdkundu ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_periods_of_service_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------< update_pds_details (overloaded) >-----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure update_pds_details
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_period_of_service_id in number
16 ,p_termination_accepted_person in number default hr_api.g_number
17 ,p_accepted_termination_date in date default hr_api.g_date
18 ,p_object_version_number in out nocopy number
19 ,p_comments in varchar2 default hr_api.g_varchar2
20 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
21 ,p_notified_termination_date in date default hr_api.g_date
22 ,p_projected_termination_date in date default hr_api.g_date
23 ,p_actual_termination_date in date default hr_api.g_date
24 ,p_last_standard_process_date in date default hr_api.g_date
25 ,p_final_process_date in date default hr_api.g_date
26 ,p_attribute_category in varchar2 default hr_api.g_varchar2
27 ,p_attribute1 in varchar2 default hr_api.g_varchar2
28 ,p_attribute2 in varchar2 default hr_api.g_varchar2
29 ,p_attribute3 in varchar2 default hr_api.g_varchar2
30 ,p_attribute4 in varchar2 default hr_api.g_varchar2
31 ,p_attribute5 in varchar2 default hr_api.g_varchar2
32 ,p_attribute6 in varchar2 default hr_api.g_varchar2
33 ,p_attribute7 in varchar2 default hr_api.g_varchar2
34 ,p_attribute8 in varchar2 default hr_api.g_varchar2
35 ,p_attribute9 in varchar2 default hr_api.g_varchar2
36 ,p_attribute10 in varchar2 default hr_api.g_varchar2
37 ,p_attribute11 in varchar2 default hr_api.g_varchar2
38 ,p_attribute12 in varchar2 default hr_api.g_varchar2
39 ,p_attribute13 in varchar2 default hr_api.g_varchar2
40 ,p_attribute14 in varchar2 default hr_api.g_varchar2
41 ,p_attribute15 in varchar2 default hr_api.g_varchar2
42 ,p_attribute16 in varchar2 default hr_api.g_varchar2
43 ,p_attribute17 in varchar2 default hr_api.g_varchar2
44 ,p_attribute18 in varchar2 default hr_api.g_varchar2
45 ,p_attribute19 in varchar2 default hr_api.g_varchar2
46 ,p_attribute20 in varchar2 default hr_api.g_varchar2
47 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
48 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
49 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
50 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
51 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
52 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
53 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
54 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
55 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
56 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
57 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
58 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
59 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
60 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
61 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
62 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
63 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
64 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
65 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
66 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
67 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
68 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
69 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
70 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
71 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
72 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
73 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
74 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
75 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
76 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
77 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
78 ) is
79 --
80 l_org_now_no_manager_warning BOOLEAN := FALSE;
81 l_asg_future_changes_warning BOOLEAN := FALSE;
82 l_entries_changed_warning VARCHAR2(200) := '';
83 --
84 begin
85 --
86 update_pds_details
87 (p_validate => p_validate
88 ,p_effective_date => p_effective_date
89 ,p_period_of_service_id => p_period_of_service_id
90 ,p_termination_accepted_person => p_termination_accepted_person
91 ,p_accepted_termination_date => p_accepted_termination_date
92 ,p_object_version_number => p_object_version_number
93 ,p_comments => p_comments
94 ,p_leaving_reason => p_leaving_reason
95 ,p_notified_termination_date => p_notified_termination_date
96 ,p_projected_termination_date => p_projected_termination_date
97 ,p_actual_termination_date => p_actual_termination_date
98 ,p_last_standard_process_date => p_last_standard_process_date
99 ,p_final_process_date => p_final_process_date
100 ,p_attribute_category => p_attribute_category
101 ,p_attribute1 => p_attribute1
102 ,p_attribute2 => p_attribute2
103 ,p_attribute3 => p_attribute3
104 ,p_attribute4 => p_attribute4
105 ,p_attribute5 => p_attribute5
106 ,p_attribute6 => p_attribute6
107 ,p_attribute7 => p_attribute7
108 ,p_attribute8 => p_attribute8
109 ,p_attribute9 => p_attribute9
110 ,p_attribute10 => p_attribute10
111 ,p_attribute11 => p_attribute11
112 ,p_attribute12 => p_attribute12
113 ,p_attribute13 => p_attribute13
114 ,p_attribute14 => p_attribute14
115 ,p_attribute15 => p_attribute15
116 ,p_attribute16 => p_attribute16
117 ,p_attribute17 => p_attribute17
118 ,p_attribute18 => p_attribute18
119 ,p_attribute19 => p_attribute19
120 ,p_attribute20 => p_attribute20
121 ,p_pds_information_category => p_pds_information_category
122 ,p_pds_information1 => p_pds_information1
123 ,p_pds_information2 => p_pds_information2
124 ,p_pds_information3 => p_pds_information3
125 ,p_pds_information4 => p_pds_information4
126 ,p_pds_information5 => p_pds_information5
127 ,p_pds_information6 => p_pds_information6
128 ,p_pds_information7 => p_pds_information7
129 ,p_pds_information8 => p_pds_information8
130 ,p_pds_information9 => p_pds_information9
131 ,p_pds_information10 => p_pds_information10
132 ,p_pds_information11 => p_pds_information11
133 ,p_pds_information12 => p_pds_information12
134 ,p_pds_information13 => p_pds_information13
135 ,p_pds_information14 => p_pds_information14
136 ,p_pds_information15 => p_pds_information15
137 ,p_pds_information16 => p_pds_information16
138 ,p_pds_information17 => p_pds_information17
139 ,p_pds_information18 => p_pds_information18
140 ,p_pds_information19 => p_pds_information19
141 ,p_pds_information20 => p_pds_information20
142 ,p_pds_information21 => p_pds_information21
143 ,p_pds_information22 => p_pds_information22
144 ,p_pds_information23 => p_pds_information23
145 ,p_pds_information24 => p_pds_information24
146 ,p_pds_information25 => p_pds_information25
147 ,p_pds_information26 => p_pds_information26
148 ,p_pds_information27 => p_pds_information27
149 ,p_pds_information28 => p_pds_information28
150 ,p_pds_information29 => p_pds_information29
151 ,p_pds_information30 => p_pds_information30
152 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
153 ,p_asg_future_changes_warning => l_asg_future_changes_warning
154 ,p_entries_changed_warning => l_entries_changed_warning
155 );
156 --
157 end update_pds_details;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |-----------------------< update_pds_details >-----------------------------|
161 -- ----------------------------------------------------------------------------
162 --
163 procedure update_pds_details
164 (p_validate in boolean default false
165 ,p_effective_date in date
166 ,p_period_of_service_id in number
167 ,p_termination_accepted_person in number default hr_api.g_number
168 ,p_accepted_termination_date in date default hr_api.g_date
169 ,p_object_version_number in out nocopy number
170 ,p_comments in varchar2 default hr_api.g_varchar2
171 ,p_leaving_reason in varchar2 default hr_api.g_varchar2
172 ,p_notified_termination_date in date default hr_api.g_date
173 ,p_projected_termination_date in date default hr_api.g_date
174 --
175 -- 115.9 (START)
176 --
177 ,p_actual_termination_date in date default hr_api.g_date
178 ,p_last_standard_process_date in date default hr_api.g_date
179 ,p_final_process_date in date default hr_api.g_date
180 --
181 -- 115.9 (END)
182 --
183 ,p_attribute_category in varchar2 default hr_api.g_varchar2
184 ,p_attribute1 in varchar2 default hr_api.g_varchar2
185 ,p_attribute2 in varchar2 default hr_api.g_varchar2
186 ,p_attribute3 in varchar2 default hr_api.g_varchar2
187 ,p_attribute4 in varchar2 default hr_api.g_varchar2
188 ,p_attribute5 in varchar2 default hr_api.g_varchar2
189 ,p_attribute6 in varchar2 default hr_api.g_varchar2
190 ,p_attribute7 in varchar2 default hr_api.g_varchar2
191 ,p_attribute8 in varchar2 default hr_api.g_varchar2
192 ,p_attribute9 in varchar2 default hr_api.g_varchar2
193 ,p_attribute10 in varchar2 default hr_api.g_varchar2
194 ,p_attribute11 in varchar2 default hr_api.g_varchar2
195 ,p_attribute12 in varchar2 default hr_api.g_varchar2
196 ,p_attribute13 in varchar2 default hr_api.g_varchar2
197 ,p_attribute14 in varchar2 default hr_api.g_varchar2
198 ,p_attribute15 in varchar2 default hr_api.g_varchar2
199 ,p_attribute16 in varchar2 default hr_api.g_varchar2
200 ,p_attribute17 in varchar2 default hr_api.g_varchar2
201 ,p_attribute18 in varchar2 default hr_api.g_varchar2
202 ,p_attribute19 in varchar2 default hr_api.g_varchar2
203 ,p_attribute20 in varchar2 default hr_api.g_varchar2
204 ,p_pds_information_category in varchar2 default hr_api.g_varchar2
205 ,p_pds_information1 in varchar2 default hr_api.g_varchar2
206 ,p_pds_information2 in varchar2 default hr_api.g_varchar2
207 ,p_pds_information3 in varchar2 default hr_api.g_varchar2
208 ,p_pds_information4 in varchar2 default hr_api.g_varchar2
209 ,p_pds_information5 in varchar2 default hr_api.g_varchar2
210 ,p_pds_information6 in varchar2 default hr_api.g_varchar2
211 ,p_pds_information7 in varchar2 default hr_api.g_varchar2
212 ,p_pds_information8 in varchar2 default hr_api.g_varchar2
213 ,p_pds_information9 in varchar2 default hr_api.g_varchar2
214 ,p_pds_information10 in varchar2 default hr_api.g_varchar2
215 ,p_pds_information11 in varchar2 default hr_api.g_varchar2
216 ,p_pds_information12 in varchar2 default hr_api.g_varchar2
217 ,p_pds_information13 in varchar2 default hr_api.g_varchar2
218 ,p_pds_information14 in varchar2 default hr_api.g_varchar2
219 ,p_pds_information15 in varchar2 default hr_api.g_varchar2
220 ,p_pds_information16 in varchar2 default hr_api.g_varchar2
221 ,p_pds_information17 in varchar2 default hr_api.g_varchar2
222 ,p_pds_information18 in varchar2 default hr_api.g_varchar2
223 ,p_pds_information19 in varchar2 default hr_api.g_varchar2
224 ,p_pds_information20 in varchar2 default hr_api.g_varchar2
225 ,p_pds_information21 in varchar2 default hr_api.g_varchar2
226 ,p_pds_information22 in varchar2 default hr_api.g_varchar2
227 ,p_pds_information23 in varchar2 default hr_api.g_varchar2
228 ,p_pds_information24 in varchar2 default hr_api.g_varchar2
229 ,p_pds_information25 in varchar2 default hr_api.g_varchar2
230 ,p_pds_information26 in varchar2 default hr_api.g_varchar2
231 ,p_pds_information27 in varchar2 default hr_api.g_varchar2
232 ,p_pds_information28 in varchar2 default hr_api.g_varchar2
233 ,p_pds_information29 in varchar2 default hr_api.g_varchar2
234 ,p_pds_information30 in varchar2 default hr_api.g_varchar2
235 --
236 -- 115.9 (START)
237 --
238 ,p_org_now_no_manager_warning OUT NOCOPY BOOLEAN
239 ,p_asg_future_changes_warning OUT NOCOPY BOOLEAN
240 ,p_entries_changed_warning OUT NOCOPY VARCHAR2
241 --
242 -- 115.9 (END)
243 --
244 ) is
245 --
246 -- Declare cursors and local variables
247 --
248
249 l_proc varchar2(72) := g_package||'update_pds_details';
250 l_object_version_number number := p_object_version_number;
251 l_ovn number := p_object_version_number;
252 l_validate boolean := false;
253 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%TYPE;
254 l_ptu_object_version_number per_person_type_usages_f.object_version_number%TYPE;
255 l_effective_start_date DATE;
256 l_effective_end_date DATE;
257
258 --
259 -- 115.9 (START)
260 --
261 l_old_final_process_date per_periods_of_service.final_process_date%TYPE;
262 --
263 -- 115.9 (END)
264 --
265 -- Fix for Bug 5152193 Starts here
266
267 l_pds_rec per_periods_of_service%rowtype;
268 l_per_rec per_all_people_f%rowtype;
269 l_orig_hire_warning boolean;
270 l_dob_null_warning boolean;
271 l_name_combination_warning boolean;
272
273 cursor csr_get_pds_details is
274 select * from per_periods_of_service
275 where period_of_service_id = p_period_of_service_id;
276
277 cursor csr_get_per_rec(p_person_id number,p_date date) is
278 select * from per_all_people_f
279 where person_id = p_person_id
280 and effective_start_date >= p_date;
281
282
283 -- Fix for Bug 5152193 Ends here
284
285 begin
286 hr_utility.set_location('Entering:'|| l_proc, 5);
287 --
288 -- Issue a savepoint.
289 --
290 savepoint update_pds_details;
291 hr_utility.set_location(l_proc, 6);
292 --
293 -- Start of API User Hook for the before hook of final_process_emp.
294 --
295 begin
296 --
297 hr_periods_of_service_bk1.update_pds_details_b
298 (p_effective_date => p_effective_date
299 ,p_period_of_service_id => p_period_of_service_id
300 ,p_termination_accepted_person => p_termination_accepted_person
301 ,p_accepted_termination_date => p_accepted_termination_date
302 ,p_comments => p_comments
303 ,p_leaving_reason => p_leaving_reason
304 ,p_notified_termination_date => p_notified_termination_date
305 ,p_projected_termination_date => p_projected_termination_date
306 ,p_actual_termination_date => p_actual_termination_date
307 ,p_last_standard_process_date => p_last_standard_process_date
308 ,p_final_process_date => p_final_process_date
309 ,p_attribute_category => p_attribute_category
310 ,p_attribute1 => p_attribute1
311 ,p_attribute2 => p_attribute2
312 ,p_attribute3 => p_attribute3
313 ,p_attribute4 => p_attribute4
314 ,p_attribute5 => p_attribute5
315 ,p_attribute6 => p_attribute6
316 ,p_attribute7 => p_attribute7
317 ,p_attribute8 => p_attribute8
318 ,p_attribute9 => p_attribute9
319 ,p_attribute10 => p_attribute10
320 ,p_attribute11 => p_attribute11
321 ,p_attribute12 => p_attribute12
322 ,p_attribute13 => p_attribute13
323 ,p_attribute14 => p_attribute14
324 ,p_attribute15 => p_attribute15
325 ,p_attribute16 => p_attribute16
326 ,p_attribute17 => p_attribute17
327 ,p_attribute18 => p_attribute18
328 ,p_attribute19 => p_attribute19
329 ,p_attribute20 => p_attribute20
330 ,p_pds_information_category => p_pds_information_category
331 ,p_pds_information1 => p_pds_information1
332 ,p_pds_information2 => p_pds_information2
333 ,p_pds_information3 => p_pds_information3
334 ,p_pds_information4 => p_pds_information4
335 ,p_pds_information5 => p_pds_information5
336 ,p_pds_information6 => p_pds_information6
337 ,p_pds_information7 => p_pds_information7
338 ,p_pds_information8 => p_pds_information8
339 ,p_pds_information9 => p_pds_information9
340 ,p_pds_information10 => p_pds_information10
341 ,p_pds_information11 => p_pds_information11
342 ,p_pds_information12 => p_pds_information12
343 ,p_pds_information13 => p_pds_information13
344 ,p_pds_information14 => p_pds_information14
345 ,p_pds_information15 => p_pds_information15
346 ,p_pds_information16 => p_pds_information16
347 ,p_pds_information17 => p_pds_information17
348 ,p_pds_information18 => p_pds_information18
349 ,p_pds_information19 => p_pds_information19
350 ,p_pds_information20 => p_pds_information20
351 ,p_pds_information21 => p_pds_information21
352 ,p_pds_information22 => p_pds_information22
353 ,p_pds_information23 => p_pds_information23
354 ,p_pds_information24 => p_pds_information24
355 ,p_pds_information25 => p_pds_information25
356 ,p_pds_information26 => p_pds_information26
357 ,p_pds_information27 => p_pds_information27
358 ,p_pds_information28 => p_pds_information28
359 ,p_pds_information29 => p_pds_information29
360 ,p_pds_information30 => p_pds_information30
361 ,p_object_version_number => p_object_version_number
362 );
363 exception
364 when hr_api.cannot_find_prog_unit then
365 hr_api.cannot_find_prog_unit_error
366 (p_module_name => 'UPDATE_PDS_DETAILS',
367 p_hook_type => 'BP'
368 );
369 end;
370 --
371 -- Process Logic
372 --
373 per_pds_upd.upd
374 (p_period_of_service_id => p_period_of_service_id
375 ,p_termination_accepted_person => p_termination_accepted_person
376 ,p_accepted_termination_date => p_accepted_termination_date
377 ,p_comments => p_comments
378 ,p_leaving_reason => p_leaving_reason
379 ,p_notified_termination_date => p_notified_termination_date
380 ,p_projected_termination_date => p_projected_termination_date
381 --
382 -- 115.9 (START)
383 --
384 ,p_actual_termination_date => p_actual_termination_date
385 ,p_last_standard_process_date => p_last_standard_process_date
386 ,p_final_process_date => p_final_process_date
387 --
388 -- 115.9 (END)
389 --
390 ,p_object_version_number => p_object_version_number
391 ,p_effective_date => p_effective_date
392 ,p_validate => l_validate
393 ,p_attribute_category => p_attribute_category
394 ,p_attribute1 => p_attribute1
395 ,p_attribute2 => p_attribute2
396 ,p_attribute3 => p_attribute3
397 ,p_attribute4 => p_attribute4
398 ,p_attribute5 => p_attribute5
399 ,p_attribute6 => p_attribute6
400 ,p_attribute7 => p_attribute7
401 ,p_attribute8 => p_attribute8
402 ,p_attribute9 => p_attribute9
403 ,p_attribute10 => p_attribute10
404 ,p_attribute11 => p_attribute11
405 ,p_attribute12 => p_attribute12
406 ,p_attribute13 => p_attribute13
407 ,p_attribute14 => p_attribute14
408 ,p_attribute15 => p_attribute15
409 ,p_attribute16 => p_attribute16
410 ,p_attribute17 => p_attribute17
411 ,p_attribute18 => p_attribute18
412 ,p_attribute19 => p_attribute19
413 ,p_attribute20 => p_attribute20
414 ,p_pds_information_category => p_pds_information_category
415 ,p_pds_information1 => p_pds_information1
416 ,p_pds_information2 => p_pds_information2
417 ,p_pds_information3 => p_pds_information3
418 ,p_pds_information4 => p_pds_information4
419 ,p_pds_information5 => p_pds_information5
420 ,p_pds_information6 => p_pds_information6
421 ,p_pds_information7 => p_pds_information7
422 ,p_pds_information8 => p_pds_information8
423 ,p_pds_information9 => p_pds_information9
424 ,p_pds_information10 => p_pds_information10
425 ,p_pds_information11 => p_pds_information11
426 ,p_pds_information12 => p_pds_information12
427 ,p_pds_information13 => p_pds_information13
428 ,p_pds_information14 => p_pds_information14
429 ,p_pds_information15 => p_pds_information15
430 ,p_pds_information16 => p_pds_information16
431 ,p_pds_information17 => p_pds_information17
432 ,p_pds_information18 => p_pds_information18
433 ,p_pds_information19 => p_pds_information19
434 ,p_pds_information20 => p_pds_information20
435 ,p_pds_information21 => p_pds_information21
436 ,p_pds_information22 => p_pds_information22
437 ,p_pds_information23 => p_pds_information23
438 ,p_pds_information24 => p_pds_information24
439 ,p_pds_information25 => p_pds_information25
440 ,p_pds_information26 => p_pds_information26
441 ,p_pds_information27 => p_pds_information27
442 ,p_pds_information28 => p_pds_information28
443 ,p_pds_information29 => p_pds_information29
444 ,p_pds_information30 => p_pds_information30
445 );
446
447 --
448 -- 115.9 (START)
449 --
450 --
451 -- Retain old value of FPD
452 --
453 l_old_final_process_date := per_pds_shd.g_old_rec.final_process_date;
454 --
455 -- Handle maintenance of records linked to PDS
456 --
457 move_term_assignments(p_period_of_service_id => p_period_of_service_id
458 ,p_old_final_process_date => l_old_final_process_date
459 ,p_new_final_process_date => p_final_process_date
460 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
461 ,p_asg_future_changes_warning => p_asg_future_changes_warning
462 ,p_entries_changed_warning => p_entries_changed_warning
463 );
464 --
465 -- 115.9 (END)
466 --
467
468 if per_pds_shd.g_old_rec.actual_termination_date is not null
469 then
470 --
471 -- Fix for bug 3882918. check with nvl for the leaving_reason.
472 --
473 hr_utility.set_location('Update_pds_details ',800);
474 --
475 if nvl(per_pds_shd.g_old_rec.leaving_reason, hr_api.g_varchar2)
476 <> nvl(p_leaving_reason,hr_api.g_varchar2)
477 then
478 --
479 -- Fix for bug 3882918. If this proc is called without leaving_reason parameter
480 -- then no need to check further as it means, user doesn't want to change this value.
481 --
482 hr_utility.set_location('Update_pds_details ',810);
483
484 --
485 -- Bug number 4900409 - validating actual termination date cannot be future date
486 -- if leaving reason is 'D' Deceased
487
488 IF nvl(p_leaving_reason,hr_api.g_varchar2)='D' AND
489 (per_pds_shd.g_old_rec.actual_termination_date > SYSDATE) THEN
490
491 hr_utility.set_location('Update_pds_details ',812);
492 fnd_message.set_name('PER','PER_449766_NO_FUT_ACTUAL_TERM');
493 fnd_message.raise_error;
494
495 END IF;
496
497 hr_utility.set_location('Update_pds_details ',818);
498
499 -- End of changes for 4900409
500
501 IF not (p_leaving_reason = hr_api.g_varchar2 and p_leaving_reason is not null) THEN
502 --
503 hr_utility.set_location('Update_pds_details ',820);
504 --
505 if (nvl(p_leaving_reason, hr_api.g_varchar2) = 'R' and
506 nvl(per_pds_shd.g_old_rec.leaving_reason, hr_api.g_varchar2) <> 'R' )
507 then
508
509 hr_utility.set_location('Update_pds_details ',900);
510
511 hr_per_type_usage_internal.create_person_type_usage
512 (p_person_id => per_pds_shd.g_old_rec.person_id
513 ,p_person_type_id =>
514 hr_person_type_usage_info.get_default_person_type_id
515 (p_business_group_id => per_pds_shd.g_old_rec.business_group_id
516 ,p_system_person_type => 'RETIREE')
517 ,p_effective_date => per_pds_shd.g_old_rec.actual_termination_date+1
518 ,p_person_type_usage_id => l_person_type_usage_id
519 ,p_object_version_number=> l_ptu_object_version_number
520 ,p_effective_start_date => l_effective_start_date
521 ,p_effective_end_date => l_effective_end_date);
522
523 elsif (nvl(p_leaving_reason, hr_api.g_varchar2) <> 'R' and
524 nvl(per_pds_shd.g_old_rec.leaving_reason,hr_api.g_varchar2) = 'R' )
525 then
526
527 hr_utility.set_location('Update_pds_details ',910);
528
529 hr_per_type_usage_internal.maintain_person_type_usage
530 (p_effective_date => per_pds_shd.g_old_rec.actual_termination_date+1
531 ,p_person_id => per_pds_shd.g_old_rec.person_id
532 ,p_person_type_id =>
533 hr_person_type_usage_info.get_default_person_type_id
534 (p_business_group_id => per_pds_shd.g_old_rec.business_group_id
535 ,p_system_person_type => 'RETIREE')
536 ,p_datetrack_delete_mode => 'ZAP');
537
538 -- Fix for Bug 5152193 Starts here
539
540 elsif (nvl(p_leaving_reason, hr_api.g_varchar2) = 'D') then
541 open csr_get_pds_details;
542 fetch csr_get_pds_details into l_pds_rec;
543 close csr_get_pds_details;
544
545 open csr_get_per_rec(l_pds_rec.person_id,l_pds_rec.actual_termination_date);
546 loop
547 fetch csr_get_per_rec into l_per_rec;
548 exit when csr_get_per_rec%NOTFOUND ;
549
550 -- Invoke per_per_upd.upd only to update Date of Death in per_all_people_f ONLY
551 -- when leaving reason has been set to Deceased and Date of Death has NOT been
552 -- previously set.
553
554 -- per_per_upd.upd updates the Date of Death to Actual Termination Date(ATD)
555
556 if (l_per_rec.date_of_death is null) then
557 hr_utility.set_location('Update PER Details ',920);
558 per_per_upd.upd
559 (p_person_id => l_pds_rec.person_id
560 ,p_effective_start_date => l_per_rec.effective_start_date
561 ,p_effective_end_date => l_per_rec.effective_end_date
562 ,p_person_type_id => l_per_rec.person_type_id
563 ,p_comment_id => l_per_rec.comment_id
564 ,p_current_applicant_flag => l_per_rec.current_applicant_flag
565 ,p_current_emp_or_apl_flag => l_per_rec.current_emp_or_apl_flag
566 ,p_current_employee_flag => l_per_rec.current_employee_flag
567 ,p_employee_number => l_per_rec.employee_number
568 ,p_applicant_number => l_per_rec.applicant_number
569 ,p_full_name => l_per_rec.full_name
570 ,p_object_version_number => l_per_rec.object_version_number
571 ,p_effective_date => l_pds_rec.actual_termination_date + 1
572 ,p_datetrack_mode => 'CORRECTION'
573 ,p_date_of_death => l_pds_rec.actual_termination_date
574 ,p_validate => p_validate
575 ,p_name_combination_warning => l_name_combination_warning
576 ,p_dob_null_warning => l_dob_null_warning
577 ,p_orig_hire_warning => l_orig_hire_warning
578 ,p_npw_number => l_per_rec.npw_number
579 );
580 end if;
581 end loop;
582 close csr_get_per_rec;
583
584 -- Fix for Bug 5152193 Ends here
585 end if;
586 --
587 END IF;
588
589 end if;
590
591 end if;
592
593 --
594 hr_utility.set_location(l_proc, 7);
595 --
596 -- When in validation only mode raise the Validate_Enabled exception
597 --
598 --
599 -- Start of API User Hook for the after hook of final_process_emp.
600 --
601 begin
602 hr_periods_of_service_bk1.update_pds_details_a
603 (p_effective_date => p_effective_date
604 ,p_period_of_service_id => p_period_of_service_id
605 ,p_object_version_number => p_object_version_number
606 ,p_termination_accepted_person => p_termination_accepted_person
607 ,p_accepted_termination_date => p_accepted_termination_date
608 ,p_comments => p_comments
609 ,p_leaving_reason => p_leaving_reason
610 ,p_notified_termination_date => p_notified_termination_date
611 ,p_projected_termination_date => p_projected_termination_date
612 ,p_actual_termination_date => p_actual_termination_date
613 ,p_last_standard_process_date => p_last_standard_process_date
614 ,p_final_process_date => p_final_process_date
615 ,p_attribute_category => p_attribute_category
616 ,p_attribute1 => p_attribute1
617 ,p_attribute2 => p_attribute2
618 ,p_attribute3 => p_attribute3
619 ,p_attribute4 => p_attribute4
620 ,p_attribute5 => p_attribute5
621 ,p_attribute6 => p_attribute6
622 ,p_attribute7 => p_attribute7
623 ,p_attribute8 => p_attribute8
624 ,p_attribute9 => p_attribute9
625 ,p_attribute10 => p_attribute10
626 ,p_attribute11 => p_attribute11
627 ,p_attribute12 => p_attribute12
628 ,p_attribute13 => p_attribute13
629 ,p_attribute14 => p_attribute14
630 ,p_attribute15 => p_attribute15
631 ,p_attribute16 => p_attribute16
632 ,p_attribute17 => p_attribute17
633 ,p_attribute18 => p_attribute18
634 ,p_attribute19 => p_attribute19
635 ,p_attribute20 => p_attribute20
636 ,p_pds_information_category => p_pds_information_category
637 ,p_pds_information1 => p_pds_information1
638 ,p_pds_information2 => p_pds_information2
639 ,p_pds_information3 => p_pds_information3
640 ,p_pds_information4 => p_pds_information4
641 ,p_pds_information5 => p_pds_information5
642 ,p_pds_information6 => p_pds_information6
643 ,p_pds_information7 => p_pds_information7
644 ,p_pds_information8 => p_pds_information8
645 ,p_pds_information9 => p_pds_information9
646 ,p_pds_information10 => p_pds_information10
647 ,p_pds_information11 => p_pds_information11
648 ,p_pds_information12 => p_pds_information12
649 ,p_pds_information13 => p_pds_information13
650 ,p_pds_information14 => p_pds_information14
651 ,p_pds_information15 => p_pds_information15
652 ,p_pds_information16 => p_pds_information16
653 ,p_pds_information17 => p_pds_information17
654 ,p_pds_information18 => p_pds_information18
655 ,p_pds_information19 => p_pds_information19
656 ,p_pds_information20 => p_pds_information20
657 ,p_pds_information21 => p_pds_information21
658 ,p_pds_information22 => p_pds_information22
659 ,p_pds_information23 => p_pds_information23
660 ,p_pds_information24 => p_pds_information24
661 ,p_pds_information25 => p_pds_information25
662 ,p_pds_information26 => p_pds_information26
663 ,p_pds_information27 => p_pds_information27
664 ,p_pds_information28 => p_pds_information28
665 ,p_pds_information29 => p_pds_information29
666 ,p_pds_information30 => p_pds_information30
667 );
668 exception
669 when hr_api.cannot_find_prog_unit then
670 hr_api.cannot_find_prog_unit_error
671 (p_module_name => 'UPDATE_PDS_DETAILS',
672 p_hook_type => 'AP'
673 );
674 end;
675 --
676 if p_validate then
677 raise hr_api.validate_enabled;
678 end if;
679 --
680 hr_utility.set_location(' Leaving:'||l_proc, 8);
681 --
682 exception
683 when hr_api.validate_enabled then
684 --
685 -- As the Validate_Enabled exception has been raised
686 -- we must rollback to the savepoint
687 --
688 ROLLBACK TO update_pds_details;
689 --
690 -- Only set output warning arguments
691 -- (Any key or derived arguments must be set to null
692 -- when validation only mode is being used.)
693 --
694 p_object_version_number := l_object_version_number;
695 --
696 when others then
697 --
698 -- A validation or unexpected error has occurred
699 --
700 -- Added as part of fix to bug 632479
701 --
702 ROLLBACK TO update_pds_details;
703 --
704 -- set in out parameters and set out parameters
705 --
706 p_object_version_number := l_ovn;
707 raise;
708 --
709 -- End of fix.
710 --
711 end update_pds_details;
712 --
713 -- 115.9 (START)
714 --
715 -- ----------------------------------------------------------------------------
716 -- |--------------------------< MOVE_TERM_ASSIGNMENTS >-----------------------|
717 -- ----------------------------------------------------------------------------
718 -- {Start Of Comments}
719 --
720 -- Description: This procedure keeps assignment children and the TERM_ASSIGN
721 -- record in sync with any allowable change in FPD. It also
722 -- validates that the change does not adversely impact any of
723 -- the child records.
724 --
725 -- Prerequisites: None
726 --
727 --
728 -- In Parameters:
729 -- Name Reqd Type Description
730 -- P_PERIOD_OF_SERVICE_ID Yes NUMBER PDS Identifier
731 -- P_OLD_FINAL_PROCESS_DATE Yes DATE Old FPD
732 -- P_NEW_FINAL_PROCESS_DATE Yes DATE New FPD
733 --
734 -- Out Parameters:
735 -- Name Type Description
736 -- P_ORG_NOW_NO_MANAGER_WARNING BOOLEAN Org now no manager flag
737 -- P_ASG_FUTURE_CHANGES_WARNING BOOLEAN Future Assignment changes flag
738 -- P_ENTRIES_CHANGED_WARNING VARCHAR2 Element entries changed flag
739 --
740 -- Post Success:
741 -- The TERM_ASSIGN assignment record and its children will be in sync with
742 -- the Final Process Date on the Period Of Service associated with that
743 -- assignment.
744 --
745 -- Name Type Description
746 -- - - -
747 -- Post Failure:
748 -- An exception will be raised depending on the nature of failure.
749 --
750 -- Access Status:
751 -- Internal Development Use Only.
752 --
753 -- {End Of Comments}
754 --
755 PROCEDURE move_term_assignments
756 (p_period_of_service_id IN NUMBER
757 ,p_old_final_process_date IN DATE
758 ,p_new_final_process_date IN DATE
759 --
760 -- 115.16 (START)
761 --
762 ,p_org_now_no_manager_warning OUT NOCOPY BOOLEAN
763 ,p_asg_future_changes_warning OUT NOCOPY BOOLEAN
764 ,p_entries_changed_warning OUT NOCOPY VARCHAR2
765 --
766 -- 115.16 (END)
767 --
768 ) IS
769 --
770 l_proc VARCHAR2(80) := g_package||'move_term_assignments';
771 --
772 -- Cursor to check for any Payroll actions existing after
773 -- the new FPD
774 --
775 CURSOR csr_compl_pay_act IS
776 SELECT NULL
777 FROM pay_payroll_actions ppa
778 ,pay_assignment_actions paa
779 ,per_assignments_f asg
780 ,per_periods_of_service pds
781 WHERE pds.period_of_service_id = p_period_of_service_id
782 AND pds.person_id = asg.person_id
783 AND asg.period_of_service_id = pds.period_of_service_id
784 AND asg.assignment_id = paa.assignment_id
785 AND paa.payroll_action_id = ppa.payroll_action_id
786 AND ppa.action_type NOT IN ('X','BEE')
787 AND ppa.effective_date > p_new_final_process_date;
788 --
789 l_dummy VARCHAR2(1);
790 --
791 -- Cursor to get all assignments attached to the current
792 -- PDS that used to end on the old FPD
793 --
794 CURSOR csr_pds_asgs IS
795 SELECT asg.assignment_id
796 ,asg.effective_start_date
797 ,asg.effective_end_date
798 ,asg.object_version_number
799 ,pds.actual_termination_date
800 FROM per_assignments_f asg
801 ,per_periods_of_service pds
802 WHERE pds.period_of_service_id = p_period_of_service_id
803 AND pds.person_id = asg.person_id
804 AND pds.period_of_service_id = asg.period_of_service_id
805 AND asg.effective_end_date = p_old_final_process_date;
806 --
807 l_assignment_id per_assignments_f.assignment_id%TYPE;
808 l_object_version_number per_assignments_f.object_version_number%TYPE;
809 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
810 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
811 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
812 l_new_final_process_date per_periods_of_service.final_process_date%TYPE;
813 --
814 -- 115.16 (START)
815 --
816 --l_org_now_no_manager_warning BOOLEAN;
817 --l_asg_future_changes_warning BOOLEAN;
818 --l_entries_changed_warning VARCHAR2(200);
819 --
820 -- Get correct ASG OVN
821 --
822 CURSOR csr_asg_ovn (p_assignment_id NUMBER
823 ,p_effective_date DATE) IS
824 SELECT asg.object_version_number
825 FROM per_assignments_f asg
826 WHERE asg.assignment_id = csr_asg_ovn.p_assignment_id
827 AND csr_asg_ovn.p_effective_date BETWEEN asg.effective_start_date
828 AND asg.effective_end_date;
829 --
830 -- 115.16 (END)
831 --
832 --
833 -- Locking ladder cursors
834 --
835 CURSOR csr_asgs IS
836 SELECT NULL
837 FROM per_assignments_f asg
838 ,per_periods_of_service pds
839 WHERE pds.period_of_service_id = p_period_of_service_id
840 AND pds.period_of_service_id = asg.period_of_service_id
841 AND pds.person_id = asg.person_id
842 AND asg.effective_end_date = p_old_final_process_date;
843 --
844 CURSOR csr_lock_csa (p_assignment_id NUMBER) IS
845 SELECT NULL
846 FROM pay_cost_allocations_f csa
847 WHERE csa.assignment_id = csr_lock_csa.p_assignment_id
848 FOR UPDATE NOWAIT;
849 --
850 CURSOR csr_lock_alu (p_assignment_id NUMBER) IS
851 SELECT NULL
852 FROM pay_assignment_link_usages_f alu
853 WHERE alu.assignment_id = csr_lock_alu.p_assignment_id
854 FOR UPDATE NOWAIT;
855 --
856 CURSOR csr_lock_ele (p_assignment_id NUMBER) IS
857 SELECT NULL
858 FROM pay_element_entries_f ele
859 WHERE ele.assignment_id = csr_lock_ele.p_assignment_id
860 FOR UPDATE NOWAIT;
861 --
862 CURSOR csr_lock_eev (p_assignment_id NUMBER) IS
863 SELECT eev.element_entry_id
864 FROM pay_element_entry_values_f eev,
865 pay_element_entries_f ele
866 WHERE ele.assignment_id = p_assignment_id
867 AND eev.element_entry_id = ele.element_entry_id
868 FOR UPDATE NOWAIT;
869 --
870 CURSOR csr_lock_spp (p_assignment_id NUMBER) IS
871 SELECT NULL
872 FROM per_spinal_point_placements_f spp
873 WHERE spp.assignment_id = csr_lock_spp.p_assignment_id
874 FOR UPDATE NOWAIT;
875 --
876 CURSOR csr_lock_ppm (p_assignment_id NUMBER) IS
877 SELECT NULL
878 FROM pay_personal_payment_methods_f ppm
879 WHERE ppm.assignment_id = csr_lock_ppm.p_assignment_id
880 FOR UPDATE NOWAIT;
881 --
882 CURSOR csr_lock_sas (p_assignment_id NUMBER) IS
883 SELECT NULL
884 FROM per_secondary_ass_statuses sas
885 WHERE sas.assignment_id = csr_lock_sas.p_assignment_id
886 FOR UPDATE NOWAIT;
887 --
888 CURSOR csr_lock_pyp (p_assignment_id NUMBER) IS
889 SELECT NULL
890 FROM per_pay_proposals pyp
891 WHERE pyp.assignment_id = csr_lock_pyp.p_assignment_id
892 FOR UPDATE NOWAIT;
893 --
894 CURSOR csr_lock_asa (p_assignment_id NUMBER) IS
895 SELECT asa.assignment_action_id
896 FROM pay_assignment_actions asa
897 WHERE asa.assignment_id = csr_lock_asa.p_assignment_id
898 FOR UPDATE NOWAIT;
899 --
900 CURSOR csr_lock_abv (p_assignment_id NUMBER) IS
901 SELECT assignment_budget_value_id
902 FROM per_assignment_budget_values_f
903 WHERE assignment_id = csr_lock_abv.p_assignment_id
904 AND p_new_final_process_date BETWEEN effective_start_date
905 AND effective_end_date
906 FOR UPDATE NOWAIT;
907 --
908 BEGIN
909 --
910 hr_utility.set_location('Entering:'|| l_proc, 5);
911 hr_utility.set_location('Old FPD:'|| p_old_final_process_date, 6);
912 hr_utility.set_location('New FPD:'|| p_new_final_process_date, 7);
913 --
914 -- Issue a savepoint.
915 --
916 SAVEPOINT move_term_assignments;
917 --
918 hr_utility.set_location(l_proc, 10);
919 --
920 -- FPD is changing to an earlier value
921 --
922 IF p_new_final_process_date IS NOT NULL
923 AND p_old_final_process_date IS NOT NULL
924 AND p_new_final_process_date < p_old_final_process_date
925 THEN
926 --
927 hr_utility.set_location(l_proc, 20);
928 --
929 -- Check if for any Payroll actions existing after
930 -- the new FPD
931 --
932 OPEN csr_compl_pay_act;
933 FETCH csr_compl_pay_act INTO l_dummy;
934 IF csr_compl_pay_act%FOUND THEN
935 CLOSE csr_compl_pay_act;
936 hr_utility.set_message(800,'HR_449742_EMP_FPD_PAYACT');
937 hr_utility.raise_error;
938 END IF;
939 CLOSE csr_compl_pay_act;
940 --
941 hr_utility.set_location(l_proc, 30);
942 --
943 -- For all assignments in the current PDS that ended on
944 -- the old FPD
945 --
946 OPEN csr_pds_asgs;
947 LOOP
948 --
949 hr_utility.set_location(l_proc, 40);
950 --
951 FETCH csr_pds_asgs INTO l_assignment_id
952 ,l_effective_start_date
953 ,l_effective_end_date
954 ,l_object_version_number
955 ,l_actual_termination_date;
956 EXIT WHEN csr_pds_asgs%NOTFOUND;
957 --
958 -- 115.16 (START)
959 --
960 --
961 -- Get the correct assignment ovn in case of future assignment changes
962 --
963 OPEN csr_asg_ovn(l_assignment_id
964 ,p_new_final_process_date);
965 FETCH csr_asg_ovn INTO l_object_version_number;
966 CLOSE csr_asg_ovn;
967 --
968 -- 115.16 (END)
969 --
970 --
971 -- Invoke internal process to validate and then perform the
972 -- appropriate updates to effective end date and delete any
973 -- rows now starting after the new FPD
974 --
975 hr_assignment_internal.final_process_emp_asg_sup
976 (p_assignment_id => l_assignment_id
977 ,p_object_version_number => l_object_version_number
978 ,p_final_process_date => p_new_final_process_date
979 ,p_actual_termination_date => l_actual_termination_date
980 ,p_effective_start_date => l_effective_start_date
981 ,p_effective_end_date => l_effective_end_date
982 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
983 ,p_asg_future_changes_warning => p_asg_future_changes_warning
984 ,p_entries_changed_warning => p_entries_changed_warning
985 );
986 --
987 hr_utility.set_location(l_proc, 45);
988 --
989 END LOOP;
990 CLOSE csr_pds_asgs;
991 --
992 END IF; -- new FPD < old FPD
993 --
994 hr_utility.set_location(l_proc, 50);
995 --
996 -- FPD is changing to a later value or FPD has been made null
997 --
998 IF (p_new_final_process_date IS NOT NULL
999 AND p_old_final_process_date IS NOT NULL
1000 AND p_new_final_process_date > p_old_final_process_date)
1001 OR (p_new_final_process_date IS NULL
1002 AND p_old_final_process_date IS NOT NULL)
1003 THEN
1004 --
1005 hr_utility.set_location(l_proc, 60);
1006 --
1007 IF p_new_final_process_date IS NOT NULL
1008 AND p_old_final_process_date IS NOT NULL
1009 AND p_new_final_process_date > p_old_final_process_date
1010 THEN
1011 l_new_final_process_date := p_new_final_process_date;
1012 ELSE
1013 l_new_final_process_date := hr_api.g_eot;
1014 END IF;
1015 --
1016 -- Lock assignment records
1017 --
1018 OPEN csr_asgs;
1019 CLOSE csr_asgs;
1020 --
1021 -- For all assignments in the current PDS that ended on
1022 -- the old FPD
1023 --
1024 OPEN csr_pds_asgs;
1025 LOOP
1026 --
1027 hr_utility.set_location(l_proc, 70);
1028 --
1029 FETCH csr_pds_asgs INTO l_assignment_id
1030 ,l_effective_start_date
1031 ,l_effective_end_date
1032 ,l_object_version_number
1033 ,l_actual_termination_date;
1034 EXIT WHEN csr_pds_asgs%NOTFOUND;
1035 --
1036 hr_utility.set_location('RowCount:'||SQL%ROWCOUNT, 75);
1037 --
1038 OPEN csr_lock_csa(l_assignment_id); -- Locking ladder step 970
1039 CLOSE csr_lock_csa;
1040 --
1041 OPEN csr_lock_alu(l_assignment_id); -- Locking ladder step 1110
1042 CLOSE csr_lock_alu;
1043 --
1044 OPEN csr_lock_asa(l_assignment_id); -- Locking ladder step 1190
1045 CLOSE csr_lock_asa;
1046 --
1047 OPEN csr_lock_ele(l_assignment_id); -- Locking ladder step 1440
1048 CLOSE csr_lock_ele;
1049 --
1050 OPEN csr_lock_eev(l_assignment_id); -- Locking ladder step 1450
1051 CLOSE csr_lock_eev;
1052 --
1053 OPEN csr_lock_spp(l_assignment_id); -- Locking ladder step 1470
1054 CLOSE csr_lock_spp;
1055 --
1056 OPEN csr_lock_ppm(l_assignment_id); -- Locking ladder step 1490
1057 CLOSE csr_lock_ppm;
1058 --
1059 OPEN csr_lock_abv(l_assignment_id); -- Locking ladder step 1550
1060 CLOSE csr_lock_abv;
1061 --
1062 OPEN csr_lock_sas(l_assignment_id); -- Locking ladder step 1590
1063 CLOSE csr_lock_sas;
1064 --
1065 hr_utility.set_location(l_proc, 80);
1066 --
1067 UPDATE per_assignments_f
1068 SET effective_end_date = l_new_final_process_date
1069 WHERE assignment_id = l_assignment_id
1070 AND effective_start_date = l_effective_start_date
1071 AND effective_end_date = l_effective_end_date
1072 AND object_version_number = l_object_version_number;
1073 --
1074 UPDATE pay_cost_allocations_f
1075 SET effective_end_date = l_new_final_process_date
1076 WHERE assignment_id = l_assignment_id
1077 AND p_old_final_process_date BETWEEN effective_start_date
1078 AND effective_end_date;
1079 --
1080 UPDATE per_spinal_point_placements_f
1081 SET effective_end_date = l_new_final_process_date
1082 WHERE assignment_id = l_assignment_id
1083 AND p_old_final_process_date BETWEEN effective_start_date
1084 AND effective_end_date;
1085 --
1086 UPDATE per_assignment_budget_values_f
1087 SET effective_end_date = l_new_final_process_date
1088 WHERE assignment_id = l_assignment_id
1089 AND p_old_final_process_date BETWEEN effective_start_date
1090 AND effective_end_date;
1091 --
1092 UPDATE pay_assignment_link_usages_f
1093 SET effective_end_date = l_new_final_process_date
1094 WHERE assignment_id = l_assignment_id
1095 AND p_old_final_process_date BETWEEN effective_start_date
1096 AND effective_end_date;
1097 --
1098 UPDATE pay_assignment_actions
1099 SET end_date = l_new_final_process_date
1100 WHERE assignment_id = l_assignment_id
1101 AND p_old_final_process_date BETWEEN start_date
1102 AND end_date;
1103 --
1104 --
1105 -- 115.14 (START)
1106 --
1107 -- UPDATE pay_element_entry_values_f
1108 -- SET effective_end_date = l_new_final_process_date
1109 -- WHERE p_old_final_process_date BETWEEN effective_start_date
1110 -- AND effective_end_date
1111 -- AND element_entry_id IN (SELECT element_entry_id
1112 -- FROM pay_element_entries_f
1113 -- WHERE assignment_id = l_assignment_id);
1114 --
1115 -- UPDATE pay_element_entries_f
1116 -- SET effective_end_date = l_new_final_process_date
1117 -- WHERE assignment_id = l_assignment_id
1118 -- AND p_old_final_process_date BETWEEN effective_start_date
1119 -- AND effective_end_date;
1120 --
1121 -- 115.14 (END)
1122 --
1123 -- 115.15 (START)
1124 --
1125 per_pds_utils.move_elements_with_fpd
1126 (p_assignment_id => l_assignment_id
1127 ,p_periods_of_service_id => p_period_of_service_id
1128 ,p_old_final_process_date => p_old_final_process_date
1129 ,p_new_final_process_date => l_new_final_process_date
1130 );
1131 --
1132 -- 115.15 (END)
1133 --
1134 --
1135 UPDATE pay_personal_payment_methods_f ppm
1136 SET effective_end_date = l_new_final_process_date
1137 WHERE assignment_id = l_assignment_id
1138 AND p_old_final_process_date BETWEEN effective_start_date
1139 AND effective_end_date;
1140 --
1141 UPDATE per_secondary_ass_statuses
1142 SET end_date = l_new_final_process_date
1143 WHERE assignment_id = l_assignment_id
1144 AND NVL(end_date,p_old_final_process_date) = p_old_final_process_date;
1145 --
1146 hr_utility.set_location(l_proc, 90);
1147 --
1148 END LOOP;
1149 CLOSE csr_pds_asgs;
1150 END IF; -- new FPD > old FPD
1151 --
1152 hr_utility.set_location('Leaving:'|| l_proc, 100);
1153 --
1154 EXCEPTION
1155 --
1156 WHEN OTHERS THEN
1157 --
1158 -- An unexpected error has occurred
1159 --
1160 hr_utility.set_location(l_proc, 9999);
1161 ROLLBACK TO move_term_assignments;
1162 RAISE;
1163 --
1164 END move_term_assignments;
1165 --
1166 -- 115.9 (END)
1167 --
1168 --
1169 end hr_periods_of_service_api;