1 package body hr_payrolls as
2 /* $Header: pypyroll.pkb 120.1.12010000.2 2009/09/03 04:34:49 sivanara ship $ */
3 --
4 -- Package constants, denoting the base period types.
5 --
6 WEEKLY CONSTANT varchar2(1) := 'W';
7 MONTHLY CONSTANT varchar2(1) := 'M';
8 SEMIMONTHLY CONSTANT varchar2(1) := 'S';
9 TEN_DAYS CONSTANT varchar2(1) := 'T'; -- Bug 5935976
10 --
11 -- Disable/enable the period name display fetch inline with the view
12 g_enable_period_name_fetch BOOLEAN := TRUE;
13 --
14 --
15 -- Warning variables used by the form
16 g_weeks_reset_warn BOOLEAN := FALSE;
17 g_end_date_changed_warn BOOLEAN := FALSE;
18 g_no_of_weeks_reset NUMBER := 0;
19 g_reset_period_start_date per_time_periods.start_date%type;
20 g_reset_period_end_date per_time_periods.end_date%type;
21 g_reset_period_name per_time_periods.period_name%type;
22 g_new_end_date per_time_periods.end_date%type;
23 g_constant_end_date BOOLEAN := FALSE;
24 --
25 -- This procedure does not currently use PER_TIME_PERIOD_RULES, since that
26 -- table is subject to some change.
27 --
28 procedure get_period_details (p_proc_period_type in varchar2,
29 p_base_period_type out nocopy varchar2,
30 p_multiple out nocopy number) is
31 --
32 proc_name CONSTANT varchar2(50) := 'hr_payrolls.get_period_details';
33 --
34 no_periods per_time_period_types.number_per_fiscal_year%type;
35 --
36 begin
37 hr_utility.set_location(proc_name, 1);
38 select tp.number_per_fiscal_year
39 into no_periods
40 from per_time_period_types tp
41 where tp.period_type = p_proc_period_type;
42 --
43 -- Use the number of periods in a fiscal year to deduce the base
44 -- period and multiple.
45 if no_periods = 1 then -- Yearly
46 p_base_period_type := MONTHLY;
47 p_multiple := 12;
48 elsif no_periods = 2 then -- Semi yearly
49 p_base_period_type := MONTHLY;
50 p_multiple := 6;
51 elsif no_periods = 4 then -- Quarterly
52 p_base_period_type := MONTHLY;
53 p_multiple := 3;
54 elsif no_periods = 6 then -- Bi monthly
55 p_base_period_type := MONTHLY;
56 p_multiple := 2;
57 elsif no_periods = 12 then -- Monthly
58 p_base_period_type := MONTHLY;
59 p_multiple := 1;
60 elsif no_periods = 13 then -- Lunar monthly
61 p_base_period_type := WEEKLY;
62 p_multiple := 4;
63 elsif no_periods = 24 then -- Semi monthly
64 p_base_period_type := SEMIMONTHLY;
65 p_multiple := 1; -- Not used for semi-monthly
66 elsif no_periods = 26 then -- Fortnightly
67 p_base_period_type := WEEKLY;
68 p_multiple := 2;
69 elsif no_periods = 36 then -- 10 Day --Bug 5935976
70 p_base_period_type := TEN_DAYS;
71 p_multiple := 1;
72 elsif no_periods = 52 then -- Weekly
73 p_base_period_type := WEEKLY;
74 p_multiple := 1;
75 else
76 -- Unknown period type.
77 hr_utility.set_message(801, 'PAY_6601_PAYROLL_INV_PERIOD_TP');
78 hr_utility.raise_error;
79 end if;
80 --
81 end get_period_details;
82 --
83 -- Locally defined function that, given the end-date of a semi-month
84 -- period and the first period's end-date (p_fpe_date) returns
85 -- the end date of the following semi-monthly period.
86 --
87 function next_semi_month(p_semi_month_date in date, p_fpe_date in date)
88 return date is
89 day_of_month varchar2(2);
90 last_of_month date;
91 temp_day varchar2(2);
92 func_name CONSTANT varchar2(50) := 'hr_payrolls.next_semi_month';
93 begin
94 hr_utility.set_location(func_name, 1);
95 day_of_month := substr(to_char(p_fpe_date, 'DD-MON-YYYY'), 1, 2);
96 if (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) then
97 -- The first period's end-date is either the 15th or the end-of-month
98 if last_day(p_semi_month_date) = p_semi_month_date then
99 -- End of month: add 15 days
100 return(p_semi_month_date + 15);
101 else
102 -- 15th of month: return last day
103 return(last_day(p_semi_month_date));
104 end if;
105 else
106 -- The first period's end-date is neither the 15th nor the end-of-month
107 -- temp_day = smaller of the 2 day numbers used to calc period end-dates
108 temp_day := day_of_month ;
109 if temp_day > '15' then
110 temp_day := substr(to_char(p_fpe_date - 15, 'DD-MON-YYYY'), 1, 2);
111 end if ;
112 --
113 day_of_month := substr(to_char(p_semi_month_date, 'DD-MON-YYYY'), 1, 2);
114 if day_of_month between '01' AND '15' then
115 if last_day(p_semi_month_date+15) = last_day(p_semi_month_date) then
116 return(p_semi_month_date + 15);
117 else
118 -- for p_semi_month_date = Feb 14th, for example
119 return(last_day(p_semi_month_date));
120 end if;
121 else -- if on the 16th or later
122 return(to_date( (temp_day ||
123 substr(to_char(add_months(p_semi_month_date,1),'DD-MON-YYYY'),3)
124 ), 'DD-MON-YYYY'));
125 end if ;
126 end if ;
127 end next_semi_month;
128 --
129 -- Locally defined function that, given the end-date of a semi-month
130 -- period and the first period's end-date (p_fpe_date) returns
131 -- the end date of the previous semi-monthly period.
132 --
133 function prev_semi_month(p_semi_month_date in date, p_fpe_date in date)
134 return date is
135 day_of_month varchar2(2);
136 last_of_month date;
137 temp_date date;
138 temp_day varchar2(2);
139 func_name CONSTANT varchar2(50) := 'hr_payrolls.prev_semi_month';
140 begin
141 -- Get the day of the current month.
142 hr_utility.set_location(func_name, 1);
143 day_of_month := substr(to_char(p_fpe_date, 'DD-MON-YYYY'), 1, 2);
144 if (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) then
145 -- The first period's end-date is either the 15th or the end-of-month
146 if last_day(p_semi_month_date) = p_semi_month_date then
147 -- End of month: return 15th of current month
148 return(add_months(p_semi_month_date,-1) + 15);
149 else
150 -- 15th of month: return last day of previous month
151 return(last_day(add_months(p_semi_month_date,-1)));
152 end if;
153 else
154 -- The first period's end-date is neither the 15th nor the end-of-month
155 -- temp_day = smaller of the 2 day numbers used to calc period end-dates
156 temp_day := day_of_month ;
157 if temp_day > '15' then
158 temp_day := substr(to_char(p_fpe_date - 15, 'DD-MON-YYYY'), 1, 2);
159 end if ;
160 --
161 day_of_month := substr(to_char(p_semi_month_date, 'DD-MON-YYYY'), 1, 2);
162 if day_of_month between '01' AND '15' then
163 temp_date := add_months (p_semi_month_date, -1) ;
164 if last_day(temp_date+15) = last_day(temp_date) then
165 return(temp_date + 15);
166 else
167 -- for p_semi_month_date = Mar 14th, for example
168 return(last_day(temp_date));
169 end if;
170 else -- if after the 16th
171 return(to_date( (temp_day ||
172 substr(to_char(p_semi_month_date,'DD-MON-YYYY'),3)
173 ), 'DD-MON-YYYY'));
174 end if ;
175 end if ;
176 end prev_semi_month;
177 --
178 --
179 -- This function is a customized version of add_months function to
180 -- suit the requirement where an option has to be provided to the user
181 -- to create payroll periods with the same end day as the first period.
182 -- This is controlled by the global variable g_constant_end_date.
183 -- If g_constant_end_date is not true then the result of add_months is
184 -- returned back. If g_constant_end_date is true then a period end date
185 -- which has the day as in the first period end date is determined.
186 -- This value is returned if such a date is valid for the period else
187 -- the last day in the period is returned. Bug #3697834.
188 --
189 FUNCTION customized_add_months ( p_date_in in DATE,
190 p_first_period_end_date in DATE,
191 p_months_shift in NUMBER )
192 RETURN DATE IS
193 l_return_value DATE;
194 l_day_of_month VARCHAR2(2);
195 l_month_year VARCHAR2(6);
196 l_end_of_month DATE;
197 BEGIN
198
199 l_return_value := add_months(p_date_in, p_months_shift);
200
201 if g_constant_end_date then
202
203 -- Pull out the day number of the first period end date
204 l_day_of_month := least ( to_number(to_char(p_first_period_end_date,'DD')), to_number(to_char(last_day ( l_return_value ) , 'DD'))) ;
205
206 -- Grab the month and year of the new date
207 l_month_year := to_char(l_return_value, 'MMYYYY');
208
209 -- Combine these components into an actual date
210
211 l_end_of_month := to_date(l_month_year || l_day_of_month, 'MMYYYYDD');
212
213
214 -- Return the earliest of
215 -- (a) the normal result of ADD_MONTHS
216 -- and (b) the same day in the new period as in the first period end date.
217
218 l_return_value := LEAST (l_return_value, l_end_of_month);
219
220 end if;
221
222 return l_return_value;
223
224 END customized_add_months;
225 --
226 -- The period numbers and period names are derived using regular pay date.
227 -- Since leap years have an extra day in February,
228 -- for some offsets the period numbers derived might not be consistent.
229 -- To avoid this an adjustment is done to the regualar pay date in
230 -- leap years to ignore the extra day in february.
231 -- This function helps in finding out if the adjustment is required or not
232 -- by returning either -1 , 1 or 0. The value returned is added to the
233 -- normal regual pay date before using it for deriving period numbers.
234 -- This is applicable only for payrolls with base period type as MONTHLY.
235 -- Bug # 3663126.
236
237 FUNCTION get_leap_year_offset ( p_period_end_date in date,
238 p_pay_date_offset in number )
239 RETURN NUMBER IS
240 --
241 l_year varchar2(4);
242 l_start_date date;
243 l_end_date date;
244 l_date date;
245 l_return_value number;
246 --
247 BEGIN
248
249 l_year := to_char( p_period_end_date, 'YYYY');
250 l_return_value := 0;
251
252 -- Determine if this is a leap year.
253
254 if ( mod(to_number(l_year), 4) = 0 and mod(to_number(l_year), 100) <> 0 ) or
255 ( mod(to_number(l_year), 4) = 0 and mod(to_number(l_year), 400) = 0 ) then
256
257 l_date := to_date('2902'|| l_year ,'DDMMYYYY');
258
259 if p_pay_date_offset < 0 then
260
261 l_start_date := p_period_end_date + p_pay_date_offset;
262 l_end_date := p_period_end_date;
263
264 if l_date >= l_start_date and l_date <= l_end_date then
265 l_return_value := -1;
266 end if;
267 else
268 l_start_date := p_period_end_date;
269 l_end_date := p_period_end_date + p_pay_date_offset;
270
271 if l_date > l_start_date and l_date <= l_end_date then
272 l_return_value := 1;
273 end if;
274 end if;
275 end if;
276
277 return l_return_value;
278
279 END get_leap_year_offset;
280 --
281 --
282 -- This function performs the date calculation according to the
283 -- base period type for the period type being considered.
284 -- Note that for WEEKLY base period, the calculation can be
285 -- performed by adding days ie. straightforward addition operation.
286 -- For MONTHLY base period, the add_months() function is used.
287 -- The exception to these general categories is semi-monthly,
288 -- which is handled explicitly by the SEMIMONTHLY base period.
289 -- This case only uses the p_multiple parameter to determine whether
290 -- to add or subtract a semi-month, ie. this function can only
291 -- increase semi-months unitarily. Furthermore, it has exclusive use
292 -- of the 'regular_pay_mode' parameter, which for SEMIMONTHLY,
293 -- indicates whether or not we are calculating for a regular payment
294 -- date calculation. The p_fpe_date is the first period's end-date
295 -- and is needed only for semimonthly period calculations.
296 --
297 function add_multiple_of_base (p_target_date in date,
298 p_base_period_type in varchar2,
299 p_multiple in number,
300 p_fpe_date in date,
301 p_regular_pay_mode boolean default false,
302 p_pay_date_offset number default null)
303 return date is
304 --
305 func_name CONSTANT varchar2(50) := 'hr_payrolls.add_multiple_of_base';
306 rest_of_date varchar2(9);
307 temp_date date;
308 -- Bug 5935976 for 10 Payroll
309 l_date2 date;
310 l_day varchar2(10);
311 l_month varchar2(10);
312 l_year varchar2(10);
313 l_start_day varchar2(10);
314 --
315 begin
316 -- Errors can occur when performing date manipulation.
317 if p_base_period_type = WEEKLY then
318 hr_utility.set_location(func_name, 1);
319 return (p_target_date + (7 * p_multiple));
320 elsif p_base_period_type = MONTHLY then
321 hr_utility.set_location(func_name, 2);
322
323 -- Replacing add_months with customized_add_months for Bug #3697834.
324
325 if not p_regular_pay_mode then
326 temp_date := customized_add_months( p_date_in => p_target_date,
327 p_first_period_end_date => p_fpe_date,
328 p_months_shift => p_multiple );
329
330 return temp_date;
331 else
332 temp_date := p_target_date - p_pay_date_offset;
333 temp_date := customized_add_months( p_date_in => temp_date,
334 p_first_period_end_date => p_fpe_date,
335 p_months_shift => p_multiple );
336 return ( temp_date + p_pay_date_offset );
337 end if;
338 -- added following code for 10 day Payroll Bug #5935976
339 elsif p_base_period_type = TEN_DAYS then
340
341 hr_utility.set_location(func_name, 3);
342 l_day := to_char(p_target_date, 'DD');
343 l_month := to_char(p_target_date, 'MM');
344 l_year := to_char(p_target_date, 'YYYY');
345 l_start_day :='01' ;
346
347 if p_multiple > 0 then
348 -- return 10, 20, last_day
349 -- go front and return end date
350 if l_day = 20 then
351 l_date2 := last_day(p_target_date);
352 else
353 l_date2 := p_target_date + 10;
354 end if;
355 else
356 -- return 1, 11,21
357 -- go back and return start date
358 if p_target_date = last_day(p_target_date) then
359 l_start_day := '21';
360 l_date2 := to_date( l_start_day ||'-'|| l_month ||'-' ||l_year, 'DD-MM-YYYY');
361 else
362 l_date2 := p_target_date -9;
363 end if;
364 end if;
365 return (l_date2 );
366 --
367 else
368 -- This is semi-monthly. A pair of semi-months always spand
369 -- a whole calendar month. Their start and end dates are either
370 -- 1st - 15th or 16th - last day of month. This makes the
371 -- addition/subtraction of a period reasonably straightforward,
372 -- if a little involved.
373 -- On the other hand, if the p_regular_pay_mode is TRUE, we
374 -- have to take into account the regular pay date offset.
375 if not p_regular_pay_mode then
376 if p_multiple > 0 then
377 -- Addition of one semi-month.
378 return(next_semi_month(p_target_date, p_fpe_date));
379 else
380 -- Substraction of one semi-month.
381 return(prev_semi_month(p_target_date, p_fpe_date));
382 end if;
383 else
384 -- Special calculation for regular payment date offset.
385 -- In this special case, we calculate the pay date of the previous
386 -- semimonth period: Current pay-date -> Current end-date ->
387 -- Previous period's end-date -> Previous period's pay-date.
388 temp_date := prev_semi_month (p_target_date - p_pay_date_offset,
389 p_fpe_date) + p_pay_date_offset ;
390 hr_utility.trace('pay date ' || to_char(temp_date, 'dd-mon-yyyy'));
391 return(temp_date);
392 -- OLD CODE
393 -- Now period's are not guaranteed to end on the 15th or the end_of_month.
394 --
395 -- rest_of_date := substr(to_char(p_target_date, 'DD-MON-YYYY'), 3);
396 -- if p_target_date between
397 -- to_date(('01' || rest_of_date), 'DD-MON-YYYY') and
398 -- to_date(('15' || rest_of_date), 'DD-MON-YYYY')
399 -- then
400 -- -- First half of month, put to 15th and subtract a period.
401 -- temp_date := to_date(('15' || rest_of_date), 'DD-MON-YYYY');
402 -- temp_date := prev_semi_month(temp_date) + p_pay_date_offset;
403 -- hr_utility.trace('1st half ' || to_char(temp_date, 'dd-mon-yyyy'));
404 -- return(temp_date);
405 -- else
406 -- -- We are in second half of month.
407 -- temp_date := last_day(p_target_date);
408 -- temp_date := prev_semi_month(temp_date) + p_pay_date_offset;
409 -- hr_utility.trace('2nd half ' || to_char(temp_date, 'dd-mon-yyyy'));
410 -- return(temp_date);
411 -- end if;
412 -- OLD CODE
413 end if;
414 end if;
415 end add_multiple_of_base;
416 --
417 --
418 -- This procedure determines the period information that is not
419 -- held directly on the payroll itself, namely:
420 --
421 -- 1. Start date of first period for payroll.
422 -- 2. Start date of first period to be generated.
423 -- 3. End date of first period to be generated.
424 --
425 -- Note that it relies on the base period type, multiple and semi-month
426 -- count on the payroll details record being populated.
427 --
428 procedure derive_payroll_dates(p_pay_det in out nocopy payroll_rec_type) is
429 --
430 proc_name CONSTANT varchar2(50) := 'hr_payrolls.derive_payroll_dates';
431 --
432 earliest_start_date date;
433 latest_end_date date;
434 no_periods number;
435 --
436 begin
437 -- Find the start and end dates of the earliest and latest periods
438 -- generated for the payroll.
439 hr_utility.set_location(proc_name, 1);
440 select min(start_date),
441 max(end_date),
442 count(time_period_id)
443 into earliest_start_date,
444 latest_end_date,
445 no_periods
446 from per_time_periods ptp
447 where ptp.payroll_id = p_pay_det.payroll_id;
448 --
449 if no_periods = 0 then
450 --
451 -- No periods have been generated for the payroll before.
452 --
453 --
454 -- The start date must be derived.
455 hr_utility.set_location(proc_name, 2);
456 -- added for 10 day payroll Bug 5935976
457 if p_pay_det.base_period_type = TEN_DAYS then
458 p_pay_det.first_start_date
459 := add_multiple_of_base(p_pay_det.first_end_date,
460 p_pay_det.base_period_type,
461 -(p_pay_det.multiple),
462 p_pay_det.first_end_date) ;
463 else
464 p_pay_det.first_start_date
465 := add_multiple_of_base(p_pay_det.first_end_date,
466 p_pay_det.base_period_type,
467 -(p_pay_det.multiple),
468 p_pay_det.first_end_date) + 1;
469 end if;
470 --
471 -- The generated start and end dates will be the same as
472 -- those of the earliest payroll period.
473 p_pay_det.first_gen_start_date := p_pay_det.first_start_date;
474 p_pay_det.first_gen_end_date := p_pay_det.first_end_date;
475 else
476 --
477 -- Periods have been generated for the payroll before.
478 --
479 hr_utility.set_location(proc_name, 3);
480 p_pay_det.first_gen_start_date := latest_end_date + 1;
481 p_pay_det.first_start_date := earliest_start_date;
482 --
483 -- The end date of the first period to be generated must be
484 -- derived.
485 hr_utility.set_location(proc_name, 4);
486 p_pay_det.first_gen_end_date
487 := add_multiple_of_base((p_pay_det.first_gen_start_date)-1,
488 p_pay_det.base_period_type,
489 p_pay_det.multiple,
490 p_pay_det.first_end_date) ;
491 end if;
492 --
493 end derive_payroll_dates;
494 --
495 PROCEDURE chk_reset ( p_payroll_details in payroll_rec_type
496 ,p_period_number in per_time_periods.period_num%type
497 ,p_no_of_periods in per_time_period_types.number_per_fiscal_year%type
498 ,p_period_start_date in per_time_periods.start_date%type
499 ,p_next_leg_start_date in date
500 ,p_period_end_date in out nocopy per_time_periods.end_date%type
501 ,p_no_of_weeks_reset in out nocopy number ) IS
502 --
503 proc_name CONSTANT varchar2(50) := 'hr_payrolls.chk_reset';
504 --
505 --
506 l_no_of_weeks number := 0;
507 l_week_number number;
508 l_year_end_date date;
509 l_next_period_start_date date;
510 l_next_period_end_date date;
511 l_last_period boolean := false;
512 --
513 CURSOR csr_week_number (p_date in date) IS
514 select to_char(p_date,'IW') from dual;
515 --
516 BEGIN
517 --
518 hr_utility.set_location(proc_name, 1);
519 --
520 -- Reset the Payroll Periods for Lunar Month
521 --
522
523 -- Check if this is the last period of the financial year
524
525 l_next_period_end_date := add_multiple_of_base(p_period_end_date,
526 p_payroll_details.base_period_type,
527 p_payroll_details.multiple,
528 p_payroll_details.first_end_date);
529
530 -- Determine the period number of the this period.
531
532 if (l_next_period_end_date + p_payroll_details.pay_date_offset - (7 * p_no_of_weeks_reset))
533 >= p_next_leg_start_date then
534 l_last_period := true;
535 else
536 l_last_period := false;
537 end if;
538
539 if (((l_last_period = true and p_payroll_details.period_reset_years = 'L')
540 or (p_period_number = 1 and p_payroll_details.period_reset_years = 'F'))
541 and p_no_of_periods = 13 ) then
542
543 l_year_end_date := to_date('31/12/' ||
544 to_char(p_period_end_date, 'YYYY'), 'DD/MM/YYYY');
545
546 open csr_week_number(l_year_end_date);
547 fetch csr_week_number into l_no_of_weeks;
548 close csr_week_number;
549
550 -- Continue only if the year has 53 ISO Weeks
551
552 if l_no_of_weeks = 53 then
553
554 if p_payroll_details.period_reset_years = 'F' then
555
556 p_period_end_date := add_multiple_of_base(p_period_end_date,
557 p_payroll_details.base_period_type,
558 p_payroll_details.multiple * 12,
559 p_payroll_details.first_end_date);
560
561 end if;
562
563 l_next_period_start_date := p_period_end_date + 1;
564
565 open csr_week_number(l_next_period_start_date);
566 fetch csr_week_number into l_week_number;
567 close csr_week_number;
568
569 -- Calculate the number of weeks to be reset
570 p_no_of_weeks_reset := 0;
571 while l_week_number <> 1 and l_next_period_start_date < p_next_leg_start_date loop
572
573 p_period_end_date := p_period_end_date + 7;
574 l_next_period_start_date := p_period_end_date + 1;
575 p_no_of_weeks_reset := p_no_of_weeks_reset + 1;
576
577 open csr_week_number(l_next_period_start_date);
578 fetch csr_week_number into l_week_number;
579 close csr_week_number;
580
581 end loop;
582
583 if p_payroll_details.period_reset_years = 'F' then
584
585 p_period_end_date := add_multiple_of_base(p_period_end_date - (p_no_of_weeks_reset * 7),
586 p_payroll_details.base_period_type,
587 -(p_payroll_details.multiple * 12),
588 p_payroll_details.first_end_date);
589
590 p_period_end_date := p_period_end_date + (p_no_of_weeks_reset * 7);
591
592 end if;
593
594 if p_no_of_weeks_reset > 1 then
595
596 -- Set the warning variables
597 g_weeks_reset_warn := true;
598 g_reset_period_start_date := p_period_start_date;
599 g_reset_period_end_date := p_period_end_date;
600 g_no_of_weeks_reset := p_no_of_weeks_reset;
601
602 end if;
603
604 end if;
605 end if;
606
607 hr_utility.set_location(proc_name, 5);
608 --
609 END chk_reset;
610 --
611 PROCEDURE clear_warnings IS
612 --
613 proc_name CONSTANT varchar2(50) := 'hr_payrolls.clear_warnings';
614 --
615 BEGIN
616 --
617 hr_utility.set_location(proc_name, 1);
618
619 g_weeks_reset_warn := false;
620 g_end_date_changed_warn := false;
621 g_no_of_weeks_reset := 0;
622 g_reset_period_name := null;
623 g_new_end_date := null;
624
625 hr_utility.set_location(proc_name, 5);
626 --
627 END clear_warnings;
628 --
629 --
630 PROCEDURE get_warnings ( p_weeks_reset_warn IN OUT nocopy boolean
631 ,p_end_date_changed_warn IN OUT nocopy boolean
632 ,p_no_of_weeks_reset IN OUT nocopy number
633 ,p_reset_period_name IN OUT nocopy per_time_periods.period_name%type
634 ,p_new_end_date IN OUT nocopy per_time_periods.end_date%type ) IS
635 --
636 proc_name CONSTANT varchar2(50) := 'hr_payrolls.get_warnings';
637 --
638 BEGIN
639 --
640 hr_utility.set_location(proc_name, 1);
641
642 p_weeks_reset_warn := g_weeks_reset_warn;
643 p_end_date_changed_warn := g_end_date_changed_warn;
644 p_no_of_weeks_reset := g_no_of_weeks_reset;
645 p_reset_period_name := g_reset_period_name;
646 p_new_end_date := g_new_end_date;
647
648 hr_utility.set_location(proc_name, 5);
649 --
650 END get_warnings;
651 --
652 -- This procedure is called either to create payroll processing
653 -- periods for a payroll for the first time, or to create further
654 -- periods to the ones which already exist.
655 -- If no periods already exist, then we only have the end date of
656 -- the first period, from which we can calculate that period's
657 -- start date. However, if periods do already exist, then the
658 -- start date of the first period we must generate is passed in.
659 -- Note that the end date of the very first period generated/to
660 -- be generated is ALWAYS passed in. It is from this that we can
661 -- determine how far into the future we generate payroll periods.
662 -- Note further that throughout the procedure, reference is made
663 -- to the period number. For calculation purposes, this is a
664 -- monotomically increasing number for periods within a legislative
665 -- year, being reset to 1 for each year. It is NOT the period number
666 -- which is stored against the period. The requirement is that
667 -- the displayed period number is a multiple of the base period
668 -- types comprising the period type of the generated periods
669 -- eg. the first lunar month period is numbered 4, the second 8 etc.
670 --
671 --
672 procedure insert_proc_periods(p_pay_det in out nocopy payroll_rec_type,
673 p_last_update_date in date default sysdate,
674 p_last_updated_by in number default -1,
675 p_last_update_login in number default -1,
676 p_created_by in number default -1,
677 p_creation_date in date default sysdate,
678 p_first_gen_date out nocopy date) is
679 --
680 proc_name CONSTANT varchar2(50) := 'hr_payrolls.insert_proc_periods';
681 --
682 per_num number; -- Monotonic period number, not displayed.
683 payroll_start_date pay_all_payrolls_f.effective_start_date%type;
684 payroll_end_date pay_all_payrolls_f.effective_end_date%type;
685 within_period_flag boolean := FALSE;
686 leg_start_date date;
687 end_years_marker date;
688 next_leg_start_date date;
689 period_start_date date;
690 period_end_date date;
691 current_regular_pay_date date;
692 l_period_number number;
693 l_period_name per_time_periods.period_name%type ;
694 l_display_period_type per_time_period_types.display_period_type%type ;
695 l_first_period_end_date date;
696 l_period_generate_count number := 1;
697 l_tax_year number(4);
698 --
699 l_no_of_periods per_time_period_types.number_per_fiscal_year%type;
700 l_no_of_weeks_reset number := 0;
701 l_period_end_date date;
702 l_leap_year_offset number;
703 --
704 CURSOR c_get_no_of_periods IS
705 select tp.number_per_fiscal_year
706 from per_time_period_types tp
707 where tp.period_type = p_pay_det.period_type;
708 --
709 begin
710 --
711 -- Derive the complete date range for this payroll.
712 hr_utility.set_location(proc_name, 1);
713 select min(ppy.effective_start_date),
714 max(ppy.effective_end_date)
715 into payroll_start_date,
716 payroll_end_date
717 from pay_all_payrolls_f ppy
718 where ppy.payroll_id = p_pay_det.payroll_id;
719 --
720 --
721 -- The periods generated must exist within the lifetime of the
722 -- payroll. Throughout the system, data is viewed within the
723 -- context of a period; hence, creating periods outside of the
724 -- payroll could introduce problems.
725 -- A consequence of this is that no periods will be generated
726 -- for the payroll if we cannot fit one into the date-effective
727 -- lifetime of the payroll. In this case, we raise an exception.
728 -- Note that if the code is invoked to add further periods to existing
729 -- ones, and none are created because of the end date on the payroll,
730 -- then no exception is raised (this situation could quite possibly
731 -- arise).
732 --
733 if p_pay_det.first_start_date < payroll_start_date or
734 p_pay_det.first_end_date > payroll_end_date then
735 hr_utility.set_message(801, 'PAY_6603_PAYROLL_NOGEN_PERIODS');
736 hr_utility.raise_error;
737 end if;
738 --
739 --
740 -- Derive the period number of the first period. It is not necessarily
741 -- 1, since this period is defined to be the first period whose regular
742 -- payment date lies within the legislative year.
743 --
744 declare
745 regular_pay_date date;
746 no_periods number;
747 begin
748 -- Loop back to find the first time period with its regular pay date
749 -- in the current legislative year. This is period 1, so from this
750 -- the period number of the first period to be generated can be
751 -- derived.
752 hr_utility.set_location(proc_name, 2);
753
754 -- Adjust regular pay date for the first generated period if required.
755
756 l_leap_year_offset := 0;
757
758 if p_pay_det.base_period_type = MONTHLY then
759
760 l_leap_year_offset := get_leap_year_offset ( p_period_end_date => p_pay_det.first_gen_end_date
761 ,p_pay_date_offset => p_pay_det.pay_date_offset );
762
763 end if;
764
765 regular_pay_date := p_pay_det.first_gen_end_date + l_leap_year_offset +
766 p_pay_det.pay_date_offset ;
767 --
768 -- Append the year component of the first regular pay date
769 -- to the legislative year start. This is necessary so that
770 -- we can determine the legislative start date to consider
771 -- eg. if the pay date is 04-apr-1992, then the legislative start
772 -- date in the UK is 06-apr-1991; if the pay date is 07-apr-1992,
773 -- then the legislative start date is 06-apr-1992.
774 -- Changed for MLS compliance to query the rule mode of the
775 -- format DD/MM, then make up the actual date using the regular
776 -- pay for the year element as before.
777 --
778 hr_utility.set_location(proc_name, 3);
779 begin
780 select to_date(plr.rule_mode || '/' ||
781 to_char(regular_pay_date, 'YYYY'), 'DD/MM/YYYY')
782 into leg_start_date
783 from pay_legislation_rules plr
784 where plr.rule_type = 'L'
785 and plr.legislation_code = p_pay_det.legislation_code ;
786 --
787 -- Also change defaulting.
788 EXCEPTION
789 when NO_DATA_FOUND then
790 leg_start_date := to_date('01/01/' ||
791 to_char(regular_pay_date, 'YYYY'), 'DD/MM/YYYY');
792 end;
793 --
794 -- Now establish whether the payment date must always lie within
795 -- the period. Default already set to FALSE.
796 hr_utility.set_location(proc_name, 11);
797 --
798 declare
799 rulemode pay_legislation_rules.rule_mode%type;
800 begin
801 --
802 select plr.rule_mode
803 into rulemode
804 from pay_legislation_rules plr
805 where plr.rule_type = 'PDO'
806 and plr.legislation_code = p_pay_det.legislation_code ;
807 --
808 if rulemode = 'N' then
809 within_period_flag := TRUE;
810 end if;
811 --
812 EXCEPTION
813 when NO_DATA_FOUND then null;
814 end;
815 --
816 if leg_start_date > regular_pay_date then
817 -- Must reduce the legislative start date by a year.
818 hr_utility.set_location(proc_name, 4);
819 leg_start_date := add_months(leg_start_date, -12);
820 end if;
821 --
822 -- Remove the l_leap_year_offset added to the regular pay date
823 -- as it is again added in the while loop.
824
825 regular_pay_date := regular_pay_date - l_leap_year_offset ;
826
827 no_periods := 0;
828 while regular_pay_date + l_leap_year_offset >= leg_start_date loop
829 regular_pay_date := add_multiple_of_base(regular_pay_date,
830 p_pay_det.base_period_type,
831 -(p_pay_det.multiple),
832 p_pay_det.first_end_date,
833 true, -- regular pay mode.
834 p_pay_det.pay_date_offset);
835 no_periods := no_periods + 1;
836 -- added to calculate previous period end date
837 if p_pay_det.base_period_type = TEN_DAYS then
838 regular_pay_date := regular_pay_date -1 ;
839 end if;
840
841 l_leap_year_offset := 0;
842
843 if p_pay_det.base_period_type = MONTHLY then
844
845 l_leap_year_offset := get_leap_year_offset ( p_period_end_date => regular_pay_date - p_pay_det.pay_date_offset
846 ,p_pay_date_offset => p_pay_det.pay_date_offset );
847
848 end if;
849
850 end loop;
851 --
852 per_num := no_periods;
853 hr_utility.set_location('per_num '||per_num,10);
854 end; -- Deriving period number.
855 --
856
857 -- Clear the Global Warning Variables
858 --
859 clear_warnings;
860
861 --
862 -- Insert time periods for the number of years required.
863 --
864 --
865 hr_utility.set_location(proc_name, 5);
866 end_years_marker := add_months(p_pay_det.first_start_date,
867 (12 * p_pay_det.no_years));
868 --
869 -- Hold the start date of the next legislative year in order to
870 -- determine when we are crossing the year boundary.
871 hr_utility.set_location(proc_name, 6);
872 next_leg_start_date := add_months(leg_start_date, 12);
873 period_start_date := p_pay_det.first_gen_start_date;
874 period_end_date := p_pay_det.first_gen_end_date;
875 --
876
877 -- Code for the resetting of periods for lunar months
878 --
879 open c_get_no_of_periods;
880 fetch c_get_no_of_periods into l_no_of_periods;
881 close c_get_no_of_periods;
882
883 -- Check if first generated period has to be reset.
884 -- Also check if the first period end date has to be changed (possible only while inserting)
885
886 l_period_end_date := period_end_date;
887
888 chk_reset( p_payroll_details => p_pay_det
889 ,p_period_number => per_num
890 ,p_no_of_periods => l_no_of_periods
891 ,p_period_start_date => period_start_date
892 ,p_next_leg_start_date => next_leg_start_date
893 ,p_period_end_date => period_end_date
894 ,p_no_of_weeks_reset => l_no_of_weeks_reset );
895
896 if l_period_end_date <> period_end_date then
897
898 -- If first generated period is same as first period of the entire payroll then
899 -- first period end date of the payroll has to be updated
900
901 if p_pay_det.first_start_date = p_pay_det.first_gen_start_date and
902 p_pay_det.first_end_date = p_pay_det.first_gen_end_date then
903
904 p_pay_det.first_end_date := period_end_date;
905 p_pay_det.first_gen_end_date := period_end_date;
906
907 update pay_all_payrolls_f
908 set first_period_end_date = period_end_date
909 where payroll_id = p_pay_det.payroll_id;
910
911 -- Set the warning variable
912 g_end_date_changed_warn := true;
913 g_new_end_date := period_end_date;
914
915 if p_pay_det.first_end_date > payroll_end_date then
916 hr_utility.set_message(801, 'PAY_6603_PAYROLL_NOGEN_PERIODS');
917 hr_utility.raise_error;
918 end if;
919 else
920 p_pay_det.first_gen_end_date := period_end_date;
921 end if;
922
923 end if;
924 --
925
926 -- get leap year offset for the first generated period as it might have been
927 -- lost.
928
929 l_leap_year_offset := 0;
930
931 if p_pay_det.base_period_type = MONTHLY then
932
933 l_leap_year_offset := get_leap_year_offset ( p_period_end_date => p_pay_det.first_gen_end_date
934 ,p_pay_date_offset => p_pay_det.pay_date_offset );
935
936 end if;
937
938
939 -- Generate periods for the more restrictive of the two date
940 -- ranges:
941 --
942 -- 1. Number of years to be generated on the payroll.
943 -- 2. Date effective lifetime of the payroll.
944 --
945 while (period_start_date < end_years_marker) and
946 (period_end_date <= payroll_end_date) loop
947 --
948 current_regular_pay_date := period_end_date + p_pay_det.pay_date_offset;
949 --
950 -- If payment dates must lie within their periods for the legislation,
951 -- then check this is the case. It's too difficult to catch all cases
952 -- at offset definition time.
953 --
954 if within_period_flag = TRUE then
955 if ( current_regular_pay_date < period_start_date ) or
956 ( current_regular_pay_date > period_end_date ) then
957 hr_utility.set_message(801, 'PAY_6999_PAYROLL_INV_PAY_DATE');
958 hr_utility.raise_error;
959 end if;
960 end if;
961 --
962 -- Bug 493007
963 -- Find the Display Period Type (translated version) for the given
964 -- Period Type.
965 --
966 select NVL(tpt.display_period_type, p_pay_det.period_type)
967 into l_display_period_type
968 from per_time_period_types_vl tpt
969 where tpt.period_type = p_pay_det.period_type;
970 --
971 -- For GB legislation we store the period number as the multiple of
972 -- constituent base period types.
973 --
974 l_tax_year := (to_char(next_leg_start_date, 'YYYY') - 1);
975
976 if ( p_pay_det.legislation_code = 'GB' ) then
977 l_period_number := per_num * p_pay_det.multiple ;
978 --
979 -- Check to see if a profile value is set to create period names
980 -- based on current tax year instead of current calendar year.
981 --
982 --Bug 1818469
983 if (fnd_profile.value('PAY_GENERATE_PAYROLL_PERIODS_TAX_YEAR') = 'Y' and
984 period_start_date < next_leg_start_date) then
985 l_period_name := to_char(per_num * p_pay_det.multiple) || ' '
986 || to_char(l_tax_year) || ' '
987 || l_display_period_type ;
988 else
989 l_period_name := to_char(per_num * p_pay_det.multiple) || ' '
990 || to_char(current_regular_pay_date + l_leap_year_offset - (7 * l_no_of_weeks_reset), 'YYYY') || ' '
991 || l_display_period_type ;
992 end if;
993 --
994 else
995 l_period_number := per_num ;
996 --
997 -- Check to see if a profile value is set to create period names
998 -- based on current tax year instead of current calendar year.
999 --
1000 if (fnd_profile.value('PAY_GENERATE_PAYROLL_PERIODS_TAX_YEAR') = 'Y' and
1001 period_start_date < next_leg_start_date) then
1002 l_period_name := to_char(per_num) || ' '
1003 || to_char(l_tax_year) || ' '
1004 || l_display_period_type ;
1005
1006 else
1007 l_period_name := to_char(per_num) || ' '
1008 || to_char(current_regular_pay_date + l_leap_year_offset - (7 * l_no_of_weeks_reset), 'YYYY') || ' '
1009 || l_display_period_type ;
1010 end if;
1011 --
1012 end if;
1013
1014 -- Store the reset period name for displaying in the warning message
1015
1016 if period_start_date = g_reset_period_start_date and period_end_date = g_reset_period_end_date then
1017 g_reset_period_name := l_period_name;
1018 end if;
1019
1020 --
1021 hr_utility.set_location('l_period_name '||l_period_name,10);
1022 -- Insert the processing time period.
1023 hr_utility.set_location(proc_name, 7);
1024 insert into per_time_periods
1025 (time_period_id,
1026 payroll_id,
1027 start_date,
1028 end_date,
1029 regular_payment_date,
1030 cut_off_date,
1031 pay_advice_date,
1032 default_dd_date,
1033 period_type,
1034 period_num,
1035 period_name,
1036 status,
1037 run_display_number,
1038 quickpay_display_number,
1039 last_update_date,
1040 last_updated_by,
1041 last_update_login,
1042 created_by,
1043 creation_date,
1044 payslip_view_date)
1045 select
1046 per_time_periods_s.nextval,
1047 p_pay_det.payroll_id,
1048 period_start_date,
1049 period_end_date,
1050 current_regular_pay_date,
1051 period_end_date + p_pay_det.cut_off_date_offset,
1052 period_end_date + p_pay_det.pay_advice_date_offset,
1053 period_end_date + p_pay_det.direct_deposit_date_offset,
1054 p_pay_det.period_type,
1055 l_period_number,
1056 l_period_name,
1057 'O',
1058 1,
1059 1,
1060 p_last_update_date,
1061 p_last_updated_by,
1062 p_last_update_login,
1063 p_created_by,
1064 p_creation_date,
1065 Current_regular_pay_date + p_pay_det.payslip_view_date_offset
1066 from sys.dual;
1067 --
1068 -- Increment loop variables.
1069 hr_utility.set_location(proc_name, 8);
1070 -- Store the first period end date, on the first loop.
1071 if l_period_generate_count = 1 then
1072 l_first_period_end_date := period_end_date;
1073 end if;
1074 --
1075 l_period_generate_count := l_period_generate_count + 1;
1076 period_start_date := period_end_date + 1;
1077 --
1078 period_end_date := add_multiple_of_base(period_end_date,
1079 p_pay_det.base_period_type,
1080 p_pay_det.multiple,
1081 p_pay_det.first_end_date);
1082
1083 -- Determine the period number.
1084 hr_utility.set_location(proc_name, 9);
1085
1086 -- Adjust regular pay date if required.
1087
1088 l_leap_year_offset := 0;
1089
1090 if p_pay_det.base_period_type = MONTHLY then
1091
1092 l_leap_year_offset := get_leap_year_offset ( p_period_end_date => period_end_date
1093 ,p_pay_date_offset => p_pay_det.pay_date_offset );
1094
1095 end if;
1096
1097 if (period_end_date + l_leap_year_offset + p_pay_det.pay_date_offset - (7 * l_no_of_weeks_reset))
1098 >= next_leg_start_date then
1099
1100 -- Reset the period number since we have just crossed the year
1101 -- boundary. Also, advance next_leg_start_date to next year.
1102 per_num := 1;
1103 hr_utility.set_location(proc_name, 10);
1104 next_leg_start_date := add_months(next_leg_start_date, 12);
1105 else
1106 per_num := per_num + 1;
1107 end if;
1108
1109 -- Check if reset is required.
1110
1111 chk_reset( p_payroll_details => p_pay_det
1112 ,p_period_number => per_num
1113 ,p_no_of_periods => l_no_of_periods
1114 ,p_period_start_date => period_start_date
1115 ,p_next_leg_start_date => next_leg_start_date
1116 ,p_period_end_date => period_end_date
1117 ,p_no_of_weeks_reset => l_no_of_weeks_reset );
1118 --
1119 end loop;
1120 --
1121 p_first_gen_date := l_first_period_end_date;
1122 --
1123 end insert_proc_periods;
1124 -------------------------------------------------------------------------
1125 --
1126 -- PROCEDURE create_dynamic_local_cal
1127 -- This procedure is used to derive a call to a localization's
1128 -- calendar procedure, by use of dynamic SQL. All the necessary
1129 -- parameters are here to enable localizations to effectively
1130 -- seed extra calendar data.
1131 -------------------------------------------------------------------------
1132 --
1133 procedure create_dynamic_local_cal (p_payroll_id in number,
1134 p_first_period_end in date,
1135 p_first_gen_date in date,
1136 p_period_type in varchar2,
1137 p_base_period_type in varchar2,
1138 p_multiple in number,
1139 p_legislation_code in varchar2) is
1140 --
1141 l_tp_last_end_date date;
1142 NO_PACKAGE_BODY exception;
1143 pragma exception_init(NO_PACKAGE_BODY,-6508);
1144 NO_PACKAGE_PROCEDURE exception;
1145 pragma exception_init(NO_PACKAGE_PROCEDURE,-6550);
1146 --
1147 -- dynamic SQL variables
1148 --
1149 sql_curs number;
1150 rows_processed integer;
1151 statem varchar2(512);
1152 --
1153 cursor get_period_last_end_date(c_payroll_id in number) is
1154 select max(end_date)
1155 from per_time_periods
1156 where payroll_id = c_payroll_id;
1157 --
1158 begin
1159 --
1160 open get_period_last_end_date(p_payroll_id);
1161 fetch get_period_last_end_date into
1162 l_tp_last_end_date;
1163 close get_period_last_end_date;
1164 --
1165 --
1166 -- Create dynamic SQL call to localization calendars package
1167 --
1168 statem := 'BEGIN
1169 pay_'||lower(p_legislation_code)||'_calendars_pkg.create_calendar
1170 (:p_payroll_id,
1171 :p_first_end_date,
1172 :p_last_end_date,
1173 :p_period_type,
1174 :p_base_period_type,
1175 :p_multiple,
1176 :p_first_period_end); END;';
1177 --
1178 sql_curs := dbms_sql.open_cursor;
1179 --
1180 dbms_sql.parse(sql_curs,
1181 statem,
1182 dbms_sql.v7);
1183 --
1184 -- Bind all the variables for the dynamic call
1185 --
1186 dbms_sql.bind_variable(sql_curs,'p_payroll_id',p_payroll_id);
1187 dbms_sql.bind_variable(sql_curs,'p_first_end_date',p_first_gen_date);
1188 dbms_sql.bind_variable(sql_curs,'p_last_end_date',l_tp_last_end_date);
1189 dbms_sql.bind_variable(sql_curs,'p_period_type',p_period_type);
1190 dbms_sql.bind_variable(sql_curs,'p_base_period_type',p_base_period_type);
1191 dbms_sql.bind_variable(sql_curs,'p_multiple',p_multiple);
1192 dbms_sql.bind_variable(sql_curs,'p_first_period_end',p_first_period_end);
1193 --
1194 -- Execute the dyn cursor for the procedure call, then close.
1195 --
1196 BEGIN
1197 --
1198 rows_processed := dbms_sql.execute(sql_curs);
1199 --
1200 -- If one of the user-defined exceptions are raised,
1201 -- do nothing as it means that the legislation does not
1202 -- have a package procedure defined for the localization, in which
1203 -- case we can ignore the call and no further processing outside
1204 -- the normal payroll periods is necessary.
1205 -- Other exceptions (that might be raised by the actual
1206 -- localization's code) can be raised as normal.
1207 --
1208 EXCEPTION
1209 WHEN NO_PACKAGE_BODY OR NO_PACKAGE_PROCEDURE THEN
1210 NULL;
1211 --
1212 END;
1213 dbms_sql.close_cursor(sql_curs);
1214 --
1215 --
1216 end create_dynamic_local_cal;
1217 --
1218 -----------------------------------------------------------------------------
1219 --
1220 -- This procedure is called to insert payroll processing periods.
1221 --
1222 --
1223 procedure create_payroll_proc_periods (p_payroll_id in number,
1224 p_last_update_date in date,
1225 p_last_updated_by in number,
1226 p_last_update_login in number,
1227 p_created_by in number,
1228 p_creation_date in date) is
1229 --
1230 proc_name CONSTANT varchar2(50) := 'hr_payrolls.create_payroll_proc_periods';
1231 --
1232 payroll_details payroll_rec_type;
1233 l_first_gen_end_date date;
1234 --
1235 begin
1236 payroll_details.payroll_id := p_payroll_id;
1237 --
1238 -- Get the payroll details from the date-effective view, so that
1239 -- the correct number of years is retrieved. All other payroll
1240 -- attributes that we are interested in cannot be date-effectively
1241 -- updated.
1242 --
1243 hr_utility.set_location(proc_name, 1);
1244 select ppy.number_of_years,
1245 ppy.period_type,
1246 ppy.pay_date_offset,
1247 ppy.cut_off_date_offset,
1248 ppy.pay_advice_date_offset,
1249 ppy.direct_deposit_date_offset,
1250 ppy.first_period_end_date,
1251 ppy.period_reset_years,
1252 hr_api.return_legislation_code(ppy.business_group_id),
1253 ppy.payslip_view_date_offset
1254 into payroll_details.no_years,
1255 payroll_details.period_type,
1256 payroll_details.pay_date_offset,
1257 payroll_details.cut_off_date_offset,
1258 payroll_details.pay_advice_date_offset,
1259 payroll_details.direct_deposit_date_offset,
1260 payroll_details.first_end_date,
1261 payroll_details.period_reset_years,
1262 payroll_details.legislation_code,
1263 payroll_details.payslip_view_date_offset
1264 from pay_all_payrolls ppy
1265 where ppy.payroll_id = p_payroll_id;
1266 --
1267 --
1268 -- Validate the number of years and midpoint offset, where
1269 -- appropriate.
1270 --
1271 -- Number of years must be > 0.
1272 if payroll_details.no_years <= 0 then
1273 hr_utility.set_message(801, 'PAY_6604_PAYROLL_INV_NO_YEARS');
1274 hr_utility.raise_error;
1275 end if;
1276 --
1277 -- All of the supported time period types map to either a weekly or
1278 -- monthly base period and multiple eg. fortnight = 2 * week,
1279 -- bi-monthly = 2 * month.
1280 --
1281 -- Determine the base period and multiple for the period type specified.
1282 get_period_details(payroll_details.period_type,
1283 payroll_details.base_period_type,
1284 payroll_details.multiple);
1285 --
1286 -- Now derive the remaining payroll period dates.
1287 derive_payroll_dates(payroll_details);
1288 --
1289 -- Insert the further periods required.
1290 insert_proc_periods(payroll_details,
1291 p_last_update_date,
1292 p_last_updated_by,
1293 p_last_update_login,
1294 p_created_by,
1295 p_creation_date,
1296 l_first_gen_end_date);
1297 --
1298 -- Create legislative further calendars information.
1299 --
1300 create_dynamic_local_cal (p_payroll_id => p_payroll_id,
1301 p_first_period_end => payroll_details.first_end_date,
1302 p_first_gen_date => l_first_gen_end_date,
1303 p_period_type => payroll_details.period_type,
1304 p_base_period_type => payroll_details.base_period_type,
1305 p_multiple => payroll_details.multiple,
1306 p_legislation_code => payroll_details.legislation_code);
1307 --
1308 end create_payroll_proc_periods;
1309 ------------------------------------------------------------------------------------
1310 --This is a overloaded version of create_payroll_proc_periods with
1311 --additional parameter p_effective_date and using PAY_ALL_PAYROLLS_F
1312 --table instead of PAY_ALL_PAYROLLS view.
1313 --
1314 procedure create_payroll_proc_periods (p_payroll_id in number,
1315 p_last_update_date in date,
1316 p_last_updated_by in number,
1317 p_last_update_login in number,
1318 p_created_by in number,
1319 p_creation_date in date,
1320 p_effective_date in date ) is
1321 --
1322 proc_name CONSTANT varchar2(50) := 'hr_payrolls.create_payroll_proc_periods';
1323 --
1324 payroll_details payroll_rec_type;
1325 l_first_gen_end_date date;
1326 --
1327 begin
1328 payroll_details.payroll_id := p_payroll_id;
1329 --
1330 -- Get the payroll details from the date-effective view, so that
1331 -- the correct number of years is retrieved. All other payroll
1332 -- attributes that we are interested in cannot be date-effectively
1333 -- updated.
1334 --
1335 hr_utility.set_location(proc_name, 1);
1336 select ppy.number_of_years,
1337 ppy.period_type,
1338 ppy.pay_date_offset,
1339 ppy.cut_off_date_offset,
1340 ppy.pay_advice_date_offset,
1341 ppy.direct_deposit_date_offset,
1342 ppy.first_period_end_date,
1343 pbg.legislation_code,
1344 ppy.payslip_view_date_offset,
1345 ppy.period_reset_years -- Added for bug 8616134
1346 into payroll_details.no_years,
1347 payroll_details.period_type,
1348 payroll_details.pay_date_offset,
1349 payroll_details.cut_off_date_offset,
1350 payroll_details.pay_advice_date_offset,
1351 payroll_details.direct_deposit_date_offset,
1352 payroll_details.first_end_date,
1353 payroll_details.legislation_code,
1354 payroll_details.payslip_view_date_offset,
1355 payroll_details.period_reset_years -- Added for bug 8616134
1356 from pay_all_payrolls_f ppy,
1357 per_business_groups pbg
1358 where ppy.payroll_id = p_payroll_id
1359 and ppy.business_group_id + 0 = pbg.business_group_id + 0
1360 and ppy.effective_start_date <= p_effective_date
1361 and ppy.effective_end_date >= p_effective_date ;
1362 --
1363 --
1364 -- Validate the number of years and midpoint offset, where
1365 -- appropriate.
1366 --
1367 -- Number of years must be > 0.
1368 if payroll_details.no_years <= 0 then
1369 hr_utility.set_message(801, 'PAY_6604_PAYROLL_INV_NO_YEARS');
1370 hr_utility.raise_error;
1371 end if;
1372 --
1373 -- All of the supported time period types map to either a weekly or
1374 -- monthly base period and multiple eg. fortnight = 2 * week,
1375 -- bi-monthly = 2 * month.
1376 --
1377 -- Determine the base period and multiple for the period type specified.
1378 get_period_details(payroll_details.period_type,
1379 payroll_details.base_period_type,
1380 payroll_details.multiple);
1381 --
1382 -- Now derive the remaining payroll period dates.
1383 derive_payroll_dates(payroll_details);
1384 --
1385 -- Insert the further periods required.
1386 insert_proc_periods(payroll_details,
1387 p_last_update_date,
1388 p_last_updated_by,
1389 p_last_update_login,
1390 p_created_by,
1391 p_creation_date,
1392 l_first_gen_end_date);
1393 --
1394 -- Create legislative further calendars information.
1395 --
1396 create_dynamic_local_cal (p_payroll_id => p_payroll_id,
1397 p_first_period_end => payroll_details.first_end_date,
1398 p_first_gen_date => l_first_gen_end_date,
1399 p_period_type => payroll_details.period_type,
1400 p_base_period_type => payroll_details.base_period_type,
1401 p_multiple => payroll_details.multiple,
1402 p_legislation_code => payroll_details.legislation_code);
1403 --
1404 end create_payroll_proc_periods;
1405 --
1406 ------------------------------------------------------------------------------------
1407 --
1408 -- This procedure displays the correct format of period_name
1409 -- depending on ACTION_TYPE.
1410 --
1411 --
1412 function local_display_period_name(p_payroll_action_id IN NUMBER)
1413 --
1414 RETURN VARCHAR2 is
1415 --
1416 l_period_name VARCHAR2(70);
1417 l_payroll_id NUMBER;
1418 l_time_period_id NUMBER;
1419 l_effective_date DATE;
1420 l_start_date DATE;
1421 l_date_earned DATE;
1422 l_action_type VARCHAR2(4);
1423 l_date_from_and_to BOOLEAN := FALSE;
1424 --
1425 cursor action_info(c_payroll_action_id IN NUMBER) is
1426 SELECT payroll_id,
1427 time_period_id,
1428 effective_date,
1429 start_date,
1430 date_earned,
1431 action_type
1432 FROM pay_payroll_actions
1433 WHERE payroll_action_id = c_payroll_action_id;
1434 --
1435 cursor get_name (c_payroll_id IN NUMBER,
1436 c_date_earned IN DATE) is
1437 SELECT period_name
1438 FROM per_time_periods
1439 WHERE payroll_id = c_payroll_id
1440 AND c_date_earned between start_date and end_date;
1441 --
1442 BEGIN
1443 --
1444 open action_info(p_payroll_action_id);
1445 FETCH action_info INTO l_payroll_id,
1446 l_time_period_id,
1447 l_effective_date,
1448 l_start_date,
1449 l_date_earned,
1450 l_action_type;
1451 close action_info;
1452 --
1453 -- Display period name as 'date from and to' if in following action types:
1454 -- Transfer To GL, Retropay, Costing.
1455 --
1456 if l_action_type in ('C','O','P','T') then
1457 l_date_from_and_to := TRUE;
1458 end if;
1459 --
1460 -- where no date_earned is set and action type is not 'date from and to' type
1461 -- just report the period as the start and end of the action.
1462 --
1463 if l_date_earned IS NULL or l_date_from_and_to THEN
1464 l_period_name := to_char(l_start_date, 'dd-MON-yyyy')||' - '
1465 ||to_char (l_effective_date,'dd-MON-yyyy');
1466 else
1467 open get_name (l_payroll_id, l_date_earned);
1468 FETCH get_name into l_period_name;
1469 close get_name;
1470 end if;
1471 --
1472 RETURN l_period_name;
1473 --
1474 end local_display_period_name;
1475 --
1476 function display_period_name(p_payroll_action_id IN NUMBER)
1477 return VARCHAR2 IS
1478 BEGIN
1479 IF g_enable_period_name_fetch THEN
1480 RETURN local_display_period_name(p_payroll_action_id);
1481 END IF;
1482 RETURN NULL;
1483 END display_period_name;
1484 --
1485 function display_period_name_forced(p_payroll_action_id IN NUMBER)
1486 return VARCHAR2 IS
1487 BEGIN
1488 RETURN local_display_period_name(p_payroll_action_id);
1489 END display_period_name_forced;
1490 --
1491 PROCEDURE enable_display_fetch(p_mode IN BOOLEAN) IS
1492 BEGIN
1493 g_enable_period_name_fetch := p_mode;
1494 END enable_display_fetch;
1495 --
1496 PROCEDURE set_globals ( p_constant_end_date in boolean ) IS
1497 BEGIN
1498 g_constant_end_date := p_constant_end_date ;
1499 END set_globals;
1500 --
1501 end hr_payrolls;
[Home] [Help]