1 package body hxc_period_evaluation as
2 /* $Header: hxcperevl.pkb 120.3 2006/05/31 10:19:54 sechandr noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 FUNCTION make_date(p_day varchar2,
7 p_month_year varchar2) RETURN DATE
8 IS
9 BEGIN
10 RETURN to_date(p_day||'-'||p_month_year,'DD-MM-YYYY');
11 END make_date;
12
13 FUNCTION get_period_list (p_current_date date,
14 p_recurring_period_type varchar2,
15 p_duration_in_days number,
16 p_rec_period_start_date date,
17 p_max_date_in_futur date,
18 p_max_date_in_past date)
19 return period_list IS
20
21 l_multiple number;
22 l_base varchar2(1);
23
24 l_temporary_date date := p_max_date_in_futur;
25 l_period_start date;
26 l_period_end date;
27 l_index_period_list number :=0;
28
29 l_period_list period_list;
30
31 l_max_date_in_past date := p_max_date_in_past;
32
33 BEGIN
34
35 IF p_recurring_period_type is not null THEN
36
37 --populate the table with the period in the futur
38 get_period_details(p_proc_period_type => p_recurring_period_type,
39 p_base_period_type => l_base,
40 p_multiple => l_multiple);
41
42 IF p_rec_period_start_date > p_max_date_in_past THEN
43 l_max_date_in_past := p_rec_period_start_date;
44 END IF;
45
46 while l_temporary_date > l_max_date_in_past loop
47
48 period_start_stop(p_current_date => l_temporary_date,
49 p_rec_period_start_date => p_rec_period_start_date,
50 l_period_start => l_period_start,
51 l_period_end => l_period_end,
52 l_base_period_type => l_base,
53 p_multiple => l_multiple);
54
55 if l_period_list.exists(l_period_list.last) then
56 l_index_period_list := l_period_list.last;
57 end if;
58
59 l_period_list(to_number(to_char(l_period_start,'J'))).start_date := l_period_start;
60 l_period_list(to_number(to_char(l_period_start,'J'))).end_date := l_period_end;
61
62 --l_period_list(l_index_period_list + 1).start_date := l_period_start;
63 --l_period_list(l_index_period_list + 1).end_date := l_period_end;
64
65 l_temporary_date := l_period_start - 1;
66
67 END loop;
68
69 ELSIF p_duration_in_days is not null THEN
70
71 -- get the first period from the end_date
72 l_period_start := p_rec_period_start_date +
73 (p_duration_in_days * FLOOR(((p_max_date_in_futur - p_rec_period_start_date)/p_duration_in_days)));
74
75 l_period_end := l_period_start + p_duration_in_days - 1;
76
77 -- populate the table
78 if l_period_list.exists(l_period_list.last) then
79 l_index_period_list := l_period_list.last;
80 end if;
81
82 l_period_list(to_number(to_char(l_period_start,'J'))).start_date := l_period_start;
83 l_period_list(to_number(to_char(l_period_start,'J'))).end_date := l_period_end;
84
85 --l_period_list(l_index_period_list + 1).start_date := l_period_start;
86 --l_period_list(l_index_period_list + 1).end_date := l_period_end;
87
88 l_temporary_date := l_period_start - 1;
89
90 -- now loop to the past period
91 while l_temporary_date > p_max_date_in_past loop
92 -- populate the table
93
94 if l_period_list.exists(l_period_list.last) then
95 l_index_period_list := l_period_list.last;
96 end if;
97
98 l_period_list(to_number(to_char(l_temporary_date - p_duration_in_days + 1,'J'))).start_date
99 := l_temporary_date - p_duration_in_days + 1 ;
100 l_period_list(to_number(to_char(l_temporary_date - p_duration_in_days + 1,'J'))).end_date := l_temporary_date;
101
102 --l_period_list(l_index_period_list + 1).start_date := l_temporary_date - p_duration_in_days + 1 ;
103 --l_period_list(l_index_period_list + 1).end_date := l_temporary_date;
104
105 l_temporary_date := l_temporary_date - p_duration_in_days;
106
107 END LOOP;
108 END IF;
109 return l_period_list;
110
111 END get_period_list;
112
113 PROCEDURE get_period_details (p_proc_period_type IN VARCHAR2,
114 p_base_period_type OUT NOCOPY VARCHAR2,
115 p_multiple OUT NOCOPY NUMBER) IS
116 --
117 l_no_periods per_time_period_types.number_per_fiscal_year%type;
118 --
119 l_proc VARCHAR2(100);
120 --
121
122 l_iter BINARY_INTEGER;
123 l_cached BOOLEAN := false;
124
125 CURSOR c_get_no_periods IS
126 SELECT number_per_fiscal_year
127 FROM per_time_period_types
128 WHERE period_type = p_proc_period_type;
129
130 BEGIN
131
132 g_debug := hr_utility.debug_enabled;
133
134 --
135 if g_debug then
136 l_proc := 'hxc_period_evaluation.GET_PERIOD_DETAILS';
137 hr_utility.set_location(l_proc, 10);
138 end if;
139 --
140
141 --Let us check the cached table
142
143 l_iter := g_per_time_period_types_ct.first;
144 WHILE l_iter IS NOT NULL
145 LOOP
146 if (g_per_time_period_types_ct(l_iter).p_proc_period_type = p_proc_period_type)
147 then
148 l_no_periods := g_per_time_period_types_ct(l_iter).number_per_fiscal_year;
149 l_cached := true;
150 exit;
151 end if;
152 l_iter := g_per_time_period_types_ct.next(l_iter);
153 END LOOP;
154
155
156 if (not l_cached)
157 then
158 OPEN c_get_no_periods;
159 FETCH c_get_no_periods INTO l_no_periods;
160 CLOSE c_get_no_periods;
161
162 l_iter := nvl(g_per_time_period_types_ct.last,0)+1;
163 g_per_time_period_types_ct(l_iter).p_proc_period_type := p_proc_period_type;
164 g_per_time_period_types_ct(l_iter).number_per_fiscal_year := l_no_periods;
165 end if;
166 --
167 if g_debug then
168 hr_utility.set_location(l_proc, 20);
169 end if;
170 --
171 -- Use the number of periods in a fiscal year to deduce the base
172 -- period and multiple.
173 --
174 IF l_no_periods = 1 THEN -- Yearly
175 p_base_period_type := 'M';
176 p_multiple := 12;
177 ELSIF l_no_periods = 2 THEN -- Semi yearly
178 p_base_period_type := 'M';
179 p_multiple := 6;
180 ELSIF l_no_periods = 4 THEN -- Quarterly
181 p_base_period_type := 'M';
182 p_multiple := 3;
183 ELSIF l_no_periods = 6 THEN -- Bi monthly
184 p_base_period_type := 'M';
185 p_multiple := 2;
186 ELSIF l_no_periods = 12 THEN -- Monthly
187 p_base_period_type := 'M';
188 p_multiple := 1;
189 ELSIF l_no_periods = 13 THEN -- Lunar monthly
190 p_base_period_type := 'W';
191 p_multiple := 4;
192 ELSIF l_no_periods = 24 THEN -- Semi monthly
193 p_base_period_type := 'S';
194 p_multiple := 1; -- Not used for semi-monthly
195 ELSIF l_no_periods = 26 THEN -- Fortnightly
196 p_base_period_type := 'W';
197 p_multiple := 2;
198 ELSIF l_no_periods = 52 THEN -- Weekly
199 p_base_period_type := 'W';
200 p_multiple := 1;
201 ELSE
202 -- Unknown period type.
203 hr_utility.set_message(801, 'PAY_6601_PAYROLL_INV_PERIOD_TP');
204 hr_utility.raise_error;
205 END IF;
206 --
207 if g_debug then
208 hr_utility.set_location(l_proc, 30);
209 end if;
210 --
211 END get_period_details;
212
213 PROCEDURE period_start_stop(p_current_date date,
214 p_rec_period_start_date date,
215 l_period_start in out nocopy date,
216 l_period_end in out nocopy date,
217 l_base_period_type varchar2)
218 IS
219
220 l_multiple number;
221 l_base varchar2(1);
222
223 BEGIN
224
225 get_period_details(p_proc_period_type => l_base_period_type,
226 p_base_period_type => l_base,
227 p_multiple => l_multiple);
228
229 period_start_stop(p_current_date => p_current_date,
230 p_rec_period_start_date => p_rec_period_start_date,
231 l_period_start => l_period_start,
232 l_period_end => l_period_end,
233 l_base_period_type => l_base,
234 p_multiple => l_multiple);
235
236 END period_start_stop;
237
238
239
240 PROCEDURE period_start_stop(p_current_date date,
241 p_rec_period_start_date date,
242 l_period_start in out nocopy date,
243 l_period_end in out nocopy date,
244 l_base_period_type varchar2,
245 p_multiple number)
246 is
247
248 l_before_days number;
249 l_num_days_dIFf number;
250 l_num_months_to_period_start number;
251 l_months_before number;
252 l_current_month_year varchar2(10);
253 l_period_start_day number;
254 l_period_end_day number;
255 l_current_day number;
256 l_fpe number;
257 l_spe number;
258 l_previous_month_start date;
259 l_sm_period_end date;
260
261 BEGIN
262
263 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
264 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_PERIOD_EVALUATION.PERIOD_START_STOP',
265 'Base Period Type'||l_base_period_type||' Multiple'||p_multiple);
266 end if;
267
268 IF(l_base_period_type = 'W') THEN
269 l_period_end := NEXT_DAY(p_current_date,to_char(p_rec_period_start_date,'DAY')) - 1;
270 l_period_start := l_period_end - 6;
271
272 l_num_days_dIFf := trunc(l_period_start) - trunc(p_rec_period_start_date);
273
274 -- l_before_days := mod(l_num_days_dIFf,7*p_multiple);
275 -- commenting out the above line for bug 3902747
276 -- Adding the ABS( ) function to the result of the MOD( ) to eliminate the chance of
277 -- getting negative values
278
279 l_before_days := abs(mod(l_num_days_dIFf,7*p_multiple));
280
281
282 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
283 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_PERIOD_EVALUATION.PERIOD_START_STOP',
284 'l_num_days_dIFf '||l_num_days_dIFf||'l_before_days '||l_before_days);
285 end if;
286
287 l_period_start := l_period_start - l_before_days;
288 l_period_end := l_period_end + (7 * (p_multiple-1) - l_before_days);
289
290 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
291 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_PERIOD_EVALUATION.PERIOD_START_STOP',
292 'Start of Period '||l_period_start||'End of Period '||l_period_end);
293 end if;
294 END IF;
295
296 IF(l_base_period_type = 'M') THEN
297 l_period_start := add_months(p_rec_period_start_date,
298 floor(months_between(p_current_date,p_rec_period_start_date)));
299 l_period_end := add_months(l_period_start,1) - 1;
300
301 l_num_months_to_period_start := months_between(l_period_start,p_rec_period_start_date);
302 l_months_before := mod(l_num_months_to_period_start,p_multiple);
303
304 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
305 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_PERIOD_EVALUATION.PERIOD_START_STOP',
306 'l_num_months_to_period_start '||l_num_months_to_period_start||
307 'l_months_before '||l_months_before);
308 end if;
309
310 l_period_start := add_months(l_period_start,-l_months_before);
311 l_period_end := add_months(l_period_end ,(p_multiple-1)-l_months_before);
312
313 if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
314 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'HXC_PERIOD_EVALUATION.PERIOD_START_STOP',
315 'Start of Period '||l_period_start||'End of Period '||l_period_end);
316 end if;
317 END IF;
318
319 /*
320
321 Note that p_rec_period_start_date is the start of the period. The following
322 evaluation is based on the period end dates. Therefore we step back one day
323 from the p_rec_period_start_date before doing the evaluation.
324
325 This is taken from /hld/. This is by far the worst of all the evaluations
326
327 Rules for Semi-Monthly
328 ( INPUT ) (REMARKS) ( OUTPUT )
329 1st period 1st period 2nd per. 3rd per. 4th per. 5th per.
330 end date end date end date end date end date end date
331 ---------- -------------- --------------------------------------
332 01/1 01/1 16/1 01/2 16/2 01/3
333 02/1 02/1 17/1 02/2 17/2 02/3
334 .........
335 13/1 13/1 28/1 13/2 28/2 13/3
336 14/1 (leap year) 14/1 29/1 14/2 29/2 14/3
337 14/1 (non-leap year) 14/1 29/1 14/2 28/2 14/3
338 15/1 (Month halfs) 15/1 31/1 15/2 end-of-FEB 15/3
339 16/1 16/1 01/2 16/2 01/3 16/3
340 ........
341 28/1 28/1 13/2 28/2 13/3 28/3
342 29/1 (leap year) 29/1 14/2 29/2 14/3 29/3
343 29/1 (non-leap year) 29/1 14/2 28/2 14/3 29/3
344 30/1 30/1 15/2 end-of-FEB 15/3 30/3
345 31/1 (Month halfs) 31/1 15/2 end-of-FEB 15/3 31/3
346
347 THE RULES for generating the periods are the following:
348
349 (1) IF the first period's end-date is either the 15th or the end of a month
350 THEN the generated end-dates will be the 15th or last day of a month.
351
352 (2) IF the first period's end-date is before the 15th (i.e 1st - 14th)
353 THEN add 15 days for the next period with the restriction that we are
354 still in the same month. (i.e. Feb 14th -> Feb 28th for a non-leap year)
355
356 (3) IF the first period's end-date is after the 15th but not the end of month
357 THEN subtract 15 days and use that day of the following month.
358 Example: March 29th -> April 14th
359
360 Addendum to this. From observation of how payrolls are generated, if the period
361 ends prematurely due to the fact that a month has less dates that the FPE start date,
362 then the start of the follwing period is always pushed to the start day of the next month.
363 */
364
365 if(l_base_period_type = 'S') then
366
367 l_sm_period_end := p_rec_period_start_date - 1;
368
369 l_period_end_day := to_char(l_sm_period_end,'DD');
370 l_current_day := to_char(p_current_date,'DD');
371 l_current_month_year := to_char(p_current_date,'MM-YYYY');
372
373 if (l_sm_period_end = last_day(l_sm_period_end) or l_period_end_day = '15') then
374 if(l_current_day <= '15') then
375 l_period_start:=make_date('01',l_current_month_year);
376 l_period_end :=make_date('15',l_current_month_year);
377 else
378 l_period_start:=make_date('16',l_current_month_year);
379 l_period_end := last_day(p_current_date);
380 end if;
381 return;
382 elsif (l_period_end_day <= 14 and l_period_end_day >= '1') then
383 l_fpe:=l_period_end_day;
384 l_spe:=l_fpe+15;
385 elsif (l_period_end_day >= 16 and l_period_end_day <= '30') then
386 l_spe:=l_period_end_day;
387 l_fpe:=l_spe-15;
388 end if;
389
390 if (l_current_day > l_fpe and l_current_day <= l_spe ) then
391 l_period_start:= make_date(l_fpe+1,l_current_month_year);
392 if(to_char(last_day(p_current_date),'DD') < l_spe) then
393 l_period_end:=last_day(p_current_date);
394 else
395 l_period_end := make_date (l_spe,l_current_month_year);
396 end if;
397 end if;
398
399 if (l_current_day > l_spe) then
400 l_period_start:=make_date(l_spe+1,l_current_month_year);
401 l_period_end := add_months(make_date(l_fpe,l_current_month_year),1);
402 end if;
403
404 if (l_current_day <= l_fpe) then
405 l_period_end:=make_date(l_fpe,l_current_month_year);
406 l_previous_month_start:=add_months(make_date('01',l_current_month_year),-1);
407 if(to_char(last_day(l_previous_month_start),'DD') < l_spe +1 ) then
408 -- try something from observation!!!!!!!!!!!!!!
409 l_period_start:= make_date('01',l_current_month_year);
410 else
411 l_period_start:= make_date(l_spe+1,to_char(l_previous_month_start,'MM-YYYY'));
412 end if;
413 end if;
414
415 end if;
416
417 END period_start_stop;
418
419 end hxc_period_evaluation;
[Home] [Help]