[Home] [Help]
PACKAGE BODY: APPS.PER_PERIODS_OF_SERVICE_PKG_V2
Source
1 PACKAGE BODY PER_PERIODS_OF_SERVICE_PKG_V2 AS
2 /* $Header: pepds02t.pkb 120.1 2006/05/08 08:44:47 lsilveir noship $ */
3 --
4 -- flemonni
5 -- hire date changes bug # 625423
6 ----------------------------------------------------------------------------
7 --
8 -- hire date changes flemonni bug # 625423
9 --
10 TYPE Age_dates
11 IS RECORD
12 ( min_date date
13 , max_date date
14 );
15 --
16 g_b2b_allowed BOOLEAN;
17 g_person_type_changes_exist BOOLEAN;
18
19 l_start_of_time date := hr_general.start_of_time;
20 l_end_of_time date := hr_general.end_of_time;
21 ----------------------------------------------------------------------------
22 -------------------------------------------------------------------------------
23 -- FLEMONNI
24 -- hire date changes bug #625423
25 -------------------------------------------------------------------------------
26 FUNCTION Get_Max_Last_Process_date
27 ( p_person_id IN NUMBER
28 )
29 RETURN DATE
30 IS
31 --
32 l_date DATE;
33 l_proc VARCHAR2 (100) :=
34 'per_periods_of_service_pkg_v2.Get_Max_Last_Process_date';
35 --
36 CURSOR cur_get_max_date (p_person_id NUMBER)
37 IS
38 SELECT max(final_process_date)
39 FROM per_periods_of_service
40 WHERE person_id = p_person_id;
41 --
42 BEGIN
43 hr_utility.set_location('Entering ' || l_proc, 10);
44 OPEN cur_get_max_date (p_person_id => p_person_id);
45 FETCH cur_get_max_date INTO l_date;
46 IF cur_get_max_date%NOTFOUND THEN
47 CLOSE cur_get_max_date;
48 l_date := NULL;
49 ELSE
50 CLOSE cur_get_max_date;
51 END IF;
52 --
53 RETURN l_date;
54 END Get_Max_Last_Process_date;
55 -------------------------------------------------------------------------------
56 FUNCTION Get_Current_PDS_Record
57 ( p_person_id IN per_people_f.person_id%TYPE
58 )
59 RETURN per_periods_of_service%ROWTYPE
60 IS
61 l_record per_periods_of_service%ROWTYPE;
62 --
63 CURSOR csr_pds_status
64 ( p_person_id per_people_f.person_id%TYPE
65 )
66 IS
67 SELECT *
68 FROM per_periods_of_service
69 WHERE period_of_service_id =
70 ( SELECT max(period_of_service_id)
71 FROM per_periods_of_service
72 WHERE person_id = p_person_id
73 );
74 BEGIN
75 OPEN csr_pds_status
76 ( p_person_id => p_person_id
77 );
78 FETCH csr_pds_status INTO l_record;
79 IF csr_pds_status%NOTFOUND THEN
80 CLOSE csr_pds_status;
81 ELSE
82 CLOSE csr_pds_status;
83 END IF;
84 --
85 RETURN l_record;
86 END Get_Current_PDS_Record;
87 -------------------------------------------------------------------------------
88 FUNCTION Get_Current_Open_PDS_id
89 ( p_person_id IN per_people_f.person_id%TYPE
90 )
91 RETURN NUMBER
92 IS
93 l_record per_periods_of_service%ROWTYPE;
94 BEGIN
95 l_record := Get_Current_PDS_Record
96 ( p_person_id => p_person_id);
97 --
98 IF l_record.last_standard_process_date IS NULL THEN
99 RETURN l_record.period_of_service_id;
100 ELSE
101 RETURN NULL;
102 END IF;
103 END Get_Current_Open_PDS_id;
104 -------------------------------------------------------------------------------
105 FUNCTION Is_Max_PDS_Not_Closed
106 ( p_person_id IN per_people_f.person_id%TYPE
107 )
108 RETURN BOOLEAN
109 IS
110 --
111 l_boolean BOOLEAN := FALSE;
112 l_record per_periods_of_service%ROWTYPE;
113 BEGIN
114 l_record := Get_Current_PDS_Record
115 ( p_person_id => p_person_id);
116
117 IF l_record.final_process_date IS NULL THEN
118 l_boolean := TRUE;
119 ELSE
120 NULL;
121 END IF;
122 --
123 RETURN l_boolean;
124 END Is_Max_PDS_Not_Closed;
125 -------------------------------------------------------------------------------
126 FUNCTION Get_Person_Age_Min_Max_Dates
127 ( p_person_id IN per_all_people_f.person_id%TYPE
128 , p_session_date IN DATE
129 , p_dob IN DATE
130 , p_business_group_id IN NUMBER
131 )
132 RETURN Age_dates
133 IS
134 l_dob DATE;
135 l_business_group_id NUMBER;
136 l_record Age_dates;
137 l_min NUMBER;
138 l_max NUMBER;
139 l_proc VARCHAR2 (100) :=
140 'per_periods_of_service_pkg_v2.Get_Person_Age_Min_Max_Dates';
141 --
142 CURSOR csr_dob
143 ( p_person_id per_all_people_f.person_id%TYPE
144 , p_session_date DATE)
145 IS
146 SELECT date_of_birth, business_group_id
147 FROM per_people_f ppf
148 WHERE ppf.person_id = p_person_id
149 AND p_session_date between effective_start_date and effective_end_date;
150 --
151 BEGIN
152 hr_utility.set_location('Entering ' || l_proc, 10);
153 --
154 IF p_dob IS NULL THEN
155 -- get data
156 hr_utility.set_location(l_proc, 20);
157 OPEN csr_dob
158 ( p_person_id => p_person_id
159 , p_session_date => p_session_date);
160 FETCH csr_dob INTO l_dob, l_business_group_id;
161 CLOSE csr_dob;
162 ELSE
163 hr_utility.set_location(l_proc, 30);
164 -- use parameters; dob may be supplied even if person_id is null
165 l_dob := p_dob;
166 l_business_group_id := p_business_group_id;
167 END IF;
168 --
169 hr_utility.set_location(l_proc, 40);
170 hr_utility.set_location(' using dob ' || to_char(l_dob, 'DD-MON-RRRR'), 41);
171 hr_utility.set_location(' using bgid ' || to_char(l_business_group_id), 42);
172 --
173 per_people3_pkg.get_legislative_ages
174 ( p_business_group_id => l_business_group_id
175 , p_minimum_age => l_min
176 , p_maximum_age => l_max);
177 --
178 -- 887747: This processing is now not used for an error on the hire date
179 -- but as a warning on commit_record processing
180 -- l_record.min_date := nvl(add_months(l_dob, l_min * 12), l_start_of_time);
181 -- l_record.max_date := nvl(add_months(l_dob, l_max * 12), l_end_of_time);
182 l_record.min_date := l_start_of_time;
183 l_record.max_date := l_end_of_time;
184 RETURN l_record;
185 END Get_Person_Age_Min_Max_Dates;
186 -------------------------------------------------------------------------------
187 FUNCTION Get_atd
188 ( p_person_id IN per_all_people_f.person_id%type
189 , p_final_process_date IN DATE
190 )
191 RETURN date
192 IS
193 l_date DATE := null;
194 l_proc VARCHAR2 (100) := 'per_periods_of_service_pkg_v2.Get_atd';
195 --
196 CURSOR csr_atd
197 ( p_person_id per_all_people_f.person_id%type
198 , p_final_process_date date)
199 IS
200 SELECT actual_termination_date
201 FROM per_periods_of_service
202 WHERE person_id = p_person_id
203 AND final_process_date = p_final_process_date;
204 BEGIN
205 hr_utility.set_location('Entering ' || l_proc, 10);
206
207 OPEN csr_atd
208 ( p_person_id => p_person_id
209 , p_final_process_date => p_final_process_date);
210 FETCH csr_atd INTO l_date;
211 CLOSE csr_atd;
212 --
213 RETURN l_date;
214 END Get_atd;
215 -------------------------------------------------------------------------------
216 FUNCTION Get_Current_PDS_Start_Date
217 ( p_person_id IN per_all_people_f.person_id%TYPE
218 , p_type IN VARCHAR2 DEFAULT NULL
219 )
220 RETURN DATE
221 IS
222 l_date DATE;
223 l_proc VARCHAR2 (100) :=
224 'per_periods_of_service_pkg_v2.Get_Current_PDS_Start_Date';
225 --
226 CURSOR csr_pds_start_date
227 (p_person_id per_all_people_f.person_id%TYPE)
228 IS
229 SELECT max(date_start)
230 FROM per_periods_of_service
231 WHERE person_id = p_person_id
232 AND final_process_date is null;
233
234 CURSOR csr_pds_start_date_nn
235 (p_person_id per_all_people_f.person_id%TYPE)
236 IS
237 SELECT max(date_start)
238 FROM per_periods_of_service
239 WHERE person_id = p_person_id;
240
241 BEGIN
242 hr_utility.set_location('Entering ' || l_proc, 10);
243
244 IF p_type IS NULL THEN
245 hr_utility.set_location(l_proc, 20);
246 -- look for a current employment only
247
248 OPEN csr_pds_start_date
249 ( p_person_id => p_person_id);
250 FETCH csr_pds_start_date INTO l_date;
251 CLOSE csr_pds_start_date;
252 ELSE
253 hr_utility.set_location(l_proc, 30);
254 -- look for the most recent hire date current or not
255 OPEN csr_pds_start_date_nn
256 ( p_person_id => p_person_id);
257 FETCH csr_pds_start_date_nn INTO l_date;
258 CLOSE csr_pds_start_date_nn;
259 END IF;
260 --
261 RETURN l_date;
262 END Get_Current_PDS_Start_Date;
263 -------------------------------------------------------------------------------
264 FUNCTION Get_Max_Asg_Hire_Date
265 ( p_person_id IN per_all_people_f.person_id%TYPE
266 , p_check_date IN date
267 )
268 RETURN date
269 IS
270 l_date DATE := NULL;
271 l_proc VARCHAR2 (100) :=
272 'per_periods_of_service_pkg_v2.Get_Max_Asg_Hire_Date';
273 --
274 CURSOR Get_Max_Asg_Hire_Date
275 ( p_person_id per_all_people_f.person_id%TYPE
276 , p_check_date date)
277 IS
278 SELECT Min(effective_start_date) + 1
279 FROM per_all_assignments_f
280 WHERE person_id = p_person_id
281 AND effective_start_date > p_check_date;
282 BEGIN
283 hr_utility.set_location('Entering ' || l_proc, 10);
284 OPEN Get_Max_Asg_Hire_Date
285 ( p_person_id => p_person_id
286 , p_check_date =>p_check_date);
287 FETCH Get_Max_Asg_Hire_Date INTO l_date;
288 CLOSE Get_Max_Asg_Hire_Date;
289 --
290 RETURN l_date;
291 END Get_Max_Asg_Hire_Date;
292 -------------------------------------------------------------------------------
293 FUNCTION Get_Min_Asg_Hire_Date
294 ( p_person_id IN per_all_people_f.person_id%TYPE
295 , p_check_date IN date
296 )
297 RETURN date
298 IS
299 l_date DATE := NULL;
300 l_proc VARCHAR2 (100) :=
301 'per_periods_of_service_pkg_v2.Get_Min_Asg_Hire_Date';
302 --
303 CURSOR Get_Min_Asg_Hire_Date
304 ( p_person_id per_all_people_f.person_id%TYPE
305 , p_check_date date)
306 IS
307 SELECT Max(effective_start_date) + 1
308 FROM per_all_assignments_f
309 WHERE person_id = p_person_id
310 AND effective_start_date < p_check_date;
311 BEGIN
312 hr_utility.set_location('Entering ' || l_proc, 10);
313 OPEN Get_Min_Asg_Hire_Date
314 ( p_person_id => p_person_id
315 , p_check_date => p_check_date);
316 FETCH Get_Min_Asg_Hire_Date INTO l_date;
317 CLOSE Get_Min_Asg_Hire_Date;
318 --
319 RETURN l_date;
320 END Get_Min_Asg_Hire_Date;
321 -------------------------------------------------------------------------------
322 FUNCTION Get_Min_Person_End_Date
323 ( p_person_id IN per_all_people_f.person_id%TYPE
324 , p_date_from IN DATE
325 , p_date_to IN DATE
326 )
327 RETURN DATE
328 IS
329 l_date DATE;
330 l_proc VARCHAR2 (100) :=
331 'per_periods_of_service_pkg_v2.Get_Min_Person_End_Date';
332 --
333 CURSOR csr_get_end_date
334 ( p_person_id per_all_people_f.person_id%TYPE
335 , p_date_from DATE
336 , p_date_to DATE)
337 IS
338 SELECT min(effective_end_date)
339 FROM per_all_people_f
340 WHERE person_id = p_person_id
341 AND effective_end_date between p_date_from + 1 and p_date_to -1;
342 BEGIN
343 hr_utility.set_location('Entering ' || l_proc, 10);
344 OPEN csr_get_end_date
345 ( p_person_id => p_person_id
346 , p_date_from => p_date_from
347 , p_date_to => p_date_to);
348 FETCH csr_get_end_date INTO l_date;
349 CLOSE csr_get_end_date;
350 --
351 RETURN l_date;
352 END Get_Min_Person_End_Date;
353 -------------------------------------------------------------------------------
354 FUNCTION Get_Max_Person_Start_Date
355 ( p_person_id IN per_all_people_f.person_id%TYPE
356 , p_date_from IN DATE
357 , p_date_to IN DATE
358 )
359 RETURN DATE
360 IS
361 l_date DATE;
362 l_proc VARCHAR2 (100) :=
363 'per_periods_of_service_pkg_v2.Get_Max_Person_Start_Date';
364 --
365 CURSOR csr_get_start_date
366 ( p_person_id per_all_people_f.person_id%TYPE
367 , p_date_from DATE
368 , p_date_to DATE)
369 IS
370 SELECT max(effective_start_date) +1
371 FROM per_all_people_f
372 WHERE person_id = p_person_id
373 AND effective_start_date between p_date_from + 1 and p_date_to -1;
374 BEGIN
375 hr_utility.set_location('Entering ' || l_proc, 10);
376 OPEN csr_get_start_date
377 ( p_person_id => p_person_id
378 , p_date_from => p_date_from
379 , p_date_to => p_date_to);
380 FETCH csr_get_start_date INTO l_date;
381 CLOSE csr_get_start_date;
382 --
383 RETURN l_date;
384 END Get_Max_Person_Start_Date;
385 -------------------------------------------------------------------------------
386 FUNCTION Is_Back_to_Back_Allowed
387 ( p_pds_hd_bb IN DATE
388 , p_person_id IN per_all_people_f.person_id%TYPE
389 , p_current_hire_date IN DATE)
390 RETURN BOOLEAN
391 IS
392 l_boolean BOOLEAN;
393 l_effective_start_date DATE;
394 l_proc VARCHAR2 (100) :=
395 'per_periods_of_service_pkg_v2.
396 Is_Back_to_Back_Allowed';
397 --
398 CURSOR csr_person_type_change
399 ( p_person_id per_all_people_f.person_id%TYPE
400 , p_current_hire_date DATE)
401 IS
402 SELECT effective_start_date
403 FROM per_people_f ppf, per_person_types ppt
404 WHERE ppf.person_id = p_person_id
405 AND ppf.person_type_id = ppt.person_type_id
406 AND ppf.effective_start_date > p_current_hire_date;
407 BEGIN
408 hr_utility.set_location('Entering ' || l_proc, 10);
409 IF p_pds_hd_bb = hr_general.start_of_time THEN
410 hr_utility.set_location(l_proc, 20);
411 l_boolean := FALSE;
412 ELSE
413 hr_utility.set_location(l_proc, 30);
414 OPEN csr_person_type_change
415 ( p_person_id => p_person_id
416 , p_current_hire_date => p_current_hire_date);
417 FETCH csr_person_type_change INTO l_effective_start_date;
418 --
419 IF csr_person_type_change%FOUND THEN
420 CLOSE csr_person_type_change;
421 l_boolean := TRUE;
422 ELSE
423 CLOSE csr_person_type_change;
424 l_boolean := FALSE;
425 END IF;
426 END IF;
427 --
428 RETURN nvl(l_boolean, false);
429 END Is_Back_to_Back_Allowed;
430 -------------------------------------------------------------------------------
431 FUNCTION Set_Date_Using_FPD
432 ( p_person_id IN per_all_people_f.person_id%TYPE
433 , p_final_process_date date
434 )
435 RETURN DATE
436 IS
437 l_date DATE;
438 l_actual_termination_date DATE;
439 l_count NUMBER;
440 l_person_type_id NUMBER;
441 l_pt_changes_exist BOOLEAN := FALSE;
442 l_proc VARCHAR2 (100) :=
443 'per_periods_of_service_pkg_v2.Set_Date_Using_FPD';
444 --
445 -- + 2
446 -- there must be at least an ex_empp_ record but no other changes
447 --
448 CURSOR csr_per_row_count
449 IS
450 SELECT COUNT (*), person_type_id
451 FROM per_people_f
452 WHERE person_id = p_person_id
453 AND effective_start_date > p_final_process_date
454 GROUP BY person_type_id;
455 BEGIN
456 hr_utility.set_location('Entering ' || l_proc, 10);
457 IF p_final_process_date IS NULL THEN
458 hr_utility.set_location(l_proc, 20);
459 l_date := NULL;
460 g_b2b_allowed := false;
461 ELSE
462 OPEN csr_per_row_count;
463 LOOP
464 FETCH csr_per_row_count INTO l_count, l_person_type_id;
465 EXIT WHEN csr_per_row_count%NOTFOUND;
466 hr_utility.set_location(l_proc, 30);
467 IF csr_per_row_count%ROWCOUNT > 1 THEN
468 hr_utility.set_location(' person type changes exist ', 31);
469 l_pt_changes_exist := TRUE;
470 EXIT;
471 ELSE
472 NULL;
473 END IF;
474 END LOOP;
475 --
476 CLOSE csr_per_row_count;
477 --
478 -- if rowcount = 1 then = emp or exemp record and b2b allowed with hd = fpd + 1
479 -- but if > 1 then person type changes exist so cannot create a b2b
480 -- employment
481 --
482 hr_utility.set_location(l_proc, 40);
483 l_actual_termination_date :=
484 get_atd
485 ( p_person_id => p_person_id
486 , p_final_process_date => p_final_process_date
487 );
488 --
489 hr_utility.set_location(' l_atd ' || to_char(l_actual_termination_date, 'DD-MON-RRRR'), 41);
490 hr_utility.set_location(' p_fpd ' || to_char(p_final_process_date, 'DD-MON-RRRR'), 42);
491 --
492 -- flemonni changed
493 -- always = to fpd + 1
494 --
495 l_date := p_final_process_date + 1;
496 IF l_actual_termination_date = p_final_process_date
497 AND NOT l_pt_changes_exist THEN
498 -- l_date := p_final_process_date + 1;
499 g_b2b_allowed := TRUE;
500 ELSE
501 -- l_date := p_final_process_date + 2;
502 g_b2b_allowed := FALSE;
503 END IF;
504 END IF;
505 --
506 g_person_type_changes_exist := l_pt_changes_exist;
507
508 --
509 RETURN l_date;
510 END Set_Date_Using_FPD;
511 -------------------------------------------------------------------------------
512 FUNCTION Get_Min_Asg_Accepted
513 ( p_person_id IN per_all_people_f.person_id%TYPE
514 )
515 RETURN DATE
516 IS
517 l_date DATE;
518 l_proc VARCHAR2 (100) :=
519 'per_periods_of_service_pkg_v2.Get_Min_Asg_Accepted';
520 --
521 CURSOR csr_Get_Accepted_Asg
522 (p_person_id per_all_people_f.person_id%TYPE)
523 IS
524 SELECT min(effective_start_date) + 1
525 FROM per_all_assignments_f paf
526 , per_assignment_status_types past
527 WHERE person_id = p_person_id
528 AND paf.assignment_status_type_id = past.assignment_status_type_id
529 AND past.per_system_status = 'ACCEPTED';
530 BEGIN
531 hr_utility.set_location('Entering ' || l_proc, 10);
532 OPEN csr_Get_Accepted_Asg
533 ( p_person_id => p_person_id);
534 FETCH csr_Get_Accepted_Asg INTO l_date;
535 CLOSE csr_Get_Accepted_Asg;
536 --
537 RETURN l_date;
538 END Get_Min_Asg_Accepted;
539 -------------------------------------------------------------------------------
540 PROCEDURE Get_Valid_Hire_Dates
541 ( p_person_id IN per_all_people_f.person_id%TYPE
542 , p_session_date IN DATE
543 , p_dob IN DATE
544 , p_business_group_id IN NUMBER
545 , p_min_date OUT NOCOPY DATE
546 , p_max_date OUT NOCOPY DATE
547 , p_b2b_allowed OUT NOCOPY BOOLEAN
548 , p_pds_not_terminated OUT NOCOPY BOOLEAN
549 )
550 IS
551 l_hd_min DATE;
552 l_hd_max DATE;
553 l_age_dates Age_dates;
554 l_pds_hd DATE;
555 l_fpd DATE;
556 l_atd DATE;
557 l_mod_fpd DATE;
558 l_most_recent_pds DATE;
559 l_min_asg_date DATE;
560 l_max_asg_date DATE;
561 l_min_asg_accept DATE;
562 l_max DATE;
563 l_min DATE;
564 l_min_years NUMBER;
565 l_max_years NUMBER;
566 l_fail_step NUMBER;
567 l_proc VARCHAR2 (100) :=
568 'per_periods_of_service_pkg_v2.Get_valid_hire_dates';
569 BEGIN
570 hr_utility.set_location('Entering ' || l_proc, 10);
571 --
572 -- nb: hd = hire date
573 -- fpd = final process date (last payroll run)
574 -- atd = actual termination date (last physical day of contract)
575 -- pds = period of service
576 -- l_fail_step to provide the step value for all procedure fail
577 -- if proc fails to retrieve data
578 --
579 -- GET AGE DATES: even if person_id is null, can use business_group_id
580 -- to retrieve age dates; if age dates are null then set automatically to
581 -- start / end of time
582 --
583 -- person age dates
584 --
585 l_fail_step := 10;
586
587 l_age_dates := Get_Person_Age_Min_Max_Dates
588 ( p_person_id => p_person_id
589 , p_session_date => p_session_date
590 , p_dob => p_dob
591 , p_business_group_id => p_business_group_id
592 );
593
594 hr_utility.set_location(' min age date ' || to_char(l_age_dates.min_date, 'DD-MON-RRRR') , 11);
595 hr_utility.set_location(' max age date ' || to_char(l_age_dates.max_date, 'DD-MON-RRRR') , 12);
596
597 IF p_person_id IS NULL THEN
598 hr_utility.set_location(l_proc, 20);
599 --
600 -- NO PERSON RECORD EXISTS
601 --
602 l_hd_min := l_age_dates.min_date; -- l_start_of_time;
603 l_hd_max := l_age_dates.max_date; -- l_end_of_time;
604 ELSE
605 hr_utility.set_location(l_proc, 30);
606 --
607 -- period of service dates
608 --
609 hr_utility.set_location(l_proc, 50);
610 l_fail_step := 20;
611 --
612 l_pds_hd := Get_Current_PDS_Start_Date (p_person_id => p_person_id);
613 hr_utility.set_location(' pds hd ' || to_char(l_pds_hd, 'DD-MON-RRRR') , 51);
614 --
615 -- final process date
616 --
617 hr_utility.set_location(l_proc, 60);
618 l_fail_step := 30;
619 --
620 l_fpd := Get_Max_Last_process_Date (p_person_id);
621 l_atd := get_atd
622 ( p_person_id => p_person_id
623 , p_final_process_date => l_fpd);
624 hr_utility.set_location(' fpd ' || to_char(l_fpd, 'DD-MON-RRRR') , 61);
625 hr_utility.set_location(' atd ' || to_char(l_atd, 'DD-MON-RRRR') , 62);
626 --
627 -- GET ASSIGNMENT DATA
628 --
629 hr_utility.set_location(l_proc, 70);
630 l_fail_step := 40;
631 --
632 -- modified fpd : i.e. whether date of new emp record is date +1 or date +2
633 --
634 l_mod_fpd := Set_Date_Using_FPD
635 ( p_person_id => p_person_id
636 , p_final_process_date => l_fpd);
637 --
638 hr_utility.set_location(' mod fpd ' || to_char(l_mod_fpd, 'DD-MON-RRRR') , 71);
639 --
640 -- asg : if mod_fpd null, pds hd [open emp] null and current hd null then
641 -- does not matter that asg check retrieves null
642 --
643 l_most_recent_pds := Get_Current_PDS_Start_Date
644 ( p_person_id => p_person_id
645 , p_type => 'recent');
646 hr_utility.set_location(' most recent pds ' || to_char(l_most_recent_pds, 'DD-MON-RRRR') , 72);
647
648 l_min_asg_date := nvl(Get_Min_Asg_Hire_Date
649 ( p_person_id => p_person_id
650 , p_check_date => GREATEST
651 (nvl
652 (l_mod_fpd
653 , l_most_recent_pds
654 )
655 , l_most_recent_pds
656 )
657 ), l_start_of_time);
658 --
659 -- if no future dated changes exist (set in set_date_using_fpd
660 -- then compare the following
661 -- otherwise may have e.g. future application accepted (empapl)
662 -- but hire date must be within boundaries of two person type changes
663 -- i.e. exemp -> emp -> empapl : valid hire date range = exemp + 1 to
664 -- empapl - 1 (presuming no attribute changes in between)
665 --
666 IF NOT g_person_type_changes_exist THEN
667 l_min_asg_date :=
668 GREATEST
669 ( l_min_asg_date
670 , NVL(Get_Min_Asg_Accepted (p_person_id => p_person_id), l_min_asg_date)
671 );
672 ELSE
673 NULL;
674 END IF;
675
676 hr_utility.set_location(' gmaa ' || to_char(Get_Min_Asg_Accepted (p_person_id => p_person_id), 'DD-MON-RRRR') , 721);
677 --
678 l_max_asg_date := nvl(Get_Max_Asg_Hire_Date
679 ( p_person_id => p_person_id
680 , p_check_date => GREATEST
681 (nvl
682 (l_mod_fpd, l_most_recent_pds)
683 , l_most_recent_pds)), l_end_of_time);
684 hr_utility.set_location(' min asg date ' || to_char(l_min_asg_date, 'DD-MON-RRRR') , 73);
685 hr_utility.set_location(' max asg date ' || to_char(l_max_asg_date, 'DD-MON-RRRR') , 74);
686 --
687 IF l_pds_hd IS NULL AND l_fpd IS NULL THEN
688 --
689 hr_utility.set_location(l_proc, 80);
690 l_fail_step := 50;
691 --
692 --
693 -- PERSON RECORD EXISTS BUT NO PERIOD OF SERVICE RECORD - APPLICANT
694 --
695 l_min_asg_accept := Get_Min_Asg_Accepted (p_person_id => p_person_id);
696 hr_utility.set_location(' min asg accepted date ' || to_char(l_min_asg_accept, 'DD-MON-RRRR') , 81);
697 --
698 IF l_min_asg_accept IS NULL THEN
699 --
700 hr_utility.set_location(l_proc, 90);
701 --
702 --
703 -- NO HIRE DATE ALLOWED
704 --
705 l_fail_step := 60;
706 l_hd_min := NULL;
707 l_hd_max := NULL;
708 --
709 hr_utility.set_location(' hd min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 91);
710 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 92);
711 ELSE
712 --
713 hr_utility.set_location(l_proc, 100);
714 --
715 -- HIRE DATE ALLOWED ON GMAA (i.e. Accepted + 1);
716 --
717 l_fail_step := 70;
718 l_hd_min := l_min_asg_accept;
719 --
720 l_hd_max := LEAST
721 ( l_end_of_time
722 , l_age_dates.max_date);
723 hr_utility.set_location(' l_hd_max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 101);
724
725 --
726 -- but must be lower than maximum assignment change
727 --
728 l_hd_max := LEAST
729 ( l_hd_max
730 , l_max_asg_date);
731 hr_utility.set_location(' l_hd_max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 102);
732 --
733 -- but must be higher than any person attribute change
734 --
735 l_min := nvl(Get_Max_Person_Start_Date
736 ( p_person_id => p_person_id
737 , p_date_from => l_min_asg_accept
738 , p_date_to => l_end_of_time), l_min_asg_accept);
739
740 hr_utility.set_location(' l_min ' || to_char(l_min, 'DD-MON-RRRR') , 103);
741 --
742 l_hd_min := GREATEST
743 ( l_hd_min
744 , l_min
745 );
746 --
747 hr_utility.set_location(' hd min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 104);
748 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 105);
749 END IF;
750 ELSE
751 --
752 hr_utility.set_location(l_proc, 110);
753 --
754 -- AT LEAST ONE EMP RECORD AND THEREFORE PDS START DATE EXISTS
755 --
756 l_fail_step := 80;
757 l_hd_min := GREATEST
758 ( l_start_of_time
759 , l_age_dates.min_date
760 , nvl(l_mod_fpd, l_start_of_time));
761 --
762 hr_utility.set_location(' hd min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 111);
763
764 IF l_fpd IS NULL THEN
765 --
766 hr_utility.set_location(l_proc, 120);
767 --
768 -- FIRST PDS
769 --
770 -- chg l_pds_hd to most_recent_pds
771 --
772 l_fail_step := 90;
773 l_max := nvl(Get_Min_Person_End_Date
774 ( p_person_id => p_person_id
775 , p_date_from => nvl(l_most_recent_pds, l_hd_min)
776 , p_date_to => l_end_of_time), l_end_of_time);
777 --
778 hr_utility.set_location(' l_max ' || to_char(l_max, 'DD-MON-RRRR') , 121);
779 --
780 l_hd_max := LEAST
781 ( l_end_of_time
782 , l_age_dates.max_date
783 , l_max);
784
785 l_hd_max := LEAST
786 ( l_hd_max
787 , l_max_asg_date);
788 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 122);
789 /*
790 --
791 -- CHECK FUTURE ATTRIBUTE CHANGES
792 --
793 l_min := nvl(Get_Max_Person_Start_Date
794 ( p_person_id => p_person_id
795 , p_date_from => nvl(l_most_recent_pds, l_start_of_time)
796 , p_date_to => l_end_of_time), l_start_of_time);
797 --
798 l_hd_min := l_min;
799 */
800 --
801 hr_utility.set_location(' hd min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 123);
802 --
803 ELSE
804 --
805 hr_utility.set_location(l_proc, 130);
806 --
807 -- NOT IN FIRST PDS
808 --
809 l_fail_step := 100;
810 l_min := Get_Max_Person_Start_Date
811 ( p_person_id => p_person_id
812 , p_date_from => l_atd + 1
813 , p_date_to => nvl(l_pds_hd, l_end_of_time));
814 --
815 hr_utility.set_location(' l_min ' || to_char(l_min, 'DD-MON-RRRR') , 131);
816 --
817 IF l_min IS NULL THEN
818 --
819 hr_utility.set_location(l_proc, 140);
820 --
821 -- NO INTERFERING ATTRIBUTE CHANGES IN THE PERSON RECORD
822 --
823 l_fail_step := 110;
824 l_hd_max := LEAST
825 ( l_end_of_time
826 , l_age_dates.max_date);
827 --
828 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 141);
829 ELSE
830 --
831 hr_utility.set_location(l_proc, 150);
832 --
833 -- ATTRIBUTES EXIST
834 --
835 l_fail_step := 120;
836 l_hd_min := l_min;
837 g_b2b_allowed := FALSE;
838 --
839 IF l_pds_hd IS NULL THEN
840 --
841 hr_utility.set_location(l_proc, 160);
842 --
843 l_hd_max := LEAST
844 ( l_end_of_time
845 , l_age_dates.max_date);
846 --
847 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 161);
848 ELSE
849 --
850 hr_utility.set_location(l_proc, 170);
851 --
852 l_max := nvl(Get_Min_Person_End_Date
853 ( p_person_id => p_person_id
854 , p_date_from => l_pds_hd
855 , p_date_to => l_end_of_time), l_end_of_time);
856 --
857 hr_utility.set_location(' l_max ' || to_char(l_max, 'DD-MON-RRRR') , 171);
858 --
859 l_hd_max := LEAST
860 ( l_end_of_time
861 , l_age_dates.max_date
862 , l_max);
863 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 172);
864 END IF;
865 END IF;
866 END IF;
867 --
868 hr_utility.set_location(l_proc, 180);
869 --
870 hr_utility.set_location(' l_hd_max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 181);
871 hr_utility.set_location(' l_max_asg_date ' || to_char(l_max_asg_date, 'DD-MON-RRRR') , 182);
872
873 l_hd_max := LEAST
874 ( l_hd_max
875 , l_max_asg_date);
876 l_hd_min := GREATEST
877 ( l_hd_min
878 , l_min_asg_date);
879 --
880 hr_utility.set_location(' hd min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 183);
881 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 184);
882 --
883 IF l_hd_min >= l_hd_max THEN
884 --
885 -- there are no interfering records above the l_hd_max;
886 -- Therefore, use eot/age date as the max
887 --
888 l_hd_max :=LEAST
889 ( l_end_of_time
890 , l_age_dates.max_date);
891 ELSE
892 NULL;
893 END IF;
894 --
895 hr_utility.set_location(' hd min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 185);
896 hr_utility.set_location(' hd max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 186);
897
898 --
899 -- but may miss e.g. ex_emp changes; need to ensure that pers / asg changes
900 -- also compared to pds changes
901 --
902 /*
903 l_hd_min := GREATEST
904 ( l_hd_min
905 , l_mod_fpd);
906 */
907 END IF;
908 END IF;
909 --
910 -- assign out parameters
911 --
912 l_fail_step := 130;
913 p_min_date := l_hd_min;
914 p_max_date := l_hd_max;
915 --
916 IF g_b2b_allowed THEN
917 --
918 hr_utility.set_location(l_proc, 190);
919 --
920 p_b2b_allowed := TRUE;
921 ELSE
922 --
923 hr_utility.set_location(l_proc, 200);
924 --
925 p_b2b_allowed := FALSE;
926 END IF;
927 --
928 p_pds_not_terminated := Is_Max_PDS_Not_Closed
929 ( p_person_id => p_person_id);
930
931 hr_utility.set_location(' OUT min ' || to_char(l_hd_min, 'DD-MON-RRRR') , 201);
932 hr_utility.set_location(' OUT max ' || to_char(l_hd_max, 'DD-MON-RRRR') , 202);
933 hr_utility.set_location('Leaving ' || l_proc, 220);
934 --
935 EXCEPTION
936 WHEN OTHERS THEN
937 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
938 fnd_message.set_token('PROCEDURE','Per_period_of_service_pkg.Get_valid_Hire_dates',false);
939 fnd_message.set_token('STEP', l_fail_step);
940 RAISE;
941 END Get_Valid_Hire_Dates;
942 -------------------------------------------------------------------------------
943 FUNCTION IsBackToBackContract
944 ( p_person_id IN per_people_f.person_id%TYPE
945 , p_hire_date_of_current_pds IN DATE
946 )
947 RETURN BOOLEAN
948 IS
949 --
950 l_fpd DATE;
951 l_atd DATE;
952 l_boolean BOOLEAN;
953 l_proc VARCHAR2 (100) :=
954 'per_periods_of_service_pkg_v2.IsBackToBackContract';
955 --
956 BEGIN
957 hr_utility.set_location('Entering ' || l_proc, 10);
958 l_fpd := Get_Max_Last_process_Date (p_person_id);
959 l_atd := get_atd
960 ( p_person_id => p_person_id
961 , p_final_process_date => l_fpd);
962 --
963 -- 115.3 (START)
964 --
965 --IF l_fpd = l_atd
966 --AND p_hire_date_of_current_pds = l_fpd + 1
967 IF l_fpd IS NOT NULL
968 AND p_hire_date_of_current_pds = l_atd + 1
969 --
970 -- 115.3 (END)
971 --
972 THEN
973 RETURN TRUE;
974 ELSE
975 RETURN FALSE;
976 END IF;
977 END IsBackToBackContract;
978 --
979 END PER_PERIODS_OF_SERVICE_PKG_V2;