1 Package Body pqp_service_history_calc_pkg as
2 /* $Header: pqshpcal.pkb 120.0 2005/05/29 02:11:33 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-------------------------< calculate_period >-----------------------------|
6 -- ----------------------------------------------------------------------------
7 PROCEDURE calculate_period (p_start_date in date
8 ,p_end_date in date
9 ,p_days out nocopy number
10 ) IS
11 --
12 -- Calculate the number of days for given dates
13 --
14 l_proc VARCHAR2(60) := 'pqp_service_history_calc_pkg.calculate_period';
15 l_start_date DATE := trunc(p_start_date);
16 l_end_date DATE := trunc(p_end_date);
17 l_days NUMBER(12);
18 --
19 BEGIN
20 hr_utility.set_location('Entering: '||l_proc, 10);
21 --
22 SELECT l_end_date - l_start_date
23 INTO l_days
24 FROM dual;
25 --
26 l_days := l_days + 1;
27 p_days := l_days;
28 --
29 hr_utility.set_location('Leaving: '||l_proc, 20);
30
31
32 -- Added by tmehra for nocopy changes Feb'03
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 hr_utility.set_location('Entering excep:'||l_proc, 35);
37 p_days := 0;
38 raise;
39
40 --
41 END calculate_period;
42 --
43 -- ----------------------------------------------------------------------------
44 -- |---------------------< calculate_current_service >------------------------|
45 -- ----------------------------------------------------------------------------
46 --
47 FUNCTION calculate_current_service (p_calculation_date in date
48 ,p_assignment_id in number
49 ,p_continuous_service in varchar2
50 ) RETURN number IS
51 --
52 -- Calculate the current service period for a given assignment
53 --
54 CURSOR c_curr_serv IS
55 SELECT pps.date_start
56 FROM per_periods_of_service pps
57 ,per_all_assignments_f asg
58 WHERE pps.period_of_service_id = asg.period_of_service_id
59 AND trunc(p_calculation_date) BETWEEN trunc(asg.effective_start_date)
60 AND trunc(asg.effective_end_date)
61 AND asg.assignment_id = p_assignment_id
62 AND not exists (SELECT 1 FROM pqp_service_history_periods shp
63 WHERE trunc(pps.date_start) BETWEEN trunc(shp.start_date)
64 AND trunc(shp.end_date)
65 AND shp.assignment_id = p_assignment_id
66 AND shp.continuous_service = NVL(p_continuous_service, shp.continuous_service));
67 --
68 l_func VARCHAR2(60) := 'pqp_service_history_calc_pkg.calculate_current_service';
69 l_days NUMBER(12) := 0 ;
70 --
71 BEGIN
72 hr_utility.set_location('Entering: '||l_func, 10);
73 --
74 FOR c_curr_serv_rec IN c_curr_serv LOOP
75 --
76 -- Calculate the service period
77 --
78 calculate_period (p_start_date => c_curr_serv_rec.date_start
79 ,p_end_date => p_calculation_date
80 ,p_days => l_days
81 );
82 --
83 hr_utility.set_location(l_func, 20);
84 END LOOP;
85 --
86 return l_days;
87 --
88 hr_utility.set_location('Leaving: '||l_func, 30);
89 --
90 END calculate_current_service;
91 --
92 -- ----------------------------------------------------------------------------
93 -- |-----------------------< convert_years_to_days >--------------------------|
94 -- ----------------------------------------------------------------------------
95 --
96 PROCEDURE convert_years_to_days (p_start_date in date
97 ,p_period_years in number
98 ,p_days out nocopy number) IS
99 --
100 l_proc VARCHAR2(60) := 'pqp_service_history_calc_pkg.convert_years_to_days';
101 l_days NUMBER(12) := 0 ;
102 l_end_date DATE;
103 --
104 BEGIN
105 --
106 hr_utility.set_location('Entering: '||l_proc, 10);
107 --
108 SELECT ADD_MONTHS(p_start_date,(p_period_years * 12))
109 INTO l_end_date
110 FROM dual;
111
112 hr_utility.set_location(l_proc, 20);
113 SELECT l_end_date - p_start_date
114 INTO l_days
115 FROM dual;
116
117 p_days := l_days;
118
119 hr_utility.set_location('Leaving: '||l_proc, 30);
120 --
121 END convert_years_to_days;
122 --
123 -- ----------------------------------------------------------------------------
124 -- |-----------------------< check_overlap_curr_serv >------------------------|
125 -- ----------------------------------------------------------------------------
126 --
127 PROCEDURE check_overlap_curr_serv (p_start_date in date
128 ,p_end_date in date
129 ,p_period_years in number
130 ,p_period_days in number
131 ,p_assignment_id in number
132 ,p_calculation_date in date
133 ,p_days out nocopy number) IS
134
135 --
136 -- Check whether this service history period overlaps
137 -- with the current service period
138 --
139 CURSOR c_overlap_serv2 IS
140 SELECT NULL
141 FROM per_periods_of_service pps
142 ,per_all_assignments_f asg
143 WHERE pps.period_of_service_id = asg.period_of_service_id
144 AND trunc(pps.date_start) between trunc(p_start_date)
145 and trunc(p_end_date)
146 AND trunc(p_calculation_date) between trunc(asg.effective_start_date)
147 and trunc(asg.effective_end_date)
148 AND asg.assignment_id = p_assignment_id ;
149 --
150 l_proc VARCHAR2(60) := 'pqp_service_history_calc_pkg.check_overlap_curr_serv';
151 l_days NUMBER(12) := 0 ;
152 l_exists VARCHAR2(1);
153 --
154 BEGIN
155 hr_utility.set_location('Entering: '||l_proc, 10);
156 --
157 OPEN c_overlap_serv2;
158 FETCH c_overlap_serv2 INTO l_exists;
159 hr_utility.set_location (l_proc, 20);
160 If c_overlap_serv2%FOUND THEN
161
162 --
163 -- Reset the end_date to calculation_date
164 -- Calculate the service period
165 --
166 calculate_period (p_start_date => p_start_date
167 ,p_end_date => p_calculation_date
168 ,p_days => l_days
169 );
170 Else
171 --
172 -- Pass the end date value
173 -- Calculate the service period
174 --
175 -- Check period years or period days have a value
176 -- PS Bug 2028104 for details
177 --
178
179 If p_period_years Is Null And
180 p_period_days Is Null Then
181
182 calculate_period (p_start_date => p_start_date
183 ,p_end_date => p_end_date
184 ,p_days => l_days
185 );
186
187 Else
188
189 -- The following conversion of years to days
190 -- is incorrect as 365 days for a year will be incorrect
191 -- for leap years
192 -- See Bug 4318334 for details
193
194 -- l_days := nvl(p_period_years,0) * 365;
195 l_days := 0;
196 IF NVL(p_period_years, 0) <> 0 THEN
197 hr_utility.set_location(l_proc, 25);
198 convert_years_to_days (p_start_date => p_start_date
199 ,p_period_years => p_period_years
200 ,p_days => l_days
201 );
202 END IF; -- End if of period years is not zero...
203 l_days := l_days + nvl(p_period_days,0);
204
205
206 End If; -- End of of period check...
207
208 End If;
209 CLOSE c_overlap_serv2;
210 --
211 p_days := l_days;
212 hr_utility.set_location('Leaving: '||l_proc, 30);
213 --
214
215 -- Added by tmehra for nocopy changes Feb'03
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 hr_utility.set_location('Entering excep:'||l_proc, 35);
220 p_days := 0;
221 raise;
222
223 END check_overlap_curr_serv;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |-----------------------< calculate_service_history >----------------------|
227 -- ----------------------------------------------------------------------------
228 --
229 FUNCTION calculate_service_history (p_assignment_id in number
230 ,p_calculation_date in date
231 ) RETURN number IS
232 --
233 -- This function should query back every period of service for the given
234 -- assignment, and add them together, finally adding on the length of the
235 -- current period of service, up to and including p_calculation_date.
236 -- The result should be returned in days.
237 --
238 -- Select years and days as we need not calculate period if they have a value
239 -- PS bug # for details
240 --
241 CURSOR c_serv_hist IS
242 SELECT start_date
243 ,LEAST(trunc(end_date), trunc(p_calculation_date)) end_date
244 ,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
245 period_years, NULL) period_years
246 ,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
247 period_days, NULL) period_days
248 FROM pqp_service_history_periods shp1
249 WHERE shp1.assignment_id = p_assignment_id
250 AND trunc(shp1.start_date) <= trunc(p_calculation_date)
251 AND not exists (SELECT 1 FROM pqp_service_history_periods shp2
252 WHERE shp2.assignment_id = shp1.assignment_id
253 AND shp2.service_history_period_id <> shp1.service_history_period_id
254 AND (trunc(shp2.start_date) between trunc(shp1.start_date)
255 and trunc(shp1.end_date)
256 or trunc(shp2.end_date) between trunc(shp1.start_date)
257 and trunc(shp1.end_date)
258 or trunc(shp1.start_date) between trunc(shp2.start_date)
259 and trunc(shp2.end_date)
260 or trunc(shp1.end_date) between trunc(shp2.start_date)
261 and trunc(shp2.end_date)));
262 --
263 CURSOR c_overlap_serv IS
264 SELECT MIN(shp1.start_date) start_date
265 ,LEAST(trunc(MAX(shp1.end_date)),trunc(p_calculation_date)) end_date
266 FROM pqp_service_history_periods shp1
267 WHERE shp1.assignment_id = p_assignment_id
268 AND trunc(shp1.start_date) <= trunc(p_calculation_date)
269 AND exists (SELECT 1 FROM pqp_service_history_periods shp2
270 WHERE shp2.assignment_id = shp1.assignment_id
271 AND shp2.service_history_period_id <> shp1.service_history_period_id
272 AND (trunc(shp2.start_date) between trunc(shp1.start_date)
273 and trunc(shp1.end_date)
274 or trunc(shp2.end_date) between trunc(shp1.start_date)
275 and trunc(shp1.end_date)
276 or trunc(shp1.start_date) between trunc(shp2.start_date)
277 and trunc(shp2.end_date)
278 or trunc(shp1.end_date) between trunc(shp2.start_date)
279 and trunc(shp2.end_date)));
280 --
281 l_func VARCHAR2(60) := 'pqp_service_history_calc_pkg.calculate_service_history';
282 l_days NUMBER(12);
283 l_tot_days NUMBER(12) := 0;
284 --
285 BEGIN
286 hr_utility.set_location('Entering: '||l_func, 10);
287 --
288 FOR c_serv_hist_rec in c_serv_hist LOOP
289
290 If c_serv_hist_rec.start_date IS NOT NULL And
291 c_serv_hist_rec.end_date IS NOT NULL Then
292 --
293 -- Before calculating check whether this date
294 -- overlaps with current service period
295 --
296 hr_utility.set_location(l_func, 20);
297 check_overlap_curr_serv (p_start_date => c_serv_hist_rec.start_date
298 ,p_end_date => c_serv_hist_rec.end_date
299 ,p_period_years => c_serv_hist_rec.period_years
300 ,p_period_days => c_serv_hist_rec.period_days
301 ,p_assignment_id => p_assignment_id
302 ,p_calculation_date => p_calculation_date
303 ,p_days => l_days
304 );
305 --
306 l_tot_days := l_tot_days + l_days;
307 --
308 End If;
309 --
310 hr_utility.set_location(l_func, 30);
311 END LOOP;
312 --
313 FOR c_overlap_rec IN c_overlap_serv LOOP
314 If c_overlap_rec.start_date IS NOT NULL And
315 c_overlap_rec.end_date IS NOT NULL Then
316 --
317 -- Before calculating check whether this date
318 -- overlaps with current service period
319 --
320 hr_utility.set_location(l_func, 40);
321 check_overlap_curr_serv (p_start_date => c_overlap_rec.start_date
322 ,p_end_date => c_overlap_rec.end_date
323 ,p_period_years => NULL
324 ,p_period_days => NULL
325 ,p_assignment_id => p_assignment_id
326 ,p_calculation_date => p_calculation_date
327 ,p_days => l_days
328 );
329 --
330 l_tot_days := l_tot_days + l_days;
331 --
332 End If;
333 --
334 hr_utility.set_location(l_func, 50);
335 END LOOP;
336 --
337 --
338 -- Calculate the current service period
339 --
340 l_days := calculate_current_service (p_calculation_date => p_calculation_date
341 ,p_assignment_id => p_assignment_id
342 ,p_continuous_service => NULL
343 );
344 l_tot_days := l_tot_days + l_days;
345 --
346 hr_utility.set_location(l_func, 60);
347 --
348 return l_tot_days;
349 --
350 hr_utility.set_location('Leaving: '||l_func, 70);
351 --
352 END calculate_service_history;
353 --
354 -- Added this new function
355 -- PS Bug 2028104 for details
356 -- ----------------------------------------------------------------------------
357 -- |-----------------------< calculate_all_service_history >----------------------|
358 -- ----------------------------------------------------------------------------
359 --
360 FUNCTION calculate_all_service_history (p_assignment_id in number
361 ) RETURN number IS
362 --
363 -- This function should query back every period of service for the given
364 -- assignment, and add them together, finally adding on the length of the
365 -- current period of service
366 -- The result should be returned in days.
367 --
368
369 CURSOR c_all_serv_hist IS
370 SELECT start_date
371 ,end_date
372 ,period_years
376 --
373 ,period_days
374 FROM pqp_service_history_periods shp1
375 WHERE shp1.assignment_id = p_assignment_id;
377 l_func VARCHAR2(80) := 'pqp_service_history_calc_pkg.calculate_all_service_history';
378 l_days NUMBER(12);
379 l_tot_days NUMBER(12) := 0;
380 --
381 BEGIN
382 hr_utility.set_location('Entering: '||l_func, 10);
383 --
384 FOR c_all_serv_hist_rec in c_all_serv_hist LOOP
385
386 --
387 -- Calculate period if period_years and period_days do not have values
388 --
389 hr_utility.set_location(l_func, 20);
390
391 If c_all_serv_hist_rec.period_years Is Null And
392 c_all_serv_hist_rec.period_days Is Null Then
393
394 calculate_period (p_start_date => c_all_serv_hist_rec.start_date
395 ,p_end_date => c_all_serv_hist_rec.end_date
396 ,p_days => l_days
397 );
398
399 Else
400
401 -- The following conversion of years to days
402 -- is incorrect as 365 days for a year will be incorrect
403 -- for leap years
404 -- See Bug 4318334 for details
405
406 -- l_days := nvl(c_all_serv_hist_rec.period_years,0) * 365;;
407 l_days := 0;
408 IF NVL(c_all_serv_hist_rec.period_years, 0) <> 0 THEN
409 hr_utility.set_location(l_func, 25);
410 convert_years_to_days (p_start_date => c_all_serv_hist_rec.start_date
411 ,p_period_years => c_all_serv_hist_rec.period_years
412 ,p_days => l_days
413 );
414 END IF; -- End if of period years is not zero...
415 l_days := l_days + nvl(c_all_serv_hist_rec.period_days,0);
416
417 End If; -- End if of period check...
418
419 --
420 l_tot_days := l_tot_days + l_days;
421 --
422
423 END LOOP;
424
425 --
426 hr_utility.set_location(l_func, 30);
427 --
428 return l_tot_days;
429 --
430 hr_utility.set_location('Leaving: '||l_func, 40);
431 --
432 END calculate_all_service_history;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |---------------------< calculate_continuous_service >---------------------|
436 -- ----------------------------------------------------------------------------
437 FUNCTION calculate_continuous_service (p_assignment_id in number
438 ,p_calculation_date in date
439 ) RETURN number IS
440 --
441 -- This function should query back every period of service for the given
442 -- assignment, where the continuous service flag has been checked, and add
443 -- them together, finally adding on the length of the current period of
444 -- service, up to and including p_calculation_date.
445 -- The result should be returned in days.
446 --
447 -- Select years and days as we need not calculate period if they have a value
448 -- PS bug # for details
449 --
450
451 CURSOR c_cont_serv IS
452 SELECT start_date
453 ,LEAST(trunc(end_date), trunc(p_calculation_date)) end_date
454 ,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
455 period_years, NULL) period_years
456 ,DECODE(LEAST(trunc(end_date), trunc(p_calculation_date)), trunc(end_date),
457 period_days, NULL) period_days
458 FROM pqp_service_history_periods shp1
459 WHERE shp1.assignment_id = p_assignment_id
460 AND trunc(shp1.start_date) <= trunc(p_calculation_date)
461 AND shp1.continuous_service = 'Y'
462 AND not exists (SELECT 1 FROM pqp_service_history_periods shp2
463 WHERE shp2.assignment_id = shp1.assignment_id
464 AND shp2.continuous_service = 'Y'
465 AND shp2.service_history_period_id <> shp1.service_history_period_id
466 AND (trunc(shp2.start_date) between trunc(shp1.start_date)
467 and trunc(shp1.end_date)
468 or trunc(shp2.end_date) between trunc(shp1.start_date)
469 and trunc(shp1.end_date)
470 or trunc(shp1.start_date) between trunc(shp2.start_date)
471 and trunc(shp2.end_date)
472 or trunc(shp1.end_date) between trunc(shp2.start_date)
473 and trunc(shp2.end_date)));
474 --
475 CURSOR c_cont_overlap_serv IS
476 SELECT MIN(shp1.start_date) start_date
477 ,LEAST(trunc(MAX(shp1.end_date)), trunc(p_calculation_date)) end_date
478 FROM pqp_service_history_periods shp1
479 WHERE shp1.assignment_id = p_assignment_id
480 AND trunc(shp1.start_date) <= trunc(p_calculation_date)
481 AND shp1.continuous_service = 'Y'
482 AND exists (SELECT 1 FROM pqp_service_history_periods shp2
483 WHERE shp2.assignment_id = shp1.assignment_id
484 AND shp2.continuous_service = 'Y'
485 AND shp2.service_history_period_id <> shp1.service_history_period_id
489 and trunc(shp1.end_date)
486 AND (trunc(shp2.start_date) between trunc(shp1.start_date)
487 and trunc(shp1.end_date)
488 or trunc(shp2.end_date) between trunc(shp1.start_date)
490 or trunc(shp1.start_date) between trunc(shp2.start_date)
491 and trunc(shp2.end_date)
492 or trunc(shp1.end_date) between trunc(shp2.start_date)
493 and trunc(shp2.end_date)));
494 --
495 l_func VARCHAR2(60) := 'pqp_service_history_calc_pkg.calculate_continuous_service';
496 l_days NUMBER(12);
497 l_tot_days NUMBER(12) := 0;
498 --
499 BEGIN
500 hr_utility.set_location('Entering: '||l_func, 10);
501 --
502 FOR c_cont_serv_rec IN c_cont_serv LOOP
503 If c_cont_serv_rec.start_date IS NOT NULL And
504 c_cont_serv_rec.end_date IS NOT NULL Then
505 --
506 -- Before calculating check whether this date
507 -- overlaps with current service period
508 --
509 hr_utility.set_location(l_func, 20);
510 check_overlap_curr_serv (p_start_date => c_cont_serv_rec.start_date
511 ,p_end_date => c_cont_serv_rec.end_date
512 ,p_period_years => c_cont_serv_rec.period_years
513 ,p_period_days => c_cont_serv_rec.period_days
514 ,p_assignment_id => p_assignment_id
515 ,p_calculation_date => p_calculation_date
516 ,p_days => l_days
517 );
518 --
519 l_tot_days := l_tot_days + l_days;
520 --
521 End If;
522 END LOOP;
523 hr_utility.set_location(l_func, 30);
524 --
525 FOR c_cont_overlap_rec IN c_cont_overlap_serv LOOP
526 If c_cont_overlap_rec.start_date IS NOT NULL And
527 c_cont_overlap_rec.end_date IS NOT NULL Then
528 --
529 -- Before calculating check whether this date
530 -- overlaps with current service period
531 --
532 hr_utility.set_location(l_func, 40);
533 check_overlap_curr_serv (p_start_date => c_cont_overlap_rec.start_date
534 ,p_end_date => c_cont_overlap_rec.end_date
535 ,p_period_years => NULL
536 ,p_period_days => NULL
537 ,p_assignment_id => p_assignment_id
538 ,p_calculation_date => p_calculation_date
539 ,p_days => l_days
540 );
541 --
542 l_tot_days := l_tot_days + l_days;
543 --
544 End If;
545 END LOOP;
546 hr_utility.set_location(l_func, 50);
547 --
548 -- Calculate the current service period
549 --
550 l_days := calculate_current_service (p_calculation_date => p_calculation_date
551 ,p_assignment_id => p_assignment_id
552 ,p_continuous_service => 'Y'
553 );
554 l_tot_days := l_tot_days + l_days;
555 --
556 hr_utility.set_location(l_func, 60);
557 --
558 return l_tot_days;
559 --
560 hr_utility.set_location('Leaving: '||l_func, 70);
561 --
562 END calculate_continuous_service;
563 --
564 -- Added this new function
565 -- PS Bug 2028104 for details
566 -- ----------------------------------------------------------------------------
567 -- |-----------------------< calculate_all_continuous_serv >------------------|
568 -- ----------------------------------------------------------------------------
569 --
570 FUNCTION calculate_all_continuous_serv (p_assignment_id in number
571 ) RETURN number IS
572 --
573 -- This function should query back every period of service that is continuous for the given
574 -- assignment, and add them together, finally adding on the length of the
575 -- current period of service
576 -- The result should be returned in days.
577 --
578
579 CURSOR c_all_cont_serv IS
580 SELECT start_date
581 ,end_date
582 ,period_years
583 ,period_days
584 FROM pqp_service_history_periods shp1
585 WHERE shp1.assignment_id = p_assignment_id
586 AND shp1.continuous_service = 'Y';
587 --
588 l_func VARCHAR2(80) := 'pqp_service_history_calc_pkg.calculate_all_continuous_serv';
589 l_days NUMBER(12);
590 l_tot_days NUMBER(12) := 0;
591 --
592 BEGIN
593 hr_utility.set_location('Entering: '||l_func, 10);
594 --
595 FOR c_all_cont_serv_rec in c_all_cont_serv LOOP
596
597 --
598 -- Calculate period if period_years and period_days do not have values
599 --
600 hr_utility.set_location(l_func, 20);
601
602 If c_all_cont_serv_rec.period_years Is Null And
603 c_all_cont_serv_rec.period_days Is Null Then
604
608 );
605 calculate_period (p_start_date => c_all_cont_serv_rec.start_date
606 ,p_end_date => c_all_cont_serv_rec.end_date
607 ,p_days => l_days
609
610 Else
611
612 -- The following conversion of years to days
613 -- is incorrect as 365 days for a year will be incorrect
614 -- for leap years
615 -- See Bug 4318334 for details
616
617 -- l_days := nvl(c_all_cont_serv_rec.period_years,0) * 365;
618 l_days := 0;
619 IF NVL(c_all_cont_serv_rec.period_years, 0) <> 0 THEN
620 hr_utility.set_location(l_func, 25);
621 convert_years_to_days (p_start_date => c_all_cont_serv_rec.start_date
622 ,p_period_years => c_all_cont_serv_rec.period_years
623 ,p_days => l_days
624 );
625 END IF; -- End if of period years is not zero...
626 l_days := l_days + nvl(c_all_cont_serv_rec.period_days,0);
627
628 End If; -- End if of period check...
629
630 --
631 l_tot_days := l_tot_days + l_days;
632 --
633
634 END LOOP;
635
636 --
637 hr_utility.set_location(l_func, 30);
638 --
639 return l_tot_days;
640 --
641 hr_utility.set_location('Leaving: '||l_func, 40);
642 --
643 END calculate_all_continuous_serv;
644 --
645 -- ----------------------------------------------------------------------------
646 -- |-------------------< calculate_service_hist_period >----------------------|
647 -- ----------------------------------------------------------------------------
648 PROCEDURE calculate_service_hist_period (p_start_date in date
649 ,p_end_date in date
650 ,p_years out nocopy number
651 ,p_days out nocopy number
652 ) IS
653 --
654 -- This procedure should calculate the duration of a particular period of
655 -- service history.
656 -- The result should be returned in years and days.
657 --
658 l_proc VARCHAR2(60) := 'pqp_service_history_calc_pkg.calculate_service_hist_period';
659 l_start_date DATE := trunc(p_start_date);
660 l_new_start_date DATE;
661 -- include the end date so set the date to +1
662 l_end_date DATE := trunc(p_end_date) + 1;
663 l_tot_days NUMBER(12);
664 l_years NUMBER(12);
665 l_days NUMBER(12);
666 --
667 BEGIN
668 hr_utility.set_location('Entering: '||l_proc, 10);
669 --
670 SELECT (l_end_date - l_start_date)
671 INTO l_tot_days
672 FROM dual;
673 --
674 hr_utility.set_location(l_proc, 30);
675 --
676 -- The following conversion of days to years
677 -- is incorrect as 365 days for a year will be incorrect
678 -- for leap years
679 -- See Bug 4318334 for details
680
681 -- l_years := trunc(l_tot_days/365);
682 --
683 -- hr_utility.set_location(l_proc, 40);
684 --
685
686 -- SELECT MOD(l_tot_days,365)
687 -- INTO l_days
688 -- FROM dual;
689 --
690 -- l_days := l_days + 1 ;
691 -- --
692 -- IF l_days = 365 THEN
693 -- l_days := 0;
694 -- l_years := l_years + 1;
695 -- END IF;
696
697 -- Get the number of years based on date using MONTHS_BETWEEN function
698 SELECT TRUNC(MONTHS_BETWEEN(l_end_date, l_start_date)/12)
699 INTO l_years
700 FROM dual;
701
702 -- Get the new start date based on the l_years figure from the start date
703
704 hr_utility.set_location(l_proc, 40);
705 SELECT ADD_MONTHS(l_start_date, l_years * 12)
706 INTO l_new_start_date
707 FROM dual;
708
709 -- Get the number of days based on this new start date
710
711 hr_utility.set_location(l_proc, 50);
712 SELECT l_end_date - l_new_start_date
713 INTO l_days
714 FROM dual;
715
716
717 --
718 p_years := l_years;
719 p_days := l_days;
720 --
721 hr_utility.set_location('Leaving: '||l_proc, 60);
722 --
723
724 -- Added by tmehra for nocopy changes Feb'03
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 hr_utility.set_location('Entering excep:'||l_proc, 35);
729 p_years := 0;
730 p_days := 0;
731 raise;
732
733
734 END calculate_service_hist_period;
735 --
736 END pqp_service_history_calc_pkg;
[Home] [Help]