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