1 package body pay_za_calendars_pkg as
2 /* $Header: pyzapcal.pkb 120.2 2005/06/28 00:08:22 kapalani noship $ */
3 -- +======================================================================+
4 -- | Copyright (c) 1998 Oracle Corporation South Africa Ltd |
5 -- | Cape Town, Western Cape, South Africa |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 -- SQL Script File Name : pyzapcal.pkb
9 -- Description : This sql script seeds the za_pay_calendars
10 -- package for the ZA localisation. This package
11 -- creates the payroll calendars needed for ZA.
12 --
13 -- Change List:
14 -- ------------
15 --
16 -- Name Date Version Bug Text
17 -- -------------- ---------- ------- ------- ------------------------------
18 -- F.D. Loubser 03-SEP-98 110.0 Initial Version
19 -- A. Mills 20-APR-99 110.1 Changed package name and
20 -- main interface procedure
21 -- (create_calendar) so that
22 -- dynamic call from core code
23 -- (hr_payrolls) will work.
24 -- F.D. Loubser 12-MAY-99 110.2 Added default value for
25 -- l_store_end_date in
26 -- create_za_employee_tax_years
27 -- F.D. Loubser 09-JUN-99 110.3 Changed 'PAY_' Messages to
28 -- conform to ZA standard
29 -- F.D. Loubser 11-JUN-99 110.4 Added move to last period
30 -- in Month End for first period
31 -- being start of tax year
32 -- J.N. Louw 23-AUG-2000 115.0 Updated package for 11i
33 -- Flexible Date Functionality
34 -- -- org_information11
35 -- L.J. Kloppers 22-Nov-2000 115.1 Move History Section into Package
36 -- L.J. Kloppers 27-Nov-2000 115.2 Added update of:
37 -- prd_information_category = 'ZA'
38 -- Ghanshyam 07-Jul-2002 115.0 Moved the contents from
39 -- $PER_TOP/patch/115/sql/hrzapcal.pkb
40 -- ========================================================================
41
42
43 ---------------------------------------------------------------------------------------
44 -- Global variables
45 ---------------------------------------------------------------------------------------
46
47 -- Package constants, denoting the base period types.
48 -- These constants are needed to call the core function add_multiple_of_base
49 WEEKLY CONSTANT varchar2(1) := 'W';
50 MONTHLY CONSTANT varchar2(1) := 'M';
51 SEMIMONTHLY CONSTANT varchar2(1) := 'S';
52
53 ---------------------------------------------------------------------------------------
54 -- This procedure is a driver that defaults the calendars for the payroll name
55 -- passed to it. It is only provided as a manual substitute, until the functionality
56 -- is incorporated into the core code.
57 -- Parameters:
58 -- p_payroll_name - The name of the payroll for which the calendars should be
59 -- defaulted.
60 -- p_effective_date - The effective date
61 -- NOTE: Error checking is not provided, since this procedure is not supposed to be
62 -- called as part of a core process.
63 ---------------------------------------------------------------------------------------
64
65 procedure do
66 (
67 p_payroll_name varchar2,
68 p_effective_date date
69 )
70 is
71
72 l_payroll_id number;
73 l_first_end_date date;
74 l_last_end_date date;
75 l_period_type varchar2(30);
76 l_period_type2 varchar2(1);
77 l_multiple number;
78 l_fpe date;
79
80 begin
81
82 -- Obtain the Payroll ID, First Period End Date and Period Type
83 select distinct
84 payroll_id, first_period_end_date, period_type
85 into
86 l_payroll_id, l_fpe, l_period_type
87 from
88 pay_payrolls_f
89 where
90 payroll_name = p_payroll_name
91 and
92 p_effective_date between effective_start_date and effective_end_date;
93
94 -- Obtain the chronologically first end date in the Payroll
95 select
96 min(end_date)
97 into
98 l_first_end_date
99 from
100 per_time_periods
101 where
102 payroll_id = l_payroll_id;
103
104 -- Obtain the chronologically last end date in the Payroll
105 select
106 max(end_date)
107 into
108 l_last_end_date
109 from
110 per_time_periods
111 where
112 payroll_id = l_payroll_id;
113
114 -- Determine the base period type and the multiple of the base
115 if l_period_type = 'Year' then
116 l_period_type2 := MONTHLY;
117 l_multiple := 12;
118 elsif l_period_type = 'Semi-Year' then
119 l_period_type2 := MONTHLY;
120 l_multiple := 6;
121 elsif l_period_type = 'Quarter' then
122 l_period_type2 := MONTHLY;
123 l_multiple := 3;
124 elsif l_period_type = 'Bi-Month' then
125 l_period_type2 := MONTHLY;
126 l_multiple := 2;
127 elsif l_period_type = 'Calendar Month' then
128 l_period_type2 := MONTHLY;
129 l_multiple := 1;
130 elsif l_period_type = 'Lunar Month' then
131 l_period_type2 := WEEKLY;
132 l_multiple := 4;
133 elsif l_period_type = 'Semi-Month' then
134 l_period_type2 := SEMIMONTHLY;
135 l_multiple := 1; -- Not used for semi-monthly
136 elsif l_period_type = 'Bi-Week' then
137 l_period_type2 := WEEKLY;
138 l_multiple := 2;
139 elsif l_period_type = 'Week' then
140 l_period_type2 := WEEKLY;
141 l_multiple := 1;
142 else
143 -- Unknown period type.
144 hr_utility.set_message(801, 'PAY_6601_PAYROLL_INV_PERIOD_TP');
145 hr_utility.raise_error;
146 end if;
147
148 -- Call the procedure that creates the ZA calendars with the necessary parameters.
149 create_calendar
150 (
151 l_payroll_id,
152 l_first_end_date,
153 l_last_end_date,
154 l_period_type,
155 l_period_type2,
156 l_multiple,
157 l_fpe
158 );
159 end do;
160
161 ---------------------------------------------------------------------------------------
162 -- This procedure is the driving procedure in the creation of the ZA Payroll
163 -- Calendars. It goes through the following steps:
167 -- 4. Generate the Payroll Month Ends between p_first_end_date and p_last_end_date
164 -- 1. Obtain the Business Group Id of the current user
165 -- 2. Obtain the day on which the Company Financial Year starts
166 -- 3. Obtain the date on which the Payroll Tax Year starts
168 -- 5. Generate the Tax Years between p_first_end_date and p_last_end_date
169 -- 6. Generate the Calendar Years between p_first_end_date and p_last_end_date
170 -- 7. Generate the Tax Quarters between p_first_end_date and p_last_end_date
171 -- Parameters:
172 -- p_payroll_id - The primary key ID of the payroll.
173 -- p_first_end_date - The first period end date in the generation time span.
174 -- p_last_end_date - The last period end date in the generation time span.
175 -- p_proc_period_type - The time period type, e.g. Week, Calendar Month.
176 -- p_base_period_type - The base period type, other period types are multiples of the
177 -- base period type, e.g. WEEKLY, MONTHLY, SEMIMONTHLY.
178 -- p_multiple - The amount of base periods that make up the period type.
179 -- p_fpe_date - The first period end date in the payroll time span.
180 ---------------------------------------------------------------------------------------
181 procedure create_calendar
182 (
183 p_payroll_id in number,
184 p_first_end_date in date,
185 p_last_end_date in date,
186 p_proc_period_type in varchar2,
187 -- Parameters needed to call the core function add_multiple_of_base
188 p_base_period_type in varchar2,
189 p_multiple in number,
190 p_fpe_date in date
191 )
192 is
193 -- The Business Group Id of the current user
194 l_business_group_id number;
195 -- The day on which the Company Financial Year Starts
196 l_fiscal_start_day number;
197 -- The date in DD-MON format on which the Payroll Tax Year starts
198 l_tax_year_start varchar2(50);
199 -- Function name used in trace package
200 func_name CONSTANT varchar2(50) := 'za_pay_calendars.create_calendar';
201
202 begin
203
204 hr_utility.set_location(func_name, 1);
205 -- Obtain the Business Group Id of the current user
206 l_business_group_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
207 -- Obtain the day on which the Company Financial Year starts
208 l_fiscal_start_day := to_number(
209 to_char(retrieve_fiscal_year_start(l_business_group_id), 'DD'));
210 --
211 -- Obtain the date on which the Payroll Tax Year starts
212 l_tax_year_start := retrieve_tax_year_start;
213
214 -- Generate the Payroll Month Ends between p_first_end_date and p_last_end_date
215 create_za_payroll_month_ends
216 (
217 p_payroll_id,
218 p_first_end_date,
219 p_last_end_date,
220 l_fiscal_start_day,
221 p_base_period_type,
222 p_multiple,
223 p_fpe_date
224 );
225
226 -- Generate the Tax Years between p_first_end_date and p_last_end_date
227 create_za_employee_tax_years
228 (
229 p_payroll_id,
230 p_first_end_date,
231 p_last_end_date,
232 l_tax_year_start,
233 p_proc_period_type
234 );
235
236 -- Generate the Calendar Years between p_first_end_date and p_last_end_date
237 create_za_employee_cal_years
238 (
239 p_payroll_id,
240 p_first_end_date,
241 p_last_end_date,
242 p_proc_period_type
243 );
244
245 -- Generate the Tax Quarters between p_first_end_date and p_last_end_date
246 create_za_tax_quarters
247 (
248 p_payroll_id,
249 p_first_end_date,
250 p_last_end_date
251 );
252
253 end create_calendar;
254
255 ---------------------------------------------------------------------------------------
256 -- This function is called to retrieve the Payroll Tax Year Start from the table
257 -- pay_legislation_rules.
258 -- NOTE: The function makes sure that the Payroll Tax Year is in DD-MON format.
259 ---------------------------------------------------------------------------------------
260 function retrieve_tax_year_start return varchar2 is
261
262 -- The Payroll Tax Year start date in DD-MON format.
263 l_tax_year_start varchar2(50);
264 -- The day of the Payroll Tax Year
265 l_tax_day number;
266 -- The month of the Payroll Tax Year
267 l_tax_month varchar2(50);
268 -- Function name used in trace package
269 func_name CONSTANT varchar2(50) := 'za_pay_calendars.retrieve_tax_year_start';
270
271 begin
272
273 hr_utility.set_location(func_name, 1);
274 -- Obtain the Tax Year Start from pay_legislation_rules
275 select
276 rule_mode
277 into
278 l_tax_year_start
279 from
280 pay_legislation_rules
281 where
282 legislation_code = 'ZA'
283 and
284 rule_type = 'L';
285
286 -- Determine the day of the Tax Year
287 l_tax_day :=
288 to_number(substr(l_tax_year_start, 1, instr(l_tax_year_start, '-') - 1));
289 -- Check whether this is a valid day
290 if l_tax_day > 31 then
291 raise invalid_number;
292 end if;
293
294 -- Determine the month of the Tax Year
295 l_tax_month := substr(l_tax_year_start, instr(l_tax_year_start, '-') + 1, 3);
296 -- Check whether this is a valid month
297 if instr('JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC', l_tax_month) = 0 then
298 raise invalid_number;
299 end if;
300
301 return l_tax_year_start;
302
303 exception
304 -- The Tax Year is not in DD-MON format
305 when no_data_found then
306 hr_utility.set_message(800, 'PAY_PAYROLL_TAX_YEAR');
307 hr_utility.raise_error;
308 when too_many_rows then
309 hr_utility.set_message(800, 'PAY_PAYROLL_TAX_YEAR');
310 hr_utility.raise_error;
311 when invalid_number then
312 hr_utility.set_message(800, 'PAY_PAYROLL_TAX_YEAR');
313 hr_utility.raise_error;
314
315 end retrieve_tax_year_start;
316
317 ---------------------------------------------------------------------------------------
318 -- This function is called to retrieve the Company Fiscal Year Start for the
319 -- current Business Group.
320 -- Parameters:
321 -- p_business_group_id - The Business Group ID of the currently logged in user.
322 -- NOTE: The flexfield already forces this value to DD-MON-YYYY format.
323 ---------------------------------------------------------------------------------------
324 function retrieve_fiscal_year_start
325 (
326 p_business_group_id in number
327 )
328 return date is
329
330 -- The start date of the Fiscal year
331 l_fiscal_start date;
332 -- Function name used in trace package
333 func_name CONSTANT varchar2(50) := 'za_pay_calendars.retrieve_fiscal_year_start';
334
335 begin
336
337 hr_utility.set_location(func_name, 1);
338 -- Determine the Company Fiscal Year Start for the current Business Group
339 select
340 fnd_date.canonical_to_date(org_information11)
341 into
342 l_fiscal_start
346 organization_id = p_business_group_id
343 from
344 hr_organization_information
345 where
347 and
348 org_information_context = 'Business Group Information';
349
350 return l_fiscal_start;
351
352 exception
353 when no_data_found then
354 hr_utility.set_message(800, 'PAY_COMPANY_FISCAL_YEAR');
355 hr_utility.raise_error;
356
357 end retrieve_fiscal_year_start;
358
359 ---------------------------------------------------------------------------------------
360 -- This procedure creates the Payroll Month Ends for the ZA calendars, by using the
361 -- following steps:
362 -- 1. Loop through the periods.
363 -- 2. Calculate the Company Financial Month Start for the current period.
364 -- 3. Store the Financial Month Start and End Date of the current period.
365 -- 4. If the Financial Month Start of the current period is different from that
366 -- of the previous period, then the previous period is a Payroll Month End.
367 -- Update all the periods between l_store_end_date and the previous period,
368 -- so that their Payroll Month End is equal to the Period End Date of the
369 -- previous period. Store the current period's end in l_store_end_date.
370 -- NOTE: For the last few periods you might have to generate extra periods, using
371 -- add_multiple_of_base, in order to find the Payroll Month End.
372 -- Parameters:
373 -- p_payroll_id - The primary key ID of the payroll.
374 -- p_first_end_date - The first period end date in the generation time span.
375 -- p_last_end_date - The last period end date in the generation time span.
376 -- p_fiscal_start_day - The day on which the Company Fiscal Year starts.
377 -- p_base_period_type - The base period type, other period types are multiples of the
378 -- base period type, e.g. WEEKLY, MONTHLY, SEMIMONTHLY.
379 -- p_multiple - The amount of base periods that make up the period type.
380 -- p_fpe_date - The first period end date in the payroll time span.
381 ---------------------------------------------------------------------------------------
382 procedure create_za_payroll_month_ends
383 (
384 p_payroll_id in number,
385 p_first_end_date in date,
386 p_last_end_date in date,
387 p_fiscal_start_day in number,
388 -- Parameters needed to call the core function add_multiple_of_base
389 p_base_period_type in varchar2,
390 p_multiple in number,
391 p_fpe_date in date
392 )
393 is
394
395 -- Temporary variable used to store the previous period's end date.
396 l_previous_end_date date;
397 -- Temporary variable used to store the end date where the Payroll Month starts.
398 l_store_end_date date;
399 -- Temporary variable used to store the previous period's end date.
400 l_old_end_date date;
401 -- Used to store the current period's end date.
402 l_end_date date;
403 -- Temporary variable used to store the Fiscal Month Start.
404 l_store_fin_ms date;
405 -- Used to store the current period's Fiscal Month Start.
406 l_fin_ms date;
407 -- Function name used in trace package
408 func_name CONSTANT varchar2(50) := 'za_pay_calendars.za_payroll_month_ends';
409
410 -- Cursor that holds all the periods in the generation time span.
411 cursor get_periods is
412 select
413 end_date
414 from
415 per_time_periods
416 where
417 payroll_id = p_payroll_id
418 and
419 end_date between p_first_end_date and p_last_end_date
420 order by
421 end_date;
422
423 begin
424
425 hr_utility.set_location(func_name, 1);
426 open get_periods;
427 -- Store the first period end date
428 fetch get_periods into l_store_end_date;
429
430 -- Check whether there are any periods to process
431 if get_periods%notfound then
432 -- No periods to generate Month Ends for.
433 null;
434 else
435 -- Calculate the Company Financial Month Start for the first period and store it
436 l_store_fin_ms :=
437 generate_fiscal_month_start(l_store_end_date, p_fiscal_start_day);
438
439 -- Store the previous period end date
440 l_previous_end_date := l_store_end_date;
441
442 -- Loop through the remaining periods
443 loop
444 fetch get_periods into l_end_date;
445 exit when get_periods%notfound;
446
447 -- Calculate the Company Financial Month Start for the current period and
448 -- compare it with the previous one
449 l_fin_ms :=
450 generate_fiscal_month_start(l_end_date, p_fiscal_start_day);
451
452 -- If the Company Financial Month Starts differ, then the previous period is a
453 -- Payroll Month End
454 if l_store_fin_ms <> l_fin_ms then
455 -- Update all the periods between l_store_end_date and the previous
456 -- period, so that their Payroll Month End is equal to the Period End Date
457 -- of the previous period
458 update
459 per_time_periods
460 set
461 pay_advice_date = l_previous_end_date
462 where
463 payroll_id = p_payroll_id
464 and
465 end_date between l_store_end_date and l_previous_end_date;
466
467 -- Set l_store_end_date to the current period's end date
468 l_store_end_date := l_end_date;
469 end if;
470
471 -- Store the previous period's end date
472 l_previous_end_date := l_end_date;
473 l_store_fin_ms := l_fin_ms;
474 end loop;
475
476 -- For the last few periods there might not exist enough periods in the payroll
477 -- time span to obtain the Payroll Month End, therefore you have to obtain the
478 -- Payroll Month End for these periods by generating dummy periods using the
479 -- add_multiple_of_base function.
480 if l_store_end_date <= p_last_end_date then
481
482 -- Save the period to update from
483 l_end_date := l_store_end_date;
484
485 -- Loop until you find a Period End Date that is bigger than the current
486 -- Company Financial Month Start
487 while l_store_end_date < l_store_fin_ms loop
488
489 -- Store the old end date
490 l_old_end_date := l_store_end_date;
491
492 -- Determine the next period's end date
493 l_store_end_date := add_multiple_of_base(l_store_end_date,
494 p_base_period_type,
495 p_multiple,
496 p_fpe_date);
497 end loop;
498
499 -- Update all the periods between l_store_end_date and the last period,
500 -- so that their Payroll Month End is equal to l_old_end_date
501 update
502 per_time_periods
503 set
504 pay_advice_date = l_old_end_date
505 where
506 payroll_id = p_payroll_id
507 and
508 end_date between l_end_date and p_last_end_date;
509
510 end if;
511 end if;
512 close get_periods;
513 end create_za_payroll_month_ends;
514
515 ---------------------------------------------------------------------------------------
516 -- This function returns the Company Fiscal Month Start associated with the payroll
517 -- period. It returns the first date that falls on the day on which the Company
518 -- Fiscal Year start, and which is larger than the period's end date.
519 -- Parameters:
520 -- p_end_date - The period end date of the pay period.
521 -- p_fiscal_start_day - The day on which the Company Fiscal Year starts.
522 ---------------------------------------------------------------------------------------
523 function generate_fiscal_month_start
524 (
525 p_end_date in date,
526 p_fiscal_start_day in number
527 )
528 return date is
529
530 -- Temporary variable that holds the Fiscal Month Start.
531 l_fiscal_ms date;
532 -- Function name used in trace package
533 func_name CONSTANT varchar2(50) := 'za_pay_calendars.generate_fiscal_month_start';
534
535 begin
536
537 hr_utility.set_location(func_name, 1);
538 -- As a first try, try using the day of the fiscal year and the month and year
539 -- of the period's end date
540 -- next_lower_day checks whether the date exist, otherwise it subtracts days until
541 -- the date does exist
542 l_fiscal_ms := next_lower_day(p_fiscal_start_day, p_end_date);
543
544 -- The date must be larger than the period's end date, otherwise you must add a
545 -- month
546 while l_fiscal_ms <= p_end_date loop
547 l_fiscal_ms := next_lower_day(p_fiscal_start_day, add_months(l_fiscal_ms, 1));
548 end loop;
549 return l_fiscal_ms;
550 end generate_fiscal_month_start;
551
552 ---------------------------------------------------------------------------------------
553 -- This function returns p_fiscal_start_day concatenated with p_date's month and
554 -- year, if it exist, else it returns the next lower date that exist.
555 -- Parameters:
556 -- p_fiscal_start_day - The day on which the Fiscal Year starts.
557 -- p_date - A valid date that has the month and year of the date to be
558 -- returned.
559 ---------------------------------------------------------------------------------------
560 function next_lower_day
561 (
562 p_fiscal_start_day in number,
563 p_date in date
564 )
565 return date is
566
567 -- The last day of the month.
568 l_last_day number;
569 -- The date of the last day of the month.
570 l_last date;
571 -- Function name used in trace package
572 func_name CONSTANT varchar2(50) := 'za_pay_calendars.next_lower_day';
573
574 begin
575
576 hr_utility.set_location(func_name, 1);
577 -- Determine the date of the last day of the month
578 l_last := last_day(p_date);
579
580 -- Determine the day of the last day of the month
581 l_last_day := to_char(l_last, 'DD');
582
583 -- If the day of the date is larger than the last day of the month,
584 -- return the last day of the month, else return the date
585 if p_fiscal_start_day >= l_last_day then
586 return l_last;
587 else
588 return to_date(to_char(p_fiscal_start_day) || '-' ||
589 to_char(p_date, 'MM') || '-' ||
590 to_char(p_date, 'YYYY'), 'DD-MM-YYYY');
591 end if;
592
593 end next_lower_day;
594
598 -- calculation can be performed by adding days ie. straightforward addition
595 ---------------------------------------------------------------------------------------
596 -- This function performs the date calculation according to the base period type for
597 -- the period type being considered. Note that for WEEKLY base period, the
599 -- operation. For MONTHLY base period, the add_months() function is used. The
600 -- exception to these general categories is semi-monthly, which is handled explicitly
601 -- by the SEMIMONTHLY base period. This case only uses the p_multiple parameter to
602 -- determine whether to add or subtract a semi-month, ie. this function can only
603 -- increase semi-months unitarily. Furthermore, it has exclusive use of the
604 -- 'regular_pay_mode' parameter, which for SEMIMONTHLY, indicates whether or not we
605 -- are calculating for a regular payment date calculation. The p_fpe_date is the
606 -- first period's end-date and is needed only for semimonthly period calculations.
607 -- NOTE: This function is a copy of the core function in the hr_payrolls package,
608 -- which does not have a public interface.
609 ---------------------------------------------------------------------------------------
610 function add_multiple_of_base
611 (
612 p_target_date in date,
613 p_base_period_type in varchar2,
614 p_multiple in number,
615 p_fpe_date in date,
616 p_regular_pay_mode boolean default false,
617 p_pay_date_offset number default null
618 )
619 return date is
620
621 func_name CONSTANT varchar2(50) := 'za_pay_calendars.add_multiple_of_base';
622 rest_of_date varchar2(9);
623 temp_date date;
624
625 begin
626
627 -- Errors can occur when performing date manipulation.
628 if p_base_period_type = WEEKLY then
629 hr_utility.set_location(func_name, 1);
630 return (p_target_date + (7 * p_multiple));
631 elsif p_base_period_type = MONTHLY then
632 hr_utility.set_location(func_name, 2);
633 return (add_months(p_target_date, p_multiple));
634 else
635 -- This is semi-monthly. A pair of semi-months always spand a whole calendar
636 -- month. Their start and end dates are either 1st - 15th or 16th - last day of
637 -- month. This makes the addition/subtraction of a period reasonably
638 -- straightforward, if a little involved.
639 -- On the other hand, if the p_regular_pay_mode is TRUE, we have to take into
640 -- account the regular pay date offset.
641 if not p_regular_pay_mode then
642 if p_multiple > 0 then
643 -- Addition of one semi-month.
644 return(next_semi_month(p_target_date, p_fpe_date));
645 else
646 -- Substraction of one semi-month.
647 return(prev_semi_month(p_target_date, p_fpe_date));
648 end if;
649 else
650 -- Special calculation for regular payment date offset. In this special case,
651 -- we calculate the pay date of the previous semimonth period: Current
652 -- pay-date -> Current end-date -> Previous period's end-date -> Previous
653 -- period's pay-date.
654 temp_date := prev_semi_month(p_target_date - p_pay_date_offset,
655 p_fpe_date) + p_pay_date_offset;
656 hr_utility.trace('pay date ' || to_char(temp_date, 'dd-mon-yyyy'));
657 return(temp_date);
658 -- OLD CODE
659 -- Now period's are not guaranteed to end on the 15th or the end_of_month.
660 --
661 -- rest_of_date := substr(to_char(p_target_date, 'DD-MON-YYYY'), 3);
662 -- if p_target_date between
663 -- to_date(('01' || rest_of_date), 'DD-MON-YYYY') and
664 -- to_date(('15' || rest_of_date), 'DD-MON-YYYY')
665 -- then
666 -- -- First half of month, put to 15th and subtract a period.
667 -- temp_date := to_date(('15' || rest_of_date), 'DD-MON-YYYY');
668 -- temp_date := prev_semi_month(temp_date) + p_pay_date_offset;
669 -- hr_utility.trace('1st half ' || to_char(temp_date, 'dd-mon-yyyy'));
670 -- return(temp_date);
671 -- else
672 -- -- We are in second half of month.
673 -- temp_date := last_day(p_target_date);
674 -- temp_date := prev_semi_month(temp_date) + p_pay_date_offset;
675 -- hr_utility.trace('2nd half ' || to_char(temp_date, 'dd-mon-yyyy'));
676 -- return(temp_date);
677 -- end if;
678 -- OLD CODE
679 end if;
680 end if;
681 end add_multiple_of_base;
682
683 ---------------------------------------------------------------------------------------
684 -- Locally defined function that, given the end-date of a semi-month period and the
685 -- first period's end-date (p_fpe_date) returns the end date of the following
686 -- semi-monthly period.
687 -- NOTE: This function is a copy of the core function in the hr_payrolls package,
688 -- which does not have a public interface.
689 ---------------------------------------------------------------------------------------
690 function next_semi_month
691 (
692 p_semi_month_date in date,
693 p_fpe_date in date
694 )
695 return date is
696
697 day_of_month varchar2(2);
698 last_of_month date;
699 temp_day varchar2(2);
700 func_name CONSTANT varchar2(50) := 'za_pay_calendars.next_semi_month';
701
702 begin
703 hr_utility.set_location(func_name, 1);
704 day_of_month := substr(to_char(p_fpe_date, 'DD-MON-YYYY'), 1, 2);
705 if (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) then
706 -- The first period's end-date is either the 15th or the end-of-month
707 if last_day(p_semi_month_date) = p_semi_month_date then
711 -- 15th of month: return last day
708 -- End of month: add 15 days
709 return(p_semi_month_date + 15);
710 else
712 return(last_day(p_semi_month_date));
713 end if;
714 else
715 -- The first period's end-date is neither the 15th nor the end-of-month
716 -- temp_day = smaller of the 2 day numbers used to calc period end-dates
717 temp_day := day_of_month ;
718 if temp_day > '15' then
719 temp_day := substr(to_char(p_fpe_date - 15, 'DD-MON-YYYY'), 1, 2);
720 end if ;
721 --
722 day_of_month := substr(to_char(p_semi_month_date, 'DD-MON-YYYY'), 1, 2);
723 if day_of_month between '01' AND '15' then
724 if last_day(p_semi_month_date+15) = last_day(p_semi_month_date) then
725 return(p_semi_month_date + 15);
726 else
727 -- for p_semi_month_date = Feb 14th, for example
728 return(last_day(p_semi_month_date));
729 end if;
730 else -- if on the 16th or later
731 return(to_date( (temp_day ||
732 substr(to_char(add_months(p_semi_month_date,1),'DD-MON-YYYY'),3)
733 ), 'DD-MON-YYYY'));
734 end if ;
735 end if ;
736 end next_semi_month;
737
738 ---------------------------------------------------------------------------------------
739 -- Locally defined function that, given the end-date of a semi-month period and the
740 -- first period's end-date (p_fpe_date) returns the end date of the previous
741 -- semi-monthly period.
742 -- NOTE: This function is a copy of the core function in the hr_payrolls package,
743 -- which does not have a public interface.
744 ---------------------------------------------------------------------------------------
745 function prev_semi_month
746 (
747 p_semi_month_date in date,
748 p_fpe_date in date
749 )
750 return date is
751
752 day_of_month varchar2(2);
753 last_of_month date;
754 temp_date date;
755 temp_day varchar2(2);
756 func_name CONSTANT varchar2(50) := 'za_pay_calendars.prev_semi_month';
757
758 begin
759 -- Get the day of the current month.
760 hr_utility.set_location(func_name, 1);
761 day_of_month := substr(to_char(p_fpe_date, 'DD-MON-YYYY'), 1, 2);
762 if (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) then
763 -- The first period's end-date is either the 15th or the end-of-month
764 if last_day(p_semi_month_date) = p_semi_month_date then
765 -- End of month: return 15th of current month
766 return(add_months(p_semi_month_date,-1) + 15);
767 else
768 -- 15th of month: return last day of previous month
769 return(last_day(add_months(p_semi_month_date,-1)));
770 end if;
771 else
772 -- The first period's end-date is neither the 15th nor the end-of-month
773 -- temp_day = smaller of the 2 day numbers used to calc period end-dates
774 temp_day := day_of_month ;
775 if temp_day > '15' then
776 temp_day := substr(to_char(p_fpe_date - 15, 'DD-MON-YYYY'), 1, 2);
777 end if ;
778 --
779 day_of_month := substr(to_char(p_semi_month_date, 'DD-MON-YYYY'), 1, 2);
780 if day_of_month between '01' AND '15' then
781 temp_date := add_months (p_semi_month_date, -1) ;
782 if last_day(temp_date+15) = last_day(temp_date) then
783 return(temp_date + 15);
784 else
785 -- for p_semi_month_date = Mar 14th, for example
786 return(last_day(temp_date));
787 end if;
788 else -- if after the 16th
789 return(to_date( (temp_day ||
790 substr(to_char(p_semi_month_date,'DD-MON-YYYY'),3)
791 ), 'DD-MON-YYYY'));
792 end if ;
793 end if ;
794 end prev_semi_month;
795
796 ---------------------------------------------------------------------------------------
797 -- This procedure creates the payroll tax years for the ZA calendars, by using the
798 -- following steps:
799 -- 1. Determine the first tax year start in the generation period:
800 -- 2. Check whether the previous, current or next year is closest to the first
801 -- period's start date.
802 -- 3. If the first period's Payroll Month End is within 14 days of the Tax Year
803 -- End, then use that Month End as the first Employee Tax Year End;
804 -- else,
805 -- if the Payroll Month End is larger than the Tax Year End, add a year to the
806 -- Tax Year End. Loop forward through the periods until you find a Payroll
807 -- Month End that is larger than the Tax Year End, and determine whether the
808 -- current Payroll Month End or the previous one is closer to the Tax Year
809 -- End. Use the closest Payroll Month End as the first Employee Tax Year End.
810 -- 4. Update all the periods up to the end of this Payroll Month End with the
811 -- first Tax Year.
812 -- 5. Default the Tax Year for the rest of the periods:
813 -- 6. Get the number of periods in the year from per_time_period_types.
814 -- 7. Loop through all the remaining periods.
815 -- 8. Jump forward by the amount of periods in the year, and keep moving
816 -- forward until you find the last period in that Month End. Update the
817 -- periods in between to the next Tax year.
818 -- 9. Go to the beginning of the loop.
819 -- 10. If there are not enough periods to move forward by an entire year, then just
820 -- update the remaining periods to the next Tax Year.
821 -- ASSUMPTION: The tax year start is in 'DD-MON' format.
822 -- Parameters:
823 -- p_payroll_id - The primary key ID of the payroll.
827 -- p_proc_period_type - The processing period type, e.g. Week, Calendar Month.
824 -- p_first_end_date - The first period end date in the generation time span.
825 -- p_last_end_date - The last period end date in the generation time span.
826 -- p_tax_year_start - The date on which the tax year starts in DD-MON format.
828 ---------------------------------------------------------------------------------------
829 procedure create_za_employee_tax_years
830 (
831 p_payroll_id in number,
832 p_first_end_date in date,
833 p_last_end_date in date,
834 p_tax_year_start in varchar2,
835 p_proc_period_type in varchar2
836 )
837 is
838
839 -- Variable used to hold the start date.
840 l_start_date date;
841 -- Variable used to hold the end date.
842 l_end_date date;
843 -- Variable used to store the end date.
844 l_store_end_date date := p_first_end_date;
845 -- Variable used to store the end date.
846 l_old_end_date date;
847 -- Variable used to hold the Payroll Month End.
848 l_pay_me date;
849 -- Variable used to store the Payroll Month End.
850 l_old_pay_me date;
851 -- Variable used to hold the Tax Year.
852 l_tax_year date;
853 -- Variable used to move a certain amount of rows forward.
854 l_cur_row number;
855 -- The number of periods in the year.
856 l_no_periods number;
857 -- Function name used in trace package
858 func_name CONSTANT varchar2(50) := 'za_pay_calendars.create_za_employee_tax_years';
859 -- Check whether a year should be added
860 l_flag number := 0;
861
862 -- Cursor that holds all the periods in the generation time span.
863 cursor get_periods is
864 select
865 end_date, pay_advice_date
866 from
867 per_time_periods
868 where
869 payroll_id = p_payroll_id
870 and
871 end_date between p_first_end_date and p_last_end_date
872 order by
873 end_date;
874
875 -- Cursor that looks for a previous payroll month end
876 cursor get_previous_periods is
877 select
878 end_date, pay_advice_date
879 from
880 per_time_periods
881 where
882 payroll_id = p_payroll_id
883 and
884 end_date < p_first_end_date
885 order by
886 end_date desc;
887
888 begin
889
890 ---------------------------------------------------------------------------------------
891 -- Determine the first tax year start in the generation period
892 ---------------------------------------------------------------------------------------
893 hr_utility.set_location(func_name, 1);
894 declare
895 l_tax_year2 date;
896 begin
897 -- Obtain the first period's start date
898 select
899 start_date
900 into
901 l_start_date
902 from
903 per_time_periods
904 where
905 payroll_id = p_payroll_id
906 and
907 end_date = p_first_end_date;
908
909 -- Concatenate p_tax_year_start with the year of the start_date
910 l_tax_year := to_date(p_tax_year_start ||
911 to_char(l_start_date, 'YYYY'), 'DD-MON-YYYY');
912
913 -- Determine the next year's Tax year start
914 l_tax_year2 := to_date(p_tax_year_start ||
915 to_char(add_months(l_start_date, 12), 'YYYY'),
916 'DD-MON-YYYY');
917
918 -- Determine which one of the Tax Year Starts are the closest to the start date
919 if abs(l_tax_year2 - l_start_date) < abs(l_tax_year - l_start_date) then
920 l_tax_year := l_tax_year2;
921 end if;
922
923 -- Convert to a Tax Year End
924 l_tax_year := l_tax_year - 1;
925
926 exception
927 when no_data_found then
928 -- Create a dummy, since it won't be used anyway
929 l_tax_year := to_date('01-03-1001', 'DD-MM-YYYY');
930 end;
931
932 open get_periods;
933 fetch get_periods into l_end_date, l_old_pay_me;
934 if get_periods%notfound then
935 -- No periods to generate Tax Years for.
936 null;
937 else
938 -- If the first period's Payroll Month End is within 14 days of the Tax Year
939 -- End, then use that Month End as the first Employee Tax Year End
940 if abs(l_old_pay_me-l_tax_year) < 15 then
941 l_tax_year := l_old_pay_me;
942
943 -- Find the last end date in the Payroll Month End
944 select
945 max(end_date)
946 into
947 l_store_end_date
948 from
949 per_time_periods
950 where
951 payroll_id = p_payroll_id
952 and
953 pay_advice_date = l_old_pay_me;
954 else
955 -- If the Payroll Month End is larger than the Tax Year End, then add a year
956 -- to the Tax Year End
957 if l_old_pay_me > l_tax_year then
958 l_tax_year := add_months(l_tax_year, 12);
959 end if;
960
961 loop
962 -- Get the current period's Payroll Month End
963 fetch get_periods into l_end_date, l_pay_me;
964 if get_periods%notfound then
965 l_flag := 1;
966 end if;
967 exit when get_periods%notfound;
968
969 -- If the current Payroll Month End is larger than the Tax Year End, then
973
970 -- decide whether this Payroll Month End or the previous one is closer to
971 -- the Tax Year End. Use that Month End as the first Employee Tax Year End.
972 if l_pay_me > l_tax_year then
974 -- Determine whether this Payroll Month End or the previous one is
975 -- closer to the Tax Year End.
976 if abs(l_old_pay_me - l_tax_year) < abs(l_pay_me - l_tax_year) then
977 l_tax_year := l_old_pay_me;
978 l_store_end_date := l_old_end_date;
979 else
980 l_tax_year := l_pay_me;
981
982 -- Find the last end date in the Payroll Month End
983 select
984 max(end_date)
985 into
986 l_store_end_date
987 from
988 per_time_periods
989 where
990 payroll_id = p_payroll_id
991 and
992 pay_advice_date = l_pay_me;
993 end if;
994
995 -- Exit the loop
996 exit;
997
998 end if;
999
1000 -- Store the previous Payroll Month End and End Date
1001 l_old_pay_me := l_pay_me;
1002 l_old_end_date := l_end_date;
1003 end loop;
1004 end if;
1005
1006 -- Update the first periods to the first tax year
1007 update
1008 per_time_periods
1009 set
1010 prd_information1 = to_char(l_tax_year, 'YYYY'),
1011 prd_information_category = 'ZA'
1012 where
1013 payroll_id = p_payroll_id
1014 and
1015 end_date between p_first_end_date and l_store_end_date;
1016 end if;
1017 close get_periods;
1018
1019 ---------------------------------------------------------------------------------------
1020 -- Now default the tax year for the rest of the periods
1021 ---------------------------------------------------------------------------------------
1022 open get_periods;
1023 fetch get_periods into l_end_date, l_pay_me;
1024 if get_periods%notfound then
1025 -- No periods to generate Tax Years for.
1026 null;
1027 else
1028 -- Move to the next period to process
1029 while l_end_date <= l_store_end_date loop
1030 -- Get the current period's details
1031 fetch get_periods into l_end_date, l_pay_me;
1032 exit when get_periods%notfound;
1033 end loop;
1034
1035 -- If there are no more periods to process then do nothing
1036 if get_periods%notfound then
1037 -- This should never happen
1038 null;
1039 else
1040 begin
1041 -- Get the number of periods in the year
1042 select
1043 number_per_fiscal_year
1044 into
1045 l_no_periods
1046 from
1047 per_time_period_types
1048 where
1049 period_type = p_proc_period_type;
1050
1051 exception
1052 -- No details exists for this period type
1053 when no_data_found then
1054 hr_utility.set_message(800, 'PAY_NO_PERIOD_DETAILS');
1055 hr_utility.raise_error;
1056 end;
1057
1058 -- Loop through all the periods until you are finished
1059 while not get_periods%notfound loop
1060
1061 -- Estimate the location of the next tax year, by adding the amount of
1062 -- payroll periods in the tax year to the current position
1063 l_cur_row := get_periods%rowcount + l_no_periods - 1;
1064
1065 -- Save the current end date
1066 l_old_end_date := l_end_date;
1067
1068 -- Determine the current tax year
1069 if l_flag = 1 then
1070 l_flag := 0;
1071 else
1072 l_tax_year := add_months(l_tax_year, 12);
1073 end if;
1074
1075 -- Move forward by the amount of payroll periods in the tax year
1076 while get_periods%rowcount < l_cur_row loop
1077 -- Get the current period's details
1078 fetch get_periods into l_end_date, l_pay_me;
1079 exit when get_periods%notfound;
1080 end loop;
1081
1082 -- If we got to the end of the cursor then we have to update the tax year up
1083 -- to the end of the cursor, otherwise move forward until you find the last
1084 -- period in that Payroll Month End
1085 if get_periods%notfound then
1086 -- Update the periods up to the last period end date
1087 update
1088 per_time_periods
1089 set
1090 prd_information1 = to_char(l_tax_year, 'YYYY'),
1091 prd_information_category = 'ZA'
1092 where
1093 payroll_id = p_payroll_id
1094 and
1095 end_date between l_old_end_date and p_last_end_date;
1096 else
1097 -- Find the last period in the current Payroll Month End
1098 l_old_pay_me := l_pay_me;
1099 while l_pay_me = l_old_pay_me loop
1100 fetch get_periods into l_end_date, l_pay_me;
1101 exit when get_periods%notfound;
1102 -- Save the previous end date
1103 l_store_end_date := l_end_date;
1104 end loop;
1105
1106 if get_periods%notfound then
1110 set
1107 -- Update the periods up to the last period end date
1108 update
1109 per_time_periods
1111 prd_information1 = to_char(l_tax_year, 'YYYY'),
1112 prd_information_category = 'ZA'
1113 where
1114 payroll_id = p_payroll_id
1115 and
1116 end_date between l_old_end_date and p_last_end_date;
1117 else
1118 -- Update the periods up to the previous period
1119 update
1120 per_time_periods
1121 set
1122 prd_information1 = to_char(l_tax_year, 'YYYY'),
1123 prd_information_category = 'ZA'
1124 where
1125 payroll_id = p_payroll_id
1126 and
1127 end_date between l_old_end_date and l_store_end_date;
1128 end if;
1129 end if;
1130 end loop;
1131 end if;
1132 end if;
1133 close get_periods;
1134 end create_za_employee_tax_years;
1135
1136 ---------------------------------------------------------------------------------------
1137 -- This procedure creates the payroll calendar years for the ZA calendars, by using
1138 -- the following steps:
1139 -- 1. Determine the first calendar year start in the generation period:
1140 -- 2. Check whether the previous, current or next year is closest to the first
1141 -- period's start date.
1142 -- 3. If the first period's Payroll Month End is within 14 days of the Cal Year
1143 -- End, then use that Month End as the first Employee Cal Year End;
1144 -- else,
1145 -- if the Payroll Month End is larger than the Cal Year End, add a year to the
1146 -- Cal Year End. Loop forward through the periods until you find a Payroll
1147 -- Month End that is larger than the Cal Year End, and determine whether the
1148 -- current Payroll Month End or the previous one is closer to the Cal Year
1149 -- End. Use the closest Payroll Month End as the first Employee Cal Year End.
1150 -- 4. Update all the periods up to the end of this Payroll Month End with the
1151 -- first Cal Year.
1152 -- 5. Default the Cal Year for the rest of the periods:
1153 -- 6. Get the number of periods in the year from per_time_period_types.
1154 -- 7. Loop through all the remaining periods.
1155 -- 8. Jump forward by the amount of periods in the year, and keep moving
1156 -- forward until you find the last period in that Month End. Update the
1157 -- periods in between to the next Cal year.
1158 -- 9. Go to the beginning of the loop.
1159 -- 10. If there are not enough periods to move forward by an entire year, then just
1160 -- update the remaining periods to the next Cal Year.
1161 -- Parameters:
1162 -- p_payroll_id - The primary key ID of the payroll.
1163 -- p_first_end_date - The first period end date in the generation time span.
1164 -- p_last_end_date - The last period end date in the generation time span.
1165 -- p_proc_period_type - The processing period type, e.g. Week, Calendar Month.
1166 ---------------------------------------------------------------------------------------
1167 procedure create_za_employee_cal_years
1168 (
1169 p_payroll_id in number,
1170 p_first_end_date in date,
1171 p_last_end_date in date,
1172 p_proc_period_type in varchar2
1173 )
1174 is
1175
1176 -- Variable used to hold the start date.
1177 l_start_date date;
1178 -- Variable used to hold the end date.
1179 l_end_date date;
1180 -- Variable used to store the end date.
1181 l_store_end_date date;
1182 -- Variable used to store the end date.
1183 l_old_end_date date;
1184 -- Variable used to hold the Payroll Month End.
1185 l_pay_me date;
1186 -- Variable used to store the Payroll Month End.
1187 l_old_pay_me date;
1188 -- Variable used to hold the Calendar Year.
1189 l_cal_year date;
1190 -- Variable used to move a certain amount of rows forward.
1191 l_cur_row number;
1192 -- The number of periods in the year.
1193 l_no_periods number;
1194 -- Function name used in trace package
1195 func_name CONSTANT varchar2(50) := 'za_pay_calendars.create_za_employee_cal_years';
1196
1197 -- Cursor that holds all the periods in the generation time span.
1198 cursor get_periods is
1199 select
1200 end_date, pay_advice_date
1201 from
1202 per_time_periods
1203 where
1204 payroll_id = p_payroll_id
1205 and
1206 end_date between p_first_end_date and p_last_end_date
1207 order by
1208 end_date;
1209
1210 begin
1211
1212 ---------------------------------------------------------------------------------------
1213 -- Determine the first calendar year start in the generation period
1214 ---------------------------------------------------------------------------------------
1215 hr_utility.set_location(func_name, 1);
1216 declare
1217 l_cal_year2 date;
1218 begin
1219 -- Obtain the first period's start date
1220 select
1221 start_date
1222 into
1223 l_start_date
1224 from
1225 per_time_periods
1226 where
1227 payroll_id = p_payroll_id
1228 and
1229 end_date = p_first_end_date;
1230
1234
1231 -- Concatenate 01-JAN with the year of the start_date
1232 l_cal_year := to_date('01-JAN' ||
1233 to_char(l_start_date, 'YYYY'), 'DD-MON-YYYY');
1235 -- Determine the next year's Calendar year start
1236 l_cal_year2 := to_date('01-JAN' ||
1237 to_char(add_months(l_start_date, 12), 'YYYY'),
1238 'DD-MON-YYYY');
1239
1240 -- Determine which one of the Cal Year Starts are the closest to the start date
1241 if abs(l_cal_year2 - l_start_date) < abs(l_cal_year - l_start_date) then
1242 l_cal_year := l_cal_year2;
1243 end if;
1244
1245 -- Convert to a Calendar Year End
1246 l_cal_year := l_cal_year - 1;
1247
1248 exception
1249 when no_data_found then
1250 -- Create a dummy, since it won't be used anyway
1251 l_cal_year := to_date('01-01-1001', 'DD-MM-YYYY');
1252 end;
1253
1254 open get_periods;
1255 fetch get_periods into l_end_date, l_old_pay_me;
1256 if get_periods%notfound then
1257 -- No periods to generate Calendar Years for.
1258 null;
1259 else
1260 -- If the first period's Payroll Month End is within 14 days of the Cal Year
1261 -- End, then use that Month End as the first Employee Cal Year End
1262 if abs(l_old_pay_me-l_cal_year) < 15 then
1263 l_cal_year := l_old_pay_me;
1264
1265 -- Find the last end date in the Payroll Month End
1266 select
1267 max(end_date)
1268 into
1269 l_store_end_date
1270 from
1271 per_time_periods
1272 where
1273 payroll_id = p_payroll_id
1274 and
1275 pay_advice_date = l_old_pay_me;
1276
1277 else
1278 -- If the Payroll Month End is larger than the Cal Year End, then add a year
1279 -- to the Cal Year End
1280 if l_old_pay_me > l_cal_year then
1281 l_cal_year := add_months(l_cal_year, 12);
1282 end if;
1283
1284 loop
1285 -- Get the current period's Payroll Month End
1286 fetch get_periods into l_end_date, l_pay_me;
1287 exit when get_periods%notfound;
1288
1289 -- If the current Payroll Month End is larger than the Cal Year End, then
1290 -- decide whether this Payroll Month End or the previous one is closer to
1291 -- the Cal Year End. Use that Month End as the first Employee Cal Year End.
1292 if l_pay_me > l_cal_year then
1293
1294 -- Determine whether this Payroll Month End or the previous one is
1295 -- closer to the Calendar Year End.
1296 if abs(l_old_pay_me - l_cal_year) < abs(l_pay_me - l_cal_year) then
1297 l_cal_year := l_old_pay_me;
1298 l_store_end_date := l_old_end_date;
1299 else
1300 l_cal_year := l_old_pay_me;
1301
1302 -- Find the last end date in the Payroll Month End
1303 select
1304 max(end_date)
1305 into
1306 l_store_end_date
1307 from
1308 per_time_periods
1309 where
1310 payroll_id = p_payroll_id
1311 and
1312 pay_advice_date = l_pay_me;
1313 end if;
1314
1315 -- Exit the loop
1316 exit;
1317
1318 end if;
1319
1320 -- Store the previous Payroll Month End and End Date
1321 l_old_pay_me := l_pay_me;
1322 l_old_end_date := l_end_date;
1323 end loop;
1324 end if;
1325
1326 -- Update the first periods to the first calendar year
1327 update
1328 per_time_periods
1329 set
1330 prd_information3 = to_char(l_cal_year, 'YYYY'),
1331 prd_information_category = 'ZA'
1332 where
1333 payroll_id = p_payroll_id
1334 and
1335 end_date between p_first_end_date and l_store_end_date;
1336 end if;
1337 close get_periods;
1338
1339 ---------------------------------------------------------------------------------------
1340 -- Now default the calendar year for the rest of the periods
1341 ---------------------------------------------------------------------------------------
1342 open get_periods;
1343 fetch get_periods into l_end_date, l_pay_me;
1344 if get_periods%notfound then
1345 -- No periods to generate Calendar Years for.
1346 null;
1347 else
1348 -- Move to the next period to process
1349 while l_end_date <= l_store_end_date loop
1350 -- Get the current period's details
1351 fetch get_periods into l_end_date, l_pay_me;
1352 exit when get_periods%notfound;
1353 end loop;
1354
1355 -- If there are no more periods to process then do nothing
1356 if get_periods%notfound then
1357 -- This should never happen
1358 null;
1359 else
1360 begin
1361 -- Get the number of periods in the year
1362 select
1363 number_per_fiscal_year
1364 into
1365 l_no_periods
1366 from
1367 per_time_period_types
1368 where
1369 period_type = p_proc_period_type;
1370
1371 exception
1375 hr_utility.raise_error;
1372 -- No details exists for this period type
1373 when no_data_found then
1374 hr_utility.set_message(800, 'PAY_NO_PERIOD_DETAILS');
1376
1377 end;
1378
1379 -- Loop through all the periods until you are finished
1380 while not get_periods%notfound loop
1381
1382 -- Estimate the location of the next cal year, by adding the amount of
1383 -- payroll periods in the calendar year to the current position.
1384 l_cur_row := get_periods%rowcount + l_no_periods - 1;
1385
1386 -- Save the current end date
1387 l_old_end_date := l_end_date;
1388
1389 -- Determine the current calendar year
1390 l_cal_year := add_months(l_cal_year, 12);
1391
1392 -- Move forward by the amount of payroll periods in the calendar year
1393 while get_periods%rowcount < l_cur_row loop
1394 -- Get the current period's details
1395 fetch get_periods into l_end_date, l_pay_me;
1396 exit when get_periods%notfound;
1397 end loop;
1398
1399 -- If we got to the end of the cursor then we have to update the cal year up
1400 -- to the end of the cursor, otherwise move forward until you find the last
1401 -- period in that Payroll Month End
1402 if get_periods%notfound then
1403 -- Update the periods up to the last period end date
1404 update
1405 per_time_periods
1406 set
1407 prd_information3 = to_char(l_cal_year, 'YYYY'),
1408 prd_information_category = 'ZA'
1409 where
1410 payroll_id = p_payroll_id
1411 and
1412 end_date between l_old_end_date and p_last_end_date;
1413 else
1414 -- Find the last period in the current Payroll Month End
1415 l_old_pay_me := l_pay_me;
1416 while l_pay_me = l_old_pay_me loop
1417 fetch get_periods into l_end_date, l_pay_me;
1418 exit when get_periods%notfound;
1419 -- Save the previous end date
1420 l_store_end_date := l_end_date;
1421 end loop;
1422
1423 if get_periods%notfound then
1424 -- Update the periods up to the last period end date
1425 update
1426 per_time_periods
1427 set
1428 prd_information3 = to_char(l_cal_year, 'YYYY'),
1429 prd_information_category = 'ZA'
1430 where
1431 payroll_id = p_payroll_id
1432 and
1433 end_date between l_old_end_date and p_last_end_date;
1434 else
1435 -- Update the periods up to the previous period
1436 update
1437 per_time_periods
1438 set
1439 prd_information3 = to_char(l_cal_year, 'YYYY'),
1440 prd_information_category = 'ZA'
1441 where
1442 payroll_id = p_payroll_id
1443 and
1444 end_date between l_old_end_date and l_store_end_date;
1445 end if;
1446 end if;
1447 end loop;
1448 end if;
1449 end if;
1450 close get_periods;
1451 end create_za_employee_cal_years;
1452
1453 ---------------------------------------------------------------------------------------
1454 -- This procedure creates the payroll tax quarters for the ZA calendars, using the
1455 -- following steps:
1456 -- 1. Determine the tax year of the first period.
1457 -- 2. Default the tax quarters for this tax year by counting down from the end of
1458 -- the tax year, instead of up as explained in the following steps.
1459 -- 3. Loop through the rest of the periods.
1460 -- 4. Every time you encounter a new Payroll Month End increment the counter.
1461 -- 5. If you have counted 3 Payroll Month Ends, update the periods in between
1462 -- to the current quarter value. Increment the quarter value, if the quarter
1463 -- value is 5 then reset it to 1.
1464 -- 6. Next period.
1465 -- 7. If you did not find 3 Payroll Month Ends, but there are still some periods
1466 -- left, then update these periods to the current quarter value.
1467 -- Parameters:
1468 -- p_payroll_id - The primary key ID of the payroll.
1469 -- p_first_end_date - The first period end date in the generation time span.
1470 -- p_last_end_date - The last period end date in the generation time span.
1471 -- ASSUMPTION: The tax year and payroll month end details were already defaulted.
1472 -- NOTE: This procedure currently only handles Week and Calendar Month period types.
1473 ---------------------------------------------------------------------------------------
1474 procedure create_za_tax_quarters
1475 (
1476 p_payroll_id in number,
1477 p_first_end_date in date,
1478 p_last_end_date in date
1479 )
1480 is
1481
1482 -- Temporary variable that holds the tax year of the first period
1483 l_tax_year per_time_periods.prd_information1%TYPE;
1484 -- Variable used to store the end date of the period
1485 l_old_end_date date;
1486 -- Variable used to hold the end date of the period
1487 l_end_date date;
1488 -- Variable used to store the Payroll Month End of the period
1489 l_old_pay_me date;
1490 -- Variable used to hold the Payroll Month End of the period
1491 l_pay_me date;
1495 l_quarter number;
1492 -- Variable used to count the Payroll Month Ends
1493 l_count number;
1494 -- Variable used to count the Tax Quarters
1496 -- Variable used to check for one row left at end of generation span
1497 l_flag number := 0;
1498 -- Function name used in trace package
1499 func_name CONSTANT varchar2(50) := 'za_pay_calendars.create_za_tax_quarters';
1500
1501 -- The periods after the first tax year
1502 cursor get_periods(c_tax_year per_time_periods.prd_information1%TYPE) is
1503 select
1504 end_date, pay_advice_date
1505 from
1506 per_time_periods
1507 where
1508 payroll_id = p_payroll_id
1509 and
1510 end_date between p_first_end_date and p_last_end_date
1511 and
1512 prd_information1 > c_tax_year
1513 order by
1514 end_date;
1515
1516 -- The periods in the first tax year, in reverse order
1517 cursor get_periods2(c_tax_year per_time_periods.prd_information1%TYPE) is
1518 select
1519 end_date, pay_advice_date
1520 from
1521 per_time_periods
1522 where
1523 payroll_id = p_payroll_id
1524 and
1525 prd_information1 = c_tax_year
1526 order by
1527 end_date DESC;
1528
1529 begin
1530
1531 hr_utility.set_location(func_name, 1);
1532 begin
1533 -- Determine the tax year of the first period
1534 select
1535 prd_information1
1536 into
1537 l_tax_year
1538 from
1539 per_time_periods
1540 where
1541 payroll_id = p_payroll_id
1542 and
1543 end_date = p_first_end_date;
1544
1545 exception
1546 -- Create a dummy, since it won't be used anyway
1547 when no_data_found then
1548 l_tax_year := '1001';
1549
1550 end;
1551
1552 -- Variable used to count 3 payroll months
1553 l_count := 1;
1554 -- Variable used to default quarter
1555 l_quarter := 5;
1556 open get_periods2(l_tax_year);
1557 fetch get_periods2 into l_old_end_date, l_old_pay_me;
1558 if get_periods2%notfound then
1559 -- No periods to generate Tax Quarters for.
1560 null;
1561 else
1562 loop
1563 -- Get the current period's Payroll Month End
1564 fetch get_periods2 into l_end_date, l_pay_me;
1565 if get_periods2%notfound then
1566 if get_periods2%rowcount = 1 then
1567 -- Set l_end_date so that only 1 row will be updated
1568 l_end_date := l_old_end_date - 1;
1569 exit;
1570 else
1571 exit;
1572 end if;
1573 end if;
1574
1575 if l_pay_me < l_old_pay_me then
1576
1577 -- If we have entered a new Payroll Month End increment the counter
1578 l_count := l_count + 1;
1579
1580 -- Store the old Payroll Month end
1581 l_old_pay_me := l_pay_me;
1582
1583 -- Check whether we have reached the last Payroll Month End in the quarter
1584 if l_count = 4 then
1585
1586 -- Reset the counter
1587 l_count := 1;
1588
1589 -- Increment the quarter
1590 l_quarter := l_quarter - 1;
1591 if l_quarter = 0 then
1592 l_quarter := 4;
1593 end if;
1594
1595 -- Update the periods with the quarter
1596 update
1597 per_time_periods
1598 set
1599 prd_information2 = to_char(l_quarter),
1600 prd_information_category = 'ZA'
1601 where
1602 payroll_id = p_payroll_id
1603 and
1604 end_date between l_end_date and l_old_end_date;
1605
1606 -- Store the beginning of the next quarter
1607 l_old_end_date := l_end_date;
1608 end if;
1609
1610 end if;
1611 end loop;
1612
1613 -- Default the last few periods if needed
1614 if l_end_date < l_old_end_date then
1615
1616 -- Increment the quarter
1617 l_quarter := l_quarter - 1;
1618 if l_quarter = 0 then
1619 l_quarter := 4;
1620 end if;
1621
1622 -- Update the periods with the quarter
1623 update
1624 per_time_periods
1625 set
1626 prd_information2 = to_char(l_quarter),
1627 prd_information_category = 'ZA'
1628 where
1629 payroll_id = p_payroll_id
1630 and
1631 end_date between l_end_date and l_old_end_date;
1632
1633 end if;
1634 end if;
1635 close get_periods2;
1636
1637 -- Variable used to count 3 payroll months
1638 l_count := 1;
1639 -- Variable used to default quarter
1640 l_quarter := 0;
1641 open get_periods(l_tax_year);
1642 fetch get_periods into l_old_end_date, l_old_pay_me;
1643 if get_periods%notfound then
1644 -- No periods to generate Tax Quarters for.
1645 null;
1646 else
1647 loop
1648 -- Get the current period's Payroll Month End
1649 fetch get_periods into l_end_date, l_pay_me;
1650 exit when get_periods%notfound;
1651
1652 if l_pay_me > l_old_pay_me then
1653
1654 -- Check whether there is one row left to generate
1658
1655 if l_end_date = p_last_end_date then
1656 l_flag := 1;
1657 end if;
1659 -- If we have entered a new Payroll Month End increment the counter
1660 l_count := l_count + 1;
1661
1662 -- Store the old Payroll Month end
1663 l_old_pay_me := l_pay_me;
1664
1665 -- Check whether we have reached the last Payroll Month End in the quarter
1666 if l_count = 4 then
1667
1668 -- Reset the counter
1669 l_count := 1;
1670
1671 -- Increment the quarter
1672 l_quarter := l_quarter + 1;
1673 if l_quarter = 5 then
1674 l_quarter := 1;
1675 end if;
1676
1677 -- Update the periods with the quarter
1678 update
1679 per_time_periods
1680 set
1681 prd_information2 = to_char(l_quarter),
1682 prd_information_category = 'ZA'
1683 where
1684 payroll_id = p_payroll_id
1685 and
1686 end_date between l_old_end_date and l_end_date;
1687
1688 -- Store the beginning of the next quarter
1689 l_old_end_date := l_end_date;
1690 end if;
1691
1692 end if;
1693 end loop;
1694
1695 -- Default the last few periods if needed
1696 if (l_end_date > l_old_end_date) or l_flag = 1 then
1697
1698 -- Increment the quarter
1699 l_quarter := l_quarter + 1;
1700 if l_quarter = 5 then
1701 l_quarter := 1;
1702 end if;
1703
1704 -- Update the periods with the quarter
1705 update
1706 per_time_periods
1707 set
1708 prd_information2 = to_char(l_quarter),
1709 prd_information_category = 'ZA'
1710 where
1711 payroll_id = p_payroll_id
1712 and
1713 end_date between l_old_end_date and l_end_date;
1714
1715 end if;
1716 end if;
1717 close get_periods;
1718
1719 end create_za_tax_quarters;
1720
1721 ---------------------------------------------------------------------------------------
1722 -- This procedure creates the period numbers for the ZA calendars. This procedure
1723 -- should be called every time a change is made to the Payroll Tax Year field on the
1724 -- Additional Period Information Flexfield. It uses the following steps:
1725 -- 1. Store the first period number of the first tax year in l_period, by selecting
1726 -- it from per_time_periods.
1727 -- 2. Loop through the tax years.
1728 -- 3. Loop through the periods in the current tax year.
1729 -- 4. Update the period number of the current period to l_period.
1730 -- 5. Increment l_period.
1731 -- 4. Next period.
1732 -- 5. Reset l_period to 1.
1733 -- 6. Next tax year.
1734 -- NOTE: There is no need to call this procedure during the period generation
1735 -- process, the default period numbers should be correct.
1736 -- NOTE: The procedure should be called with the end date of the period on which
1737 -- the change occured as p_first_end_date, and the end date of the last period in
1738 -- p_last_end_date
1739 ---------------------------------------------------------------------------------------
1740 procedure create_za_period_numbers
1741 (
1742 p_payroll_id in number,
1743 p_first_end_date in date,
1744 p_last_end_date in date,
1745 p_proc_period_type in varchar2
1746 )
1747 is
1748
1749 -- Variable used to hold the current tax year
1750 l_tax_year per_time_periods.prd_information1%TYPE;
1751 -- The rowid of the period to be updated
1752 l_rowid rowid;
1753 -- The current period's number
1754 l_period number;
1755 -- The end date of the current period
1756 l_end_date date;
1757 -- Function name used in trace package
1758 func_name CONSTANT varchar2(50) := 'za_pay_calendars.create_za_period_numbers';
1759
1760 -- All tax years in the generation time span.
1761 cursor tax_years is
1762 select distinct
1763 prd_information1
1764 from
1765 per_time_periods
1766 where
1767 payroll_id = p_payroll_id
1768 and
1769 end_date between p_first_end_date and p_last_end_date
1770 order by
1771 prd_information1;
1772
1773 -- The periods in the current tax year.
1774 cursor get_periods(c_tax_year per_time_periods.prd_information1%TYPE) is
1775 select
1776 rowid, end_date
1777 from
1778 per_time_periods
1779 where
1780 payroll_id = p_payroll_id
1781 and
1782 end_date between p_first_end_date and p_last_end_date
1783 and
1784 prd_information1 = c_tax_year
1785 order by
1786 end_date;
1787
1788 begin
1789
1790 hr_utility.set_location(func_name, 1);
1791 open tax_years;
1792 fetch tax_years into l_tax_year;
1793 if tax_years%notfound then
1794 -- This should never happen
1795 null;
1796 else
1797
1798 begin
1799 -- Determine the first period number of the first tax year
1800 select
1801 period_num
1802 into
1803 l_period
1804 from
1805 per_time_periods
1806 where
1807 payroll_id = p_payroll_id
1808 and
1809 end_date = p_first_end_date;
1810
1811 exception
1812 -- Create a dummy, since it won't be used anyway.
1813 when no_data_found then
1814 l_period := 1;
1815
1816 end;
1817
1818 -- Loop through tax years
1819 loop
1820
1821 open get_periods(l_tax_year);
1822 -- Loop through periods in current tax year
1823 loop
1824
1825 -- Get the period
1826 fetch get_periods into l_rowid, l_end_date;
1827 exit when get_periods%notfound;
1828
1829 -- Update the period number of the current period
1830 update
1831 per_time_periods
1832 set
1833 period_num = l_period
1834 where
1835 rowid = l_rowid;
1836
1837 -- Update the period name of the current period
1838 update
1839 per_time_periods
1840 set
1841 period_name = to_char(l_period) || ' ' || to_char(l_end_date, 'YYYY')
1842 || ' ' || p_proc_period_type
1843 where
1844 rowid = l_rowid;
1845
1846 -- Increment the period number
1847 l_period := l_period + 1;
1848
1849 end loop;
1850 close get_periods;
1851
1852 -- Reset the period number
1853 l_period := 1;
1854
1855 -- Get the tax year
1856 fetch tax_years into l_tax_year;
1857 exit when tax_years%notfound;
1858
1859 end loop;
1860
1861 end if;
1862 close tax_years;
1863
1864 end create_za_period_numbers;
1865
1866 ---------------------------------------------------------------------------------------
1867
1868 end pay_za_calendars_pkg;
[Home] [Help]