[Home] [Help]
PACKAGE BODY: APPS.HR_ASSIGNMENT_INTERNAL
Source
1 Package Body hr_assignment_internal as
2 /* $Header: peasgbsi.pkb 120.37.12020000.3 2012/12/12 07:28:52 shpatro ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_assignment_internal.';
7 --
8 -- Start of 3335915
9 g_debug boolean := hr_utility.debug_enabled;
10 -- End of 3335915
11 -- ----------------------------------------------------------------------------
12 -- |---------------------< get_max_asg_fut_change_end_dt >--------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This function returns the maximum end date of any future assignment
18 -- changes that exist after the effective date.
19 --
20 -- Prerequisites:
21 -- It is already known that the assignment exists as of the effective date.
22 --
23 -- In Parameters:
24 -- Name Reqd Type Description
25 -- p_assignment_id Yes number
26 -- p_effective_date Yes date
27 --
28 --
29 -- Post Success:
30 --
31 -- The latest end date of a future assignment change is returned if one is
32 -- found. The process will return null if no future changes exist.
33 --
34 -- Access Status:
35 -- Internal Development Use Only.
36 --
37 -- {End Of Comments}
38 --
39 function get_max_asg_fut_change_end_dt
40 (p_assignment_id in number
41 ,p_effective_date in date
42 ) return date is
43 --
44 -- Declare cursors and local variables
45 --
46 l_max_asg_end_date per_assignments_f.effective_end_date%TYPE;
47 l_proc varchar2(72)
48 := g_package || 'get_max_asg_fut_change_end_dt';
49 --
50 cursor csr_get_max_asg_end_date is
51 select max(asg.effective_end_date)
52 from per_assignments_f asg
53 where asg.assignment_id = p_assignment_id
54 and asg.effective_start_date > p_effective_date;
55 --
56 begin
57 hr_utility.set_location('Entering:'|| l_proc, 1);
58 --
59 -- This function returns the maximum effective end date of any changes to
60 -- the specified assignment which start after the specified effective date.
61 -- If no future changes are found, then a NULL is returned.
62 --
63 open csr_get_max_asg_end_date;
64 fetch csr_get_max_asg_end_date
65 into l_max_asg_end_date;
66 close csr_get_max_asg_end_date;
67 --
68 hr_utility.set_location(l_proc, 10);
69 --
70 return l_max_asg_end_date;
71 --
72 hr_utility.set_location(' Leaving:'||l_proc, 200);
73 end get_max_asg_fut_change_end_dt;
74 --
75 -- ----------------------------------------------------------------------------
76 -- |------------------------< actual_term_cwk_asg >---------------------------|
77 -- ----------------------------------------------------------------------------
78 --
79 procedure actual_term_cwk_asg
80 (p_assignment_id in number
81 ,p_object_version_number in out nocopy number
82 ,p_actual_termination_date in date
83 ,p_last_standard_process_date in date
84 ,p_assignment_status_type_id in number
85 ,p_effective_start_date out nocopy date
86 ,p_effective_end_date out nocopy date
87 ,p_asg_future_changes_warning out nocopy boolean
88 ,p_entries_changed_warning out nocopy varchar2
89 ,p_pay_proposal_warning out nocopy boolean
90 ) is
91 --
92 -- Declare cursors and local variables
93 --
94 -- Out variables
95 --
96 l_asg_future_changes_warning boolean := FALSE;
97 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
98 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
99 l_entries_changed_warning varchar2(1) := 'N';
100 l_object_version_number per_assignments_f.object_version_number%TYPE;
101 --
102 l_business_group_id per_assignments_f.business_group_id%TYPE;
103 l_comment_id per_assignments_f.comment_id%TYPE;
104 l_correction boolean;
105 l_datetrack_mode varchar2(30);
106 l_entries_changed varchar2(1);
107 l_legislation_code per_business_groups.legislation_code%TYPE;
108 l_max_asg_end_date per_assignments_f.effective_end_date%TYPE;
109 l_no_managers_warning boolean;
110 l_org_now_no_manager_warning boolean;
111 l_pay_proposal_warning boolean := FALSE; -- Bug 3202260
112 l_other_manager_warning boolean;
113 l_hourly_salaried_warning boolean;
114 l_payroll_id_updated boolean;
115 l_update boolean;
116 l_update_change_insert boolean;
117 l_update_override boolean;
118 l_future_records_flag boolean;
119 l_cor_validation_start_date date;
120 l_cor_validation_end_date date;
121 l_validation_start_date date;
122 l_validation_end_date date;
123 l_esd_not_required date;
124 l_eed_not_required date;
125 l_proc varchar2(72) :=
126 g_package || 'actual_term_cwk_asg';
127 --
128 cursor csr_get_legislation_code is
129 select bus.legislation_code
130 from per_business_groups bus
131 where bus.business_group_id = l_business_group_id;
132 --
133 cursor csr_asg_values (l_effective_date date) is
134 select asg.object_version_number
135 , asg.effective_start_date
136 , asg.effective_end_date
137 from per_assignments_f asg
138 where asg.assignment_id = p_assignment_id
139 and l_effective_date between asg.effective_start_date
140 and asg.effective_end_date;
141 --
142 cursor csr_get_future_asg (l_effective_date date) is
143 select asg.object_version_number
144 , asg.effective_start_date
145 , asg.effective_end_date
146 from per_assignments_f asg
147 where asg.assignment_id = p_assignment_id
148 and asg.effective_start_date >= l_effective_date;
149 --
150 cursor csr_lock_alu is
151 select null
152 from pay_assignment_link_usages_f alu
153 where alu.assignment_id = p_assignment_id
154 for update nowait;
155
156 -- Fix for bug 5841180 starts here
157 /* cursor csr_lock_ele is
158 select null
159 from pay_element_entries_f ele
160 where ele.assignment_id = p_assignment_id
161 for update nowait;
162 --
163 cursor csr_lock_eev is
164 select eev.element_entry_id
165 from pay_element_entry_values_f eev,
166 pay_element_entries_f ele
167 where ele.assignment_id = p_assignment_id
168 and eev.element_entry_id = ele.element_entry_id
169 for update nowait; */
170 --
171
172
173 cursor csr_lock_ele(p_effective_date date) is
174 select null
175 from pay_element_entries_f ele
176 where ele.assignment_id = p_assignment_id
177 and p_effective_date between effective_start_date and effective_end_date
178 for update nowait;
179
180 cursor csr_lock_eev(p_effective_date date) is
181 select eev.element_entry_id
182 from pay_element_entry_values_f eev,
183 pay_element_entries_f ele
184 where ele.assignment_id = p_assignment_id
185 and eev.element_entry_id = ele.element_entry_id
186 and p_effective_date between ele.effective_start_date and ele.effective_end_date
187 for update nowait;
188
189 -- Fix for bug 5841180 ends here
190
191 cursor csr_lock_pyp is
192 select null
193 from per_pay_proposals pyp
194 where pyp.assignment_id = p_assignment_id
195 for update nowait;
196 --
197 cursor csr_lock_asa is
198 select asa.assignment_action_id
199 from pay_assignment_actions asa
200 where asa.assignment_id = p_assignment_id
201 for update nowait;
202 --
203 -- Start of fix 3202260
204 cursor csr_pay_proposal is
205 select pyp.pay_proposal_id, pyp.object_version_number
206 from per_pay_proposals pyp
207 where pyp.assignment_id = p_assignment_id
208 and pyp.change_date > p_actual_termination_date
209 order by pyp.change_date desc;
210
211 --
212 cursor csr_proposal_comp(l_proposal_id number) is
213 select ppc.component_id, ppc.object_version_number
214 from per_pay_proposal_components ppc
215 where ppc.pay_proposal_id = l_proposal_id;
216 -- End of 3202260
217 begin
218 hr_utility.set_location('Entering:'|| l_proc, 1);
219 --
220 -- Process Logic
221 --
222 -- Determine the datetrack mode to use for the assignment table handler
223 -- update call.
224 --
225 per_asg_shd.find_dt_upd_modes
226 (p_effective_date => p_actual_termination_date + 1
227 ,p_base_key_value => p_assignment_id
228 ,p_correction => l_correction
229 ,p_update => l_update
230 ,p_update_override => l_update_override
231 ,p_update_change_insert => l_update_change_insert
232 );
233 hr_utility.set_location(l_proc, 10);
234 --
235 if l_update_change_insert then
236 --
237 -- This is the case where there is a future dated assignment and
238 -- we need to insert a record betwen ATD+1 and that future change
239 -- with a TERM status. We need 'CORRECTION' of future records to
240 -- have the right status.
241 --
242 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
243 l_asg_future_changes_warning := TRUE;
244 l_object_version_number := p_object_version_number;
245 l_future_records_flag := TRUE;
246 hr_utility.set_location(l_proc, 20);
247 elsif l_update then
248 --
249 l_datetrack_mode := 'UPDATE';
250 l_object_version_number := p_object_version_number;
251 hr_utility.set_location(l_proc, 30);
252 --
253 elsif l_correction then
254 --
255 hr_utility.set_location(l_proc, 40);
256 --
257 -- We have the OVN for the assignment record which starts on ATD+1
258 -- so lock this one for termination.
259 --
260 l_object_version_number := p_object_version_number;
261 --
262 -- Lock the current row as of ATD+1, using correction mode.
263 -- This validates the object version number passed in and obtains the
264 -- validation_start_date and validation_end_date.
265 --
266 per_asg_shd.lck
267 (p_effective_date => p_actual_termination_date + 1
268 ,p_datetrack_mode => 'CORRECTION'
269 ,p_assignment_id => p_assignment_id
270 ,p_object_version_number => l_object_version_number
271 ,p_validation_start_date => l_cor_validation_start_date
272 ,p_validation_end_date => l_cor_validation_end_date
273 );
274 hr_utility.set_location(l_proc, 50);
275 --
276 -- Find out if there changes after, the day
277 -- after the actual termination date
278 --
279 l_max_asg_end_date := get_max_asg_fut_change_end_dt
280 (p_assignment_id => p_assignment_id
281 ,p_effective_date => p_actual_termination_date + 1
282 );
283 hr_utility.set_location(l_proc, 70);
284 --
285 if l_max_asg_end_date is not null then
286 --
287 l_future_records_flag := TRUE;
288 hr_utility.set_location(l_proc, 80);
289 end if;
290 --
291 hr_utility.set_location(l_proc, 110);
292 --
293 l_datetrack_mode := 'CORRECTION';
294 l_asg_future_changes_warning := TRUE;
295 -- For correction the object_version_number has already been derived.
296 hr_utility.set_location(l_proc, 120);
297 else
298 --
299 -- No other datetrack modes are valid, and so should not occur.
300 --
301 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
302 hr_utility.set_message_token('PROCEDURE', l_proc);
303 hr_utility.set_message_token('STEP','130');
304 hr_utility.raise_error;
305 end if;
306 hr_utility.set_location(l_proc, 140);
307 --
308 -- Update employee assignment.
309 --
310 per_asg_upd.upd
311 (p_assignment_id => p_assignment_id
312 ,p_effective_start_date => l_effective_start_date
313 ,p_effective_end_date => l_effective_end_date
314 ,p_business_group_id => l_business_group_id
315 -- ,p_assignment_status_type_id => p_assignment_status_type_id
316 ,p_comment_id => l_comment_id
317 ,p_payroll_id_updated => l_payroll_id_updated
318 ,p_other_manager_warning => l_other_manager_warning
319 ,p_no_managers_warning => l_no_managers_warning
320 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
321 ,p_validation_start_date => l_validation_start_date
322 ,p_validation_end_date => l_validation_end_date
323 ,p_object_version_number => l_object_version_number
324 ,p_effective_date => p_actual_termination_date + 1
325 ,p_datetrack_mode => l_datetrack_mode
326 ,p_validate => FALSE
327 ,p_hourly_salaried_warning => l_hourly_salaried_warning
328 );
329 hr_utility.set_location(l_proc, 150);
330 --
331 -- If there are future dated records then we need to process these and set the
332 -- right assignment status.
333 --
334 if l_future_records_flag then
335 --
336 -- We have future dated assignment records so set them to a
337 -- TERM_CWK_ASG status.
338 --
339 for c_asg_rec in csr_get_future_asg(p_actual_termination_date+2)
340 loop
341 per_asg_upd.upd
342 (p_assignment_id => p_assignment_id
343 ,p_effective_start_date => l_effective_start_date
344 ,p_effective_end_date => l_effective_end_date
345 ,p_business_group_id => l_business_group_id
346 -- ,p_assignment_status_type_id => p_assignment_status_type_id /*Commented for 4377925*/
347 ,p_comment_id => l_comment_id
348 ,p_payroll_id_updated => l_payroll_id_updated
349 ,p_other_manager_warning => l_other_manager_warning
350 ,p_no_managers_warning => l_no_managers_warning
351 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
352 ,p_validation_start_date => l_validation_start_date
353 ,p_validation_end_date => l_validation_end_date
354 ,p_object_version_number => c_asg_rec.object_version_number
355 ,p_effective_date => c_asg_rec.effective_start_date
356 ,p_datetrack_mode => 'CORRECTION'
357 ,p_validate => FALSE
358 ,p_hourly_salaried_warning => l_hourly_salaried_warning
359 );
360 end loop;
361 end if;
362 --
363 -- Lock the appropriate child rows for this assignment.
364 --
365 open csr_lock_alu; -- Locking ladder processing order 1110
366 close csr_lock_alu;
367 hr_utility.set_location(l_proc, 160);
368 --
369 open csr_lock_asa; -- Locking ladder processing order 1190
370 close csr_lock_asa;
371 hr_utility.set_location(l_proc,170);
372 --
373
374 -- fix for bug 5841180 starts here
375
376 /* open csr_lock_ele; -- Locking ladder processing order 1440
377 close csr_lock_ele;
378 hr_utility.set_location(l_proc, 180);
379 --
380 open csr_lock_eev; -- Locking ladder processing order 1450
381 close csr_lock_eev;
382 hr_utility.set_location(l_proc, 190); */
383 --
384
385 open csr_lock_ele(p_actual_termination_date); -- Locking ladder processing order 1440
386 close csr_lock_ele;
387 hr_utility.set_location(l_proc, 180);
388 --
389 open csr_lock_eev(p_actual_termination_date); -- Locking ladder processing order 1450
390 close csr_lock_eev;
391 hr_utility.set_location(l_proc, 190);
392 --
393
394 -- fix for bug 5841180 ends here
395
396 open csr_lock_pyp; -- Locking ladder processing order 1630
397 close csr_lock_pyp;
398 hr_utility.set_location(l_proc, 200);
399 --
400 -- Process any element entries and assignment_link_usages for this
401 -- assignment.
402 -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
403 -- the Forms Application to perform this task, so it will be used here
404 -- as well. (We require the legislation code.)
405 --
406 open csr_get_legislation_code;
407 fetch csr_get_legislation_code into l_legislation_code;
408 if csr_get_legislation_code%NOTFOUND then
409 close csr_get_legislation_code;
410 -- This should never happen
411 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
412 hr_utility.set_message_token('PROCEDURE', l_proc);
413 hr_utility.set_message_token('STEP','210');
414 hr_utility.raise_error;
415 end if;
416 close csr_get_legislation_code;
417 hr_utility.set_location(l_proc, 220);
418 --
419 -- VT 10/07/96 bug #306710 added parameter in a call list
420 hrempter.terminate_entries_and_alus
421 (p_assignment_id => p_assignment_id
422 ,p_actual_term_date => p_actual_termination_date
423 ,p_last_standard_date => p_last_standard_process_date
424 ,p_final_process_date => null
425 ,p_legislation_code => l_legislation_code
426 ,p_entries_changed_warning => l_entries_changed_warning
427 );
428 --
429 hr_utility.set_location(l_proc, 230);
430 --
431 -- Delete any pay proposals for this assignment that occur after the
432 -- actual termination date.
433 --
434 -- After the delete from per_pay_proposals a warning out parameter is set.
435 --
436 -- Start of fix 3202260
437 for rec_pay_prop in csr_pay_proposal loop
438 --
439 hr_utility.set_location(l_proc, 231);
440 --
441 for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
442 -- Calling the per_pay_proposal_components row handler to delete the
443 -- proposal components
444 --
445 hr_utility.set_location(l_proc, 232);
446 --
447 per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
448 p_object_version_number => rec_prop_comp.object_version_number,
449 p_validation_strength => 'WEAK');
450 --
451 hr_utility.set_location(l_proc, 233);
452 --
453 end loop;
454 --
455 -- Now deleting the salary proposal
456 --
457 hr_utility.set_location(l_proc, 234);
458 --
459 delete
460 from per_pay_proposals pyp
461 where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
462 --
463 -- Setting the Warning Out variable
464 if sql%notfound then
465 l_pay_proposal_warning := FALSE;
466 else
467 l_pay_proposal_warning := TRUE;
468 end if;
469 --
470 --
471 hr_utility.set_location(l_proc, 235);
472 --
473 end loop;
474 -- End of fix 3202260
475 --
476 hr_utility.set_location(l_proc, 240);
477 --
478 if l_datetrack_mode = 'CORRECTION' then
479 --
480 -- Leave p_object_version_number set to its existing value, as it will
481 -- not have changed.
482 -- Set effective date parameters to the validation start and date values
483 -- which were returned when the assignment row was locked.
484 --
485 p_effective_start_date := l_cor_validation_start_date;
486 p_effective_end_date := l_cor_validation_end_date;
487 hr_utility.set_location(l_proc, 250);
488 else
489 hr_utility.set_location(l_proc, 260);
490 --
491 -- When a different DateTrack mode is used, need to select the current
492 -- object version number and effective dates. This is because the row
493 -- as of the actual termination date will have been modified.
494 --
495 open csr_asg_values (p_actual_termination_date);
496 fetch csr_asg_values into p_object_version_number
497 , p_effective_start_date
498 , p_effective_end_date;
499 if csr_asg_values%notfound then
500 close csr_asg_values;
501 -- This should never happen.
502 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
503 hr_utility.set_message_token('PROCEDURE', l_proc);
504 hr_utility.set_message_token('STEP','270');
505 hr_utility.raise_error;
506 end if;
507 close csr_asg_values;
508 hr_utility.set_location(l_proc, 280);
509 end if;
510 --
511 -- Set other output arguments
512 --
513 p_asg_future_changes_warning := l_asg_future_changes_warning;
514 p_entries_changed_warning := l_entries_changed_warning;
515 p_pay_proposal_warning := l_pay_proposal_warning;
516 --
517 hr_utility.set_location(' Leaving:'||l_proc, 300);
518 end actual_term_cwk_asg;
519 --
520 -- ----------------------------------------------------------------------------
521 -- |-----------------------< final_process_cwk_asg >--------------------------|
522 -- ----------------------------------------------------------------------------
523 --
524
525 procedure final_process_cwk_asg
526 (p_assignment_id in number
527 ,p_object_version_number in out nocopy number
528 ,p_final_process_date in date
529 ,p_actual_termination_date in date
530 ,p_effective_start_date out nocopy date
531 ,p_effective_end_date out nocopy date
532 ,p_org_now_no_manager_warning out nocopy boolean
533 ,p_asg_future_changes_warning out nocopy boolean
534 ,p_entries_changed_warning out nocopy varchar2
535 ) is
536 --
537 -- Declare cursors and local variables
538 --
539 -- Out variables
540 --
541 l_asg_future_changes_warning boolean := FALSE;
542 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
543 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
544 l_entries_changed_warning varchar2(1) := 'N';
545 l_object_version_number per_assignments_f.object_version_number%TYPE;
546 l_org_now_no_manager_warning boolean := FALSE;
547 --
548 l_business_group_id per_assignments_f.business_group_id%TYPE;
549 l_legislation_code per_business_groups.legislation_code%TYPE;
550 l_max_asg_end_date per_assignments_f.effective_end_date%TYPE;
551 l_proc varchar2(72) :=
552 g_package || 'final_process_cwk_asg';
553 l_validation_start_date per_assignments_f.effective_start_date%TYPE;
554 l_validation_end_date per_assignments_f.effective_end_date%TYPE;
555 l_status varchar2(2);
556 --
557 --
558 cursor csr_get_busgrp_legislation is
559 select pbg.business_group_id, pbg.legislation_code
560 from per_business_groups_perf pbg
561 where pbg.business_group_id = (select distinct asg.business_group_id from
562 per_assignments_f asg
563 where asg.assignment_id = p_assignment_id);
564
565 --
566 --
567 cursor csr_lock_csa is
568 select null
569 from pay_cost_allocations_f csa
570 where csa.assignment_id = p_assignment_id
571 for update nowait;
572 --
573 cursor csr_lock_alu is
574 select null
575 from pay_assignment_link_usages_f alu
576 where alu.assignment_id = p_assignment_id
577 for update nowait;
578
579 -- fix fr bug 5841180 starts here
580 /* cursor csr_lock_ele is
581 select null
582 from pay_element_entries_f ele
583 where ele.assignment_id = p_assignment_id
584 for update nowait;
585 --
586 cursor csr_lock_eev is
587 select eev.element_entry_id
588 from pay_element_entry_values_f eev,
589 pay_element_entries_f ele
590 where ele.assignment_id = p_assignment_id
591 and eev.element_entry_id = ele.element_entry_id
592 for update nowait; */
593
594 cursor csr_lock_ele(p_effective_date date) is
595 select null
596 from pay_element_entries_f ele
597 where ele.assignment_id = p_assignment_id
598 and p_effective_date between effective_start_date and effective_end_date
599 for update nowait;
600
601 cursor csr_lock_eev(p_effective_date date) is
602 select eev.element_entry_id
603 from pay_element_entry_values_f eev,
604 pay_element_entries_f ele
605 where ele.assignment_id = p_assignment_id
606 and eev.element_entry_id = ele.element_entry_id
607 and p_effective_date between ele.effective_start_date and ele.effective_end_date
608 for update nowait;
609
610 -- fix for bug 5841180 ends here
611
612 cursor csr_lock_spp is
613 select null
614 from per_spinal_point_placements_f spp
615 where spp.assignment_id = p_assignment_id
616 for update nowait;
617 --
618 cursor csr_lock_ppm is
619 select null
620 from pay_personal_payment_methods_f ppm
621 where ppm.assignment_id = p_assignment_id
622 for update nowait;
623 --
624 cursor csr_lock_asa is
625 select asa.assignment_action_id
626 from pay_assignment_actions asa
627 where asa.assignment_id = p_assignment_id
628 for update nowait;
629 --
630 cursor csr_lock_sas is
631 select null
632 from per_secondary_ass_statuses sas
633 where sas.assignment_id = p_assignment_id
634 for update nowait;
635 --
636 cursor csr_lock_pyp is
637 select null
638 from per_pay_proposals pyp
639 where pyp.assignment_id = p_assignment_id
640 for update nowait;
641 --
642 -- Start of fix for Bug 2796523
643 cursor csr_zap_ppm is
644 select personal_payment_method_id,object_version_number,effective_start_date
645 from pay_personal_payment_methods_f
646 where assignment_id = p_assignment_id
647 and effective_start_date > p_final_process_date;
648 -- End of fix for Bug 2796523
649 --
650 cursor csr_dt_del_ppm is
651 select personal_payment_method_id,object_version_number
652 from pay_personal_payment_methods_f
653 where assignment_id = p_assignment_id
654 and p_final_process_date between effective_start_date
655 and effective_end_date;
656 --
657 cursor csr_lock_abv is
658 select assignment_budget_value_id
659 from per_assignment_budget_values_f
660 where assignment_id = p_assignment_id
661 and p_final_process_date between effective_start_date
662 and effective_end_date;
663 --
664 cursor csr_lock_asg_rates is
665 select pgr.grade_or_spinal_point_id
666 from pay_grade_rules_f pgr
667 where pgr.grade_or_spinal_point_id = p_assignment_id
668 and rate_type = 'A'
669 and p_final_process_Date between pgr.effective_start_date
670 and pgr.effective_end_date;
671 --
672 begin
673 hr_utility.set_location('Entering:'|| l_proc, 1);
674 --
675 l_object_version_number := p_object_version_number;
676 --
677 -- Validation in addition to Table Handlers
678 --
679 -- None.
680 --
681 -- Process Logic
682 --
683 -- Determine asg future changes warning.
684 -- Made changes according to first_api_issues.txt
685
686 If p_final_process_date = p_actual_termination_date then
687 --
688 l_max_asg_end_date := get_max_asg_fut_change_end_dt
689 (p_assignment_id => p_assignment_id
690 ,p_effective_date => p_actual_termination_date + 1
691 );
692 --
693 else
694 l_max_asg_end_date := get_max_asg_fut_change_end_dt
695 (p_assignment_id => p_assignment_id
696 ,p_effective_date => p_final_process_date
697 );
698 End if;
699 hr_utility.set_location(l_proc, 10);
700 --
701 if l_max_asg_end_date is not null then
702 --
703 l_asg_future_changes_warning := TRUE;
704 hr_utility.set_location(l_proc, 20);
705 --
706 end if;
707 hr_utility.set_location(l_proc, 30);
708 --
709 -- Lock the appropriate child rows for this assignment.
710 --
711 open csr_lock_csa; -- Locking ladder processing order 970
712 close csr_lock_csa;
713 hr_utility.set_location(l_proc, 40);
714 --
715 open csr_lock_alu; -- Locking ladder processing order 1110
716 close csr_lock_alu;
717 hr_utility.set_location(l_proc, 50);
718 --
719 open csr_lock_asa; -- Locking ladder processing order 1190
720 close csr_lock_asa;
721 hr_utility.set_location(l_proc,55);
722 --
723 -- fix for bug 5841180 starts here
724 /* open csr_lock_ele; -- Locking ladder processing order 1440
725 close csr_lock_ele;
726 hr_utility.set_location(l_proc, 60);
727 --
728 open csr_lock_eev; -- Locking ladder processing order 1450
729 close csr_lock_eev;
730 hr_utility.set_location(l_proc, 70);*/
731
732 open csr_lock_ele(p_final_process_date); -- Locking ladder processing order 1440
733 close csr_lock_ele;
734 hr_utility.set_location(l_proc, 60);
735 --
736 open csr_lock_eev(p_final_process_date); -- Locking ladder processing order 1450
737 close csr_lock_eev;
738 hr_utility.set_location(l_proc, 70);
739
740 -- fix for bug 5841180 ends here
741 --
742
743 open csr_lock_spp; -- Locking ladder processing order 1470
744 close csr_lock_spp;
745 hr_utility.set_location(l_proc, 80);
746 --
747 open csr_lock_ppm; -- Locking ladder processing order 1490
748 close csr_lock_ppm;
749 hr_utility.set_location(l_proc, 90);
750 --
751 --
752 open csr_lock_abv; -- Locking ladder processing order 1550
753 close csr_lock_abv;
754 hr_utility.set_location(l_proc, 115);
755
756 --
757 open csr_lock_sas; -- Locking ladder processing order 1590
758 close csr_lock_sas;
759 hr_utility.set_location(l_proc, 120);
760 --
761 open csr_lock_pyp; -- Locking ladder processing order 1630
762 close csr_lock_pyp;
763 --
764 hr_utility.set_location(l_proc, 130);
765 --
766 open csr_lock_asg_rates;
767 close csr_lock_asg_rates;
768 --
769 hr_utility.set_location(l_proc,135);
770 --
771 -- For the following tables, date effectively delete any rows which exist as
772 -- of the final process date, and ZAP any rows which start after the final
773 -- process date:
774 --
775 -- per_secondary_ass_statuses (not datetracked)
776 -- pay_cost_allocations_f
777 -- per_spinal_point_placements_f
778 -- pay_personal_payment_methods_f
779 -- per_assignment_budget_values_f
780 --
781 update per_secondary_ass_statuses sas
782 set sas.end_date = p_final_process_date
783 where sas.assignment_id = p_assignment_id
784 and sas.end_date IS NULL;
785 --
786 hr_utility.set_location(l_proc, 140);
787 --
788 delete per_secondary_ass_statuses sas
789 where sas.assignment_id = p_assignment_id
790 and sas.start_date > p_final_process_date;
791 --
792 hr_utility.set_location(l_proc, 150);
793 --
794 hr_utility.set_location(l_proc, 170);
795 --
796 update pay_cost_allocations_f pca
797 set pca.effective_end_date = p_final_process_date
798 where pca.assignment_id = p_assignment_id
799 and p_final_process_date between pca.effective_start_date
800 and pca.effective_end_date;
801 --
802 hr_utility.set_location(l_proc, 180);
803 --
804 delete pay_cost_allocations_f pca
805 where pca.assignment_id = p_assignment_id
806 and pca.effective_start_date > p_final_process_date;
807 --
808 hr_utility.set_location(l_proc, 190);
809 --
810 update per_spinal_point_placements_f spp
811 set spp.effective_end_date = p_final_process_date
812 where spp.assignment_id = p_assignment_id
813 and p_final_process_date between spp.effective_start_date
814 and spp.effective_end_date;
815 --
816 hr_utility.set_location(l_proc, 200);
817 --
818 delete per_spinal_point_placements_f spp
819 where spp.assignment_id = p_assignment_id
820 and spp.effective_start_date > p_final_process_date;
821 --
822 hr_utility.set_location(l_proc, 210);
823
824 --
825 -- SASmith date track of abv. 16-APR-1998
826
827 update per_assignment_budget_values_f abv
828 set abv.effective_end_date = p_final_process_date
829 where abv.assignment_id = p_assignment_id
830 and p_final_process_date between abv.effective_start_date
831 and abv.effective_end_date;
832 --
833 hr_utility.set_location(l_proc, 212);
834 --
835 delete per_assignment_budget_values_f abv
836 where abv.assignment_id = p_assignment_id
837 and abv.effective_start_date > p_final_process_date;
838 --
839 hr_utility.set_location(l_proc, 214);
840 --
841 update pay_grade_rules_f pgr
842 set pgr.effective_end_date = p_final_process_date
843 where pgr.grade_or_spinal_point_id = p_assignment_id
844 and pgr.rate_type = 'A'
845 and p_final_process_date between pgr.effective_start_date
846 and pgr.effective_end_date;
847 --
848 hr_utility.set_location(l_proc,220);
849 --
850 delete pay_grade_rules_f pgr
851 where grade_or_spinal_point_Id = p_assignment_id
852 and pgr.rate_type = 'A'
853 and pgr.effective_start_Date > p_final_process_date;
854 --
855 hr_utility.set_location(l_proc,225);
856 --
857 -- Process any element entries and assignment_link_usages for this
858 -- assignment.
859 -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
860 -- the Forms Application to perform this task, so it will be used here
861 -- as well. (We require the legislation code.)
862 --
863
864 open csr_get_busgrp_legislation;
865 fetch csr_get_busgrp_legislation
866 into l_business_group_id, l_legislation_code;
867 --
868 --
869 if csr_get_busgrp_legislation%NOTFOUND
870 then
871 --
872 hr_utility.set_location(l_proc, 230);
873 --
874 close csr_get_busgrp_legislation;
875 --
876 -- This should never happen!
877 --
878 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
879 hr_utility.set_message_token('PROCEDURE', l_proc);
880 hr_utility.set_message_token('STEP','20');
881 hr_utility.raise_error;
882 end if;
883 --
884 close csr_get_busgrp_legislation;
885 --
886 hr_utility.set_location(l_proc, 240);
887 hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
888 hr_utility.set_location('effective date : '||to_char(p_final_process_date,
889 'DD-MON-yyyy'),99);
890 --
891 -- VT 10/07/96 bug #306710 added parameter in a call list
892 hrempter.terminate_entries_and_alus
893 (p_assignment_id => p_assignment_id
894 ,p_actual_term_date => null
895 ,p_last_standard_date => null
896 ,p_final_process_date => p_final_process_date
897 ,p_legislation_code => l_legislation_code
898 ,p_entries_changed_warning => l_entries_changed_warning
899 );
900 --
901 --
902 hr_utility.set_location(l_proc, 250);
903 --
904
905 -- Call the row handler to date effectively delete the rows
906 --
907 for rec in csr_dt_del_ppm loop
908
909 pay_ppm_del.del
910 ( p_personal_payment_method_id => rec.personal_payment_method_id
911 ,p_effective_start_date => l_effective_start_date
912 ,p_effective_end_date => l_effective_end_date
913 ,p_object_version_number => rec.object_version_number
914 ,p_effective_date => p_final_process_date
915 ,p_datetrack_mode => 'DELETE');
916
917 end loop;
918 --
919 hr_utility.set_location(l_proc, 255);
920
921 -- Call the row handler to zap rows
922
923 for rec in csr_zap_ppm loop
924
925 pay_ppm_del.del
926 ( p_personal_payment_method_id => rec.personal_payment_method_id
927 ,p_effective_start_date => l_effective_start_date
928 ,p_effective_end_date => l_effective_end_date
929 ,p_object_version_number => rec.object_version_number
930 ,p_effective_date => rec.effective_start_date -- Bug 2796523
931 ,p_datetrack_mode => 'ZAP');
932
933 end loop;
934
935 -- Date effectively delete the assignment.
936 --
937 hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
938 hr_utility.set_location('effective date : '||to_char(p_final_process_date,
939 'DD-MON-yyyy'),99);
940 per_asg_del.del
941 (p_assignment_id => p_assignment_id
942 ,p_effective_start_date => l_effective_start_date
943 ,p_effective_end_date => l_effective_end_date
944 ,p_business_group_id => l_business_group_id
945 ,p_object_version_number => l_object_version_number
946 ,p_effective_date => p_final_process_date
947 ,p_validation_start_date => l_validation_start_date
948 ,p_validation_end_date => l_validation_end_date
949 ,p_datetrack_mode => 'DELETE'
950 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
951 );
952 --
953 --
954 hr_utility.set_location(l_proc, 260);
955 --
956 -- Set all output arguments
957 --
958 p_asg_future_changes_warning := l_asg_future_changes_warning;
959 p_effective_end_date := l_effective_end_date;
960 p_effective_start_date := l_effective_start_date;
961 p_entries_changed_warning := l_entries_changed_warning;
962 p_object_version_number := l_object_version_number;
963 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
964 --
965 hr_utility.set_location(' Leaving:'||l_proc, 400);
966 end final_process_cwk_asg;
967 --
968 -- 115.66 (START)
969 --
970 -- ----------------------------------------------------------------------------
971 -- |------------------< actual_term_emp_asg_sup (overloaded) >----------------|
972 -- ----------------------------------------------------------------------------
973 --
974 procedure actual_term_emp_asg_sup
975 (p_assignment_id in number
976 ,p_object_version_number in out nocopy number
977 ,p_actual_termination_date in date
978 ,p_last_standard_process_date in date
979 ,p_assignment_status_type_id in number
980 ,p_effective_start_date out nocopy date
981 ,p_effective_end_date out nocopy date
982 ,p_asg_future_changes_warning out nocopy boolean
983 ,p_entries_changed_warning out nocopy varchar2
984 ,p_pay_proposal_warning out nocopy boolean
985 ) is
986 --
987 l_alu_change_warning VARCHAR2(1) := 'N';
988 --
989 begin
990 actual_term_emp_asg_sup
991 (p_assignment_id => p_assignment_id
992 ,p_object_version_number => p_object_version_number
993 ,p_actual_termination_date => p_actual_termination_date
994 ,p_last_standard_process_date => p_last_standard_process_date
995 ,p_assignment_status_type_id => p_assignment_status_type_id
996 ,p_effective_start_date => p_effective_start_date
997 ,p_effective_end_date => p_effective_end_date
998 ,p_asg_future_changes_warning => p_asg_future_changes_warning
999 ,p_entries_changed_warning => p_entries_changed_warning
1000 ,p_pay_proposal_warning => p_pay_proposal_warning
1001 ,p_alu_change_warning => l_alu_change_warning
1002 );
1003 end actual_term_emp_asg_sup;
1004 --
1005 -- 115.66 (END)
1006 --
1007 -- ----------------------------------------------------------------------------
1008 -- |------------------------< actual_term_emp_asg_sup >-----------------------|
1009 -- ----------------------------------------------------------------------------
1010 --
1011 procedure actual_term_emp_asg_sup
1012 (p_assignment_id in number
1013 ,p_object_version_number in out nocopy number
1014 ,p_actual_termination_date in date
1015 ,p_last_standard_process_date in date
1016 ,p_assignment_status_type_id in number
1017 ,p_effective_start_date out nocopy date
1018 ,p_effective_end_date out nocopy date
1019 ,p_asg_future_changes_warning out nocopy boolean
1020 ,p_entries_changed_warning out nocopy varchar2
1021 ,p_pay_proposal_warning out nocopy boolean
1022 --
1023 -- 115.66 (START)
1024 --
1025 ,p_alu_change_warning out nocopy varchar2
1026 --
1027 -- 115.66 (END)
1028 --
1029 ) is
1030 --
1031 -- Declare cursors and local variables
1032 --
1033 -- Out variables
1034 --
1035 l_asg_future_changes_warning boolean := FALSE;
1036 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1037 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1038 l_entries_changed_warning varchar2(1) := 'N';
1039 l_object_version_number per_assignments_f.object_version_number%TYPE;
1040 --
1041 -- 115.66 (START)
1042 --
1043 l_alu_change_warning varchar2(1) := 'N';
1044 --
1045 -- 115.66 (END)
1046 --
1047 --
1048 l_business_group_id per_assignments_f.business_group_id%TYPE;
1049 l_comment_id per_assignments_f.comment_id%TYPE;
1050 l_correction boolean;
1051 l_datetrack_mode varchar2(30);
1052 l_entries_changed varchar2(1);
1053 l_legislation_code per_business_groups.legislation_code%TYPE;
1054 l_max_asg_end_date per_assignments_f.effective_end_date%TYPE;
1055 l_no_managers_warning boolean;
1056 l_org_now_no_manager_warning boolean;
1057 l_pay_proposal_warning boolean := FALSE; -- Bug 3202260
1058 l_other_manager_warning boolean;
1059 l_hourly_salaried_warning boolean;
1060 l_payroll_id_updated boolean;
1061 l_update boolean;
1062 l_update_change_insert boolean;
1063 l_update_override boolean;
1064 l_future_records_flag boolean;
1065 l_cor_validation_start_date date;
1066 l_cor_validation_end_date date;
1067 l_validation_start_date date;
1068 l_validation_end_date date;
1069 l_esd_not_required date;
1070 l_eed_not_required date;
1071 l_proc varchar2(72) :=
1072 g_package || 'actual_term_emp_asg_sup';
1073 --
1074 cursor csr_get_legislation_code is
1075 select bus.legislation_code
1076 from per_business_groups bus
1077 where bus.business_group_id = l_business_group_id;
1078 --
1079 cursor csr_asg_values (l_effective_date date) is
1080 select asg.object_version_number
1081 , asg.effective_start_date
1082 , asg.effective_end_date
1083 from per_assignments_f asg
1084 where asg.assignment_id = p_assignment_id
1085 and l_effective_date between asg.effective_start_date
1086 and asg.effective_end_date;
1087 --
1088 cursor csr_get_future_asg (l_effective_date date) is
1089 select asg.object_version_number
1090 , asg.effective_start_date
1091 , asg.effective_end_date
1092 from per_assignments_f asg
1093 where asg.assignment_id = p_assignment_id
1094 and asg.effective_start_date >= l_effective_date;
1095 --
1096 cursor csr_lock_alu is
1097 select null
1098 from pay_assignment_link_usages_f alu
1099 where alu.assignment_id = p_assignment_id
1100 for update nowait;
1101
1102 -- fix for bug 5841180 starts here
1103 /*
1104 cursor csr_lock_ele is
1105 select null
1106 from pay_element_entries_f ele
1107 where ele.assignment_id = p_assignment_id
1108 for update nowait;
1109 --
1110 cursor csr_lock_eev is
1111 select eev.element_entry_id
1112 from pay_element_entry_values_f eev,
1113 pay_element_entries_f ele
1114 where ele.assignment_id = p_assignment_id
1115 and eev.element_entry_id = ele.element_entry_id
1116 for update nowait;
1117 */
1118
1119 cursor csr_lock_ele(p_effective_date date) is
1120 select null
1121 from pay_element_entries_f ele
1122 where ele.assignment_id = p_assignment_id
1123 and p_effective_date between effective_start_date and effective_end_date
1124 for update nowait;
1125
1126 cursor csr_lock_eev (p_effective_date date) is
1127 select eev.element_entry_id
1128 from pay_element_entry_values_f eev,
1129 pay_element_entries_f ele
1130 where ele.assignment_id = p_assignment_id
1131 and eev.element_entry_id = ele.element_entry_id
1132 and p_effective_date between ele.effective_start_date and ele.effective_end_date
1133 for update nowait;
1134
1135 -- fix for bug 5841180 ends here
1136
1137 cursor csr_lock_asa is
1138 select asa.assignment_action_id
1139 from pay_assignment_actions asa
1140 where asa.assignment_id = p_assignment_id
1141 for update nowait;
1142 --
1143 cursor csr_lock_pyp is
1144 select null
1145 from per_pay_proposals pyp
1146 where pyp.assignment_id = p_assignment_id
1147 for update nowait;
1148 --
1149 -- Start of fix 3202260
1150 cursor csr_pay_proposal is
1151 select pyp.pay_proposal_id, pyp.object_version_number, pyp.business_group_id
1152 from per_pay_proposals pyp
1153 where pyp.assignment_id = p_assignment_id
1154 and pyp.change_date > p_actual_termination_date
1155 order by pyp.change_date desc;
1156 --Added business_group_id for bug 4689950
1157 --
1158 cursor csr_proposal_comp(l_proposal_id number) is
1159 select ppc.component_id, ppc.object_version_number
1160 from per_pay_proposal_components ppc
1161 where ppc.pay_proposal_id = l_proposal_id;
1162 -- End of 3202260
1163
1164 --start of bug 5026287
1165 /*commented the changes for bug 5026287
1166 l_effective_end_date1 per_assignments_f.effective_end_date%TYPE;
1167 l_effective_start_date1 per_assignments_f.effective_start_date%TYPE;
1168 l_object_version_number1 per_assignments_f.object_version_number%TYPE;*/
1169 --end of bug 5026287
1170
1171 begin
1172 hr_utility.set_location('Entering:'|| l_proc, 1);
1173 --
1174 -- Process Logic
1175 --
1176 -- Determine the datetrack mode to use for the assignment table handler
1177 -- update call.
1178 --
1179 --start of bug 5026287
1180
1181 /* open csr_asg_values (p_actual_termination_date);
1182 fetch csr_asg_values into l_object_version_number1
1183 , l_effective_start_date1
1184 , l_effective_end_date1;
1185 if csr_asg_values%notfound then
1186 close csr_asg_values;
1187 -- This should never happen.
1188 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1189 hr_utility.set_message_token('PROCEDURE', l_proc);
1190 hr_utility.set_message_token('STEP','270');
1191 hr_utility.raise_error;
1192 end if;
1193 close csr_asg_values;*/
1194 /* commented the changes made by bug 5026287*/
1195 /*if p_actual_termination_date = l_effective_start_date1 then
1196
1197 per_asg_shd.find_dt_upd_modes
1198 (p_effective_date => p_actual_termination_date
1199 ,p_base_key_value => p_assignment_id
1200 ,p_correction => l_correction
1201 ,p_update => l_update
1202 ,p_update_override => l_update_override
1203 ,p_update_change_insert => l_update_change_insert
1204 );
1205 else
1206 per_asg_shd.find_dt_upd_modes
1207 (p_effective_date => p_actual_termination_date + 1
1208 ,p_base_key_value => p_assignment_id
1209 ,p_correction => l_correction
1210 ,p_update => l_update
1211 ,p_update_override => l_update_override
1212 ,p_update_change_insert => l_update_change_insert
1213 );
1214 end if;*/
1215
1216 per_asg_shd.find_dt_upd_modes
1217 (p_effective_date => p_actual_termination_date + 1
1218 ,p_base_key_value => p_assignment_id
1219 ,p_correction => l_correction
1220 ,p_update => l_update
1221 ,p_update_override => l_update_override
1222 ,p_update_change_insert => l_update_change_insert
1223 );
1224 --end of bug 5026287
1225
1226
1227 hr_utility.set_location(l_proc, 10);
1228 --
1229 if l_update_change_insert then
1230 --
1231 -- This is the case where there is a future dated assignment and
1232 -- we need to insert a record betwen ATD+1 and that future change
1233 -- with a TERM status. We need 'CORRECTION' of future records to
1234 -- have the right status.
1235 --
1236 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1237 l_asg_future_changes_warning := TRUE;
1238 l_object_version_number := p_object_version_number;
1239 l_future_records_flag := TRUE;
1240 hr_utility.set_location(l_proc, 20);
1241 elsif l_update then
1242 --
1243 l_datetrack_mode := 'UPDATE';
1244 l_object_version_number := p_object_version_number;
1245 hr_utility.set_location(l_proc, 30);
1246 --
1247 elsif l_correction then
1248 --
1249 hr_utility.set_location(l_proc, 40);
1250 --
1251 -- We have the OVN for the assignment record which starts on ATD+1
1252 -- so lock this one for termination.
1253 --
1254 l_object_version_number := p_object_version_number;
1255 --
1256 -- Lock the current row as of ATD+1, using correction mode.
1257 -- This validates the object version number passed in and obtains the
1258 -- validation_start_date and validation_end_date.
1259 --
1260 per_asg_shd.lck
1261 (p_effective_date => p_actual_termination_date + 1
1262 ,p_datetrack_mode => 'CORRECTION'
1263 ,p_assignment_id => p_assignment_id
1264 ,p_object_version_number => l_object_version_number
1265 ,p_validation_start_date => l_cor_validation_start_date
1266 ,p_validation_end_date => l_cor_validation_end_date
1267 );
1268 hr_utility.set_location(l_proc, 50);
1269 --
1270 -- Find out if there changes after, the day
1271 -- after the actual termination date
1272 --
1273 l_max_asg_end_date := get_max_asg_fut_change_end_dt
1274 (p_assignment_id => p_assignment_id
1275 ,p_effective_date => p_actual_termination_date + 1
1276 );
1277 hr_utility.set_location(l_proc, 70);
1278 --
1279 if l_max_asg_end_date is not null then
1280 --
1281 l_future_records_flag := TRUE;
1282 hr_utility.set_location(l_proc, 80);
1283 end if;
1284 --
1285 hr_utility.set_location(l_proc, 110);
1286 --
1287 l_datetrack_mode := 'CORRECTION';
1288 l_asg_future_changes_warning := TRUE;
1289 -- For correction the object_version_number has already been derived.
1290 hr_utility.set_location(l_proc, 120);
1291 else
1292 --
1293 -- No other datetrack modes are valid, and so should not occur.
1294 --
1295 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1296 hr_utility.set_message_token('PROCEDURE', l_proc);
1297 hr_utility.set_message_token('STEP','130');
1298 hr_utility.raise_error;
1299 end if;
1300 hr_utility.set_location(l_proc, 140);
1301 --
1302 -- Update employee assignment.
1303 --
1304 per_asg_upd.upd
1305 (p_assignment_id => p_assignment_id
1306 ,p_effective_start_date => l_effective_start_date
1307 ,p_effective_end_date => l_effective_end_date
1308 ,p_business_group_id => l_business_group_id
1309 ,p_assignment_status_type_id => p_assignment_status_type_id
1310 ,p_comment_id => l_comment_id
1311 ,p_payroll_id_updated => l_payroll_id_updated
1312 ,p_other_manager_warning => l_other_manager_warning
1313 ,p_no_managers_warning => l_no_managers_warning
1314 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1315 ,p_validation_start_date => l_validation_start_date
1316 ,p_validation_end_date => l_validation_end_date
1317 ,p_object_version_number => l_object_version_number
1318 ,p_effective_date => p_actual_termination_date + 1
1319 ,p_datetrack_mode => l_datetrack_mode
1320 ,p_validate => FALSE
1321 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1322 );
1323 hr_utility.set_location(l_proc, 150);
1324 --
1325 -- If there are future dated records then we need to process these and set the
1326 -- right assignment status.
1327 --
1328 if l_future_records_flag then
1329 --
1330 -- We have future dated assignment records so set them to a TERM_ASSIGN
1331 -- status.
1332 --
1333 for c_asg_rec in csr_get_future_asg(p_actual_termination_date+2)
1334 loop
1335 per_asg_upd.upd
1336 (p_assignment_id => p_assignment_id
1337 ,p_effective_start_date => l_effective_start_date
1338 ,p_effective_end_date => l_effective_end_date
1339 ,p_business_group_id => l_business_group_id
1340 ,p_assignment_status_type_id => p_assignment_status_type_id
1341 ,p_comment_id => l_comment_id
1342 ,p_payroll_id_updated => l_payroll_id_updated
1343 ,p_other_manager_warning => l_other_manager_warning
1344 ,p_no_managers_warning => l_no_managers_warning
1345 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
1346 ,p_validation_start_date => l_validation_start_date
1347 ,p_validation_end_date => l_validation_end_date
1348 ,p_object_version_number => c_asg_rec.object_version_number
1349 ,p_effective_date => c_asg_rec.effective_start_date
1350 ,p_datetrack_mode => 'CORRECTION'
1351 ,p_validate => FALSE
1352 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1353 );
1354 end loop;
1355 end if;
1356 --
1357 -- Lock the appropriate child rows for this assignment.
1358 --
1359 open csr_lock_alu; -- Locking ladder processing order 1110
1360 close csr_lock_alu;
1361 hr_utility.set_location(l_proc, 160);
1362 --
1363 open csr_lock_asa; -- Locking ladder processing order 1190
1364 close csr_lock_asa;
1365 hr_utility.set_location(l_proc,170);
1366 --
1367
1368 -- Fix for bug 5841180 starts here
1369
1370 /* open csr_lock_ele; -- Locking ladder processing order 1440
1371 close csr_lock_ele;
1372 hr_utility.set_location(l_proc, 180);
1373 --
1374 open csr_lock_eev; -- Locking ladder processing order 1450
1375 close csr_lock_eev;
1376 hr_utility.set_location(l_proc, 190); */
1377
1378 open csr_lock_ele(p_actual_termination_date); -- Locking ladder processing order 1440
1379 close csr_lock_ele;
1380 hr_utility.set_location(l_proc, 180);
1381 --
1382 open csr_lock_eev(p_actual_termination_date); -- Locking ladder processing order 1450
1383 close csr_lock_eev;
1384 hr_utility.set_location(l_proc, 190);
1385
1386 -- Fix for bug 5841180 ends here
1387
1388 open csr_lock_pyp; -- Locking ladder processing order 1630
1389 close csr_lock_pyp;
1390 hr_utility.set_location(l_proc, 200);
1391 --
1392 -- Process any element entries and assignment_link_usages for this
1393 -- assignment.
1394 -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
1395 -- the Forms Application to perform this task, so it will be used here
1396 -- as well. (We require the legislation code.)
1397 --
1398 open csr_get_legislation_code;
1399 fetch csr_get_legislation_code into l_legislation_code;
1400 if csr_get_legislation_code%NOTFOUND then
1401 close csr_get_legislation_code;
1402 -- This should never happen
1403 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1404 hr_utility.set_message_token('PROCEDURE', l_proc);
1405 hr_utility.set_message_token('STEP','210');
1406 hr_utility.raise_error;
1407 end if;
1408 close csr_get_legislation_code;
1409 hr_utility.set_location(l_proc, 220);
1410 --
1411 -- VT 10/07/96 bug #306710 added parameter in a call list
1412 hrempter.terminate_entries_and_alus
1413 (p_assignment_id => p_assignment_id
1414 ,p_actual_term_date => p_actual_termination_date
1415 ,p_last_standard_date => p_last_standard_process_date
1416 ,p_final_process_date => null
1417 ,p_legislation_code => l_legislation_code
1418 ,p_entries_changed_warning => l_entries_changed_warning
1419 --
1420 -- 115.66 (START)
1421 --
1422 ,p_alu_change_warning => l_alu_change_warning
1423 --
1424 -- 115.66 (END)
1425 --
1426 );
1427 --
1428 -- 115.66 (START)
1429 --
1430 p_alu_change_warning := l_alu_change_warning;
1431 --
1432 -- 115.66 (END)
1433 --
1434 --
1435 hr_utility.set_location(l_proc, 230);
1436 --
1437 -- Delete any pay proposals for this assignment that occur after the
1438 -- actual termination date.
1439 --
1440 -- After the delete from per_pay_proposals a warning out parameter is set.
1441 --
1442 -- Start of fix 3202260
1443 for rec_pay_prop in csr_pay_proposal loop
1444 --
1445 hr_utility.set_location(l_proc, 231);
1446 --
1447 for rec_prop_comp in csr_proposal_comp(rec_pay_prop.pay_proposal_id) loop
1448 -- Calling the per_pay_proposal_components row handler to delete the
1449 -- proposal components
1450 --
1451 hr_utility.set_location(l_proc, 232);
1452 --
1453 per_ppc_del.del(p_component_id => rec_prop_comp.component_id,
1454 p_object_version_number => rec_prop_comp.object_version_number,
1455 p_validation_strength => 'WEAK',
1456 p_validate => true);
1457 --
1458 hr_utility.set_location(l_proc, 233);
1459 --
1460 end loop;
1461 /* --
1462 -- Now deleting the salary proposal
1463 --
1464 hr_utility.set_location(l_proc, 234);
1465 --
1466 delete
1467 from per_pay_proposals pyp
1468 where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
1469 --
1470 -- Setting the Warning Out variable
1471 if sql%notfound then
1472 l_pay_proposal_warning := FALSE;
1473 else
1474 l_pay_proposal_warning := TRUE;
1475 end if;
1476 --*/
1477 --Start of fix for bug 4689950
1478 hr_maintain_proposal_api.delete_salary_proposal(
1479 p_pay_proposal_id => rec_pay_prop.pay_proposal_id,
1480 p_business_group_id => rec_pay_prop.business_group_id,
1481 p_object_version_number => rec_pay_prop.object_version_number,
1482 p_validate => NULL,
1483 p_salary_warning => l_pay_proposal_warning
1484 );
1485 l_pay_proposal_warning := TRUE;
1486 --End of fix for bug 4689950
1487 --
1488 hr_utility.set_location(l_proc, 235);
1489 --
1490 end loop;
1491 -- End of fix 3202260
1492 --
1493 hr_utility.set_location(l_proc, 240);
1494 --
1495 if l_datetrack_mode = 'CORRECTION' then
1496 --
1497 -- Leave p_object_version_number set to its existing value, as it will
1498 -- not have changed.
1499 -- Set effective date parameters to the validation start and date values
1500 -- which were returned when the assignment row was locked.
1501 --
1502 p_effective_start_date := l_cor_validation_start_date;
1503 p_effective_end_date := l_cor_validation_end_date;
1504 hr_utility.set_location(l_proc, 250);
1505 else
1506 hr_utility.set_location(l_proc, 260);
1507 --
1508 -- When a different DateTrack mode is used, need to select the current
1509 -- object version number and effective dates. This is because the row
1510 -- as of the actual termination date will have been modified.
1511 --
1512 open csr_asg_values (p_actual_termination_date);
1513 fetch csr_asg_values into p_object_version_number
1514 , p_effective_start_date
1515 , p_effective_end_date;
1516 if csr_asg_values%notfound then
1517 close csr_asg_values;
1518 -- This should never happen.
1519 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520 hr_utility.set_message_token('PROCEDURE', l_proc);
1521 hr_utility.set_message_token('STEP','270');
1522 hr_utility.raise_error;
1523 end if;
1524 close csr_asg_values;
1525 hr_utility.set_location(l_proc, 280);
1526 end if;
1527 --
1528 -- Set other output arguments
1529 --
1530 p_asg_future_changes_warning := l_asg_future_changes_warning;
1531 p_entries_changed_warning := l_entries_changed_warning;
1532 p_pay_proposal_warning := l_pay_proposal_warning;
1533 --
1534 hr_utility.set_location(' Leaving:'||l_proc, 300);
1535 end actual_term_emp_asg_sup;
1536 --
1537 -- ----------------------------------------------------------------------------
1538 -- |---------------------------< delete_first_spp >---------------------------|
1539 -- ----------------------------------------------------------------------------
1540 --
1541 procedure delete_first_spp
1542 (p_effective_date in date
1543 ,p_assignment_id in number
1544 ,p_validation_start_date in date
1545 ,p_validation_end_date in date
1546 ,p_future_spp_warning out nocopy boolean) is
1547
1548 l_effective_end_date date;
1549 l_effective_start_date date;
1550 l_object_version_number number;
1551 l_placement_id number;
1552 l_update number;
1553 l_future_spp_warning boolean;
1554 l_datetrack_mode varchar2(30);
1555
1556 --
1557 -- Check to see if a grade step has been created for assignment
1558 --
1559 cursor csr_grade_step is
1560 select spp.placement_id
1561 from per_spinal_point_placements_f spp
1562 where spp.assignment_id = p_assignment_id
1563 and p_validation_start_date between spp.effective_start_date
1564 and spp.effective_end_date;
1565
1566 begin
1567
1568 --
1569 -- Check that there has been a grade step created for this assignment
1570 --
1571 open csr_grade_step;
1572 fetch csr_grade_step into l_update;
1573 if csr_grade_step%found then
1574
1575 --
1576 -- Get the placement id and object version number for the assignment
1577 --
1578 select placement_id,object_version_number,effective_end_date
1579 into l_placement_id,l_object_version_number,l_effective_end_date
1580 from per_spinal_point_placements_f
1581 where assignment_id = p_assignment_id
1582 and p_validation_start_date between effective_start_date
1583 and effective_end_date;
1584
1585 --
1586 -- Delete next change until the effective end date of the record
1587 -- that was just inserted matches the validation end date
1588 --
1589 loop
1590
1591 l_datetrack_mode := 'DELETE_NEXT_CHANGE';
1592
1593 hr_utility.set_location('l_effective_end_date :'||l_effective_end_date,25);
1594
1595 if l_effective_end_date = p_validation_end_date then
1596 exit;
1597 end if;
1598
1599 hr_sp_placement_api.delete_spp
1600 (p_effective_date => p_validation_start_date
1601 ,p_datetrack_mode => l_datetrack_mode
1602 ,p_placement_id => l_placement_id
1603 ,p_object_version_number => l_object_version_number
1604 ,p_effective_start_date => l_effective_start_date
1605 ,p_effective_end_date => l_effective_end_date);
1606
1607 select effective_end_date
1608 into l_effective_end_date
1609 from per_spinal_point_placements_f
1610 where placement_id = l_placement_id
1611 and p_validation_start_date between effective_start_date
1612 and effective_end_date;
1613
1614 end loop;
1615
1616 --
1617 -- Now that there is only one record for the period, use dml to remove the first record
1618 --
1619 delete from per_spinal_point_placements_f
1620 where assignment_id = p_assignment_id
1621 and placement_id = l_placement_id
1622 and effective_start_date = p_validation_start_date;
1623
1624 l_future_spp_warning := TRUE;
1625 p_future_spp_warning := l_future_spp_warning;
1626
1627 end if;
1628
1629 end delete_first_spp;
1630
1631 -- ----------------------------------------------------------------------------
1632 -- |------------------------< create_default_emp_asg >------------------------|
1633 -- ----------------------------------------------------------------------------
1634 --
1635 procedure create_default_emp_asg
1636 (p_effective_date in date
1637 ,p_person_id in number
1638 ,p_business_group_id in number
1639 ,p_period_of_service_id in number
1640 ,p_assignment_id out nocopy number
1641 ,p_object_version_number out nocopy number
1642 ,p_assignment_sequence out nocopy number
1643 ,p_assignment_number out nocopy varchar2
1644 ) is
1645 --
1646 -- Declare cursors and local variables
1647 --
1648 l_location_id per_business_groups.location_id%TYPE;
1649 l_time_normal_finish per_business_groups.default_end_time%TYPE;
1650 l_time_normal_start per_business_groups.default_start_time%TYPE;
1651 l_normal_hours number;
1652 l_frequency per_business_groups.frequency%TYPE;
1653 l_legislation_code per_business_groups.legislation_code%TYPE;
1654 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1655 l_effective_end_date per_assignments_f.effective_start_date%TYPE;
1656 l_assignment_number per_assignments_f.assignment_number%TYPE;
1657 l_comment_id per_assignments_f.comment_id%TYPE;
1658 l_other_manager_warning boolean;
1659 l_proc varchar2(72):=g_package||'create_default_emp_asg';
1660 --
1661 cursor csr_get_default_details is
1662 select bus.location_id
1663 , bus.default_end_time
1664 , bus.default_start_time
1665 , fnd_number.canonical_to_number(bus.working_hours)
1666 , bus.frequency
1667 , bus.legislation_code
1668 from per_business_groups bus
1669 where bus.business_group_id = p_business_group_id;
1670 --
1671 begin
1672 hr_utility.set_location('Entering:'|| l_proc, 1);
1673 --
1674 l_assignment_number := null;
1675 --
1676 -- Process Logic
1677 --
1678 -- Get default details.
1679 --
1680 hr_api.mandatory_arg_error
1681 (p_api_name => l_proc,
1682 p_argument => 'business_group_id',
1683 p_argument_value => p_business_group_id);
1684 --
1685 open csr_get_default_details;
1686 fetch csr_get_default_details
1687 into l_location_id
1688 , l_time_normal_finish
1689 , l_time_normal_start
1690 , l_normal_hours
1691 , l_frequency
1692 , l_legislation_code;
1693 --
1694 if csr_get_default_details%NOTFOUND then
1695 --
1696 hr_utility.set_location(l_proc, 10);
1697 --
1698 close csr_get_default_details;
1699 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
1700 hr_utility.raise_error;
1701 end if;
1702 close csr_get_default_details;
1703 --
1704 hr_utility.set_location(l_proc, 20);
1705 --
1706 -- Create employee assignment.
1707 --
1708 hr_assignment_internal.create_emp_asg
1709 (p_effective_date => p_effective_date
1710 ,p_legislation_code => l_legislation_code
1711 ,p_business_group_id => p_business_group_id
1712 ,p_person_id => p_person_id
1713 ,p_organization_id => p_business_group_id
1714 ,p_primary_flag => 'Y'
1715 ,p_period_of_service_id => p_period_of_service_id
1716 ,p_location_id => l_location_id
1717 ,p_people_group_id => null
1718 ,p_assignment_number => l_assignment_number
1719 ,p_frequency => l_frequency
1720 ,p_normal_hours => l_normal_hours
1721 ,p_time_normal_finish => l_time_normal_finish
1722 ,p_time_normal_start => l_time_normal_start
1723 ,p_assignment_id => p_assignment_id
1724 ,p_object_version_number => p_object_version_number
1725 ,p_effective_start_date => l_effective_start_date
1726 ,p_effective_end_date => l_effective_end_date
1727 ,p_assignment_sequence => p_assignment_sequence
1728 ,p_comment_id => l_comment_id
1729 ,p_other_manager_warning => l_other_manager_warning
1730 ,p_validate_df_flex => false
1731 );
1732 --
1733 hr_utility.set_location(l_proc, 30);
1734 --
1735 -- Set remaining output arguments
1736 --
1737 p_assignment_number := l_assignment_number;
1738 --
1739 hr_utility.set_location(' Leaving:'||l_proc, 100);
1740 end create_default_emp_asg;
1741
1742 --
1743 -- ----------------------------------------------------------------------------
1744 -- |----------------------------< create_emp_asg >---------------------------|
1745 -- ----------------------------------------------------------------------------
1746 --
1747 procedure create_emp_asg
1748 (p_effective_date in date
1749 ,p_legislation_code in varchar2
1750 ,p_business_group_id in number
1751 ,p_person_id in number
1752 ,p_organization_id in number
1753 ,p_primary_flag in varchar2
1754 ,p_period_of_service_id in number
1755 ,p_grade_id in number default null
1756 ,p_position_id in number default null
1757 ,p_job_id in number default null
1758 ,p_assignment_status_type_id in number default null
1759 ,p_payroll_id in number default null
1760 ,p_location_id in number default null
1761 ,p_supervisor_id in number default null
1762 ,p_special_ceiling_step_id in number default null
1763 ,p_people_group_id in number default null
1764 ,p_soft_coding_keyflex_id in number default null
1765 ,p_pay_basis_id in number default null
1766 ,p_assignment_number in out nocopy varchar2
1767 ,p_change_reason in varchar2 default null
1768 ,p_comments in varchar2 default null
1769 ,p_date_probation_end in date default null
1770 ,p_default_code_comb_id in number default null
1771 ,p_employment_category in varchar2 default null
1772 ,p_frequency in varchar2 default null
1773 ,p_internal_address_line in varchar2 default null
1774 ,p_manager_flag in varchar2 default null
1775 ,p_normal_hours in number default null
1776 ,p_perf_review_period in number default null
1777 ,p_perf_review_period_frequency in varchar2 default null
1778 ,p_probation_period in number default null
1779 ,p_probation_unit in varchar2 default null
1780 ,p_sal_review_period in number default null
1781 ,p_sal_review_period_frequency in varchar2 default null
1782 ,p_set_of_books_id in number default null
1783 ,p_source_type in varchar2 default null
1784 ,p_time_normal_finish in varchar2 default null
1785 ,p_time_normal_start in varchar2 default null
1786 ,p_bargaining_unit_code in varchar2 default null
1787 ,p_labour_union_member_flag in varchar2 default 'N'
1788 ,p_hourly_salaried_code in varchar2 default null
1789 ,p_ass_attribute_category in varchar2 default null
1790 ,p_ass_attribute1 in varchar2 default null
1791 ,p_ass_attribute2 in varchar2 default null
1792 ,p_ass_attribute3 in varchar2 default null
1793 ,p_ass_attribute4 in varchar2 default null
1794 ,p_ass_attribute5 in varchar2 default null
1795 ,p_ass_attribute6 in varchar2 default null
1796 ,p_ass_attribute7 in varchar2 default null
1797 ,p_ass_attribute8 in varchar2 default null
1798 ,p_ass_attribute9 in varchar2 default null
1799 ,p_ass_attribute10 in varchar2 default null
1800 ,p_ass_attribute11 in varchar2 default null
1801 ,p_ass_attribute12 in varchar2 default null
1802 ,p_ass_attribute13 in varchar2 default null
1803 ,p_ass_attribute14 in varchar2 default null
1804 ,p_ass_attribute15 in varchar2 default null
1805 ,p_ass_attribute16 in varchar2 default null
1806 ,p_ass_attribute17 in varchar2 default null
1807 ,p_ass_attribute18 in varchar2 default null
1808 ,p_ass_attribute19 in varchar2 default null
1809 ,p_ass_attribute20 in varchar2 default null
1810 ,p_ass_attribute21 in varchar2 default null
1811 ,p_ass_attribute22 in varchar2 default null
1812 ,p_ass_attribute23 in varchar2 default null
1813 ,p_ass_attribute24 in varchar2 default null
1814 ,p_ass_attribute25 in varchar2 default null
1815 ,p_ass_attribute26 in varchar2 default null
1816 ,p_ass_attribute27 in varchar2 default null
1817 ,p_ass_attribute28 in varchar2 default null
1818 ,p_ass_attribute29 in varchar2 default null
1819 ,p_ass_attribute30 in varchar2 default null
1820 ,p_title in varchar2 default null
1821 ,p_contract_id in number default null
1822 ,p_establishment_id in number default null
1823 ,p_collective_agreement_id in number default null
1824 ,p_cagr_id_flex_num in number default null
1825 ,p_cagr_grade_def_id in number default null
1826 ,p_notice_period in number default null
1827 ,p_notice_period_uom in varchar2 default null
1828 ,p_employee_category in varchar2 default null
1829 ,p_work_at_home in varchar2 default null
1830 ,p_job_post_source_name in varchar2 default null
1831 ,p_validate_df_flex in boolean default true
1832 ,p_grade_ladder_pgm_id in number default null
1833 ,p_supervisor_assignment_id in number default null
1834 ,p_assignment_id out nocopy number
1835 ,p_object_version_number out nocopy number
1836 ,p_effective_start_date out nocopy date
1837 ,p_effective_end_date out nocopy date
1838 ,p_assignment_sequence out nocopy number
1839 ,p_comment_id out nocopy number
1840 ,p_other_manager_warning out nocopy boolean
1841 ) is
1842 --
1843 -- Declare cursors and local variables
1844 --
1845 l_assignment_id per_assignments_f.assignment_id%TYPE;
1846 l_assignment_sequence per_assignments_f.assignment_sequence%TYPE;
1847 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
1848 l_entries_changed varchar2(1);
1849 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
1850 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
1851 l_proc varchar2(72) := g_package||'create_emp_asg';
1852 l_hourly_salaried_warning boolean;
1853 l_object_version_number per_assignments_f.object_version_number%TYPE;
1854
1855 --
1856 begin
1857 hr_utility.set_location('Entering:'|| l_proc, 1);
1858 --
1859 l_assignment_status_type_id := p_assignment_status_type_id;
1860
1861 hr_assignment_internal.create_emp_asg
1862 (p_effective_date => p_effective_date
1863 ,p_legislation_code => p_legislation_code
1864 ,p_business_group_id => p_business_group_id
1865 ,p_person_id => p_person_id
1866 ,p_organization_id => p_organization_id
1867 ,p_primary_flag => p_primary_flag
1868 ,p_period_of_service_id => p_period_of_service_id
1869 ,p_grade_id => p_grade_id
1870 ,p_position_id => p_position_id
1871 ,p_job_id => p_job_id
1872 ,p_assignment_status_type_id => p_assignment_status_type_id
1873 ,p_payroll_id => p_payroll_id
1874 ,p_location_id => p_location_id
1875 ,p_supervisor_id => p_supervisor_id
1876 ,p_special_ceiling_step_id => p_special_ceiling_step_id
1877 ,p_people_group_id => p_people_group_id
1878 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
1879 ,p_pay_basis_id => p_pay_basis_id
1880 ,p_assignment_number => p_assignment_number
1881 ,p_change_reason => p_change_reason
1882 ,p_comments => p_comments
1883 ,p_date_probation_end => p_date_probation_end
1884 ,p_default_code_comb_id => p_default_code_comb_id
1885 ,p_employment_category => p_employment_category
1886 ,p_frequency => p_frequency
1887 ,p_internal_address_line => p_internal_address_line
1888 ,p_manager_flag => p_manager_flag
1889 ,p_normal_hours => p_normal_hours
1890 ,p_perf_review_period => p_perf_review_period
1891 ,p_perf_review_period_frequency => p_perf_review_period_frequency
1892 ,p_probation_period => p_probation_period
1893 ,p_probation_unit => p_probation_unit
1894 ,p_sal_review_period => p_sal_review_period
1895 ,p_sal_review_period_frequency => p_sal_review_period_frequency
1896 ,p_set_of_books_id => p_set_of_books_id
1897 ,p_source_type => p_source_type
1898 ,p_time_normal_finish => p_time_normal_finish
1899 ,p_time_normal_start => p_time_normal_start
1900 ,p_bargaining_unit_code => p_bargaining_unit_code
1901 ,p_labour_union_member_flag => p_labour_union_member_flag
1902 ,p_hourly_salaried_code => p_hourly_salaried_code
1903 ,p_ass_attribute_category => p_ass_attribute_category
1904 ,p_ass_attribute1 => p_ass_attribute1
1905 ,p_ass_attribute2 => p_ass_attribute2
1906 ,p_ass_attribute3 => p_ass_attribute3
1907 ,p_ass_attribute4 => p_ass_attribute4
1908 ,p_ass_attribute5 => p_ass_attribute5
1909 ,p_ass_attribute6 => p_ass_attribute6
1910 ,p_ass_attribute7 => p_ass_attribute7
1911 ,p_ass_attribute8 => p_ass_attribute8
1912 ,p_ass_attribute9 => p_ass_attribute9
1913 ,p_ass_attribute10 => p_ass_attribute10
1914 ,p_ass_attribute11 => p_ass_attribute11
1915 ,p_ass_attribute12 => p_ass_attribute12
1916 ,p_ass_attribute13 => p_ass_attribute13
1917 ,p_ass_attribute14 => p_ass_attribute14
1918 ,p_ass_attribute15 => p_ass_attribute15
1919 ,p_ass_attribute16 => p_ass_attribute16
1920 ,p_ass_attribute17 => p_ass_attribute17
1921 ,p_ass_attribute18 => p_ass_attribute18
1922 ,p_ass_attribute19 => p_ass_attribute19
1923 ,p_ass_attribute20 => p_ass_attribute20
1924 ,p_ass_attribute21 => p_ass_attribute21
1925 ,p_ass_attribute22 => p_ass_attribute22
1926 ,p_ass_attribute23 => p_ass_attribute23
1927 ,p_ass_attribute24 => p_ass_attribute24
1928 ,p_ass_attribute25 => p_ass_attribute25
1929 ,p_ass_attribute26 => p_ass_attribute26
1930 ,p_ass_attribute27 => p_ass_attribute27
1931 ,p_ass_attribute28 => p_ass_attribute28
1932 ,p_ass_attribute29 => p_ass_attribute29
1933 ,p_ass_attribute30 => p_ass_attribute30
1934 ,p_notice_period => p_notice_period
1935 ,p_notice_period_uom => p_notice_period_uom
1936 ,p_employee_category => p_employee_category
1937 ,p_work_at_home => p_work_at_home
1938 ,p_job_post_source_name => p_job_post_source_name
1939 ,p_title => p_title
1940 ,p_contract_id => p_contract_id
1941 ,p_establishment_id => p_establishment_id
1942 ,p_collective_agreement_id => p_collective_agreement_id
1943 ,p_cagr_id_flex_num => p_cagr_id_flex_num
1944 ,p_cagr_grade_def_id => p_cagr_grade_def_id
1945 ,p_assignment_id => l_assignment_id
1946 ,p_object_version_number => l_object_version_number
1947 ,p_effective_start_date => l_effective_start_date
1948 ,p_effective_end_date => l_effective_end_date
1949 ,p_assignment_sequence => l_assignment_sequence
1950 ,p_comment_id => p_comment_id
1951 ,p_other_manager_warning => p_other_manager_warning
1952 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1953 ,p_validate_df_flex => p_validate_df_flex --Added to fix the bug 2354616
1954 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
1955 ,p_supervisor_assignment_id => p_supervisor_assignment_id
1956 );
1957
1958
1959 p_assignment_id := l_assignment_id;
1960 p_effective_start_date := l_effective_start_date;
1961 p_effective_end_date := l_effective_end_date;
1962 p_object_version_number := l_object_version_number ;
1963 --
1964 hr_utility.set_location(' Leaving:'||l_proc, 100);
1965 end create_emp_asg;
1966
1967 --
1968 -- ----------------------------------------------------------------------------
1969 -- |----------------------------< create_emp_asg >------ OVERLOADED----------|
1970 -- ----------------------------------------------------------------------------
1971 --
1972 procedure create_emp_asg
1973 (p_effective_date in date
1974 ,p_legislation_code in varchar2
1975 ,p_business_group_id in number
1976 ,p_person_id in number
1977 ,p_organization_id in number
1978 ,p_primary_flag in varchar2
1979 ,p_period_of_service_id in number
1980 ,p_grade_id in number default null
1981 ,p_position_id in number default null
1982 ,p_job_id in number default null
1983 ,p_assignment_status_type_id in number default null
1984 ,p_payroll_id in number default null
1985 ,p_location_id in number default null
1986 ,p_supervisor_id in number default null
1987 ,p_special_ceiling_step_id in number default null
1988 ,p_people_group_id in number default null
1989 ,p_soft_coding_keyflex_id in number default null
1990 ,p_pay_basis_id in number default null
1991 ,p_assignment_number in out nocopy varchar2
1992 ,p_change_reason in varchar2 default null
1993 ,p_comments in varchar2 default null
1994 ,p_date_probation_end in date default null
1995 ,p_default_code_comb_id in number default null
1996 ,p_employment_category in varchar2 default null
1997 ,p_frequency in varchar2 default null
1998 ,p_internal_address_line in varchar2 default null
1999 ,p_manager_flag in varchar2 default null
2000 ,p_normal_hours in number default null
2001 ,p_perf_review_period in number default null
2002 ,p_perf_review_period_frequency in varchar2 default null
2003 ,p_probation_period in number default null
2004 ,p_probation_unit in varchar2 default null
2005 ,p_sal_review_period in number default null
2006 ,p_sal_review_period_frequency in varchar2 default null
2007 ,p_set_of_books_id in number default null
2008 ,p_source_type in varchar2 default null
2009 ,p_time_normal_finish in varchar2 default null
2010 ,p_time_normal_start in varchar2 default null
2011 ,p_bargaining_unit_code in varchar2 default null
2012 ,p_labour_union_member_flag in varchar2 default 'N'
2013 ,p_hourly_salaried_code in varchar2 default null
2014 ,p_ass_attribute_category in varchar2 default null
2015 ,p_ass_attribute1 in varchar2 default null
2016 ,p_ass_attribute2 in varchar2 default null
2017 ,p_ass_attribute3 in varchar2 default null
2018 ,p_ass_attribute4 in varchar2 default null
2019 ,p_ass_attribute5 in varchar2 default null
2020 ,p_ass_attribute6 in varchar2 default null
2021 ,p_ass_attribute7 in varchar2 default null
2022 ,p_ass_attribute8 in varchar2 default null
2023 ,p_ass_attribute9 in varchar2 default null
2024 ,p_ass_attribute10 in varchar2 default null
2025 ,p_ass_attribute11 in varchar2 default null
2026 ,p_ass_attribute12 in varchar2 default null
2027 ,p_ass_attribute13 in varchar2 default null
2028 ,p_ass_attribute14 in varchar2 default null
2029 ,p_ass_attribute15 in varchar2 default null
2030 ,p_ass_attribute16 in varchar2 default null
2031 ,p_ass_attribute17 in varchar2 default null
2032 ,p_ass_attribute18 in varchar2 default null
2033 ,p_ass_attribute19 in varchar2 default null
2034 ,p_ass_attribute20 in varchar2 default null
2035 ,p_ass_attribute21 in varchar2 default null
2036 ,p_ass_attribute22 in varchar2 default null
2037 ,p_ass_attribute23 in varchar2 default null
2038 ,p_ass_attribute24 in varchar2 default null
2039 ,p_ass_attribute25 in varchar2 default null
2040 ,p_ass_attribute26 in varchar2 default null
2041 ,p_ass_attribute27 in varchar2 default null
2042 ,p_ass_attribute28 in varchar2 default null
2043 ,p_ass_attribute29 in varchar2 default null
2044 ,p_ass_attribute30 in varchar2 default null
2045 ,p_title in varchar2 default null
2046 ,p_contract_id in number default null
2047 ,p_establishment_id in number default null
2048 ,p_collective_agreement_id in number default null
2049 ,p_cagr_id_flex_num in number default null
2050 ,p_cagr_grade_def_id in number default null
2051 ,p_notice_period in number default null
2052 ,p_notice_period_uom in varchar2 default null
2053 ,p_employee_category in varchar2 default null
2054 ,p_work_at_home in varchar2 default null
2055 ,p_job_post_source_name in varchar2 default null
2056 ,p_validate_df_flex in boolean default true
2057 ,p_grade_ladder_pgm_id in number default null
2058 ,p_supervisor_assignment_id in number default null
2059 ,p_assignment_id out nocopy number
2060 ,p_object_version_number out nocopy number
2061 ,p_effective_start_date out nocopy date
2062 ,p_effective_end_date out nocopy date
2063 ,p_assignment_sequence out nocopy number
2064 ,p_comment_id out nocopy number
2065 ,p_other_manager_warning out nocopy boolean
2066 ,p_hourly_salaried_warning out nocopy boolean
2067 ) is
2068 --
2069 -- Declare cursors and local variables
2070 --
2071 l_assignment_id per_assignments_f.assignment_id%TYPE;
2072 l_assignment_sequence per_assignments_f.assignment_sequence%TYPE;
2073 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
2074 l_entries_changed varchar2(1);
2075 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
2076 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
2077 l_proc varchar2(72) := g_package||'create_emp_asg';
2078 l_labour_union_member_flag per_assignments_f.labour_union_member_flag%TYPE;
2079 l_work_at_home per_all_assignments_f.work_at_home%TYPE; --FIX FOR BUG 9162187
2080 --
2081 begin
2082 hr_utility.set_location('Entering:'|| l_proc, 1);
2083 --
2084 l_assignment_status_type_id := p_assignment_status_type_id;
2085 --
2086 -- fix for bug 4550165 starts here.
2087 if p_legislation_code = 'DE' then
2088 l_labour_union_member_flag := null;
2089 else
2090 l_labour_union_member_flag := p_labour_union_member_flag;
2091 end if;
2092 -- fix for bug 4550165 ends here.
2093
2094 -- Validation in addition to Table Handlers
2095 --
2096 -- None required.
2097 --
2098 -- Process Logic
2099 --
2100 -- If p_assignment_status_type_id is null then derive it's default value,
2101 -- otherwise validate it.
2102 --
2103 l_work_at_home := nvl(p_work_at_home,'N'); --FIX FOR BUG 9162187
2104
2105 per_asg_bus1.chk_assignment_status_type
2106 (p_assignment_status_type_id => l_assignment_status_type_id
2107 ,p_business_group_id => p_business_group_id
2108 ,p_legislation_code => p_legislation_code
2109 ,p_expected_system_status => 'ACTIVE_ASSIGN'
2110 );
2111 --
2112 hr_utility.set_location(l_proc, 10);
2113 --
2114 -- Insert per_assignments_f row.
2115 --
2116 per_asg_ins.ins
2117 (p_assignment_id => l_assignment_id
2118 ,p_effective_start_date => l_effective_start_date
2119 ,p_effective_end_date => l_effective_end_date
2120 ,p_business_group_id => p_business_group_id
2121 ,p_grade_id => p_grade_id
2122 ,p_position_id => p_position_id
2123 ,p_job_id => p_job_id
2124 ,p_assignment_status_type_id => l_assignment_status_type_id
2125 ,p_payroll_id => p_payroll_id
2126 ,p_location_id => p_location_id
2127 ,p_supervisor_id => p_supervisor_id
2128 ,p_special_ceiling_step_id => p_special_ceiling_step_id
2129 ,p_person_id => p_person_id
2130 ,p_organization_id => p_organization_id
2131 ,p_people_group_id => p_people_group_id
2132 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
2133 ,p_pay_basis_id => p_pay_basis_id
2134 ,p_assignment_sequence => p_assignment_sequence
2135 ,p_assignment_type => 'E'
2136 ,p_primary_flag => p_primary_flag
2137 ,p_assignment_number => p_assignment_number
2138 ,p_change_reason => p_change_reason
2139 ,p_comment_id => p_comment_id
2140 ,p_comments => p_comments
2141 ,p_date_probation_end => p_date_probation_end
2142 ,p_default_code_comb_id => p_default_code_comb_id
2143 ,p_employment_category => p_employment_category
2144 ,p_frequency => p_frequency
2145 ,p_internal_address_line => p_internal_address_line
2146 ,p_manager_flag => p_manager_flag
2147 ,p_normal_hours => p_normal_hours
2148 ,p_perf_review_period => p_perf_review_period
2149 ,p_perf_review_period_frequency => p_perf_review_period_frequency
2150 ,p_period_of_service_id => p_period_of_service_id
2151 ,p_probation_period => p_probation_period
2152 ,p_probation_unit => p_probation_unit
2153 ,p_sal_review_period => p_sal_review_period
2154 ,p_sal_review_period_frequency => p_sal_review_period_frequency
2155 ,p_set_of_books_id => p_set_of_books_id
2156 ,p_source_type => p_source_type
2157 ,p_time_normal_finish => p_time_normal_finish
2158 ,p_time_normal_start => p_time_normal_start
2159 ,p_bargaining_unit_code => p_bargaining_unit_code
2160 ,p_labour_union_member_flag => l_labour_union_member_flag -- fix for bug 4550165.
2161 ,p_hourly_salaried_code => p_hourly_salaried_code
2162 ,p_ass_attribute_category => p_ass_attribute_category
2163 ,p_ass_attribute1 => p_ass_attribute1
2164 ,p_ass_attribute2 => p_ass_attribute2
2165 ,p_ass_attribute3 => p_ass_attribute3
2166 ,p_ass_attribute4 => p_ass_attribute4
2167 ,p_ass_attribute5 => p_ass_attribute5
2168 ,p_ass_attribute6 => p_ass_attribute6
2169 ,p_ass_attribute7 => p_ass_attribute7
2170 ,p_ass_attribute8 => p_ass_attribute8
2171 ,p_ass_attribute9 => p_ass_attribute9
2172 ,p_ass_attribute10 => p_ass_attribute10
2173 ,p_ass_attribute11 => p_ass_attribute11
2174 ,p_ass_attribute12 => p_ass_attribute12
2175 ,p_ass_attribute13 => p_ass_attribute13
2176 ,p_ass_attribute14 => p_ass_attribute14
2177 ,p_ass_attribute15 => p_ass_attribute15
2178 ,p_ass_attribute16 => p_ass_attribute16
2179 ,p_ass_attribute17 => p_ass_attribute17
2180 ,p_ass_attribute18 => p_ass_attribute18
2181 ,p_ass_attribute19 => p_ass_attribute19
2182 ,p_ass_attribute20 => p_ass_attribute20
2183 ,p_ass_attribute21 => p_ass_attribute21
2184 ,p_ass_attribute22 => p_ass_attribute22
2185 ,p_ass_attribute23 => p_ass_attribute23
2186 ,p_ass_attribute24 => p_ass_attribute24
2187 ,p_ass_attribute25 => p_ass_attribute25
2188 ,p_ass_attribute26 => p_ass_attribute26
2189 ,p_ass_attribute27 => p_ass_attribute27
2190 ,p_ass_attribute28 => p_ass_attribute28
2191 ,p_ass_attribute29 => p_ass_attribute29
2192 ,p_ass_attribute30 => p_ass_attribute30
2193 ,p_title => p_title
2194 ,p_contract_id => p_contract_id
2195 ,p_establishment_id => p_establishment_id
2196 ,p_collective_agreement_id => p_collective_agreement_id
2197 ,p_cagr_id_flex_num => p_cagr_id_flex_num
2198 ,p_cagr_grade_def_id => p_cagr_grade_def_id
2199 ,p_notice_period => p_notice_period
2200 ,p_notice_period_uom => p_notice_period_uom
2201 ,p_employee_category => p_employee_category
2202 ,p_work_at_home => l_work_at_home --FIX FOR BUG 9162187
2203 ,p_job_post_source_name => p_job_post_source_name
2204 ,p_other_manager_warning => p_other_manager_warning
2205 ,p_object_version_number => p_object_version_number
2206 ,p_effective_date => p_effective_date
2207 ,p_validate => FALSE
2208 ,p_validate_df_flex => p_validate_df_flex
2209 ,p_hourly_salaried_warning => p_hourly_salaried_warning
2210 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
2211 ,p_supervisor_assignment_id => p_supervisor_assignment_id
2212 );
2213 --
2214 hr_utility.set_location(l_proc, 20);
2215 --
2216 -- Create standard element entries for this assignment.
2217 --
2218 hrentmnt.maintain_entries_asg
2219 (p_assignment_id => l_assignment_id
2220 ,p_old_payroll_id => null
2221 ,p_new_payroll_id => null
2222 ,p_business_group_id => p_business_group_id
2223 ,p_operation => 'ASG_CRITERIA'
2224 ,p_actual_term_date => null
2225 ,p_last_standard_date => null
2226 ,p_final_process_date => null
2227 ,p_dt_mode => 'INSERT'
2228 ,p_validation_start_date => l_effective_start_date
2229 ,p_validation_end_date => l_effective_end_date
2230 ,p_entries_changed => l_entries_changed
2231 );
2232 --
2233 hr_utility.set_location(l_proc, 30);
2234 --
2235 -- Create budget values for this assignment.
2236 -- 16-APR-1998 Change to include effective dates. SASmith
2237 --
2238 hr_assignment.load_budget_values
2239 (p_assignment_id => l_assignment_id
2240 ,p_business_group_id => p_business_group_id
2241 ,p_userid => null
2242 ,p_login => null
2243 ,p_effective_start_date => l_effective_start_date
2244 ,p_effective_end_date => l_effective_end_date
2245 );
2246 --
2247 hr_assignment.load_assignment_allocation
2248 (p_assignment_id => l_assignment_id
2249 ,p_business_group_id => p_business_group_id
2250 ,p_effective_date =>l_effective_start_date
2251 ,p_position_id => p_position_id);
2252 --
2253 -- Set all output arguments
2254 --
2255 p_assignment_id := l_assignment_id;
2256 p_effective_start_date := l_effective_start_date;
2257 p_effective_end_date := l_effective_end_date;
2258 --
2259 hr_utility.set_location(' Leaving:'||l_proc, 100);
2260 end create_emp_asg;
2261
2262 --
2263 -- ----------------------------------------------------------------------------
2264 -- |-----------------------< final_process_emp_asg_sup >----------------------|
2265 -- ----------------------------------------------------------------------------
2266 --
2267 procedure final_process_emp_asg_sup
2268 (p_assignment_id in number
2269 ,p_object_version_number in out nocopy number
2270 ,p_final_process_date in date
2271 ,p_actual_termination_date in date
2272 ,p_effective_start_date out nocopy date
2273 ,p_effective_end_date out nocopy date
2274 ,p_org_now_no_manager_warning out nocopy boolean
2275 ,p_asg_future_changes_warning out nocopy boolean
2276 ,p_entries_changed_warning out nocopy varchar2
2277 ) is
2278 --
2279 -- Declare cursors and local variables
2280 --
2281 -- Out variables
2282 --
2283 l_asg_future_changes_warning boolean := FALSE;
2284 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
2285 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
2286 l_entries_changed_warning varchar2(1) := 'N';
2287 --
2288 -- 115.66 (START)
2289 --
2290 l_alu_change_warning varchar2(1) := 'N';
2291 --
2292 -- 115.66 (END)
2293 --
2294 l_object_version_number per_assignments_f.object_version_number%TYPE;
2295 l_org_now_no_manager_warning boolean := FALSE;
2296 --surendra
2297 --
2298 l_loc_change_tax_issues boolean;
2299 l_delete_asg_budgets boolean;
2300 l_element_salary_warning boolean;
2301 l_element_entries_warning boolean;
2302 l_spp_warning boolean;
2303 l_cost_warning boolean;
2304 l_life_events_exists boolean;
2305 --
2306 l_business_group_id per_assignments_f.business_group_id%TYPE;
2307 l_legislation_code per_business_groups.legislation_code%TYPE;
2308 l_max_asg_end_date per_assignments_f.effective_end_date%TYPE;
2309 l_proc varchar2(72) :=
2310 g_package || 'final_process_emp_asg_sup';
2311 l_validation_start_date per_assignments_f.effective_start_date%TYPE;
2312 l_validation_end_date per_assignments_f.effective_end_date%TYPE;
2313 l_status varchar2(2);
2314 --
2315 --
2316 cursor csr_get_busgrp_legislation is
2317 select pbg.business_group_id, pbg.legislation_code
2318 from per_business_groups_perf pbg
2319 where pbg.business_group_id = (select distinct asg.business_group_id from
2320 per_assignments_f asg
2321 where asg.assignment_id = p_assignment_id);
2322 --
2323 --
2324 cursor csr_lock_csa is
2325 select null
2326 from pay_cost_allocations_f csa
2327 where csa.assignment_id = p_assignment_id
2328 for update nowait;
2329 --
2330 cursor csr_lock_alu is
2331 select null
2332 from pay_assignment_link_usages_f alu
2333 where alu.assignment_id = p_assignment_id
2334 for update nowait;
2335 --
2336 -- Fix for bug 5841180 starts here
2337 /* cursor csr_lock_ele is
2338 select null
2339 from pay_element_entries_f ele
2340 where ele.assignment_id = p_assignment_id
2341 for update nowait;
2342 --
2343 cursor csr_lock_eev is
2344 select eev.element_entry_id
2345 from pay_element_entry_values_f eev,
2346 pay_element_entries_f ele
2347 where ele.assignment_id = p_assignment_id
2348 and eev.element_entry_id = ele.element_entry_id
2349 for update nowait; */
2350
2351
2352 cursor csr_lock_ele(p_effective_date date) is
2353 select null
2354 from pay_element_entries_f ele
2355 where ele.assignment_id = p_assignment_id
2356 and p_effective_date between effective_start_date and effective_end_date
2357 for update nowait;
2358
2359 cursor csr_lock_eev(p_effective_date date) is
2360 select eev.element_entry_id
2361 from pay_element_entry_values_f eev,
2362 pay_element_entries_f ele
2363 where ele.assignment_id = p_assignment_id
2364 and eev.element_entry_id = ele.element_entry_id
2365 and p_effective_date between ele.effective_start_date and ele.effective_end_date
2366 for update nowait;
2367
2368 -- Fix for bug 5841180 ends here
2369 --
2370 cursor csr_lock_spp is
2371 select null
2372 from per_spinal_point_placements_f spp
2373 where spp.assignment_id = p_assignment_id
2374 for update nowait;
2375 --
2376 cursor csr_lock_ppm is
2377 select null
2378 from pay_personal_payment_methods_f ppm
2379 where ppm.assignment_id = p_assignment_id
2380 for update nowait;
2381 --
2382 cursor csr_lock_sas is
2383 select null
2384 from per_secondary_ass_statuses sas
2385 where sas.assignment_id = p_assignment_id
2386 for update nowait;
2387 --
2388 cursor csr_lock_pyp is
2389 select null
2390 from per_pay_proposals pyp
2391 where pyp.assignment_id = p_assignment_id
2392 for update nowait;
2393 --
2394 -- Start of fix for Bug 2796523
2395 cursor csr_zap_ppm is
2396 select personal_payment_method_id,object_version_number,effective_start_date
2397 from pay_personal_payment_methods_f
2398 where assignment_id = p_assignment_id
2399 and effective_start_date > p_final_process_date;
2400 -- End of fix for Bug 2796523
2401 --
2402 cursor csr_dt_del_ppm is
2403 select personal_payment_method_id,object_version_number
2404 from pay_personal_payment_methods_f
2405 where assignment_id = p_assignment_id
2406 and p_final_process_date between effective_start_date
2407 and effective_end_date;
2408 cursor csr_lock_asa is
2409 select asa.assignment_action_id
2410 from pay_assignment_actions asa
2411 where asa.assignment_id = p_assignment_id
2412 for update nowait;
2413 --
2414 --
2415 cursor csr_lock_abv is
2416 select assignment_budget_value_id
2417 from per_assignment_budget_values_f
2418 where assignment_id = p_assignment_id
2419 and p_final_process_date between effective_start_date
2420 and effective_end_date;
2421
2422 begin
2423 hr_utility.set_location('Entering:'|| l_proc, 1);
2424 --
2425 l_object_version_number := p_object_version_number;
2426 --
2427 -- Validation in addition to Table Handlers
2428 --
2429 -- None.
2430 --
2431 -- Process Logic
2432 --
2433 -- Determine asg future changes warning.
2434 -- Made changes according to first_api_issues.txt
2435
2436 If p_final_process_date = p_actual_termination_date then
2437 --
2438 l_max_asg_end_date := get_max_asg_fut_change_end_dt
2439 (p_assignment_id => p_assignment_id
2440 ,p_effective_date => p_actual_termination_date + 1
2441 );
2442 --
2443 else
2444 l_max_asg_end_date := get_max_asg_fut_change_end_dt
2445 (p_assignment_id => p_assignment_id
2446 ,p_effective_date => p_final_process_date
2447 );
2448 End if;
2449 hr_utility.set_location(l_proc, 10);
2450 --
2451 if l_max_asg_end_date is not null then
2452 --
2453 l_asg_future_changes_warning := TRUE;
2454 hr_utility.set_location(l_proc, 20);
2455 --
2456 end if;
2457 hr_utility.set_location(l_proc, 30);
2458 --
2459 -- Lock the appropriate child rows for this assignment.
2460 --
2461 open csr_lock_csa; -- Locking ladder processing order 970
2462 close csr_lock_csa;
2463 hr_utility.set_location(l_proc, 40);
2464 --
2465 open csr_lock_alu; -- Locking ladder processing order 1110
2466 close csr_lock_alu;
2467 hr_utility.set_location(l_proc, 50);
2468 --
2469 open csr_lock_asa; -- Locking ladder processing order 1190
2470 close csr_lock_asa;
2471 hr_utility.set_location(l_proc,55);
2472 --
2473 -- Fix for bug 5841180 starts here
2474 /* open csr_lock_ele; -- Locking ladder processing order 1440
2475 close csr_lock_ele;
2476 hr_utility.set_location(l_proc, 60);
2477 --
2478 open csr_lock_eev; -- Locking ladder processing order 1450
2479 close csr_lock_eev;
2480 hr_utility.set_location(l_proc, 70); */
2481
2482 open csr_lock_ele(p_final_process_date); -- Locking ladder processing order 1440
2483 close csr_lock_ele;
2484 hr_utility.set_location(l_proc, 60);
2485 --
2486 open csr_lock_eev(p_final_process_date); -- Locking ladder processing order 1450
2487 close csr_lock_eev;
2488 hr_utility.set_location(l_proc, 70);
2489
2490 -- Fix for bug 5841180 ends here
2491 --
2492 open csr_lock_spp; -- Locking ladder processing order 1470
2493 close csr_lock_spp;
2494 hr_utility.set_location(l_proc, 80);
2495 --
2496 open csr_lock_ppm; -- Locking ladder processing order 1490
2497 close csr_lock_ppm;
2498 hr_utility.set_location(l_proc, 90);
2499 --
2500 open csr_lock_abv; -- Locking ladder processing order 1550
2501 close csr_lock_abv;
2502 hr_utility.set_location(l_proc, 115);
2503 --
2504 open csr_lock_sas; -- Locking ladder processing order 1590
2505 close csr_lock_sas;
2506 hr_utility.set_location(l_proc, 120);
2507 --
2508 open csr_lock_pyp; -- Locking ladder processing order 1630
2509 close csr_lock_pyp;
2510 hr_utility.set_location(l_proc, 130);
2511
2512 --
2513 -- For the following tables, date effectively delete any rows which exist as
2514 -- of the final process date, and ZAP any rows which start after the final
2515 -- process date:
2516 --
2517 -- per_secondary_ass_statuses (not datetracked)
2518 -- pay_cost_allocations_f
2519 -- per_spinal_point_placements_f
2520 -- pay_personal_payment_methods_f
2521 -- per_assignment_budget_values_f
2522 --
2523 update per_secondary_ass_statuses sas
2524 set sas.end_date = p_final_process_date
2525 where sas.assignment_id = p_assignment_id
2526 and sas.end_date IS NULL;
2527 --
2528 hr_utility.set_location(l_proc, 140);
2529 --
2530 delete per_secondary_ass_statuses sas
2531 where sas.assignment_id = p_assignment_id
2532 and sas.start_date > p_final_process_date;
2533 --
2534 hr_utility.set_location(l_proc, 150);
2535 --
2536 hr_utility.set_location(l_proc, 170);
2537 --
2538 update pay_cost_allocations_f pca
2539 set pca.effective_end_date = p_final_process_date
2540 where pca.assignment_id = p_assignment_id
2541 and p_final_process_date between pca.effective_start_date
2542 and pca.effective_end_date;
2543 --
2544 hr_utility.set_location(l_proc, 180);
2545 --
2546 delete pay_cost_allocations_f pca
2547 where pca.assignment_id = p_assignment_id
2548 and pca.effective_start_date > p_final_process_date;
2549 --
2550 hr_utility.set_location(l_proc, 190);
2551 --
2552 update per_spinal_point_placements_f spp
2553 set spp.effective_end_date = p_final_process_date
2554 where spp.assignment_id = p_assignment_id
2555 and p_final_process_date between spp.effective_start_date
2556 and spp.effective_end_date;
2557 --
2558 hr_utility.set_location(l_proc, 200);
2559 --
2560 delete per_spinal_point_placements_f spp
2561 where spp.assignment_id = p_assignment_id
2562 and spp.effective_start_date > p_final_process_date;
2563 --
2564 hr_utility.set_location(l_proc, 210);
2565
2566 --
2567 -- SASmith date track of abv. 16-APR-1998
2568
2569 update per_assignment_budget_values_f abv
2570 set abv.effective_end_date = p_final_process_date
2571 where abv.assignment_id = p_assignment_id
2572 and p_final_process_date between abv.effective_start_date
2573 and abv.effective_end_date;
2574 --
2575 hr_utility.set_location(l_proc, 212);
2576 --
2577 delete per_assignment_budget_values_f abv
2578 where abv.assignment_id = p_assignment_id
2579 and abv.effective_start_date > p_final_process_date;
2580 --
2581 hr_utility.set_location(l_proc, 214);
2582
2583
2584 --
2585 -- Process any element entries and assignment_link_usages for this
2586 -- assignment.
2587 -- N.B. The procedure hrempter.terminate_entries_and_alus was procduced for
2588 -- the Forms Application to perform this task, so it will be used here
2589 -- as well. (We require the legislation code.)
2590 --
2591
2592 open csr_get_busgrp_legislation;
2593 fetch csr_get_busgrp_legislation
2594 into l_business_group_id, l_legislation_code;
2595 --
2596 --
2597 if csr_get_busgrp_legislation%NOTFOUND
2598 then
2599 --
2600 hr_utility.set_location(l_proc, 230);
2601 --
2602 close csr_get_busgrp_legislation;
2603 --
2604 -- This should never happen!
2605 --
2606 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
2607 hr_utility.set_message_token('PROCEDURE', l_proc);
2608 hr_utility.set_message_token('STEP','20');
2609 hr_utility.raise_error;
2610 end if;
2611 --
2612 close csr_get_busgrp_legislation;
2613 --
2614 hr_utility.set_location(l_proc, 240);
2615 hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2616 hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2617 'DD-MON-yyyy'),99);
2618 --
2619 -- VT 10/07/96 bug #306710 added parameter in a call list
2620 hrempter.terminate_entries_and_alus
2621 (p_assignment_id => p_assignment_id
2622 ,p_actual_term_date => null
2623 ,p_last_standard_date => null
2624 ,p_final_process_date => p_final_process_date
2625 ,p_legislation_code => l_legislation_code
2626 ,p_entries_changed_warning => l_entries_changed_warning
2627 --
2628 -- 115.66 (START)
2629 --
2630 ,p_alu_change_warning => l_alu_change_warning
2631 --
2632 -- 115.66 (END)
2633 --
2634 );
2635 --
2636 --
2637 hr_utility.set_location(l_proc, 250);
2638 --
2639
2640 -- Call the row handler to date effectively delete the rows
2641 --
2642 for rec in csr_dt_del_ppm loop
2643
2644 pay_ppm_del.del
2645 ( p_personal_payment_method_id => rec.personal_payment_method_id
2646 ,p_effective_start_date => l_effective_start_date
2647 ,p_effective_end_date => l_effective_end_date
2648 ,p_object_version_number => rec.object_version_number
2649 ,p_effective_date => p_final_process_date
2650 ,p_datetrack_mode => 'DELETE');
2651
2652 end loop;
2653 --
2654 hr_utility.set_location(l_proc, 255);
2655
2656 -- Call the row handler to zap rows
2657
2658 for rec in csr_zap_ppm loop
2659
2660 pay_ppm_del.del
2661 ( p_personal_payment_method_id => rec.personal_payment_method_id
2662 ,p_effective_start_date => l_effective_start_date
2663 ,p_effective_end_date => l_effective_end_date
2664 ,p_object_version_number => rec.object_version_number
2665 ,p_effective_date => rec.effective_start_date -- Bug #2796523
2666 ,p_datetrack_mode => 'ZAP');
2667
2668 end loop;
2669
2670 -- Date effectively delete the assignment.
2671 --
2672 hr_utility.set_location('assignment_id : '||to_char(p_assignment_id),99);
2673 hr_utility.set_location('effective date : '||to_char(p_final_process_date,
2674 'DD-MON-yyyy'),99);
2675 --
2676 per_asg_del.del
2677 (p_assignment_id => p_assignment_id
2678 ,p_effective_start_date => l_effective_start_date
2679 ,p_effective_end_date => l_effective_end_date
2680 ,p_business_group_id => l_business_group_id
2681 ,p_object_version_number => l_object_version_number
2682 ,p_effective_date => p_final_process_date
2683 ,p_validation_start_date => l_validation_start_date
2684 ,p_validation_end_date => l_validation_end_date
2685 ,p_datetrack_mode => 'DELETE'
2686 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
2687 );
2688 --
2689 --
2690 hr_utility.set_location(l_proc, 260);
2691 --
2692 -- Set all output arguments
2693 --
2694 p_asg_future_changes_warning := l_asg_future_changes_warning;
2695 p_effective_end_date := l_effective_end_date;
2696 p_effective_start_date := l_effective_start_date;
2697 p_entries_changed_warning := l_entries_changed_warning;
2698 p_object_version_number := l_object_version_number;
2699 p_org_now_no_manager_warning := l_org_now_no_manager_warning;
2700 --
2701 hr_utility.set_location(' Leaving:'||l_proc, 999);
2702 --
2703 end final_process_emp_asg_sup;
2704 --
2705 --
2706 -- ----------------------------------------------------------------------------
2707 -- |--------------------------------< SPP_ZAP >--------------------------------|
2708 -- ----------------------------------------------------------------------------
2709 --
2710 PROCEDURE spp_zap
2711 (p_assignment_id IN per_assignments_f.assignment_id%TYPE) IS
2712 --
2713 -- Declare Local Variables
2714 --
2715 l_proc VARCHAR2(72) := g_package||'spp_zap';
2716 l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
2717 l_effective_start_date DATE;
2718 l_effective_end_date DATE;
2719 --
2720 CURSOR csr_spp_records IS
2721 SELECT spp.placement_id,
2722 spp.object_version_number,
2723 spp.effective_start_date
2724 FROM per_spinal_point_placements_f spp
2725 WHERE spp.assignment_id = p_assignment_id
2726 ORDER BY placement_id;
2727 --
2728 BEGIN
2729 --
2730 hr_utility.set_location('Entering : '||l_proc,10);
2731 --
2732 l_previous_id := -1;
2733 --
2734 FOR c_spp_record IN csr_spp_records LOOP
2735 --
2736 hr_utility.set_location(l_proc||'/'||c_spp_record.placement_id,20);
2737 hr_utility.set_location(l_proc||'/'||c_spp_record.object_version_number,21);
2738 hr_utility.set_location(l_proc||'/'||c_spp_record.effective_start_date,22);
2739 --
2740 IF l_previous_id <> c_spp_record.placement_id THEN
2741 --
2742 hr_utility.set_location(l_proc,30);
2743 --
2744 hr_sp_placement_api.delete_spp
2745 (p_effective_date => c_spp_record.effective_start_date
2746 ,p_datetrack_mode => hr_api.g_zap
2747 ,p_placement_id => c_spp_record.placement_id
2748 ,p_object_version_number => c_spp_record.object_version_number
2749 ,p_effective_start_date => l_effective_start_date
2750 ,p_effective_end_date => l_effective_end_date);
2751 --
2752 l_previous_id := c_spp_record.placement_id;
2753 --
2754 END IF;
2755 --
2756 END LOOP;
2757 --
2758 hr_utility.set_location('Leaving : '||l_proc,999);
2759 --
2760 END spp_zap;
2761 --
2762 -- ----------------------------------------------------------------------------
2763 -- |-----------------------< SPP_UPDATE_CHANGE_INSERT >-----------------------|
2764 -- ----------------------------------------------------------------------------
2765 --
2766 PROCEDURE spp_update_change_insert
2767 (p_assignment_id IN per_assignments_f.assignment_id%TYPE
2768 ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
2769 ,p_validation_start_date IN DATE
2770 ,p_validation_end_date IN DATE
2771 ,p_spp_eff_start_date IN DATE
2772 ,p_datetrack_mode IN OUT NOCOPY VARCHAR2
2773 ,p_object_version_number IN OUT NOCOPY NUMBER) IS
2774 --
2775 -- Declare Local Variables
2776 --
2777 l_proc VARCHAR2(72) := g_package||'spp_update_change_insert';
2778 l_effective_start_date DATE;
2779 l_effective_end_date DATE;
2780 l_datetrack_mode VARCHAR2(30);
2781 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
2782 l_dummy_id per_spinal_point_placements_f.placement_id%TYPE;
2783 --
2784 -- Checks to see if future rows exist
2785 --
2786 CURSOR csr_future_records IS
2787 SELECT spp.placement_id
2788 FROM per_spinal_point_placements_f spp
2789 WHERE spp.assignment_id = p_assignment_id
2790 AND spp.effective_start_date > p_validation_start_date;
2791 --
2792 --
2793 -- Cursor used to retrieve all SPP records for an
2794 -- grade when performing an update_change_insert.
2795 --
2796 CURSOR csr_update_change_insert_rows IS
2797 SELECT spp.placement_id,
2798 spp.object_version_number,
2799 spp.effective_start_date
2800 FROM per_spinal_point_placements_f spp
2801 WHERE effective_start_date BETWEEN p_spp_eff_start_date
2802 AND p_validation_end_date
2803 AND effective_end_date < p_validation_end_date
2804 AND assignment_id = p_assignment_id
2805 ORDER BY effective_start_date DESC;
2806 --
2807 BEGIN
2808 --
2809 hr_utility.set_location('Entering : '||l_proc,10);
2810 --
2811 l_datetrack_mode := p_datetrack_mode;
2812 l_object_version_number := p_object_version_number;
2813 --
2814 -- Check for future dated SPP records.
2815 --
2816 OPEN csr_future_records;
2817 FETCH csr_future_records INTO l_dummy_id;
2818 --
2819 IF csr_future_records%FOUND THEN
2820 --
2821 hr_utility.set_location(l_proc,20);
2822 --
2823 -- Loop through all SPP records that are linked to the
2824 -- assignment between the assignment record start and end
2825 -- date and perform a DELETE_NEXT_CHANGE on these records.
2826 --
2827 -- Update Change Insert
2828 -- Gr3
2829 -- |
2830 -- |
2831 -- Gr1 Gr2 Gr3
2832 --ASG |-------------|-------------|------------>
2833 -- 1 2 3 4 8
2834 --SPP |----|-----|--|-------------|------------>
2835 --
2836 -- BECOMES
2837 --
2838 -- Gr1 Gr3 Gr2 Gr3
2839 --ASG |--|----------|-------------|------------>
2840 -- 1 8 4 8
2841 --SPP |--|----------|-------------|------------>
2842 --
2843 -- The FOR LOOP below will perform a DELETE_NEXT_CHANGE
2844 -- on SPP records 2 then 1, so that SPP 1 record will match
2845 -- the assignment start and end dates. An
2846 -- UPDATE_CHANGE_INSERT will then be performed on SPP 1
2847 -- record to insert SPP 8. THis update will be done in the
2848 -- maintain_spp_asg procedure.
2849 --
2850 FOR c1_rec IN csr_update_change_insert_rows LOOP
2851 --
2852 hr_utility.set_location(l_proc||'/'||c1_rec.object_version_number,30);
2853 hr_utility.set_location(l_proc||'/'||c1_rec.effective_start_date,31);
2854 --
2855 l_object_version_number := c1_rec.object_version_number;
2856 --
2857 hr_sp_placement_api.delete_spp
2858 (p_effective_date => c1_rec.effective_start_date
2859 ,p_datetrack_mode => hr_api.g_delete_next_change
2860 ,p_placement_id => p_placement_id
2861 ,p_object_version_number => l_object_version_number
2862 ,p_effective_start_date => l_effective_start_date
2863 ,p_effective_end_date => l_effective_end_date);
2864 --
2865 END LOOP;
2866 --
2867 -- Check if the step placement record starts on the same day
2868 -- as the updated assignment record. If it does then change
2869 -- the date track mode to Correction.
2870 --
2871 IF p_spp_eff_start_date = p_validation_start_date THEN
2872 --
2873 hr_utility.set_location(l_proc,40);
2874 --
2875 l_datetrack_mode := 'CORRECTION';
2876 --
2877 ELSE
2878 --
2879 hr_utility.set_location(l_proc,50);
2880 --
2881 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
2882 --
2883 END IF;
2884 --
2885 -- If no future records have been found then
2886 -- set the datetrack mode
2887 --
2888 ELSE
2889 --
2890 hr_utility.set_location(l_proc,60);
2891 --
2892 -- Check if the step placement record starts on the same
2893 -- day as the updated assignment record. If it does then change
2894 -- the date track mode to Correction.
2895 --
2896 IF p_spp_eff_start_date = p_validation_start_date THEN
2897 --
2898 hr_utility.set_location(l_proc,70);
2899 --
2900 l_datetrack_mode := 'CORRECTION';
2901 --
2902 ELSE
2903 --
2904 hr_utility.set_location(l_proc,80);
2905 --
2906 l_datetrack_mode := 'UPDATE';
2907 --
2908 END IF;
2909 --
2910 CLOSE csr_future_records;
2911 --
2912 END IF; -- csr_future_records%found
2913 --
2914 -- Set Out parameters
2915 --
2916 p_object_version_number := l_object_version_number;
2917 p_datetrack_mode := l_datetrack_mode;
2918 --
2919 hr_utility.set_location('Leaving : '||l_proc,999);
2920 --
2921 END spp_update_change_insert;
2922 --
2923 -- ----------------------------------------------------------------------------
2924 -- |-------------------------< CHECK_VALID_PLACEMENT >------------------------|
2925 -- ----------------------------------------------------------------------------
2926 --
2927 procedure chk_valid_placement_id
2928 (p_assignment_id in per_all_assignments_f.assignment_id%Type
2929 ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
2930 ,p_validation_start_date in date) is
2931 --
2932 -- Local variables
2933 l_exist varchar2(1);
2934 --
2935 l_proc varchar(72) := g_package||'chk_valid_placement_id';
2936 -- Fetch future SPP Records(other placement id)
2937 cursor csr_invalid_placement_id is
2938 select 'Y'
2939 from per_spinal_point_placements_f spp
2940 where spp.assignment_id = p_assignment_id
2941 and spp.effective_start_date > p_validation_start_date
2942 and spp.placement_id <> p_placement_id;
2943 --
2944 begin
2945 --
2946 if g_debug then
2947 hr_utility.set_location('Entering : '||l_proc, 10);
2948 end if;
2949 -- If there are future SPP records that have
2950 -- a different placement id then raise an error
2951 open csr_invalid_placement_id;
2952 fetch csr_invalid_placement_id into l_exist;
2953 if csr_invalid_placement_id%found then
2954 --
2955 close csr_invalid_placement_id;
2956 --
2957 hr_utility.set_message(800, 'HR_289827_SPP_FUTURE_SPP_REC');
2958 hr_utility.raise_error;
2959 --
2960 else
2961 --
2962 close csr_invalid_placement_id;
2963 --
2964 end if;
2965 --
2966 if g_debug then
2967 hr_utility.set_location('Leaving : '||l_proc, 99);
2968 end if;
2969 --
2970 end;
2971 --
2972 -- ----------------------------------------------------------------------------
2973 -- |-----------------------------< CLEANUP_SPP >------------------------------|
2974 -- ----------------------------------------------------------------------------
2975 --
2976 procedure cleanup_spp
2977 (p_assignment_id in per_all_assignments_f.assignment_id%Type
2978 ,p_datetrack_mode in varchar2
2979 ,p_validation_start_date in date
2980 ,p_del_end_future_spp in out nocopy boolean) is
2981 --
2982 -- Local variables
2983 l_old_grade_id per_grade_spines_f.grade_id%Type;
2984 --
2985 l_proc varchar(72) := g_package||'cleanup_spp';
2986 -- Cursor to retrive the assignment records
2987 cursor csr_asg_details is
2988 select paa.effective_start_date,
2989 paa.effective_end_date,
2990 paa.grade_id
2991 from per_all_assignments_f paa
2992 where paa.assignment_id = p_assignment_id
2993 and paa.effective_end_date >= p_validation_start_date - 1
2994 order by paa.effective_start_date;
2995 -- Cursor to retrive the spp records for the assignment record for with
2996 -- there is no Grade attached (if any)
2997 cursor csr_asg_spp(l_asg_eff_start_date date, l_asg_eff_end_date date) is
2998 select spp.placement_id,
2999 spp.object_version_number,
3000 spp.effective_start_date,
3001 spp.effective_end_date
3002 from per_spinal_point_placements_f spp
3003 where spp.assignment_id = p_assignment_id
3004 and spp.effective_start_date >= l_asg_eff_start_date
3005 and spp.effective_end_date <= l_asg_eff_end_date;
3006 -- Cursor to retrive the SPP records without a valid Grade
3007 cursor csr_spp_placement(l_asg_eff_start_date date, l_asg_eff_end_date date) is
3008 select pgs.grade_id,
3009 spp.placement_id,
3010 spp.effective_start_date,
3011 spp.effective_end_date
3012 from per_grade_spines_f pgs,
3013 per_spinal_point_steps_f sps,
3014 per_spinal_point_placements_f spp
3015 where sps.grade_spine_id = pgs.grade_spine_id
3016 and spp.step_id = sps.step_id
3017 and pgs.parent_spine_id = spp.parent_spine_id
3018 and spp.assignment_id = p_assignment_id
3019 and spp.effective_start_date >= l_asg_eff_start_date
3020 and spp.effective_end_date <= l_asg_eff_end_date;
3021 -- Cursor to get the future SPP records, with effective_start_date >
3022 -- validation_start_date and effective_end_date is > validation_end_date
3023 cursor csr_asg_spp_error(l_asg_eff_start_date date, l_asg_eff_end_date date) is
3024 select pgs.grade_id
3025 from per_grade_spines_f pgs,
3026 per_spinal_point_steps_f sps,
3027 per_spinal_point_placements_f spp
3028 where sps.grade_spine_id = pgs.grade_spine_id
3029 and spp.step_id = sps.step_id
3030 and pgs.parent_spine_id = spp.parent_spine_id
3031 and spp.assignment_id = p_assignment_id
3032 and spp.effective_start_date between l_asg_eff_start_date
3033 and l_asg_eff_end_date
3034 and spp.effective_end_date > l_asg_eff_end_date;
3035 -- Cursor to check any SPP record is existing from the validation start date
3036 -- If any such SPP record is existing, then we need to delete that records
3037 cursor csr_spp_records is
3038 select spp.placement_id,
3039 spp.effective_start_date,
3040 spp.effective_end_date
3041 from per_spinal_point_placements_f spp
3042 where spp.assignment_id = p_assignment_id
3043 and spp.effective_start_date >= p_validation_start_date;
3044 --
3045 begin
3046 --
3047 if g_debug then
3048 hr_utility.set_location('Entering : '||l_proc, 10);
3049 end if;
3050 -- All Non valid future SPP records needs to be deleted(if any)
3051 for csr_asg_rec in csr_asg_details loop
3052 -- If Grade Id is already set to Null then we should delete the corresponding
3053 -- Grade Step records.
3054 if csr_asg_rec.grade_id is null then
3055 --
3056 if g_debug then
3057 hr_utility.set_location(l_proc, 20);
3058 end if;
3059 -- Needs to be deleted all non valid SPP records
3060 for asg_spp_rec in csr_asg_spp(csr_asg_rec.effective_start_date,
3061 csr_asg_rec.effective_end_date) loop
3062 -- There are some SPP records existing for this assignment without a Grade.
3063 delete from per_spinal_point_placements_f spp
3064 where spp.placement_id = asg_spp_rec.placement_id
3065 and spp.effective_start_date = asg_spp_rec.effective_start_date
3066 and spp.effective_end_date = asg_spp_rec.effective_end_date;
3067 --
3068 if g_debug then
3069 hr_utility.set_location(l_proc, 30);
3070 end if;
3071 -- Setting the warning parameter
3072 p_del_end_future_spp := true;
3073 --
3074 end loop;
3075 -- Grade is attached, but a placement is existing for an invalid Grade
3076 -- during this assignment period, then that SPP record needs to be deleted.
3077 else
3078 for rec_spp_placement in csr_spp_placement(csr_asg_rec.effective_start_date,
3079 csr_asg_rec.effective_end_date) loop
3080 -- Placement is with an invalid Grade, whcih needs to be deleted
3081 if g_debug then
3082 hr_utility.set_location(l_proc||' Placement Grade Id '||rec_spp_placement.grade_id , 40);
3083 hr_utility.set_location(l_proc||' Asg Grade Id '||csr_asg_rec.grade_id , 50);
3084 end if;
3085 if rec_spp_placement.grade_id <> csr_asg_rec.grade_id then
3086 --
3087 delete from per_spinal_point_placements_f spp
3088 where spp.placement_id = rec_spp_placement.placement_id
3089 and spp.effective_start_date = rec_spp_placement.effective_start_date
3090 and spp.effective_end_date = rec_spp_placement.effective_end_date;
3091 --
3092 if g_debug then
3093 hr_utility.set_location(l_proc, 60);
3094 end if;
3095 -- Setting the warning parameter
3096 p_del_end_future_spp := true;
3097 --
3098 end if;
3099 --
3100 end loop;
3101 --
3102 end if;
3103 -- We need to verify that, any SPP record is existing during the assignment
3104 -- record period, and effective_start_date of that SPP record is after the
3105 -- validation_start_date and effective_end_date is greater than the
3106 -- validation_end_date, such cases we cannot perform delete next change,
3107 -- should display an error message and user needs to delete that SPP record
3108 -- through Placement form, if he wants to proceed.
3109 if p_datetrack_mode = hr_api.g_delete_next_change then
3110 --
3111 open csr_asg_spp_error(csr_asg_rec.effective_start_date,
3112 csr_asg_rec.effective_end_date);
3113 fetch csr_asg_spp_error into l_old_grade_id;
3114 if csr_asg_spp_error%found and
3115 nvl(l_old_grade_id, hr_api.g_number) <>
3116 nvl(csr_asg_rec.grade_id, hr_api.g_number) then
3117 --
3118 close csr_asg_spp_error;
3119 --
3120 hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
3121 hr_utility.raise_error;
3122 --
3123 else
3124 --
3125 close csr_asg_spp_error;
3126 --
3127 end if;
3128 --
3129 end if;
3130 --
3131 end loop;
3132 if p_datetrack_mode in (hr_api.g_correction,
3133 hr_api.g_update_change_insert) then
3134 --
3135 for spp_rec in csr_spp_records loop
3136 --
3137 delete from per_spinal_point_placements_f spp
3138 where spp.placement_id = spp_rec.placement_id
3139 and spp.effective_start_date = spp_rec.effective_start_date
3140 and spp.effective_end_date = spp_rec.effective_end_date;
3141 if g_debug then
3142 hr_utility.set_location(l_proc, 70);
3143 end if;
3144 -- Setting the warning parameter
3145 p_del_end_future_spp := true;
3146 --
3147 end loop;
3148 --
3149 end if;
3150 if g_debug then
3151 hr_utility.set_location('Leaving : '||l_proc, 99);
3152 end if;
3153 --
3154 end cleanup_spp;
3155 --
3156 -- ----------------------------------------------------------------------------
3157 -- |------------------------< DELETE_NEXT_CHANGE_SPP >-------------------------|
3158 -- ----------------------------------------------------------------------------
3159 --
3160 procedure delete_next_change_spp
3161 (p_assignment_id in per_all_assignments_f.assignment_id%Type
3162 ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3163 ,p_grade_id in per_grade_spines_f.grade_id%Type
3164 ,p_datetrack_mode in varchar2
3165 ,p_validation_start_date in date
3166 ,p_validation_end_date in date
3167 ,p_del_end_future_spp out nocopy boolean) is
3168 --
3169 -- Declare Local Variables
3170 l_placement_id number;
3171 l_datetrack_mode varchar2(30);
3172 l_exists varchar2(1);
3173 l_effective_start_date date;
3174 l_effective_end_date date;
3175 l_del_end_future_spp boolean := false;
3176 l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3177 l_grade_id per_spinal_point_placements_f.placement_id%Type;
3178 --
3179 l_proc varchar(72) := g_package||'delete_next_change_spp';
3180 -- Cursor used to retrieve all SPP records for a Grade.
3181 cursor csr_update_change_rows is
3182 select spp.placement_id,
3183 spp.effective_start_date,
3184 spp.effective_end_date,
3185 spp.object_version_number
3186 from per_spinal_point_placements_f spp
3187 where spp.effective_end_date between p_validation_start_date - 1
3188 and p_validation_end_date
3189 and spp.effective_end_date < p_validation_end_date
3190 and spp.assignment_id = p_assignment_id
3191 order by spp.effective_start_date desc;
3192 -- Cursor to get the Grade of future SPP
3193 cursor csr_spp_grade is
3194 select pgs.grade_id
3195 from per_grade_spines_f pgs,
3196 per_spinal_point_steps_f sps,
3197 per_spinal_point_placements_f spp
3198 where sps.grade_spine_id = pgs.grade_spine_id
3199 and spp.step_id = sps.step_id
3200 and pgs.parent_spine_id = spp.parent_spine_id
3201 and spp.assignment_id = p_assignment_id
3202 and spp.effective_start_date between p_validation_start_date
3203 and p_validation_end_date;
3204 --
3205 -- check if any spp is continues at the Validation End Date.
3206 --
3207 cursor csr_ved_continues_spp IS
3208 select spp.placement_id,spp.effective_start_date
3209 ,spp.effective_end_date, spp.object_version_number
3210 from per_spinal_point_placements_f spp
3211 where spp.assignment_id = p_assignment_id
3212 and spp.effective_start_date < p_validation_end_date
3213 and spp.effective_end_date > p_validation_end_date;
3214 --
3215 -- Check for future spp dt records.
3216 --
3217 cursor csr_future_spp_exists(p_date date,p_placement_id number) IS
3218 select 'Y'
3219 from per_spinal_point_placements_f
3220 where placement_id = p_placement_id
3221 and effective_start_date > p_date;
3222 --
3223 begin
3224 --
3225 if g_debug then
3226 hr_utility.set_location('Entering : '||l_proc, 10);
3227 end if;
3228 -- Needs to perform the delete next change operation only, if there is
3229 -- Grade change after the assignment level DNC. (Not null to a new not null)
3230 open csr_spp_grade;
3231 fetch csr_spp_grade into l_grade_id;
3232 close csr_spp_grade;
3233 --
3234 if p_grade_id <> l_grade_id then
3235 -- If there are future date SPP records that have
3236 -- a different placement id then raise an error
3237 if g_debug then
3238 hr_utility.set_location(l_proc, 20);
3239 end if;
3240 --
3241 -- At validation end date check for continues placement record.
3242 -- If found, just break it.
3243 --
3244 hr_utility.set_location(l_proc, 30);
3245 open csr_ved_continues_spp;
3246 fetch csr_ved_continues_spp into
3247 l_placement_id,l_effective_start_date
3248 ,l_effective_end_date,l_object_version_number;
3249 if csr_ved_continues_spp%found then
3250 --
3251 --
3252 l_datetrack_mode := hr_api.g_update;
3253 --
3254 open csr_future_spp_exists(p_validation_end_date,l_placement_id);
3255 fetch csr_future_spp_exists into l_exists;
3256 if csr_future_spp_exists%found then
3257 --
3258 l_datetrack_mode := hr_api.g_update_change_insert;
3259 --
3260 end if;
3261 --
3262 close csr_future_spp_exists;
3263 --
3264 -- continues placement record found on validation end date.
3265 -- Therefore simply break it on validation end date.
3266 --
3267 hr_utility.set_location('ved spp found', 40);
3268 hr_utility.set_location('dt mode :'||l_datetrack_mode, 40);
3269 hr_sp_placement_api.update_spp(
3270 p_effective_date => p_validation_end_date+1
3271 ,p_datetrack_mode => l_datetrack_mode
3272 ,p_placement_id => l_placement_id
3273 ,p_object_version_number => l_object_version_number
3274 ,p_effective_start_date => l_effective_start_date
3275 ,p_effective_end_date => l_effective_end_date);
3276 --
3277 end if;
3278 close csr_ved_continues_spp;
3279 --
3280 chk_valid_placement_id(p_assignment_id => p_assignment_id
3281 ,p_placement_id => p_placement_id
3282 ,p_validation_start_date => p_validation_start_date);
3283 --
3284 for csr_rec in csr_update_change_rows loop
3285 --
3286 if g_debug then
3287 hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
3288 hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 40);
3289 hr_utility.set_location(l_proc||'/'||csr_rec.effective_end_date, 50);
3290 hr_utility.set_location(l_proc||'/'||csr_rec.placement_id, 60);
3291 end if;
3292 --
3293 if p_validation_end_date > csr_rec.effective_end_date and
3294 csr_rec.effective_end_date <> hr_api.g_eot then
3295 --
3296 l_object_version_number := csr_rec.object_version_number;
3297 hr_sp_placement_api.delete_spp(
3298 p_effective_date => csr_rec.effective_start_date
3299 ,p_datetrack_mode => hr_api.g_delete_next_change
3300 ,p_placement_id => csr_rec.placement_id
3301 ,p_object_version_number => l_object_version_number
3302 ,p_effective_start_date => l_effective_start_date
3303 ,p_effective_end_date => l_effective_end_date);
3304 --
3305 l_del_end_future_spp := true;
3306 --
3307 if g_debug then
3308 hr_utility.set_location(l_proc, 70);
3309 end if;
3310 --
3311 end if;
3312 --
3313 end loop;
3314 --
3315 end if;
3316 -- Cleanup of all invalid steps(if any)
3317 cleanup_spp(p_assignment_id => p_assignment_id
3318 ,p_datetrack_mode => p_datetrack_mode
3319 ,p_validation_start_date => p_validation_start_date
3320 ,p_del_end_future_spp => l_del_end_future_spp);
3321 -- Setting the out parameter
3322 p_del_end_future_spp := l_del_end_future_spp;
3323 --
3324 if g_debug then
3325 hr_utility.set_location('Leaving : '||l_proc, 99);
3326 end if;
3327 --
3328 end delete_next_change_spp;
3329 --
3330 -- ----------------------------------------------------------------------------
3331 -- |--------------------------< FUTURE_CHANGE_SPP >---------------------------|
3332 -- ----------------------------------------------------------------------------
3333 --
3334 procedure future_change_spp
3335 (p_assignment_id in per_all_assignments_f.assignment_id%Type
3336 ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3337 ,p_datetrack_mode in varchar2
3338 ,p_validation_start_date in date
3339 ,p_del_end_future_spp out nocopy boolean) is
3340 --
3341 -- Local variables
3342 l_spp_eff_start_date date;
3343 l_spp_eff_end_date date;
3344 l_effective_start_date date;
3345 l_effective_end_date date;
3346 l_del_end_future_spp boolean := false;
3347 l_placement_id per_spinal_point_placements_f.placement_id%Type;
3348 l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3349 --
3350 l_proc varchar2(72) := g_package||'future_change_spp';
3351 -- As the validation start and end dates are for the asg row being
3352 -- deleted, then we need to get the SPP record that belongs to the
3353 -- previous date tracked row.
3354 cursor csr_spp_details is
3355 select spp.placement_id,
3356 spp.object_version_number,
3357 spp.effective_start_date,
3358 spp.effective_end_date
3359 from per_spinal_point_placements_f spp
3360 where spp.assignment_id = p_assignment_id
3361 and spp.effective_start_date < p_validation_start_date
3362 order by effective_start_date desc;
3363 --
3364 begin
3365 --
3366 if g_debug then
3367 hr_utility.set_location('Entering : '||l_proc, 10);
3368 end if;
3369 -- Check for the uniqueness of Step placement id
3370 chk_valid_placement_id(p_assignment_id => p_assignment_id
3371 ,p_placement_id => p_placement_id
3372 ,p_validation_start_date => p_validation_start_date);
3373 if g_debug then
3374 hr_utility.set_location(l_proc, 20);
3375 end if;
3376 -- Check that there has been a grade step created for this assignment
3377 open csr_spp_details;
3378 fetch csr_spp_details into l_placement_id
3379 ,l_object_version_number
3380 ,l_spp_eff_start_date
3381 ,l_spp_eff_end_date;
3382 -- If the are SPP records for the assignment then
3383 -- perform a DT FUTURE_CHANGE on the current spp_row.
3384 -- We can perform a FUTURE_CHANGE operation on SPP records only if,
3385 -- a) Current effective records effective_end_date is NOT EOT and
3386 -- b) p_validation_end_date is NOT less than or equal to current
3387 -- effective_end_date of SPP record.
3388 if csr_spp_details%found and
3389 l_spp_eff_end_date <> hr_api.g_eot then
3390 --
3391 hr_sp_placement_api.delete_spp(
3392 p_effective_date => l_spp_eff_start_date
3393 ,p_datetrack_mode => p_datetrack_mode
3394 ,p_placement_id => l_placement_id
3395 ,p_object_version_number => l_object_version_number
3396 ,p_effective_start_date => l_effective_start_date
3397 ,p_effective_end_date => l_effective_end_date);
3398 --
3399 l_del_end_future_spp := true;
3400 --
3401 if g_debug then
3402 hr_utility.set_location(l_proc, 30);
3403 end if;
3404 --
3405 end if;
3406 --
3407 close csr_spp_details;
3408 --
3409 if g_debug then
3410 hr_utility.set_location(l_proc, 40);
3411 end if;
3412 -- Cleanup of all invalid steps(if any)
3413 cleanup_spp(p_assignment_id => p_assignment_id
3414 ,p_datetrack_mode => p_datetrack_mode
3415 ,p_validation_start_date => p_validation_start_date
3416 ,p_del_end_future_spp => l_del_end_future_spp);
3417 -- Setting the out parameter
3418 p_del_end_future_spp := l_del_end_future_spp;
3419 if g_debug then
3420 hr_utility.set_location('Leaving : '||l_proc, 99);
3421 end if;
3422 --
3423 end future_change_spp;
3424 --
3425 -- ----------------------------------------------------------------------------
3426 -- |-------------------------< UPDATE_OVERRIDE_SPP >--------------------------|
3427 -- ----------------------------------------------------------------------------
3428 --
3429 procedure update_override_spp
3430 (p_assignment_id in per_all_assignments_f.assignment_id%Type
3431 ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3432 ,p_datetrack_mode in varchar2
3433 ,p_validation_start_date in date
3434 ,p_validation_end_date in date
3435 ,p_spp_eff_start_date in date
3436 ,p_grade_id in number
3437 ,p_step_id in number
3438 ,p_object_version_number in number
3439 ,p_current_spp_exist in boolean
3440 ,p_pay_scale_defined in boolean
3441 ,p_del_end_future_spp out nocopy boolean) is
3442 --
3443 -- declare local variables
3444 l_effective_start_date date;
3445 l_effective_end_date date;
3446 l_max_eff_end_date date;
3447 l_grade_id number;
3448 l_datetrack_mode varchar2(30);
3449 l_del_end_future_spp boolean := false;
3450 l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3451 l_previous_ovn per_spinal_point_placements_f.object_version_number%Type;
3452 l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3453 := p_object_version_number;
3454 --
3455 l_proc varchar2(72) := g_package||'update_override_spp';
3456 -- Assignment record is already updated with UPDATE_OVERRIDE DT Mode
3457 -- We need to get the previous assignment record to check whether any
3458 -- Grade changes is happend in this DT UPDATE_OVERRIDE
3459 cursor csr_asg_details is
3460 select paa.grade_id
3461 from per_all_assignments_f paa
3462 where paa.assignment_id = p_assignment_id
3463 and p_validation_start_date - 1 between paa.effective_start_date
3464 and paa.effective_end_date
3465 order by paa.effective_start_date;
3466 -- Checks to see if any future Grade Step rows exist for the given assignment
3467 cursor csr_future_records is
3468 select spp.placement_id
3469 from per_spinal_point_placements_f spp
3470 where spp.assignment_id = p_assignment_id
3471 and spp.effective_start_date > p_validation_start_date;
3472 -- Cursor to retrive the past SPP record to perform
3473 -- DELETE_NEXT_CHANGE
3474 cursor csr_past_spp_details(l_placement_id in number) is
3475 select spp.object_version_number,
3476 spp.effective_start_date,
3477 spp.effective_end_date
3478 from per_spinal_point_placements_f spp
3479 where spp.placement_id = l_placement_id
3480 and p_validation_start_date - 1 between spp.effective_start_date
3481 and spp.effective_end_date;
3482 -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3483 cursor csr_past_spp_records(l_placement_id in number) is
3484 select spp.object_version_number
3485 from per_spinal_point_placements_f spp
3486 where spp.placement_id = l_placement_id
3487 and spp.effective_start_date < p_validation_start_date
3488 order by effective_start_date desc;
3489 -- Cursor used to retrieve all SPP records for a Grade
3490 cursor csr_update_change_rows is
3491 select spp.object_version_number,
3492 spp.effective_start_date
3493 from per_spinal_point_placements_f spp
3494 where spp.effective_start_date between p_spp_eff_start_date
3495 and p_validation_end_date
3496 and spp.effective_end_date < p_validation_end_date
3497 and spp.assignment_id = p_assignment_id
3498 order by spp.effective_start_date desc;
3499 --
3500 begin
3501 --
3502 if g_debug then
3503 hr_utility.set_location('Entering : '||l_proc, 10);
3504 end if;
3505 -- Assignment record is having some SPP records for this DT period
3506 if p_current_spp_exist then
3507 -- If the assignemnt record is having a Grade and that Grade has a Pay Scale
3508 -- already defined. Or no changes in Grade Information for the new
3509 -- datetracked assgt record
3510 -- If the Pay Scale is not defined for the new Grade or the user is setting the
3511 -- Grade to Null
3512 -- Check for the uniqueness of Step placement id
3513 chk_valid_placement_id(p_assignment_id => p_assignment_id
3514 ,p_placement_id => p_placement_id
3515 ,p_validation_start_date => p_validation_start_date);
3516 --
3517 if p_pay_scale_defined then
3518 --
3519 open csr_asg_details;
3520 fetch csr_asg_details into l_grade_id;
3521 --
3522 if csr_asg_details%found then
3523 --
3524 close csr_asg_details;
3525 -- Here both values must be not null (only that case will come
3526 -- to this IF condition)
3527 -- User is swapping the Grade with a New Grade
3528 if p_grade_id <> l_grade_id then
3529 --
3530 if g_debug then
3531 hr_utility.set_location(l_proc, 20);
3532 end if;
3533 --
3534 open csr_future_records;
3535 fetch csr_future_records into l_dummy_id;
3536 if csr_future_records%found then
3537 -- As future records are existing, we can perform an UPDATE_OVERRIDE
3538 l_datetrack_mode := p_datetrack_mode;
3539 --
3540 if g_debug then
3541 hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 30);
3542 hr_utility.set_location(l_proc, 40);
3543 end if;
3544 --
3545 else
3546 -- As there is no future records are existing.
3547 -- We need to end date the current record and create a new DT record
3548 -- with the new Grade
3549 -- Performing a DT UPDATE
3550 l_datetrack_mode := hr_api.g_update;
3551 if g_debug then
3552 hr_utility.set_location(l_proc||' /l_datetrack_mode '||l_datetrack_mode, 50);
3553 hr_utility.set_location(l_proc, 60);
3554 end if;
3555 --
3556 end if;
3557 --
3558 close csr_future_records;
3559 --
3560 hr_sp_placement_api.update_spp(
3561 p_effective_date => p_validation_start_date
3562 ,p_datetrack_mode => l_datetrack_mode
3563 ,p_placement_id => p_placement_id
3564 ,p_object_version_number => l_object_version_number
3565 ,p_step_id => p_step_id
3566 ,p_auto_increment_flag => 'N'
3567 ,p_reason => ''
3568 ,p_increment_number => NULL
3569 ,p_effective_start_date => l_effective_start_date
3570 ,p_effective_end_date => l_effective_end_date);
3571 -- Setting the warning parameter
3572 l_del_end_future_spp := true;
3573 --
3574 if g_debug then
3575 hr_utility.set_location(l_proc, 70);
3576 end if;
3577 -- No change in Grade
3578 else
3579 -- Needs to check if any future records are existing for the SPP records
3580 -- If there is no future records, then we cannot perfom an UPDATE_OVERRIDE
3581 -- as there is no future records to be overridden
3582 --
3583 open csr_future_records;
3584 fetch csr_future_records into l_dummy_id;
3585 if csr_future_records%found then
3586 -- Since future records are existing then perfom an UPDATE_OVERRIDE
3587 if g_debug then
3588 hr_utility.set_location(l_proc||' /p_datetrack_mode '||p_datetrack_mode, 80);
3589 end if;
3590 -- As future records existing for the current SPP. And if the SPP
3591 -- effective start date and validation start date are same, then we
3592 -- cannot perform a DT UPDATE_OVERRIDE.
3593 if p_validation_start_date = p_spp_eff_start_date then
3594 --
3595 for csr_rec in csr_update_change_rows loop
3596 --
3597 if g_debug then
3598 hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 90);
3599 hr_utility.set_location(l_proc||'/'||csr_rec.effective_start_date, 100);
3600 end if;
3601 l_object_version_number := csr_rec.object_version_number;
3602 hr_sp_placement_api.delete_spp(
3603 p_effective_date => csr_rec.effective_start_date
3604 ,p_datetrack_mode => hr_api.g_delete_next_change
3605 ,p_placement_id => p_placement_id
3606 ,p_object_version_number => l_object_version_number
3607 ,p_effective_start_date => l_effective_start_date
3608 ,p_effective_end_date => l_effective_end_date);
3609 -- Setting the warning parameter
3610 l_del_end_future_spp := true;
3611 --
3612 if g_debug then
3613 hr_utility.set_location(l_proc, 110);
3614 end if;
3615 --
3616 end loop;
3617 --
3618 else
3619 -- Normal case, performing UPDATE_OVERRIDE
3620 hr_sp_placement_api.update_spp(
3621 p_effective_date => p_validation_start_date
3622 ,p_datetrack_mode => p_datetrack_mode
3623 ,p_placement_id => p_placement_id
3624 ,p_object_version_number => l_object_version_number
3625 ,p_step_id => p_step_id
3626 ,p_auto_increment_flag => 'N'
3627 ,p_reason => ''
3628 ,p_increment_number => NULL
3629 ,p_effective_start_date => l_effective_start_date
3630 ,p_effective_end_date => l_effective_end_date);
3631 -- Setting the warning parameter
3632 l_del_end_future_spp := true;
3633 --
3634 if g_debug then
3635 hr_utility.set_location(l_proc, 120);
3636 end if;
3637 -- We need to do extra process if the DT Mode is UPDATE_OVERRIDE
3638 -- Update SPP API will end date the current record with
3639 -- p_validation_start_date - 1 and insert a new record. Then the same
3640 -- placement will be repeating twice with a different datetrack period
3641 -- We need to combine these records.
3642 for rec_past_spp in csr_past_spp_details(l_placement_id => p_placement_id) loop
3643 --
3644 if rec_past_spp.effective_end_date <> hr_api.g_eot then
3645 l_object_version_number := rec_past_spp.object_version_number;
3646 hr_sp_placement_api.delete_spp(
3647 p_effective_date => p_validation_start_date - 1
3648 ,p_datetrack_mode => hr_api.g_delete_next_change
3649 ,p_placement_id => p_placement_id
3650 ,p_object_version_number => l_object_version_number
3651 ,p_effective_start_date => l_effective_start_date
3652 ,p_effective_end_date => l_effective_end_date);
3653 if g_debug then
3654 hr_utility.set_location(l_proc, 130);
3655 end if;
3656 --
3657 end if;
3658 --
3659 end loop;
3660 --
3661 end if;
3662 --
3663 end if;
3664 --
3665 close csr_future_records;
3666 --
3667 end if; -- End of Grade change check
3668 --
3669 end if; -- End of assignment details found
3670 --
3671 if csr_asg_details%isopen then close csr_asg_details; end if;
3672 --
3673 else
3674 --
3675 open csr_past_spp_records(l_placement_id => p_placement_id);
3676 fetch csr_past_spp_records into l_previous_ovn;
3677 if csr_past_spp_records%found then
3678 --
3679 if g_debug then
3680 hr_utility.set_location(l_proc, 140);
3681 hr_utility.set_location(l_proc||' ovn ='||l_previous_ovn, 150);
3682 end if;
3683 hr_sp_placement_api.delete_spp(
3684 p_effective_date => p_validation_start_date - 1
3685 ,p_datetrack_mode => hr_api.g_delete
3686 ,p_placement_id => p_placement_id
3687 ,p_object_version_number => l_previous_ovn
3688 ,p_effective_start_date => l_effective_start_date
3689 ,p_effective_end_date => l_effective_end_date);
3690 -- Setting the warning parameter
3691 l_del_end_future_spp := true;
3692 if g_debug then
3693 hr_utility.set_location(l_proc, 160);
3694 end if;
3695 --
3696 end if;
3697 --
3698 close csr_past_spp_records;
3699 --
3700 end if; -- End of Pay Scale defined check
3701 --
3702 end if; -- End of current SPP exists
3703 --
3704 -- Cleanup of all invalid steps(if any)
3705 cleanup_spp(p_assignment_id => p_assignment_id
3706 ,p_datetrack_mode => p_datetrack_mode
3707 ,p_validation_start_date => p_validation_start_date
3708 ,p_del_end_future_spp => l_del_end_future_spp);
3709 -- Setting the out parameter
3710 p_del_end_future_spp := l_del_end_future_spp;
3711 if g_debug then
3712 hr_utility.set_location('Leaving : '||l_proc, 999);
3713 end if;
3714 --
3715 end update_override_spp;
3716 --
3717 -- ----------------------------------------------------------------------------
3718 -- |--------------------------< CLOSE_SPP_RECORDS >---------------------------|
3719 -- ----------------------------------------------------------------------------
3720 --
3721 procedure close_spp_records
3722 (p_assignment_id in per_all_assignments_f.assignment_id%Type
3723 ,p_placement_id in per_spinal_point_placements_f.placement_id%Type
3724 ,p_datetrack_mode in varchar2
3725 ,p_validation_start_date in date
3726 ,p_object_version_number in number
3727 ,p_current_spp_exist in boolean
3728 ,p_del_end_future_spp out nocopy boolean) is
3729 --
3730 -- Declare Local Variables
3731 l_effective_start_date date;
3732 l_effective_end_date date;
3733 l_del_end_future_spp boolean := false;
3734 l_dummy_id per_spinal_point_placements_f.placement_id%Type;
3735 l_object_version_number per_spinal_point_placements_f.object_version_number%Type
3736 := p_object_version_number;
3737 --
3738 l_proc varchar(72) := g_package||'close_spp_records';
3739 -- Checks to see if future rows exist for the Placement_id.
3740 cursor csr_spp_future_records is
3741 select spp.placement_id
3742 from per_spinal_point_placements_f spp
3743 where spp.assignment_id = p_assignment_id
3744 and spp.placement_id = p_placement_id
3745 and spp.effective_start_date > p_validation_start_date;
3746 -- Cursor to see if any past Grade Step rows exist for thie given assignment.
3747 cursor csr_past_spp_records is
3748 select spp.object_version_number
3749 from per_spinal_point_placements_f spp
3750 where spp.placement_id = p_placement_id
3751 and spp.effective_start_date < p_validation_start_date
3752 order by effective_start_date desc;
3753 --
3754 begin
3755 --
3756 if g_debug then
3757 hr_utility.set_location('Entering : '||l_proc, 10);
3758 end if;
3759 -- Assignment record is having some SPP records for this DT period
3760 if p_current_spp_exist then
3761 -- Check for the uniqueness of Step placement id
3762 chk_valid_placement_id(p_assignment_id => p_assignment_id
3763 ,p_placement_id => p_placement_id
3764 ,p_validation_start_date => p_validation_start_date);
3765 if g_debug then
3766 hr_utility.set_location(l_proc, 20);
3767 end if;
3768 -- Check for future records SPP records if any for the same placement id
3769 open csr_spp_future_records;
3770 fetch csr_spp_future_records into l_dummy_id;
3771 if csr_spp_future_records%found then
3772 if g_debug then
3773 hr_utility.set_location(l_proc, 30);
3774 end if;
3775 -- If the future SPP changes are existing after the validation_start_date
3776 -- then we need to delete all future changes
3777 hr_sp_placement_api.delete_spp(
3778 p_effective_date => p_validation_start_date
3779 ,p_datetrack_mode => hr_api.g_future_change
3780 ,p_placement_id => p_placement_id
3781 ,p_object_version_number => l_object_version_number
3782 ,p_effective_start_date => l_effective_start_date
3783 ,p_effective_end_date => l_effective_end_date);
3784 -- Setting the warning parameter
3785 l_del_end_future_spp := true;
3786 --
3787 if g_debug then
3788 hr_utility.set_location(l_proc, 40);
3789 end if;
3790 --
3791 end if;
3792 --
3793 close csr_spp_future_records;
3794 -- End dating the existing Grade step placement
3795 open csr_past_spp_records;
3796 fetch csr_past_spp_records into l_object_version_number;
3797 if csr_past_spp_records%found then
3798 --
3799 if g_debug then
3800 hr_utility.set_location(l_proc, 50);
3801 hr_utility.set_location(l_proc||' ovn ='||l_object_version_number, 60);
3802 end if;
3803 --
3804 hr_sp_placement_api.delete_spp(
3805 p_effective_date => p_validation_start_date - 1
3806 ,p_datetrack_mode => hr_api.g_delete
3807 ,p_placement_id => p_placement_id
3808 ,p_object_version_number => l_object_version_number
3809 ,p_effective_start_date => l_effective_start_date
3810 ,p_effective_end_date => l_effective_end_date);
3811 -- Setting the warning parameter
3812 l_del_end_future_spp := true;
3813 --
3814 if g_debug then
3815 hr_utility.set_location(l_proc, 70);
3816 end if;
3817 --
3818 end if;
3819 --
3820 end if;
3821 -- Cleanup of all invalid steps(if any)
3822 cleanup_spp(p_assignment_id => p_assignment_id
3823 ,p_datetrack_mode => p_datetrack_mode
3824 ,p_validation_start_date => p_validation_start_date
3825 ,p_del_end_future_spp => l_del_end_future_spp);
3826 -- Setting the out parameter
3827 p_del_end_future_spp := l_del_end_future_spp;
3828 --
3829 if g_debug then
3830 hr_utility.set_location('Leaving : '||l_proc, 99);
3831 end if;
3832 --
3833 end close_spp_records;
3834 --
3835 -- ----------------------------------------------------------------------------
3836 -- |---------------------------< CORRECTION_SPP >-----------------------------|
3837 -- ----------------------------------------------------------------------------
3838 --
3839 procedure correction_spp(
3840 p_assignment_id number
3841 ,p_placement_id number
3842 ,p_grade_id number
3843 ,p_min_step_id number
3844 ,p_validation_start_date date
3845 ,p_validation_end_date date
3846 ,p_del_end_future_spp in out nocopy boolean ) IS
3847 --
3848 -- Local variables
3849 --
3850 l_placement_id number;
3851 l_effective_start_date date;
3852 l_effective_end_date date;
3853 l_effective_date date;
3854 l_datetrack_mode varchar2(30);
3855 l_del_end_future_spp boolean := false;
3856 l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
3857 l_vsd_continues_spp_exists boolean :=false;
3858 l_ved_continues_spp_exists boolean :=false;
3859 l_min_step_id number;
3860 l_exists varchar2(1);
3861 --
3862 l_proc varchar2(72) := g_package||'correction_spp';
3863 --
3864 -- check if any spp is continues at the Validation Start Date.
3865 --
3866 cursor csr_vsd_continues_spp IS
3867 select spp.placement_id,spp.effective_start_date
3868 ,spp.effective_end_date, spp.object_version_number
3869 from per_spinal_point_placements_f spp
3870 where spp.assignment_id = p_assignment_id
3871 and spp.effective_start_date < p_validation_start_date
3872 -- and spp.effective_end_date > p_validation_start_date;
3873 and spp.effective_end_date >= p_validation_start_date;-- fix for the bug5203227
3874 --
3875 -- check if any spp is continues at the Validation End Date.
3876 --
3877 cursor csr_ved_continues_spp IS
3878 select spp.placement_id,spp.effective_start_date
3879 ,spp.effective_end_date, spp.object_version_number
3880 from per_spinal_point_placements_f spp
3881 where spp.assignment_id = p_assignment_id
3882 and spp.effective_start_date < p_validation_end_date
3883 and spp.effective_end_date > p_validation_end_date;
3884 --
3885 -- select the placement records in the validation period.
3886 --
3887 cursor csr_spps_in_validation_period IS
3888 select spp.placement_id,spp.effective_start_date
3889 ,spp.effective_end_date, spp.object_version_number
3890 from per_spinal_point_placements_f spp
3891 where spp.assignment_id = p_assignment_id
3892 and spp.effective_start_date between p_validation_start_date and p_validation_end_date
3893 order by effective_end_date;
3894 --
3895 cursor csr_next_spp(p_date date) IS
3896 select placement_id, effective_start_date,
3897 effective_end_date, object_version_number
3898 from per_spinal_point_placements_f
3899 where placement_id = p_placement_id
3900 -- and effective_start_date >= p_date;--fix for bug 5067855 .
3901 and effective_start_date = p_date; -- fix for the bug 5306697 .
3902 --
3903 -- Check for future spp dt records.
3904 --
3905 cursor csr_future_spp_exists(p_date date) IS
3906 select 'Y'
3907 from per_spinal_point_placements_f
3908 where placement_id = p_placement_id
3909 and effective_start_date > p_date;
3910 --
3911 begin
3912 --
3913 if g_debug then
3914 hr_utility.set_location('Entering : '||l_proc, 10);
3915 end if;
3916 --
3917 l_min_step_id := p_min_step_id;
3918 --
3919 open csr_ved_continues_spp;
3920 fetch csr_ved_continues_spp into
3921 l_placement_id,l_effective_start_date
3922 ,l_effective_end_date,l_object_version_number;
3923 if csr_ved_continues_spp%found then
3924 --
3925 --
3926 l_datetrack_mode := hr_api.g_update;
3927 --
3928 open csr_future_spp_exists(p_validation_end_date);
3929 fetch csr_future_spp_exists into l_exists;
3930 if csr_future_spp_exists%found then
3931 --
3932 l_datetrack_mode := hr_api.g_update_change_insert;
3933 --
3934 end if;
3935 --
3936 close csr_future_spp_exists;
3937
3938 -- continues placement record found on validation end date.
3939 -- Therefore simply break it on validation end date.
3940 --
3941 hr_utility.set_location('ved spp found', 20);
3942 hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3943 --
3944 l_del_end_future_spp := TRUE;
3945 --
3946 hr_sp_placement_api.update_spp(
3947 p_effective_date => p_validation_end_date+1
3948 ,p_datetrack_mode => l_datetrack_mode
3949 ,p_placement_id => l_placement_id
3950 ,p_object_version_number => l_object_version_number
3951 ,p_effective_start_date => l_effective_start_date
3952 ,p_effective_end_date => l_effective_end_date);
3953 --
3954 end if;
3955 close csr_ved_continues_spp;
3956 --
3957 open csr_vsd_continues_spp;
3958 fetch csr_vsd_continues_spp into
3959 l_placement_id,l_effective_start_date
3960 ,l_effective_end_date,l_object_version_number;
3961 if csr_vsd_continues_spp%found then
3962 --
3963 --
3964 l_datetrack_mode := hr_api.g_update;
3965 --
3966 open csr_future_spp_exists(p_validation_start_date);
3967 fetch csr_future_spp_exists into l_exists;
3968 if csr_future_spp_exists%found then
3969 --
3970 l_datetrack_mode := hr_api.g_update_change_insert;
3971 --
3972 end if;
3973 --
3974 close csr_future_spp_exists;
3975 --
3976 -- continues placement record found on validation start date.
3977 -- Therefore simply break it on validation start date.
3978 --
3979 hr_utility.set_location('vsd spp found', 20);
3980 hr_utility.set_location('dt mode :'||l_datetrack_mode, 20);
3981 --
3982 l_del_end_future_spp := TRUE;
3983 --
3984 hr_sp_placement_api.update_spp(
3985 p_effective_date => p_validation_start_date
3986 ,p_datetrack_mode => l_datetrack_mode
3987 ,p_placement_id => l_placement_id
3988 ,p_step_id => l_min_step_id
3989 ,p_object_version_number => l_object_version_number
3990 ,p_effective_start_date => l_effective_start_date
3991 ,p_effective_end_date => l_effective_end_date);
3992 --
3993 end if;
3994 close csr_vsd_continues_spp;
3995 --
3996 -- Check for the uniqueness of Step placement id
3997 --
3998 chk_valid_placement_id(p_assignment_id => p_assignment_id
3999 ,p_placement_id => p_placement_id
4000 ,p_validation_start_date => p_validation_start_date);
4001 --
4002 hr_utility.set_location(l_proc,30);
4003 -- Now starting from the first spp record in the validation period, perform
4004 -- DELETE-NEXT-CHANGE to make all the spp records in the validation
4005 -- period as a single record.
4006 --
4007 -- Get the first record details in the validation period.
4008 --
4009 hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
4010 --
4011 --fix for bug 5067855 starts here.
4012
4013 open csr_next_spp(p_validation_start_date);
4014
4015 LOOP
4016 fetch csr_next_spp into l_placement_id, l_effective_start_date,
4017 l_effective_end_date, l_object_version_number;
4018
4019 hr_utility.set_location(l_proc,40);
4020 hr_utility.set_location('l_object_version_number'||l_object_version_number,199);
4021 hr_utility.set_location('l_effective_start_date'||l_effective_start_date,199);
4022 hr_utility.set_location('l_effective_end_date'||l_effective_end_date,199);
4023
4024 if (l_effective_end_date >= p_validation_end_date or csr_next_spp%notfound ) then
4025 --
4026 hr_utility.set_location(l_proc,50);
4027 close csr_next_spp;
4028 exit;
4029 --
4030 end if;
4031
4032 --fix for bug 5067855 ends here.
4033 --
4034 -- For further safety, perform the DNC only when the spp eed is
4035 -- less than the validation end date.
4036 --
4037 hr_utility.set_location(l_proc,60);
4038 if l_effective_end_date < p_validation_end_date then
4039 --
4040 -- ADD check to see if the SPP is not end dated in
4041 -- between the validation dates. DNC which will open up the end dated SPP.
4042 --
4043 hr_utility.set_location('performing delete_spp',70);
4044 hr_utility.set_location('l_effective_start_date'||l_effective_start_date,399);
4045 --
4046 l_effective_date := l_effective_start_date;
4047 hr_sp_placement_api.delete_spp(
4048 p_effective_date => l_effective_date
4049 ,p_datetrack_mode => hr_api.g_delete_next_change
4050 ,p_placement_id => l_placement_id
4051 ,p_object_version_number => l_object_version_number
4052 ,p_effective_start_date => l_effective_start_date
4053 ,p_effective_end_date => l_effective_end_date);
4054 --
4055 l_del_end_future_spp := TRUE;
4056 --
4057 hr_utility.set_location('l_object_version_number'||l_object_version_number,299);
4058 hr_utility.set_location('l_effective_start_date'||l_effective_start_date,299);
4059 hr_utility.set_location('l_effective_end_date'||l_effective_end_date,299);
4060 --
4061 end if;
4062 --
4063 hr_utility.set_location(l_proc,90);
4064 END LOOP;
4065 --
4066 -- Now, update the spp in the validation period in CORRECTION mode.
4067 --
4068 -- fix for the bug 5160851
4069 -- added the following if condition.
4070 if (l_placement_id is not null ) then
4071 hr_sp_placement_api.update_spp(
4072 p_effective_date => p_validation_start_date
4073 ,p_datetrack_mode => hr_api.g_correction
4074 ,p_placement_id => l_placement_id
4075 ,p_step_id => l_min_step_id
4076 ,p_object_version_number => l_object_version_number
4077 ,p_effective_start_date => l_effective_start_date
4078 ,p_effective_end_date => l_effective_end_date);
4079 --
4080 end if;
4081 p_del_end_future_spp := l_del_end_future_spp;
4082 --
4083 if g_debug then
4084 hr_utility.set_location('Leaving : '||l_proc, 99);
4085 end if;
4086 --
4087 end correction_spp;
4088 --
4089 -- ----------------------------------------------------------------------------
4090 -- |----------------------< DELETE_FUTURE_SPP_RECORDS >-----------------------|
4091 -- ----------------------------------------------------------------------------
4092 --
4093 PROCEDURE delete_future_spp_records
4094 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
4095 ,p_datetrack_mode IN VARCHAR2
4096 ,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
4097 ,p_object_version_number IN per_spinal_point_placements_f.object_version_number%TYPE
4098 ,p_effective_date IN DATE) IS
4099 --
4100 -- Declare Local Variables
4101 --
4102 l_proc VARCHAR2(72) := g_package||'delete_future_spp_records';
4103 l_effective_date DATE;
4104 l_effective_start_date DATE;
4105 l_effective_end_date DATE;
4106 l_previous_end_date DATE;
4107 l_previous_id per_spinal_point_placements_f.placement_id%TYPE;
4108 l_previous_ovn per_spinal_point_placements_f.object_version_number%TYPE;
4109 l_object_version_number per_spinal_point_placements_f.object_version_number%TYPE;
4110 l_placement_id per_spinal_point_placements_f.placement_id%TYPE;
4111 --
4112 -- Fetch future SPP_Records
4113 --
4114 CURSOR csr_future_spp_records IS
4115 SELECT spp.placement_id,
4116 spp.object_version_number,
4117 spp.effective_start_date
4118 FROM per_spinal_point_placements_f spp
4119 WHERE spp.assignment_id = p_assignment_id
4120 AND spp.effective_start_date > p_effective_date
4121 AND spp.placement_id <> p_placement_id
4122 ORDER BY placement_id;
4123 --
4124 -- Cursor to see if past rows exist.
4125 --
4126 CURSOR csr_past_spp_records IS
4127 SELECT spp.placement_id
4128 FROM per_spinal_point_placements_f spp
4129 WHERE spp.placement_id = p_placement_id
4130 AND spp.effective_start_date < p_effective_date;
4131 --
4132 CURSOR csr_previous_spp_record IS
4133 SELECT spp.object_version_number,
4134 spp.effective_end_date
4135 FROM per_spinal_point_placements_f spp
4136 WHERE spp.placement_id = p_placement_id
4137 AND spp.effective_start_date < p_effective_date
4138 ORDER BY spp.effective_end_date desc;
4139 --
4140 BEGIN
4141 --
4142 hr_utility.set_location('Entering : '||l_proc, 10);
4143 --
4144 IF p_datetrack_mode = hr_api.g_correction THEN
4145 --
4146 hr_utility.set_location(l_proc, 20);
4147 --
4148 -- Check for previous SPP records
4149 --
4150 OPEN csr_past_spp_records;
4151 FETCH csr_past_spp_records INTO l_placement_id;
4152 --
4153 -- If there are no previous SPP records
4154 -- then ZAP the SPP record.
4155 --
4156 IF csr_past_spp_records%NOTFOUND THEN
4157 --
4158 hr_utility.set_location(l_proc, 30);
4159 --
4160 l_object_version_number := p_object_version_number;
4161 --
4162 hr_sp_placement_api.delete_spp
4163 (p_effective_date => p_effective_date
4164 ,p_datetrack_mode => hr_api.g_zap
4165 ,p_placement_id => p_placement_id
4166 ,p_object_version_number => l_object_version_number
4167 ,p_effective_start_date => l_effective_start_date
4168 ,p_effective_end_date => l_effective_end_date);
4169 --
4170 -- If there are previous SPP records
4171 -- then perform a DT Delete.
4172 --
4173 ELSE
4174 --
4175 hr_utility.set_location(l_proc, 40);
4176 --
4177 l_object_version_number := p_object_version_number;
4178 --
4179 OPEN csr_previous_spp_record;
4180 FETCH csr_previous_spp_record INTO l_previous_ovn,
4181 l_previous_end_date;
4182 --
4183 hr_utility.set_location(l_proc||l_previous_ovn||'/'||l_previous_end_date, 50);
4184 --
4185 hr_sp_placement_api.delete_spp
4186 (p_effective_date => l_previous_end_date
4187 ,p_datetrack_mode => hr_api.g_delete
4188 ,p_placement_id => p_placement_id
4189 ,p_object_version_number => l_previous_ovn
4190 ,p_effective_start_date => l_effective_start_date
4191 ,p_effective_end_date => l_effective_end_date);
4192 --
4193 END IF;
4194 --
4195 -- If datetrack mode is not CORRECTION then
4196 --
4197 ELSE
4198 --
4199 hr_utility.set_location(l_proc, 50);
4200 --
4201 l_object_version_number := p_object_version_number;
4202 --
4203 hr_sp_placement_api.delete_spp
4204 (p_effective_date => p_effective_date -1
4205 ,p_datetrack_mode => hr_api.g_delete
4206 ,p_placement_id => p_placement_id
4207 ,p_object_version_number => l_object_version_number
4208 ,p_effective_start_date => l_effective_start_date
4209 ,p_effective_end_date => l_effective_end_date);
4210 --
4211 END IF;
4212 --
4213 hr_utility.set_location(l_proc, 60);
4214 --
4215 l_previous_id := -1;
4216 --
4217 -- Delete all future SPP records that have
4218 -- a different placement_id do the SPP record delete above
4219 --
4220 FOR c_future_spp IN csr_future_spp_records LOOP
4221 --
4222 hr_utility.set_location(l_proc||'/ pl_id = '||c_future_spp.placement_id, 70);
4223 hr_utility.set_location(l_proc||'/ ovn = '||c_future_spp.object_version_number, 71);
4224 --
4225 -- If the record retrieved has a different placement id
4226 -- then perform a ZAP on this record. If the ID is the same
4227 -- as the previous id then do nothing as this record has already
4228 -- been deleted.
4229 --
4230 IF l_previous_id <> c_future_spp.placement_id THEN
4231 --
4232 hr_utility.set_location(l_proc, 80);
4233 --
4234 l_previous_id := c_future_spp.placement_id;
4235 l_object_version_number := c_future_spp.object_version_number;
4236 --
4237 hr_sp_placement_api.delete_spp
4238 (p_effective_date => c_future_spp.effective_start_date
4239 ,p_datetrack_mode => hr_api.g_zap
4240 ,p_placement_id => c_future_spp.placement_id
4241 ,p_object_version_number => l_object_version_number
4242 ,p_effective_start_date => l_effective_start_date
4243 ,p_effective_end_date => l_effective_end_date);
4244 --
4245 END IF;
4246 --
4247 END LOOP;
4248 --
4249 hr_utility.set_location('Leaving : '||l_proc, 999);
4250 --
4251 END delete_future_spp_records;
4252 --
4253 -- ----------------------------------------------------------------------------
4254 -- |--------------------------< MAINTAIN_SPP_ASG >----------------------------|
4255 -- ----------------------------------------------------------------------------
4256 --
4257 procedure maintain_spp_asg
4258 (p_assignment_id in number
4259 ,p_datetrack_mode in varchar2
4260 ,p_validation_start_date in date
4261 ,p_validation_end_date in date
4262 ,p_grade_id in number
4263 ,p_spp_delete_warning out nocopy boolean) is
4264 --
4265 -- Declare local variables
4266 l_effective_start_date date;
4267 l_effective_end_date date;
4268 l_spp_eff_start_date date;
4269 l_spp_eff_end_date date;
4270 l_min_spp_date date;
4271 l_proc varchar2(72) := g_package||'maintain_spp_asg';
4272 l_datetrack_mode varchar2(30);
4273 l_current_spp_exist boolean := false;
4274 l_future_spp_exist boolean := false;
4275 l_pay_scale_defined boolean := false;
4276 l_placement_id per_spinal_point_placements_f.placement_id%Type;
4277 l_object_version_number per_spinal_point_placements_f.object_version_number%Type;
4278 l_min_step_id per_spinal_point_steps_f.step_id%Type;
4279 l_grade_spine_id per_grade_spines_f.grade_spine_id%Type;
4280 l_dummy_id per_spinal_point_placements_f.placement_id%Type;
4281 -- This warning variable will be used, whenever system internaly delete's
4282 -- any future dated SPP records or End Date an SPP record whcih is having
4283 -- effective_end_date greater than the validation end date. This warning
4284 -- will be set depends on the DT Mode and the SPP records.
4285 l_del_end_future_spp boolean := false;
4286 -- Cursor to lock all the current assignment's SPPs. Also this cursor will
4287 -- ensure that any SPP record is available for the current assignment.
4288 -- We need this check for ZAP mode, all other DT modes we need a date
4289 -- effective check and this will be done by cursor csr_spp_details.
4290 cursor csr_lock_spp_rows is
4291 select spp.placement_id
4292 from per_spinal_point_placements_f spp
4293 where spp.assignment_id = p_assignment_id
4294 for update nowait;
4295 -- Checks to see if any future Grade Step rows exist for the given assignment
4296 cursor csr_future_records is
4297 select spp.placement_id
4298 from per_spinal_point_placements_f spp
4299 where spp.assignment_id = p_assignment_id
4300 and spp.effective_start_date > p_validation_start_date;
4301 -- Checks to see if future rows exist for the Placement_id.
4302 cursor csr_spp_future_records(p_placement_id number) is
4303 select spp.placement_id
4304 from per_spinal_point_placements_f spp
4305 where spp.assignment_id = p_assignment_id
4306 and spp.placement_id = p_placement_id
4307 and spp.effective_start_date > p_validation_start_date;
4308 -- Cursor to retrive the current Grade Step placement details
4309 cursor csr_spp_details is
4310 select spp.placement_id,
4311 spp.object_version_number,
4312 spp.effective_start_date,
4313 spp.effective_end_date
4314 from per_spinal_point_placements_f spp
4315 where spp.assignment_id = p_assignment_id
4316 and p_validation_start_date between spp.effective_start_date
4317 and spp.effective_end_date;
4318 -- Cursor to retrieve the first step (Minimum Grade Step) on the pay scale
4319 -- for the new grade
4320 cursor csr_new_grade_scale is
4321 select sps.step_id
4322 from per_grade_spines_f pgs,
4323 per_spinal_point_steps_f sps
4324 where sps.grade_spine_id = pgs.grade_spine_id
4325 and p_validation_start_date between sps.effective_start_date
4326 and sps.effective_end_date
4327 and pgs.grade_id = p_grade_id
4328 and p_validation_start_date between pgs.effective_start_date
4329 and pgs.effective_end_date
4330 and sps.sequence in (
4331 select min(sps2.sequence)
4332 from per_spinal_point_steps_f sps2
4333 where sps2.grade_spine_id = pgs.grade_spine_id
4334 and p_validation_start_date between sps2.effective_start_date
4335 and sps2.effective_end_date);
4336 -- Cursor to check if the new Grade has been linked to a Pay Scale at any time
4337 cursor csr_grade_pay_scale_defined is
4338 select grade_spine_id
4339 from per_grade_spines_f pgs
4340 where grade_id = p_grade_id;
4341 -- Cursor to get the minimum effective_start_date of the SPP records
4342 cursor csr_min_spp_date is
4343 select min(spp.effective_start_date)
4344 from per_spinal_point_placements_f spp
4345 where spp.assignment_id = p_assignment_id;
4346 --
4347 begin
4348 --
4349 if g_debug then
4350 hr_utility.set_location('Entering:'|| l_proc, 10);
4351 end if;
4352 -- Validation in addition to Table Handlers
4353 -- Check that all mandatory arguments are not null.
4354 hr_api.mandatory_arg_error
4355 (p_api_name => l_proc,
4356 p_argument => 'assignment_id',
4357 p_argument_value => p_assignment_id);
4358 --
4359 hr_api.mandatory_arg_error
4360 (p_api_name => l_proc,
4361 p_argument => 'datetrack_mode',
4362 p_argument_value => p_datetrack_mode);
4363 --
4364 hr_api.mandatory_arg_error
4365 (p_api_name => l_proc,
4366 p_argument => 'validation_start_date',
4367 p_argument_value => p_validation_start_date);
4368 --
4369 hr_api.mandatory_arg_error
4370 (p_api_name => l_proc,
4371 p_argument => 'validation_end_date',
4372 p_argument_value => p_validation_end_date);
4373 -- Process Logic
4374 if g_debug then
4375 hr_utility.set_location(l_proc||' p_assignment_id = '||p_assignment_id, 15);
4376 hr_utility.set_location(l_proc||' p_datetrack_mode = '||p_datetrack_mode, 16);
4377 hr_utility.set_location(l_proc||' p_val_st_date = '||p_validation_start_date, 17);
4378 hr_utility.set_location(l_proc||' p_val_end_date = '||p_validation_end_date, 18);
4379 end if;
4380 -- Setting this variable as TRUE, because when we call SPP rhi, the validation proc
4381 -- per_spp_bus.chk_future_asg_changes should not be executed
4382 g_called_from_spp_asg := true;
4383 -- Obtaining Lock on datetracked instance of any SPPs associated with this
4384 -- assignment.
4385 open csr_lock_spp_rows;
4386 fetch csr_lock_spp_rows into l_dummy_id;
4387 -- Ensuring that atleast one SPP record is available to maintain
4388 if csr_lock_spp_rows%found then
4389 --
4390 close csr_lock_spp_rows;
4391 --
4392 if g_debug then
4393 hr_utility.set_location(l_proc, 20);
4394 end if;
4395 -- DT mode ZAP needs to processed seperately. Date effective SPP records
4396 -- check may not be valid for all the case.
4397 -- If the datetrack mode is ZAP,(This DT mode is allowed only for
4398 -- secondary assignment) then removing all the SPP records pertaining
4399 -- to this assignment from the database.
4400 if p_datetrack_mode = hr_api.g_zap then
4401 --
4402 if g_debug then
4403 hr_utility.set_location(l_proc, 30);
4404 hr_utility.set_location(l_proc||' DT Mode = '||p_datetrack_mode, 31);
4405 end if;
4406 --
4407 spp_zap (p_assignment_id => p_assignment_id);
4408 -- All other DT mode, Date effective check that there has been a grade step
4409 -- created for this assignment, Needs to maintain SPP records only if there
4410 -- is Grade Step for this assignment.
4411 else
4412 --
4413 open csr_spp_details;
4414 fetch csr_spp_details into l_placement_id ,l_object_version_number
4415 ,l_spp_eff_start_date ,l_spp_eff_end_date;
4416 if csr_spp_details%found then
4417 --
4418 l_current_spp_exist := true;
4419 --
4420 if g_debug then
4421 hr_utility.set_location(l_proc||' Current SPP record exist', 32);
4422 end if;
4423 -- If there is NO current SPP records exists, then we need to
4424 -- check for future SPP records
4425 else
4426 --
4427 open csr_future_records;
4428 fetch csr_future_records into l_dummy_id;
4429 if csr_future_records%found then
4430 --
4431 l_future_spp_exist := true;
4432 --
4433 if g_debug then
4434 hr_utility.set_location(l_proc||' Future SPP record exist', 33);
4435 end if;
4436 --
4437 end if;
4438 --
4439 end if;
4440 --
4441 if csr_future_records%isopen then close csr_future_records; end if;
4442 if csr_spp_details%isopen then close csr_spp_details; end if;
4443 -- Grade Step exists for current or future assignment then, maintain the
4444 -- spinal point information.
4445 if l_current_spp_exist or l_future_spp_exist then
4446 -- Checking the datetarck mode selected by the user, and performing
4447 -- the process accordingly
4448 if p_datetrack_mode = hr_api.g_delete_next_change then
4449 --
4450 delete_next_change_spp(
4451 p_assignment_id => p_assignment_id
4452 ,p_placement_id => l_placement_id
4453 ,p_grade_id => p_grade_id
4454 ,p_datetrack_mode => p_datetrack_mode
4455 ,p_validation_start_date => p_validation_start_date
4456 ,p_validation_end_date => p_validation_end_date
4457 ,p_del_end_future_spp => l_del_end_future_spp);
4458 if g_debug then
4459 hr_utility.set_location(l_proc, 60);
4460 end if;
4461 --
4462 elsif p_datetrack_mode = hr_api.g_future_change then
4463 --
4464 future_change_spp(
4465 p_assignment_id => p_assignment_id
4466 ,p_placement_id => l_placement_id
4467 ,p_datetrack_mode => p_datetrack_mode
4468 ,p_validation_start_date => p_validation_start_date
4469 ,p_del_end_future_spp => l_del_end_future_spp);
4470 if g_debug then
4471 hr_utility.set_location(l_proc, 80);
4472 end if;
4473 -- Needs to perform DT modes like CORRECTION, UPDATE, UPDATE_CHANGE_INSERT,
4474 -- UPDATE_OVERRIDE, DELETE only if there is an SPP record exist for current DT
4475 else
4476 -- If a valid new grade is passed.
4477 If p_grade_id is not null then
4478 -- Check if the new Grade has a Pay Scale defined for it
4479
4480 open csr_grade_pay_scale_defined;
4481 fetch csr_grade_pay_scale_defined into l_grade_spine_id;
4482 if csr_grade_pay_scale_defined%found then
4483 --
4484 l_pay_scale_defined := true;
4485 if g_debug then
4486 hr_utility.set_location(l_proc, 100);
4487 end if;
4488 open csr_new_grade_scale;
4489 fetch csr_new_grade_scale into l_min_step_id;
4490 -- If no steps exists on the effective date then raise an error
4491 if csr_new_grade_scale%notfound then
4492 --
4493 close csr_new_grade_scale;
4494 --
4495 hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4496 hr_utility.raise_error;
4497 --
4498 end if;
4499 --
4500 close csr_new_grade_scale;
4501 --
4502 if g_debug then
4503 hr_utility.set_location(l_proc||'/l_min_step_id = '||l_min_step_id, 250);
4504 end if;
4505 --
4506 /* else
4507 --
4508 hr_utility.set_message(800, 'HR_289829_NO_SPP_REC_FOR_EDATE');
4509 hr_utility.raise_error; */ /* commented for bug 6346478*/
4510
4511 --
4512 end if;
4513 --
4514 close csr_grade_pay_scale_defined;
4515 --
4516 End if;
4517 --
4518 if p_datetrack_mode = hr_api.g_update_override then
4519 --
4520 update_override_spp(
4521 p_assignment_id => p_assignment_id
4522 ,p_placement_id => l_placement_id
4523 ,p_datetrack_mode => p_datetrack_mode
4524 ,p_validation_start_date => p_validation_start_date
4525 ,p_validation_end_date => p_validation_end_date
4526 ,p_spp_eff_start_date => l_spp_eff_start_date
4527 ,p_grade_id => p_grade_id
4528 ,p_step_id => l_min_step_id
4529 ,p_object_version_number => l_object_version_number
4530 ,p_current_spp_exist => l_current_spp_exist
4531 ,p_pay_scale_defined => l_pay_scale_defined
4532 ,p_del_end_future_spp => l_del_end_future_spp);
4533 --
4534 elsif p_datetrack_mode in (hr_api.g_correction,
4535 hr_api.g_update_change_insert) then
4536 --
4537 If p_grade_id is not null then
4538 --
4539 correction_spp(
4540 p_assignment_id => p_assignment_id
4541 ,p_placement_id => l_placement_id
4542 ,p_grade_id => p_grade_id
4543 ,p_min_step_id => l_min_step_id
4544 ,p_validation_start_date => p_validation_start_date
4545 ,p_validation_end_date => p_validation_end_date
4546 ,p_del_end_future_spp => l_del_end_future_spp);
4547 --
4548 Else
4549 --
4550 -- Inform user there are steps that they will have to delete before correcting.
4551 --
4552 hr_utility.set_message(800, 'HR_50426_REM_STEP_BEF_REM_GRD');
4553 hr_utility.raise_error;
4554 End if;
4555 --
4556 elsif p_datetrack_mode = hr_api.g_update then
4557 -- Check for future records SPP records if any for the same
4558 -- placement id
4559 open csr_spp_future_records(l_placement_id);
4560 fetch csr_spp_future_records into l_dummy_id;
4561 --
4562 if csr_spp_future_records%found then
4563 -- If the Step placement record starts on the same day
4564 -- as the updated assignment record then change the
4565 -- date track mode to CORRECTION
4566 if l_spp_eff_start_date = p_validation_start_date then
4567 -- If the future SPP changes are existing and the
4568 -- validation_start_date is same as current SPP
4569 -- effective_start_date, then we need to delete
4570 -- all future changes
4571 hr_sp_placement_api.delete_spp(
4572 p_effective_date => p_validation_start_date
4573 ,p_datetrack_mode => hr_api.g_future_change
4574 ,p_placement_id => l_placement_id
4575 ,p_object_version_number => l_object_version_number
4576 ,p_effective_start_date => l_effective_start_date
4577 ,p_effective_end_date => l_effective_end_date);
4578 -- Setting the warning parameter
4579 l_del_end_future_spp := true;
4580 --
4581 if g_debug then
4582 hr_utility.set_location(l_proc, 275);
4583 end if;
4584 l_datetrack_mode := hr_api.g_correction;
4585 --
4586 else
4587 --
4588 l_datetrack_mode := hr_api.g_update_override;
4589 --
4590 end if;
4591 -- There are no future SPP records existing
4592 else
4593 -- If the step placement record starts on the same day
4594 -- as the updated assignment record then change the
4595 -- date track mode to CORRECTION
4596 if l_spp_eff_start_date = p_validation_start_date then
4597 --
4598 if g_debug then
4599 hr_utility.set_location(l_proc, 300);
4600 end if;
4601 l_datetrack_mode := hr_api.g_correction;
4602 --
4603 else
4604 --
4605 if g_debug then
4606 hr_utility.set_location(l_proc, 310);
4607 end if;
4608 l_datetrack_mode := hr_api.g_update;
4609 --
4610 end if;
4611 --
4612 end if;
4613 --
4614 close csr_spp_future_records;
4615 --
4616 if g_debug then
4617 hr_utility.set_location(l_proc||' DT Mode = '||l_datetrack_mode, 320);
4618 end if;
4619 --
4620 elsif p_datetrack_mode = hr_api.g_delete then
4621 --
4622 l_datetrack_mode := hr_api.g_delete;
4623 --
4624 end if;
4625 --
4626 if p_datetrack_mode in (hr_api.g_update, hr_api.g_delete) then
4627 -- Check that the effective date of the process is not less than the min
4628 -- effective start date for the spp record for the assignment
4629 -- If it is then the process will not be able to update the current step
4630 -- as there is none so raise an error
4631 open csr_min_spp_date;
4632 fetch csr_min_spp_date into l_min_spp_date;
4633 if l_min_spp_date > p_validation_start_date then
4634 --
4635 hr_utility.set_message(800, 'HR_289771_SPP_MIN_START_DATE');
4636 hr_utility.raise_error;
4637 --
4638 end if;
4639 --
4640 close csr_min_spp_date;
4641 --
4642 -- We need to end date the existing (current) grade step information if,
4643 -- 1) NO pay scale defined for the new grade (update mode)
4644 -- 2) Updating the assignment grade information with a Null Grade.
4645 -- 3) Hiring an applicant whose applicant assignment is not having
4646 -- a grade attached and updating the primary assignment. As a result
4647 -- primary assignment will get end dated and the new primary assignment
4648 -- (created from applicant assignment) will not be having a grade.
4649 -- In such case the previous grade step placement should get end dated.
4650 if not l_pay_scale_defined and p_datetrack_mode = hr_api.g_update then
4651 --
4652 close_spp_records(
4653 p_assignment_id => p_assignment_id
4654 ,p_placement_id => l_placement_id
4655 ,p_datetrack_mode => p_datetrack_mode
4656 ,p_validation_start_date => p_validation_start_date
4657 ,p_object_version_number => l_object_version_number
4658 ,p_current_spp_exist => l_current_spp_exist
4659 ,p_del_end_future_spp => l_del_end_future_spp);
4660 --
4661 else
4662 --
4663 hr_sp_placement_api.update_spp(
4664 p_effective_date => p_validation_start_date
4665 ,p_datetrack_mode => l_datetrack_mode
4666 ,p_placement_id => l_placement_id
4667 ,p_object_version_number => l_object_version_number
4668 ,p_step_id => l_min_step_id
4669 ,p_auto_increment_flag => 'N'
4670 ,p_reason => ''
4671 ,p_increment_number => NULL
4672 ,p_effective_start_date => l_effective_start_date
4673 ,p_effective_end_date => l_effective_end_date);
4674 --
4675 end if;
4676 --
4677 end if;
4678 --
4679 end if; -- End of Second inner DT Mode check
4680 --
4681 end if; -- End of Current or future SPP exist check
4682 --
4683 end if; -- End of First DT Mode check
4684 --
4685 end if; -- End of Lock
4686 --
4687 if csr_lock_spp_rows%isopen then close csr_lock_spp_rows; end if;
4688 -- Setting the out warning parameter(if any)
4689 p_spp_delete_warning := l_del_end_future_spp;
4690 -- Resetting this variable back as this will be used through SPP rhi's.
4691 -- The value should be FALSE, when maintain_app_asg is called through SPP rhi's.
4692 g_called_from_spp_asg := false;
4693 --
4694 if g_debug then
4695 hr_utility.set_location(' Leaving:'||l_proc, 999);
4696 end if;
4697 --
4698 exception
4699 --
4700 when others then
4701 --
4702 l_del_end_future_spp := false;
4703 -- Resetting this variable back as this will be used through SPP rhi's.
4704 -- The value should be FALSE, when maintain_app_asg is called through SPP rhi's.
4705 g_called_from_spp_asg := false;
4706 --
4707 raise;
4708 --
4709 --
4710 end maintain_spp_asg;
4711 --
4712 -- ----------------------------------------------------------------------------
4713 -- |----------------------< update_status_type_cwk_asg >----------------------|
4714 -- ----------------------------------------------------------------------------
4715 --
4716 procedure update_status_type_cwk_asg
4717 (p_effective_date in date
4718 ,p_datetrack_update_mode in varchar2
4719 ,p_assignment_id in number
4720 ,p_change_reason in varchar2 default hr_api.g_varchar2
4721 --
4722 ,p_object_version_number in out nocopy number
4723 ,p_expected_system_status in varchar2
4724 ,p_assignment_status_type_id in number default hr_api.g_number
4725 ,p_effective_start_date out nocopy date
4726 ,p_effective_end_date out nocopy date
4727 ) is
4728 --
4729 -- Declare cursors and local variables
4730 --
4731 -- Out variables
4732 --
4733 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
4734 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
4735 l_object_version_number per_assignments_f.object_version_number%TYPE;
4736 --
4737 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
4738 l_assignment_type per_assignments_f.assignment_type%TYPE;
4739 l_business_group_id per_assignments_f.business_group_id%TYPE;
4740 l_comment_id per_assignments_f.comment_id%TYPE;
4741 l_legislation_code per_business_groups.legislation_code%TYPE;
4742 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
4743 l_no_managers_warning boolean;
4744 l_other_manager_warning boolean;
4745 l_hourly_salaried_warning boolean;
4746 l_payroll_id_updated boolean;
4747 l_org_now_no_manager_warning boolean;
4748 l_validation_start_date per_assignments_f.effective_start_date%TYPE;
4749 l_validation_end_date per_assignments_f.effective_end_date%TYPE;
4750 l_proc varchar2(72):=
4751 g_package||'update_status_type_cwk_asg';
4752 --
4753 cursor csr_get_asg_dets is
4754 select asg.assignment_type
4755 , asg.business_group_id
4756 , bus.legislation_code
4757 from per_assignments_f asg
4758 , per_business_groups_perf bus
4759 where asg.assignment_id = p_assignment_id
4760 and p_effective_date between asg.effective_start_date
4761 and asg.effective_end_date
4762 and bus.business_group_id+0 = asg.business_group_id;
4763 --
4764 begin
4765 hr_utility.set_location('Entering:'|| l_proc, 1);
4766 --
4767 l_assignment_status_type_id := p_assignment_status_type_id;
4768 l_object_version_number := p_object_version_number;
4769 --
4770 -- Validation in addition to Table Handlers
4771 --
4772 if p_expected_system_status <> 'ACTIVE_CWK'
4773 and p_expected_system_status <> 'SUSP_CWK_ASG'
4774 then
4775 --
4776 hr_utility.set_location(l_proc, 10);
4777 --
4778 hr_utility.set_message(800, 'HR_289693_ASG_INV_EXP_STATUS');
4779 hr_utility.raise_error;
4780 end if;
4781 --
4782 hr_utility.set_location(l_proc, 20);
4783 --
4784 -- Get assignment details.
4785 --
4786 hr_api.mandatory_arg_error
4787 (p_api_name => l_proc,
4788 p_argument => 'assignment_id',
4789 p_argument_value => p_assignment_id);
4790 --
4791 hr_api.mandatory_arg_error
4792 (p_api_name => l_proc,
4793 p_argument => 'effective_date',
4794 p_argument_value => p_effective_date);
4795 --
4796 hr_utility.set_location(l_proc, 30);
4797 --
4798 open csr_get_asg_dets;
4799 fetch csr_get_asg_dets
4800 into l_assignment_type
4801 , l_business_group_id
4802 , l_legislation_code;
4803 --
4804 if csr_get_asg_dets%NOTFOUND then
4805 --
4806 hr_utility.set_location(l_proc, 40);
4807 --
4808 close csr_get_asg_dets;
4809 hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4810 hr_utility.raise_error;
4811 end if;
4812 --
4813 close csr_get_asg_dets;
4814 --
4815 hr_utility.set_location(l_proc, 50);
4816 --
4817 if l_assignment_type <> 'C' then
4818 --
4819 -- Assignment is not an employee assignment.
4820 --
4821 hr_utility.set_location(l_proc, 60);
4822 --
4823 hr_utility.set_message(800, 'HR_289616_ASG_NOT_CWK');
4824 hr_utility.raise_error;
4825 end if;
4826 --
4827 hr_utility.set_location(l_proc, 70);
4828 --
4829 -- Process Logic
4830 --
4831 -- If p_assignment_status_type_id is g_number then derive it's default value,
4832 -- otherwise validate it.
4833 --
4834 per_asg_bus1.chk_assignment_status_type
4835 (p_assignment_status_type_id => l_assignment_status_type_id
4836 ,p_business_group_id => l_business_group_id
4837 ,p_legislation_code => l_legislation_code
4838 ,p_expected_system_status => p_expected_system_status
4839 );
4840 --
4841 hr_utility.set_location(l_proc, 80);
4842 --
4843 -- Update employee assignment.
4844 --
4845 per_asg_upd.upd
4846 (p_assignment_id => p_assignment_id
4847 ,p_effective_start_date => l_effective_start_date
4848 ,p_effective_end_date => l_effective_end_date
4849 ,p_business_group_id => l_business_group_id
4850 ,p_assignment_status_type_id => l_assignment_status_type_id
4851 ,p_change_reason => p_change_reason
4852 ,p_comment_id => l_comment_id
4853 ,p_payroll_id_updated => l_payroll_id_updated
4854 ,p_other_manager_warning => l_other_manager_warning
4855 ,p_no_managers_warning => l_no_managers_warning
4856 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
4857 ,p_validation_start_date => l_validation_start_date
4858 ,p_validation_end_date => l_validation_end_date
4859 ,p_object_version_number => l_object_version_number
4860 ,p_effective_date => p_effective_date
4861 ,p_datetrack_mode => p_datetrack_update_mode
4862 ,p_validate => FALSE
4863 ,p_hourly_salaried_warning => l_hourly_salaried_warning
4864 );
4865 --
4866 hr_utility.set_location(l_proc, 90);
4867 --
4868 -- Set all output arguments
4869 --
4870 p_effective_start_date := l_effective_start_date;
4871 p_effective_end_date := l_effective_end_date;
4872 p_object_version_number := l_object_version_number;
4873 --
4874 hr_utility.set_location(' Leaving:'||l_proc, 200);
4875 end update_status_type_cwk_asg;
4876 --
4877 -- ----------------------------------------------------------------------------
4878 -- |----------------------< update_status_type_emp_asg >----------------------|
4879 -- ----------------------------------------------------------------------------
4880 --
4881 procedure update_status_type_emp_asg
4882 (p_effective_date in date
4883 ,p_datetrack_update_mode in varchar2
4884 ,p_assignment_id in number
4885 ,p_change_reason in varchar2 default hr_api.g_varchar2
4886 --
4887 ,p_object_version_number in out nocopy number
4888 ,p_expected_system_status in varchar2
4889 ,p_assignment_status_type_id in number default hr_api.g_number
4890 ,p_effective_start_date out nocopy date
4891 ,p_effective_end_date out nocopy date
4892 ) is
4893 --
4894 -- Declare cursors and local variables
4895 --
4896 -- Out variables
4897 --
4898 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
4899 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
4900 l_object_version_number per_assignments_f.object_version_number%TYPE;
4901 --
4902 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
4903 l_assignment_type per_assignments_f.assignment_type%TYPE;
4904 l_business_group_id per_assignments_f.business_group_id%TYPE;
4905 l_comment_id per_assignments_f.comment_id%TYPE;
4906 l_legislation_code per_business_groups.legislation_code%TYPE;
4907 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
4908 l_no_managers_warning boolean;
4909 l_other_manager_warning boolean;
4910 l_hourly_salaried_warning boolean;
4911 l_payroll_id_updated boolean;
4912 l_org_now_no_manager_warning boolean;
4913 l_validation_start_date per_assignments_f.effective_start_date%TYPE;
4914 l_validation_end_date per_assignments_f.effective_end_date%TYPE;
4915 l_proc varchar2(72):=
4916 g_package||'update_status_type_emp_asg';
4917 --
4918 cursor csr_get_asg_dets is
4919 select asg.assignment_type
4920 , asg.business_group_id
4921 , bus.legislation_code
4922 from per_assignments_f asg
4923 , per_business_groups_perf bus
4924 where asg.assignment_id = p_assignment_id
4925 and p_effective_date between asg.effective_start_date
4926 and asg.effective_end_date
4927 and bus.business_group_id+0 = asg.business_group_id;
4928 --
4929 begin
4930 hr_utility.set_location('Entering:'|| l_proc, 1);
4931 --
4932 l_assignment_status_type_id := p_assignment_status_type_id;
4933 l_object_version_number := p_object_version_number;
4934 --
4935 -- Validation in addition to Table Handlers
4936 --
4937 if p_expected_system_status <> 'ACTIVE_ASSIGN'
4938 and p_expected_system_status <> 'SUSP_ASSIGN'
4939 then
4940 --
4941 hr_utility.set_location(l_proc, 10);
4942 --
4943 hr_utility.set_message(801, 'HR_7947_ASG_INV_EXP_STATUS');
4944 hr_utility.raise_error;
4945 end if;
4946 --
4947 hr_utility.set_location(l_proc, 20);
4948 --
4949 -- Get assignment details.
4950 --
4951 hr_api.mandatory_arg_error
4952 (p_api_name => l_proc,
4953 p_argument => 'assignment_id',
4954 p_argument_value => p_assignment_id);
4955 --
4956 hr_api.mandatory_arg_error
4957 (p_api_name => l_proc,
4958 p_argument => 'effective_date',
4959 p_argument_value => p_effective_date);
4960 --
4961 hr_utility.set_location(l_proc, 30);
4962 --
4963 open csr_get_asg_dets;
4964 fetch csr_get_asg_dets
4965 into l_assignment_type
4966 , l_business_group_id
4967 , l_legislation_code;
4968 --
4969 if csr_get_asg_dets%NOTFOUND then
4970 --
4971 hr_utility.set_location(l_proc, 40);
4972 --
4973 close csr_get_asg_dets;
4974 hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
4975 hr_utility.raise_error;
4976 end if;
4977 --
4978 close csr_get_asg_dets;
4979 --
4980 hr_utility.set_location(l_proc, 50);
4981 --
4982 if l_assignment_type <> 'E' then
4983 --
4984 -- Assignment is not an employee assignment.
4985 --
4986 hr_utility.set_location(l_proc, 60);
4987 --
4988 hr_utility.set_message(801, 'HR_7948_ASG_ASG_NOT_EMP');
4989 hr_utility.raise_error;
4990 end if;
4991 --
4992 hr_utility.set_location(l_proc, 70);
4993 --
4994 -- Process Logic
4995 --
4996 -- If p_assignment_status_type_id is g_number then derive it's default value,
4997 -- otherwise validate it.
4998 --
4999 per_asg_bus1.chk_assignment_status_type
5000 (p_assignment_status_type_id => l_assignment_status_type_id
5001 ,p_business_group_id => l_business_group_id
5002 ,p_legislation_code => l_legislation_code
5003 ,p_expected_system_status => p_expected_system_status
5004 );
5005 --
5006 hr_utility.set_location(l_proc, 80);
5007 --
5008 -- Update employee assignment.
5009 --
5010 per_asg_upd.upd
5011 (p_assignment_id => p_assignment_id
5012 ,p_effective_start_date => l_effective_start_date
5013 ,p_effective_end_date => l_effective_end_date
5014 ,p_business_group_id => l_business_group_id
5015 ,p_assignment_status_type_id => l_assignment_status_type_id
5016 ,p_change_reason => p_change_reason
5017 ,p_comment_id => l_comment_id
5018 ,p_payroll_id_updated => l_payroll_id_updated
5019 ,p_other_manager_warning => l_other_manager_warning
5020 ,p_no_managers_warning => l_no_managers_warning
5021 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
5022 ,p_validation_start_date => l_validation_start_date
5023 ,p_validation_end_date => l_validation_end_date
5024 ,p_object_version_number => l_object_version_number
5025 ,p_effective_date => p_effective_date
5026 ,p_datetrack_mode => p_datetrack_update_mode
5027 ,p_validate => FALSE
5028 ,p_hourly_salaried_warning => l_hourly_salaried_warning
5029 );
5030 --
5031 hr_utility.set_location(l_proc, 90);
5032 --
5033 -- Set all output arguments
5034 --
5035 p_effective_start_date := l_effective_start_date;
5036 p_effective_end_date := l_effective_end_date;
5037 p_object_version_number := l_object_version_number;
5038 --
5039 hr_utility.set_location(' Leaving:'||l_proc, 200);
5040 end update_status_type_emp_asg;
5041 --
5042 -- ----------------------------------------------------------------------------
5043 -- |----------------------------< create_apl_asg >----------------------------|
5044 -- ----------------------------------------------------------------------------
5045 --
5046 procedure create_apl_asg
5047 (p_effective_date in date
5048 ,p_legislation_code in varchar2
5049 ,p_business_group_id in number
5050 ,p_recruiter_id in number default null
5051 ,p_grade_id in number default null
5052 ,p_position_id in number default null
5053 ,p_job_id in number default null
5054 ,p_assignment_status_type_id in number default null
5055 ,p_payroll_id in number default null
5056 ,p_location_id in number default null
5057 ,p_person_referred_by_id in number default null
5058 ,p_supervisor_id in number default null
5059 ,p_special_ceiling_step_id in number default null
5060 ,p_person_id in number
5061 ,p_recruitment_activity_id in number default null
5062 ,p_source_organization_id in number default null
5063 ,p_organization_id in number
5064 ,p_people_group_id in number default null
5065 ,p_soft_coding_keyflex_id in number default null
5066 ,p_vacancy_id in number default null
5067 ,p_pay_basis_id in number default null
5068 ,p_application_id in number
5069 ,p_change_reason in varchar2 default null
5070 ,p_comments in varchar2 default null
5071 ,p_date_probation_end in date default null
5072 ,p_default_code_comb_id in number default null
5073 ,p_employment_category in varchar2 default null
5074 ,p_frequency in varchar2 default null
5075 ,p_internal_address_line in varchar2 default null
5076 ,p_manager_flag in varchar2 default null
5077 ,p_normal_hours in number default null
5078 ,p_perf_review_period in number default null
5079 ,p_perf_review_period_frequency in varchar2 default null
5080 ,p_probation_period in number default null
5081 ,p_probation_unit in varchar2 default null
5082 ,p_sal_review_period in number default null
5083 ,p_sal_review_period_frequency in varchar2 default null
5084 ,p_set_of_books_id in number default null
5085 ,p_source_type in varchar2 default null
5086 ,p_time_normal_finish in varchar2 default null
5087 ,p_time_normal_start in varchar2 default null
5088 ,p_bargaining_unit_code in varchar2 default null
5089 ,p_labour_union_member_flag in varchar2 default 'N'
5090 ,p_hourly_salaried_code in varchar2 default null
5091 ,p_ass_attribute_category in varchar2 default null
5092 ,p_ass_attribute1 in varchar2 default null
5093 ,p_ass_attribute2 in varchar2 default null
5094 ,p_ass_attribute3 in varchar2 default null
5095 ,p_ass_attribute4 in varchar2 default null
5096 ,p_ass_attribute5 in varchar2 default null
5097 ,p_ass_attribute6 in varchar2 default null
5098 ,p_ass_attribute7 in varchar2 default null
5099 ,p_ass_attribute8 in varchar2 default null
5100 ,p_ass_attribute9 in varchar2 default null
5101 ,p_ass_attribute10 in varchar2 default null
5102 ,p_ass_attribute11 in varchar2 default null
5103 ,p_ass_attribute12 in varchar2 default null
5104 ,p_ass_attribute13 in varchar2 default null
5105 ,p_ass_attribute14 in varchar2 default null
5106 ,p_ass_attribute15 in varchar2 default null
5107 ,p_ass_attribute16 in varchar2 default null
5108 ,p_ass_attribute17 in varchar2 default null
5109 ,p_ass_attribute18 in varchar2 default null
5110 ,p_ass_attribute19 in varchar2 default null
5111 ,p_ass_attribute20 in varchar2 default null
5112 ,p_ass_attribute21 in varchar2 default null
5113 ,p_ass_attribute22 in varchar2 default null
5114 ,p_ass_attribute23 in varchar2 default null
5115 ,p_ass_attribute24 in varchar2 default null
5116 ,p_ass_attribute25 in varchar2 default null
5117 ,p_ass_attribute26 in varchar2 default null
5118 ,p_ass_attribute27 in varchar2 default null
5119 ,p_ass_attribute28 in varchar2 default null
5120 ,p_ass_attribute29 in varchar2 default null
5121 ,p_ass_attribute30 in varchar2 default null
5122 ,p_title in varchar2 default null
5123 ,p_contract_id in number default null
5124 ,p_establishment_id in number default null
5125 ,p_collective_agreement_id in number default null
5126 ,p_cagr_id_flex_num in number default null
5127 ,p_cagr_grade_def_id in number default null
5128 ,p_notice_period in number default null
5129 ,p_notice_period_uom in varchar2 default null
5130 ,p_employee_category in varchar2 default null
5131 ,p_work_at_home in varchar2 default null
5132 ,p_job_post_source_name in varchar2 default null
5133 ,p_validate_df_flex in boolean default true
5134 ,p_posting_content_id in number default null
5135 ,p_applicant_rank in number default null
5136 ,p_grade_ladder_pgm_id in number default null
5137 ,p_supervisor_assignment_id in number default null
5138 ,p_object_version_number out nocopy number
5139 ,p_assignment_id out nocopy number
5140 ,p_effective_start_date out nocopy date
5141 ,p_effective_end_date out nocopy date
5142 ,p_assignment_sequence out nocopy number
5143 ,p_comment_id out nocopy number
5144 ) is
5145 --
5146 -- Declare cursors and local variables
5147 --
5148 l_assignment_status_id number;
5149 l_asg_status_ovn number;
5150 l_assignment_id per_assignments_f.assignment_id%TYPE;
5151 l_assignment_sequence per_assignments_f.assignment_sequence%TYPE;
5152 l_object_version_number per_assignments_f.object_version_number%TYPE;
5153 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
5154 l_assignment_number per_assignments_f.assignment_number%TYPE;
5155 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
5156 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
5157 l_comment_id per_assignments_f.comment_id%TYPE;
5158 l_other_manager_warning boolean;
5159 l_hourly_salaried_warning boolean;
5160 l_proc varchar2(72) := g_package||'create_apl_asg';
5161 l_labour_union_member_flag per_assignments_f.labour_union_member_flag%TYPE;
5162 l_work_at_home per_all_assignments_f.work_at_home%TYPE; --FIX FOR BUG 9246917
5163 begin
5164 --
5165 hr_utility.set_location(l_proc, 10);
5166 -- fix for bug 4550165 starts here.
5167 if p_legislation_code = 'DE' then
5168 l_labour_union_member_flag := null;
5169 else
5170 l_labour_union_member_flag := p_labour_union_member_flag;
5171 end if;
5172 -- fix for bug 4550165 ends here.
5173 --
5174 -- Validation in addition to Table Handlers
5175 --
5176 -- None required.
5177 --
5178 -- Process logic
5179 -- Start of 3554801
5180 if p_application_id is not null then
5181 --
5182 per_app_asg_pkg.check_apl_end_date(p_application_id => p_application_id);
5183 --
5184 end if;
5185 -- End of 3554801
5186 -- If p_assignment_status_type_id is null, derive default status for
5187 -- person's business group.
5188 --
5189 if p_assignment_status_type_id is null then
5190 per_people3_pkg.get_default_person_type
5191 (p_required_type => 'ACTIVE_APL'
5192 ,p_business_group_id => p_business_group_id
5193 ,p_legislation_code => p_legislation_code
5194 ,p_person_type => l_assignment_status_type_id
5195 );
5196 else
5197 l_assignment_status_type_id := p_assignment_status_type_id;
5198 end if;
5199 --
5200 hr_utility.set_location(l_proc, 20);
5201 --
5202 l_assignment_number := null;
5203 l_work_at_home := nvl(p_work_at_home,'N'); --FIX FOR BUG 9246917
5204 --
5205 -- Insert per_assignments_f row.
5206 --
5207 per_asg_ins.ins
5208 (p_assignment_id => l_assignment_id
5209 ,p_effective_start_date => l_effective_start_date
5210 ,p_effective_end_date => l_effective_end_date
5211 ,p_business_group_id => p_business_group_id
5212 ,p_recruiter_id => p_recruiter_id
5213 ,p_grade_id => p_grade_id
5214 ,p_position_id => p_position_id
5215 ,p_job_id => p_job_id
5216 ,p_assignment_status_type_id => l_assignment_status_type_id
5217 ,p_payroll_id => p_payroll_id
5218 ,p_location_id => p_location_id
5219 ,p_person_referred_by_id => p_person_referred_by_id
5220 ,p_special_ceiling_step_id => p_special_ceiling_step_id
5221 ,p_supervisor_id => p_supervisor_id
5222 ,p_person_id => p_person_id
5223 ,p_recruitment_activity_id => p_recruitment_activity_id
5224 ,p_source_organization_id => p_source_organization_id
5225 ,p_organization_id => p_organization_id
5226 ,p_people_group_id => p_people_group_id
5227 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
5228 ,p_vacancy_id => p_vacancy_id
5229 ,p_pay_basis_id => p_pay_basis_id
5230 ,p_assignment_sequence => l_assignment_sequence
5231 ,p_assignment_type => 'A'
5232 ,p_primary_flag => 'N'
5233 ,p_application_id => p_application_id
5234 ,p_assignment_number => l_assignment_number
5235 ,p_change_reason => p_change_reason
5236 ,p_comment_id => l_comment_id
5237 ,p_comments => p_comments
5238 ,p_date_probation_end => p_date_probation_end
5239 ,p_default_code_comb_id => p_default_code_comb_id
5240 ,p_employment_category => p_employment_category
5241 ,p_frequency => p_frequency
5242 ,p_internal_address_line => p_internal_address_line
5243 ,p_manager_flag => p_manager_flag
5244 ,p_normal_hours => p_normal_hours
5245 ,p_perf_review_period => p_perf_review_period
5246 ,p_perf_review_period_frequency => p_perf_review_period_frequency
5247 ,p_probation_period => p_probation_period
5248 ,p_probation_unit => p_probation_unit
5249 ,p_sal_review_period => p_sal_review_period
5250 ,p_sal_review_period_frequency => p_sal_review_period_frequency
5251 ,p_set_of_books_id => p_set_of_books_id
5252 ,p_source_type => p_source_type
5253 ,p_time_normal_finish => p_time_normal_finish
5254 ,p_time_normal_start => p_time_normal_start
5255 ,p_bargaining_unit_code => p_bargaining_unit_code
5256 ,p_labour_union_member_flag => l_labour_union_member_flag -- fix for bug 4550165
5257 ,p_hourly_salaried_code => p_hourly_salaried_code
5258 ,p_ass_attribute_category => p_ass_attribute_category
5259 ,p_ass_attribute1 => p_ass_attribute1
5260 ,p_ass_attribute2 => p_ass_attribute2
5261 ,p_ass_attribute3 => p_ass_attribute3
5262 ,p_ass_attribute4 => p_ass_attribute4
5263 ,p_ass_attribute5 => p_ass_attribute5
5264 ,p_ass_attribute6 => p_ass_attribute6
5265 ,p_ass_attribute7 => p_ass_attribute7
5266 ,p_ass_attribute8 => p_ass_attribute8
5267 ,p_ass_attribute9 => p_ass_attribute9
5268 ,p_ass_attribute10 => p_ass_attribute10
5269 ,p_ass_attribute11 => p_ass_attribute11
5270 ,p_ass_attribute12 => p_ass_attribute12
5271 ,p_ass_attribute13 => p_ass_attribute13
5272 ,p_ass_attribute14 => p_ass_attribute14
5273 ,p_ass_attribute15 => p_ass_attribute15
5274 ,p_ass_attribute16 => p_ass_attribute16
5275 ,p_ass_attribute17 => p_ass_attribute17
5276 ,p_ass_attribute18 => p_ass_attribute18
5277 ,p_ass_attribute19 => p_ass_attribute19
5278 ,p_ass_attribute20 => p_ass_attribute20
5279 ,p_ass_attribute21 => p_ass_attribute21
5280 ,p_ass_attribute22 => p_ass_attribute22
5281 ,p_ass_attribute23 => p_ass_attribute23
5282 ,p_ass_attribute24 => p_ass_attribute24
5283 ,p_ass_attribute25 => p_ass_attribute25
5284 ,p_ass_attribute26 => p_ass_attribute26
5285 ,p_ass_attribute27 => p_ass_attribute27
5286 ,p_ass_attribute28 => p_ass_attribute28
5287 ,p_ass_attribute29 => p_ass_attribute29
5288 ,p_ass_attribute30 => p_ass_attribute30
5289 ,p_title => p_title
5290 ,p_contract_id => p_contract_id
5291 ,p_establishment_id => p_establishment_id
5292 ,p_collective_agreement_id => p_collective_agreement_id
5293 ,p_cagr_id_flex_num => p_cagr_id_flex_num
5294 ,p_cagr_grade_def_id => p_cagr_grade_def_id
5295 ,p_notice_period => p_notice_period
5296 ,p_notice_period_uom => p_notice_period_uom
5297 ,p_employee_category => p_employee_category
5298 ,p_work_at_home => l_work_at_home
5299 ,p_job_post_source_name => p_job_post_source_name
5300 ,p_other_manager_warning => l_other_manager_warning
5301 ,p_posting_content_id => p_posting_content_id
5302 ,p_applicant_rank => p_applicant_rank
5303 ,p_object_version_number => l_object_version_number
5304 ,p_effective_date => p_effective_date
5305 ,p_validate => FALSE
5306 ,p_validate_df_flex => p_validate_df_flex
5307 ,p_hourly_salaried_warning => l_hourly_salaried_warning
5308 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
5309 ,p_supervisor_assignment_id => p_supervisor_assignment_id
5310 );
5311 --
5312
5313 IRC_ASG_STATUS_API.create_irc_asg_status
5314 (p_assignment_id => l_assignment_id
5315 , p_assignment_status_type_id => l_assignment_status_type_id
5316 , p_status_change_date => p_effective_date
5317 , p_status_change_reason => p_change_reason
5318 , p_assignment_status_id => l_assignment_status_id
5319 , p_object_version_number => l_asg_status_ovn);
5320
5321
5322 hr_utility.set_location(l_proc, 30);
5323 --
5324 -- Create assignment budget values.
5325 --Change 16-APR-1998 Include effective dates. SASmith
5326 --
5327 hr_assignment.load_budget_values
5328 (p_assignment_id => l_assignment_id
5329 ,p_business_group_id => p_business_group_id
5330 ,p_userid => null
5331 ,p_login => null
5332 ,p_effective_start_date => l_effective_start_date
5333 ,p_effective_end_date => l_effective_end_date
5334 );
5335 --
5336 hr_utility.set_location(l_proc, 40);
5337 --
5338 -- Check if a letter request is necessary for the assignment.
5339 --
5340 per_applicant_pkg.check_for_letter_requests
5341 (p_business_group_id => p_business_group_id
5342 ,p_per_system_status => null
5343 ,p_assignment_status_type_id => l_assignment_status_type_id
5344 ,p_person_id => p_person_id
5345 ,p_assignment_id => l_assignment_id
5346 ,p_effective_start_date => l_effective_start_date
5347 ,p_validation_start_date => l_effective_start_date
5348 ,p_vacancy_id => p_vacancy_id
5349 );
5350 --
5351 hr_utility.set_location(l_proc, 50);
5352 --
5353 -- Set OUT parameters
5354 --
5355 p_assignment_id := l_assignment_id;
5356 p_object_version_number := l_object_version_number;
5357 p_effective_start_date := l_effective_start_date;
5358 p_effective_end_date := l_effective_end_date;
5359 p_assignment_sequence := l_assignment_sequence;
5360 p_comment_id := l_comment_id;
5361 --
5362 hr_utility.set_location(' Leaving:'||l_proc, 200);
5363 --
5364 end create_apl_asg;
5365 --
5366 -- ----------------------------------------------------------------------------
5367 -- |------------------------< create_default_cwk_asg >------------------------|
5368 -- ----------------------------------------------------------------------------
5369 --
5370 procedure create_default_cwk_asg
5371 (p_effective_date in date
5372 ,p_person_id in number
5373 ,p_business_group_id in number
5374 ,p_placement_date_start in date
5375 ,p_assignment_id out nocopy number
5376 ,p_object_version_number out nocopy number
5377 ,p_assignment_sequence out nocopy number
5378 ,p_assignment_number out nocopy varchar2
5379 ) is
5380 --
5381 -- Declare cursors and local variables
5382 --
5383 l_location_id per_business_groups.location_id%TYPE;
5384 l_time_normal_finish per_business_groups.default_end_time%TYPE;
5385 l_time_normal_start per_business_groups.default_start_time%TYPE;
5386 l_normal_hours number;
5387 l_frequency per_business_groups.frequency%TYPE;
5388 l_legislation_code per_business_groups.legislation_code%TYPE;
5389 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
5390 l_effective_end_date per_assignments_f.effective_start_date%TYPE;
5391 l_assignment_number per_assignments_f.assignment_number%TYPE;
5392 l_comment_id per_assignments_f.comment_id%TYPE;
5393 l_other_manager_warning boolean;
5394 l_proc varchar2(72):=g_package||'create_default_cwk_asg';
5395 --
5396 cursor csr_get_default_details is
5397 select bus.location_id
5398 , bus.default_end_time
5399 , bus.default_start_time
5400 , fnd_number.canonical_to_number(bus.working_hours)
5401 , bus.frequency
5402 , bus.legislation_code
5403 from per_business_groups bus
5404 where bus.business_group_id = p_business_group_id;
5405 --
5406 begin
5407 --
5408 hr_utility.set_location('Entering:'|| l_proc, 1);
5409 --
5410 l_assignment_number := null;
5411 --
5412 -- Process Logic
5413 --
5414 -- Get default details.
5415 --
5416 hr_api.mandatory_arg_error
5417 (p_api_name => l_proc,
5418 p_argument => 'business_group_id',
5419 p_argument_value => p_business_group_id);
5420 --
5421 open csr_get_default_details;
5422 fetch csr_get_default_details
5423 into l_location_id
5424 , l_time_normal_finish
5425 , l_time_normal_start
5426 , l_normal_hours
5427 , l_frequency
5428 , l_legislation_code;
5429 --
5430 if csr_get_default_details%NOTFOUND then
5431 --
5432 hr_utility.set_location(l_proc, 10);
5433 --
5434 close csr_get_default_details;
5435 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5436 hr_utility.raise_error;
5437 --
5438 end if;
5439 --
5440 close csr_get_default_details;
5441 --
5442 hr_utility.set_location(l_proc, 20);
5443 --
5444 -- Create the contingent worker assignment.
5445 --
5446 hr_assignment_internal.create_cwk_asg
5447 (p_effective_date => p_effective_date
5448 ,p_legislation_code => l_legislation_code
5449 ,p_business_group_id => p_business_group_id
5450 ,p_person_id => p_person_id
5451 ,p_organization_id => p_business_group_id
5452 ,p_primary_flag => 'Y'
5453 ,p_placement_date_start => p_placement_date_start
5454 ,p_location_id => l_location_id
5455 ,p_people_group_id => null
5456 ,p_assignment_number => l_assignment_number
5457 ,p_frequency => l_frequency
5458 ,p_normal_hours => l_normal_hours
5459 ,p_time_normal_finish => l_time_normal_finish
5460 ,p_time_normal_start => l_time_normal_start
5461 ,p_assignment_id => p_assignment_id
5462 ,p_object_version_number => p_object_version_number
5463 ,p_effective_start_date => l_effective_start_date
5464 ,p_effective_end_date => l_effective_end_date
5465 ,p_assignment_sequence => p_assignment_sequence
5466 ,p_comment_id => l_comment_id
5467 ,p_other_manager_warning => l_other_manager_warning
5468 ,p_validate_df_flex => false
5469 );
5470 --
5471 hr_utility.set_location(l_proc, 30);
5472 --
5473 -- Set remaining output arguments
5474 --
5475 p_assignment_number := l_assignment_number;
5476 --
5477 hr_utility.set_location(' Leaving:'||l_proc, 999);
5478 --
5479 end create_default_cwk_asg;
5480 --
5481 -- ----------------------------------------------------------------------------
5482 -- |----------------------------< create_cwk_asg >----------------------------|
5483 -- ----------------------------------------------------------------------------
5484 --
5485 procedure create_cwk_asg
5486 (p_validate in boolean default false
5487 ,p_effective_date in date
5488 ,p_business_group_id in number
5489 ,p_legislation_code in varchar2
5490 ,p_person_id in number
5491 ,p_placement_date_start in date
5492 ,p_organization_id in number
5493 ,p_primary_flag in varchar2
5494 ,p_assignment_number in out nocopy varchar2
5495 ,p_assignment_category in varchar2 default null
5496 ,p_assignment_status_type_id in number default null
5497 ,p_change_reason in varchar2 default null
5498 ,p_comments in varchar2 default null
5499 ,p_default_code_comb_id in number default null
5500 ,p_employment_category in varchar2 default null
5501 ,p_establishment_id in number default null
5502 ,p_frequency in varchar2 default null
5503 ,p_internal_address_line in varchar2 default null
5504 ,p_job_id in number default null
5505 ,p_labor_union_member_flag in varchar2 default null
5506 ,p_location_id in number default null
5507 ,p_manager_flag in varchar2 default null
5508 ,p_normal_hours in number default null
5509 ,p_position_id in number default null
5510 ,p_grade_id in number default null
5511 ,p_project_title in varchar2 default null
5512 ,p_title in varchar2 default null
5513 ,p_set_of_books_id in number default null
5514 ,p_source_type in varchar2 default null
5515 ,p_supervisor_id in number default null
5516 ,p_time_normal_start in varchar2 default null
5517 ,p_time_normal_finish in varchar2 default null
5518 ,p_vendor_assignment_number in varchar2 default null
5519 ,p_vendor_employee_number in varchar2 default null
5520 ,p_vendor_id in number default null
5521 ,p_vendor_site_id in number default null
5522 ,p_po_header_id in number default null
5523 ,p_po_line_id in number default null
5524 ,p_projected_assignment_end in date default null
5525 ,p_people_group_id in number default null
5526 ,p_soft_coding_keyflex_id in number default null
5527 ,p_ass_attribute_category in varchar2 default null
5528 ,p_ass_attribute1 in varchar2 default null
5529 ,p_ass_attribute2 in varchar2 default null
5530 ,p_ass_attribute3 in varchar2 default null
5531 ,p_ass_attribute4 in varchar2 default null
5532 ,p_ass_attribute5 in varchar2 default null
5533 ,p_ass_attribute6 in varchar2 default null
5534 ,p_ass_attribute7 in varchar2 default null
5535 ,p_ass_attribute8 in varchar2 default null
5536 ,p_ass_attribute9 in varchar2 default null
5537 ,p_ass_attribute10 in varchar2 default null
5538 ,p_ass_attribute11 in varchar2 default null
5539 ,p_ass_attribute12 in varchar2 default null
5540 ,p_ass_attribute13 in varchar2 default null
5541 ,p_ass_attribute14 in varchar2 default null
5542 ,p_ass_attribute15 in varchar2 default null
5543 ,p_ass_attribute16 in varchar2 default null
5544 ,p_ass_attribute17 in varchar2 default null
5545 ,p_ass_attribute18 in varchar2 default null
5546 ,p_ass_attribute19 in varchar2 default null
5547 ,p_ass_attribute20 in varchar2 default null
5548 ,p_ass_attribute21 in varchar2 default null
5549 ,p_ass_attribute22 in varchar2 default null
5550 ,p_ass_attribute23 in varchar2 default null
5551 ,p_ass_attribute24 in varchar2 default null
5552 ,p_ass_attribute25 in varchar2 default null
5553 ,p_ass_attribute26 in varchar2 default null
5554 ,p_ass_attribute27 in varchar2 default null
5555 ,p_ass_attribute28 in varchar2 default null
5556 ,p_ass_attribute29 in varchar2 default null
5557 ,p_ass_attribute30 in varchar2 default null
5558 ,p_validate_df_flex in boolean default true
5559 ,p_supervisor_assignment_id in number default null
5560 ,p_assignment_id out nocopy number
5561 ,p_object_version_number out nocopy number
5562 ,p_effective_start_date out nocopy date
5563 ,p_effective_end_date out nocopy date
5564 ,p_assignment_sequence out nocopy number
5565 ,p_comment_id out nocopy number
5566 ,p_other_manager_warning out nocopy boolean
5567 ) is
5568 --
5569 -- Declare cursors and local variables
5570 --
5571 l_assignment_id per_assignments_f.assignment_id%TYPE;
5572 l_assignment_sequence per_assignments_f.assignment_sequence%TYPE;
5573 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
5574 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
5575 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
5576 l_hourly_salaried_warning boolean;
5577 l_proc varchar2(72) := g_package||'create_cwk_asg';
5578 --
5579 begin
5580 hr_utility.set_location('Entering:'|| l_proc, 1);
5581 --
5582 l_assignment_status_type_id := p_assignment_status_type_id;
5583 --
5584 -- Validation in addition to Table Handlers
5585 --
5586 -- None required.
5587 --
5588 -- Process Logic
5589 --
5590 -- If p_assignment_status_type_id is null then derive it's default value,
5591 -- otherwise validate it.
5592 --
5593 per_asg_bus1.chk_assignment_status_type
5594 (p_assignment_status_type_id => l_assignment_status_type_id
5595 ,p_business_group_id => p_business_group_id
5596 ,p_legislation_code => p_legislation_code
5597 ,p_expected_system_status => 'ACTIVE_CWK'
5598 );
5599 --
5600 hr_utility.set_location(l_proc, 10);
5601 --
5602 -- Insert per_assignments_f row.
5603 --
5604 per_asg_ins.ins
5605 (p_validate => p_validate
5606 ,p_effective_date => p_effective_date
5607 ,p_business_group_id => p_business_group_id
5608 ,p_person_id => p_person_id
5609 ,p_placement_date_start => p_placement_date_start
5610 ,p_organization_id => p_organization_id
5611 ,p_primary_flag => p_primary_flag
5612 ,p_assignment_number => p_assignment_number
5613 ,p_assignment_category => p_assignment_category
5614 ,p_assignment_status_type_id => l_assignment_status_type_id
5615 ,p_assignment_type => 'C'
5616 ,p_change_reason => p_change_reason
5617 ,p_comments => p_comments
5618 ,p_default_code_comb_id => p_default_code_comb_id
5619 ,p_employment_category => p_employment_category
5620 ,p_establishment_id => p_establishment_id
5621 ,p_frequency => p_frequency
5622 ,p_internal_address_line => p_internal_address_line
5623 ,p_job_id => p_job_id
5624 ,p_labour_union_member_flag => p_labor_union_member_flag
5625 ,p_location_id => p_location_id
5626 ,p_manager_flag => p_manager_flag
5627 ,p_normal_hours => p_normal_hours
5628 ,p_position_id => p_position_id
5629 -- Bug 3545065, Grade should not be maintained for CWK asg
5630 -- ,p_grade_id => p_grade_id
5631 ,p_project_title => p_project_title
5632 ,p_set_of_books_id => p_set_of_books_id
5633 ,p_source_type => p_source_type
5634 ,p_supervisor_id => p_supervisor_id
5635 ,p_time_normal_start => p_time_normal_start
5636 ,p_time_normal_finish => p_time_normal_finish
5637 ,p_title => p_title
5638 ,p_vendor_assignment_number => p_vendor_assignment_number
5639 ,p_vendor_employee_number => p_vendor_employee_number
5640 ,p_vendor_id => p_vendor_id
5641 ,p_vendor_site_id => p_vendor_site_id
5642 ,p_po_header_id => p_po_header_id
5643 ,p_po_line_id => p_po_line_id
5644 ,p_projected_assignment_end => p_projected_assignment_end
5645 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
5646 ,p_people_group_id => p_people_group_id
5647 ,p_ass_attribute_category => p_ass_attribute_category
5648 ,p_ass_attribute1 => p_ass_attribute1
5649 ,p_ass_attribute2 => p_ass_attribute2
5650 ,p_ass_attribute3 => p_ass_attribute3
5651 ,p_ass_attribute4 => p_ass_attribute4
5652 ,p_ass_attribute5 => p_ass_attribute5
5653 ,p_ass_attribute6 => p_ass_attribute6
5654 ,p_ass_attribute7 => p_ass_attribute7
5655 ,p_ass_attribute8 => p_ass_attribute8
5656 ,p_ass_attribute9 => p_ass_attribute9
5657 ,p_ass_attribute10 => p_ass_attribute10
5658 ,p_ass_attribute11 => p_ass_attribute11
5659 ,p_ass_attribute12 => p_ass_attribute12
5660 ,p_ass_attribute13 => p_ass_attribute13
5661 ,p_ass_attribute14 => p_ass_attribute14
5662 ,p_ass_attribute15 => p_ass_attribute15
5663 ,p_ass_attribute16 => p_ass_attribute16
5664 ,p_ass_attribute17 => p_ass_attribute17
5665 ,p_ass_attribute18 => p_ass_attribute18
5666 ,p_ass_attribute19 => p_ass_attribute19
5667 ,p_ass_attribute20 => p_ass_attribute20
5668 ,p_ass_attribute21 => p_ass_attribute21
5669 ,p_ass_attribute22 => p_ass_attribute22
5670 ,p_ass_attribute23 => p_ass_attribute23
5671 ,p_ass_attribute24 => p_ass_attribute24
5672 ,p_ass_attribute25 => p_ass_attribute25
5673 ,p_ass_attribute26 => p_ass_attribute26
5674 ,p_ass_attribute27 => p_ass_attribute27
5675 ,p_ass_attribute28 => p_ass_attribute28
5676 ,p_ass_attribute29 => p_ass_attribute29
5677 ,p_ass_attribute30 => p_ass_attribute30
5678 ,p_validate_df_flex => p_validate_df_flex
5679 ,p_assignment_id => l_assignment_id
5680 ,p_object_version_number => p_object_version_number
5681 ,p_effective_start_date => l_effective_start_date
5682 ,p_effective_end_date => l_effective_end_date
5683 ,p_assignment_sequence => p_assignment_sequence
5684 ,p_comment_id => p_comment_id
5685 ,p_other_manager_warning => p_other_manager_warning
5686 ,p_hourly_salaried_warning => l_hourly_salaried_warning
5687 ,p_supervisor_assignment_id => p_supervisor_assignment_id
5688 );
5689 --
5690 hr_utility.set_location(l_proc, 30);
5691 --
5692 -- Set all output arguments
5693 --
5694 p_assignment_id := l_assignment_id;
5695 p_effective_start_date := l_effective_start_date;
5696 p_effective_end_date := l_effective_end_date;
5697 --
5698 hr_utility.set_location(' Leaving:'||l_proc, 100);
5699 end create_cwk_asg;
5700 --
5701 -- ----------------------------------------------------------------------------
5702 -- |------------------------< create_default_apl_asg >------------------------|
5703 -- ----------------------------------------------------------------------------
5704 --
5705 procedure create_default_apl_asg
5706 (p_effective_date in date
5707 ,p_person_id in number
5708 ,p_business_group_id in number
5709 ,p_application_id in number
5710 ,p_vacancy_id in number
5711 ,p_assignment_id out nocopy number
5712 ,p_object_version_number out nocopy number
5713 ,p_assignment_sequence out nocopy number
5714 ) is
5715 --
5716 -- Declare cursors and local variables
5717 --
5718 l_location_id per_business_groups.location_id%TYPE;
5719 -- Bug 4325900
5720 l_vac_location_id per_all_vacancies.location_id%TYPE;
5721 -- Bug 4520212
5722 l_org_id per_all_vacancies.business_group_id%TYPE := p_business_group_id;
5723 l_vac_org_id per_all_vacancies.business_group_id%TYPE ;
5724 l_vac_pgp_id per_all_vacancies.people_group_id%TYPE ;
5725 l_vac_rec_id per_all_vacancies.recruiter_id%TYPE ;
5726 l_vac_job_id per_all_vacancies.job_id%TYPE ;
5727 l_vac_position_id per_all_vacancies.position_id%TYPE ;
5728 l_vac_grade_id per_all_vacancies.grade_id%TYPE ;
5729
5730 l_time_normal_finish per_business_groups.default_end_time%TYPE;
5731 l_time_normal_start per_business_groups.default_start_time%TYPE;
5732 l_normal_hours number;
5733 l_frequency per_business_groups.frequency%TYPE;
5734 l_legislation_code per_business_groups.legislation_code%TYPE;
5735 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
5736 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
5737 l_comment_id per_assignments_f.comment_id%TYPE;
5738 l_proc varchar2(72):=g_package||'create_default_apl_asg';
5739 --
5740 cursor csr_get_default_details is
5741 select bus.location_id
5742 , bus.default_start_time
5743 , bus.default_end_time
5744 , fnd_number.canonical_to_number(bus.working_hours)
5745 , bus.frequency
5746 , bus.legislation_code
5747 from per_business_groups bus
5748 where bus.business_group_id = p_business_group_id;
5749 --
5750 -- Bug 4325900 Starts
5751 cursor csr_get_vac_loc is
5752 select location_id,people_group_id,recruiter_id,job_id,position_id,grade_id,organization_id
5753 from PER_ALL_VACANCIES
5754 where vacancy_id = p_vacancy_id
5755 and p_effective_date between date_from
5756 and nvl(date_to, hr_api.g_eot);
5757 -- Bug 4325900 Ends
5758 --
5759 begin
5760 hr_utility.set_location('Entering:'|| l_proc, 10);
5761 --
5762 -- Process Logic
5763 --
5764 -- Get default details.
5765 --
5766 open csr_get_default_details;
5767 fetch csr_get_default_details
5768 into l_location_id
5769 , l_time_normal_start
5770 , l_time_normal_finish
5771 , l_normal_hours
5772 , l_frequency
5773 , l_legislation_code;
5774 if csr_get_default_details%NOTFOUND then
5775 --
5776 close csr_get_default_details;
5777 --
5778 hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
5779 hr_utility.raise_error;
5780 end if;
5781 close csr_get_default_details;
5782 --
5783 hr_utility.set_location(l_proc, 20);
5784 -- Bug 4325900 Starts
5785 -- Desc: Check to see if the vacancy is null or not. If vacancy
5786 -- is not null then get the default location of the vacancy
5787 -- to pass the the assignment rowhandler.
5788 if p_vacancy_id is not null then
5789 open csr_get_vac_loc;
5790 fetch csr_get_vac_loc into l_vac_location_id
5791 ,l_vac_pgp_id
5792 ,l_vac_rec_id
5793 ,l_vac_job_id
5794 ,l_vac_position_id
5795 ,l_vac_grade_id
5796 ,l_vac_org_id;
5797 close csr_get_vac_loc;
5798 if l_vac_location_id is not null then
5799 l_location_id := l_vac_location_id;
5800 end if;
5801 if l_vac_org_id is not null then
5802 l_org_id := l_vac_org_id;
5803 end if;
5804 end if;
5805 -- Bug 4325900 Ends
5806 --
5807 -- Create applicant assignment.
5808 --
5809 hr_assignment_internal.create_apl_asg
5810 (p_effective_date => p_effective_date
5811 ,p_legislation_code => l_legislation_code
5812 ,p_business_group_id => p_business_group_id
5813 ,p_person_id => p_person_id
5814 ,p_organization_id => l_org_id
5815 ,p_application_id => p_application_id
5816 ,p_location_id => l_location_id
5817 -- ,p_people_group_id => null
5818 ,p_frequency => l_frequency
5819 ,p_manager_flag => 'N'
5820 ,p_normal_hours => l_normal_hours
5821 ,p_time_normal_finish => l_time_normal_finish
5822 ,p_time_normal_start => l_time_normal_start
5823 ,p_assignment_id => p_assignment_id
5824 ,p_object_version_number => p_object_version_number
5825 ,p_effective_start_date => l_effective_start_date
5826 ,p_effective_end_date => l_effective_end_date
5827 ,p_assignment_sequence => p_assignment_sequence
5828 ,p_comment_id => l_comment_id
5829 ,p_validate_df_flex => false
5830 ,p_vacancy_id => p_vacancy_id
5831 ,p_recruiter_id => l_vac_rec_id
5832 ,p_job_id => l_vac_job_id
5833 ,p_position_id => l_vac_position_id
5834 ,p_grade_id => l_vac_grade_id
5835 ,p_people_group_id => l_vac_pgp_id
5836 );
5837 --
5838 hr_utility.set_location(' Leaving:'||l_proc, 40);
5839 end create_default_apl_asg;
5840 --
5841 -- ----------------------------------------------------------------------------
5842 -- |----------------------< update_status_type_apl_asg >----------------------|
5843 -- ----------------------------------------------------------------------------
5844 --
5845 procedure update_status_type_apl_asg
5846 (p_effective_date in date
5847 ,p_datetrack_update_mode in varchar2
5848 ,p_assignment_id in number
5849 ,p_object_version_number in out nocopy number
5850 ,p_expected_system_status in varchar2
5851 ,p_assignment_status_type_id in number default hr_api.g_number
5852 ,p_change_reason in varchar2 default hr_api.g_varchar2
5853 ,p_effective_start_date out nocopy date
5854 ,p_effective_end_date out nocopy date
5855 ) is
5856 Cursor csr_vacancy_id is
5857 Select vacancy_id
5858 From per_all_assignments_f
5859 Where assignment_id = p_assignment_id
5860 And p_effective_date between effective_start_date and effective_end_date;
5861
5862 --
5863 -- Declare cursors and local variables
5864 --
5865 -- Out variables
5866 --
5867 l_assignment_status_id number;
5868 L_ASG_STATUS_OVN number;
5869 l_effective_end_date per_assignments_f.effective_end_date%TYPE;
5870 l_effective_start_date per_assignments_f.effective_start_date%TYPE;
5871 l_object_version_number per_assignments_f.object_version_number%TYPE;
5872 --
5873 l_assignment_status_type_id per_assignments_f.assignment_status_type_id%TYPE;
5874 l_assignment_type per_assignments_f.assignment_type%TYPE;
5875 l_person_id per_assignments_f.person_id%TYPE;
5876 l_business_group_id per_assignments_f.business_group_id%TYPE;
5877 l_comment_id per_assignments_f.comment_id%TYPE;
5878 l_legislation_code per_business_groups.legislation_code%TYPE;
5879 l_per_system_status per_assignment_status_types.per_system_status%TYPE;
5880 l_no_managers_warning boolean;
5881 l_other_manager_warning boolean;
5882 l_payroll_id_updated boolean;
5883 l_org_now_no_manager_warning boolean;
5884 l_hourly_salaried_warning boolean;
5885 l_validation_start_date per_assignments_f.effective_start_date%TYPE;
5886 l_validation_end_date per_assignments_f.effective_end_date%TYPE;
5887 l_proc varchar2(72):=
5888 g_package||'update_status_type_apl_asg';
5889 l_vacancy_id number;
5890 --
5891 cursor csr_get_asg_dets is
5892 select asg.assignment_type
5893 , asg.person_id
5894 , asg.business_group_id
5895 , bus.legislation_code
5896 from per_assignments_f asg
5897 , per_business_groups_perf bus
5898 where asg.assignment_id = p_assignment_id
5899 and p_effective_date between asg.effective_start_date
5900 and asg.effective_end_date
5901 and bus.business_group_id+0 = asg.business_group_id;
5902 --
5903 cursor csr_get_asg_status_type is
5904 select ast.per_system_status
5905 from per_assignment_status_types ast
5906 where ast.assignment_status_type_id = p_assignment_status_type_id;
5907 --
5908
5909 -- call to ethnicity 12858051
5910
5911 cursor csr_asg_status_type is
5912 select ast.per_system_status
5913 from per_assignment_status_types ast
5914 where ast.assignment_status_type_id = p_assignment_status_type_id;
5915
5916 cursor c_person_enthn is
5917 select person_id,business_group_id,per_information1
5918 from per_all_people_f
5919 where person_id in (select person_id from per_all_assignments_f where assignment_id= p_assignment_id
5920 and p_effective_date between effective_start_date and effective_end_date)
5921 and p_effective_date between effective_start_date and effective_end_date;
5922
5923
5924 cursor csr_is_emp is
5925 select 1 from
5926 per_periods_of_service
5927 where person_id in (select person_id from per_all_assignments_f
5928 where assignment_id= p_assignment_id
5929 and p_effective_date between effective_start_date and effective_end_date);
5930
5931
5932 l_eth_person_id number;
5933 l_bgid number;
5934 l_per_info1 varchar2(150);
5935 l_asg_status varchar2(30);
5936 l_yes_an_emp number :=2;
5937
5938 -- call to ethnicity
5939
5940
5941 begin
5942 hr_utility.set_location('Entering:'|| l_proc, 1);
5943 --
5944 -- Validation in addition to Table Handlers
5945 --
5946 if p_expected_system_status <> 'ACTIVE_APL'
5947 and p_expected_system_status <> 'OFFER'
5948 and p_expected_system_status <> 'ACCEPTED'
5949 and p_expected_system_status <> 'INTERVIEW1'
5950 and p_expected_system_status <> 'INTERVIEW2'
5951 then
5952 --
5953 hr_utility.set_message(801, 'HR_51232_ASG_INV_AASG_AST');
5954 hr_utility.raise_error;
5955 end if;
5956 --
5957 hr_utility.set_location(l_proc, 10);
5958 --
5959
5960 -- call to ethnicity
5961
5962 open c_person_enthn;
5963 fetch c_person_enthn into l_eth_person_id,l_bgid,l_per_info1;
5964 close c_person_enthn;
5965
5966 hr_utility.set_location('l_bgid call enthn pkg :'||l_bgid,262);
5967
5968 hr_utility.set_location('l_perinfo1 enthn pkg :'||l_per_info1,262);
5969
5970 hr_utility.set_location('p_expected_system_status :'||p_expected_system_status,262);
5971 hr_utility.set_location('hr_api.return_legislation_code (l_bgid):'||hr_api.return_legislation_code (l_bgid),262);
5972
5973 open csr_asg_status_type;
5974 fetch csr_asg_status_type into l_asg_status;
5975 close csr_asg_status_type;
5976
5977 hr_utility.set_location(' l_asg_status :'|| l_asg_status,262);
5978 hr_utility.set_location(' p_assignment_status_type_id :'|| p_assignment_status_type_id,262);
5979
5980 if hr_api.return_legislation_code (l_bgid) ='US' and l_per_info1 is not null
5981 and l_asg_status = 'ACCEPTED' then
5982
5983 hr_utility.set_location('now check if an emp enthn pkg :'||l_per_info1,262);
5984 open csr_is_emp;
5985 fetch csr_is_emp into l_yes_an_emp;
5986 close csr_is_emp;
5987
5988 if nvl(l_yes_an_emp,-1) <> 1 then
5989
5990 hr_utility.set_location('Yes not an EMP : enthn pkg :'||l_per_info1,262);
5991 hr_utility.set_location('calling enthn pkg :',262);
5992
5993 per_us_eth_orig_mig_pkg.irc_upgrade_ethnic_origin(l_eth_person_id,'Y');
5994
5995 end if;
5996
5997 hr_utility.set_location('AFTER calling enthn pkg :',262);
5998 end if;
5999
6000 -- call to ethnicity 12858051
6001
6002 -- Get assignment details.
6003 --
6004 open csr_get_asg_dets;
6005 fetch csr_get_asg_dets
6006 into l_assignment_type
6007 , l_person_id
6008 , l_business_group_id
6009 , l_legislation_code;
6010 --
6011 if csr_get_asg_dets%NOTFOUND then
6012 --
6013 close csr_get_asg_dets;
6014 hr_utility.set_message(801, 'HR_52360_ASG_DOES_NOT_EXIST');
6015 hr_utility.raise_error;
6016 end if;
6017 --
6018 close csr_get_asg_dets;
6019 --
6020 hr_utility.set_location(l_proc, 20);
6021 --
6022 if l_assignment_type <> 'A' then
6023 --
6024 -- Assignment is not an applicant assignment.
6025 --
6026 hr_utility.set_message(801, 'HR_51036_ASG_ASG_NOT_APL');
6027 hr_utility.raise_error;
6028 end if;
6029 --
6030 hr_utility.set_location(l_proc, 30);
6031 --
6032 -- Process Logic
6033 --
6034 -- If p_assignment_status_type_id is hr_api.g_number then derive it's default
6035 -- value, otherwise validate it.
6036 --
6037 l_assignment_status_type_id := p_assignment_status_type_id;
6038 --
6039 per_asg_bus1.chk_assignment_status_type
6040 (p_assignment_status_type_id => l_assignment_status_type_id
6041 ,p_business_group_id => l_business_group_id
6042 ,p_legislation_code => l_legislation_code
6043 ,p_expected_system_status => p_expected_system_status
6044 );
6045 --
6046 l_object_version_number := p_object_version_number;
6047 --
6048 -- Update applicant assignment.
6049 --
6050 per_asg_upd.upd
6051 (p_assignment_id => p_assignment_id
6052 ,p_effective_start_date => l_effective_start_date
6053 ,p_effective_end_date => l_effective_end_date
6054 ,p_business_group_id => l_business_group_id
6055 ,p_assignment_status_type_id => l_assignment_status_type_id
6056 ,p_comment_id => l_comment_id
6057 ,p_change_reason => p_change_reason
6058 ,p_payroll_id_updated => l_payroll_id_updated
6059 ,p_other_manager_warning => l_other_manager_warning
6060 ,p_no_managers_warning => l_no_managers_warning
6061 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
6062 ,p_validation_start_date => l_validation_start_date
6063 ,p_validation_end_date => l_validation_end_date
6064 ,p_object_version_number => l_object_version_number
6065 ,p_effective_date => p_effective_date
6066 ,p_datetrack_mode => p_datetrack_update_mode
6067 ,p_validate => FALSE
6068 ,p_hourly_salaried_warning => l_hourly_salaried_warning
6069 );
6070 --
6071 hr_utility.set_location(l_proc, 45);
6072 --
6073 IRC_ASG_STATUS_API.dt_update_irc_asg_status
6074 (p_assignment_id => p_assignment_id
6075 , p_datetrack_mode => 'INSERT'
6076 , p_assignment_status_type_id => l_assignment_status_type_id
6077 , p_status_change_reason => p_change_reason
6078 , p_status_change_date => p_effective_date
6079 , p_assignment_status_id => l_assignment_status_id
6080 , p_object_version_number => l_asg_status_ovn);
6081
6082 --
6083 hr_utility.set_location(l_proc, 50);
6084 --
6085 -- Remove out-of-date letter request lines
6086 --
6087 per_app_asg_pkg.cleanup_letters
6088 (p_assignment_id => p_assignment_id);
6089 --
6090 -- Check if a letter request is necessary for the assignment.
6091 --
6092 open csr_vacancy_id;
6093 fetch csr_vacancy_id into l_vacancy_id;
6094 if csr_vacancy_id%NOTFOUND then null;
6095 end if;
6096 close csr_vacancy_id;
6097
6098 per_applicant_pkg.check_for_letter_requests
6099 (p_business_group_id => l_business_group_id
6100 ,p_per_system_status => null
6101 ,p_assignment_status_type_id => l_assignment_status_type_id
6102 ,p_person_id => l_person_id
6103 ,p_assignment_id => p_assignment_id
6104 ,p_effective_start_date => l_effective_start_date
6105 ,p_validation_start_date => l_validation_start_date
6106 ,p_vacancy_id => l_vacancy_id
6107 );
6108 --
6109 hr_utility.set_location(l_proc, 60);
6110 --
6111 -- Set out arguments
6112 --
6113 p_effective_start_date := l_effective_start_date;
6114 p_effective_end_date := l_effective_end_date;
6115 p_object_version_number := l_object_version_number;
6116 --
6117 hr_utility.set_location(' Leaving:'||l_proc, 70);
6118 end update_status_type_apl_asg;
6119 --surendra
6120 --
6121 -- ----------------------------------------------------------------------------
6122 -- |----------------------< irc_delete_assgt_checks >--------------------------|
6123 -- ----------------------------------------------------------------------------
6124 --
6125 procedure irc_delete_assgt_checks
6126 (p_assignment_id in per_all_assignments_f.assignment_id%Type
6127 ,p_datetrack_mode in varchar2
6128 ,p_validation_start_date in date )
6129 is
6130 --
6131 cursor irc_asgt_statuses is
6132 SELECT ASSIGNMENT_STATUS_ID,OBJECT_VERSION_NUMBER
6133 FROM IRC_ASSIGNMENT_STATUSES
6134 WHERE ASSIGNMENT_ID = p_assignment_id
6135 AND TRUNC(STATUS_CHANGE_DATE)= p_validation_start_date;
6136 --
6137 /* cursor irc_offers is
6138 SELECT 'Y'
6139 FROM IRC_OFFERS
6140 WHERE ASSIGNMENT_ID = p_assignment_id; */
6141 --
6142 temp varchar2(10);
6143 l_asgt_status_id number;
6144 l_ovn number;
6145 l_proc varchar2(72) := g_package||'irc_delete_assgt_checks';
6146 --
6147 begin
6148 --
6149 if g_debug then
6150 hr_utility.set_location('Entering:'|| l_proc, 10);
6151 end if;
6152 --
6153 /* if per_asg_shd.g_old_rec.assignment_type = 'O' then
6154 if g_debug then
6155 hr_utility.set_location('Assignment of type O is found', 20);
6156 end if;
6157 --
6158 fnd_message.set_name('PER', 'ERROR_TO_BE_REPLACED_BY_IRC_1');
6159 fnd_message.raise_error;
6160 end if;
6161 --
6162 if ((p_datetrack_mode = 'ZAP') and ( per_asg_shd.g_old_rec.assignment_type = 'A')) then
6163 open irc_offers;
6164 fetch irc_offers into temp;
6165 close irc_offers;
6166 --
6167 if (temp = 'Y') then
6168 --
6169 if g_debug then
6170 hr_utility.set_location('IRC Offers available for this assignment', 30);
6171 end if;
6172 --
6173 fnd_message.set_name('PER', 'ERROR_TO_BE_REPLACED_BY_IRC_2');
6174 fnd_message.raise_error;
6175 end if;
6176 --
6177 end if;
6178 -- */
6179 open irc_asgt_statuses;
6180 LOOP
6181 fetch irc_asgt_statuses into l_asgt_status_id,l_ovn;
6182 EXIT WHEN irc_asgt_statuses%NOTFOUND;
6183 irc_ias_del.del( p_assignment_status_id => l_asgt_status_id,
6184 p_object_version_number => l_ovn);
6185 END LOOP;
6186 close irc_asgt_statuses;
6187 --
6188 if g_debug then
6189 hr_utility.set_location('Leaving:'|| l_proc, 40);
6190 end if;
6191 --
6192 end;
6193 --
6194 --
6195 -- ----------------------------------------------------------------------------
6196 -- |----------------------< ben_delete_assgt_checks >--------------------------|
6197 -- ----------------------------------------------------------------------------
6198 procedure ben_delete_assgt_checks
6199 (p_assignment_id in per_all_assignments_f.assignment_id%Type
6200 ,p_datetrack_mode in varchar2
6201 ,p_life_events_exists out NOCOPY boolean)
6202 is
6203 --
6204 cursor ben_le_checks is
6205 SELECT 'Y'
6206 FROM BEN_PER_IN_LER
6207 WHERE ASSIGNMENT_ID = p_assignment_id
6208 AND PER_IN_LER_STAT_CD = 'STRTD';
6209 --
6210 temp varchar2(10);
6211 l_proc varchar2(72) := g_package||'ben_delete_assgt_checks';
6212 begin
6213 --
6214 if g_debug then
6215 hr_utility.set_location('Entering:'|| l_proc, 10);
6216 end if;
6217 --
6218 if (p_datetrack_mode = 'ZAP') then
6219 open ben_le_checks;
6220 fetch ben_le_checks into temp;
6221 close ben_le_checks;
6222 --
6223 if (temp = 'Y') then
6224 --
6225 if g_debug then
6226 hr_utility.set_location('BEN Life Events available for this assignment', 20);
6227 end if;
6228 p_life_events_exists := true;
6229 --
6230 end if;
6231 --
6232 end if;
6233 if g_debug then
6234 hr_utility.set_location('Leaving:'|| l_proc, 30);
6235 end if;
6236 --
6237 end;
6238 --
6239 --surendra
6240 --
6241 procedure pre_delete
6242 (p_rec in per_asg_shd.g_rec_type,
6243 p_effective_date in date,
6244 p_datetrack_mode in varchar2,
6245 p_validation_start_date in date,
6246 p_validation_end_date in date,
6247 p_org_now_no_manager_warning out nocopy boolean,
6248 p_loc_change_tax_issues OUT nocopy boolean,
6249 p_delete_asg_budgets OUT nocopy boolean,
6250 p_element_salary_warning OUT nocopy boolean,
6251 p_element_entries_warning OUT nocopy boolean,
6252 p_spp_warning OUT nocopy boolean,
6253 p_cost_warning OUT nocopy boolean,
6254 p_life_events_exists OUT nocopy boolean,
6255 p_cobra_coverage_elements OUT nocopy boolean,
6256 p_assgt_term_elements OUT nocopy boolean,
6257 ---
6258 p_new_prim_ass_id OUT nocopy number,
6259 p_prim_change_flag OUT nocopy varchar2,
6260 p_new_end_date OUT nocopy date,
6261 p_new_primary_flag OUT nocopy varchar2,
6262 p_s_pay_id OUT nocopy number,
6263 p_cancel_atd OUT nocopy date,
6264 p_cancel_lspd OUT nocopy date,
6265 p_reterm_atd OUT nocopy date,
6266 p_reterm_lspd OUT nocopy date,
6267 ---
6268 p_appl_asg_new_end_date OUT nocopy date
6269 )
6270 is
6271 l_sys_status_type varchar2(100);
6272 l_ceil_seq number;
6273 l_new_end_date date;
6274 l_prim_change_flag varchar2(1);
6275 l_new_prim_flag varchar2(1);
6276 l_re_entry_point number;
6277 l_returned_warning varchar2(80);
6278 l_prim_date_from date;
6279 l_new_prim_ass_id number;
6280 l_cancel_atd date;
6281 l_cancel_lspd date;
6282 l_reterm_atd date;
6283 l_reterm_lspd date;
6284 l_rowid varchar2(100);
6285 --
6286 l_s_pos_id number;
6287 l_s_ass_num varchar2(30);
6288 l_s_org_id number;
6289 l_s_pg_id number;
6290 l_s_job_id number;
6291 l_s_grd_id number;
6292 l_s_pay_id number;
6293 l_s_def_code_comb_id number;
6294 l_s_soft_code_kf_id number;
6295 l_s_per_sys_st varchar2(300);
6296 l_s_ass_st_type_id number;
6297 l_s_prim_flag varchar2(10);
6298 l_s_sp_ceil_step_id number;
6299 l_s_pay_bas varchar2(300);
6300 l_pay_basis_id number; -- Added for Bug 4764140
6301
6302 l_old_emp_cat varchar2(30); -- Bug#13960540
6303 l_new_emp_cat varchar2(30); -- Bug#13960540
6304 --
6305 l_warning_text varchar2(240);
6306 l_loc_code varchar2(100);
6307 l_legislation_code per_business_groups.legislation_code%TYPE; --added for bug 6917728
6308 --
6309 cursor csr_ass_sys_type is
6310 select per_system_status
6311 from per_assignment_status_types
6312 where assignment_status_type_id = per_asg_shd.g_old_rec.assignment_status_type_id;
6313 --
6314 cursor csr_ass_step_sequence is
6315 select sequence
6316 from per_spinal_point_steps_f
6317 where step_id = per_asg_shd.g_old_rec.special_ceiling_step_id
6318 and p_effective_date between effective_start_date and effective_end_date;
6319 --
6320 cursor csr_ass_row_id is
6321 select rowid from per_all_assignments_f
6322 where assignment_id = p_rec.assignment_id
6323 and p_effective_date between effective_start_date and effective_end_date;
6324 --
6325 cursor csr_ass_loc_code is
6326 select location_code
6327 from hr_locations
6328 where location_id = per_asg_shd.g_old_rec.location_id;
6329 --
6330 --start changes for bug 6917728
6331 cursor csr_get_legislation_code is
6332 select bus.legislation_code
6333 from per_business_groups bus
6334 where bus.business_group_id = per_asg_shd.g_old_rec.business_group_id;
6335 --start changes for bug 6917728
6336
6337 l_proc varchar2(72) := g_package||'pre_delete';
6338 --
6339 begin
6340 --
6341 if g_debug then
6342 hr_utility.set_location('Entering:'|| l_proc, 10);
6343 end if;
6344 --
6345 open csr_ass_sys_type;
6346 fetch csr_ass_sys_type into l_sys_status_type;
6347 close csr_ass_sys_type;
6348 --
6349 open csr_ass_step_sequence;
6350 fetch csr_ass_step_sequence into l_ceil_seq;
6351 close csr_ass_step_sequence;
6352 --
6353 open csr_ass_row_id;
6354 fetch csr_ass_row_id into l_rowid;
6355 close csr_ass_row_id;
6356 --
6357 open csr_ass_loc_code;
6358 fetch csr_ass_loc_code into l_loc_code;
6359 close csr_ass_loc_code;
6360 --
6361 --
6362 --start changes for bug 6917728
6363 open csr_get_legislation_code;
6364 fetch csr_get_legislation_code into l_legislation_code;
6365 if csr_get_legislation_code%NOTFOUND then
6366 close csr_get_legislation_code;
6367 -- This should never happen
6368 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
6369 hr_utility.set_message_token('PROCEDURE', l_proc);
6370 hr_utility.set_message_token('STEP','210');
6371 hr_utility.raise_error;
6372 end if;
6373 close csr_get_legislation_code;
6374 --end changes for bug 6917728
6375 --
6376 -- IRC Checks.
6377 --
6378 irc_delete_assgt_checks
6379 (p_assignment_id => p_rec.assignment_id
6380 ,p_datetrack_mode => p_datetrack_mode
6381 ,p_validation_start_date => p_validation_start_date );
6382 --
6383 -- BEN Checks.
6384 --
6385 ben_delete_assgt_checks
6386 (p_assignment_id => p_rec.assignment_id
6387 ,p_datetrack_mode => p_datetrack_mode
6388 ,p_life_events_exists => p_life_events_exists );
6389 --
6390 if per_asg_shd.g_old_rec.assignment_type = 'A' then
6391 ---
6392 if g_debug then
6393 hr_utility.set_location('Applicant type Assignment', 20);
6394 end if;
6395 --
6396 per_app_asg_pkg.pre_delete_validation (
6397 p_business_group_id => per_asg_shd.g_old_rec.business_group_id,
6398 p_assignment_id => p_rec.assignment_id,
6399 p_application_id => per_asg_shd.g_old_rec.application_id,
6400 p_person_id => per_asg_shd.g_old_rec.person_id,
6401 p_session_date => p_effective_date, --:ctl_globals.session_date,
6402 p_validation_start_date => p_validation_start_date,
6403 p_validation_end_date => p_validation_end_date,
6404 p_delete_mode => p_datetrack_mode,
6405 p_new_end_date => p_appl_asg_new_end_date); -- :assgt.c_new_end_date ) ;
6406 ---
6407 elsif ((per_asg_shd.g_old_rec.assignment_type = 'E') OR (per_asg_shd.g_old_rec.assignment_type = 'C')) then
6408 ---
6409 if g_debug then
6410 hr_utility.set_location('EMP/CWK type Assignment', 30);
6411 end if;
6412 --
6413 l_re_entry_point := 999;
6414 l_returned_warning := null;
6415 --
6416 -- Note l_re_entry_point is EITHER
6417 -- the re-entry point for the update_and_delete_bundle S-S proc
6418 -- called by the pre_delete S-S proc.
6419 -- OR
6420 -- the re-entry point after the check_future_primary warning in
6421 -- the pre_delete S-S proc.
6422 --
6423 -- l_prim_change_flag := p_prim_change_flag;
6424 -- l_new_prim_flag := p_new_prim_flag;
6425 -- l_prim_date_from := p_prim_date_from;
6426 --
6427 while l_re_entry_point <> 0 loop
6428 if g_debug then
6429 hr_utility.set_location('l_re_entry_point :'||l_re_entry_point, 40);
6430 end if;
6431 --
6432 per_assignments_f2_pkg.pre_delete(
6433 p_datetrack_mode, -- p_del_mode,
6434 p_validation_start_date, -- p_val_st_date,
6435 per_asg_shd.g_old_rec.effective_start_date, -- p_eff_st_date,
6436 per_asg_shd.g_old_rec.effective_end_date, -- p_eff_end_date,
6437 per_asg_shd.g_old_rec.period_of_service_id, -- p_pd_os_id,
6438 l_sys_status_type, -- p_per_sys_st,
6439 p_rec.assignment_id, -- p_ass_id,
6440 p_effective_date, -- p_sess_date,
6441 p_new_end_date, -- need this in post_delete()
6442 p_validation_end_date, -- p_val_end_date,
6443 per_asg_shd.g_old_rec.payroll_id, -- p_pay_id,
6444 per_asg_shd.g_old_rec.grade_id, -- p_grd_id,
6445 per_asg_shd.g_old_rec.special_ceiling_step_id, -- p_sp_ceil_st_id,
6446 l_ceil_seq, -- p_ceil_seq,
6447 per_asg_shd.g_old_rec.person_id, -- p_per_id,
6448 per_asg_shd.g_old_rec.primary_flag, -- p_prim_flag,
6449 p_prim_change_flag, -- need this in post_delete()
6450 p_new_primary_flag, -- need this in post_delete()
6451 l_re_entry_point, -- no change
6452 l_returned_warning, -- no change
6453 p_cancel_atd, -- need this in post_delete()
6454 p_cancel_lspd, -- need this in post_delete()
6455 p_reterm_atd, -- need this in post_delete()
6456 p_reterm_lspd, -- need this in post_delete()
6457 l_prim_date_from, -- no change
6458 p_new_prim_ass_id, -- need this in post_delete()
6459 l_rowid, -- p_row_id,
6460 l_s_pos_id, -- modified from p_
6461 l_s_ass_num, -- modified from p_
6462 l_s_org_id, -- modified from p_
6463 l_s_pg_id, -- modified from p_
6464 l_s_job_id, -- modified from p_
6465 l_s_grd_id, -- modified from p_
6466 p_s_pay_id, -- need this in post_delete()
6467 l_s_def_code_comb_id, -- modified from p_
6468 l_s_soft_code_kf_id, -- modified from p_
6469 l_s_per_sys_st, -- modified from p_
6470 l_s_ass_st_type_id, -- modified from p_
6471 l_s_prim_flag, -- modified from p_
6472 l_s_sp_ceil_step_id, -- modified from p_
6473 l_s_pay_bas, -- modified from p_
6474 l_old_emp_cat, -- Bug#13960540
6475 l_new_emp_cat, -- Bug#13960540
6476 l_pay_basis_id -- Added for Bug 4764140
6477 );
6478 --
6479 if l_returned_warning is not null then
6480 if l_returned_warning = 'SHOW_LOV' then
6481 --
6482 -- Warning was returned to show candidate primary
6483 -- assignment LOV and get user to select a new primary
6484 -- assignment. It is not possible from API, so raise
6485 -- an application error.
6486 --
6487 fnd_message.set_name('PER', 'HR_449745_DEL_PRIM_ASG');
6488 fnd_message.raise_error;
6489 --
6490 elsif l_returned_warning = 'HR_ASS_TERM_COBRA_EXISTS' then
6491 p_cobra_coverage_elements := true;
6492 elsif l_returned_warning = 'HR_ASS_TERM_COBRA_EXISTS' then
6493 p_assgt_term_elements := true;
6494 end if;
6495 end if;
6496 --
6497 end loop;
6498 --
6499 -- It calls a procedure to check whether in next records location is
6500 -- different from the location that is present in the form. If location
6501 -- is different in any of the future records the user will not be allowed
6502 -- to delete the record. and he will get an warning This is done as an
6503 -- impact of date tracking of W4 screen
6504 --
6505 if g_debug then
6506 hr_utility.set_location('Before check_payroll_run checks', 50);
6507 end if;
6508
6509 --if condition added for bug 6917728
6510 -- extra check in if condition added for bug 8353075
6511 IF l_legislation_code = 'US'
6512 and not (hr_utility.chk_product_install ( 'GHR','US') ) then
6513 l_warning_text := pay_us_emp_dt_tax_val.check_payroll_run(
6514 p_rec.assignment_id, -- p_ass_id ,
6515 l_loc_code, -- p_loc_code,
6516 per_asg_shd.g_old_rec.location_id, -- p_loc_id ,
6517 p_effective_date, -- p_sess_date ,
6518 per_asg_shd.g_old_rec.effective_start_date, -- p_eff_st_date,
6519 per_asg_shd.g_old_rec.effective_end_date, -- p_eff_end_date,
6520 p_datetrack_mode -- l_del_mode
6521 );
6522 --
6523 if l_warning_text is not null then
6524 p_loc_change_tax_issues := true;
6525 else p_loc_change_tax_issues := false;
6526 end if;
6527 END if;
6528 --
6529 -- Now S-S checks.
6530 --
6531 -- l_new_end_date := p_new_end_date;
6532 --
6533 if g_debug then
6534 hr_utility.set_location('Before key_delrec ', 60);
6535 end if;
6536 --
6537 per_assignments_f2_pkg.key_delrec(
6538 p_datetrack_mode, -- l_del_mode,
6539 p_validation_start_date, -- p_val_st_date,
6540 per_asg_shd.g_old_rec.effective_start_date, -- p_eff_st_date,
6541 per_asg_shd.g_old_rec.effective_end_date, -- p_eff_end_date,
6542 per_asg_shd.g_old_rec.period_of_service_id, -- p_pd_os_id,
6543 l_sys_status_type, -- p_per_sys_st,
6544 p_rec.assignment_id, -- p_ass_id ,
6545 per_asg_shd.g_old_rec.grade_id, -- p_grd_id,
6546 per_asg_shd.g_old_rec.special_ceiling_step_id, -- p_sp_ceil_st_id,
6547 l_ceil_seq, -- p_ceil_seq,
6548 per_asg_shd.g_old_rec.person_id, -- p_per_id,
6549 p_effective_date, -- p_sess_date ,
6550 l_new_end_date, -- no change
6551 p_validation_end_date, -- p_val_end_date,
6552 per_asg_shd.g_old_rec.payroll_id, -- p_pay_id,
6553 l_pay_basis_id -- added for bug 4764140
6554 );
6555 --
6556 -- CHECK_TERM_BY_POS changes (called in per_assignments_f2_pkg.key_delrec)
6557 --
6558 declare
6559 l_rec2 per_asg_shd.g_rec_type;
6560 begin
6561 --
6562 l_rec2.assignment_id := p_rec.assignment_id; -- p_ass_id;
6563 l_rec2.position_id := per_asg_shd.g_old_rec.position_id; -- name_in('ASSGT.POSITION_ID');
6564 --
6565 if g_debug then
6566 hr_utility.set_location('Before per_pqh_shr.per_asg_bus call ', 70);
6567 end if;
6568 per_pqh_shr.per_asg_bus(
6569 p_event => 'DELETE_VALIDATE'
6570 ,p_rec => l_rec2
6571 ,p_effective_date => p_effective_date -- p_sess_date ,
6572 ,p_validation_start_date => p_validation_start_date -- l_validation_start_date
6573 ,p_validation_end_date => p_validation_end_date -- l_validation_end_date
6574 ,p_datetrack_mode => p_datetrack_mode -- l_del_mode
6575 );
6576 --
6577 end;
6578 --
6579 -- p_new_prim_ass_id := l_new_prim_ass_id;
6580 -- p_prim_change_flag := l_prim_change_flag;
6581 --
6582 end if; -- End of EMP/CWK type Assignment validation checks.
6583 --
6584 if g_debug then
6585 hr_utility.set_location('Leaving :'||l_proc, 80);
6586 end if;
6587 --
6588 end; -- End of pre-delete checks
6589 --
6590 --
6591 --surendra
6592 --
6593 procedure post_delete
6594 (p_rec in per_asg_shd.g_rec_type,
6595 p_effective_date in date,
6596 p_datetrack_mode in varchar2,
6597 p_validation_start_date in date,
6598 p_validation_end_date in date,
6599 p_org_now_no_manager_warning out nocopy boolean,
6600 p_loc_change_tax_issues OUT nocopy boolean,
6601 p_delete_asg_budgets OUT nocopy boolean,
6602 p_element_salary_warning OUT nocopy boolean,
6603 p_element_entries_warning OUT nocopy boolean,
6604 p_spp_warning OUT nocopy boolean,
6605 p_cost_warning OUT nocopy boolean,
6606 p_life_events_exists OUT nocopy boolean,
6607 p_cobra_coverage_elements OUT nocopy boolean,
6608 p_assgt_term_elements OUT nocopy boolean,
6609 ---
6610 p_new_prim_ass_id IN number,
6611 p_prim_change_flag IN varchar2,
6612 p_new_end_date IN date,
6613 p_new_primary_flag IN varchar2,
6614 p_s_pay_id IN number,
6615 p_cancel_atd IN date,
6616 p_cancel_lspd IN date,
6617 p_reterm_atd IN date,
6618 p_reterm_lspd IN date,
6619 ---
6620 p_appl_asg_new_end_date IN date)
6621 is
6622 --
6623 l_sys_status_type varchar2(100);
6624 l_warning varchar2(80);
6625 l_future_spp_warning boolean;
6626 l_cost_warning boolean;
6627 l_prim_change_flag varchar2(10);
6628 l_new_prim_ass_id number;
6629 --
6630 l_appl_cost_warning boolean;
6631 --
6632 cursor csr_ass_sys_type is
6633 select per_system_status
6634 from per_assignment_status_types
6635 where assignment_status_type_id = p_rec.assignment_status_type_id;
6636 --
6637 l_proc varchar2(72) := g_package||'post_delete';
6638 --
6639 begin
6640 --
6641 if g_debug then
6642 hr_utility.set_location('Entering :'||l_proc, 10);
6643 end if;
6644 --
6645 open csr_ass_sys_type;
6646 fetch csr_ass_sys_type into l_sys_status_type;
6647 close csr_ass_sys_type;
6648 --
6649 l_new_prim_ass_id := p_new_prim_ass_id;
6650 l_prim_change_flag := p_prim_change_flag;
6651 --
6652 if per_asg_shd.g_old_rec.assignment_type = 'A' then
6653 ---
6654 if g_debug then
6655 hr_utility.set_location('Applicant type Assignment', 20);
6656 end if;
6657 --
6658 if ( p_datetrack_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
6659 if ( p_appl_asg_new_end_date is null ) then
6660 if ( p_validation_end_date = hr_api.g_eot ) then
6661 hr_assignment.tidy_up_ref_int ( p_rec.assignment_id, -- p_assignment_id,
6662 'FUTURE',
6663 p_validation_end_date, -- p_validation_end_date,
6664 per_asg_shd.g_old_rec.effective_end_date, -- p_effective_end_date,
6665 null,
6666 null ,
6667 l_appl_cost_warning) ; -- used to catch the cost warning
6668 -- but as Apl asg's can't have costing
6669 -- records no need to return to caller.
6670 end if;
6671 else hr_assignment.tidy_up_ref_int ( p_rec.assignment_id, -- p_assignment_id,
6672 'FUTURE',
6673 p_validation_end_date, -- p_new_end_date,
6674 per_asg_shd.g_old_rec.effective_end_date, -- p_effective_end_date,
6675 null,
6676 null,
6677 l_appl_cost_warning ) ;
6678 end if;
6679 --
6680 if ( p_appl_asg_new_end_date is not null ) then
6681 -- set_end_date ( p_new_end_date , p_assignment_id ) ; -- copied this logic from per_app_asg_pkg
6682 -- Sets an end date on rows which are deleted with delete mode FUTURE_CHANGES or NEXT_CHANGE
6683 --
6684 if g_debug then
6685 hr_utility.set_location('Before updating assignment end date', 30);
6686 end if;
6687 --
6688 update per_assignments_f a
6689 set a.effective_end_date = p_appl_asg_new_end_date
6690 where a.assignment_id = p_rec.assignment_id -- p_assignment_id
6691 and a.effective_end_date = (
6692 select max(a2.effective_end_date)
6693 from per_assignments_f a2
6694 where a2.assignment_id = a.assignment_id);
6695 end if;
6696 --
6697 per_app_asg_pkg.cleanup_letters ( p_rec.assignment_id); -- p_assignment_id );
6698 --
6699 end if; -- end of code for 'FUTURE_CHANGE','DELETE_NEXT_CHANGE' modes.
6700 --
6701 if ( p_datetrack_mode = 'ZAP' ) then
6702 per_app_asg_pkg.post_delete ( p_assignment_id => p_rec.assignment_id, -- :ASSGT.ASSIGNMENT_ID,
6703 p_validation_start_date => p_validation_start_date); -- :ASSGT.VALIDATION_START_DATE ) ;
6704 end if; -- end of code for 'ZAP' mode.
6705 ---
6706 elsif ((per_asg_shd.g_old_rec.assignment_type = 'E') OR (per_asg_shd.g_old_rec.assignment_type = 'C')) then
6707 ---
6708 if g_debug then
6709 hr_utility.set_location('EMP/CWK type assignment checks ', 40);
6710 end if;
6711 --
6712 per_assignments_f1_pkg.post_delete(
6713 p_rec.assignment_id, -- p_ass_id,
6714 per_asg_shd.g_old_rec.grade_id, -- p_grd_id,
6715 p_effective_date, -- p_sess_date,
6716 p_new_end_date, -- from pre_del()
6717 p_validation_end_date, -- p_val_end_date,
6718 per_asg_shd.g_old_rec.effective_end_date, -- p_eff_end_date,
6719 p_datetrack_mode, -- p_del_mode,
6720 p_validation_start_date, -- p_val_st_date,
6721 p_new_primary_flag, -- from pre_del()
6722 hr_api.g_eot, -- p_eot,
6723 per_asg_shd.g_old_rec.period_of_service_id, -- p_pd_os_id,
6724 l_new_prim_ass_id, -- l_new_prim_ass_id,
6725 l_prim_change_flag, -- l_prim_change_flag,
6726 l_sys_status_type, -- p_per_sys_st,
6727 per_asg_shd.g_old_rec.business_group_id, -- p_bg_id,
6728 p_s_pay_id, -- p_old_pay_id,
6729 per_asg_shd.g_old_rec.payroll_id, -- p_new_pay_id,
6730 p_cancel_atd, -- from pre_del()
6731 p_cancel_lspd, -- from pre_del()
6732 p_reterm_atd, -- from pre_del()
6733 p_reterm_lspd, -- from pre_del()
6734 l_warning,
6735 l_future_spp_warning,
6736 l_cost_warning);
6737 --
6738 If l_warning = 'HR_7016_ASS_ENTRIES_CHANGED' then
6739 p_element_salary_warning := true;
6740 else p_element_salary_warning := false;
6741 end if;
6742 --
6743 If l_warning = 'HR_7442_ASS_SAL_ENT_CHANGED' then
6744 p_element_entries_warning := true;
6745 else p_element_entries_warning := false;
6746 end if;
6747 --
6748 p_spp_warning := l_future_spp_warning;
6749 p_cost_warning := l_cost_warning;
6750 --
6751 end if; -- End of EMP/CWK type Assignment validation checks.
6752 ---
6753 if g_debug then
6754 hr_utility.set_location('Leaving :'||l_proc, 50);
6755 end if;
6756 --
6757 end;
6758 --3
6759
6760 -- ----------------------------------------------------------------------------
6761 -- Bug#14700032. FTE Calculation logic is available in PUI through PQH_GEN.pld
6762 -- The same code is available via pegbasgp.pkb from SSHR for UK legislation only.
6763 -- This procedure is created for calculating the FTE values from SSHR for all
6764 -- other legislations except GB.
6765 -- This procedure is used to calculate FTE.
6766 -- ----------------------------------------------------------------------------
6767
6768 -- |-------------------------< auto_calc_FTE >--------------------------|
6769
6770 procedure auto_calc_fte (p_assignment_id in number,
6771 p_effective_start_date in Date)
6772 IS
6773 l_business_group_id pqp_configuration_values.business_group_id%TYPE;
6774 l_legislation_code pqp_configuration_values.legislation_code%TYPE;
6775 l_abv_uom pqp_configuration_values.pcv_information1%TYPE;
6776 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6777 l_effective_start_date per_all_assignments_f.effective_start_date%TYPE;
6778 l_assignment_type varchar2(30);
6779
6780 l_error_message fnd_new_messages.message_text%TYPE;
6781 g_debug boolean := true;
6782 l_proc varchar2(72) := g_package || 'auto_calc_fte';
6783
6784 -- Pick up only those UOMS which are enabled and have some
6785 -- process defnition. There is no validation as yet on the configuration
6786 -- which prevents the user from storing just one, however subsequent
6787 -- processing cannot take place without both.
6788
6789 CURSOR csr_abvm_uoms_to_process
6790 (p_business_group_id IN NUMBER
6791 ,p_legislation_code IN VARCHAR2
6792 ) IS
6793 SELECT mtn.configuration_value_id
6794 ,mtn.configuration_name
6795 ,mtn.business_group_id
6796 ,mtn.legislation_code
6797 ,mtn.pcv_information1 uom
6798 ,mtn.pcv_information2 is_enabled
6799 ,def.configuration_value_id defn_config_value_id
6800 ,def.configuration_name defn_config_name
6801 ,def.business_group_id defn_bg_id
6802 ,def.legislation_code defn_leg_code
6803 ,def.pcv_information4 defn_custom_function
6804 FROM pqp_configuration_values mtn
6805 ,pqp_configuration_values def
6806 WHERE mtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
6807 AND def.pcv_information_category = 'PQP_ABVM_DEFINITION'
6808 AND def.pcv_information1 = mtn.pcv_information1
6809 AND mtn.pcv_information2 = 'Y'
6810 AND ( mtn.business_group_id = p_business_group_id
6811 OR
6812 ( ( mtn.business_group_id IS NULL AND mtn.legislation_code = p_legislation_code )
6813 AND -- there does not exist a config for this UOM at bg level
6814 NOT EXISTS
6815 (SELECT 1
6816 FROM pqp_configuration_values bgmtn
6817 WHERE bgmtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
6818 AND bgmtn.pcv_information1 = mtn.pcv_information1
6819 AND bgmtn.business_group_id = p_business_group_id
6820 ) -- NOT EXISTS
6821 ) -- OR
6822 OR
6823 ( ( mtn.business_group_id IS NULL AND mtn.legislation_code IS NULL )
6824 AND -- there does not exist a config for this UOM at a higher level
6825 NOT EXISTS
6826 (SELECT 1
6827 FROM pqp_configuration_values hlmtn
6828 WHERE hlmtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
6829 AND hlmtn.pcv_information1 = mtn.pcv_information1
6830 AND ( hlmtn.business_group_id = p_business_group_id
6831 OR
6832 hlmtn.legislation_code = p_legislation_code
6833 )
6834 ) -- NOT EXISTS
6835 ) -- OR
6836 ) -- AND
6837 AND ( def.business_group_id = p_business_group_id
6838 OR
6839 def.business_group_id IS NULL AND def.legislation_code = p_legislation_code
6840 OR
6841 def.business_group_id IS NULL AND def.legislation_code IS NULL
6842 );
6843
6844 CURSOR get_ass_typ_bus_grp_id(p_assignment_id IN NUMBER,
6845 p_effective_date in Date)
6846 IS
6847 select assignment_type, business_group_id
6848 from per_all_assignments_f
6849 where assignment_id = p_assignment_id
6850 and p_effective_date between effective_start_date and effective_end_date ;
6851
6852
6853 BEGIN
6854
6855 IF g_debug is null THEN
6856 g_debug := hr_utility.debug_enabled;
6857 END IF;
6858
6859 hr_utility.trace('Entering '||l_proc);
6860 hr_utility.trace('p_assignment_id :'||p_assignment_id);
6861 hr_utility.trace('p_effective_start_date :'||p_effective_start_date);
6862
6863 BEGIN
6864
6865 l_assignment_id := p_assignment_id;
6866 l_effective_start_date := p_effective_start_date;
6867
6868 l_legislation_code := PER_ASG_BUS1.RETURN_LEGISLATION_CODE(P_ASSIGNMENT_ID => P_ASSIGNMENT_ID);
6869
6870 --fetch BG_id and ASG_type
6871 open get_ass_typ_bus_grp_id(p_assignment_id => l_assignment_id, p_effective_date => l_effective_start_date);
6872 fetch get_ass_typ_bus_grp_id into l_assignment_type, l_business_group_id;
6873 close get_ass_typ_bus_grp_id;
6874
6875 hr_utility.trace('l_business_group_id :'||l_business_group_id);
6876 hr_utility.trace('l_assignment_type :'||l_assignment_type);
6877 hr_utility.trace('l_legislation_code :'||l_legislation_code);
6878
6879
6880 IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
6881 THEN
6882
6883 --
6884 -- This code will attempt to update the FTE value for
6885 -- an assignment. It is within its own block because we don't
6886 -- want to rollback the update to the assignment in the event that
6887 -- the FTE processing fails for some reason.
6888 hr_utility.trace('Inside '||l_proc||l_assignment_id||' on '||
6889 fnd_date.date_to_canonical(l_effective_start_date)
6890 );
6891 FOR this_abvm IN csr_abvm_uoms_to_process
6892 (p_business_group_id => l_business_group_id
6893 ,p_legislation_code => l_legislation_code
6894 )
6895 LOOP
6896
6897 IF g_debug THEN
6898 hr_utility.trace('auto_calc_fte this_abvm.configuration_value_id:'||this_abvm.configuration_value_id);
6899 hr_utility.trace('auto_calc_fte this_abvm.configuration_name:'||this_abvm.configuration_name);
6900 hr_utility.trace('auto_calc_fte this_abvm.business_group_id:'||this_abvm.business_group_id);
6901 hr_utility.trace('auto_calc_fte this_abvm.legislation_code:'||this_abvm.legislation_code);
6902 hr_utility.trace('auto_calc_fte this_abvm.uom:'||this_abvm.uom);
6903 hr_utility.trace('auto_calc_fte this_abvm.is_enabled:'||this_abvm.is_enabled);
6904 hr_utility.trace('auto_calc_fte this_abvm.defn_config_value_id:'||this_abvm.defn_config_value_id);
6905 hr_utility.trace('auto_calc_fte this_abvm.defn_config_name:'||this_abvm.defn_config_name);
6906 hr_utility.trace('auto_calc_fte this_abvm.defn_bg_id:'||this_abvm.defn_bg_id);
6907 hr_utility.trace('auto_calc_fte this_abvm.defn_leg_code:'||this_abvm.defn_leg_code);
6908 hr_utility.trace('auto_calc_fte this_abvm.defn_custom_function:'||this_abvm.defn_custom_function);
6909 END IF;
6910
6911
6912 l_abv_uom := this_abvm.uom;
6913
6914 hr_utility.trace('this_abvm.uom is :'||this_abvm.uom);
6915 hr_utility.trace('this_abvm.defn_custom_function is :'||this_abvm.defn_custom_function);
6916
6917 IF ( this_abvm.defn_custom_function is null or
6918 this_abvm.defn_custom_function <> 'pqp_budget_maintenance.get_FTE_event_dates' )
6919 THEN
6920 pqp_budget_maintenance.maintain_abv_for_assignment
6921 (p_uom => this_abvm.uom
6922 ,p_assignment_id => l_assignment_id
6923 ,p_business_group_id => l_business_group_id
6924 ,p_effective_date => l_effective_start_date
6925 ,p_action => 'Normal'
6926 );
6927
6928 END IF;
6929 END LOOP; -- FOR this_abvm IN csr_abvm_uoms_to_process
6930
6931 IF g_debug THEN
6932 hr_utility.trace('Leaving '||l_proc||' for '|| l_assignment_id||' on '||
6933 fnd_date.date_to_canonical(l_effective_start_date));
6934 END IF;
6935 END IF; -- IF (l_assignment_id IS NOT NULL AND l_assignment_type = 'E')
6936 END;
6937
6938 EXCEPTION
6939 WHEN OTHERS THEN
6940 hr_utility.trace('Error in '||l_proc||' for '||l_assignment_id||' on '||
6941 fnd_date.date_to_canonical(l_effective_start_date));
6942 l_error_message := fnd_message.get;
6943 l_error_message := NVL(RTRIM(LTRIM(l_error_message)),'SQLERRM:'||SQLERRM);
6944 IF g_debug THEN
6945 hr_utility.trace(l_proc||' : Error msg '||l_error_message);
6946 END IF;
6947 hr_utility.set_message(8303,'PQP_230514_FTE_FAILURE');
6948 fnd_message.set_token('ABVUOM',l_abv_uom);
6949 fnd_message.set_token('ERRORMSG',l_error_message);
6950 hr_utility.set_warning;
6951 end auto_calc_fte;
6952
6953 end hr_assignment_internal;