[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.14.12020000.2 2012/11/08 12:44:57 srannama 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 l_system_status per_assignment_status_types.per_system_status%TYPE;
815 l_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%TYPE;
816 l_rec per_all_assignments_f%ROWTYPE;
817 l_created_by per_all_assignments_f.created_by%TYPE;
818 l_creation_date per_all_assignments_f.creation_date%TYPE;
819 l_last_update_date per_all_assignments_f.last_update_date%TYPE;
820 l_last_updated_by per_all_assignments_f.last_updated_by%TYPE;
821 l_last_update_login per_all_assignments_f.last_update_login%TYPE;
822 l_legislation_code per_business_groups.legislation_code%TYPE;
823 --
824 -- 115.16 (START)
825 --
826 --l_org_now_no_manager_warning BOOLEAN;
827 --l_asg_future_changes_warning BOOLEAN;
828 --l_entries_changed_warning VARCHAR2(200);
829 --
830 -- Get correct ASG OVN
831 --
832 CURSOR csr_asg_ovn (p_assignment_id NUMBER
833 ,p_effective_date DATE) IS
834 SELECT asg.object_version_number
835 FROM per_assignments_f asg
836 WHERE asg.assignment_id = csr_asg_ovn.p_assignment_id
837 AND csr_asg_ovn.p_effective_date BETWEEN asg.effective_start_date
838 AND asg.effective_end_date;
839 --
840 -- 115.16 (END)
841 --
842 --
843 -- Locking ladder cursors
844 --
845 CURSOR csr_asgs IS
846 SELECT NULL
847 FROM per_assignments_f asg
848 ,per_periods_of_service pds
849 WHERE pds.period_of_service_id = p_period_of_service_id
850 AND pds.period_of_service_id = asg.period_of_service_id
851 AND pds.person_id = asg.person_id
852 AND asg.effective_end_date = p_old_final_process_date;
853 --
854 CURSOR csr_lock_csa (p_assignment_id NUMBER) IS
855 SELECT NULL
856 FROM pay_cost_allocations_f csa
857 WHERE csa.assignment_id = csr_lock_csa.p_assignment_id
858 FOR UPDATE NOWAIT;
859 --
860 CURSOR csr_lock_alu (p_assignment_id NUMBER) IS
861 SELECT NULL
862 FROM pay_assignment_link_usages_f alu
863 WHERE alu.assignment_id = csr_lock_alu.p_assignment_id
864 FOR UPDATE NOWAIT;
865 --
866 CURSOR csr_lock_ele (p_assignment_id NUMBER) IS
867 SELECT NULL
868 FROM pay_element_entries_f ele
869 WHERE ele.assignment_id = csr_lock_ele.p_assignment_id
870 FOR UPDATE NOWAIT;
871 --
872 CURSOR csr_lock_eev (p_assignment_id NUMBER) IS
873 SELECT eev.element_entry_id
874 FROM pay_element_entry_values_f eev,
875 pay_element_entries_f ele
876 WHERE ele.assignment_id = p_assignment_id
877 AND eev.element_entry_id = ele.element_entry_id
878 FOR UPDATE NOWAIT;
879 --
880 CURSOR csr_lock_spp (p_assignment_id NUMBER) IS
881 SELECT NULL
882 FROM per_spinal_point_placements_f spp
883 WHERE spp.assignment_id = csr_lock_spp.p_assignment_id
884 FOR UPDATE NOWAIT;
885 --
886 CURSOR csr_lock_ppm (p_assignment_id NUMBER) IS
887 SELECT NULL
888 FROM pay_personal_payment_methods_f ppm
889 WHERE ppm.assignment_id = csr_lock_ppm.p_assignment_id
890 FOR UPDATE NOWAIT;
891 --
892 CURSOR csr_lock_sas (p_assignment_id NUMBER) IS
893 SELECT NULL
894 FROM per_secondary_ass_statuses sas
895 WHERE sas.assignment_id = csr_lock_sas.p_assignment_id
896 FOR UPDATE NOWAIT;
897 --
898 CURSOR csr_lock_pyp (p_assignment_id NUMBER) IS
899 SELECT NULL
900 FROM per_pay_proposals pyp
901 WHERE pyp.assignment_id = csr_lock_pyp.p_assignment_id
902 FOR UPDATE NOWAIT;
903 --
904 CURSOR csr_lock_asa (p_assignment_id NUMBER) IS
905 SELECT asa.assignment_action_id
906 FROM pay_assignment_actions asa
907 WHERE asa.assignment_id = csr_lock_asa.p_assignment_id
908 FOR UPDATE NOWAIT;
909 --
910 CURSOR csr_lock_abv (p_assignment_id NUMBER) IS
911 SELECT assignment_budget_value_id
912 FROM per_assignment_budget_values_f
913 WHERE assignment_id = csr_lock_abv.p_assignment_id
914 AND p_new_final_process_date BETWEEN effective_start_date
915 AND effective_end_date
916 FOR UPDATE NOWAIT;
917 --
918
919 CURSOR csr_get_ast_status_details(p_assignment_id NUMBER) IS
920 SELECT ast.per_system_status
921 FROM per_assignment_status_types ast,per_all_assignments_f paaf
922 WHERE ast.assignment_status_type_id = paaf.assignment_status_type_id
923 AND paaf.assignment_id=p_assignment_id
924 AND ast.per_system_status='TERM_ASSIGN'
925 AND paaf.effective_end_date=p_old_final_process_date;
926
927 CURSOR assgn_rec (p_assignment_id NUMBER) is
928 SELECT * from per_all_assignments_f paaf
929 WHERE paaf.assignment_id=p_assignment_id
930 AND paaf.effective_end_date=p_old_final_process_date;
931
932
933 BEGIN
934 --
935 hr_utility.set_location('Entering:'|| l_proc, 5);
936 hr_utility.set_location('Old FPD:'|| p_old_final_process_date, 6);
937 hr_utility.set_location('New FPD:'|| p_new_final_process_date, 7);
938 --
939 -- Issue a savepoint.
940 --
941 SAVEPOINT move_term_assignments;
942 --
943 hr_utility.set_location(l_proc, 10);
944 --
945 -- FPD is changing to an earlier value
946 --
947 IF p_new_final_process_date IS NOT NULL
948 AND p_old_final_process_date IS NOT NULL
949 AND p_new_final_process_date < p_old_final_process_date
950 THEN
951 --
952 hr_utility.set_location(l_proc, 20);
953 --
954 -- Check if for any Payroll actions existing after
955 -- the new FPD
956 --
957 OPEN csr_compl_pay_act;
958 FETCH csr_compl_pay_act INTO l_dummy;
959 IF csr_compl_pay_act%FOUND THEN
960 CLOSE csr_compl_pay_act;
961 hr_utility.set_message(800,'HR_449742_EMP_FPD_PAYACT');
962 hr_utility.raise_error;
963 END IF;
964 CLOSE csr_compl_pay_act;
965 --
966 hr_utility.set_location(l_proc, 30);
967 --
968 -- For all assignments in the current PDS that ended on
969 -- the old FPD
970 --
971 OPEN csr_pds_asgs;
972 LOOP
973 --
974 hr_utility.set_location(l_proc, 40);
975 --
976 FETCH csr_pds_asgs INTO l_assignment_id
977 ,l_effective_start_date
978 ,l_effective_end_date
979 ,l_object_version_number
980 ,l_actual_termination_date;
981 EXIT WHEN csr_pds_asgs%NOTFOUND;
982 --
983 -- 115.16 (START)
984 --
985 --
986 -- Get the correct assignment ovn in case of future assignment changes
987 --
988 OPEN csr_asg_ovn(l_assignment_id
989 ,p_new_final_process_date);
990 FETCH csr_asg_ovn INTO l_object_version_number;
991 CLOSE csr_asg_ovn;
992 --
993 -- 115.16 (END)
994 --
995 --
996 -- Invoke internal process to validate and then perform the
997 -- appropriate updates to effective end date and delete any
998 -- rows now starting after the new FPD
999 --
1000 hr_assignment_internal.final_process_emp_asg_sup
1001 (p_assignment_id => l_assignment_id
1002 ,p_object_version_number => l_object_version_number
1003 ,p_final_process_date => p_new_final_process_date
1004 ,p_actual_termination_date => l_actual_termination_date
1005 ,p_effective_start_date => l_effective_start_date
1006 ,p_effective_end_date => l_effective_end_date
1007 ,p_org_now_no_manager_warning => p_org_now_no_manager_warning
1008 ,p_asg_future_changes_warning => p_asg_future_changes_warning
1009 ,p_entries_changed_warning => p_entries_changed_warning
1010 );
1011 --
1012 hr_utility.set_location(l_proc, 45);
1013 --
1014 END LOOP;
1015 CLOSE csr_pds_asgs;
1016 --
1017 END IF; -- new FPD < old FPD
1018 --
1019 hr_utility.set_location(l_proc, 50);
1020 --
1021 -- FPD is changing to a later value or FPD has been made null
1022 --
1023 IF (p_new_final_process_date IS NOT NULL
1024 AND p_old_final_process_date IS NOT NULL
1025 AND p_new_final_process_date > p_old_final_process_date)
1026 OR (p_new_final_process_date IS NULL
1027 AND p_old_final_process_date IS NOT NULL)
1028 THEN
1029 --
1030 hr_utility.set_location(l_proc, 60);
1031 --
1032 IF p_new_final_process_date IS NOT NULL
1033 AND p_old_final_process_date IS NOT NULL
1034 AND p_new_final_process_date > p_old_final_process_date
1035 THEN
1036 l_new_final_process_date := p_new_final_process_date;
1037 ELSE
1038 l_new_final_process_date := hr_api.g_eot;
1039 END IF;
1040 --
1041 -- Lock assignment records
1042 --
1043 OPEN csr_asgs;
1044 CLOSE csr_asgs;
1045 --
1046 -- For all assignments in the current PDS that ended on
1047 -- the old FPD
1048 --
1049 OPEN csr_pds_asgs;
1050 LOOP
1051 --
1052 hr_utility.set_location(l_proc, 70);
1053 --
1054 FETCH csr_pds_asgs INTO l_assignment_id
1055 ,l_effective_start_date
1056 ,l_effective_end_date
1057 ,l_object_version_number
1058 ,l_actual_termination_date;
1059 EXIT WHEN csr_pds_asgs%NOTFOUND;
1060 --
1061 hr_utility.set_location('RowCount:'||SQL%ROWCOUNT, 75);
1062 --
1063 OPEN csr_lock_csa(l_assignment_id); -- Locking ladder step 970
1064 CLOSE csr_lock_csa;
1065 --
1066 OPEN csr_lock_alu(l_assignment_id); -- Locking ladder step 1110
1067 CLOSE csr_lock_alu;
1068 --
1069 OPEN csr_lock_asa(l_assignment_id); -- Locking ladder step 1190
1070 CLOSE csr_lock_asa;
1071 --
1072 OPEN csr_lock_ele(l_assignment_id); -- Locking ladder step 1440
1073 CLOSE csr_lock_ele;
1074 --
1075 OPEN csr_lock_eev(l_assignment_id); -- Locking ladder step 1450
1076 CLOSE csr_lock_eev;
1077 --
1078 OPEN csr_lock_spp(l_assignment_id); -- Locking ladder step 1470
1079 CLOSE csr_lock_spp;
1080 --
1081 OPEN csr_lock_ppm(l_assignment_id); -- Locking ladder step 1490
1082 CLOSE csr_lock_ppm;
1083 --
1084 OPEN csr_lock_abv(l_assignment_id); -- Locking ladder step 1550
1085 CLOSE csr_lock_abv;
1086 --
1087 OPEN csr_lock_sas(l_assignment_id); -- Locking ladder step 1590
1088 CLOSE csr_lock_sas;
1089 --
1090 hr_utility.set_location(l_proc, 80);
1091 --
1092
1093 OPEN csr_get_ast_status_details(l_assignment_id);
1094 FETCH csr_get_ast_status_details INTO l_system_status;
1095 IF csr_get_ast_status_details%FOUND THEN
1096
1097 hr_utility.set_location(l_proc, 81);
1098
1099 UPDATE per_assignments_f
1100 SET effective_end_date = l_new_final_process_date
1101 WHERE assignment_id = l_assignment_id
1102 AND effective_start_date = l_effective_start_date
1103 AND effective_end_date = l_effective_end_date
1104 AND object_version_number = l_object_version_number;
1105
1106 else
1107 hr_utility.set_location(l_proc, 82);
1108
1109 l_created_by := fnd_global.user_id;
1110 l_creation_date := sysdate;
1111 l_last_update_date := sysdate;
1112 l_last_updated_by := fnd_global.user_id;
1113 l_last_update_login := fnd_global.login_id;
1114
1115
1116 OPEN assgn_rec(l_assignment_id);
1117 FETCH assgn_rec INTO l_rec;
1118 CLOSE assgn_rec;
1119
1120 hr_utility.set_location(l_proc, 83);
1121
1122 SELECT legislation_code INTO l_legislation_code
1123 FROM per_business_groups
1124 WHERE business_group_id=l_rec.business_group_id;
1125
1126 hr_utility.set_location(l_proc, 84);
1127
1128 per_people3_pkg.get_default_person_type
1129 (p_required_type => 'TERM_ASSIGN'
1130 ,p_business_group_id => l_rec.business_group_id
1131 ,p_legislation_code => l_legislation_code
1132 ,p_person_type => l_assignment_status_type_id);
1133
1134 hr_utility.set_location(l_proc, 85);
1135
1136 INSERT INTO per_all_assignments_f
1137 ( assignment_id,
1138 effective_start_date,
1139 effective_end_date,
1140 business_group_id,
1141 recruiter_id,
1142 grade_id,
1143 position_id,
1144 job_id,
1145 assignment_status_type_id,
1146 payroll_id,
1147 location_id,
1148 person_referred_by_id,
1149 supervisor_id,
1150 special_ceiling_step_id,
1151 person_id,
1152 recruitment_activity_id,
1153 source_organization_id,
1154 organization_id,
1155 people_group_id,
1156 soft_coding_keyflex_id,
1157 vacancy_id,
1158 pay_basis_id,
1159 assignment_sequence,
1160 assignment_type,
1161 primary_flag,
1162 application_id,
1163 assignment_number,
1164 change_reason,
1165 comment_id,
1166 date_probation_end,
1167 default_code_comb_id,
1168 employment_category,
1169 frequency,
1170 internal_address_line,
1171 manager_flag,
1172 normal_hours,
1173 perf_review_period,
1174 perf_review_period_frequency,
1175 period_of_service_id,
1176 probation_period,
1177 probation_unit,
1178 sal_review_period,
1179 sal_review_period_frequency,
1180 set_of_books_id,
1181 source_type,
1182 time_normal_finish,
1183 time_normal_start,
1184 bargaining_unit_code,
1185 labour_union_member_flag,
1186 hourly_salaried_code,
1187 request_id,
1188 program_application_id,
1189 program_id,
1190 program_update_date,
1191 ass_attribute_category,
1192 ass_attribute1,
1193 ass_attribute2,
1194 ass_attribute3,
1195 ass_attribute4,
1196 ass_attribute5,
1197 ass_attribute6,
1198 ass_attribute7,
1199 ass_attribute8,
1200 ass_attribute9,
1201 ass_attribute10,
1202 ass_attribute11,
1203 ass_attribute12,
1204 ass_attribute13,
1205 ass_attribute14,
1206 ass_attribute15,
1207 ass_attribute16,
1208 ass_attribute17,
1209 ass_attribute18,
1210 ass_attribute19,
1211 ass_attribute20,
1212 ass_attribute21,
1213 ass_attribute22,
1214 ass_attribute23,
1215 ass_attribute24,
1216 ass_attribute25,
1217 ass_attribute26,
1218 ass_attribute27,
1219 ass_attribute28,
1220 ass_attribute29,
1221 ass_attribute30,
1222 title,
1223 contract_id,
1224 establishment_id,
1225 collective_agreement_id,
1226 cagr_grade_def_id,
1227 cagr_id_flex_num,
1228 object_version_number,
1229 created_by,
1230 creation_date,
1231 last_update_date,
1232 last_updated_by,
1233 last_update_login,
1234 notice_period,
1235 notice_period_uom,
1236 employee_category,
1237 work_at_home,
1238 job_post_source_name,
1239 posting_content_id,
1240 period_of_placement_date_start,
1241 vendor_id,
1242 vendor_employee_number,
1243 vendor_assignment_number,
1244 assignment_category,
1245 project_title,
1246 applicant_rank,
1247 grade_ladder_pgm_id,
1248 supervisor_assignment_id,
1249 vendor_site_id,
1250 po_header_id,
1251 po_line_id,
1252 projected_assignment_end
1253 )
1254 VALUES
1255 ( l_rec.assignment_id,
1256 p_old_final_process_date+1,
1257 l_new_final_process_date,
1258 l_rec.business_group_id,
1259 l_rec.recruiter_id,
1260 l_rec.grade_id,
1261 l_rec.position_id,
1262 l_rec.job_id,
1263 l_assignment_status_type_id,
1264 l_rec.payroll_id,
1265 l_rec.location_id,
1266 l_rec.person_referred_by_id,
1267 l_rec.supervisor_id,
1268 l_rec.special_ceiling_step_id,
1269 l_rec.person_id,
1270 l_rec.recruitment_activity_id,
1271 l_rec.source_organization_id,
1272 l_rec.organization_id,
1273 l_rec.people_group_id,
1274 l_rec.soft_coding_keyflex_id,
1275 l_rec.vacancy_id,
1276 l_rec.pay_basis_id,
1277 l_rec.assignment_sequence,
1278 l_rec.assignment_type,
1279 l_rec.primary_flag,
1280 l_rec.application_id,
1281 l_rec.assignment_number,
1282 l_rec.change_reason,
1283 l_rec.comment_id,
1284 l_rec.date_probation_end,
1285 l_rec.default_code_comb_id,
1286 l_rec.employment_category,
1287 l_rec.frequency,
1288 l_rec.internal_address_line,
1289 l_rec.manager_flag,
1290 l_rec.normal_hours,
1291 l_rec.perf_review_period,
1292 l_rec.perf_review_period_frequency,
1293 l_rec.period_of_service_id,
1294 l_rec.probation_period,
1295 l_rec.probation_unit,
1296 l_rec.sal_review_period,
1297 l_rec.sal_review_period_frequency,
1298 l_rec.set_of_books_id,
1299 l_rec.source_type,
1300 l_rec.time_normal_finish,
1301 l_rec.time_normal_start,
1302 l_rec.bargaining_unit_code,
1303 l_rec.labour_union_member_flag,
1304 l_rec.hourly_salaried_code,
1305 l_rec.request_id,
1306 l_rec.program_application_id,
1307 l_rec.program_id,
1308 l_rec.program_update_date,
1309 l_rec.ass_attribute_category,
1310 l_rec.ass_attribute1,
1311 l_rec.ass_attribute2,
1312 l_rec.ass_attribute3,
1313 l_rec.ass_attribute4,
1314 l_rec.ass_attribute5,
1315 l_rec.ass_attribute6,
1316 l_rec.ass_attribute7,
1317 l_rec.ass_attribute8,
1318 l_rec.ass_attribute9,
1319 l_rec.ass_attribute10,
1320 l_rec.ass_attribute11,
1321 l_rec.ass_attribute12,
1322 l_rec.ass_attribute13,
1323 l_rec.ass_attribute14,
1324 l_rec.ass_attribute15,
1325 l_rec.ass_attribute16,
1326 l_rec.ass_attribute17,
1327 l_rec.ass_attribute18,
1328 l_rec.ass_attribute19,
1329 l_rec.ass_attribute20,
1330 l_rec.ass_attribute21,
1331 l_rec.ass_attribute22,
1332 l_rec.ass_attribute23,
1333 l_rec.ass_attribute24,
1334 l_rec.ass_attribute25,
1335 l_rec.ass_attribute26,
1336 l_rec.ass_attribute27,
1337 l_rec.ass_attribute28,
1338 l_rec.ass_attribute29,
1339 l_rec.ass_attribute30,
1340 l_rec.title,
1341 l_rec.contract_id,
1342 l_rec.establishment_id,
1343 l_rec.collective_agreement_id,
1344 l_rec.cagr_grade_def_id,
1345 l_rec.cagr_id_flex_num,
1346 l_rec.object_version_number,
1347 l_created_by,
1348 l_creation_date,
1349 l_last_update_date,
1350 l_last_updated_by,
1351 l_last_update_login,
1352 l_rec.notice_period,
1353 l_rec.notice_period_uom,
1354 l_rec.employee_category,
1355 l_rec.work_at_home,
1356 l_rec.job_post_source_name,
1357 l_rec.posting_content_id,
1358 l_rec.period_of_placement_date_start,
1359 l_rec.vendor_id,
1360 l_rec.vendor_employee_number,
1361 l_rec.vendor_assignment_number,
1362 l_rec.assignment_category,
1363 l_rec.project_title,
1364 l_rec.applicant_rank,
1365 l_rec.grade_ladder_pgm_id,
1366 l_rec.supervisor_assignment_id,
1367 l_rec.vendor_site_id,
1368 l_rec.po_header_id,
1369 l_rec.po_line_id,
1370 l_rec.projected_assignment_end
1371 );
1372
1373 hr_utility.set_location(l_proc, 86);
1374
1375 END IF;
1376
1377 hr_utility.set_location(l_proc, 87);
1378
1379 CLOSE csr_get_ast_status_details;
1380
1381 hr_utility.set_location(l_proc, 88);
1382 --
1383 UPDATE pay_cost_allocations_f
1384 SET effective_end_date = l_new_final_process_date
1385 WHERE assignment_id = l_assignment_id
1386 AND p_old_final_process_date BETWEEN effective_start_date
1387 AND effective_end_date;
1388 --
1389 UPDATE per_spinal_point_placements_f
1390 SET effective_end_date = l_new_final_process_date
1391 WHERE assignment_id = l_assignment_id
1392 AND p_old_final_process_date BETWEEN effective_start_date
1393 AND effective_end_date;
1394 --
1395 UPDATE per_assignment_budget_values_f
1396 SET effective_end_date = l_new_final_process_date
1397 WHERE assignment_id = l_assignment_id
1398 AND p_old_final_process_date BETWEEN effective_start_date
1399 AND effective_end_date;
1400 --
1401 UPDATE pay_assignment_link_usages_f
1402 SET effective_end_date = l_new_final_process_date
1403 WHERE assignment_id = l_assignment_id
1404 AND p_old_final_process_date BETWEEN effective_start_date
1405 AND effective_end_date;
1406 --
1407 UPDATE pay_assignment_actions
1408 SET end_date = l_new_final_process_date
1409 WHERE assignment_id = l_assignment_id
1410 AND p_old_final_process_date BETWEEN start_date
1411 AND end_date;
1412 --
1413 --
1414 -- 115.14 (START)
1415 --
1416 -- UPDATE pay_element_entry_values_f
1417 -- SET effective_end_date = l_new_final_process_date
1418 -- WHERE p_old_final_process_date BETWEEN effective_start_date
1419 -- AND effective_end_date
1420 -- AND element_entry_id IN (SELECT element_entry_id
1421 -- FROM pay_element_entries_f
1422 -- WHERE assignment_id = l_assignment_id);
1423 --
1424 -- UPDATE pay_element_entries_f
1425 -- SET effective_end_date = l_new_final_process_date
1426 -- WHERE assignment_id = l_assignment_id
1427 -- AND p_old_final_process_date BETWEEN effective_start_date
1428 -- AND effective_end_date;
1429 --
1430 -- 115.14 (END)
1431 --
1432 -- 115.15 (START)
1433 --
1434 per_pds_utils.move_elements_with_fpd
1435 (p_assignment_id => l_assignment_id
1436 ,p_periods_of_service_id => p_period_of_service_id
1437 ,p_old_final_process_date => p_old_final_process_date
1438 ,p_new_final_process_date => l_new_final_process_date
1439 );
1440 --
1441 -- 115.15 (END)
1442 --
1443 per_saladmin_utility.adjust_pay_proposals(l_assignment_id); -- Bug 13936772
1444 --
1445 UPDATE pay_personal_payment_methods_f ppm
1446 SET effective_end_date = l_new_final_process_date
1447 WHERE assignment_id = l_assignment_id
1448 AND p_old_final_process_date BETWEEN effective_start_date
1449 AND effective_end_date;
1450 --
1451 UPDATE per_secondary_ass_statuses
1452 SET end_date = l_new_final_process_date
1453 WHERE assignment_id = l_assignment_id
1454 AND NVL(end_date,p_old_final_process_date) = p_old_final_process_date;
1455 --
1456 hr_utility.set_location(l_proc, 90);
1457 --
1458 END LOOP;
1459 CLOSE csr_pds_asgs;
1460 END IF; -- new FPD > old FPD
1461 --
1462 hr_utility.set_location('Leaving:'|| l_proc, 100);
1463 --
1464 EXCEPTION
1465 --
1466 WHEN OTHERS THEN
1467 --
1468 -- An unexpected error has occurred
1469 --
1470 hr_utility.set_location(l_proc, 9999);
1471 ROLLBACK TO move_term_assignments;
1472 RAISE;
1473 --
1474 END move_term_assignments;
1475 --
1476 -- 115.9 (END)
1477 --
1478 --
1479 end hr_periods_of_service_api;