1 package body HR_CALENDAR_PKG as
2 /* $Header: hrcalapi.pkb 120.1.12010000.2 2008/11/07 11:03:35 pbalu noship $ */
3 --
4 --------------------------------------------------------------------------------
5 /*
6 +==============================================================================+
7 | Copyright (c) 1994 Oracle Corporation |
8 | Redwood Shores, California, USA |
9 | All rights reserved. |
10 +==============================================================================+
11 --
12 Name
13 Calendars Business Process
14 Purpose
15 To provide routines to give information about calendars
16 History
17 05 sep 95 N Simpson Created
18 07 Sep 95 N Simpson Added function purpose_usage_id
19 Version Date BugNo Author Comment
20 -------+-----------+---------+---------+--------------------------------------
21 40.3 18-Jul_97 513292 RThirlby Created another overload of procedure
22 denormalise_calendar. Altered function
23 total_availability to accept parameters
24 for both bg and person pattern in the
25 same (linked) absence.
26 40.4 29-Sep-97 504386 RThirlby Changes made to derive_pattern_cycle
27 to enhance performance.
28 40.5 11-Nov-97 572460 AMills Added exit to reconcile_schedule to trap
29 values where the normal pattern stack
30 pointer exceeds the number of rows in
31 the normal_pattern table.
32 40.6 14-Nov-97 504386 AMills Added extra criteria to 'derive_pattern
33 cycle' to ensure normal_pattern table
34 is restored for absence periods greater
35 than the original time period, where the
36 calendar remains the same. Cancelled
37 last alteration to reconcile_schedule as
38 no longer necessary.
39 40.7 27-Nov-97 584613 RThirlby Global variables re-initiated at start
40 of denormalise_calendar, so that the
41 Schedules window can be re-queried.
42 40.8 03-JUL-98 655707 A.Myers Added processing_level parameter which
43 indicates how much processing needs to
44 be done, whether the existing calendar
45 can be used or not (used by calling
46 package SSP_SSP_PKG only).
47 115.9 23-jul-02 1404898 vmkhande The denormalise_calendar procedure in
48 TOTAL_AVAILABILITY funciton is called
49 if the prcoessing level is less than 2.
50 When this funciton is called for the
51 BG calendar usage, the processing level
52 is set as 2 hence the
53 denormalise_calendar is not called and
54 the schedule stack holds incorrect date
55 values. The fix is: The condtion which
56 check the parameter value is to be
57 changed to <= 2.
58 115.10 15-JUN-04 3669001 kthampan Changes to the derive_pattern_cycle procdure
59 to use g_period_from instead of p_period_from
60 when extending the pattern cycle.
61 115.11 15-JUN-04 kthampan Fix GSCC warnings
62 115.12 24-MAR-08 6850908 pbalu When a person has two Qualifying patterns and both are created
63 after the absence start date and there is a gap between the two
64 Person qualifying patterns then while saving or updating the absence
65 oracle error no data found occurs
66 */
67 --------------------------------------------------------------------------------
68 type number_table is table of number index by binary_integer;
69 type varchar_table is table of varchar2 (30) index by binary_integer;
70 type date_table is table of date index by binary_integer;
71 --
72 one_second constant number := 1/86400; -- a second as a proportion of one day
73 --
74 -- Normal pattern construction variables
75 --
76 type pattern_info is record (
77 availability varchar_table,
78 duration_days number_table,
79 stored_pattern_id number :=0,
80 stored_number_of_bits number :=0);
81 --
82 type stack is record (
83 --
84 pointer integer :=1,
85 run_out boolean := FALSE,
86 start_date date_table,
87 end_date date_table,
88 level number_table,
89 availability varchar_table);
90 --
91 calendar_exception stack;
92 usage_exception stack;
93 normal_pattern stack;
94 schedule stack;
95 empty_stack stack;
96 --
97 pattern pattern_info;
98 empty_pattern pattern_info;
99 index_no integer;
100 g_hc_package constant varchar2 (18) := 'hr_calendar_pkg.';
101 g_calendar_id number; --global calendar ID
102 g_period_from date; --global period from
103 g_period_to date; --global period to
104 initialise_flag boolean := false;
105 --
106 cursor pattern_bits (p_pattern_id in number) is
107 --
108 -- Get the pattern by denormalising the duration of each bit of the pattern
109 -- to show the duration and availability. The sql separates those bits of the
110 -- pattern which are directly based on time units from those which are
111 -- themselves patterns and require a further step to get the time units. There
112 -- is a business rule which limits the pattern hierarchy to two levels.
113 --
114 select bit1.time_unit_multiplier,
115 bit1.base_time_unit,
116 con1.sequence_no,
117 0,
118 con1.availability
119 from hr_pattern_constructions CON1,
120 hr_pattern_bits BIT1
121 where bit1.pattern_bit_id = con1.pattern_bit_id
122 and con1.pattern_id = p_pattern_id
123 union all
124 select bit2.time_unit_multiplier,
125 bit2.base_time_unit,
126 con2.sequence_no,
127 con3.sequence_no,
128 con3.availability
129 from hr_pattern_bits BIT2,
130 hr_pattern_constructions CON2,
131 hr_pattern_constructions CON3
132 where bit2.pattern_bit_id = con3.pattern_bit_id
133 and con2.component_pattern_id = con3.pattern_id
134 and con2.pattern_id = p_pattern_id
135 order by 3,4;
136 --------------------------------------------------------------------------------
137 --
138 function TO_DAYS (
139 --
140 -- Convert user-defined time unit into days for ease of comparison and
141 -- manipulation.
142 --
143 quantity number,
144 units varchar2) return number is
145 --
146 conversion_factor number :=1;
147 --
148 begin
149 --
150 if units = 'H' then
151 conversion_factor := 24;
152 --
153 elsif units = 'W' then
154 conversion_factor := 1/7;
155 --
156 end if;
157 --
158 return (quantity / conversion_factor);
159 --
160 end to_days;
161 --------------------------------------------------------------------------------
162 --
163 procedure DERIVE_PATTERN_CYCLE (
164 --
165 -- Converts a pattern from a single iteration of undated durations into a
166 -- schedule of dated chunks.
167 --
168 p_calendar_id number,
169 p_period_from date,
170 p_period_to date,
171 p_called_from_SSP boolean default false) is
172 --
173 cursor calendar is
174 --
175 -- Get the details of the calendar
176 --
177 select pattern_start_position,
178 calendar_start_time,
179 pattern_id
180 from hr_calendars
181 where calendar_id = P_CALENDAR_ID;
182 --
183 start_position integer;
184 start_time date;
185 l_pattern_id number;
186 cycle_date date;
187 n integer :=0;
188 first_bit integer :=1;
189 total_duration_time number :=0;
190 duration_time number :=0;
191 first_loop boolean;
192 old_cycle_date date;
193 counter integer := 0;
194 l_proc varchar2 (42) := g_hc_package||'Derive_Pattern_Cycle';
195 --
196 begin
197 --
198 hr_utility.set_location('Entering:'||l_proc,1);
199 --
200 -- Find out which pattern we are dealing with, where within the pattern sequence
201 -- to start the calendar, and what the start date for the pattern is.
202 --
203 -- Bug 504386 - If the same calendar is being passed in, then use the values
204 -- already in the pl/sql table normal_pattern.
205 --
206 if g_calendar_id is null or
207 g_calendar_id <> p_calendar_id or
208 p_period_to not between g_period_from and g_period_to or
209 (p_period_from < g_period_from or p_called_from_SSP = false)
210 then
211 if p_called_from_SSP = true
212 then
213 if (g_period_from is null or p_period_from < g_period_from)
214 then
215 --
216 -- Do not reset g_period_from if we are currently using it (i.e. it is
217 -- not null, but set it if p_period_from is earlier so that we get a
218 -- complete calendar.
219 --
220 g_period_from := p_period_from;
221 end if;
222 else
223 g_period_from := p_period_from;
224 end if;
225 --
226 hr_utility.set_location(' In:'||l_proc||', rederiving calendar',10);
227 --
228 g_period_to := p_period_to;
229 g_calendar_id := p_calendar_id;
230 index_no := 1;
231 --
232 open calendar;
233 fetch calendar into start_position, cycle_date, l_pattern_id;
234 close calendar;
235 --
236 -- Get required pattern from the database, and convert it to units of a day.
237 --
238 if l_pattern_id <> pattern.stored_pattern_id then
239 --
240 -- Get pattern construction and cache it in private global variables
241 --
242 for next_bit in pattern_bits (l_pattern_id) LOOP
243 counter := counter+1;
244 --
245 pattern.duration_days (counter) :=
246 hr_calendar_pkg.to_days (next_bit.time_unit_multiplier,
247 next_bit.base_time_unit);
248 pattern.availability (counter) := next_bit.availability;
249 end loop;
250 --
251 pattern.stored_pattern_id := l_pattern_id;
252 pattern.stored_number_of_bits := counter;
253 end if;
254 --
255 -- Bug 504386 - Find out the patterns total duration time
256 --
257 for n in first_bit..pattern.stored_number_of_bits LOOP
258 total_duration_time := total_duration_time + pattern.duration_days (n);
259 end loop;
260 --
261 -- Bug 504386 - Roll forward to the periods required.
262 first_loop := TRUE;
263 --
264 -- Bug 3669001 - change from p_period_from to g_period_from
265 while cycle_date <= g_period_from
266 loop
267 old_cycle_date := cycle_date;
268 --
269 if first_loop = TRUE
270 then
271 --
272 -- If we are in the first pass through the loop (ie we are on the first
273 -- iteration of the pattern) then we must take account of the position
274 -- in the pattern on which the calendar starts, because we may not want
275 -- to start at the beginning of the pattern.
276 --
277 first_loop := FALSE;
278 first_bit := start_position;
279 for n in first_bit..pattern.stored_number_of_bits LOOP
280 duration_time := duration_time + pattern.duration_days (n);
281 end loop;
282 else
283 --
284 -- If we are in any but the first pass through the loop, then we always
285 -- take the whole pattern into account.
286 --
287 duration_time := total_duration_time;
288 end if;
289 --
290 cycle_date := cycle_date + duration_time;
291 end loop;
292 --
293 --6850908 Begin
294 --cycle_date := old_cycle_date;
295 --before assigning the old cycle date the value is checked
296 IF old_cycle_date <= g_period_from THEN
297 cycle_date := old_cycle_date;
298 END IF;
299 --6850908 End
300 --
301 -- Generate a denormalised calendar by placing copies of the pattern end to
302 -- end, starting with the calendar start date and ending with the end of our
303 -- period of interest.
304 --
305 while cycle_date <= p_period_to
306 LOOP
307 --
308 -- Go through the cached pattern, deriving the dates of the calendar by
309 -- taking the date we have reached so far and adding the duration of the
310 -- next bit of the pattern to it.
311 --
312 for n in 1..pattern.stored_number_of_bits LOOP
313 --
314 -- Derive the dates of the next bit of the calendar.
315 --
316 normal_pattern.start_date (index_no) := cycle_date;
317 --
318 normal_pattern.end_date (index_no) :=
319 normal_pattern.start_date (index_no) + pattern.duration_days (n);
320 --
321 -- Increment 'date reached so far' by the duration of the pattern bit.
322 --
323 cycle_date := cycle_date + pattern.duration_days (n);
324 --
325 -- Derive the availability value for the next bit of the calendar.
326 --
327 normal_pattern.availability (index_no) := pattern.availability (n);
328 --
329 index_no := index_no + 1;
330 --
331 end loop;
332 end loop;
333 end if;
334 --
335 hr_utility.set_location('Leaving :'||l_proc,100);
336 --
337 end derive_pattern_cycle;
338 --------------------------------------------------------------------------------
339 --
340 procedure DERIVE_CALENDAR_EXCEPTIONS (
341 --
342 -- Cache the calendar exceptions in a pl/sql data structure for use in the
343 -- reconcile_schedule procedure.
344 --
345 p_calendar_id number,
346 p_period_start_time date,
347 p_period_end_time date) is
348 --
349 cursor calendar_exceptions is
350 --
351 -- Get all the calendar exceptions for the calendar, which are within
352 -- our period of interest
353 --
354 select exc.pattern_id,
355 exc.exception_start_time
356 from hr_pattern_exceptions EXC,
357 hr_exception_usages EXC_USE
358 where exc.exception_start_time < p_period_end_time
359 and exc.exception_end_time > p_period_start_time
360 and exc.exception_id = exc_use.exception_id
361 and exc_use.calendar_id = p_calendar_id
362 order by exc.exception_start_time;
363 --
364 index_no integer :=1;
365 cycle_date date;
366 --
367 begin
368 --
369 for next_exception in calendar_exceptions LOOP
370 --
371 -- For each exception in the period on the calendar, derive the dates of each
372 -- bit of the pattern on which the exception is based.
373 --
374 cycle_date := next_exception.exception_start_time;
378 --
375 --
376 for next_exception_bit in pattern_bits (next_exception.pattern_id)
377 LOOP
379 calendar_exception.start_date (index_no) := cycle_date;
380 --
381 -- Increment the date reached so far by the duration of the next pattern bit
382 --
383 cycle_date := cycle_date
384 + (hr_calendar_pkg.to_days (next_exception_bit.time_unit_multiplier,
385 next_exception_bit.base_time_unit)) ;
386 --
387 calendar_exception.end_date (index_no) := cycle_date ;
388 --
389 calendar_exception.availability (index_no)
390 := next_exception_bit.availability;
391 --
392 index_no := index_no+1;
393 --
394 end loop;
395 --
396 end loop;
397 --
398 end derive_calendar_exceptions;
399 --------------------------------------------------------------------------------
400 --
401 procedure DERIVE_USAGE_EXCEPTIONS (
402 --
403 p_calendar_usage_id number,
404 p_period_start_time date,
405 p_period_end_time date) is
406 --
407 cursor usage_exceptions is
408 --
409 -- Get all the exceptions for the specified usage which are within the
410 -- period of interest.
411 --
412 select exc.pattern_id,
413 exc.exception_start_time
414 from hr_pattern_exceptions EXC,
415 hr_exception_usages EXC_USE
416 where exc.exception_start_time < p_period_end_time
417 and exc.exception_end_time > p_period_start_time
418 and exc.exception_id = exc_use.exception_id
419 and exc_use.calendar_usage_id = p_calendar_usage_id
420 order by exc.exception_start_time;
421 --
422 index_no integer :=1;
423 cycle_date date;
424 --
425 begin
426 --
427 for next_exception in usage_exceptions LOOP
428 --
429 -- For each exception, derive the dates of each bit of the pattern on which
430 -- the exception is based.
431 --
432 cycle_date := next_exception.exception_start_time;
433 --
434 for next_exception_bit in pattern_bits (next_exception.pattern_id)
435 LOOP
436 --
437 usage_exception.start_date (index_no) := cycle_date;
438 --
439 -- Increment the date reached so far by the duration of the next pattern bit
440 --
441 cycle_date := cycle_date
442 + (hr_calendar_pkg.to_days (next_exception_bit.time_unit_multiplier,
443 next_exception_bit.base_time_unit)) ;
444 --
445 usage_exception.end_date (index_no) := cycle_date ;
446 --
447 usage_exception.availability (index_no) := next_exception_bit.availability;
448 --
449 index_no := index_no+1;
450 --
451 end loop;
452 --
453 end loop;
454 --
455 end derive_usage_exceptions;
456 --------------------------------------------------------------------------------
457 procedure RECONCILE_SCHEDULE (
458 --
459 -- Reconcile the repeating pattern on which the calendar is based with all the
460 -- exceptions to that pattern at calendar and usage level.
461 --
462 p_period_from date,
463 p_period_to date) is
464 --
465 cycle_date date := p_period_from;
466 --
467 begin
468 --
469 -- Reset the pointers
470 --
471 usage_exception.pointer :=1;
472 calendar_exception.pointer :=1;
473 normal_pattern.pointer :=1;
474 usage_exception.run_out := FALSE;
475 calendar_exception.run_out := FALSE;
476 normal_pattern.run_out := FALSE;
477 --
478 -- Construct the schedule from the start to the end of the requested period
479 --
480 while cycle_date < p_period_to LOOP
481 --
482 -- There are 3 stacks to be merged; the normal pattern, the exceptions
483 -- which apply to the calendar as a whole (calendar exceptions), and
484 -- the exceptions which apply only to a particular entity (usage
485 -- exceptions).
486 --
487 -- Set the pointers of each stack to the current or next date
488 --
489 -- Set the usage exception stack pointer
490 --
491 if (not usage_exception.run_out) then
492 --
493 begin
494 --
495 LOOP
496 --
497 -- Find the first row which is later than the date we have reached so far
498 --
499 exit when usage_exception.end_date (usage_exception.pointer) > cycle_date;
500 usage_exception.pointer := usage_exception.pointer +1;
501 --
502 end loop;
503 --
504 exception
505 when no_data_found then
506 --
507 -- There are no more rows in the data structure so do not attempt more
508 -- fetches. Set flag to prevent this.
509 --
510 usage_exception.run_out := TRUE;
511 --
512 end;
513 --
514 end if;
515 --
516 -- Set the calendar excption stack pointer
517 --
518 if (not calendar_exception.run_out) then
519 --
520 begin
521 --
522 LOOP
523 --
524 -- Find the first row which is later than the date we have reached so far
525 --
526 exit when calendar_exception.end_date (calendar_exception.pointer)
527 > cycle_date;
528 calendar_exception.pointer := calendar_exception.pointer +1;
529 --
530 end loop;
531 --
532 exception
533 when no_data_found then
537 --
534 --
535 -- There are no more rows in the data structure so do not attempt more
536 -- fetches. Set flag to prevent this.
538 calendar_exception.run_out := TRUE;
539 --
540 end;
541 --
542 end if;
543 --
544 -- Set the normal pattern stack pointer
545 --
546 if (not normal_pattern.run_out) then
547 --
548 begin
549 --
550 LOOP
551 --
552 exit when normal_pattern.end_date (normal_pattern.pointer) > cycle_date;
553 normal_pattern.pointer := normal_pattern.pointer +1;
554 --
555 end loop;
556 --
557 exception
558 when no_data_found then
559 normal_pattern.run_out := TRUE;
560 end;
561 --
562 end if;
563 --
564 schedule.start_date (schedule.pointer) := cycle_date;
565 --
566 if (not usage_exception.run_out)
567 and usage_exception.start_date (usage_exception.pointer) <=cycle_date then
568 --
569 -- We are currently on a usage exception. Usage exceptions override
570 -- all other levels, so the schedule takes on all the values of the
571 -- usage exception.
572 --
573 schedule.level (schedule.pointer) := 3;
574 schedule.availability (schedule.pointer)
575 := usage_exception.availability (usage_exception.pointer);
576 schedule.end_date (schedule.pointer)
577 := usage_exception.end_date (usage_exception.pointer);
578 --
579 elsif (not calendar_exception.run_out)
580 and calendar_exception.start_date (calendar_exception.pointer)
581 <= cycle_date then
582 --
583 -- We are currently on a calendar exception. Assign the level and
584 -- availability values of the calendar exception to the schedule
585 --
586 schedule.level (schedule.pointer) := 2;
587 schedule.availability (schedule.pointer)
588 := calendar_exception.availability (calendar_exception.pointer);
589 --
590 -- Before we can assign the end date value to the schedule, we must
591 -- determine if there is a usage exception starting before the calendar
592 -- exception ends, because that start date would take precedence
593 --
594 if (not usage_exception.run_out)
595 and usage_exception.start_date (usage_exception.pointer)
596 < calendar_exception.end_date (calendar_exception.pointer)
597 then
598 schedule.end_date (schedule.pointer)
599 := usage_exception.start_date (usage_exception.pointer) ;
600 else
601 schedule.end_date (schedule.pointer)
602 := calendar_exception.end_date (calendar_exception.pointer);
603 end if;
604 --
605 else
606 --
607 -- If we get to this point, there must be no exceptions so we are on the
608 -- normal pattern. Assign the level and availability values to those of
609 -- the normal pattern.
610 --
611 schedule.level (schedule.pointer) := 1;
612 schedule.availability (schedule.pointer)
613 := normal_pattern.availability (normal_pattern.pointer);
614 --
615 -- Before we can assign the end date of the normal pattern bit to the
616 -- schedule, we must determine if there are any exceptions which start
617 -- before the normal bit ends. The start of such an exception would take
618 -- precedence over the end of the normal pattern bit.
619 --
620 -- First check for usage exceptions
621 --
622 if (not usage_exception.run_out)
623 and usage_exception.start_date (usage_exception.pointer)
624 < normal_pattern.end_date (normal_pattern.pointer) then
625 --
626 schedule.end_date (schedule.pointer)
627 := usage_exception.start_date (usage_exception.pointer) ;
628 --
629 -- Now check for calendar exceptions
630 --
631 elsif (not calendar_exception.run_out)
632 and calendar_exception.start_date (calendar_exception.pointer)
633 < normal_pattern.end_date (normal_pattern.pointer) then
634 --
635 schedule.end_date (schedule.pointer) :=
636 calendar_exception.start_date (calendar_exception.pointer) ;
637 --
638 else
639 --
640 -- There are no exceptions before the normal pattern bit ends
641 --
642 schedule.end_date (schedule.pointer)
643 := normal_pattern.end_date (normal_pattern.pointer);
644 --
645 end if;
646 --
647 end if;
648 --
649 if schedule.end_date (schedule.pointer) > p_period_to then
650 --
651 -- Drag the end date back to the end of the period
652 --
653 schedule.end_date (schedule.pointer) := p_period_to;
654 --
655 end if;
656 --
657 -- Move on to the next bit of the schedule
658 --
659 cycle_date := schedule.end_date (schedule.pointer) + one_second ;
660 --
661 schedule.pointer := schedule.pointer +1;
662 --
663 end loop;
664 --
665 end reconcile_schedule;
666 --------------------------------------------------------------------------------
667 --
668 function START_DATE (row_number integer) return date is
669 --
670 -- Returns the start date of the pl/sql table row identified by the row_number
671 --
672 l_start_date date;
673 --
674 begin
675 --
679 --
676 l_start_date := schedule.start_date (row_number);
677 --
678 return l_start_date;
680 exception
681 when no_data_found then
682 return null;
683 end start_date;
684 --------------------------------------------------------------------------------
685 --
686 function END_DATE (row_number integer) return date is
687 --
688 -- Returns the end date of the pl/sql table row identified by the row_number
689 --
690 begin
691 --
692 return schedule.end_date (row_number);
693 --
694 exception
695 when no_data_found then
696 return null;
697 end end_date;
698 --------------------------------------------------------------------------------
699 --
700 function AVAILABILITY_VALUE (row_number integer) return varchar2 is
701 --
702 -- Returns the availability of the pl/sql table row identified by the row_number
703 --
704 begin
705 --
706 return schedule.availability (row_number);
707 --
708 exception
709 when no_data_found then
710 return null;
711 end availability_value;
712 --------------------------------------------------------------------------------
713 --
714 function SCHEDULE_LEVEL_VALUE (row_number integer) return number is
715 --
716 -- Returns the level of the pl/sql table row identified by the row_number
717 --
718 begin
719 --
720 return schedule.level (row_number);
721 --
722 exception
723 when no_data_found then
724 return null;
725 end schedule_level_value;
726 --------------------------------------------------------------------------------
727 --
728 procedure DENORMALISE_CALENDAR (
729 --
730 -- Derive a denormalised calendar into an internal pl/sql data structure, for
731 -- the calendar usage passed in. Take into account the repetitive pattern on
732 -- which the calendar is based, and all exceptions for both the calendar itself
733 -- and for the usage.
734 -- NB This procedure is OVERLOADED 3 times! This one is called by SSP.
735 --
736 -- Bug 513292 - new cursor which returns both BG and person patterns depending
737 -- which parameters are passed in.
738 --
739 p_person_purpose_usage_id number,
740 p_person_primary_key_value number,
741 p_bg_purpose_usage_id number,
742 p_bg_primary_key_value number,
743 p_period_from date,
744 p_period_to date,
745 p_called_from_SSP boolean default false) is
746 --
747 cursor calendar is
748 --
749 -- Get the calendars and usages
750 --
751 select use.calendar_usage_id,
752 use.calendar_id,
753 use.start_date,
754 use.end_date
755 from hr_calendar_usages USE
756 where use.purpose_usage_id = p_bg_purpose_usage_id
757 and use.primary_key_value = p_bg_primary_key_value
758 and use.start_date <= p_period_to
759 and use.end_date >= p_period_from
760 UNION ALL
761 select use.calendar_usage_id,
762 use.calendar_id,
763 use.start_date,
764 use.end_date
765 from hr_calendar_usages USE
766 where use.purpose_usage_id = p_person_purpose_usage_id
767 and use.primary_key_value = p_person_primary_key_value
768 and use.start_date <= p_period_to
769 and use.end_date >= p_period_from
770 order by 3;
771 --
772 start_of_period date;
773 end_of_period date;
774 --
775 begin
776 --
777 -- Clear the data structures
778 --
779 schedule := empty_stack;
780 --
781 -- Denormalise the exceptions for the usage and hold them internally
782 --
783 for each_calendar_usage in calendar
784 LOOP
785 --
786 start_of_period := greatest (each_calendar_usage.start_date, p_period_from);
787 end_of_period := least (each_calendar_usage.end_date, p_period_to);
788 --
789 if initialise_flag <> true then
790 normal_pattern := empty_stack;
791 initialise_flag := true;
792 calendar_exception := empty_stack;
793 usage_exception := empty_stack;
794 end if;
795 --
796 derive_pattern_cycle(each_calendar_usage.calendar_id,
797 start_of_period, end_of_period,
798 p_called_from_SSP);
799 --
800 derive_calendar_exceptions(each_calendar_usage.calendar_id,
801 start_of_period, end_of_period);
802 --
803 derive_usage_exceptions (each_calendar_usage.calendar_usage_id,
804 start_of_period, end_of_period);
805 --
806 reconcile_schedule (start_of_period, end_of_period);
807 --
808 end loop;
809 --
810 end denormalise_calendar;
811 --------------------------------------------------------------------------------
812 --
813 procedure DENORMALISE_CALENDAR (
814 --
815 -- Derive a denormalised calendar into an internal pl/sql data structure, for
816 -- the calendar usage passed in. Take into account the repetitive pattern on
817 -- which the calendar is based, and all exceptions for both the calendar itself
818 -- and for the usage.
819 -- NB This procedure is OVERLOADED 3 times!
820 --
821 p_purpose_usage_id number,
822 p_primary_key_value number,
823 p_period_from date,
827 --
824 p_period_to date) is
825 --
826 cursor calendar is
828 -- Get the calendars and usages
829 --
830 select use.calendar_usage_id,
831 use.calendar_id,
832 use.start_date,
833 use.end_date
834 from hr_calendar_usages USE,
835 hr_calendars CAL
836 where use.purpose_usage_id = p_purpose_usage_id
837 and use.primary_key_value = p_primary_key_value
838 and use.start_date <= p_period_to
839 and use.end_date >= p_period_from
840 and cal.calendar_id = use.calendar_id
841 order by use.start_date;
842 --
843 start_of_period date;
844 end_of_period date;
845 --
846 begin
847 --
848 -- Bug 584613 - re-initialise global variables so that the Schedules window can
849 -- be re-queried.
850 --
851 g_period_from := null;
852 g_period_to := null;
853 g_calendar_id := null;
854 --
855 -- Clear the data structures
856 --
857 schedule := empty_stack;
858 --
859 -- Denormalise the exceptions for the usage and hold them internally
860 --
861 for each_calendar_usage in calendar
862 LOOP
863 --
864 start_of_period := greatest (each_calendar_usage.start_date,
865 p_period_from);
866 end_of_period := least (each_calendar_usage.end_date,
867 p_period_to);
868 --
869 normal_pattern := empty_stack;
870 calendar_exception := empty_stack;
871 usage_exception := empty_stack;
872 --
873 derive_pattern_cycle (each_calendar_usage.calendar_id,
874 start_of_period,
875 end_of_period);
876 --
877 derive_calendar_exceptions (each_calendar_usage.calendar_id,
878 start_of_period,
879 end_of_period);
880 --
881 derive_usage_exceptions (each_calendar_usage.calendar_usage_id,
882 start_of_period,
883 end_of_period);
884 --
885 reconcile_schedule (start_of_period,
886 end_of_period);
887 --
888 end loop;
889 --
890 end denormalise_calendar;
891 ------------------------------------------------------------------------------
892 --
893 procedure DENORMALISE_CALENDAR (
894 --
895 -- Derive the denormalised calendar into an internal data structure. This
896 -- procedure does not require that usages be defined for the calendar and does
897 -- not take any usage exceptions into account.
898 -- NB This procedure is OVERLOADED 3 times!
899 --
900 p_calendar_id number,
901 p_calendar_start_time date,
902 p_period_from date,
903 p_period_to date) is
904 --
905 start_of_period date := greatest (p_period_from, p_calendar_start_time);
906 end_of_period date := p_period_to;
907 --
908 begin
909 -- Bug 584613 - re-initialise global variables so that the Schedules window can
910 -- be re-queried.
911 --
912 g_period_from := null;
913 g_period_to := null;
914 g_calendar_id := null;
915 --
916 -- Clear the data structures
917 --
918 schedule := empty_stack;
919 usage_exception := empty_stack;
920 normal_pattern := empty_stack;
921 calendar_exception := empty_stack;
922 --
923 -- Get the pattern on which the calendar is based
924 --
925 derive_pattern_cycle (p_calendar_id,
926 start_of_period,
927 end_of_period);
928 --
929 -- Get all the exceptions to the pattern which apply to the calendar
930 --
931 derive_calendar_exceptions (p_calendar_id,
932 start_of_period,
933 end_of_period);
934 --
935 reconcile_schedule (start_of_period,
936 end_of_period);
937 --
938 end denormalise_calendar;
939 --------------------------------------------------------------------------------
940 --
941 function SCHEDULE_ROWCOUNT return number is
942 --
943 -- Returns the number of rows in the internal plsql data structure which stores
944 -- the denormalised calendar.
945 --
946 begin
947 --
948 return greatest (schedule.pointer -1,0);
949 --
950 end schedule_rowcount;
951 --------------------------------------------------------------------------------
952 --
953 function TOTAL_AVAILABILITY (
954 --
955 -- Returns the amount of time within a calendar for an individual that is
956 -- marked as having a specified availability. Eg how much time (in days) a
957 -- person is 'on-call'.
958 --
959 -- Bug 513292 - parameters added so that both BG and person patterns can be
960 -- returned for one (linked absence).
961 -- Bug 701750 - parameter p_processing_level to control amount of processing,
962 -- if called by SSP package SSP_SSP_PKG.
963 --
964 p_availability varchar2,
965 p_person_purpose_usage_id number,
966 p_person_primary_key_value number,
967 p_bg_purpose_usage_id number,
968 p_bg_primary_key_value number,
969 p_period_from date,
970 p_period_to date,
974 l_called_from_SSP boolean := false;
971 p_processing_level number default 0) return number is
972 --
973 l_proc varchar2 (42) := g_hc_package||'Total_Availability';
975 l_total number :=0;
976 l_start_date date;
977 l_end_date date;
978 --
979 begin
980 --
981 hr_utility.set_location('Entering:'||l_proc||'. Processing level: '||
982 to_char(p_processing_level),1);
983 --
984 if p_processing_level > 0
985 then
986 l_called_from_SSP := true;
987 end if;
988 --
989 -- derive the calendar for the required period
990 --
991 if p_processing_level <= 2 or g_period_from is null
992 then
993 denormalise_calendar(p_person_purpose_usage_id,
994 p_person_primary_key_value,
995 p_bg_purpose_usage_id,
996 p_bg_primary_key_value,
997 p_period_from,
998 p_period_to,
999 l_called_from_SSP);
1000 end if;
1001 --
1002 -- Loop through the schedule, adding up the duration of any period during
1003 -- which the availability matches the required availability value.
1004 --
1005 for this_row in 1..schedule_rowcount loop
1006 -- hr_utility.trace('Schedule rec: '||to_char(this_row) ||
1007 -- ' Availability: '||schedule.availability (this_row));
1008 -- hr_utility.trace(' Dates: Start:'||
1009 -- to_char(schedule.start_date (this_row)) || ', End:' ||
1010 -- to_char(schedule.end_date (this_row)));
1011 --
1012 if schedule.availability (this_row) = p_availability then
1013 l_start_date := schedule.start_date(this_row);
1014 l_end_date := schedule.end_date(this_row);
1015 --
1016 if p_processing_level > 1
1017 then
1018 if l_start_date < p_period_to
1019 then
1020 if l_end_date > p_period_from
1021 then
1022 if l_start_date < p_period_from
1023 then
1024 l_start_date := p_period_from;
1025 end if;
1026
1027 if l_end_date > p_period_to
1028 then
1029 l_end_date := p_period_to;
1030 end if;
1031
1032 l_total := l_total + (l_end_date - l_start_date);
1033
1034 if p_processing_level = 3
1035 then
1036 exit;
1037 end if;
1038 end if;
1039 else
1040 exit;
1041 end if;
1042 else
1043 l_total := l_total + (l_end_date - l_start_date);
1044 end if;
1045 end if;
1046 end loop;
1047 --
1048 hr_utility.set_location('Leaving :'||l_proc||'. Returning '||
1049 substr(to_char(l_total),1,8)||' days.',100);
1050 --
1051 return l_total;
1052 --
1053 end total_availability;
1054 --------------------------------------------------------------------------------
1055 function AVAILABILITY (
1056 --
1057 -- Returns the availability value for an individual which is valid at a
1058 -- specified time. Eg At 8 o'clock on 12th March 1995, what is Fred Bloggs'
1059 -- availability?
1060 --
1061 p_date_and_time date,
1062 p_purpose_usage_id number,
1063 p_primary_key_value number)
1064 --
1065 return varchar2 is
1066 --
1067 this_row integer := 1;
1068 --
1069 begin
1070 --
1071 denormalise_calendar ( p_purpose_usage_id,
1072 p_primary_key_value,
1073 p_date_and_time-1,
1074 p_date_and_time+1);
1075 --
1076 -- Find the row in the internal data structure which covers the required time
1077 --
1078 while this_row <> Schedule_rowcount LOOP
1079 --
1080 exit when p_date_and_time between schedule.start_date (this_row) and schedule.end_date (this_row);
1081 --
1082 this_row := this_row +1;
1083 --
1084 end loop;
1085 --
1086 return schedule.availability (this_row);
1087 --
1088 end availability;
1089 --------------------------------------------------------------------------------
1090 function PURPOSE_USAGE_ID (
1091 --
1092 -- Return the purpose usage id for a given entity name and pattern purpose
1093 --
1094 p_entity_name varchar2,
1095 p_pattern_purpose varchar2
1096 ) return number is
1097 --
1098 cursor csr_purpose_usage is
1099 --
1100 -- Get the pattern purpose usage id
1101 --
1102 select purpose_usage_id
1103 from hr_pattern_purpose_usages
1104 where entity_name = p_entity_name
1105 and pattern_purpose = p_pattern_purpose;
1106 --
1107 l_purpose_usage_id number := null;
1108 --
1109 begin
1110 --
1111 open csr_purpose_usage;
1112 fetch csr_purpose_usage into l_purpose_usage_id;
1113 close csr_purpose_usage;
1114 --
1115 return l_purpose_usage_id;
1116 --
1117 end purpose_usage_id;
1118 --------------------------------------------------------------------------------
1119 --
1120 end hr_calendar_pkg;
[Home] [Help]