1 PACKAGE BODY HR_BUDGET_CALENDAR as
2 /* $Header: pybudcal.pkb 115.1 99/07/17 05:46:32 porting sh $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9 Name
10 hr_budget_calendar
11 Purpose
12 Maintains budgetary calendars ie. creates and removes time periods
13 representing years of calendar.
14 Notes
15 Used by the PAYWSDCL (Define Budgetary Calendar) form.
16 History
17 11-Mar-94 J.S.Hobbs 40.0 Date created.
18 ============================================================================*/
19 --
20 -- Constants to represent the basic period types.
21 WEEKLY constant varchar2(1) := 'W';
22 MONTHLY constant varchar2(1) := 'M';
23 SEMIMONTHLY constant varchar2(1) := 'S';
24 --
25 -- A record structure to hold information on a calendar.
26 type t_cal_details is record
27 (
28 period_set_name varchar2(80),
29 start_date date,
30 number_of_years number,
31 actual_period_type varchar2(30),
32 proc_period_type varchar2(30),
33 number_per_fiscal_year number,
34 midpoint_offset number,
35 base_period_type varchar2(30),
36 multiplier number,
37 start_year_number number
38 );
39 --
40 -----------------------------------------------------------------------------
41 -- Name --
42 -- midpoint_offset --
43 -- Purpose --
44 -- Returns the midpoint offset used by a semi-monthly calendar. --
45 -- Arguments --
46 -- See Below. --
47 -- Notes --
48 -- Used in the PAYWSDCL (Define Budgetary Calendar) form on post query --
49 -- to display the midpoint offset used in semi-monthly calendars. --
50 -----------------------------------------------------------------------------
51 --
52 function midpoint_offset
53 (
54 p_period_set_name varchar2,
55 p_start_date date
56 ) return number is
57 --
58 v_midpoint_offset number;
59 --
60 begin
61 --
62 -- Find the first time period for the calendar and find how many days are
63 -- between the start and end of the period.
64 begin
65 select tpe.end_date - tpe.start_date + 1
66 into v_midpoint_offset
67 from per_time_periods tpe
68 where tpe.period_set_name = p_period_set_name
69 and tpe.start_date = p_start_date;
70 exception
71 when no_data_found then
72 hr_utility.set_message(801, 'ALL_PROCEDURE_FAIL');
73 hr_utility.set_message_token('PROCEDURE',
74 'hr_budget_calendar.midpoint_offset');
75 hr_utility.set_message_token('STEP', '1');
76 hr_utility.raise_error;
77 end;
81 end midpoint_offset;
78 --
79 return v_midpoint_offset;
80 --
82 --
83 -----------------------------------------------------------------------------
84 -- Name --
85 -- num_of_cal_yrs --
86 -- Purpose --
87 -- Counts the number of calendar years that have already been created --
88 -- for a calendar. --
89 -- Arguments --
90 -- See below. --
91 -- Notes --
92 -- Used in the PAYWSDCL (Define Budgetary Calendar) form on post query --
93 -- to display the current number of calendar years created. --
94 -----------------------------------------------------------------------------
95 --
96 function num_of_cal_yrs
97 (
98 p_period_set_name varchar2
99 ) return number is
100 --
101 cursor csr_lock_calendar is
102 select cal.period_set_name
103 from pay_calendars cal
104 where cal.period_set_name = p_period_set_name
105 for update;
106 --
107 v_yr_count number := 0;
108 --
109 begin
110 --
111 -- Lock the calendar to stop other users changing the number of calendar
112 -- years.
113 open csr_lock_calendar;
114 close csr_lock_calendar;
115 --
116 -- Count the number of existing periods for the calendar.
117 begin
118 select count(*)
119 into v_yr_count
120 from per_time_period_sets tps
121 where tps.period_set_name = p_period_set_name;
122 exception
123 when no_data_found then null;
124 end;
125 --
126 return (v_yr_count);
127 --
128 end num_of_cal_yrs;
129 --
130 -----------------------------------------------------------------------------
131 -- Name --
132 -- Ins_time_period --
133 -- Purpose --
134 -- Create a time period for a calendar. --
135 -- Arguments --
136 -- See Below. --
137 -- Notes --
138 -- None. --
139 -----------------------------------------------------------------------------
140 --
141 procedure Ins_time_period
142 (
143 p_period_set_name varchar2,
144 p_prd_type varchar2,
145 p_yr_num number,
146 p_qtr_num number,
147 p_prd_num number,
148 p_prd_start_date date,
149 p_prd_end_date date
150 ) is
151 --
152 begin
153 --
154 -- Creates a single time period for a calendar if the time period to be
155 -- created does not overlap with an existing time period for the calendar.
156 insert into per_time_periods
157 (time_period_id,
158 period_set_name,
159 period_name,
160 period_type,
161 year_number,
162 period_year,
163 quarter_num,
164 period_num,
165 start_date,
166 end_date,
167 last_update_date,
168 last_updated_by,
169 last_update_login,
170 created_by,
171 creation_date)
172 select
173 per_time_periods_s.nextval,
174 p_period_set_name,
175 to_char(p_prd_num) || ' ' || to_char(p_prd_end_date, 'YYYY') || ' ' ||
176 p_prd_type,
177 p_prd_type,
178 fnd_number.canonical_to_number(to_char(p_prd_end_date, 'YYYY')),
179 p_yr_num,
180 p_qtr_num,
181 p_prd_num,
182 p_prd_start_date,
183 p_prd_end_date,
184 trunc(sysdate),
185 0,
186 0,
187 0,
188 trunc(sysdate)
189 from sys.dual;
190 --
191 end ins_time_period;
192 --
193 -----------------------------------------------------------------------------
194 -- Name --
195 -- time_period_end_date --
196 -- Purpose --
197 -- Calculates the end date of a period. --
198 -- Arguments --
199 -- See Notes. --
200 -- Notes --
201 -- All period types except Semi-Monthly are defined in terms of a base --
202 -- period type ie. week or month and a multiplier that determines how --
203 -- many of the base period type make up a period. --
204 -- eg. Semi-Year == 6 x month, Fortnight == 2 x week etc ... --
205 -- Given the base period type and the multiplier it is possible to --
206 -- calculate the end date of a period given its start date. --
207 -----------------------------------------------------------------------------
208 --
209 function time_period_end_date
210 (
211 p_period_start_date date,
212 p_base_period_type varchar2,
213 p_multiplier number,
214 p_midpoint_offset number,
215 p_saved_end_date in out date
216 ) return date is
217 --
218 v_prd_end_date date;
219 --
220 begin
221 --
225 --
222 -- Period type is defined in terms of multiples of weeks eg. fortnight etc...
223 if p_base_period_type = WEEKLY then
224 return (p_period_start_date + (7 * p_multiplier) - 1);
226 -- Period type is defined in terms of multiples of months eg. bi-month etc...
227 elsif p_base_period_type = MONTHLY then
228 return (add_months(p_period_start_date, p_multiplier) - 1);
229 --
230 -- Period type is semi-month ie. each month is split into 2 halves divided
231 -- by the midpoint offset eg. 01-jan-1990 - 15-jan-1990, 16-jan-1990 -
232 -- 31-jan-1990 etc ... where the midpoint offset would be 15. The midpoint
233 -- offset is added to the start date of the fist half and is not absolote
234 -- ie. a midpoint offset of 15 does not mean that the first half will
235 -- always end on the 15th of the month.
236 else
237 --
238 -- To simplify processing of semi-monthly calendars both halves of the
239 -- period are calculated together. The first half end date is returned
240 -- and the second half is saved. When the function is called again the
241 -- second half end date is returned.
242 if p_saved_end_date is not null then
243 --
244 v_prd_end_date := p_saved_end_date;
245 p_saved_end_date := null;
246 return v_prd_end_date;
247 --
248 else
249 --
250 p_saved_end_date := add_months(p_period_start_date,1) - 1;
251 return (p_period_start_date + p_midpoint_offset - 1);
252 --
253 end if;
254 --
255 end if;
256 --
257 end time_period_end_date;
258 --
259 -----------------------------------------------------------------------------
260 -- Name --
261 -- get_cal_details --
262 -- Purpose --
263 -- Builds up a record containing information on the calendar. This is --
264 -- required to be able to generate the calendar. --
265 -- Arguments --
266 -- See Below. --
267 -- Notes --
268 -- None. --
269 -----------------------------------------------------------------------------
270 --
271 function get_cal_details
272 (
273 p_period_set_name varchar2,
274 p_midpoint_offset number,
275 p_number_of_years number
276 ) return t_cal_details is
277 --
278 v_cal_details t_cal_details;
279 v_num_yrs number;
280 --
281 begin
282 --
283 v_cal_details.period_set_name := p_period_set_name;
284 v_cal_details.midpoint_offset := p_midpoint_offset;
285 --
286 begin
287 select cal.start_date,
288 cal.actual_period_type,
289 cal.proc_period_type,
290 tpt.number_per_fiscal_year
291 into v_cal_details.start_date,
292 v_cal_details.actual_period_type,
293 v_cal_details.proc_period_type,
294 v_cal_details.number_per_fiscal_year
295 from pay_calendars cal,
296 per_time_period_types tpt
297 where cal.period_set_name = p_period_set_name
298 and tpt.period_type = cal.actual_period_type;
299 exception
300 when no_data_found then
301 hr_utility.set_message(801, 'ALL_PROCEDURE_FAIL');
302 hr_utility.set_message_token('PROCEDURE',
303 'hr_budget_calendar.get_cal_details');
304 hr_utility.set_message_token('STEP', '1');
305 hr_utility.raise_error;
306 end;
307 --
308 -- Find the base period type and multiplier for the period type.
309 hr_payrolls.get_period_details
310 (v_cal_details.actual_period_type,
311 v_cal_details.base_period_type,
312 v_cal_details.multiplier);
313 --
314 -- See how many years of the calendar have already been defined.
315 v_num_yrs := num_of_cal_yrs(v_cal_details.period_set_name);
316
317 -- If there have not been any calendar years created yet then it is not
318 -- necessary to change the definition of the calendar ie. the calendar
319 -- start date and number of years can be used.
320 -- If a number of years have already been created then the definition of the
321 -- calendar has to be changed so that only the new calendar years are
322 -- created ie. if 5 years of calendar exist and the number of years is now
323 -- 7 years then it is only necessary to create 2 years of calendar starting
324 -- from when the 5 years of calendar finished.
325 -- If the number of years is actually less than or the same as the number
326 -- of existing years then no action needs to be taken.
327 if v_num_yrs = 0 then
328 --
329 v_cal_details.number_of_years := p_number_of_years;
330 v_cal_details.start_year_number := 1;
331 --
332 elsif v_num_yrs < p_number_of_years then
333 --
334 -- Find how many years of time periods need to be created.
335 v_cal_details.number_of_years := p_number_of_years - v_num_yrs;
336 --
337 -- Set the start date of the calendar to be the day after the last time
338 -- period.
339 select max(tpe.end_date) + 1
340 into v_cal_details.start_date
341 from per_time_periods tpe
342 where tpe.period_set_name = v_cal_details.period_set_name;
343 --
344 v_cal_details.start_year_number := v_num_yrs + 1;
345 --
346 else
347 --
348 v_cal_details.number_of_years := 0;
349 --
350 end if;
351 --
352 return v_cal_details;
353 --
354 end get_cal_details;
355 --
356 -----------------------------------------------------------------------------
360 -- Generates a number of years of time periods for a calendar. --
357 -- Name --
358 -- generate --
359 -- Purpose --
361 -- Arguments --
362 -- p_number_of_years should be the number of calendar years that exist --
363 -- after the code has completed. --
364 -- Notes --
365 -- None. --
366 -----------------------------------------------------------------------------
367 --
368 procedure generate
369 (
370 p_period_set_name varchar2,
371 p_midpoint_offset number,
372 p_number_of_years number
373 ) is
374 --
375 v_cal_details t_cal_details;
376 v_yr_created boolean := FALSE;
377 v_yr_num number := 0;
378 v_qtr_num number;
379 v_prd_num number := 0;
380 v_prd_start_date date;
381 v_prd_end_date date;
382 v_yr_start_date date;
383 v_yr_end_date date;
384 v_1st_qtr_end_date date;
385 v_2nd_qtr_end_date date;
386 v_3rd_qtr_end_date date;
387 v_4th_qtr_end_date date;
388 v_saved_prd_end_date date;
389 --
390 begin
391 --
392 -- Fetch calendar information required to generate the calendar years ie.
393 -- basic period type, number per fiscal year etc ...
394 v_cal_details := get_cal_details(p_period_set_name,
395 p_midpoint_offset,
396 p_number_of_years);
397 --
398 -- No calendar years need to be created so exit procedure.
399 if v_cal_details.number_of_years = 0 then
400 return;
401 end if;
402 --
403 v_yr_num := v_cal_details.start_year_number;
404 v_yr_start_date := v_cal_details.start_date;
405 --
406 -- Create v_num_yrs years of time periods for the calendar.
407 for v_yr_count in 1..v_cal_details.number_of_years loop
408 --
409 v_yr_created := FALSE;
410 v_yr_end_date := add_months(v_yr_start_date, 12) - 1;
411 v_prd_num := 1;
412 v_prd_start_date := v_yr_start_date;
413 --
414 -- Calculate the quarter end dates for later use in calculating which
415 -- quarter a period lies in.
416 v_1st_qtr_end_date := add_months(v_yr_start_date,3) - 1;
417 v_2nd_qtr_end_date := add_months(v_yr_start_date,6) - 1;
418 v_3rd_qtr_end_date := add_months(v_yr_start_date,9) - 1;
419 v_4th_qtr_end_date := add_months(v_yr_start_date,12) - 1;
420 --
421 -- Create a calendar year. This is to allow the 2.3 form to query back
422 -- calendars created by the 4.0 form and vice versa.
423 insert into per_time_period_sets
424 (start_date,
425 period_set_name,
426 period_type,
427 end_date_q1,
428 end_date_q2,
429 end_date_q3,
430 end_date_q4,
431 month_mid_day,
432 year_number)
433 values
434 (v_yr_start_date,
435 v_cal_details.period_set_name,
436 v_cal_details.actual_period_type,
437 v_1st_qtr_end_date,
438 v_2nd_qtr_end_date,
439 v_3rd_qtr_end_date,
440 v_4th_qtr_end_date,
441 v_cal_details.midpoint_offset,
442 v_yr_num);
443 --
444 -- Keep looping until a calendars year of time periods have been created.
445 loop
446 --
447 -- Get the end date of the period about to be created.
448 v_prd_end_date := time_period_end_date
449 (v_prd_start_date,
450 v_cal_details.base_period_type,
451 v_cal_details.multiplier,
452 v_cal_details.midpoint_offset,
453 v_saved_prd_end_date);
454 --
455 -- If the period ends on or overlaps with the end of the calendar year
456 -- then adjust its end date so it falls within the calendar year and
457 -- also flag that a calendar year of time periods has been created.
458 if v_prd_end_date >= v_yr_end_date then
459 v_prd_end_date := v_yr_end_date;
460 v_yr_created := TRUE;
461 end if;
462 --
463 -- Set the quarter number to the quarter in which the end of the
464 -- period falls NB. for period types with a number per fiscal year < 4
465 -- then there will not be enough periods to divide between the quarters.
466 if v_cal_details.number_per_fiscal_year >= 4 then
467 if v_prd_end_date between v_yr_start_date and
468 v_1st_qtr_end_date then
469 v_qtr_num := 1;
470 elsif v_prd_end_date between v_1st_qtr_end_date + 1 and
471 v_2nd_qtr_end_date then
472 v_qtr_num := 2;
473 elsif v_prd_end_date between v_2nd_qtr_end_date + 1 and
474 v_3rd_qtr_end_date then
475 v_qtr_num := 3;
476 else
477 v_qtr_num := 4;
478 end if;
479 end if;
480 --
481 -- Create time period.
482 ins_time_period
483 (v_cal_details.period_set_name,
484 v_cal_details.actual_period_type,
485 v_yr_num,
486 v_qtr_num,
487 v_prd_num,
488 v_prd_start_date,
489 v_prd_end_date);
490 --
491 exit when v_yr_created;
492 --
493 -- Increment the period number for the next period and also calculate
494 -- the start date of the next period.
495 v_prd_num := v_prd_num + 1;
496 v_prd_start_date := v_prd_end_date + 1;
497 --
498 end loop;
499 --
500 v_yr_num := v_yr_num + 1;
501 v_yr_start_date := v_prd_end_date + 1;
502 --
503 end loop;
504 --
505 end generate;
506 --
507 -----------------------------------------------------------------------------
508 -- Name --
509 -- remove --
510 -- Purpose --
511 -- Removes a number of years of time periods for a calendar. --
512 -- Arguments --
513 -- p_number_of_years should be the number of calendar years that exist --
514 -- after the code has completed. --
515 -- Notes --
516 -- None. --
517 -----------------------------------------------------------------------------
518 --
519 procedure remove
520 (
521 p_period_set_name varchar2,
522 p_number_of_years number,
523 p_at_least_one_year boolean
524 ) is
525 --
526 cursor csr_budget_values is
527 select tpe.time_period_id
528 from per_time_periods tpe
529 where tpe.period_set_name = p_period_set_name
530 and tpe.period_year > p_number_of_years
531 and exists
532 (select null
533 from per_budget_values bv
534 where bv.time_period_id = tpe.time_period_id);
535 --
536 v_time_period_id number;
537 --
538 begin
539 --
540 -- At least one year of time periods must exist for the calendar unless the
541 -- calendar is being removed where all time period need to be removed.
542 if p_at_least_one_year and p_number_of_years < 1 then
543 hr_utility.set_message(801, 'HR_7087_TIME_ONE_YR_AT_LEAST');
544 hr_utility.raise_error;
545 else
546 --
547 -- See if any of the time periods to be removed are used by a budget.
548 open csr_budget_values;
549 fetch csr_budget_values into v_time_period_id;
550 if csr_budget_values%found then
551 close csr_budget_values;
552 hr_utility.set_message(801, 'HR_7088_TIME_USED_IN_BUDGET');
553 hr_utility.raise_error;
554 else
555 close csr_budget_values;
556 end if;
557 --
558 -- Remove all time periods that exist in years greater than the new
559 -- number of years.
560 delete from per_time_periods tpe
561 where tpe.period_set_name = p_period_set_name
562 and tpe.period_year > p_number_of_years;
563 --
564 -- Remove calendar years.
565 delete from per_time_period_sets tps
566 where tps.period_set_name = p_period_set_name
567 and tps.year_number > p_number_of_years;
568 --
569 end if;
570 --
571 end remove;
572 --
573 end HR_BUDGET_CALENDAR;
[Home] [Help]