1 package body hr_date_chk as
2 /* $Header: pehchchk.pkb 120.1 2006/01/13 13:52:37 irgonzal noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : hr_date_chk (BODY)
24
25 Description : This package declares procedures required to test when
26 the period of service and application start dates changes
27 are valid. If they are valid then database items which
28 must have the same dates are updated.
29 */
30 /*
31 Change List
32 -----------
33
34 Version Date Author ER/CR No. Description of Change
35 -------+---------+----------+---------+--------------------------
36 70.0 23-MAY-93 Tmathers Date Created
37 70.1 01-JUN-93 Tmathers Changed updates to Per_people_f.
38 70.4 18-OCT-93 TMathers Changed updates to per_people_f to
39 80.1 update all rows for a person rather than
40 those affected by start and end dates.
41 70.5 21-DEC-93 PShergill Improved speed of check_for_entries
42 70.6 18-FEB-94 TMathers Fixed BUG B385.
43 70.7 14-JUL-94 TMathers Fixed BUG WW225779 and 225558
44 70.9 23-Oct-94 TMathers Fixed BUG WW245159.
45 Added extrajoin to check_for_entries
46 to stop errors when entries exist
47 between new and old hire dates.
48 70.10 15-Feb-95 TMathers Fixed BUG WW264072 , added extra check
49 to check_sp_placements, and added
50 spinal points to lock_row
51 and update hire rows.
52 70.11 29-APR-95 TMathers 275487 Added check for supervisor/Payroll
53 276867 not existing, removed check_for_entries
54 when changing hire date.
55 70.12 13-Jul-95 TMathers Added check_for_cost_alloc for
56 292807
57 70.13 25-JUL-95 AForte Changed tokenised message
58 HR_7474_EMP_SUP_PAY_NOT_EXIST
59 to hard coded messages
60 HR_7679_EMP_SUP_PAY_NOT_EXIST and
61 HR_7680_EMP_SUP_PAY_NOT_EXIST
62 70.14 19-Sep-95 TMathers Fixed 308000 removed select
63 and join on minimum date.
64 70.16 25-Nov-96 VTreiger 401587 Added check for completed payroll
65 actions beteween the old and new hire dates.
66 399253 Added check for contiguous periods
67 of service.
68 70.17 27-Jan-97 VTreiger 399253 Check for contiguous periods of
69 service now works for any change of
70 the hire date value.
71 110.1 28-JUL-97 Mbocutt N/A Changed to use language independent
72 date format mask in check_for_contig_pos
73 110.2 14-Oct-97 rfine 563034 Changed modified table names to
74 include _ALL
75
76 110.3 06-nov-97 achauhan Added the update of the tax tables in the
77 update_hire_records, in case of 'US Payroll'
78 installed and change in hire date.
79
80 110.5 8-MAY-1998 SASmith Due to the date tracking of per_assignment_budget_values_f.
81 Add the update to this table in case there are changes in the
82 hire date then there will be a required change in the
83 assignment budget values.
84
85 110.6 22-MAY-98 Asahay 638603 modified update_hire_records
86 to update per_applications table
87 with DATE_END for those applicants
88 who are hired as EMPLOYEE and not
89 EMPLOYEE and APPLICANT.
90
91 110.7 23-MAR-99 F.Duchene Added a call to hr_contract_api.maintain_contracts
92 in update_hire_records and update_appl_records
93 to keep CTR start-dates in synch with PER and ASG
94
95 110.8 29-FEB-2000 tclewis removed check to determine if payroll is installed.
96 this check is now performed in the
97 pay_us_emp_dt_tax_rules.default_tax_with_validation
98 procedure.
99
100 115.7 02-SEP-2002 vramanai 2403885 Modified the cursor defination of 'pay' to enhance the
101 performance of the query.
102
103 115.8 23-MAY-2003 vramanai 2947287 Modified the cursor app in procedure update_hire_records
104 to fetch only those records which donot have
105 current_applicant_flag set
106
107 115.9 26-MAY-2003 vramanai 2947287 Corrected gscc warnings.
108 115.10 13-Jan-2006 irgonzal 4894555 Perf changes. Modified update statement in
109 update_appl_records procedure.
110
111
112 */
113 procedure lock_row(p_person_id NUMBER
114 ,p_person_type VARCHAR2) is
115 l_dummy VARCHAR2(30);
116 cursor add is
117 select 'address'
118 from per_addresses pa
119 where pa.person_id = p_person_id
120 for update of person_id;
121 cursor pay is
122 select 'pay'
123 from pay_personal_payment_methods_f pa
124 where pa.assignment_id IN (select a.assignment_id
125 from per_assignments_f a
126 where a.assignment_type = 'E'
127 and a.person_id = p_person_id)
128 for update of pa.assignment_id;
129 --
130 cursor ssp is
131 select 'ssp'
132 from per_spinal_point_placements_f sp
133 where assignment_id in (select a.assignment_id
134 from per_assignments_f a
135 where person_id = p_person_id
136 and a.assignment_type = 'E')
137 for update of effective_start_date;
138 --
139 cursor cost is
140 select 'cost'
141 from pay_cost_allocations_f cost
142 where assignment_id in (select a.assignment_id
143 from per_assignments_f a
144 where person_id = p_person_id
145 and a.assignment_type = 'E')
146 for update of effective_start_date;
147 --
148 cursor ass is
149 select 'assignment'
150 from per_assignments_f pa
151 where pa.person_id = p_person_id
152 for update of person_id;
153 cursor app is
154 select 'application'
155 from per_applications pa
156 where pa.person_id = p_person_id
157 for update of person_id;
158 cursor per is
159 select 'person'
160 from per_people_f pa
161 where pa.person_id = p_person_id
162 for update of person_id;
163 begin
164 hr_utility.set_location('hr_date_chk.lock_row',1);
165 open add;
166 loop
167 fetch add into l_dummy;
168 exit when add%NOTFOUND;
169 end loop;
170 close add;
171 --
172 hr_utility.set_location('hr_date_chk.lock_row',2);
173 if (p_person_type = 'E') then
174 begin
175 open pay;
176 loop
177 fetch pay into l_dummy;
178 exit when pay%NOTFOUND;
179 end loop;
180 close pay;
181 end;
182 begin
183 open ssp;
184 loop
185 fetch ssp into l_dummy;
186 exit when ssp%NOTFOUND;
187 end loop;
188 close ssp;
189 end;
190 begin
191 open cost;
192 loop
193 fetch cost into l_dummy;
194 exit when cost%NOTFOUND;
195 end loop;
196 close cost;
197 end;
198 end if;
199 --
200 hr_utility.set_location('hr_date_chk.lock_row',3);
201 open ass;
202 loop
203 fetch ass into l_dummy;
204 exit when ass%NOTFOUND;
205 end loop;
206 if ass%ROWCOUNT < 1 then
207 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
208 hr_utility.set_message_token('PROCEDURE', 'lock_row');
209 hr_utility.set_message_token('STEP', '1');
210 hr_utility.raise_error;
211 end if;
212 close ass;
213 --
214 hr_utility.set_location('hr_date_chk.lock_row',4);
215 open app;
216 loop
217 fetch app into l_dummy;
218 exit when app%NOTFOUND;
219 end loop;
220 close app;
221 --
222 hr_utility.set_location('hr_date_chk.lock_row',5);
223 open per;
224 loop
225 fetch per into l_dummy;
226 exit when per%NOTFOUND;
227 end loop;
228 if per%ROWCOUNT < 1 then
229 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
230 hr_utility.set_message_token('PROCEDURE', 'lock_row');
231 hr_utility.set_message_token('STEP', '2');
232 hr_utility.raise_error;
233 end if;
234 close per;
235 --
236 end;
237 ------------------------- BEGIN :check_for_compl_actions -----------------
238 procedure check_for_compl_actions(p_person_id NUMBER
239 ,p_s_start_date DATE
240 ,p_start_date DATE) is
241 l_act_chk VARCHAR2(1) := 'N';
242 begin
243 -- VT 11/22/96 #401587 check previous completed actions
244 BEGIN
245 SELECT 'Y'
246 INTO l_act_chk
247 FROM sys.dual
248 WHERE EXISTS
249 (SELECT NULL
250 FROM pay_payroll_actions pac,
251 pay_assignment_actions act,
252 per_assignments_f asg
253 WHERE asg.person_id = p_person_id
254 AND act.assignment_id = asg.assignment_id
255 AND pac.payroll_action_id = act.payroll_action_id
256 AND pac.action_status = 'C'
257 AND ((pac.effective_date BETWEEN p_s_start_date AND p_start_date)
258 OR (pac.date_earned BETWEEN p_s_start_date AND p_start_date)));
259 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
260 END;
261 IF l_act_chk = 'Y' THEN
262 hr_utility.set_message(801,'HR_51810_EMP_COMPL_ACTIONS');
263 hr_utility.raise_error;
264 END IF;
265 --
266 end;
267 --
268 ------------------------- BEGIN :check_for_contig_pos --------------------
269 procedure check_for_contig_pos(p_person_id NUMBER
270 ,p_s_start_date DATE
271 ,p_start_date DATE) is
272 -- VT 11/25/96 #399253
273 l_action_chk VARCHAR2(1) := 'N';
274 l_prev_end_date DATE;
275 l_date_start DATE;
276 l_act_term_date DATE;
277 --
278 cursor pps
279 is
280 select date_start
281 ,actual_termination_date
282 from per_periods_of_service
283 WHERE PERSON_ID = P_PERSON_ID
284 ORDER BY date_start;
285 --
286 begin
287 l_action_chk := 'N';
288 BEGIN
289 OPEN pps;
290 l_prev_end_date := to_date('01/01/0001','DD/MM/YYYY');
291 LOOP
292 FETCH pps INTO l_date_start,l_act_term_date;
293 EXIT WHEN pps%NOTFOUND;
294 IF (l_date_start - 1 = l_prev_end_date) AND
295 (p_s_start_date = l_date_start) THEN
296 l_action_chk := 'Y';
297 EXIT;
298 END IF;
299 l_prev_end_date := l_act_term_date;
300 END LOOP;
301 CLOSE pps;
302 IF l_action_chk = 'Y' THEN
303 hr_utility.set_message(801,'HR_51811_EMP_CONTIG_POS');
304 hr_utility.raise_error;
305 END IF;
306 END;
307 end;
308 --
309 ------------------------- BEGIN :check_supe_pay --------------------------
310 procedure check_supe_pay(p_period_of_service_id NUMBER
311 ,p_start_date DATE) is
312 l_payroll_id number;
313 l_supervisor_id number;
314 l_temp varchar2(1);
315 --
316 -- Cannot move start date if there are assignment changes
317 -- only need to test the first assignment row
318 --
319 cursor assignment is
320 select a.payroll_id , a.supervisor_id
321 from per_assignments_f a,
322 per_periods_of_service p
323 where a.period_of_service_id = p.period_of_service_id
324 and p.period_of_service_id = p_period_of_service_id
325 and p.date_start = a.effective_start_date;
326 --
327 begin
328 open assignment;
329 fetch assignment into l_payroll_id,l_supervisor_id;
330 close assignment;
331 --
332 if l_payroll_id is not null then
333 begin
334 select '1'
335 into l_temp
336 from sys.dual
337 where exists ( select payroll_id
338 from pay_payrolls_f
339 where payroll_id = l_payroll_id
340 and p_start_date between
341 effective_start_date and effective_end_date
342 );
343 exception
344 when no_data_found then
345 hr_utility.set_message('801','HR_7679_EMP_SUP_PAY_NOT_EXIST');
346 hr_utility.raise_error;
347 end;
348 end if;
349 if l_supervisor_id is not null then
350 begin
351 select '1'
352 into l_temp
353 from sys.dual
354 where exists ( select person_id
355 from per_people_f
356 where person_id = l_supervisor_id
357 and current_employee_flag = 'Y'
358 and p_start_date between
359 effective_start_date and effective_end_date
360 );
361 exception
362 when no_data_found then
363 hr_utility.set_message('801','HR_7680_EMP_SUP_PAY_NOT_EXIST');
364 hr_utility.raise_error;
365 end;
366 end if;
367 end;
368 --
369 ------------------------- BEGIN : check_for_entries --------------------------
370 procedure check_for_entries (p_person_id NUMBER
371 ,p_period_of_service_id NUMBER
372 ,p_start_date DATE) is
373 v_dummy number;
374 begin
375 hr_utility.set_location('hr_date_chk.check_for_entries',1);
376 select 1
377 into v_dummy
378 from sys.dual
379 where exists (select null
380 from pay_element_entries_f n,
381 per_periods_of_service p,
382 per_assignments_f a
383 where a.person_id = p_person_id
384 and a.period_of_service_id = p_period_of_service_id
385 and p.period_of_service_id = p_period_of_service_id
386 and n.assignment_id = a.assignment_id
387 and n.effective_start_date > p.date_start
388 and n.effective_start_date < p_start_date);
389 hr_utility.set_message(801,'HR_6836_EMP_REF_DATE_CHG');
390 hr_utility.raise_error;
391 --
392 exception
393 when NO_DATA_FOUND then null;
394 when others then raise;
395 end;
396 --
397 ------------------------- BEGIN : check_for_sp_placements ---------------------
398 procedure check_for_sp_placements(p_person_id NUMBER
399 ,p_period_of_service_id NUMBER
400 ,p_start_date DATE) is
401 v_dummy number;
402 --
403 begin
404 hr_utility.set_location('hr_date_chk.check_sp_placement',1);
405 select 1
406 into v_dummy
407 from sys.dual
408 where exists (select 1
409 from per_spinal_point_placements_f sp,
410 per_periods_of_service p,
411 per_assignments_f a
412 where a.person_id = p_person_id
413 and a.period_of_service_id = p_period_of_service_id
414 and p.period_of_service_id = p_period_of_service_id
415 and a.assignment_id = sp.assignment_id
416 and sp.effective_start_date > p.date_start
417 and sp.effective_start_date < p_start_date
418 );
419 --
420 hr_utility.set_message(801,'HR_6837_EMP_REF_DATE_CHG');
421 hr_utility.raise_error;
422 --
423 exception
424 when NO_DATA_FOUND then null;
425 when others then raise;
426 --
427 end;
428 --
429 ------------------------- BEGIN : check_for_cost_alloc ---------------------
430 procedure check_for_cost_alloc(p_person_id NUMBER
431 ,p_period_of_service_id NUMBER
432 ,p_start_date DATE) is
433 v_dummy number;
434 --
435 begin
436 hr_utility.set_location('hr_date_chk.check_sp_placement',1);
437 select 1
438 into v_dummy
439 from sys.dual
440 where exists (select 1
441 from PAY_COST_ALLOCATIONS_F ca,
442 per_periods_of_service p,
443 per_assignments_f a
444 where a.person_id = p_person_id
445 and a.period_of_service_id = p_period_of_service_id
446 and p.period_of_service_id = p_period_of_service_id
447 and a.assignment_id = ca.assignment_id
448 and ca.effective_start_date > p.date_start
449 and ca.effective_start_date < p_start_date
450 );
451 --
452 hr_utility.set_message(801,'HR_7860_EMP_REF_DATE_CHG');
453 hr_utility.raise_error;
454 --
455 exception
456 when NO_DATA_FOUND then null;
457 when others then raise;
458 --
459 end;
460 --
461 ------------------------- BEGIN : check_people_changes -----------------------
462 procedure check_people_changes(p_person_id NUMBER
463 ,p_earlier_date DATE
464 ,p_later_date DATE
465 ,p_start_date DATE) is
466 v_dummy number;
467 --
468 begin
469 hr_utility.set_location('hr_date_chk.check_people_changes',1);
470 select 1
471 into v_dummy
472 from sys.dual
473 where exists (select 1
474 from per_people_f p
475 where p.effective_start_date between p_earlier_date
476 and p_later_date
477 and p.effective_start_date <> p_start_date
478 and p.person_id = p_person_id
479 );
480 --
481 hr_utility.set_message(801,'HR_6841_EMP_REF_DATE_CHG');
482 hr_utility.raise_error;
483 --
484 exception
485 when NO_DATA_FOUND then null;
486 when others then raise;
487 --
488 end;
489 --
490 ------------------------- BEGIN : check_for_ass_st_chg -----------------------
491 procedure check_for_ass_st_chg(p_person_id NUMBER
492 ,p_earlier_date DATE
493 ,p_later_date DATE
494 ,p_assignment_type VARCHAR2
495 ,p_start_date DATE) is
496 v_dummy number;
497 --
498 begin
499 hr_utility.set_location('hr_date_chk.check_for_ass_st_chg',1);
500 select 1
501 into v_dummy
502 from sys.dual
503 where exists (select 1
504 from per_assignments a
505 ,per_assignments_f f
506 where f.effective_start_date between p_earlier_date
507 and p_later_date
508 and f.effective_start_date <> p_start_date
509 and f.assignment_id = a.assignment_id
510 and a.assignment_type = p_assignment_type
511 and f.assignment_status_type_id <>
512 a.assignment_status_type_id
513 and f.person_id = a.person_id
514 and a.person_id = p_person_id
515 );
516 --
517 hr_utility.set_message(801,'HR_6838_EMP_REF_DATE_CHG');
518 hr_utility.raise_error;
519 --
520 exception
521 when NO_DATA_FOUND then null;
522 when others then raise;
523 --
524 end;
525 --
526 ------------------------- BEGIN : check_for_ass_chg --------------------------
527 procedure check_for_ass_chg(p_person_id NUMBER
528 ,p_earlier_date DATE
529 ,p_later_date DATE
530 ,p_assignment_type VARCHAR2
531 ,p_s_start_date DATE
532 ,p_start_date DATE) is
533 v_dummy number;
534 --
535 begin
536 hr_utility.set_location('hr_date_chk.check_for_ass_chg',1);
537 select 1
538 into v_dummy
539 from sys.dual
540 where exists (select 1
541 from per_assignments_f f
542 where f.effective_start_date between p_earlier_date
543 and p_later_date
544 and f.effective_start_date <> p_s_start_date
545 -- and f.assignment_type =p_assignment_type
546 and f.person_id = p_person_id
547 );
548 --
549 hr_utility.set_message(801,'HR_6839_EMP_REF_DATE_CHG');
550 hr_utility.raise_error;
551 --
552 exception
553 when NO_DATA_FOUND then null;
554 when others then raise;
555 --
556 end;
557 ------------------------- BEGIN : check_for_prev_emp_ass ----------------------
558 procedure check_for_prev_emp_ass(p_person_id NUMBER
559 ,p_assignment_type VARCHAR2
560 ,p_s_start_date DATE
561 ,p_start_date DATE) is
562 v_dummy number;
563 --
564 begin
565 hr_utility.set_location('hr_date_chk.check_for_prev_emp_ass',1);
566 select 1
567 into v_dummy
568 from sys.dual
569 where exists (select 1
570 from per_assignments_f f
571 where f.effective_start_date >= p_start_date
572 and f.effective_start_date < p_s_start_date
573 and f.assignment_type =p_assignment_type
574 and f.person_id = p_person_id
575 );
576 --
577 hr_utility.set_message(801,'HR_6840_EMP_ENTER_PERIOD');
578 hr_utility.raise_error;
579 --
580 exception
581 when NO_DATA_FOUND then null;
582 when others then raise;
583 --
584 end;
585 --
586 ------------------------- BEGIN : check_hire_ref_int --------------------------
587 procedure check_hire_ref_int(p_person_id NUMBER
588 ,p_business_group_id NUMBER
589 ,p_period_of_service_id NUMBER
590 ,p_s_start_date DATE
591 ,p_system_person_type VARCHAR2
592 ,p_start_date DATE) is
593 l_earlier_date DATE;
594 l_assignment_type VARCHAR2(1);
595 l_later_date DATE;
596 --
597 begin
598 l_assignment_type:='E';
599 l_later_date:=p_start_date;
600 l_earlier_date:=p_s_start_date;
601 hr_utility.set_location('hr_date_chk.check_hire_ref_int',1);
602 --
603 if p_start_date > p_s_start_date then
604 -- VT 11/27/96 #401587
605 hr_date_chk.check_for_compl_actions(p_person_id
606 ,p_s_start_date
607 ,p_start_date);
608 --
609 -- VT 11/27/96 #399253
610 hr_date_chk.check_for_contig_pos(p_person_id
611 ,p_s_start_date
612 ,p_start_date);
613 --
614 hr_date_chk.check_supe_pay(p_period_of_service_id
615 ,p_start_date);
616 --
617 /* hr_date_chk.check_for_entries(p_person_id
618 ,p_period_of_service_id
619 ,p_start_date); */
620 --
621 hr_date_chk.check_for_sp_placements(p_person_id
622 ,p_period_of_service_id
623 ,p_start_date);
624 --
625 hr_date_chk.check_for_cost_alloc(p_person_id
626 ,p_period_of_service_id
627 ,p_start_date);
628 --
629 hr_date_chk.check_for_ass_st_chg(p_person_id
630 ,l_earlier_date
631 ,l_later_date
632 ,l_assignment_type
633 ,p_s_start_date);
634 --
635 hr_date_chk.check_people_changes(p_person_id
636 ,l_earlier_date
637 ,l_later_date
638 ,p_s_start_date);
639 --
640 hr_date_chk.check_for_ass_chg(p_person_id
641 ,l_earlier_date
642 ,l_later_date
643 ,l_assignment_type
644 ,p_s_start_date
645 ,p_start_date);
646 --
647 else
648 -- VT 01/27/97 #399253
649 hr_date_chk.check_for_contig_pos(p_person_id
650 ,p_s_start_date
651 ,p_start_date);
652 --
653 l_later_date:=p_s_start_date;
654 l_earlier_date:=p_start_date;
655
656 --
657 hr_date_chk.check_supe_pay(p_period_of_service_id
658 ,p_start_date);
659 --
660 hr_date_chk.check_people_changes(p_person_id
661 ,l_earlier_date
662 ,l_later_date
663 ,p_s_start_date);
664 --
665 hr_date_chk.check_for_ass_chg(p_person_id
666 ,l_earlier_date
667 ,l_later_date
668 ,l_assignment_type
669 ,p_s_start_date
670 ,p_start_date);
671 --
672 hr_date_chk.check_for_prev_emp_ass(p_person_id
673 ,l_assignment_type
674 ,p_s_start_date
675 ,p_start_date);
676 end if;
677 hr_date_chk.lock_row(p_person_id =>p_person_id
678 ,p_person_type =>l_assignment_type);
679 exception
680 when others then
681 raise;
682 end;
683 --
684 ------------------------- BEGIN : update_hire_records -------------------------
685 procedure update_hire_records(p_person_id NUMBER
686 ,p_app_number VARCHAR2
687 ,p_start_date DATE
688 ,p_s_start_date DATE
689 ,p_user_id NUMBER
690 ,p_login_id NUMBER) is
691 l_assignment_id NUMBER;
692 l_application_id NUMBER;
693 l_sp_id NUMBER;
694 l_ca_id NUMBER;
695 l_abv_id NUMBER;
696 l_pps_id NUMBER;
697 l_business_group_id number;
698 l_ret_code number;
699 l_ret_text varchar2(240);
700
701
702 cursor app_ass is
703 select assignment_id
704 from per_assignments_f a
705 where a.effective_end_date = p_s_start_date - 1
706 and a.assignment_type = 'A'
707 and a.person_id = p_person_id;
708 --
709 cursor app is
710 select application_id
711 from per_applications a
712 where a.person_id = p_person_id
713 and a.date_received = (
714 select max(a2.date_received)
715 from per_applications a2
716 where a2.person_id = a.person_id
717 and a2.date_received < p_start_date)
718 and not exists(select 1 --bug#2947287
719 from per_people_f peo
720 where peo.person_id = p_person_id
721 and peo.person_id = a.person_id
722 and peo.effective_start_date = p_s_start_date
723 and peo.current_applicant_flag = 'Y');
724 --
725 cursor ssp is
726 select placement_id
727 from per_spinal_point_placements_f sp
728 where assignment_id in (select a.assignment_id
729 from per_assignments_f a
730 where person_id = p_person_id
731 and a.assignment_type = 'E'
732 and a.effective_start_date = p_s_start_date)
733 and sp.effective_start_date = p_s_start_date;
734 --
735 cursor cost is
736 select COST_ALLOCATION_ID
737 from PAY_COST_ALLOCATIONS_F pca
738 where assignment_id in (select a.assignment_id
739 from per_assignments_f a
740 where person_id = p_person_id
741 and a.assignment_type = 'E'
742 and a.effective_start_date = p_s_start_date)
743 and pca.effective_start_date = p_s_start_date;
744 --
745 cursor pps
746 is
747 select period_of_Service_id
748 from per_periods_OF_SERVICE
749 WHERE PERSON_ID = P_PERSON_ID
750 AND DATE_START = P_S_START_DATE;
751 --
752 cursor csr_get_bg is
753 select business_group_id
754 from per_people_f
755 where person_id = p_person_id
756 and rownum < 2;
757 --
758 cursor abv is
759 select ASSIGNMENT_BUDGET_VALUE_ID
760 from PER_ASSIGNMENT_BUDGET_VALUES_F abv
761 where assignment_id in (select a.assignment_id
762 from per_assignments_f a
763 where person_id = p_person_id
764 and a.assignment_type = 'E'
765 and a.effective_start_date = p_s_start_date)
766 and abv.effective_start_date = p_s_start_date;
767 --
768
769 begin
770 hr_utility.set_location('hr_date_chk.update_hire_records',1);
771 -- Update the addresses that start at the old hire date
772 -- Providing that the addresses end date is either equal to
773 -- the new start date or greater than it.
774 update per_addresses a
775 set a.date_from = p_start_date
776 where a.date_from = p_s_start_date
777 and nvl(a.date_to,p_start_date) >= p_start_date
778 and a.person_id = p_person_id;
779 --
780 --
781 hr_utility.set_location('hr_date_chk.update_hire_records',2);
782 update pay_personal_payment_methods_f p
783 set p.effective_start_date = p_start_date
784 where p.effective_start_date = p_s_start_date
785 and p.effective_end_date >= p_start_date
786 and exists (select 1
787 from per_assignments_f a
788 where p.assignment_id = a.assignment_id
789 and a.assignment_type = 'E'
790 and a.person_id = p_person_id);
791 begin
792 open ssp;
793 loop
794 fetch ssp into l_sp_id;
795 exit when ssp%NOTFOUND;
796 update per_spinal_point_placements_f
797 set effective_start_date = p_start_date
798 where effective_start_date = p_s_start_date
799 and placement_id = l_sp_id;
800 if sql%rowcount <1 then
801 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
802 hr_utility.set_message_token('TABLE','PER_SPINAL_POINT_PLACEMENTS_F');
803 hr_utility.raise_error;
804 end if;
805 end loop;
806 close ssp;
807 end;
808 --
809 hr_utility.set_location('hr_date_chk.update_hire_records',3);
810 begin
811 open cost;
812 loop
813 fetch cost into l_ca_id;
814 exit when cost%NOTFOUND;
815 update PAY_COST_ALLOCATIONS_F
816 set effective_start_date = p_start_date
817 where effective_start_date = p_s_start_date
818 and COST_ALLOCATION_ID = l_ca_id;
819 if sql%rowcount <1 then
820 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
821 hr_utility.set_message_token('TABLE','PAY_COST_ALLOCATIONS_F');
822 hr_utility.raise_error;
823 end if;
824 end loop;
825 close cost;
826 end;
827
828 -- Update to assignment budget values required as this is now being date tracked.
829 -- This code does not deal with ALL possibilities and assumes that the user has created and
830 -- immediately changes the hire date.
831 -- If date track updates is used prior to changing the hire date then this may cause erroneous rows
832 -- on the database.
833 -- This is a wider issue and this code will be left like this until all changes are made.
834 -- SASMITH 8-MAY-1998
835 --
836
837 hr_utility.set_location('hr_date_chk.update_hire_records',5);
838 begin
839 open abv;
840 loop
841 fetch abv into l_abv_id;
842 exit when abv%NOTFOUND;
843
844 update PER_ASSIGNMENT_BUDGET_VALUES_F
845 set effective_start_date = p_start_date
846 where effective_start_date = p_s_start_date
847 and effective_end_date >= p_start_date
848 and ASSIGNMENT_BUDGET_VALUE_ID = l_abv_id;
849
850 if sql%rowcount <1 then
851 null;
852 end if;
853 end loop;
854 close abv;
855 end;
856
857 --
858 hr_utility.set_location('hr_date_chk.update_hire_records',10);
859 --
860 -- BUG 308000 removed select min (effective_date) code
861 -- and it's reference in following code tm 19-sep-1995.
862 --
863 update per_assignments_f a
864 set a.effective_start_date = p_start_date,
865 a.last_update_date = sysdate,
866 a.last_updated_by = p_user_id,
867 a.last_update_login = p_login_id
868 where a.effective_start_date = p_s_start_date
869 and a.assignment_type = 'E'
870 and a.person_id = p_person_id;
871 --
872 if sql%rowcount <1 then
873 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
874 hr_utility.set_message_token('TABLE','PER_ALL_ASSIGNMENTS_F');
875 hr_utility.raise_error;
876 end if;
877 --
878 --
879 hr_utility.set_location('hr_date_chk.update_hire_records',12);
880 -- keep contracts in synch with PER and ASG :
881 hr_contract_api.maintain_contracts
882 (p_person_id
883 ,p_start_date
884 ,p_s_start_date);
885 --
886 --
887 hr_utility.set_location('hr_date_chk.update_hire_records',15);
888 --
889 -- update the tax records and pull back their effective start date if
890 -- the defaulting tax criteria is met.
891
892 -- Checking if payroll installed is now handled in default_tax_with_validation
893 -- if hr_utility.chk_product_install(p_product =>'Oracle Payroll', then
894 open csr_get_bg;
895 fetch csr_get_bg into l_business_group_id;
896 if csr_get_bg%NOTFOUND then
897 close csr_get_bg;
898 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
899 hr_utility.set_message_token('PROCEDURE', 'update_hire_records');
900 hr_utility.set_message_token('STEP', '1');
901 hr_utility.raise_error;
902 end if;
903 close csr_get_bg;
904
905 pay_us_emp_dt_tax_rules.default_tax_with_validation(p_assignment_id => null,
906 p_person_id => p_person_id,
907 p_effective_start_date => p_start_date,
908 p_effective_end_date => null,
909 p_session_date => null,
910 p_business_group_id => l_business_group_id,
911 p_from_form => 'Person',
912 p_mode => null,
913 p_location_id => null,
914 p_return_code => l_ret_code,
915 p_return_text => l_ret_text);
916 -- end if; --end if payroll_installed
917
918
919 if p_app_number is not null then
920 hr_utility.set_location('hr_date_chk.update_hire_records',20);
921 begin
922 open app_ass;
923 loop
924 fetch app_ass into l_assignment_id;
925 exit when app_ass%NOTFOUND;
926 update per_assignments_f a
927 set a.effective_end_date = p_start_date - 1,
928 a.last_update_date = sysdate,
929 a.last_updated_by = p_user_id,
930 a.last_update_login = p_login_id
931 where a.effective_end_date =
932 (select max(a2.effective_end_date)
933 from per_assignments_f a2
934 where a2.assignment_id = a.assignment_id
935 and a2.assignment_type = 'A')
936 and a.assignment_id = l_assignment_id;
937 --
938 if sql%rowcount <1 then
939 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
940 hr_utility.set_message_token('TABLE','PER_ALL_ASSIGNMENTS_F');
941 hr_utility.raise_error;
942 end if;
943 end loop;
944 close app_ass;
945 --
946 end;
947
948 begin
949 open app;
950 loop
951 fetch app into l_application_id;
952 exit when app%NOTFOUND;
953 update per_applications a1
954 set a1.date_end = p_start_date - 1
955 where a1.application_id = l_application_id
956 /* Fix for Bug 673066 */
957 and not exists (select 1
958 from per_people_f peo
959 where peo.person_id = p_person_id
960 and a1.person_id = peo.person_id
961 and peo.effective_start_date = p_s_start_date
962 and peo.current_applicant_flag = 'Y');
963 /* End fix for Bug 673066 */
964 --
965 hr_utility.set_location('hr_date_chk.update_hire_records',25);
966
967 if sql%rowcount <1 then
968 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
969 hr_utility.set_message_token('TABLE','PER_APPLICATIONS');
970 hr_utility.raise_error;
971 end if;
972 end loop;
973 close app;
974 end;
975 end if;
976 --
977 hr_utility.set_location('hr_date_chk.update_hire_records',30);
978 update per_people_f p
979 set p.start_date =decode(p.start_date,p_s_start_date, p_start_date,
980 p.start_date),
981 p.effective_start_date =decode(p.effective_start_date,
982 p_s_start_date, p_start_date, p.effective_start_date),
983 p.effective_end_date =decode(p.effective_end_date,
984 p_s_start_date - 1, p_start_date - 1, p.effective_end_date),
985 p.last_update_date = sysdate,
986 p.last_updated_by = p_user_id,
987 p.last_update_login = p_login_id
988 where p.person_id = p_person_id;
989 --
990 if sql%rowcount <1 then
991 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
992 hr_utility.set_message_token('TABLE','PER_ALL_PEOPLE_F');
993 hr_utility.raise_error;
994 end if;
995 --
996 end;
997 --
998 ------------------------- BEGIN : check_apl_ref_int --------------------------
999 procedure check_apl_ref_int(p_person_id NUMBER
1000 ,p_business_group_id NUMBER
1001 ,p_system_person_type VARCHAR2
1002 ,p_s_start_date DATE
1003 ,p_start_date DATE) is
1004
1005 l_assignment_type VARCHAR2(1);
1006 l_earlier_date DATE;
1007 l_later_date DATE;
1008 begin
1009 l_assignment_type:='A';
1010 l_later_date:=p_start_date;
1011 l_earlier_date:=p_s_start_date;
1012 hr_utility.set_location('hr_date_chk.check_apl_ref_int',1);
1013 --
1014 if p_start_date > p_s_start_date then
1015 --
1016 hr_date_chk.check_for_ass_st_chg(p_person_id
1017 ,l_earlier_date
1018 ,l_later_date
1019 ,l_assignment_type
1020 ,p_s_start_date);
1021 --
1022 hr_date_chk.check_people_changes(p_person_id
1023 ,l_earlier_date
1024 ,l_later_date
1025 ,p_s_start_date);
1026 --
1027 hr_date_chk.check_for_ass_chg(p_person_id
1028 ,l_earlier_date
1029 ,l_later_date
1030 ,l_assignment_type
1031 ,p_s_start_date
1032 ,p_start_date);
1033 --
1034 else
1035 --
1036 l_later_date:=p_s_start_date;
1037 l_earlier_date:=p_start_date;
1038 --
1039 hr_date_chk.check_people_changes(p_person_id
1040 ,l_earlier_date
1041 ,l_later_date
1042 ,p_s_start_date);
1043 --
1044 hr_date_chk.check_for_ass_chg(p_person_id
1045 ,l_earlier_date
1046 ,l_later_date
1047 ,l_assignment_type
1048 ,p_s_start_date
1049 ,p_start_date);
1050 --
1051 end if;
1052 hr_date_chk.lock_row(p_person_id =>p_person_id
1053 ,p_person_type =>l_assignment_type);
1054 exception
1055 when others then raise;
1056 end;
1057 --
1058 ------------------------- BEGIN : update_appl_records ------------------------
1059 procedure update_appl_records(p_person_id NUMBER
1060 ,p_start_date DATE
1061 ,p_s_start_date DATE
1062 ,p_user_id NUMBER
1063 ,p_login_id NUMBER) is
1064 begin
1065 -- Update the addresses that start at the old hire date
1066 -- Providing that the addresses end date is either equal to
1067 -- the new start date or greater than it.
1068 --
1069 hr_utility.set_location('hr_date_chk.update_appl_records',1);
1070 update per_addresses a
1071 set a.date_from = p_start_date
1072 where a.date_from = p_s_start_date
1073 and nvl(a.date_to,p_start_date) >= p_start_date
1074 and a.person_id = p_person_id;
1075 --
1076 --
1077 hr_utility.set_location('hr_date_chk.update_appl_records',2);
1078 update per_assignments_f a
1079 set a.effective_start_date = p_start_date,
1080 a.last_update_date = sysdate,
1081 a.last_updated_by = p_user_id,
1082 a.last_update_login = p_login_id
1083 where a.effective_start_date = p_s_start_date
1084 and a.assignment_type = 'A'
1085 and a.person_id = p_person_id
1086 and EXISTS -- #4894555
1087 (select a2.assignment_id
1088 from per_assignments_f a2
1089 where a2.assignment_id = a.assignment_id
1090 and a2.assignment_type = 'A'
1091 group by a2.assignment_id
1092 having max(a2.effective_start_date)= a.effective_start_date);
1093
1094 --
1095 if sql%rowcount <1 then
1096 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1097 hr_utility.set_message_token('TABLE','PER_ALL_ASSIGNMENTS_F');
1098 hr_utility.raise_error;
1099 end if;
1100 hr_utility.set_location('hr_date_chk.update_appl_records',3);
1101 --
1102 update per_people_f p
1103 set p.start_date =decode(p.start_date,p_s_start_date, p_start_date,
1104 p.start_date),
1105 p.effective_start_date =decode(p.effective_start_date,
1106 p_s_start_date, p_start_date, p.effective_start_date),
1107 p.effective_end_date =decode(p.effective_end_date,
1108 p_s_start_date - 1, p_start_date - 1, p.effective_end_date),
1109 p.last_update_date = sysdate,
1110 p.last_updated_by = p_user_id,
1111 p.last_update_login = p_login_id
1112 where p.person_id = p_person_id;
1113 --
1114 if sql%rowcount <1 then
1115 hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
1116 hr_utility.set_message_token('TABLE','PER_ALL_PEOPLE_F');
1117 hr_utility.raise_error;
1118 end if;
1119 --
1120 -- keep contracts in synch with PER and ASG :
1121 hr_contract_api.maintain_contracts
1122 (p_person_id
1123 ,p_start_date
1124 ,p_s_start_date);
1125 --
1126 end;
1127 end hr_date_chk;
1128
[Home] [Help]