[Home] [Help]
PACKAGE BODY: APPS.HR_CHANGE_START_DATE_API
Source
1 Package Body hr_change_start_date_api as
2 /* $Header: pehirapi.pkb 120.15.12010000.2 2008/08/06 09:12:48 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_change_start_date_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< check_not_supervisor >------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure check_not_supervisor(p_person_id NUMBER
13 ,p_new_start_date DATE
14 ,p_old_start_date DATE) is
15 --
16 l_dummy VARCHAR2(1);
17 --
18 cursor supervisor
19 is
20 select 'Y'
21 from per_assignments_f paf
22 where paf.assignment_type in ('E','C')
23 and paf.supervisor_id = p_person_id
24 and p_new_start_date > paf.effective_start_date
25 and paf.effective_end_date >= p_old_start_date ;
26 --
27 begin
28 open supervisor;
29 fetch supervisor into l_dummy;
30 if supervisor%FOUND then
31 close supervisor;
32 fnd_message.set_name('PAY','HR_51031_INV_HIRE_CHG_IS_SUPER');
33 app_exception.raise_exception;
34 end if;
35 close supervisor;
36 --
37 end check_not_supervisor;
38 --
39 -- ----------------------------------------------------------------------------
40 -- |--------------------------< check_pds_pdp >-------------------------------|
41 -- ----------------------------------------------------------------------------
42 --
43 procedure check_pds_pdp(p_person_id NUMBER
44 ,p_new_start_date DATE
45 ,p_old_start_date DATE
46 ,p_type VARCHAR2) is
47 --
48 l_dummy VARCHAR2(1);
49 --
50 cursor csr_pds_exists is
51 select 'y' from dual where exists
52 (select 'x'
53 from per_periods_of_service pds
54 where pds.person_id = p_person_id
55 and pds.date_start = p_old_start_date);
56 --
57 cursor csr_pdp_exists is
58 select 'y' from dual where exists
59 (select 'x'
60 from per_periods_of_placement pdp
61 where pdp.person_id = p_person_id
62 and pdp.date_start = p_old_start_date);
63 --
64 begin
65 if p_type = 'E' then
66 open csr_pds_exists;
67 fetch csr_pds_exists into l_dummy;
68 if csr_pds_exists%notfound
69 and p_old_start_date is not null then
70 close csr_pds_exists;
71 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
72 hr_utility.set_message_token('PROCEDURE','check_pds_pdp');
73 hr_utility.set_message_token('STEP','1');
74 hr_utility.raise_error;
75 else
76 close csr_pds_exists;
77 end if;
78 elsif p_type = 'C' then
79 open csr_pdp_exists;
80 fetch csr_pdp_exists into l_dummy;
81 if csr_pdp_exists%notfound
82 and p_old_start_date is not null then
83 close csr_pdp_exists;
84 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
85 hr_utility.set_message_token('PROCEDURE','check_pds_pdp');
86 hr_utility.set_message_token('STEP','2');
87 hr_utility.raise_error;
88 else
89 close csr_pdp_exists;
90 end if;
91 else
92 null;
93 end if;
94 --
95 end check_pds_pdp;
96 --
97 -- ----------------------------------------------------------------------------
98 -- |-----------------------< check_un_ended_pds_pdp >--------------------------|
99 -- ----------------------------------------------------------------------------
100 --
101 procedure check_un_ended_pds_pdp(p_person_id NUMBER
102 ,p_new_start_date DATE
103 --
104 -- 115.30,115.33 (START)
105 --
106 ,p_old_start_date DATE
107 ,p_hd_rule_found BOOLEAN
108 ,p_hd_rule_value VARCHAR2
109 ,p_fpd_rule_found BOOLEAN
110 ,p_fpd_rule_value VARCHAR2
111 --
112 -- 115.30,115.33 (END)
113 --
114 ,p_type VARCHAR2) is
115 --
116 l_dummy VARCHAR2(1);
117 --
118 cursor csr_pds_exists is
119 select 'y' from dual where exists
120 (select 'x'
121 from per_periods_of_service pds
122 where pds.person_id = p_person_id
123 and pds.actual_termination_date < p_new_start_date
124 and pds.final_process_date >= p_new_start_date);
125 --
126 cursor csr_pdp_exists is
127 select 'y' from dual where exists
128 (select 'x'
129 from per_periods_of_placement pdp
130 where pdp.person_id = p_person_id
131 and pdp.actual_termination_date < p_new_start_date
132 and pdp.final_process_date >= p_new_start_date);
133 --
134 -- 115.30 (START)
135 --
136 CURSOR csr_inv_new_hd IS
137 SELECT null
138 FROM per_periods_of_service
139 WHERE person_id = p_person_id
140 AND p_old_start_date BETWEEN NVL(last_standard_process_date,
141 actual_termination_date)+1
142 AND final_process_date
143 AND p_new_start_date <= NVL(last_standard_process_date,actual_termination_date);
144 --
145 CURSOR csr_overlap (p_start_date DATE) IS
146 SELECT null
147 FROM per_periods_of_service
148 WHERE person_id = p_person_id
149 AND csr_overlap.p_start_date BETWEEN NVL(last_standard_process_date,
150 actual_termination_date)+1
151 AND NVL(final_process_date,hr_api.g_eot);
152 --
153 l_old_start_date_in_range BOOLEAN;
154 l_new_start_date_in_range BOOLEAN;
155 --
156 CURSOR csr_new_start_date IS
157 SELECT null
158 FROM per_periods_of_service pds
159 WHERE pds.person_id = p_person_id
160 AND pds.date_start = p_old_start_date
161 AND p_new_start_date BETWEEN pds.actual_termination_date
162 AND pds.final_process_date;
163 --
164 -- 115.30 (END)
165 --
166 begin
167 if p_type = 'E' then
168 --
169 if ( NOT p_fpd_rule_found
170 OR
171 (p_fpd_rule_found AND nvl(p_fpd_rule_value,'N') = 'N')
172 ) then
173 --
174 -- Rehire before FPD not allowed
175 --
176 open csr_pds_exists;
177 fetch csr_pds_exists into l_dummy;
178 if csr_pds_exists%found then
179 close csr_pds_exists;
180 hr_utility.set_message('800','PER_289309_ST_DATE_CHG_NOTALWD');
181 hr_utility.raise_error;
182 else
183 close csr_pds_exists;
184 end if;
185 else
186 --
187 -- Rehire before FPD allowed
188 --
189 -- Check if new hire date is before LSPD of prev PDS
190 --
191 OPEN csr_inv_new_hd;
192 FETCH csr_inv_new_hd INTO l_dummy;
193 IF csr_inv_new_hd%FOUND THEN
194 CLOSE csr_inv_new_hd;
195 hr_utility.set_message('800','HR_449762_HD_GT_PREV_PDS');
196 hr_utility.raise_error;
197 END IF;
198 CLOSE csr_inv_new_hd;
199 --
200 -- Check if old start date overlaps another PDS
201 --
202 OPEN csr_overlap(p_old_start_date);
203 FETCH csr_overlap INTO l_dummy;
204 IF csr_overlap%FOUND THEN
205 l_old_start_date_in_range := TRUE;
206 ELSE
207 l_old_start_date_in_range := FALSE;
208 END IF;
209 CLOSE csr_overlap;
210 --
211 -- Check if new start date overlaps another PDS
212 --
213 OPEN csr_overlap(p_new_start_date);
214 FETCH csr_overlap INTO l_dummy;
215 IF csr_overlap%FOUND THEN
216 l_new_start_date_in_range := TRUE;
217 ELSE
218 l_new_start_date_in_range := FALSE;
219 END IF;
220 CLOSE csr_overlap;
221 --
222 -- Check if gaps are being updated top overlaps or vice versa
223 --
224 IF (l_new_start_date_in_range AND NOT l_old_start_date_in_range)
225 OR
226 (NOT l_new_start_date_in_range AND l_old_start_date_in_range)
227 THEN
228 hr_utility.set_message('800','HR_449760_EMP_HD_PDS');
229 hr_utility.raise_error;
230 END IF;
231 end if; -- FPD rule check
232 --
233 -- Check the new hire date with other dates on PDS
234 --
235 OPEN csr_new_start_date;
236 FETCH csr_new_start_date INTO l_dummy;
237 IF csr_new_start_date%FOUND THEN
238 CLOSE csr_new_start_date;
239 hr_utility.set_message('800','HR_449739_EMP_HD_ATD');
240 hr_utility.raise_error;
241 END IF;
242 CLOSE csr_new_start_date;
243 --
244 elsif p_type = 'C' then
245 open csr_pdp_exists;
246 fetch csr_pdp_exists into l_dummy;
247 if csr_pdp_exists%found then
248 close csr_pdp_exists;
249 hr_utility.set_message('800','PER_289309_ST_DATE_CHG_NOTALWD');
250 hr_utility.raise_error;
251 else
252 close csr_pdp_exists;
253 end if;
254 else
255 null;
256 end if;
257 --
258 end check_un_ended_pds_pdp;
259 --
260 -- ----------------------------------------------------------------------------
261 -- |--------------------------< check_for_compl_actions >---------------------|
262 -- ----------------------------------------------------------------------------
263 --
264 procedure check_for_compl_actions(p_person_id NUMBER
265 ,p_old_start_date DATE
266 ,p_new_start_date DATE
267 ,p_type VARCHAR2) is
268 --
269 -- Bug 4221947. In below cursor, check the payroll actions in between old start
270 -- date and one day before new start date.
271 --
272 cursor csr_compl_actions is
273 select 'y' from dual where exists
274 (SELECT NULL
275 FROM pay_payroll_actions pac,
276 pay_assignment_actions act,
277 per_assignments_f asg
278 WHERE asg.person_id = p_person_id
279 AND act.assignment_id = asg.assignment_id
280 AND asg.assignment_type = p_type
281 AND pac.payroll_action_id = act.payroll_action_id
282 AND pac.action_status = 'C'
283 AND ((pac.effective_date BETWEEN p_old_start_date AND (p_new_start_date-1))
284 OR (pac.date_earned BETWEEN p_old_start_date AND (p_new_start_date-1))));
285 --
286 l_dummy varchar2(1);
287 --
288 begin
289 open csr_compl_actions;
290 fetch csr_compl_actions into l_dummy;
291 if csr_compl_actions%found then
292 close csr_compl_actions;
293 hr_utility.set_message(801,'HR_51810_EMP_COMPL_ACTIONS');
294 hr_utility.raise_error;
295 else
296 close csr_compl_actions;
297 end if;
298 --
299 end check_for_compl_actions;
300 --
301 -- ----------------------------------------------------------------------------
302 -- |-------------------------< check_contig_pds_pdp >-------------------------|
303 -- ----------------------------------------------------------------------------
304 --
305 procedure check_contig_pds_pdp(p_person_id NUMBER
306 ,p_old_start_date DATE
307 ,p_type VARCHAR2) is
308 --
309 l_action_chk VARCHAR2(1) := 'N';
310 l_prev_end_date DATE;
311 l_date_start DATE;
312 l_act_term_date DATE;
313 --
314 cursor pds is
315 select date_start,actual_termination_date
316 from per_periods_of_service
317 WHERE PERSON_ID = P_PERSON_ID
318 ORDER BY date_start;
319 --
320 cursor pdp is
321 select date_start,actual_termination_date
322 from per_periods_of_placement
323 WHERE PERSON_ID = P_PERSON_ID
324 ORDER BY date_start;
325 --
326 begin
327 l_action_chk := 'N';
328 if p_type = 'E' then
329 OPEN pds;
330 l_prev_end_date := to_date('01/01/0001','DD/MM/YYYY');
331 LOOP
332 FETCH pds INTO l_date_start,l_act_term_date;
333 EXIT WHEN pds%NOTFOUND;
334 IF (l_date_start - 1 = l_prev_end_date) AND
335 (p_old_start_date = l_date_start) THEN
336 l_action_chk := 'Y';
337 EXIT;
338 END IF;
339 l_prev_end_date := l_act_term_date;
340 END LOOP;
341 CLOSE pds;
342 elsif p_type = 'C' then
343 OPEN pdp;
344 l_prev_end_date := to_date('01/01/0001','DD/MM/YYYY');
345 LOOP
346 FETCH pdp INTO l_date_start,l_act_term_date;
347 EXIT WHEN pdp%NOTFOUND;
348 IF (l_date_start - 1 = l_prev_end_date) AND
349 (p_old_start_date = l_date_start) THEN
350 l_action_chk := 'Y';
351 EXIT;
352 END IF;
353 l_prev_end_date := l_act_term_date;
354 END LOOP;
355 CLOSE pdp;
356 else
357 null;
358 end if;
359 IF l_action_chk = 'Y' THEN
360 hr_utility.set_message(801,'HR_51811_EMP_CONTIG_POS');
361 hr_utility.raise_error;
362 END IF;
363 --
364 end check_contig_pds_pdp;
365 --
366 -- ----------------------------------------------------------------------------
367 -- |-------------------------< check_supe_pay >-------------------------------|
368 -- ----------------------------------------------------------------------------
369 --
370 procedure check_supe_pay(p_pds_or_pdp_id NUMBER
371 ,p_new_start_date DATE
372 ,p_type VARCHAR2) is
373 l_payroll_id number;
374 l_supervisor_id number;
375 l_temp varchar2(1);
376 --
377 -- Cannot move start date if there are assignment changes
378 -- only need to test the first assignment row
379 --
380 cursor assignment_pds is
381 select a.payroll_id , a.supervisor_id
382 from per_assignments_f a,
383 per_periods_of_service p
384 where a.period_of_service_id = p.period_of_service_id
385 and p.period_of_service_id = p_pds_or_pdp_id
386 and p_type= 'E'
387 and p.date_start = a.effective_start_date;
388 --
389 cursor assignment_pdp is
390 select a.payroll_id , a.supervisor_id
391 from per_assignments_f a,
392 per_periods_of_placement p
393 where a.period_of_placement_date_start = p.date_start
394 and a.person_id = p.person_id
395 and p.period_of_placement_id = p_pds_or_pdp_id
396 and p_type= 'C'
397 and p.date_start = a.effective_start_date;
398 --
399 begin
400 if p_type = 'E' then
401 open assignment_pds;
402 fetch assignment_pds into l_payroll_id,l_supervisor_id;
403 close assignment_pds;
404 elsif p_type = 'C' then
405 open assignment_pdp;
406 fetch assignment_pdp into l_payroll_id,l_supervisor_id;
407 close assignment_pdp;
408 end if;
409 --
410 if l_payroll_id is not null then --currently always null for p_type = C
411 begin
412 select '1' into l_temp
413 from dual
414 where exists ( select payroll_id
415 from pay_payrolls_f
416 where payroll_id = l_payroll_id
417 and p_new_start_date between
418 effective_start_date and effective_end_date
419 );
420 exception
421 when no_data_found then
422 hr_utility.set_message('801','HR_7679_EMP_SUP_PAY_NOT_EXIST');
423 hr_utility.raise_error;
424 end;
425 end if;
426 if l_supervisor_id is not null then
427 begin
428 select '1' into l_temp
429 from dual
430 where exists ( select person_id
431 from per_all_people_f -- Fix 3562224
432 where person_id = l_supervisor_id
433 and current_employee_flag = 'Y'
434 and p_new_start_date between
435 effective_start_date and effective_end_date
436 );
437 exception
438 when no_data_found then
439 hr_utility.set_message('801','HR_7680_EMP_SUP_PAY_NOT_EXIST');
440 hr_utility.raise_error;
441 end;
442 end if;
443 --
444 end check_supe_pay;
445 --
446 -- ----------------------------------------------------------------------------
447 -- |-------------------------< check_sp_placements >--------------------------|
448 -- ----------------------------------------------------------------------------
449 --
450 procedure check_sp_placements(p_person_id NUMBER
451 ,p_pds_or_pdp_id NUMBER
452 ,p_new_start_date DATE
453 ,p_type VARCHAR2) is
454 cursor csr_sp_placement_pds is
455 select 'x' from dual where exists
456 (select 1
457 from per_spinal_point_placements_f sp,
458 per_periods_of_service p,
459 per_assignments_f a
460 where a.person_id = p_person_id
461 and a.period_of_service_id = p.period_of_service_id
462 and p.period_of_service_id = p_pds_or_pdp_id
463 and p_type = 'E'
464 and a.assignment_id = sp.assignment_id
465 and sp.effective_start_date > p.date_start
466 -- and sp.effective_start_date < p_new_start_date); --update for bug 6021004
467 and sp.effective_start_date <= p_new_start_date);
468 --
469 cursor csr_sp_placement_pdp is
470 select 'x' from dual where exists
471 (select 1
472 from per_spinal_point_placements_f sp,
473 per_periods_of_placement p,
474 per_assignments_f a
475 where a.person_id = p_person_id
476 and a.period_of_placement_date_start = p.date_start
477 and p.period_of_placement_id = p_pds_or_pdp_id
478 and p_type = 'C'
479 and a.assignment_id = sp.assignment_id
480 and sp.effective_start_date > p.date_start
481 and sp.effective_start_date < p_new_start_date);
482 --
483 l_dummy varchar2(1);
484 --
485 begin
486 if p_type = 'E' then
487 open csr_sp_placement_pds;
488 fetch csr_sp_placement_pds into l_dummy;
489 if csr_sp_placement_pds%found then
490 close csr_sp_placement_pds;
491 hr_utility.set_message(801,'HR_6837_EMP_REF_DATE_CHG');
492 hr_utility.raise_error;
493 else
494 close csr_sp_placement_pds;
495 end if;
496 elsif p_type = 'C' then
497 open csr_sp_placement_pdp;
498 fetch csr_sp_placement_pdp into l_dummy;
499 if csr_sp_placement_pdp%found then
500 close csr_sp_placement_pdp;
501 hr_utility.set_message(801,'HR_6837_EMP_REF_DATE_CHG');
502 hr_utility.raise_error;
503 else
504 close csr_sp_placement_pdp;
505 end if;
506 else
507 null;
508 end if;
509 --
510 end check_sp_placements;
511 --
512 -- ----------------------------------------------------------------------------
513 -- |-------------------------< check_asg_rates >------------------------------|
514 -- ----------------------------------------------------------------------------
515 --
516 procedure check_asg_rates(p_person_id NUMBER
517 ,p_pds_or_pdp_id NUMBER
518 ,p_new_start_date DATE
519 ,p_type VARCHAR2) is
520 cursor csr_asg_rates_pdp is
521 select 'x' from dual where exists
522 (select 1
523 from pay_grade_rules_f pgr,
524 per_periods_of_placement p,
525 per_assignments_f a
526 where a.person_id = p_person_id
527 and a.period_of_placement_date_start = p.date_start
528 and p.period_of_placement_id = p_pds_or_pdp_id
529 and a.assignment_type = p_type
530 and a.assignment_id = pgr.grade_or_spinal_point_id
531 and pgr.rate_type = 'A'
532 and pgr.effective_start_date > p.date_start
533 and pgr.effective_start_date < p_new_start_date);
534 --
535 l_dummy varchar2(1);
536 --
537 begin
538 if p_type = 'C' then
539 open csr_asg_rates_pdp;
540 fetch csr_asg_rates_pdp into l_dummy;
541 if csr_asg_rates_pdp%found then
542 close csr_asg_rates_pdp;
543 hr_utility.set_message(801,'PER_289851_CWK_ASG_RATE_EXISTS');
544 hr_utility.raise_error;
545 else
546 close csr_asg_rates_pdp;
547 end if;
548 else
549 null;
550 end if;
551 --
552 end check_asg_rates;
553 --
554 -- ----------------------------------------------------------------------------
555 -- |-------------------------< check_cost_allocation >------------------------|
556 -- ----------------------------------------------------------------------------
557 --
558 procedure check_cost_allocation(p_person_id NUMBER
559 ,p_pds_or_pdp_id NUMBER
560 ,p_new_start_date DATE
561 ,p_type VARCHAR2) is
562 cursor csr_cost_pds is
563 select 'x' from dual where exists
564 (select 1
565 from PAY_COST_ALLOCATIONS_F ca,
566 per_periods_of_service p,
567 per_assignments_f a
568 where a.person_id = p_person_id
569 and a.period_of_service_id = p.period_of_service_id
570 and p.period_of_service_id = p_pds_or_pdp_id
571 and p_type = 'E'
572 and a.assignment_id = ca.assignment_id
573 and ca.effective_start_date > p.date_start
574 and ca.effective_start_date < p_new_start_date);
575 --
576 cursor csr_cost_pdp is
577 select 'x' from dual where exists
578 (select 1
579 from PAY_COST_ALLOCATIONS_F ca,
580 per_periods_of_placement p,
581 per_assignments_f a
582 where a.person_id = p_person_id
583 and a.period_of_placement_date_start = p.date_start
584 and p.period_of_placement_id = p_pds_or_pdp_id
585 and p_type = 'C'
586 and a.assignment_id = ca.assignment_id
587 and ca.effective_start_date > p.date_start
588 and ca.effective_start_date < p_new_start_date);
589 --
590 l_dummy varchar2(1);
591 --
592 begin
593 if p_type = 'E' then
594 open csr_cost_pds;
595 fetch csr_cost_pds into l_dummy;
596 if csr_cost_pds%found then
597 close csr_cost_pds;
598 hr_utility.set_message(801,'HR_7860_EMP_REF_DATE_CHG');
599 hr_utility.raise_error;
600 else
601 close csr_cost_pds;
602 end if;
603 elsif p_type = 'C' then
604 open csr_cost_pdp;
605 fetch csr_cost_pdp into l_dummy;
606 if csr_cost_pdp%found then
607 close csr_cost_pdp;
608 hr_utility.set_message(801,'HR_7860_EMP_REF_DATE_CHG');
609 hr_utility.raise_error;
610 else
611 close csr_cost_pdp;
612 end if;
613 else
614 null;
615 end if;
616 --
617 end check_cost_allocation;
618 --
619 -- ----------------------------------------------------------------------------
620 -- |-------------------------< check_budget_values >--------------------------|
621 -- ----------------------------------------------------------------------------
622 --
623 procedure check_budget_values(p_person_id NUMBER
624 ,p_pds_or_pdp_id NUMBER
625 ,p_new_start_date DATE
626 ,p_type VARCHAR2) is
627 cursor csr_budget_pds is
628 select 'x' from dual where exists
629 (select 1
630 from per_assignment_budget_values_f bud,
631 per_periods_of_service p,
632 per_assignments_f a
633 where a.person_id = p_person_id
634 and a.period_of_service_id = p.period_of_service_id
635 and p.period_of_service_id = p_pds_or_pdp_id
636 and p_type = 'E'
637 and a.assignment_id = bud.assignment_id
638 and bud.effective_start_date > p.date_start
639 and bud.effective_start_date < p_new_start_date);
640 --
641 cursor csr_budget_pdp is
642 select 'x' from dual where exists
643 (select 1
644 from per_assignment_budget_values_f bud,
645 per_periods_of_placement p,
646 per_assignments_f a
647 where a.person_id = p_person_id
648 and a.period_of_placement_date_start = p.date_start
649 and p.period_of_placement_id = p_pds_or_pdp_id
650 and p_type = 'C'
651 and a.assignment_id = bud.assignment_id
652 and bud.effective_start_date > p.date_start
653 and bud.effective_start_date < p_new_start_date);
654 --
655 l_dummy varchar2(1);
656 --
657 begin
658 if p_type = 'E' then
659 open csr_budget_pds;
660 fetch csr_budget_pds into l_dummy;
661 if csr_budget_pds%found then
662 close csr_budget_pds;
663 hr_utility.set_message(801,'HR_7860_EMP_REF_DATE_CHG');
664 hr_utility.raise_error;
665 else
666 close csr_budget_pds;
667 end if;
668 elsif p_type = 'C' then
669 open csr_budget_pdp;
670 fetch csr_budget_pdp into l_dummy;
671 if csr_budget_pdp%found then
672 close csr_budget_pdp;
673 hr_utility.set_message(801,'HR_7860_EMP_REF_DATE_CHG');
674 hr_utility.raise_error;
675 else
676 close csr_budget_pdp;
677 end if;
678 else
679 null;
680 end if;
681 --
682 end check_budget_values;
683 --
684 -- ----------------------------------------------------------------------------
685 -- |-------------------------< check_people_changes >-------------------------|
686 -- ----------------------------------------------------------------------------
687 --
688 procedure check_people_changes(p_person_id NUMBER
689 ,p_earlier_date DATE
690 ,p_later_date DATE
691 ,p_old_start_date DATE) is
692 cursor csr_people_change is
693 select 'x' from dual where exists
694 (select 1
695 from per_people_f p
696 where p.effective_start_date between p_earlier_date and p_later_date
697 and p.effective_start_date <> p_old_start_date
698 and p.person_id = p_person_id
699 union
700 select 1
701 from per_people_f p
702 where p.effective_start_date = p_old_start_date
703 and p.current_applicant_flag = 'Y'
704 and p.person_id = p_person_id);
705 -- union added to take care of cases when an employee is made
706 -- an internal applicant on the hire date itself. (bug 4025645)
707 --
708 l_dummy varchar2(1);
709 --
710 begin
711 open csr_people_change;
712 fetch csr_people_change into l_dummy;
713 if csr_people_change%found then
714 close csr_people_change;
715 hr_utility.set_message(801,'HR_6841_EMP_REF_DATE_CHG');
716 hr_utility.raise_error;
717 else
718 close csr_people_change;
719 end if;
720 end check_people_changes;
721 --
722 --
723 -- ----------------------------------------------------------------------------
724 -- |-------------------------< check_user_person_type_changes >-------------------------|
725 -- ----------------------------------------------------------------------------
726 --
727 procedure check_user_person_type_changes(p_person_id NUMBER
728 ,p_earlier_date DATE
729 ,p_later_date DATE
730 ,p_old_start_date DATE) is
731 cursor csr_person_type_change is
732 select 'x' from dual where exists
733 (select 1
734 from per_person_type_usages_f ptu,
735 per_person_types pt
736 where ptu.person_id = p_person_id
737 and ptu.effective_start_date <> p_old_start_date
738 and ptu.effective_start_date between p_earlier_date and p_later_date
739 and ptu.person_type_id = pt.person_type_id
740 and pt.system_person_type ='EMP');
741 --
742 l_dummy varchar2(1);
743 --
744 begin
745 open csr_person_type_change;
746 fetch csr_person_type_change into l_dummy;
747 if csr_person_type_change%found then
748 close csr_person_type_change;
749 hr_utility.set_message(800,'PER_289306_PTU_CHG_EXISTS');
750 hr_utility.raise_error;
751 else
752 close csr_person_type_change;
753 end if;
754 end check_user_person_type_changes;
755 -- ----------------------------------------------------------------------------
756 -- |-------------------------< check_asg_st_change >--------------------------|
757 -- ----------------------------------------------------------------------------
758 --
759 procedure check_asg_st_change(p_person_id NUMBER
760 ,p_earlier_date DATE
761 ,p_later_date DATE
762 ,p_type VARCHAR2
763 ,p_old_start_date DATE) is
764 cursor csr_asg_status is
765 select 'x' from dual where exists
766 (select 1
767 from per_assignments a
768 ,per_assignments_f f
769 where f.effective_start_date between p_earlier_date and p_later_date
770 and f.effective_start_date <> p_old_start_date
771 and f.assignment_id = a.assignment_id
772 and a.assignment_type = p_type
773 and f.assignment_status_type_id <> a.assignment_status_type_id
774 and f.person_id = a.person_id
775 and a.person_id = p_person_id);
776 --
777 l_dummy varchar2(1);
778 --
779 begin
780 open csr_asg_status;
781 fetch csr_asg_status into l_dummy;
782 if csr_asg_status%found then
783 close csr_asg_status;
784 hr_utility.set_message(801,'HR_6838_EMP_REF_DATE_CHG');
785 hr_utility.raise_error;
786 else
787 close csr_asg_status;
788 end if;
789 --
790 end check_asg_st_change;
791 --
792 -- ----------------------------------------------------------------------------
793 -- |-------------------------< check_asg_change >-----------------------------|
794 -- ----------------------------------------------------------------------------
795 --
796 procedure check_asg_change(p_person_id NUMBER
797 ,p_earlier_date DATE
798 ,p_later_date DATE
799 ,p_old_start_date DATE) is
800 cursor csr_asg_change is
801 select 'x' from dual where exists
802 (select 1
803 from per_assignments_f f
804 where f.effective_start_date between p_earlier_date and p_later_date
805 and f.effective_start_date <> p_old_start_date
806 and f.person_id = p_person_id);
807 --
808 l_dummy varchar2(1);
809 --
810 begin
811 open csr_asg_change;
812 fetch csr_asg_change into l_dummy;
813 if csr_asg_change%found then
814 close csr_asg_change;
815 hr_utility.set_message(801,'HR_6839_EMP_REF_DATE_CHG');
816 hr_utility.raise_error;
817 else
818 close csr_asg_change;
819 end if;
820 --
821 end check_asg_change;
822 --
823 -- ----------------------------------------------------------------------------
824 -- |-------------------------< check_prev_asg >-------------------------------|
825 -- ----------------------------------------------------------------------------
826 --
827 procedure check_prev_asg(p_person_id NUMBER
828 ,p_type VARCHAR2
829 ,p_old_start_date DATE
830 ,p_new_start_date DATE) is
831 cursor csr_prev_asg is
832 select 'x' from dual where exists
833 (select 1
834 from per_assignments_f f
835 where f.effective_start_date >= p_new_start_date
836 and f.effective_start_date < p_old_start_date
837 and f.assignment_type =p_type
838 and f.person_id = p_person_id);
839 --
840 l_dummy varchar2(1);
841 --
842 begin
843 open csr_prev_asg;
844 fetch csr_prev_asg into l_dummy;
845 if csr_prev_asg%found then
846 close csr_prev_asg;
847 hr_utility.set_message(801,'HR_6840_EMP_ENTER_PERIOD');
848 hr_utility.raise_error;
849 else
850 close csr_prev_asg;
851 end if;
852 --
853 end check_prev_asg;
854 --
855 -- ----------------------------------------------------------------------------
856 -- |-------------------------< check_recur_ee >-------------------------------|
857 -- ----------------------------------------------------------------------------
858 --
859 procedure check_recur_ee(p_person_id NUMBER
860 ,p_new_start_date DATE
861 ,p_old_start_date DATE
862 ,p_warn_raise IN OUT NOCOPY VARCHAR2) is
863 --
864 l_warn VARCHAR2(1);
865 l_earlier_date DATE;
866 l_later_date DATE;
867 --
868 begin
869 l_warn := p_warn_raise;
870 if p_new_start_date > p_old_start_date then
871 l_earlier_date := p_old_start_date;
872 l_later_date := p_new_start_date;
873 else
874 l_earlier_date := p_new_start_date;
875 l_later_date := p_old_start_date;
876 end if;
877 begin
878 select 'Y'
879 into l_warn
880 from dual
881 where exists
882 (select null
883 from pay_element_entries_f ee,
884 pay_element_links_f el,
885 pay_element_types_f et
886 where ee.assignment_id in
887 (select assignment_id
888 from per_assignments_f asg
889 where asg.person_id = p_person_id
890 and asg.effective_start_date between l_earlier_date and l_later_date)
891 and ee.element_link_id = el.element_link_id
892 and el.element_type_id = et.element_type_id
893 and et.processing_type = 'R');
894 exception when NO_DATA_FOUND then null;
895 end;
896 p_warn_raise := l_warn;
897 --
898 end check_recur_ee;
899 --
900
901 -- changes start for bug 6640794
902 -- ----------------------------------------------------------------------------
903 -- |-------------------------< check_ben_enteries >---------------------------|
904 -- ----------------------------------------------------------------------------
905 --
906 procedure check_ben_enteries(p_person_id number, p_old_start_date date) is
907
908 cursor c_ben is
909 SELECT 1
910 FROM ben_ptnl_ler_for_per
911 WHERE person_id = p_person_id
912 and LF_EVT_OCRD_DT >= p_old_start_date
913 and ptnl_ler_for_per_stat_cd not in ('VOIDD','BCKDT');
914
915 dummy varchar2(2);
916
917 begin
918
919 open c_ben;
920 fetch c_ben into dummy;
921
922 if c_ben%found then
923 close c_ben;
924 hr_utility.set_message('800','PER_449825_OP_LE_EXISTS');
925 hr_utility.raise_error;
926 end if;
927
928 close c_ben;
929
930 end check_ben_enteries;
931 --changes end for bug 6640794
932
933 --
934 -- ----------------------------------------------------------------------------
935 -- |-------------------------< update_period >--------------------------------|
936 -- ----------------------------------------------------------------------------
937 --
938 procedure update_period(p_person_id number
939 ,p_old_start_date date
940 ,p_new_start_date date
941 ,p_type VARCHAR2) is
942 cursor pds is select *
943 from per_periods_of_service pds
944 where person_id = p_person_id
945 and date_start = p_old_start_date
946 for update of date_start nowait;
947 cursor pdp is select *
948 from per_periods_of_placement pdp
949 where person_id = p_person_id
950 and date_start = p_old_start_date
951 for update of date_start nowait;
952 --
953 pds_rec pds%rowtype;
954 pdp_rec pdp%rowtype;
955 l_object_version_number number;
956 --
957 begin
958 if p_type = 'E' then
959 open pds;
960 <<pds_loop>>
961 loop
962 exit pds_loop when pds%NOTFOUND;
963 fetch pds into pds_rec;
964 end loop pds_loop;
965 if pds%rowcount <>1 then
966 close pds;
967 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
968 hr_utility.set_message_token('PROCEDURE','hr_change_start_date_api.update_period');
969 hr_utility.set_message_token('STEP','1');
970 hr_utility.raise_error;
971 else
972 close pds;
973 end if;
974 l_object_version_number := pds_rec.object_version_number;
975 per_pds_upd.upd(p_period_of_service_id => pds_rec.period_of_service_id
976 ,p_date_start => p_new_start_date
977 ,p_object_version_number => l_object_version_number
978 ,p_effective_date => p_old_start_date);
979 elsif p_type = 'C' then
980 open pdp;
981 <<pdp_loop>>
982 loop
983 exit pdp_loop when pdp%NOTFOUND;
984 fetch pdp into pdp_rec;
985 end loop pdp_loop;
986 if pdp%rowcount <>1 then
987 close pdp;
988 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
989 hr_utility.set_message_token('PROCEDURE','hr_change_start_date_api.update_period');
990 hr_utility.set_message_token('STEP','2');
991 hr_utility.raise_error;
992 else
993 close pdp;
994 end if;
995 update per_periods_of_placement
996 set date_start = p_new_start_date
997 where period_of_placement_id = pdp_rec.period_of_placement_id
998 and date_start = p_old_start_date
999 and person_id = p_person_id;
1000 end if;
1001 --
1002 end update_period;
1003 --
1004 -- ----------------------------------------------------------------------------
1005 -- |-------------------------< update_spinal_placement >----------------------|
1006 -- ----------------------------------------------------------------------------
1007 --
1008 procedure update_spinal_placement(p_person_id number
1009 ,p_old_start_date date
1010 ,p_new_start_date date
1011 ,p_type VARCHAR2) is
1012 cursor csr_ssp is
1013 select placement_id
1014 from per_spinal_point_placements_f sp
1015 where assignment_id in (select a.assignment_id
1016 from per_assignments_f a
1017 where person_id = p_person_id
1018 and a.assignment_type = p_type
1019 and a.effective_start_date = p_old_start_date)
1020 and sp.effective_start_date = p_old_start_date;
1021 --
1022 l_sp_id per_spinal_point_placements_f.placement_id%TYPE;
1023 --
1024 begin
1025 open csr_ssp;
1026 loop
1027 fetch csr_ssp into l_sp_id;
1028 exit when csr_ssp%NOTFOUND;
1029 update per_spinal_point_placements_f
1030 set effective_start_date = p_new_start_date
1031 where effective_start_date = p_old_start_date
1032 and placement_id = l_sp_id;
1033 if sql%rowcount <1 then
1034 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1035 hr_utility.set_message_token('TABLE','PER_SPINAL_POINT_PLACEMENTS_F');
1036 hr_utility.raise_error;
1037 end if;
1038 end loop;
1039 close csr_ssp;
1040 end update_spinal_placement;
1041 --
1042 -- ----------------------------------------------------------------------------
1043 -- |-------------------------< update_asg_rate >------------------------------|
1044 -- ----------------------------------------------------------------------------
1045 --
1046 procedure update_asg_rate(p_person_id number
1047 ,p_old_start_date date
1048 ,p_new_start_date date
1049 ,p_type VARCHAR2) is
1050 cursor csr_rate is
1051 select grade_rule_id
1052 from pay_grade_rules_f pgr
1053 where grade_or_spinal_point_id in (select a.assignment_id
1054 from per_assignments_f a
1055 where person_id = p_person_id
1056 and a.assignment_type = p_type
1057 and a.effective_start_date = p_old_start_date)
1058 and pgr.rate_type = 'A'
1059 and pgr.effective_start_date = p_old_start_date;
1060 --
1061 l_pgr_id pay_grade_rules_f.grade_rule_id%TYPE;
1062 --
1063 begin
1064 open csr_rate;
1065 loop
1066 fetch csr_rate into l_pgr_id;
1067 exit when csr_rate%NOTFOUND;
1068 update pay_grade_rules_f
1069 set effective_start_date = p_new_start_date
1070 where effective_start_date = p_old_start_date
1071 and grade_rule_id = l_pgr_id;
1072 if sql%rowcount <1 then
1073 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1074 hr_utility.set_message_token('TABLE','PAY_GRADE_RULES_F');
1075 hr_utility.raise_error;
1076 end if;
1077 end loop;
1078 close csr_rate;
1079 end update_asg_rate;
1080 --
1081 -- ----------------------------------------------------------------------------
1082 -- |-------------------------< update_cost_allocation >-----------------------|
1083 -- ----------------------------------------------------------------------------
1084 --
1085 procedure update_cost_allocation(p_person_id number
1086 ,p_old_start_date date
1087 ,p_new_start_date date
1088 ,p_type VARCHAR2) is
1089 cursor csr_cost is
1090 select COST_ALLOCATION_ID
1091 from PAY_COST_ALLOCATIONS_F pca
1092 where assignment_id in (select a.assignment_id
1093 from per_assignments_f a
1094 where person_id = p_person_id
1095 and a.assignment_type = p_type
1096 and a.effective_start_date = p_old_start_date)
1097 and pca.effective_start_date = p_old_start_date;
1098 --
1099 l_ca_id PAY_COST_ALLOCATIONS_F.COST_ALLOCATION_ID%TYPE;
1100 --
1101 begin
1102 open csr_cost;
1103 loop
1104 fetch csr_cost into l_ca_id;
1105 exit when csr_cost%NOTFOUND;
1106 update PAY_COST_ALLOCATIONS_F
1107 set effective_start_date = p_new_start_date
1108 where effective_start_date = p_old_start_date
1109 and COST_ALLOCATION_ID = l_ca_id;
1110 if sql%rowcount <1 then
1111 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1112 hr_utility.set_message_token('TABLE','PAY_COST_ALLOCATIONS_F');
1113 hr_utility.raise_error;
1114 end if;
1115 end loop;
1116 close csr_cost;
1117 end update_cost_allocation;
1118 --
1119 -- ----------------------------------------------------------------------------
1120 -- |-------------------------< update_asg_budget >---------------------------|
1121 -- ----------------------------------------------------------------------------
1122 --
1123 procedure update_asg_budget(p_person_id number
1124 ,p_old_start_date date
1125 ,p_new_start_date date
1126 ,p_type VARCHAR2) is
1127 cursor csr_abv is
1128 select abv.assignment_budget_value_id
1129 from per_assignment_budget_values_f abv
1130 where assignment_id in (select a.assignment_id
1131 from per_assignments_f a
1132 where person_id = p_person_id
1133 and a.assignment_type = p_type
1134 and a.effective_start_date = p_old_start_date)
1135 and abv.effective_start_date = p_old_start_date;
1136 --
1137 l_abv_id per_assignment_budget_values_f.assignment_budget_value_id%TYPE;
1138 --
1139 begin
1140 open csr_abv;
1141 loop
1142 fetch csr_abv into l_abv_id;
1143 exit when csr_abv%NOTFOUND;
1144 update PER_ASSIGNMENT_BUDGET_VALUES_F
1145 set effective_start_date = p_new_start_date
1146 where effective_start_date = p_old_start_date
1147 and effective_end_date >= p_new_start_date
1148 and ASSIGNMENT_BUDGET_VALUE_ID = l_abv_id;
1149 if sql%rowcount <1 then
1150 null;
1151 end if;
1152 end loop;
1153 close csr_abv;
1154 end update_asg_budget;
1155 --
1156 -- ----------------------------------------------------------------------------
1157 -- |--------------------------< update_tax >----------------------------------|
1158 -- ----------------------------------------------------------------------------
1159 --
1160 procedure update_tax(p_person_id number
1161 ,p_new_start_date date) is
1162 cursor csr_get_bg is
1163 select business_group_id
1164 from per_people_f
1165 where person_id = p_person_id;
1166 --
1167 l_business_group_id number;
1168 l_ret_code number;
1169 l_ret_text varchar2(240);
1170 --
1171 begin
1172 open csr_get_bg;
1173 fetch csr_get_bg into l_business_group_id;
1174 if csr_get_bg%NOTFOUND then
1175 close csr_get_bg;
1176 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1177 hr_utility.set_message_token('PROCEDURE', 'hr_change_start_date_api.update_tax');
1178 hr_utility.set_message_token('STEP', '1');
1179 hr_utility.raise_error;
1180 end if;
1181 close csr_get_bg;
1182 pay_us_emp_dt_tax_rules.default_tax_with_validation
1183 (p_assignment_id => null
1184 ,p_person_id => p_person_id
1185 ,p_effective_start_date => p_new_start_date
1186 ,p_effective_end_date => null
1187 ,p_session_date => null
1188 ,p_business_group_id => l_business_group_id
1189 ,p_from_form => 'Person'
1190 ,p_mode => null
1191 ,p_location_id => null
1192 ,p_return_code => l_ret_code
1193 ,p_return_text => l_ret_text
1194 );
1195 end update_tax;
1196 --
1197 -- ----------------------------------------------------------------------------
1198 -- |-------------------------< update_apl_asg >------------------------------|
1199 -- ----------------------------------------------------------------------------
1200 --
1201 procedure update_apl_asg(p_person_id number
1202 ,p_old_start_date date
1203 ,p_new_start_date date) is
1204 cursor csr_apl_asg is
1205 select assignment_id
1206 from per_all_assignments_f a
1207 where a.effective_end_date = p_old_start_date - 1
1208 and a.assignment_type = 'A'
1209 and a.person_id = p_person_id;
1210 --
1211 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1212 --
1213 begin
1214 open csr_apl_asg;
1215 loop
1216 fetch csr_apl_asg into l_assignment_id;
1217 exit when csr_apl_asg%NOTFOUND;
1218 update per_assignments_f a
1219 set a.effective_end_date = p_new_start_date - 1
1220 where a.effective_end_date =
1221 (select max(a2.effective_end_date)
1222 from per_assignments_f a2
1223 where a2.assignment_id = a.assignment_id
1224 and a2.assignment_type = 'A')
1225 and a.assignment_id = l_assignment_id;
1226 if sql%rowcount <1 then
1227 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1228 hr_utility.set_message_token('TABLE','PER_ALL_ASSIGNMENTS_F');
1229 hr_utility.raise_error;
1230 end if;
1231 end loop;
1232 close csr_apl_asg;
1233 end update_apl_asg;
1234 --
1235 -- ----------------------------------------------------------------------------
1236 -- |------------------------------< update_apl >------------------------------|
1237 -- ----------------------------------------------------------------------------
1238 --
1239 procedure update_apl(p_person_id number
1240 ,p_old_start_date date
1241 ,p_new_start_date date) is
1242 cursor csr_apl is
1243 select application_id
1244 from per_applications a
1245 where a.person_id = p_person_id
1246 and a.date_received =
1247 (select max(a2.date_received)
1248 from per_applications a2
1249 where a2.person_id = a.person_id
1250 and a2.date_received < p_new_start_date);
1251 --
1252 l_application_id number;
1253 --
1254 begin
1255 open csr_apl;
1256 loop
1257 fetch csr_apl into l_application_id;
1258 exit when csr_apl%NOTFOUND;
1259 update per_applications a1
1260 set a1.date_end = p_new_start_date - 1
1261 where a1.application_id = l_application_id
1262 and not exists (select 1
1263 from per_people_f peo
1264 where peo.person_id = p_person_id
1265 and a1.person_id = peo.person_id
1266 and peo.effective_start_date = p_old_start_date
1267 and peo.current_applicant_flag = 'Y');
1268 if sql%rowcount <1 then
1269 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1270 hr_utility.set_message_token('TABLE','PER_APPLICATIONS');
1271 hr_utility.raise_error;
1272 end if;
1273 end loop;
1274 close csr_apl;
1275 end update_apl;
1276 --
1277 -- ----------------------------------------------------------------------------
1278 -- |-------------------------< update_pay_proposal >--------------------------|
1279 -- ----------------------------------------------------------------------------
1280 --
1281 procedure update_pay_proposal(p_person_id number
1282 ,p_old_start_date date
1283 ,p_new_start_date date
1284 ,p_type VARCHAR2) is
1285 cursor get_pay_proposal
1286 is
1287 select pay_proposal_id
1288 from per_pay_proposals
1289 where change_date = p_old_start_date
1290 and exists (select 1
1291 from per_assignments_f
1292 where person_id = p_person_id
1293 and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
1294 -- and primary_flag = 'Y'
1295 and effective_start_date = p_new_start_date
1296 and assignment_type = p_type);
1297 --
1298 cursor prv_pay_proposals
1299 is
1300 select count(*)
1301 from per_pay_proposals
1302 where change_date <= p_new_start_date
1303 and assignment_id =
1304 (select assignment_id
1305 from per_assignments_f
1306 where person_id = p_person_id
1307 and primary_flag = 'Y'
1308 and effective_start_date = p_new_start_date
1309 and assignment_type = p_type);
1310
1311 l_count NUMBER;
1312 l_dummy number;
1313 l_pay_proposal_id number;
1314 --
1315 begin
1316 --
1317 --
1318 l_count := 0;
1319
1320 hr_utility.set_location('update_pay_proposal',1);
1321
1322 open prv_pay_proposals;
1323 fetch prv_pay_proposals into l_count;
1324 hr_utility.set_location('update_pay_proposal.count = '||l_count,2);
1325
1326 if prv_pay_proposals%FOUND then
1327 close prv_pay_proposals;
1328 else
1329 close prv_pay_proposals;
1330 hr_utility.set_location('update_pay_proposal',10);
1331 end if;
1332
1333 if l_count > 1 then
1334 hr_utility.set_message('800','PER_289794_HISTORIC_SAL_PRPSL');
1335 hr_utility.raise_error;
1336 else
1337
1338 open get_pay_proposal;
1339 loop
1340 fetch get_pay_proposal into l_pay_proposal_id;
1341 exit when get_pay_proposal%NOTFOUND;
1342 -- if get_pay_proposal%FOUND then
1343 -- close get_pay_proposal;
1344 begin
1345 hr_utility.set_location('update_pay_proposal.p_new_start_date = '||to_char(p_new_start_date,'DD-MON-YYYY'),40);
1346 hr_utility.set_location('update_pay_proposal.p_old_start_date = '||to_char(p_old_start_date,'DD-MON-YYYY'),40);
1347 hr_utility.set_location('update_pay_proposal.p_person_id = '||p_person_id,40);
1348 hr_utility.set_location('update_pay_proposal.p_type = '||p_type,40);
1349 hr_utility.set_location('update_pay_proposal.pay_proposal_id = '||l_pay_proposal_id,40);
1350 --
1351 update per_pay_proposals
1352 set change_date = p_new_start_date
1353 where change_date = p_old_start_date
1354 and pay_proposal_id = l_pay_proposal_id;
1355 /* and assignment_id =
1356 (select assignment_id
1357 from per_assignments_f
1358 where person_id = p_person_id
1359 and primary_flag = 'Y'
1360 and effective_start_date = p_new_start_date
1361 and assignment_type = p_type);
1362 */
1363 hr_utility.set_location('update_pay_proposal',50);
1364 if sql%ROWCOUNT <> 1 then
1365 raise NO_DATA_FOUND;
1366 end if;
1367 exception
1368 when NO_DATA_FOUND then
1369 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
1370 hr_utility.set_message_token('PROCEDURE','Update_row');
1371 hr_utility.set_message_token('STEP','4');
1372 hr_utility.raise_error;
1373 end;
1374 -- else
1375 -- null;
1376 -- end if;
1377 end loop;
1378 close get_pay_proposal;
1379 end if;
1380 end update_pay_proposal;
1381 --
1382 -- ----------------------------------------------------------------------------
1383 -- |-----------------------------< run_alu_ee >-------------------------------|
1384 -- ----------------------------------------------------------------------------
1385 --
1386 procedure run_alu_ee(p_person_id number
1387 ,p_old_start_date date
1388 ,p_new_start_date date
1389 ,p_type VARCHAR2) is
1390 cursor csr_get_bg is
1391 select business_group_id
1392 from per_people_f
1393 where person_id = p_person_id;
1394 --
1395 cursor ass_cur is
1396 select assignment_id
1397 from per_all_assignments_f paf
1398 where paf.person_id = p_person_id
1399 and paf.assignment_type = p_type
1400 and p_new_start_date between
1401 paf.effective_start_date and paf.effective_end_date;
1402 --
1403 l_business_group_id number;
1404 l_assignment_id number; -- assignment_id of employee assignment.
1405 l_validation_start_date date; -- End date_of Assignment.
1406 l_validation_end_date date; -- End date_of Assignment.
1407 l_entries_changed VARCHAR2(1);
1408 --
1409 begin
1410 open csr_get_bg;
1411 fetch csr_get_bg into l_business_group_id;
1412 if csr_get_bg%NOTFOUND then
1413 close csr_get_bg;
1414 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1415 hr_utility.set_message_token('PROCEDURE', 'hr_change_start_date_api.update_tax');
1416 hr_utility.set_message_token('STEP', '1');
1417 hr_utility.raise_error;
1418 end if;
1419 close csr_get_bg;
1420 -- Set the correct validation start and end dates for
1421 -- the assignments. These are the same for all
1422 -- assignments of a multiple assignment person.
1423 if(p_new_start_date > p_old_start_date) then
1424 -- We have moved the hire date forwards.
1425 l_validation_start_date := p_old_start_date;
1426 l_validation_end_date := (p_new_start_date - 1);
1427 elsif(p_new_start_date < p_old_start_date) then
1428 -- We have moved the hire date backwards.
1429 l_validation_start_date := p_new_start_date;
1430 l_validation_end_date := (p_old_start_date - 1);
1431 end if;
1432 --
1433 open ass_cur;
1434 loop
1435 fetch ass_cur into l_assignment_id;
1436 exit when ass_cur%NOTFOUND;
1437 hrentmnt.maintain_entries_asg
1438 (p_assignment_id => l_assignment_id
1439 ,p_old_payroll_id => 2
1440 ,p_new_payroll_id => 1
1441 ,p_business_group_id => l_business_group_id
1442 ,p_operation => 'HIRE_APPL' -- 'ASG_CRITERIA' for bug 5547271
1443 ,p_actual_term_date => NULL
1444 ,p_last_standard_date => NULL
1445 ,p_final_process_date => NULL
1446 ,p_validation_start_date => l_validation_start_date
1447 ,p_validation_end_date => l_validation_end_date
1448 ,p_dt_mode => 'CORRECTION'
1449 ,p_old_hire_date => p_old_start_date
1450 ,p_entries_changed => l_entries_changed);
1451 --
1452 hrentmnt.maintain_entries_asg
1453 (l_assignment_id
1454 ,l_business_group_id
1455 ,'CHANGE_PQC'
1456 ,NULL
1457 ,NULL
1458 ,NULL
1459 ,NULL
1460 ,NULL
1461 ,NULL);
1462 end loop;
1463 close ass_cur;
1464 end run_alu_ee;
1465 --
1466 -- Bug2614732 starts here.
1467 --
1468 -- ----------------------------------------------------------------------------
1469 -- |-------------------------< update_probation_end >--------------------------|
1470 -- ----------------------------------------------------------------------------
1471 -- This internal procedure is used to update date_Probation_end
1472 -- of Assignment records when the Person Hire Date is changed.
1473 --
1474 -- If the Person has multiple assignments starting on the same date
1475 -- as Hire date and when the person Hire date is changed, then for
1476 -- each Assignment if the probation detais exists then the
1477 -- date_probation_end will be updated with new date_probation_end.
1478 --
1479 -- When there are datetrack updations on Assignment record then all
1480 -- the records will be updated with new date_probation_end if the
1481 -- assignment updation was not carried on Probation columns.
1482 --
1483 PROCEDURE UPDATE_PROBATION_END(p_person_id number,
1484 p_new_effective_date date) IS
1485
1486 --
1487 -- select all the assignments for the person_id starting on the Hire date.
1488 --
1489 Cursor csr_assignments(p_person_id number, p_new_effective_date date) IS
1490 select distinct paf.assignment_id
1491 from per_assignments_f paf
1492 where paf.person_id = p_person_id
1493 and paf.date_probation_end is not null
1494 and paf.effective_start_date = p_new_effective_date;
1495 --
1496 -- select any datetrack updations on given Assignment_id.
1497 --
1498 Cursor csr_asg_updates(p_assignment_id number, p_new_effective_date date) IS
1499 select paf.effective_start_date
1500 from per_assignments_f paf
1501 where paf.assignment_id = p_assignment_id
1502 and paf.effective_start_date >= p_new_effective_date
1503 order by paf.effective_start_date;
1504 --
1505 -- select the probation details of the Assigment on the given effective date.
1506 --
1507 Cursor csr_probation_details(p_assignment_id number, p_effective_start_date date) IS
1508 select paf.date_probation_end
1509 ,paf.probation_period
1510 ,paf.probation_unit
1511 from per_assignments_f paf
1512 where paf.assignment_id = p_assignment_id
1513 and paf.effective_start_date = p_effective_start_date;
1514 --
1515 -- local variables.
1516 --
1517 l_proc varchar2(30):='update_probation_end';
1518 l_assignment_id per_all_assignments_f.assignment_id%type;
1519 l_date_probation_end1 per_all_assignments_f.date_probation_end%type;
1520 l_probation_period1 per_all_assignments_f.probation_period%type;
1521 l_probation_period per_all_assignments_f.probation_period%type;
1522 l_probation_unit1 per_all_assignments_f.probation_unit%type;
1523 l_probation_unit per_all_assignments_f.probation_unit%type;
1524 l_new_date_probation_end per_all_assignments_f.date_probation_end%type;
1525 l_date_probation_end per_all_assignments_f.date_probation_end%type;
1526 l_new_effective_date date;
1527 l_new_start_date date;
1528 l_effective_start_date date;
1529 --
1530 --
1531 BEGIN
1532 --
1533 hr_utility.set_location('Entering:'|| l_proc, 10);
1534 l_new_effective_date := p_new_effective_date;
1535 l_new_start_date := p_new_effective_date;
1536
1537 FOR assignment_rec in csr_assignments(p_person_id, p_new_effective_date) LOOP
1538 --
1539 -- for each Assignment of Person loop through.
1540 --
1541 l_assignment_id := assignment_rec.assignment_id;
1542 hr_utility.set_location('Assignment ID: '||l_assignment_id, 20);
1543 --
1544 -- Get old probation details.
1545 --
1546 open csr_probation_details(l_assignment_id, p_new_effective_date);
1547 fetch csr_probation_details into l_date_probation_end1, l_probation_period1, l_probation_unit1;
1548 close csr_probation_details;
1549 hr_utility.set_location('Old probation details: ', 30);
1550 hr_utility.set_location('date_probation_end:'||l_date_probation_end1, 30);
1551 hr_utility.set_location('probation_period: '||l_probation_period1, 30);
1552 hr_utility.set_location('probation_unit : '||l_probation_unit1, 30);
1553 --
1554 l_new_date_probation_end := null;
1555 --
1556 -- Get new probation end date.
1557 --
1558 hr_assignment.gen_probation_end(
1559 l_assignment_id,
1560 l_probation_period1,
1561 l_probation_unit1,
1562 l_new_effective_date,
1563 l_new_date_probation_end);
1564 --
1565 -- Update the Assignment updations.
1566 --
1567 For asg_update_rec in csr_asg_updates(l_assignment_id, p_new_effective_date) LOOP
1568 --
1569 -- for each updation on Assignment record loop through.
1570 -- check the probation period of updated asg. and If it is update on some other field
1571 -- then update the probation end date of this asg update.
1572 --
1573 l_effective_start_date := asg_update_rec.effective_start_date;
1574 --
1575 hr_utility.set_location('Assignment ID: '||l_assignment_id, 40);
1576 hr_utility.set_location('Effective start date.: '||l_effective_start_date, 40);
1577 --
1578 open csr_probation_details(l_assignment_id, l_effective_start_date);
1579 fetch csr_probation_details into l_date_probation_end, l_probation_period, l_probation_unit;
1580 if csr_probation_details%found then
1581 --
1582 if (l_date_probation_end <> l_date_probation_end1)
1583 or (l_probation_period <> l_probation_period1)
1584 or (l_probation_unit <> l_probation_unit1) then
1585 --
1586 null;
1587 hr_utility.set_location('date probation end is not updated', 50);
1588 --
1589 else
1590 --
1591 hr_utility.set_location('date probation end is updated', 60);
1592 update per_assignments_f paf
1593 set paf.date_probation_end = l_new_date_probation_end
1594 where paf.assignment_id = l_assignment_id
1595 and paf.effective_start_date = l_effective_start_date;
1596 --
1597 end if;
1598 close csr_probation_details;
1599 l_date_probation_end := null;
1600 --
1601 end if;
1602 --
1603 END LOOP;
1604
1605 --
1606 END LOOP;
1607 --
1608 hr_utility.set_location('Leaving:'|| l_proc, 90);
1609 --
1610 END UPDATE_PROBATION_END;
1611 --
1612 -- Bug 2614732 ends here.
1613 --
1614 -- Fix for bug 3738058 starts here.
1615 --
1616 procedure check_extra_details_of_service(p_person_id number
1617 ,p_old_start_date date
1618 ,p_new_start_date date) is
1619 --
1620 -- Cursor to check for any updates in between old and new start dates.
1621 --
1622 cursor csr_extra_details IS
1623 select 'Y'
1624 from pqp_assignment_attributes_f paa
1625 ,per_assignments_f paf
1626 where paf.person_id = p_person_id
1627 and paa.assignment_id = paf.assignment_id
1628 and paf.effective_start_date = trunc(p_old_start_date)
1629 and ( paa.effective_start_date between p_old_start_date+1 and p_new_start_date
1630 OR
1631 paa.effective_end_date between p_old_start_date and p_new_start_date);
1632 --
1633 l_dummy varchar2(1);
1634 --
1635 begin
1636 --
1637 open csr_extra_details;
1638 fetch csr_extra_details into l_dummy;
1639 if csr_extra_details%found then
1640 close csr_extra_details;
1641 hr_utility.set_message(800,'PER_449500_EXTRA_SER_DET_EXIST');
1642 hr_utility.raise_error;
1643 end if;
1644 close csr_extra_details;
1645 --
1646 end check_extra_details_of_service;
1647 --
1648 -- Fix for bug 3738058 ends here.
1649 --
1650 -- ----------------------------------------------------------------------------
1651 -- |--------------------------< check_grade_ladder >--------------------------|
1652 -- ----------------------------------------------------------------------------
1653 --
1654 -- Fix for bug 3972548 starts here.
1655 --
1656 --
1657 -- Procedure to check the existance of the grade ladders on new start date.
1658 --
1659 PROCEDURE check_grade_ladder(p_person_id in number
1660 ,p_old_start_date in date
1661 ,p_new_start_date in date) IS
1662 --
1663 l_dummy varchar2(1);
1664 l_proc varchar2(72):='hr_change_start_date_api.check_grade_ladder';
1665 --
1666 CURSOR csr_asg_records IS
1667 SELECT GRADE_LADDER_PGM_ID
1668 FROM per_all_assignments_f
1669 WHERE person_id = p_person_id
1670 AND GRADE_LADDER_PGM_ID is not null
1671 AND effective_start_date = p_old_start_date;
1672 --
1673 CURSOR csr_is_pgm_valid(p_pgm_id number) IS
1674 SELECT NULL
1675 FROM ben_pgm_f pgm
1676 WHERE pgm.pgm_id = p_pgm_id
1677 AND p_new_start_date between pgm.effective_start_date
1678 and pgm.effective_end_date;
1679 --
1680 BEGIN
1681 --
1682 -- Check if the affected assignments have any Grade ladder.
1683 --
1684 hr_utility.set_location('Entering :'||l_proc, 10);
1685 --
1686 FOR asg_rec in csr_asg_records LOOP
1687 --
1688 -- Check the grade ladder is valid after the start date is moved.
1689 --
1690 hr_utility.set_location('Entering :'||l_proc, 20);
1691 --
1692 open csr_is_pgm_valid(asg_rec.GRADE_LADDER_PGM_ID);
1693 fetch csr_is_pgm_valid into l_dummy;
1694 IF csr_is_pgm_valid%NOTFOUND THEN
1695 --
1696 -- The grade ladder is invalid on the new start date.
1697 --
1698 close csr_is_pgm_valid;
1699 --
1700 hr_utility.set_message (800,'HR_449567_USD_INVALID_PGM');
1701 hr_utility.raise_error;
1702 --
1703 END IF;
1704 --
1705 hr_utility.set_location('Entering :'||l_proc, 30);
1706 --
1707 close csr_is_pgm_valid;
1708 --
1709 END LOOP;
1710 --
1711 hr_utility.set_location('Leaving :'||l_proc, 100);
1712 --
1713 END check_grade_ladder;
1714 --
1715 -- ----------------------------------------------------------------------------
1716 -- |--------------------------< call_trigger_hook >----------------------------|
1717 -- ----------------------------------------------------------------------------
1718 --
1719 procedure call_trigger_hook( p_person_id in number,
1720 p_old_start_date in date,
1721 p_new_start_date in date
1722 )
1723 is
1724 --
1725 cursor get_asg(p_per in number,
1726 p_new_st_date in date)
1727 is
1728 select assignment_id, business_group_id
1729 from per_all_assignments_f
1730 where person_id = p_per
1731 and effective_start_date = p_new_st_date;
1732 --
1733 dt_mode varchar2(30);
1734 --
1735 begin
1736 --
1737 /* only call this procedure if the start date is earlier */
1738 if (p_old_start_date > p_new_start_date) then
1739 dt_mode := 'START_EARLIER';
1740 else
1741 dt_mode := 'START_LATER';
1742 end if;
1743 --
1744 for asgrec in get_asg(p_person_id, p_new_start_date) loop
1745 --
1746 PAY_POG_ALL_ASSIGNMENTS_PKG.AFTER_UPDATE
1747 (
1748 p_effective_date => p_new_start_date
1749 ,p_datetrack_mode => dt_mode
1750 ,p_validation_start_date => null
1751 ,p_validation_end_date => null
1752 ,P_APPLICANT_RANK => null
1753 ,P_APPLICATION_ID => null
1754 ,P_ASSIGNMENT_CATEGORY => null
1755 ,P_ASSIGNMENT_ID => asgrec.assignment_id
1756 ,P_ASSIGNMENT_NUMBER => null
1757 ,P_ASSIGNMENT_STATUS_TYPE_ID => null
1758 ,P_ASSIGNMENT_TYPE => null
1759 ,P_ASS_ATTRIBUTE1 => null
1760 ,P_ASS_ATTRIBUTE10 => null
1761 ,P_ASS_ATTRIBUTE11 => null
1762 ,P_ASS_ATTRIBUTE12 => null
1763 ,P_ASS_ATTRIBUTE13 => null
1764 ,P_ASS_ATTRIBUTE14 => null
1765 ,P_ASS_ATTRIBUTE15 => null
1766 ,P_ASS_ATTRIBUTE16 => null
1767 ,P_ASS_ATTRIBUTE17 => null
1768 ,P_ASS_ATTRIBUTE18 => null
1769 ,P_ASS_ATTRIBUTE19 => null
1770 ,P_ASS_ATTRIBUTE2 => null
1771 ,P_ASS_ATTRIBUTE20 => null
1772 ,P_ASS_ATTRIBUTE21 => null
1773 ,P_ASS_ATTRIBUTE22 => null
1774 ,P_ASS_ATTRIBUTE23 => null
1775 ,P_ASS_ATTRIBUTE24 => null
1776 ,P_ASS_ATTRIBUTE25 => null
1777 ,P_ASS_ATTRIBUTE26 => null
1778 ,P_ASS_ATTRIBUTE27 => null
1779 ,P_ASS_ATTRIBUTE28 => null
1780 ,P_ASS_ATTRIBUTE29 => null
1781 ,P_ASS_ATTRIBUTE3 => null
1782 ,P_ASS_ATTRIBUTE30 => null
1783 ,P_ASS_ATTRIBUTE4 => null
1784 ,P_ASS_ATTRIBUTE5 => null
1785 ,P_ASS_ATTRIBUTE6 => null
1786 ,P_ASS_ATTRIBUTE7 => null
1787 ,P_ASS_ATTRIBUTE8 => null
1788 ,P_ASS_ATTRIBUTE9 => null
1789 ,P_ASS_ATTRIBUTE_CATEGORY => null
1790 ,P_BARGAINING_UNIT_CODE => null
1791 ,P_CAGR_GRADE_DEF_ID => null
1792 ,P_CAGR_ID_FLEX_NUM => null
1793 ,P_CHANGE_REASON => null
1794 ,P_COLLECTIVE_AGREEMENT_ID => null
1795 ,P_COMMENTS => null
1796 ,P_COMMENT_ID => null
1797 ,P_CONTRACT_ID => null
1798 ,P_DATE_PROBATION_END => null
1799 ,P_DEFAULT_CODE_COMB_ID => null
1800 ,P_EFFECTIVE_END_DATE => null
1801 ,P_EFFECTIVE_START_DATE => p_new_start_date
1802 ,P_EMPLOYEE_CATEGORY => null
1803 ,P_EMPLOYMENT_CATEGORY => null
1804 ,P_ESTABLISHMENT_ID => null
1805 ,P_FREQUENCY => null
1806 ,P_GRADE_ID => null
1807 ,P_HOURLY_SALARIED_CODE => null
1808 ,P_HOURLY_SALARIED_WARNING => null
1809 ,P_INTERNAL_ADDRESS_LINE => null
1810 ,P_JOB_ID => null
1811 ,P_JOB_POST_SOURCE_NAME => null
1812 ,P_LABOUR_UNION_MEMBER_FLAG => null
1813 ,P_LOCATION_ID => null
1814 ,P_MANAGER_FLAG => null
1815 ,P_NORMAL_HOURS => null
1816 ,P_NOTICE_PERIOD => null
1817 ,P_NOTICE_PERIOD_UOM => null
1818 ,P_NO_MANAGERS_WARNING => null
1819 ,P_OBJECT_VERSION_NUMBER => null
1820 ,P_ORGANIZATION_ID => null
1821 ,P_ORG_NOW_NO_MANAGER_WARNING => null
1822 ,P_OTHER_MANAGER_WARNING => null
1823 ,P_PAYROLL_ID => null
1824 ,P_PAYROLL_ID_UPDATED => null
1825 ,P_PAY_BASIS_ID => null
1826 ,P_PEOPLE_GROUP_ID => null
1827 ,P_PERF_REVIEW_PERIOD => null
1828 ,P_PERF_REVIEW_PERIOD_FREQUEN => null
1829 ,P_PERIOD_OF_SERVICE_ID => null
1830 ,P_PERSON_REFERRED_BY_ID => null
1831 ,P_PLACEMENT_DATE_START => null
1832 ,P_POSITION_ID => null
1833 ,P_POSTING_CONTENT_ID => null
1834 ,P_PRIMARY_FLAG => null
1835 ,P_PROBATION_PERIOD => null
1836 ,P_PROBATION_UNIT => null
1837 ,P_PROGRAM_APPLICATION_ID => null
1838 ,P_PROGRAM_ID => null
1839 ,P_PROGRAM_UPDATE_DATE => null
1840 ,P_PROJECT_TITLE => null
1841 ,P_RECRUITER_ID => null
1842 ,P_RECRUITMENT_ACTIVITY_ID => null
1843 ,P_REQUEST_ID => null
1844 ,P_SAL_REVIEW_PERIOD => null
1845 ,P_SAL_REVIEW_PERIOD_FREQUEN => null
1846 ,P_SET_OF_BOOKS_ID => null
1847 ,P_SOFT_CODING_KEYFLEX_ID => null
1848 ,P_SOURCE_ORGANIZATION_ID => null
1849 ,P_SOURCE_TYPE => null
1850 ,P_SPECIAL_CEILING_STEP_ID => null
1851 ,P_SUPERVISOR_ID => null
1852 ,P_TIME_NORMAL_FINISH => null
1853 ,P_TIME_NORMAL_START => null
1854 ,P_TITLE => null
1855 ,P_VACANCY_ID => null
1856 ,P_VENDOR_ASSIGNMENT_NUMBER => null
1857 ,P_VENDOR_EMPLOYEE_NUMBER => null
1858 ,P_VENDOR_ID => null
1859 ,P_WORK_AT_HOME => null
1860 ,P_GRADE_LADDER_PGM_ID => null
1861 ,P_SUPERVISOR_ASSIGNMENT_ID => null
1862 ,P_VENDOR_SITE_ID => null
1863 ,P_PO_HEADER_ID => null
1864 ,P_PO_LINE_ID => null
1865 ,P_PROJECTED_ASSIGNMENT_END => null
1866 ,P_APPLICANT_RANK_O => null
1867 ,P_APPLICATION_ID_O => null
1868 ,P_ASSIGNMENT_CATEGORY_O => null
1869 ,P_ASSIGNMENT_NUMBER_O => null
1870 ,P_ASSIGNMENT_SEQUENCE_O => null
1871 ,P_ASSIGNMENT_STATUS_TYPE_ID_O => null
1872 ,P_ASSIGNMENT_TYPE_O => null
1873 ,P_ASS_ATTRIBUTE1_O => null
1874 ,P_ASS_ATTRIBUTE10_O => null
1875 ,P_ASS_ATTRIBUTE11_O => null
1876 ,P_ASS_ATTRIBUTE12_O => null
1877 ,P_ASS_ATTRIBUTE13_O => null
1878 ,P_ASS_ATTRIBUTE14_O => null
1879 ,P_ASS_ATTRIBUTE15_O => null
1880 ,P_ASS_ATTRIBUTE16_O => null
1881 ,P_ASS_ATTRIBUTE17_O => null
1882 ,P_ASS_ATTRIBUTE18_O => null
1883 ,P_ASS_ATTRIBUTE19_O => null
1884 ,P_ASS_ATTRIBUTE2_O => null
1885 ,P_ASS_ATTRIBUTE20_O => null
1886 ,P_ASS_ATTRIBUTE21_O => null
1887 ,P_ASS_ATTRIBUTE22_O => null
1888 ,P_ASS_ATTRIBUTE23_O => null
1889 ,P_ASS_ATTRIBUTE24_O => null
1890 ,P_ASS_ATTRIBUTE25_O => null
1891 ,P_ASS_ATTRIBUTE26_O => null
1892 ,P_ASS_ATTRIBUTE27_O => null
1893 ,P_ASS_ATTRIBUTE28_O => null
1894 ,P_ASS_ATTRIBUTE29_O => null
1895 ,P_ASS_ATTRIBUTE3_O => null
1896 ,P_ASS_ATTRIBUTE30_O => null
1897 ,P_ASS_ATTRIBUTE4_O => null
1898 ,P_ASS_ATTRIBUTE5_O => null
1899 ,P_ASS_ATTRIBUTE6_O => null
1900 ,P_ASS_ATTRIBUTE7_O => null
1901 ,P_ASS_ATTRIBUTE8_O => null
1902 ,P_ASS_ATTRIBUTE9_O => null
1903 ,P_ASS_ATTRIBUTE_CATEGORY_O => null
1904 ,P_BARGAINING_UNIT_CODE_O => null
1905 ,P_BUSINESS_GROUP_ID_O => asgrec.business_group_id
1906 ,P_CAGR_GRADE_DEF_ID_O => null
1907 ,P_CAGR_ID_FLEX_NUM_O => null
1908 ,P_CHANGE_REASON_O => null
1909 ,P_COLLECTIVE_AGREEMENT_ID_O => null
1910 ,P_COMMENT_ID_O => null
1911 ,P_CONTRACT_ID_O => null
1912 ,P_DATE_PROBATION_END_O => null
1913 ,P_DEFAULT_CODE_COMB_ID_O => null
1914 ,P_EFFECTIVE_END_DATE_O => null
1915 ,P_EFFECTIVE_START_DATE_O => p_old_start_date
1916 ,P_EMPLOYEE_CATEGORY_O => null
1917 ,P_EMPLOYMENT_CATEGORY_O => null
1918 ,P_ESTABLISHMENT_ID_O => null
1919 ,P_FREQUENCY_O => null
1920 ,P_GRADE_ID_O => null
1921 ,P_HOURLY_SALARIED_CODE_O => null
1922 ,P_INTERNAL_ADDRESS_LINE_O => null
1923 ,P_JOB_ID_O => null
1924 ,P_JOB_POST_SOURCE_NAME_O => null
1925 ,P_LABOUR_UNION_MEMBER_FLAG_O => null
1926 ,P_LOCATION_ID_O => null
1927 ,P_MANAGER_FLAG_O => null
1928 ,P_NORMAL_HOURS_O => null
1929 ,P_NOTICE_PERIOD_O => null
1930 ,P_NOTICE_PERIOD_UOM_O => null
1931 ,P_OBJECT_VERSION_NUMBER_O => null
1932 ,P_ORGANIZATION_ID_O => null
1933 ,P_PAYROLL_ID_O => null
1934 ,P_PAY_BASIS_ID_O => null
1935 ,P_PEOPLE_GROUP_ID_O => null
1936 ,P_PERF_REVIEW_PERIOD_O => null
1937 ,P_PERF_REVIEW_PERIOD_FREQUEN_O => null
1938 ,P_PERIOD_OF_SERVICE_ID_O => null
1939 ,P_PERSON_ID_O => p_person_id
1940 ,P_PERSON_REFERRED_BY_ID_O => null
1941 ,P_PLACEMENT_DATE_START_O => null
1942 ,P_POSITION_ID_O => null
1943 ,P_POSTING_CONTENT_ID_O => null
1944 ,P_PRIMARY_FLAG_O => null
1945 ,P_PROBATION_PERIOD_O => null
1946 ,P_PROBATION_UNIT_O => null
1947 ,P_PROGRAM_APPLICATION_ID_O => null
1948 ,P_PROGRAM_ID_O => null
1949 ,P_PROGRAM_UPDATE_DATE_O => null
1950 ,P_PROJECT_TITLE_O => null
1951 ,P_RECRUITER_ID_O => null
1952 ,P_RECRUITMENT_ACTIVITY_ID_O => null
1953 ,P_REQUEST_ID_O => null
1954 ,P_SAL_REVIEW_PERIOD_O => null
1955 ,P_SAL_REVIEW_PERIOD_FREQUEN_O => null
1956 ,P_SET_OF_BOOKS_ID_O => null
1957 ,P_SOFT_CODING_KEYFLEX_ID_O => null
1958 ,P_SOURCE_ORGANIZATION_ID_O => null
1959 ,P_SOURCE_TYPE_O => null
1960 ,P_SPECIAL_CEILING_STEP_ID_O => null
1961 ,P_SUPERVISOR_ID_O => null
1962 ,P_TIME_NORMAL_FINISH_O => null
1963 ,P_TIME_NORMAL_START_O => null
1964 ,P_TITLE_O => null
1965 ,P_VACANCY_ID_O => null
1966 ,P_VENDOR_ASSIGNMENT_NUMBER_O => null
1967 ,P_VENDOR_EMPLOYEE_NUMBER_O => null
1968 ,P_VENDOR_ID_O => null
1969 ,P_WORK_AT_HOME_O => null
1970 ,P_GRADE_LADDER_PGM_ID_O => null
1971 ,P_SUPERVISOR_ASSIGNMENT_ID_O => null
1972 ,P_VENDOR_SITE_ID_O => null
1973 ,P_PO_HEADER_ID_O => null
1974 ,P_PO_LINE_ID_O => null
1975 ,P_PROJECTED_ASSIGNMENT_END_O => null
1976 );
1977 end loop;
1978 --
1979 end call_trigger_hook;
1980 --
1981 -- Fix for bug 3972548 ends here.
1982 --
1983 -- ----------------------------------------------------------------------------
1984 -- |--------------------------< update_start_date >----------------------------|
1985 -- ----------------------------------------------------------------------------
1986 --
1987 procedure update_start_date
1988 (p_validate in boolean
1989 ,p_person_id in number
1990 ,p_old_start_date in date
1991 ,p_new_start_date in date
1992 ,p_update_type in varchar2
1993 ,p_applicant_number in varchar2
1994 ,p_warn_ee out nocopy varchar2
1995 ) is
1996 --
1997 -- Declare cursors and local variables
1998 --
1999 cursor csr_pds
2000 is
2001 select period_of_service_id
2002 from per_periods_of_service
2003 where person_id = p_person_id
2004 and date_start = p_old_start_date;
2005 --
2006 cursor csr_pdp
2007 is
2008 select period_of_placement_id
2009 from per_periods_of_placement
2010 where person_id = p_person_id
2011 and date_start = p_old_start_date;
2012 --
2013 -- 115.30,115.33 (START)
2014 --
2015 CURSOR csr_legislation
2016 (p_person_id IN per_all_people_f.person_id%TYPE
2017 ,p_effective_date IN DATE
2018 ) IS
2019 SELECT bus.legislation_code
2020 FROM per_people_f per
2021 ,per_business_groups bus
2022 WHERE per.person_id = csr_legislation.p_person_id
2023 AND per.business_group_id+0 = bus.business_group_id
2024 AND csr_legislation.p_effective_date BETWEEN per.effective_start_date
2025 AND per.effective_end_date;
2026 --
2027 l_legislation_code per_business_groups.legislation_code%TYPE;
2028 l_hd_rule_value pay_legislation_rules.rule_mode%TYPE;
2029 l_hd_rule_found BOOLEAN;
2030 l_fpd_rule_value pay_legislation_rules.rule_mode%TYPE;
2031 l_fpd_rule_found BOOLEAN;
2032 --
2033 -- 115.30,115.33 (END)
2034 --
2035 l_proc varchar2(30):='update_start_date';
2036 l_pds_or_pdp_id per_periods_of_placement.period_of_placement_id%TYPE;
2037 l_old_start_date DATE;
2038 l_new_start_date DATE;
2039 l_earlier_date DATE;
2040 l_later_date DATE;
2041 l_system_person_type per_person_types.system_person_type%TYPE;
2042 l_warn_ee varchar2(1) := 'N';
2043 --
2044 begin
2045 hr_utility.set_location('Entering:'|| l_proc, 10);
2046 --
2047 -- Check mandatory parameters have been set
2048 --
2049 hr_api.mandatory_arg_error
2050 (p_api_name => l_proc
2051 ,p_argument => 'person_id'
2052 ,p_argument_value => p_person_id);
2053 --
2054 hr_api.mandatory_arg_error
2055 (p_api_name => l_proc
2056 ,p_argument => 'old_start_date'
2057 ,p_argument_value => p_old_start_date );
2058 --
2059 hr_api.mandatory_arg_error
2060 (p_api_name => l_proc
2061 ,p_argument => 'new_start_date'
2062 ,p_argument_value => p_new_start_date);
2063 --
2064 hr_api.mandatory_arg_error
2065 (p_api_name => l_proc
2066 ,p_argument => 'update_type'
2067 ,p_argument_value => p_update_type);
2068 --
2069 -- Issue a savepoint
2070 --
2071 savepoint update_start_date;
2072 --
2073 -- Truncate the time portion from all IN date parameters
2074 --
2075 l_old_start_date := trunc(p_old_start_date);
2076 l_new_start_date := trunc(p_new_start_date);
2077 --
2078 -- Initialise local variables
2079 --
2080 if p_update_type = 'E' then
2081 open csr_pds;
2082 fetch csr_pds into l_pds_or_pdp_id;
2083 close csr_pds;
2084 l_system_person_type := 'EMP';
2085 elsif p_update_type = 'C' then
2086 open csr_pdp;
2087 fetch csr_pdp into l_pds_or_pdp_id;
2088 close csr_pdp;
2089 l_system_person_type := 'CWK';
2090 else
2091 hr_utility.set_message('801','HR_6153_ALL_PROCEDURE_FAIL');
2092 hr_utility.set_message_token('PROCEDURE','update_start_date');
2093 hr_utility.set_message_token('STEP','1');
2094 hr_utility.raise_error;
2095 end if;
2096 hr_utility.set_location(l_proc, 20);
2097 --
2098 -- Call Before Process User Hook
2099 --
2100 begin
2101 hr_change_start_date_bk1.update_start_date_b
2102 (p_person_id => p_person_id
2103 ,p_old_start_date => l_old_start_date
2104 ,p_new_start_date => l_new_start_date
2105 ,p_update_type => p_update_type
2106 ,p_applicant_number => p_applicant_number
2107 );
2108 exception
2109 when hr_api.cannot_find_prog_unit then
2110 hr_api.cannot_find_prog_unit_error
2111 (p_module_name => 'update_start_date'
2112 ,p_hook_type => 'BP'
2113 );
2114 end;
2115 --
2116 -- 115.30,115.33 (START)
2117 --
2118 --
2119 -- Get Legislation
2120 --
2121 OPEN csr_legislation(p_person_id
2122 ,p_old_start_date
2123 );
2124 FETCH csr_legislation INTO l_legislation_code;
2125 CLOSE csr_legislation;
2126 --
2127 -- Check if amend hire date beyond PAY actions is allowed
2128 --
2129 pay_core_utils.get_legislation_rule('AMEND_HIRE_WITH_PAYACT'
2130 ,l_legislation_code
2131 ,l_hd_rule_value
2132 ,l_hd_rule_found
2133 );
2134 --
2135 -- Check if rehire before FPD is allowed
2136 --
2137 pay_core_utils.get_legislation_rule('REHIRE_BEFORE_FPD'
2138 ,l_legislation_code
2139 ,l_fpd_rule_value
2140 ,l_fpd_rule_found
2141 );
2142 --
2143 -- 115.30,115.33 (END)
2144 --
2145 --
2146 -- Validation in addition to Row Handlers
2147 --
2148 IF l_old_start_date = l_new_start_date then
2149 --
2150 NULL; -- do nothing as hire dates have not actually changed.
2151 hr_utility.set_location(l_proc, 25);
2152 --
2153 ELSE
2154 --
2155 check_not_supervisor(p_person_id => p_person_id
2156 ,p_new_start_date => l_new_start_date
2157 ,p_old_start_date => l_old_start_date
2158 );
2159 hr_utility.set_location(l_proc, 30);
2160 --
2161 check_pds_pdp(p_person_id => p_person_id
2162 ,p_new_start_date => l_new_start_date
2163 ,p_old_start_date => l_old_start_date
2164 ,p_type => p_update_type
2165 );
2166 hr_utility.set_location(l_proc, 40);
2167 --
2168 check_un_ended_pds_pdp(p_person_id => p_person_id
2169 ,p_new_start_date => l_new_start_date
2170 --
2171 -- 115.30 (START)
2172 --
2173 ,p_old_start_date => l_old_start_date
2174 ,p_hd_rule_found => l_hd_rule_found
2175 ,p_hd_rule_value => l_hd_rule_value
2176 ,p_fpd_rule_found => l_fpd_rule_found
2177 ,p_fpd_rule_value => l_fpd_rule_value
2178 --
2179 -- 115.30 (END)
2180 --
2181 ,p_type => p_update_type
2182 );
2183 hr_utility.set_location(l_proc, 45);
2184 check_contig_pds_pdp(p_person_id => p_person_id
2185 ,p_old_start_date => l_old_start_date
2186 ,p_type => p_update_type);
2187 hr_utility.set_location(l_proc, 50);
2188 --
2189 check_supe_pay(p_pds_or_pdp_id => l_pds_or_pdp_id
2190 ,p_new_start_date => l_new_start_date
2191 ,p_type => p_update_type);
2192 hr_utility.set_location(l_proc, 60);
2193 --
2194 -- Fix for bug 3972548 starts here.
2195 --
2196 check_grade_ladder(p_person_id
2197 ,l_old_start_date
2198 ,l_new_start_date);
2199 --
2200 hr_utility.set_location(l_proc, 65);
2201 --
2202 -- Fix for bug 3972548 ends here.
2203 --
2204 if l_new_start_date > l_old_start_date then
2205 l_earlier_date := l_old_start_date;
2206 l_later_date := l_new_start_date;
2207 --
2208 --
2209 -- 115.30 (START)
2210 --
2211 --
2212 -- Check if amend hire date with PAY actions is enabled
2213 --
2214 if ( p_update_type = 'C'
2215 OR
2216 ( p_update_type = 'E'
2217 AND
2218 ( nvl(fnd_profile.value('HR_MV_HIRE_SKIP_ACT_VALIDATION'),'N') = 'N'
2219 OR
2220 NOT l_hd_rule_found
2221 OR
2222 (l_hd_rule_found AND nvl(l_hd_rule_value,'N') = 'N')
2223 )
2224 )
2225 ) THEN
2226 --
2227 -- Disallow change hire date beyond PAY actions.
2228 -- Retaining validation
2229 --
2230 --
2231 -- 115.30 (END)
2232 --
2233 check_for_compl_actions(p_person_id => p_person_id
2234 ,p_old_start_date => l_old_start_date
2235 ,p_new_start_date => l_new_start_date
2236 ,p_type => p_update_type);
2237 hr_utility.set_location(l_proc, 70);
2238 --
2239 -- 115.30 (START)
2240 --
2241 else
2242 --
2243 -- The hire date is allowed to change beyond PAY actions.
2244 -- Invoke other team routines to handle this via new hook.
2245 --
2246 per_pds_utils.check_move_hire_date(p_person_id => p_person_id
2247 ,p_old_start_date => l_old_start_date
2248 ,p_new_start_date => l_new_start_date
2249 ,p_type => p_update_type);
2250 --
2251 hr_utility.set_location(l_proc, 75);
2252 end if;
2253 --
2254 -- 115.30 (END)
2255 --
2256 --
2257 check_sp_placements(p_person_id => p_person_id
2258 ,p_pds_or_pdp_id => l_pds_or_pdp_id
2259 ,p_new_start_date => l_new_start_date
2260 ,p_type => p_update_type);
2261 hr_utility.set_location(l_proc, 80);
2262 --
2263 check_asg_rates(p_person_id => p_person_id
2264 ,p_pds_or_pdp_id => l_pds_or_pdp_id
2265 ,p_new_start_date => l_new_start_date
2266 ,p_type => p_update_type);
2267 hr_utility.set_location(l_proc, 85);
2268 --
2269 check_cost_allocation(p_person_id => p_person_id
2270 ,p_pds_or_pdp_id => l_pds_or_pdp_id
2271 ,p_new_start_date => l_new_start_date
2272 ,p_type => p_update_type);
2273 hr_utility.set_location(l_proc, 90);
2274 --
2275 check_budget_values(p_person_id => p_person_id
2276 ,p_pds_or_pdp_id => l_pds_or_pdp_id
2277 ,p_new_start_date => l_new_start_date
2278 ,p_type => p_update_type);
2279 hr_utility.set_location(l_proc, 95);
2280 --
2281 check_people_changes(p_person_id => p_person_id
2282 ,p_earlier_date => l_earlier_date
2283 ,p_later_date => l_later_date
2284 ,p_old_start_date => l_old_start_date);
2285 hr_utility.set_location(l_proc, 100);
2286 --
2287 check_asg_st_change(p_person_id => p_person_id
2288 ,p_earlier_date => l_earlier_date
2289 ,p_later_date => l_later_date
2290 ,p_type => p_update_type
2291 ,p_old_start_date => l_old_start_date);
2292 hr_utility.set_location(l_proc, 110);
2293 --
2294 check_asg_change(p_person_id => p_person_id
2295 ,p_earlier_date => l_earlier_date
2296 ,p_later_date => l_later_date
2297 ,p_old_start_date => l_old_start_date);
2298 --
2299 -- Bug 3006094. Check if there are any person type changes
2300 -- between old and new hire dates.
2301 --
2302 check_user_person_type_changes(p_person_id => p_person_id
2303 ,p_earlier_date => l_earlier_date
2304 ,p_later_date => l_later_date
2305 ,p_old_start_date => l_old_start_date);
2306 hr_utility.set_location(l_proc, 115);
2307 --
2308 -- Fix for bug 3738058 starts here.
2309 --
2310 check_extra_details_of_service(p_person_id => p_person_id
2311 ,p_old_start_date => l_old_start_date
2312 ,p_new_start_date => l_new_start_date);
2313 --
2314 hr_utility.set_location(l_proc, 117);
2315 --
2316 -- Fix for bug 3738058 ends here.
2317 --
2318 else
2319 l_later_date := l_old_start_date;
2320 l_earlier_date := l_new_start_date;
2321 --
2322 check_people_changes(p_person_id => p_person_id
2323 ,p_earlier_date => l_earlier_date
2324 ,p_later_date => l_later_date
2325 ,p_old_start_date => l_old_start_date);
2326 hr_utility.set_location(l_proc, 120);
2327 --
2328 check_asg_change(p_person_id => p_person_id
2329 ,p_earlier_date => l_earlier_date
2330 ,p_later_date => l_later_date
2331 ,p_old_start_date => l_old_start_date);
2332 hr_utility.set_location(l_proc, 130);
2333 --
2334 check_prev_asg(p_person_id => p_person_id
2335 ,p_type => p_update_type
2336 ,p_old_start_date => l_old_start_date
2337 ,p_new_start_date => l_new_start_date);
2338 hr_utility.set_location(l_proc, 140);
2339 --
2340 end if;
2341 --
2342 check_recur_ee(p_person_id => p_person_id
2343 ,p_new_start_date => l_new_start_date
2344 ,p_old_start_date => l_old_start_date
2345 ,p_warn_raise => l_warn_ee);
2346 hr_utility.set_location(l_proc, 150);
2347 --
2348 --changes start for bug 6640794
2349
2350 check_ben_enteries(p_person_id => p_person_id
2351 ,p_old_start_date => l_old_start_date);
2352
2353 --changes end for bug 6640794
2354
2355 -- Process Logic: now we must update the relevant records in line with the new start date
2356 --
2357 -- Fix for bug 3390731 starts here. update the start_date with min(effective_start_date).
2358 --
2359 update per_people_f p
2360 set p.effective_start_date = decode(p.effective_start_date
2361 ,l_old_start_date,l_new_start_date,p.effective_start_date)
2362 ,p.effective_end_date = decode(p.effective_end_date
2363 ,l_old_start_date-1,l_new_start_date-1,p.effective_end_date)
2364 ,p.original_date_of_hire = decode(p.original_date_of_hire
2365 ,l_old_start_date, l_new_start_date,p.original_date_of_hire)
2366 where p.person_id = p_person_id;
2367 if sql%rowcount <1 then
2368 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
2369 hr_utility.set_message_token('TABLE','PER_ALL_PEOPLE_F');
2370 hr_utility.raise_error;
2371 end if;
2372 --
2373 update per_people_f p
2374 set p.start_date = (select min(ppf.effective_start_date)
2375 from per_people_f ppf
2376 where ppf.person_id = p_person_id)
2377 where p.person_id = p_person_id;
2378 --
2379 -- Fix for bug 3390731 ends here.
2380 --
2381 update_period(p_person_id => p_person_id
2382 ,p_old_start_date => l_old_start_date
2383 ,p_new_start_date => l_new_start_date
2384 ,p_type => p_update_type);
2385 hr_utility.set_location(l_proc, 160);
2386 --
2387 -- Update the addresses that start at the old hire date
2388 -- Providing that addresses end date either equal to new start date or greater than it.
2389 --
2390 update per_addresses a
2391 set a.date_from = l_new_start_date
2392 where a.date_from = l_old_start_date
2393 and nvl(a.date_to,l_new_start_date) >= l_new_start_date
2394 and a.person_id = p_person_id;
2395 --
2396 hr_utility.set_location(l_proc, 170);
2397 --
2398 update pay_personal_payment_methods_f p
2399 set p.effective_start_date = l_new_start_date
2400 where p.effective_start_date = l_old_start_date
2401 and p.effective_end_date >= l_new_start_date
2402 and exists
2403 (select 1
2404 from per_assignments_f a
2405 where p.assignment_id = a.assignment_id
2406 and a.assignment_type = p_update_type
2407 and a.person_id = p_person_id);
2408 --
2409 hr_utility.set_location(l_proc, 180);
2410 --
2411 update_spinal_placement(p_person_id => p_person_id
2412 ,p_old_start_date => l_old_start_date
2413 ,p_new_start_date => l_new_start_date
2414 ,p_type => p_update_type);
2415 hr_utility.set_location(l_proc, 190);
2416 --
2417 update_asg_rate(p_person_id => p_person_id
2418 ,p_old_start_date => l_old_start_date
2419 ,p_new_start_date => l_new_start_date
2420 ,p_type => p_update_type);
2421 hr_utility.set_location(l_proc, 190);
2422 --
2423 update_cost_allocation(p_person_id => p_person_id
2424 ,p_old_start_date => l_old_start_date
2425 ,p_new_start_date => l_new_start_date
2426 ,p_type => p_update_type);
2427 hr_utility.set_location(l_proc, 200);
2428 --
2429 update_asg_budget(p_person_id => p_person_id
2430 ,p_old_start_date => l_old_start_date
2431 ,p_new_start_date => l_new_start_date
2432 ,p_type => p_update_type);
2433 hr_utility.set_location(l_proc, 210);
2434 --
2435 --first update ALL matching cases of the period_of_placement_date_start for integrity
2436 --
2437 if p_update_type='C' then
2438 update per_assignments_f a
2439 set a.period_of_placement_date_start = l_new_start_date
2440 where a.period_of_placement_date_start = l_old_start_date
2441 and a.assignment_type = p_update_type
2442 and a.person_id = p_person_id;
2443 if sql%rowcount <1 then
2444 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
2445 hr_utility.set_message_token('TABLE','PER_ALL_ASSIGNMENTS_F');
2446 hr_utility.raise_error;
2447 end if;
2448 end if;
2449 --
2450 update per_assignments_f a
2451 set a.effective_start_date = l_new_start_date
2452 where a.effective_start_date = l_old_start_date
2453 and a.assignment_type = p_update_type
2454 and a.person_id = p_person_id;
2455 if sql%rowcount <1 then
2456 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
2457 hr_utility.set_message_token('TABLE','PER_ALL_ASSIGNMENTS_F');
2458 hr_utility.raise_error;
2459 end if;
2460 --
2461 -- 115.32 115.34 (START)
2462 --
2463 -- Update the EED for ASG records immediately before the updated ESD
2464 --
2465 UPDATE per_assignments_f A
2466 SET A.effective_end_date = (l_new_start_date - 1)
2467 WHERE A.effective_end_date = (l_old_start_date - 1)
2468 AND (l_new_start_date - 1) >= A.effective_start_date
2469 AND A.assignment_type = p_update_type
2470 AND A.person_id = p_person_id;
2471 --
2472 -- 115.32 115.34 (END)
2473 --
2474 call_trigger_hook(p_person_id => p_person_id,
2475 p_old_start_date => l_old_start_date,
2476 p_new_start_date => l_new_start_date
2477 );
2478 --
2479 --
2480 -- Bug 2614732 starts here.
2481 --
2482 update_probation_end( p_person_id, l_new_start_date);
2483 --
2484 -- Bug 2614732 ends here.
2485 --
2486 hr_utility.set_location(l_proc, 220);
2487 --
2488 -- Fix for bug 3738058 starts here.
2489 --
2490 update pqp_assignment_attributes_f paa
2491 set paa.effective_start_date = l_new_start_date
2492 where paa.effective_start_date = l_old_start_date
2493 and paa.assignment_id in
2494 (select paf.assignment_id
2495 from per_assignments_f paf
2496 where paf.effective_start_date = l_new_start_date
2497 and paf.person_id = p_person_id);
2498 --
2499 -- Fix for bug 3738058 ends here.
2500 --
2501 hr_contract_api.maintain_contracts
2502 (p_person_id => p_person_id
2503 ,p_new_start_date => l_new_start_date
2504 ,p_old_start_date => l_old_start_date);
2505 hr_utility.set_location(l_proc, 230);
2506 --
2507 update_tax(p_person_id => p_person_id
2508 ,p_new_start_date => l_new_start_date);
2509 hr_utility.set_location(l_proc, 240);
2510 --
2511 if p_applicant_number is not null then
2512 update_apl_asg(p_person_id => p_person_id
2513 ,p_old_start_date => l_old_start_date
2514 ,p_new_start_date => l_new_start_date);
2515 hr_utility.set_location(l_proc, 250);
2516 --
2517 update_apl(p_person_id => p_person_id
2518 ,p_old_start_date => l_old_start_date
2519 ,p_new_start_date => l_new_start_date);
2520 hr_utility.set_location(l_proc, 260);
2521 --
2522 end if;
2523 --
2524 hr_utility.set_location(l_proc, 270);
2525 --
2526 hr_per_type_usage_internal.change_hire_date_ptu
2527 (p_date_start => l_new_start_date
2528 ,p_old_date_start => l_old_start_date
2529 ,p_person_id => p_person_id
2530 ,p_system_person_type => l_system_person_type
2531 );
2532 hr_utility.set_location(l_proc, 280);
2533 --
2534 update_pay_proposal(p_person_id => p_person_id
2535 ,p_old_start_date => l_old_start_date
2536 ,p_new_start_date => l_new_start_date
2537 ,p_type => p_update_type);
2538 hr_utility.set_location(l_proc, 290);
2539 --
2540 if p_update_type = 'E' then
2541 --
2542 -- 115.30 (START)
2543 --
2544 if ( nvl(fnd_profile.value('HR_MV_HIRE_SKIP_ACT_VALIDATION'),'N') = 'N'
2545 OR
2546 NOT l_hd_rule_found
2547 OR
2548 (l_hd_rule_found AND nvl(l_hd_rule_value,'N') = 'N')
2549 )
2550 then
2551 --
2552 -- Change Hire Date past PAY actions not allowed
2553 --
2554 --
2555 -- 115.30 (END)
2556 --
2557 run_alu_ee(p_person_id => p_person_id
2558 ,p_old_start_date => l_old_start_date
2559 ,p_new_start_date => l_new_start_date
2560 ,p_type => p_update_type);
2561 hr_utility.set_location(l_proc, 300);
2562 --
2563 -- 115.30 (START)
2564 --
2565 else
2566 --
2567 -- Change Hire Date past PAY actions allowed
2568 -- Invoke equivalent new routine from PAY team.
2569 --
2570 per_pds_utils.hr_run_alu_ee(p_person_id => p_person_id
2571 ,p_old_start_date => l_old_start_date
2572 ,p_new_start_date => l_new_start_date
2573 ,p_type => p_update_type);
2574 --
2575 hr_utility.set_location(l_proc, 305);
2576 end if;
2577 --
2578 -- 115.30 (END)
2579 --
2580 --
2581 if l_new_start_date < l_old_start_date then
2582 per_people12_pkg.maintain_coverage
2583 (p_person_id => p_person_id
2584 ,p_type => 'EMP'
2585 );
2586 hr_utility.set_location(l_proc, 310);
2587 end if;
2588 end if;
2589 --
2590 END IF; -- end of check that old and new start dates are actually different
2591 --
2592 -- Call After Process User Hook
2593 --
2594 begin
2595 hr_change_start_date_bk1.update_start_date_a
2596 (p_person_id => p_person_id
2597 ,p_old_start_date => l_old_start_date
2598 ,p_new_start_date => l_new_start_date
2599 ,p_update_type => p_update_type
2600 ,p_applicant_number => p_applicant_number
2601 ,p_warn_ee => l_warn_ee
2602 );
2603 exception
2604 when hr_api.cannot_find_prog_unit then
2605 hr_api.cannot_find_prog_unit_error
2606 (p_module_name => 'update_start_date'
2607 ,p_hook_type => 'AP'
2608 );
2609 end;
2610 hr_utility.set_location(l_proc, 310);
2611 --
2612 -- When in validation only mode raise the Validate_Enabled exception
2613 --
2614 if p_validate then
2615 raise hr_api.validate_enabled;
2616 end if;
2617 --
2618 -- Set all output arguments
2619 --
2620 p_warn_ee := l_warn_ee;
2621 --
2622 hr_utility.set_location(' Leaving:'||l_proc, 500);
2623 exception
2624 when hr_api.validate_enabled then
2625 --
2626 -- As the Validate_Enabled exception has been raised
2627 -- we must rollback to the savepoint
2628 --
2629 rollback to update_start_date;
2630 --
2631 -- Only set output warning arguments
2632 -- (Any key or derived arguments must be set to null
2633 -- when validation only mode is being used.)
2634 --
2635 p_warn_ee := 'N';
2636 hr_utility.set_location(' Leaving:'||l_proc, 600);
2637 when others then
2638 --
2639 -- A validation or unexpected error has occured
2640 --
2641 rollback to update_start_date;
2642 --
2643 -- set in out parameters and set out parameters
2644 --
2645 p_warn_ee := null;
2646 --
2647 hr_utility.set_location(' Leaving:'||l_proc, 700);
2648 raise;
2649 end update_start_date;
2650 --
2651 end hr_change_start_date_api;