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