1 package body peptoexc as
2 /* $Header: peptoexc.pkb 115.5 2004/04/19 08:46:17 irgonzal noship $ */
3
4 /*
5 * The following are expiry checking procedures
6 * for date paid balances used by pto accruals
7 */
8
9 /*------------------------------ ASG_PTO_YTD_EC ----------------------------*/
10 /*
11 NAME
12 ASG_PTO_YTD_EC - Assignment Processing Year to Date expiry check.
13 DESCRIPTION
14 NOTES
15 The associated dimension is expiry checked at payroll action level
16 */
17 --
18 -- This is the flag-based expiry routine.
19 --
20 procedure ASG_PTO_YTD_EC
21 (
22 p_owner_payroll_action_id in number, -- run created balance.
23 p_user_payroll_action_id in number, -- current run.
24 p_owner_assignment_action_id in number, -- assact created balance.
25 p_user_assignment_action_id in number, -- current assact.
26 p_owner_effective_date in date, -- eff date of balance.
27 p_user_effective_date in date, -- eff date of current run.
28 p_dimension_name in varchar2, -- balance dimension name.
29 p_expiry_information out nocopy number -- dimension expired flag.
30 ) is
31
32 l_expiry_date date;
33
34 begin
35
36 l_expiry_date := trunc(add_months(p_owner_effective_date,12),'Y');
37
38 IF p_user_effective_date >= l_expiry_date THEN
39 p_expiry_information := 1;
40 ELSE
41 p_expiry_information := 0;
42 END IF;
43
44 end ASG_PTO_YTD_EC;
45
46 --
47 -- This is the overloaded date-based expiry routine.
48 --
49 procedure ASG_PTO_YTD_EC
50 (
51 p_owner_payroll_action_id in number, -- run created balance.
52 p_user_payroll_action_id in number, -- current run.
53 p_owner_assignment_action_id in number, -- assact created balance.
54 p_user_assignment_action_id in number, -- current assact.
55 p_owner_effective_date in date, -- eff date of balance.
56 p_user_effective_date in date, -- eff date of current run.
57 p_dimension_name in varchar2, -- balance dimension name.
58 p_expiry_information out nocopy date -- dimension expired date.
59 ) is
60
61 l_expiry_date date;
62
63 begin
64
65 l_expiry_date := trunc(add_months(p_owner_effective_date,12),'Y');
66
67 p_expiry_information := l_expiry_date;
68
69 end ASG_PTO_YTD_EC;
70 /*------------------------------ ASG_PTO_TTD_EC ----------------------------*/
71 /*
72 NAME
73 ASG_PTO_TTD_EC - Assignment Processing Term to Date expiry check.
74 DESCRIPTION
75 NOTES
76 The associated dimension is expiry checked at payroll action level.
77 */
78 --
79 -- This is the flag-based expiry routine.
80 --
81 procedure ASG_PTO_TTD_EC
82 (
83 p_owner_payroll_action_id in number, -- run created balance.
84 p_user_payroll_action_id in number, -- current run.
85 p_owner_assignment_action_id in number, -- assact created balance.
86 p_user_assignment_action_id in number, -- current assact.
87 p_owner_effective_date in date, -- eff date of balance.
88 p_user_effective_date in date, -- eff date of current run.
89 p_dimension_name in varchar2, -- balance dimension name.
90 p_expiry_information out nocopy number -- dimension expired flag.
91 ) is
92
93 l_accrual_year_end date;
94
95 begin
96
97
98 l_accrual_year_end := to_date('31-05-'||to_char(p_owner_effective_date, 'YYYY'), 'DD-MM-YYYY');
99
100 if l_accrual_year_end < p_owner_effective_date then
101 --
102 l_accrual_year_end := add_months(l_accrual_year_end, 12);
103 --
104 end if;
105
106 --
107 -- Bug 2696406.
108 -- Changed < to > so that the balance expires when it should.
109 --
110 if p_user_effective_date > l_accrual_year_end then
111 p_expiry_information := 1;
112 else
113 p_expiry_information := 0;
114 end if;
115
116 end ASG_PTO_TTD_EC;
117
118 --
119 -- This is the overloaded date-based expiry routine.
120 --
121 procedure ASG_PTO_TTD_EC
122 (
123 p_owner_payroll_action_id in number, -- run created balance.
124 p_user_payroll_action_id in number, -- current run.
125 p_owner_assignment_action_id in number, -- assact created balance.
126 p_user_assignment_action_id in number, -- current assact.
127 p_owner_effective_date in date, -- eff date of balance.
128 p_user_effective_date in date, -- eff date of current run.
129 p_dimension_name in varchar2, -- balance dimension name.
130 p_expiry_information out nocopy date -- dimension expired date.
131 ) is
132
133 l_accrual_year_end date;
134
135 begin
136
137
138 l_accrual_year_end := to_date('31-05-'||to_char(p_owner_effective_date, 'YYYY'), 'DD-MM-YYYY');
139
140 if l_accrual_year_end < p_owner_effective_date then
141 --
142 l_accrual_year_end := add_months(l_accrual_year_end, 12);
143 --
144 end if;
145
146 p_expiry_information := l_accrual_year_end;
147
148 end ASG_PTO_TTD_EC;
149
150 /*-------------------------- ASG_PTO_HD_YTD_EC ----------------------------*/
151 /*
152 NAME
153 ASG_PTO_HD_YTD_EC - Assignment Processing Year to Date expiry check.
154 DESCRIPTION
155 NOTES
156 The associated dimension is expiry checked at payroll action level
157 */
158 --
159 -- This is the flag-based expiry routine.
160 --
161 procedure ASG_PTO_HD_YTD_EC
162 (
163 p_owner_payroll_action_id in number, -- run created balance.
164 p_user_payroll_action_id in number, -- current run.
165 p_owner_assignment_action_id in number, -- assact created balance.
166 p_user_assignment_action_id in number, -- current assact.
167 p_owner_effective_date in date, -- eff date of balance.
168 p_user_effective_date in date, -- eff date of current run.
169 p_dimension_name in varchar2, -- balance dimension name.
170 p_expiry_information out nocopy number -- dimension expired flag.
171 ) is
172
173 l_accrual_year_end date;
174
175 begin
176
177 select add_months(pps.date_start,
178 12 + trunc(months_between(bact.effective_date,
179 pps.date_start
180 )/12) *12) finyear
181 into l_accrual_year_end
182 from per_periods_of_service pps,
183 per_all_assignments_f asg,
184 pay_payroll_actions bact,
185 pay_assignment_actions bal_assact
186 where bact.payroll_action_id = p_owner_payroll_action_id
187 and bal_assact.payroll_action_id = bact.payroll_action_id
188 and pps.period_of_service_id = asg.period_of_service_id
189 and asg.assignment_id = bal_assact.assignment_id
190 and bal_assact.assignment_action_id = p_owner_assignment_action_id
191 and bact.effective_date between asg.effective_start_date
192 and asg.effective_end_date;
193
194 --
195 --
196
197 if p_user_effective_date >= l_accrual_year_end then
198 p_expiry_information := 1;
199 else
200 p_expiry_information := 0;
201 end if;
202 --
203 end ASG_PTO_HD_YTD_EC;
204
205 --
206 -- This is the overloaded date-based expiry routine.
207 --
208 procedure ASG_PTO_HD_YTD_EC
209 (
210 p_owner_payroll_action_id in number, -- run created balance.
211 p_user_payroll_action_id in number, -- current run.
212 p_owner_assignment_action_id in number, -- assact created balance.
213 p_user_assignment_action_id in number, -- current assact.
214 p_owner_effective_date in date, -- eff date of balance.
215 p_user_effective_date in date, -- eff date of current run.
216 p_dimension_name in varchar2, -- balance dimension name.
217 p_expiry_information out nocopy date -- dimension expired date.
218 ) is
219
220 l_accrual_year_end date;
221
222 begin
223
224 select add_months(pps.date_start,
225 12 + trunc(months_between(bact.effective_date,
226 pps.date_start
227 )/12) *12) finyear
228 into l_accrual_year_end
229 from per_periods_of_service pps,
230 per_all_assignments_f asg,
231 pay_payroll_actions bact,
232 pay_assignment_actions bal_assact
233 where bact.payroll_action_id = p_owner_payroll_action_id
234 and bal_assact.payroll_action_id = bact.payroll_action_id
235 and pps.period_of_service_id = asg.period_of_service_id
236 and asg.assignment_id = bal_assact.assignment_id
237 and bal_assact.assignment_action_id = p_owner_assignment_action_id
238 and bact.effective_date between asg.effective_start_date
239 and asg.effective_end_date;
240
241 --
242 --
243
244 p_expiry_information := l_accrual_year_end;
245
246 end ASG_PTO_HD_YTD_EC;
247
248 /*
249 * The following are expiry checking procedures
250 * for date earned balances used by pto accruals
251 */
252
253 /*------------------------------ ASG_PTO_DE_YTD_EC ----------------------------*/
254 /*
255 NAME
256 ASG_PTO_DE_YTD_EC - Assignment Processing Year to Date expiry check.
257 DESCRIPTION
258 Used to check expiry of seeded date earned balance in
259 PTO accruals, for a one year plan beginning 01/01.
260 */
261 --
262 -- This is the flag-based expiry routine.
263 --
264 procedure ASG_PTO_DE_YTD_EC
265 (
266 p_owner_payroll_action_id in number, -- run created balance.
267 p_user_payroll_action_id in number, -- current run.
268 p_owner_assignment_action_id in number, -- assact created balance.
269 p_user_assignment_action_id in number, -- current assact.
270 p_owner_effective_date in date, -- eff date of balance.
271 p_user_effective_date in date, -- eff date of current run.
272 p_dimension_name in varchar2, -- balance dimension name.
273 p_expiry_information out nocopy number -- dimension expired flag.
274 ) is
275
276 cursor c_date_earned(p_payroll_action_id number) is
277 select date_earned
278 from pay_payroll_actions
279 where payroll_action_id = p_payroll_action_id;
280
281 l_expiry_date date;
282 l_curr_expiry_date date;
283 l_owner_date_earned date;
284 l_user_date_earned date;
285
286 begin
287
288 open c_date_earned(p_owner_payroll_action_id);
289 fetch c_date_earned into l_owner_date_earned;
290 close c_date_earned;
291
292 open c_date_earned(p_user_payroll_action_id);
293 fetch c_date_earned into l_user_date_earned;
294 close c_date_earned;
295
296 l_expiry_date := trunc(add_months(l_owner_date_earned,12),'Y');
297 l_curr_expiry_date := trunc(l_owner_date_earned, 'Y');
298
299 IF l_user_date_earned >= l_expiry_date THEN
300 p_expiry_information := 1;
301 ELSE
302 -- Date is in a previous period
303 if l_user_date_earned < l_curr_expiry_date then
304 p_expiry_information := 2;
305 else
306 -- Date is in current period but prior to lat bal run.
307 if l_user_date_earned < l_owner_date_earned then
308 p_expiry_information := 3;
309 else
310 -- No it hasn't expired.
311 p_expiry_information := 0;
312 end if;
313 end if;
314 --
315 END IF;
316
317 end ASG_PTO_DE_YTD_EC;
318
319 --
320 -- This is the overloaded date-based expiry routine.
321 --
322 procedure ASG_PTO_DE_YTD_EC
323 (
324 p_owner_payroll_action_id in number, -- run created balance.
325 p_user_payroll_action_id in number, -- current run.
326 p_owner_assignment_action_id in number, -- assact created balance.
327 p_user_assignment_action_id in number, -- current assact.
328 p_owner_effective_date in date, -- eff date of balance.
329 p_user_effective_date in date, -- eff date of current run.
330 p_dimension_name in varchar2, -- balance dimension name.
331 p_expiry_information out nocopy date -- dimension expired date.
332 ) is
333
334 cursor c_date_earned(p_payroll_action_id number) is
335 select date_earned
336 from pay_payroll_actions
337 where payroll_action_id = p_payroll_action_id;
338
339 l_expiry_date date;
340 l_owner_date_earned date;
341
342 begin
343
344 open c_date_earned(p_owner_payroll_action_id);
345 fetch c_date_earned into l_owner_date_earned;
346 close c_date_earned;
347
348 l_expiry_date := trunc(add_months(l_owner_date_earned,12),'Y');
349
350 p_expiry_information := l_expiry_date;
351
352 end ASG_PTO_DE_YTD_EC;
353
357 ASG_PTO_DE_SM_YTD_EC - Assignment Processing Year to Date expiry check.
354 /*------------------------------ ASG_PTO_DE_SM_YTD_EC ----------------------------*/
355 /*
356 NAME
358 DESCRIPTION
359 Used to check expiry of seeded date earned balance in PTO accruals, for our
360 simple multiplier plan, beginning 01/06 each year.
361 */
362 --
363 -- This is the flag-based expiry routine.
364 --
365 procedure ASG_PTO_DE_SM_YTD_EC
366 (
367 p_owner_payroll_action_id in number, -- run created balance.
368 p_user_payroll_action_id in number, -- current run.
369 p_owner_assignment_action_id in number, -- assact created balance.
370 p_user_assignment_action_id in number, -- current assact.
371 p_owner_effective_date in date, -- eff date of balance.
372 p_user_effective_date in date, -- eff date of current run.
373 p_dimension_name in varchar2, -- balance dimension name.
374 p_expiry_information out nocopy number -- dimension expired flag.
375 ) is
376
377 cursor c_date_earned(p_payroll_action_id number) is
378 select date_earned
379 from pay_payroll_actions
380 where payroll_action_id = p_payroll_action_id;
381
382 l_expiry_date date;
383 l_curr_expiry_date date;
384 l_owner_date_earned date;
388 --
385 l_user_date_earned date;
386
387 begin
389 open c_date_earned(p_owner_payroll_action_id);
390 fetch c_date_earned into l_owner_date_earned;
391 close c_date_earned;
392
393 open c_date_earned(p_user_payroll_action_id);
394 fetch c_date_earned into l_user_date_earned;
395 close c_date_earned;
396
397 l_expiry_date := to_date('01-06-'||to_char(l_owner_date_earned, 'YYYY'), 'DD-MM-YYYY');
398
399 if l_expiry_date < l_owner_date_earned then
400 --
401 l_curr_expiry_date := l_expiry_date;
402 l_expiry_date := add_months(l_expiry_date, 12);
403 --
404 else
405 --
406 l_curr_expiry_date := add_months(l_expiry_date, -12);
407 --
408 end if;
409
410 --
411 --
412 IF l_user_date_earned >= l_expiry_date THEN
413 p_expiry_information := 1;
414 ELSE
415 -- Date is in a previous period
416 if l_user_date_earned < l_curr_expiry_date then
417 p_expiry_information := 2;
418 else
419 -- Date is in current period but prior to lat bal run.
420 if l_user_date_earned < l_owner_date_earned then
421 p_expiry_information := 3;
422 else
423 -- No it hasn't expired.
424 p_expiry_information := 0;
425 end if;
426 end if;
427 --
428 END IF;
429
430 --
431 end ASG_PTO_DE_SM_YTD_EC;
432
433 --
434 -- This is the overloaded date-based expiry routine.
435 --
436 procedure ASG_PTO_DE_SM_YTD_EC
437 (
438 p_owner_payroll_action_id in number, -- run created balance.
439 p_user_payroll_action_id in number, -- current run.
440 p_owner_assignment_action_id in number, -- assact created balance.
441 p_user_assignment_action_id in number, -- current assact.
442 p_owner_effective_date in date, -- eff date of balance.
443 p_user_effective_date in date, -- eff date of current run.
444 p_dimension_name in varchar2, -- balance dimension name.
445 p_expiry_information out nocopy date -- dimension expired date.
446 ) is
447
448 cursor c_date_earned(p_payroll_action_id number) is
449 select date_earned
450 from pay_payroll_actions
451 where payroll_action_id = p_payroll_action_id;
452
453 l_expiry_date date;
454 l_owner_date_earned date;
455
456 begin
457 --
458 open c_date_earned(p_user_payroll_action_id);
459 fetch c_date_earned into l_owner_date_earned;
460 close c_date_earned;
461
462 l_expiry_date := to_date('01-06-'||to_char(l_owner_date_earned, 'YYYY'), 'DD-MM-YYYY');
463
464 if l_expiry_date < l_owner_date_earned
465 and l_owner_date_earned > to_date('30-06-'||
466 to_char(l_owner_date_earned, 'YYYY'), 'DD-MM-YYYY')
467 then
468 --
469 l_expiry_date := add_months(l_expiry_date, 12);
470 --
471 end if;
472 --
473 p_expiry_information := l_expiry_date;
474 --
475 end ASG_PTO_DE_SM_YTD_EC;
476
477 /*------------------------------ ASG_PTO_DE_HD_YTD_EC ------------------------*/
478 /*
479 NAME
483 hire date anniversary accrual plan.
480 ASG_PTO_DE_HD_YTD_EC - Assignment Processing Year to Date expiry check.
481 DESCRIPTION
482 Used to check expiry of seeded date earned balance in PTO accruals, for a
484 */
485 --
486 -- This is the flag-based expiry routine.
487 --
488 procedure ASG_PTO_DE_HD_YTD_EC
489 (
490 p_owner_payroll_action_id in number, -- run created balance.
491 p_user_payroll_action_id in number, -- current run.
492 p_owner_assignment_action_id in number, -- assact created balance.
493 p_user_assignment_action_id in number, -- current assact.
494 p_owner_effective_date in date, -- eff date of balance.
495 p_user_effective_date in date, -- eff date of current run.
496 p_dimension_name in varchar2, -- balance dimension name.
497 p_expiry_information out nocopy number -- dimension expired flag.
498 ) is
499
500 cursor c_date_earned(p_payroll_action_id number) is
501 select date_earned
502 from pay_payroll_actions
503 where payroll_action_id = p_payroll_action_id;
504
505 l_expiry_date date;
506 l_curr_expiry_date date;
507 l_owner_date_earned date;
508 l_user_date_earned date;
509
510 begin
511 --
512
513 open c_date_earned(p_owner_payroll_action_id);
514 fetch c_date_earned into l_owner_date_earned;
515 close c_date_earned;
516
517 open c_date_earned(p_user_payroll_action_id);
521 select add_months(pps.date_start,
518 fetch c_date_earned into l_user_date_earned;
519 close c_date_earned;
520
522 12 + trunc(months_between(bact.date_earned,
523 pps.date_start
524 )/12) *12) finyear
525 into l_expiry_date
526 from per_periods_of_service pps,
527 per_all_assignments_f asg,
528 pay_payroll_actions bact,
529 pay_assignment_actions bal_assact
530 where bact.payroll_action_id = p_owner_payroll_action_id
531 and bal_assact.payroll_action_id = bact.payroll_action_id
532 and pps.period_of_service_id = asg.period_of_service_id
533 and asg.assignment_id = bal_assact.assignment_id
534 and bal_assact.assignment_action_id = p_owner_assignment_action_id
535 and bact.date_earned between asg.effective_start_date
536 and asg.effective_end_date;
537
538 l_curr_expiry_date := add_months(l_expiry_date, -12);
539 --
540 --
541
542 IF l_user_date_earned >= l_expiry_date THEN
543 p_expiry_information := 1;
544 ELSE
545 -- Date is in a previous period
546 if l_user_date_earned < l_curr_expiry_date then
547 p_expiry_information := 2;
548 else
549 -- Date is in current period but prior to lat bal run.
550 if l_user_date_earned < l_owner_date_earned then
551 p_expiry_information := 3;
552 else
553 -- No it hasn't expired.
554 p_expiry_information := 0;
555 end if;
556 end if;
557 --
558 END IF;
559
560 --
561 end ASG_PTO_DE_HD_YTD_EC;
562
563 --
564 -- This is the overloaded date-based expiry routine.
565 --
566 procedure ASG_PTO_DE_HD_YTD_EC
567 (
568 p_owner_payroll_action_id in number, -- run created balance.
569 p_user_payroll_action_id in number, -- current run.
570 p_owner_assignment_action_id in number, -- assact created balance.
571 p_user_assignment_action_id in number, -- current assact.
572 p_owner_effective_date in date, -- eff date of balance.
573 p_user_effective_date in date, -- eff date of current run.
574 p_dimension_name in varchar2, -- balance dimension name.
575 p_expiry_information out nocopy date -- dimension expired date.
576 ) is
577
578 cursor c_date_earned(p_payroll_action_id number) is
579 select date_earned
580 from pay_payroll_actions
581 where payroll_action_id = p_payroll_action_id;
582
583 l_expiry_date date;
584
585 begin
586 --
587 select add_months(pps.date_start,
588 12 + trunc(months_between(bact.date_earned,
589 pps.date_start
590 )/12) *12) finyear
591 into l_expiry_date
592 from per_periods_of_service pps,
593 per_all_assignments_f asg,
594 pay_payroll_actions bact,
595 pay_assignment_actions bal_assact
596 where bact.payroll_action_id = p_owner_payroll_action_id
597 and bal_assact.payroll_action_id = bact.payroll_action_id
598 and pps.period_of_service_id = asg.period_of_service_id
599 and asg.assignment_id = bal_assact.assignment_id
600 and bal_assact.assignment_action_id = p_owner_assignment_action_id
601 and bact.date_earned between asg.effective_start_date
602 and asg.effective_end_date;
603
604 --
605 p_expiry_information := l_expiry_date;
606 --
607 end ASG_PTO_DE_HD_YTD_EC;
608
609 end peptoexc;
[Home] [Help]