[Home] [Help]
PACKAGE BODY: APPS.PAY_FF_FUNCTIONS
Source
1 PACKAGE BODY PAY_FF_Functions as
2 /* $Header: pyfffunc.pkb 120.7.12000000.6 2007/03/15 09:58:11 kvsankar noship $ */
3 ------------------------------------------------------------------------------+
4 ------------------------------------------
5 -- Function to Get the 403B Limit
6 ------------------------------------------
7 Function Get_PQP_Limit(
8 p_effective_date IN DATE DEFAULT NULL,
9 p_payroll_action_id IN number DEFAULT NULL,
10 p_limit IN varchar) Return number is
11
12 l_fed_information1 number;
13 l_fed_information2 number;
14 l_fed_information3 number;
15 l_fed_information4 number;
16 l_fed_information5 number;
17 l_fed_information6 number;
18 l_fed_information7 number;
19 l_fed_information8 number;
20 l_fed_information9 number;
21
22 Cursor Cur_403b_limit (p_date date) is
23 Select
24 fed_information1,
25 fed_information2,
26 fed_information3,
27 fed_information4,
28 fed_information5,
29 fed_information6,
30 fed_information7,
31 fed_information8,
32 fed_information9
33 from
34 pay_us_federal_tax_info_f
35 where
36 fed_information_category = '403B LIMITS'
37 and p_date between effective_start_date and nvl(effective_end_date, p_date);
38
39
40 l_403b_limit number;
41 l_limit_name varchar2(100);
42 l_effective_date date;
43 begin
44 IF p_payroll_action_id IS NULL THEN
45 l_effective_date:=p_effective_date;
46 ELSE
47 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
48 (p_payroll_action_id));
49 END IF;
50
51 hr_utility.set_location('Start of GET_PQP_LIMIT', 5);
52
53 open Cur_403b_limit (l_effective_date);
54
55 fetch Cur_403b_limit into
56 l_fed_information1,
57 l_fed_information2,
58 l_fed_information3,
59 l_fed_information4,
60 l_fed_information5,
61 l_fed_information6,
62 l_fed_information7,
63 l_fed_information8,
64 l_fed_information9;
65
66
67 if Cur_403b_limit%notfound then
68 close Cur_403b_limit;
69 -- Error : No limits defined for 403b
70 hr_utility.set_message(801, 'PAY_PQP_LIMITS_NOT_DEFINED');
71 hr_utility.raise_error;
72 return 0;
73 end if;
74
75 hr_utility.set_location('GET_PQP_LIMIT', 10);
76
77 l_limit_name := upper(p_limit);
78
79 l_403b_limit := 0;
80
81 if l_limit_name = 'OVERALL_ER_LIMIT' then
82 l_403b_limit := l_fed_information1;
83 elsif l_limit_name = 'ANY_YEAR_LIMIT' then
84 l_403b_limit := l_fed_information2;
85 elsif l_limit_name = 'ELECTIVE_DEFERRAL_LIMIT' then
86 l_403b_limit := l_fed_information3;
87 elsif l_limit_name = 'INCLUDABLE_ANNUAL_COMP_LIMIT' then
88 l_403b_limit := l_fed_information4;
89 elsif l_limit_name = 'ELE_DEF_ADDITIONAL_CATCHUP' then
90 l_403b_limit := l_fed_information5;
91 elsif l_limit_name = 'ELECTIVE_DEFERRAL_CATCHUP' then
92 l_403b_limit := l_fed_information6;
93 elsif l_limit_name = 'ADDITIONAL_ANY_YEAR_AMOUNT' then
94 l_403b_limit := l_fed_information7;
95 elsif l_limit_name = 'COMBINED_403B_457_LIMIT' then
96 l_403b_limit := l_fed_information8;
97 elsif l_limit_name = 'GENERAL_CATCHUP_LIMIT' then
98 l_403b_limit := l_fed_information9;
99 end if;
100
101 close Cur_403b_limit;
102
103 hr_utility.set_location('GET_PQP_LIMIT',15);
104
105
106 return NVL(l_403b_limit,0);
107
108 end;
109
110 ----------------------------------------------------
111 -- Function to calculate Maximum Exclusion Allowance
112 ----------------------------------------------------
113 function Calc_max_excl_allow (
114 p_payroll_action_id in number,
115 p_ee_incld_annual_comp in number,
116 p_total_er_contr_prior_years in number,
117 p_years_of_service in number
118 )
119 Return number is
120
121 Max_excl_allow number := 0;
122 l_effective_date date;
123 begin
124 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
125 (p_payroll_action_id));
126 hr_utility.set_location('Start of Calc_max_excl_allow ', 5);
127 max_excl_allow := (0.20 * p_ee_incld_annual_comp * p_years_of_service) -
128 p_total_er_contr_prior_years;
129
130 hr_utility.set_location('End of Calc_max_excl_allow ', 10);
131
132 return max_excl_allow;
133 end;
134 --------------------------------------
135 -- Function to calculate Overall Limit
136 --------------------------------------
137 -- Function calculates the Overall Limit
138 -- which is the samllest of the following
139 -- three :-
140 -- 1 - Greater of (Employee's Incld Annual Comp,
141 -- Includable annuall comp limit)
142 -- 2 - 25% of Employee's Incld Annual Comp.
143 -- 3 - Overall Employer Limit
144 -----------------------------------------
145 Function Calc_overall_limit (
146 p_payroll_action_id in number,
147 p_ee_incld_annual_comp in number
148 )
149 Return Number is
150
151 l_incld_annual_comp_limit number := 0;
152 l_ee_incld_annual_comp number := 0;
153 l_overall_er_limit number := 0;
154 overall_limit number := 0;
155 l_effective_date date;
156
157 begin
158
159 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
160 (p_payroll_action_id));
161 hr_utility.set_location('Start of Calc_overall_limit ', 5);
162
163 l_incld_annual_comp_limit := pay_ff_functions.get_pqp_limit(
164 p_payroll_action_id =>p_payroll_action_id,
165 p_limit =>'includable_annual_comp_limit');
166 l_overall_er_limit := pay_ff_functions.get_pqp_limit(
167 p_payroll_action_id=>p_payroll_action_id,
168 p_limit =>'overall_er_limit');
169
170 -- Choose the lesser of the
171 -- Incld annual comp Limit or the employee's annual comp. */
172
173 if p_ee_incld_annual_comp > l_incld_annual_comp_limit then
174 l_ee_incld_annual_comp := l_incld_annual_comp_limit;
175 else
176 l_ee_incld_annual_comp := p_ee_incld_annual_comp;
177 end if;
178
179 overall_limit := (0.25 * l_ee_incld_annual_comp);
180
181 -- Choose lesser of the prv. computed figure or the Overall ER Limit
182
183 if overall_limit > l_overall_er_limit then
184 overall_limit := l_overall_er_limit;
185 end if;
186
187 hr_utility.set_location('End of Calc_overall_limit ',10);
188
189 return overall_limit;
190
191 end;
192 --
193 --
194 ---------------------------------------
195 -- Function to calculate Any Year Limit
196 ---------------------------------------
197 Function Calc_any_year_limit (
198 p_payroll_action_id in number,
199 p_ee_incld_annual_comp in number,
200 p_total_er_contr_prior_years in number,
201 p_years_of_service in number
202 )
203 Return number is
204
205 l_additional_any_year_amount number := 0;
206 l_any_year_limit number := 0;
207 l_max_excl_allowance number := 0;
208
209 this_limit number := 0; -- Since the same name variable
210 -- is also used for pqp_limit
211 l_effective_date date; -- of the same name
212 begin
213 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
214 (p_payroll_action_id));
215 hr_utility.set_location('Start of Calc_any_year_limit ', 5);
216 l_additional_any_year_amount := pay_ff_functions.get_pqp_limit(
217 p_payroll_action_id=>p_payroll_action_id,
218 p_limit =>'additional_any_year_amount');
219
220 this_limit := (0.25 * p_ee_incld_annual_comp)
221 + l_additional_any_year_amount;
222
223 l_max_excl_allowance := pay_ff_functions.calc_max_excl_allow (
224 p_payroll_action_id,
225 p_ee_incld_annual_comp,
226 p_total_er_contr_prior_years,
227 p_years_of_service );
228
229 -- Choose the lesser of two
230
231 if this_limit > l_max_excl_allowance then
232 this_limit := l_max_excl_allowance;
233 end if;
234
235 l_any_year_limit :=
236 pay_ff_functions.get_pqp_limit(
237 p_payroll_action_id=>p_payroll_action_id,
238 p_limit =>'any_year_limit');
239
240 -- Choose the lesser of two
241
242 if this_limit > l_any_year_limit then
243 this_limit := l_any_year_limit;
244 end if;
245
246 hr_utility.set_location('End of Calc_any_year_limit ', 5);
247
248 return this_limit;
249
250 end;
251 --
252 --
253 -------------------------------------------------
254 -- Function to calculate year of separation Limit
255 -------------------------------------------------
256 Function Calc_year_of_sep_limit(
257 p_payroll_action_id in number,
258 p_assignment_id in number,
259 p_ee_incld_annual_comp in number,
260 p_total_er_contr_prior_years in number,
261 p_years_of_service in number
262 )
263 Return number is
264
265 l_overall_er_limit number := 0;
266 l_max_excl_allow_limit number := 0;
267 l_years_of_service number := 0;
268 l_prv_date date;
269 l_calc_prv_contr number := 0;
270 l_defined_balance_id number := 0;
271 l_calc_bal number := 0;
272 l_start_date date;
273 l_effective_date date;
274 Cursor C1 is
275 select
276 db.defined_balance_id
277 from
278 pay_balance_types pbt,
279 pay_defined_balances db,
280 pay_balance_dimensions bd
281 where
282 pbt.balance_name = 'Qualified EE ER Contribution'
283 and pbt.balance_type_id = db.balance_type_id
284 and bd.dimension_name = 'Person Lifetime to Date'
285 and bd.balance_dimension_id = db.balance_dimension_id;
286
287 Cursor C2 is
288 SELECT max(service.date_start) start_date
289 FROM per_periods_of_service service,
290 per_all_assignments_f ass
291 WHERE ass.assignment_id = p_assignment_id
292 AND ass.person_id = service.person_id
293 AND service.date_start <= l_effective_date;
294
295
296 begin
297 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
298 (p_payroll_action_id));
299 l_overall_er_limit :=
300 pay_ff_functions.get_pqp_limit (
301 p_payroll_action_id=>p_payroll_action_id,
302 p_limit =>'overall_er_limit');
303
304 if p_years_of_service > 10 then
305
306 l_years_of_service := 10;
307
308 /* calculate the total employers contr. for last 10yrs */
309
310 for c1_rec in C1
311 loop
312 l_defined_balance_id := c1_rec.defined_balance_id;
313 end loop;
314
315 for c2_rec in C2
316 loop
317 l_start_date := c2_rec.start_date;
318 end loop;
319
320 l_prv_date := '31-dec-'||
321 to_char(to_number(to_char(trunc(l_effective_date,'YEAR')-1,'YYYY'))-10);
322
323 if l_prv_date >= l_start_date then
324 l_calc_bal := pay_balance_pkg.get_value(l_defined_balance_id
325 ,p_assignment_id
326 ,l_prv_date);
327 end if;
328
329 l_calc_prv_contr := p_total_er_contr_prior_years - l_calc_bal;
330
331 else
332 l_years_of_service := p_years_of_service;
333 l_calc_prv_contr := p_total_er_contr_prior_years;
334 end if;
335
336 l_max_excl_allow_limit := pay_ff_functions.calc_max_excl_allow(
337 p_payroll_action_id,
338 p_ee_incld_annual_comp,
339 l_calc_prv_contr,
340 l_years_of_service);
341 if l_overall_er_limit > l_max_excl_allow_limit then
342 return l_max_excl_allow_limit ;
343 else
344 return l_overall_er_limit;
345 end if;
346
347 exception
348 when others then
349 hr_utility.set_message(801, 'PAY_YEAR_OF_SEP_LIMIT_FAILURE');
350 -- probable cause is that the payroll has not been assigned to
351 -- the employee as of date of hire
352 hr_utility.raise_error;
353 return 0;
354 end;
355 --
356 --
357
358 -------------------------------------------------
359 -- Function to calculate effective deferral limit
360 -------------------------------------------------
361 Function Calc_elec_def_limit(
362 p_payroll_action_id in number,
363 p_catch_up in varchar2,
364 p_total_elec_def in number,
365 p_years_of_service in number,
366 p_catch_up_amt in number
367 )
368 Return number is
369
370 l_elec_def_limit number := 0;
371 l_elec_def_catchup_limit number := 0;
372
373 amt1 number := 0;
374 amt2 number := 0;
375 amt3 number := 0;
376 l_effective_date date;
377 begin
378
379 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
380 (p_payroll_action_id));
381 l_elec_def_catchup_limit :=
382 pay_ff_functions.get_pqp_limit (
383 p_payroll_action_id=>p_payroll_action_id,
384 p_limit =>'elective_deferral_catchup');
385 l_elec_def_limit :=
386 pay_ff_functions.get_pqp_limit (
387 p_payroll_action_id=>p_payroll_action_id,
388 p_limit =>'elective_deferral_limit');
389
390 if p_catch_up = 'CATCHUP_YES' then
391
392 --
393 -- If Catch Up option is taken the the limit would be minimum
394 -- of the following three amounts :
395 -- Amt 1 = (Total years of service * 5000) -
396 -- previous years contribution(i.e. in our case p_total_elec_def)
397 -- Amt 2 = Elective Deferral Limit +
398 -- Elective Defferal Additional Catchup limit
399 -- Amt 3 = (Elective Deferral Catchup Limit -
400 -- any prior catchup contribution) + elective_deferral_limit
401 --
402
403 amt1 := (5000 * p_years_of_service) - p_total_elec_def;
404 amt2 := l_elec_def_limit +
405 pay_ff_functions.get_pqp_limit (
406 p_payroll_action_id=>p_payroll_action_id,
407 p_limit =>'ELE_DEF_ADDITIONAL_CATCHUP');
408 amt3 := (l_elec_def_catchup_limit - p_catch_up_amt) + l_elec_def_limit;
409
410 if amt1 > amt2 then
411 l_elec_def_limit := amt2;
412 else
413 l_elec_def_limit := amt1;
414 end if;
415
416 if l_elec_def_limit > amt3 then
417 l_elec_def_limit := amt3;
418 end if;
419
420 end if;
421
422 return l_elec_def_limit;
423
424 end;
425 --
426 ------------------------------------------
427 -- Function to calculate length of service
428 ------------------------------------------
429 Function Calc_length_of_service(
430 p_payroll_action_id in number,
431 p_assignment_id in number,
432 p_dummy in varchar)
433 Return number is
434
435 p_years_of_service number := 0;
436 p_person_id number;
437 p_start_date date;
438 l_effective_date date;
439 cursor c1 is
440 SELECT max(service.date_start) start_date
441 FROM per_periods_of_service service,
442 per_all_assignments_f ass
443 WHERE ass.assignment_id = p_assignment_id
444 AND ass.person_id = service.person_id
445 AND service.date_start <= l_effective_date;
446
447 begin
448 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
449 (p_payroll_action_id));
450 for c1_rec in c1
451 loop
452 p_start_date := c1_rec.start_date;
453 end loop;
454 if l_effective_date < p_start_date then
455 p_years_of_service := 0;
456 else
457 p_years_of_service :=
458 ceil(months_between(l_effective_date,(p_start_date - 1)) / 12);
459 end if;
460 return p_years_of_service;
461 end;
462 --
463 ------------------------------------------
464 -- Function to check whether employee is
465 -- enrolled in both 403(b) and 457 plans
466 ------------------------------------------
467 Function Check_if_emp_in_403b_457_plan(
468 p_payroll_action_id in number,
469 p_assignment_id in number,
470 p_dummy in VARCHAR)
471 return varchar is
472 l_person_id number := 0;
473 l_both_plans number;
474 l_effective_date date;
475 cursor c_person is
476 select person_id
477 from per_assignments_f
478 where assignment_id = p_assignment_id;
479
480 cursor c1 is
481 select c.person_id
482 from ben_prtt_enrt_rslt_f c,
483 ben_pl_regn_f b,
484 ben_regn_f a
485 where c.person_id = l_person_id
486 and a.sttry_citn_name = 'IRC S457'
487 and b.regn_id = a.regn_id
488 and c.pl_id = b.pl_id
489 and l_effective_date between a.effective_start_date and
490 nvl(a.effective_end_date,l_effective_date)
491 and l_effective_date between b.effective_start_date and
492 nvl(b.effective_end_date,l_effective_date)
493 and l_effective_date between c.enrt_cvg_strt_dt and c.enrt_cvg_thru_dt
494 and c.enrt_cvg_thru_dt <= c.effective_end_date
495 and c.prtt_enrt_rslt_stat_cd is null;
496
497 begin
498
499 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
500 (p_payroll_action_id));
501 for c_person_rec in c_person
502 loop
503 l_person_id := c_person_rec.person_id;
504 end loop;
505 --
506 for c1_rec in c1
507 loop
508 l_both_plans := c1_rec.person_id;
509 end loop;
510 --
511 if l_both_plans is null then
512 return 'FALSE';
513 else
514 return 'TRUE' ;
515 end if;
516 end;
517 --
518 --
519
520 ------------------------------------------
521 -- Function to get the annual salary
522 ------------------------------------------
523 function get_annual_salary (
524 p_payroll_action_id in number,
525 p_assignment_id in number,
526 p_as_of_date in date
527 )
528 return number is
529
530 l_current_annual_salary number;
531 p_effective_date date;
532 l_effective_date date;
533
534 CURSOR c1 (l_effective_date date ) is
535 SELECT pro.proposed_salary_n Last_Apprv_Sal, pro.change_date,
536 pro.proposed_salary_n *
537 nvl(ppb.pay_annualization_factor,tpt.number_per_fiscal_year) last_anul_sal
538 FROM per_people_f per,
539 per_time_period_types tpt,
540 per_assignments_f ass,
541 per_pay_bases ppb,
542 per_pay_proposals pro,
543 pay_all_payrolls_f prl
544 WHERE per.person_id = ass.person_id
545 AND ass.pay_basis_id = ppb.pay_basis_id
546 AND ass.assignment_type = 'E'
547 AND ass.payroll_id = prl.payroll_id
548 AND ass.effective_start_date BETWEEN prl.effective_start_date AND
549 nvl(prl.effective_end_date, ass.effective_start_date)
550 AND prl.period_type = tpt.period_type
551 AND ass.assignment_id = pro.assignment_id(+)
552 AND pro.change_date = (SELECT MAX(change_date)
553 FROM per_pay_proposals pro1
554 WHERE pro.assignment_id = pro1.assignment_id
555 AND pro1.approved = 'Y'
556 AND pro1.change_date <= l_effective_date)
557 AND ass.business_group_ID + 0 = NVL(hr_general.get_business_group_id,
558 ass.business_group_id)
559 AND l_effective_date BETWEEN per.effective_start_date
560 AND nvl(per.effective_end_date, l_effective_date)
561 AND l_effective_date BETWEEN ass.effective_start_date
562 AND nvl(ass.effective_end_date, l_effective_date)
563 AND ass.assignment_id = p_assignment_id;
564
565 BEGIN
566 p_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
567 (p_payroll_action_id));
568 /*
569 * make use of the day and month that the user has passed from the formula else
570 * use the context date
571 */
572 if p_as_of_date = to_date('1951/01/01', 'yyyy/mm/dd') then
573 l_effective_date := p_effective_date;
574 else
575 l_effective_date := to_date(to_char(p_effective_date,'yyyy')||
576 to_char(p_as_of_date,'mmdd'),'yyyymmdd');
577 end if;
578 --
579 for c1_rec in c1(l_effective_date) loop
580 l_current_annual_salary := c1_rec.last_anul_sal;
581 end loop;
582 --
583 return nvl(l_current_annual_salary,0);
584 --
585 END get_annual_salary;
586 --------------------------------------------------------------------------
587
588 ------------------------------------------
589 -- Function to Get the 457 Limit
590 ------------------------------------------
591 function get_457_annual_limit(
592 p_effective_date IN DATE DEFAULT NULL,
593 p_payroll_action_id IN NUMBER DEFAULT NULL,
594 p_limit IN VARCHAR) Return number is
595
596 l_fed_information1 number;
597 l_fed_information2 number;
598 l_fed_information3 number;
599 l_effective_date date;
600 Cursor Cur_457_limit (p_date date) is
601 Select
602 fed_information1,
603 fed_information2,
604 fed_information3
605 from
606 pay_us_federal_tax_info_f
607 where
608 fed_information_category = '457 LIMITS'
609 and p_date between effective_start_date and
610 nvl(effective_end_date, p_date);
611
612 l_457_limit number;
613 l_limit_name varchar2(100);
614
615 begin
616 IF p_payroll_action_id IS NULL THEN
617 l_effective_date:=p_effective_date;
618 ELSE
619 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
620 (p_payroll_action_id));
621 END IF;
622 hr_utility.set_location('Start of GET_457_LIMIT', 5);
623
624 open Cur_457_limit (l_effective_date);
625
626 fetch Cur_457_limit into
627 l_fed_information1,
628 l_fed_information2,
629 l_fed_information3;
630
631 if Cur_457_limit%notfound then
632 close Cur_457_limit;
633 -- Error : No limits defined for 457
634 hr_utility.set_message(801, 'PAY_PQP_LIMITS_NOT_DEFINED');
635 hr_utility.raise_error;
636 return 0;
637 end if;
638
639 hr_utility.set_location('GET_457_LIMIT', 10);
640
641 l_limit_name := upper(p_limit);
642
643 l_457_limit := 0;
644
645 if l_limit_name = '457 LIMIT' then
646 l_457_limit := l_fed_information1;
647 elsif l_limit_name = '457 CATCHUP LIMIT' then
648 l_457_limit := l_fed_information2;
649 elsif l_limit_name = '457 ADDITIONAL CATCHUP' then
650 l_457_limit := l_fed_information3;
651 end if;
652
653 close Cur_457_limit;
654
655 hr_utility.set_location('GET_457_LIMIT',15);
656
657
658 return l_457_limit;
659
660 end;
661
662
663 ----------------------------------------------------
664 -- Function to calculate catchup previously unused
665 ----------------------------------------------------
666 function calc_prev_unused (p_assignment_id in number,
667 p_payroll_action_id in number,
668 p_dummy in varchar)
669 return number is
670 l_amt number := 0;
671 l_effective_date date;
672 cursor c1 is
673 select
674 sum(max(contr.max_contr_allowed) - sum(contr.amt_contr)) amt
675 from
676 pay_us_contribution_history contr,
677 per_assignments_f paf
678 where
679 paf.assignment_id = p_assignment_id
680 and l_effective_date between paf.effective_start_date and
681 nvl(paf.effective_end_date, l_effective_date)
682 and contr.person_id = paf.person_id
683 and contr.contr_type = 'G'
684 and contr.date_to < l_effective_date
685 and contr.legislation_code = 'US'
686 group by contr.date_to;
687
688 begin
689
690 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
691 (p_payroll_action_id));
692 for c1_rec in c1
693 loop
694 l_amt := c1_rec.amt;
695 end loop;
696
697 return nvl(l_amt,0);
698
699 end;
700
701 ----------------------------------------------------
702 -- Function to calculate 457 limit
703 ----------------------------------------------------
704 function get_457_calc_limit (
705 p_payroll_action_id in number,
706 p_ee_incld_annual_comp in number
707 )
708 Return number is
709
710 l_calc_457_limit number := 0;
711 l_effective_date date;
712 begin
713 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
714 (p_payroll_action_id));
715 hr_utility.set_location('Start of get_457_calc_limit ', 5);
716
717 l_calc_457_limit := (p_ee_incld_annual_comp / 3) ;
718
719 hr_utility.set_location('End of get_457_calc_limit', 10);
720
721 return l_calc_457_limit;
722 end;
723
724 ----------------------------------------------------
725 -- Function to calculate 457 vested amount
726 ----------------------------------------------------
727 function get_457_vested_amt (
728 p_assignment_id in number,
729 p_payroll_action_id in number,
730 p_dummy in varchar)
731 return number is
732 l_effective_date date;
733 begin
734 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
735 (p_payroll_action_id));
736 hr_utility.set_location('Start of get_457_vested_amt ', 5);
737
738 return 0;
739
740 hr_utility.set_location('End of get_457_vested_amt', 10);
741
742 end;
743
744 --------------------------------------------------------------------------
745 --------------------------------------------------------------------------
746
747 ----------------------------------------------
748 --- Function Run Year
749 ----------------------------------------------
750 function Run_Year (
751 p_payroll_action_id in number,
752 p_dummy in varchar)
753 return number is
754 l_effective_date date;
755 begin
756 l_effective_date := TRUNC(pqp_car_mileage_functions.pqp_get_date_paid
757 (p_payroll_action_id));
758 hr_utility.set_location('Start of Run Year ', 5);
759 return to_number(to_char(l_effective_date,'YYYY'));
760 hr_utility.set_location('End of Run Year ', 10);
761
762 end;
763 --------------------------------------------------------------------------
764
765 ----------------------------------------------
766 --- Function Get Template Earnings
767 ----------------------------------------------
768 function get_template_earnings (
769 p_ctx_original_entry_id NUMBER,
770 p_template_earnings NUMBER,
771 p_accrued_value NUMBER,
772 p_maximum_amount NUMBER,
773 p_prorate_start_date DATE,
774 p_prorate_end_date DATE,
775 p_payroll_start_date DATE,
776 p_payroll_end_date DATE,
777 p_stop_entry_flag OUT NOCOPY VARCHAR2,
778 p_clear_accrued_flag OUT NOCOPY VARCHAR2)
779 return number is
780 ln_template_earnings NUMBER;
781 ld_ele_entry_start_date DATE;
782 ld_ele_entry_end_date DATE;
783
784 cursor c_get_ele_entry_dates is
785 select min(effective_start_date),
786 max(effective_end_date)
787 from pay_element_entries_f
788 where element_entry_id = p_ctx_original_entry_id
789 group by element_entry_id;
790
791 begin
792
793
794 /*
795 * Value of the Parameters passed
796 */
797 hr_utility.trace('Original Entry ID = ' || p_ctx_original_entry_id);
798 hr_utility.trace('Template Earnings = ' || p_template_earnings);
799 hr_utility.trace('Accrued Value = ' || p_accrued_value);
800 hr_utility.trace('Maximum Amount = ' || p_maximum_amount);
801 hr_utility.trace('Prorate Start Date = ' || p_prorate_start_date);
802 hr_utility.trace('Prorate End Date = ' || p_prorate_end_date);
803 hr_utility.trace('Payroll Start Date = ' || p_payroll_start_date);
804 hr_utility.trace('Payroll End Date = ' || p_payroll_end_date);
805
806 /*
807 * Resetting the Global variables whenever a new element is getting processed
808 */
809
810 if GLB_ORIGINAL_ENTRY_ID is NULL then
811 GLB_ORIGINAL_ENTRY_ID := -9999;
812 end if;
813
814 if GLB_ORIGINAL_ENTRY_ID <> p_ctx_original_entry_id then
815 GLB_ORIGINAL_ENTRY_ID := p_ctx_original_entry_id;
816 GLB_TEMPLATE_EARNINGS := 0;
817 GLB_STOP_ENTRY_FLAG := 'N';
818 end if;
819
820 /*
821 * Get the element entry start dates and end dates
822 * to correctly set the Global variables
823 */
824 open c_get_ele_entry_dates;
825 fetch c_get_ele_entry_dates into ld_ele_entry_start_date
826 ,ld_ele_entry_end_date;
827 close c_get_ele_entry_dates;
828
829 /*
830 * If both Prorate Start date and Payroll start date are equal
831 * then it is the first FLSA period in the Payroll run and we need to
832 * set the Global variable properly.
833 */
834 if p_prorate_start_date = p_payroll_start_date then
835 if (p_maximum_amount <= p_template_earnings + p_accrued_value) then
836 GLB_TEMPLATE_EARNINGS := p_maximum_amount - p_accrued_value;
837 GLB_STOP_ENTRY_FLAG := 'Y';
838 else
839 GLB_TEMPLATE_EARNINGS := p_template_earnings;
840 GLB_STOP_ENTRY_FLAG := 'N';
841 end if;
842 else
843 /*
844 * In case Prorate Start date and Payroll start date are not equal
845 * there are two cases
846 * 1. Element Entry Started on a date later than Payroll Start date
847 * in which case we again need to set the Global variables as the
848 * First FLSA period for this element falls on a different date.
849 * 2. This might be the next FLSA period
850 */
851 if ld_ele_entry_start_date > p_payroll_start_date then
852 /*
853 * To check for the First FLSA period as first FLSA period will start
854 * from the Element Entry date if the same was more than Payroll start
855 * date
856 */
857 if ld_ele_entry_start_date = p_prorate_start_date then
858 if (p_maximum_amount <= p_template_earnings + p_accrued_value) then
859 GLB_TEMPLATE_EARNINGS := p_maximum_amount - p_accrued_value;
860 GLB_STOP_ENTRY_FLAG := 'Y';
861 else
862 GLB_TEMPLATE_EARNINGS := p_template_earnings;
863 GLB_STOP_ENTRY_FLAG := 'N';
864 end if;
865 end if;
866 end if;
867 end if;
868
869 ln_template_earnings := GLB_TEMPLATE_EARNINGS;
870 p_stop_entry_flag := 'N';
871 /*
872 * Setting of STOP_ENTRY_FLAG to 'Y' should be only on the last FLSA period
873 * for the element
874 * For element manulally end dated, we also need to set the flag to 'Y'
875 * during the last FLSA period
876 */
877 if ld_ele_entry_end_date < p_payroll_end_date then
878 if ld_ele_entry_end_date = p_prorate_end_date then
879 p_clear_accrued_flag := 'Y';
880 end if;
881 else
882 if (GLB_STOP_ENTRY_FLAG = 'Y'
883 AND p_prorate_end_date = p_payroll_end_date) then
884 p_stop_entry_flag := 'Y';
885 end if;
886 end if;
887
888 hr_utility.trace('Calculated Earnings = ' || ln_template_earnings);
889 hr_utility.trace('');
890 -- hr_utility.trace_off();
891
892 return to_number(ln_template_earnings);
893 end;
894
895 ----------------------------------------------
896 --- Function Check Authorization Date
897 ----------------------------------------------
898 function check_authorization_date (
899 p_ctx_original_entry_id NUMBER,
900 p_auth_end_date DATE,
901 p_prorate_end_date DATE,
902 p_payroll_end_date DATE,
903 p_clear_accrued_flag OUT NOCOPY VARCHAR2)
904 return varchar2 is
905
906 lv_stop_entry_flag VARCHAR2(10);
907 ld_ele_entry_start_date DATE;
908 ld_ele_entry_end_date DATE;
909
910
911 cursor c_get_ele_entry_dates is
912 select min(effective_start_date),
913 max(effective_end_date)
914 from pay_element_entries_f
915 where element_entry_id = p_ctx_original_entry_id
916 group by element_entry_id;
917
918 begin
919
920
921 /*
922 * Value of the Parameters passed
923 */
924 hr_utility.trace('Original Entry ID = ' || p_ctx_original_entry_id);
925 hr_utility.trace('Auth End Date = ' || p_auth_end_date);
926 hr_utility.trace('Prorate Start Date = ' || p_prorate_end_date);
927 hr_utility.trace('Prorate End Date = ' || p_prorate_end_date);
928
929 /*
930 * Get the element entry start dates and end dates
931 * to correctly set the Global variables
932 */
933 open c_get_ele_entry_dates;
934 fetch c_get_ele_entry_dates into ld_ele_entry_start_date
935 ,ld_ele_entry_end_date;
936 close c_get_ele_entry_dates;
937
938 /*
939 * Check if we need to end date the element becuase of
940 * Authorization end date being reached
941 */
942 if p_auth_end_date < p_payroll_end_date then
943 /*
944 * Set only the Clear accrued flag if element has been end dated
945 * manually and the end date lies in the current Payroll period
946 */
947 if ld_ele_entry_end_date < p_payroll_end_date then
948 if p_prorate_end_date = ld_ele_entry_end_date then
949 p_clear_accrued_flag := 'Y';
950 end if;
951 else
952 if p_prorate_end_date = p_payroll_end_date then
953 lv_stop_entry_flag := 'Y';
954 end if;
955 end if;
956 else
957 /*
958 * Set Clear Accrued flag if element has been end dated manually
959 */
960 if (ld_ele_entry_end_date < p_payroll_end_date AND
961 p_prorate_end_date = ld_ele_entry_end_date) then
962 p_clear_accrued_flag := 'Y';
963 end if;
964 end if;
965 return lv_stop_entry_flag;
966 end;
967
968 ----------------------------------------------
969 --- Function Get Earnings Calculation
970 ----------------------------------------------
971 function get_earnings_calculation (
972 p_ctx_original_entry_id NUMBER,
973 p_adjust_flag VARCHAR2,
974 p_max_adjust_amount NUMBER,
975 p_total_earnings NUMBER,
976 p_period_earnings NUMBER,
977 p_prorate_end_date DATE,
978 p_payroll_end_date DATE
979 )
980 return number is
981
982 l_period_earnings number;
983 l_amt_difference number;
984
985 begin
986
987 hr_utility.trace('Entering GET_CORRECT_FLSA_EARNINGS');
988 hr_utility.trace('ORIGINAL ENTRY ID........' || p_ctx_original_entry_id);
989 hr_utility.trace('ADJUST FLAG..............' || p_adjust_flag);
990 hr_utility.trace('MAX ADJUST AMOUNT .......' || p_max_adjust_amount);
991 hr_utility.trace('TOTAL EARNINGS...........' || p_total_earnings);
992 hr_utility.trace('PERIOD EARNINGS..........' || p_period_earnings);
993 hr_utility.trace('PRORATE END DATE.........' || p_prorate_end_date);
994 hr_utility.trace('PAYROLL END DATE.........' || p_payroll_end_date);
995 hr_utility.trace('GLB_ORIGINAL_ENTRY_ID_2..' || GLB_ORIGINAL_ENTRY_ID_2);
996 l_period_earnings := 0;
997
998 -- Initialize GLB_ORIGINAL_ENTRY_ID_2
999 if GLB_ORIGINAL_ENTRY_ID_2 is NULL then
1000 GLB_ORIGINAL_ENTRY_ID_2 := -9999;
1001 end if;
1002
1003 if GLB_ORIGINAL_ENTRY_ID_2 <> p_ctx_original_entry_id then
1004 hr_utility.trace('Initializing Variables');
1005 GLB_ORIGINAL_ENTRY_ID_2 := p_ctx_original_entry_id;
1006 GLB_PERIOD_EARNINGS := 0;
1007 end if;
1008
1009 -- Return the corrected earnings value in case of Allocation
1010 -- during the last FLSA Period
1011 if p_adjust_flag = 'Y' then
1012 if p_payroll_end_date = p_prorate_end_date then
1013 l_amt_difference := p_total_earnings - GLB_PERIOD_EARNINGS - p_period_earnings;
1014 hr_utility.trace('Difference = ' || l_amt_difference);
1015 if l_amt_difference < p_max_adjust_amount then
1016 l_period_earnings := p_total_earnings - GLB_PERIOD_EARNINGS;
1017 hr_utility.trace('l_period_earnings1 = ' || l_period_earnings);
1018 else
1019 l_period_earnings := round(p_period_earnings,2);
1020 hr_utility.trace('l_period_earnings1 = ' || l_period_earnings);
1021 end if;
1022 else
1023 l_period_earnings := round(p_period_earnings,2);
1024 GLB_PERIOD_EARNINGS := GLB_PERIOD_EARNINGS + l_period_earnings;
1025 hr_utility.trace('l_period_earnings2 = ' || l_period_earnings);
1026 hr_utility.trace('GLB_PERIOD_EARNINGS = ' || GLB_PERIOD_EARNINGS);
1027 end if; -- if p_payroll_end_date
1028 else
1029 l_period_earnings := p_period_earnings;
1030 hr_utility.trace('No Adjustment Done');
1031 end if; -- if p_adjust_flag = 'Y'
1032
1033 return l_period_earnings;
1034
1035 end;
1036 ---------------------------------------------------
1037 --- Function get_salbasis_detail
1038 --- Returns values are
1039 --- * 'Y' ====> Salary Basis
1040 --- * 'REG' ====> Regular Element
1041 --- * 'REDREG' ====> Reduce Regular Element
1042 --- * 'NONREG' ====> Non-Regular Element
1043 ---------------------------------------------------
1044 function GET_SALARY_BASIS_DETAIL(
1045 original_entry_id NUMBER,
1046 template_earning NUMBER,
1047 hours_passed NUMBER,
1048 red_reg_earnings NUMBER,
1049 red_reg_hours NUMBER,
1050 prorate_start DATE,
1051 prorate_end DATE,
1052 payroll_start_date DATE,
1053 payroll_end_date DATE,
1054 flsa_time_definition VARCHAR2,
1055 stop_run_flag OUT NOCOPY VARCHAR2,
1056 reduced_template_earnings OUT NOCOPY NUMBER,
1057 reduced_hours_passed OUT NOCOPY NUMBER,
1058 red_reg_adjust_amount NUMBER default 0.05,
1059 red_reg_adjust_hours NUMBER default 0.01,
1060 red_reg_raise_error VARCHAR2 default 'Y'
1061 )
1062 return varchar2 is
1063
1064 ln_reduced_template_earnings NUMBER;
1065 ln_reduced_hours_passed NUMBER;
1066 lv_salary_basis_element VARCHAR2(1);
1067 ld_ele_entry_start_date DATE;
1068 ld_ele_entry_end_date DATE;
1069 lv_element_name VARCHAR2(80);
1070 ln_element_type_id NUMBER;
1071 ln_assignment_id NUMBER;
1072 ln_sal_element_type_id NUMBER;
1073 ln_diff_earn NUMBER;
1074 ln_diff_hours NUMBER;
1075 lv_sal_element_name VARCHAR2(80);
1076 lv_red_reg_ele VARCHAR2(80);
1077 lv_return_val VARCHAR2(80);
1078 lv_reg_elem VARCHAR2(80);
1079 lv_classification VARCHAR2(80);
1080
1081 cursor c_get_ele_type_id (cp_original_entry_id number) is
1082 select pet.element_type_id, pet.element_name, pee.assignment_id,
1083 nvl(pet.element_information13,'N'),
1084 nvl(pet.element_information1, 'NONREG'),
1085 pec.classification_name
1086 from pay_element_entries_f pee,
1087 pay_element_links_f pel,
1088 pay_element_types_f pet,
1089 pay_element_classifications pec
1090 where pee.element_entry_id = cp_original_entry_id
1091 and pel.element_link_id = pee.element_link_id
1092 and pel.element_type_id = pet.element_type_id
1093 and pec.classification_id = pet.classification_id
1094 and pec.legislation_code = 'US'
1095 and pee.effective_end_date between pel.effective_start_date
1096 and pel.effective_end_date
1097 and pee.effective_end_date between pet.effective_start_date
1098 and pet.effective_end_date;
1099
1100
1101 cursor c_get_salary_basis_element(cp_assignment_id number,
1102 cp_date in date) is
1103 select ETYPE.element_type_id, ETYPE.element_name
1104 from per_all_assignments_f ASSIGN
1105 ,per_pay_bases BASES
1106 ,pay_input_values_f INPUTV
1107 ,pay_element_types_f ETYPE
1108 ,pay_rates RATE
1109 where cp_date BETWEEN ASSIGN.effective_start_date
1110 AND ASSIGN.effective_end_date
1111 and ASSIGN.assignment_id = cp_assignment_id
1112 and BASES.pay_basis_id (+)= ASSIGN.pay_basis_id
1113 and INPUTV.input_value_id (+)= BASES.input_value_id
1114 and cp_date between nvl (INPUTV.effective_start_date,cp_date)
1115 and nvl (INPUTV.effective_end_date,cp_date)
1116 and ETYPE.element_type_id (+)= INPUTV.element_type_id
1117 and cp_date between nvl (ETYPE.effective_start_date,cp_date)
1118 and nvl (ETYPE.effective_end_date,cp_date)
1119 and RATE.rate_id (+)= BASES.rate_id ;
1120
1121 begin
1122
1123 -- Value of the Parameters passed
1124 hr_utility.trace('GLB_RR_ORIGINAL_ENTRY_ID = ' || GLB_RR_ORIGINAL_ENTRY_ID);
1125 hr_utility.trace('GLB_RR_SAL_BASIS_ELEMENT = ' || GLB_RR_SAL_BASIS_ELEMENT);
1126 hr_utility.trace('GLB_RED_REG_ELE = ' || GLB_RED_REG_ELE);
1127 hr_utility.trace('stop_run_flag = ' || stop_run_flag);
1128
1129 hr_utility.trace('Original Entry ID = ' || original_entry_id);
1130 hr_utility.trace('Template Earning = ' || template_earning);
1131 hr_utility.trace('hours_passed = ' || hours_passed);
1132 hr_utility.trace('red_reg_earnings = ' || red_reg_earnings);
1133 hr_utility.trace('red_reg_hours = ' || red_reg_hours);
1134 hr_utility.trace('Prorate Start Date = ' || prorate_start);
1135 hr_utility.trace('Prorate End Date = ' || prorate_end);
1136 hr_utility.trace('Prorate Start Date = ' || prorate_start);
1137 hr_utility.trace('Prorate End Date = ' || prorate_end);
1138 hr_utility.trace('Payroll Start Date = ' || payroll_start_date);
1139 hr_utility.trace('Payroll End Date = ' || payroll_end_date);
1140 hr_utility.trace('Flsa_time_definition = ' || flsa_time_definition);
1141 hr_utility.trace('red_reg_adjust_amount = ' || red_reg_adjust_amount);
1142 hr_utility.trace('red_reg_adjust_hours = ' || red_reg_adjust_hours);
1143 hr_utility.trace('red_reg_raise_error = ' || red_reg_raise_error);
1144
1145 ln_reduced_hours_passed := 0;
1146 ln_reduced_template_earnings := 0;
1147
1148 if GLB_RR_ORIGINAL_ENTRY_ID is NULL then
1149 GLB_RR_ORIGINAL_ENTRY_ID := -9999;
1150 GLB_RR_SAL_BASIS_ELEMENT := 'N';
1151 GLB_RED_REG_ELE := 'Y';
1152 GLB_REG_ELEM := 'NONREG';
1153 stop_run_flag := 'N' ;
1154 ln_reduced_hours_passed := 0;
1155 ln_reduced_template_earnings := 0;
1156
1157 hr_utility.trace('GLB_RR_ORIGINAL_ENTRY_ID = '
1158 || GLB_RR_ORIGINAL_ENTRY_ID);
1159 hr_utility.trace('GLB_RR_SAL_BASIS_ELEMENT = '
1160 || GLB_RR_SAL_BASIS_ELEMENT);
1161 hr_utility.trace('GLB_RED_REG_ELE = ' || GLB_RED_REG_ELE);
1162 hr_utility.trace('stop_run_flag = '|| stop_run_flag);
1163 end if; -- if GLB_RR_ORIGINAL_ENTRY_ID
1164
1165 if GLB_RR_ORIGINAL_ENTRY_ID <> original_entry_id then
1166 GLB_RR_ORIGINAL_ENTRY_ID := original_entry_id ;
1167 GLB_RR_SAL_BASIS_ELEMENT := 'N';
1168 GLB_RED_REG_ELE := 'Y';
1169 GLB_REG_ELEM := 'NONREG';
1170 stop_run_flag := 'N' ;
1171 ln_reduced_hours_passed := 0;
1172 ln_reduced_template_earnings := 0;
1173
1174 hr_utility.trace('GLB_RR_ORIGINAL_ENTRY_ID Reset = '
1175 || GLB_RR_ORIGINAL_ENTRY_ID);
1176 hr_utility.trace('GLB_RR_SAL_BASIS_ELEMENT Reset = '
1177 || GLB_RR_SAL_BASIS_ELEMENT);
1178 hr_utility.trace('GLB_RED_REG_ELE Reset = ' || GLB_RED_REG_ELE);
1179 hr_utility.trace('stop_run_flag Reset = ' || stop_run_flag);
1180
1181 open c_get_ele_type_id(original_entry_id);
1182 fetch c_get_ele_type_id into ln_element_type_id
1183 ,lv_element_name
1184 ,ln_assignment_id
1185 ,lv_red_reg_ele
1186 ,lv_reg_elem
1187 ,lv_classification;
1188 close c_get_ele_type_id;
1189
1190 GLB_RED_REG_ELE := lv_red_reg_ele;
1191 if lv_reg_elem = 'REG' and upper(lv_classification) = 'EARNINGS' then
1192 GLB_REG_ELEM := 'REG';
1193 else
1194 GLB_REG_ELEM := 'NONREG';
1195 end if; -- if lv_reg_elem = 'REG'
1196
1197
1198 hr_utility.trace(' ln_assignment_id = ' ||to_char(ln_assignment_id));
1199 hr_utility.trace('ln_element_type_id Curr = '
1200 ||to_char(ln_element_type_id));
1201 hr_utility.trace('lv_element_name Curr = ' ||lv_element_name);
1202 hr_utility.trace('GLB_RED_REG_ELE = ' || GLB_RED_REG_ELE);
1203
1204 open c_get_salary_basis_element(ln_assignment_id,prorate_end);
1205 fetch c_get_salary_basis_element into ln_sal_element_type_id
1206 ,lv_sal_element_name;
1207 close c_get_salary_basis_element;
1208
1209 hr_utility.trace('ln_sal_element_type_id SB= '
1210 ||to_char(ln_sal_element_type_id));
1211 hr_utility.trace('lv_sal_element_name SB = '
1212 ||lv_sal_element_name);
1213
1214 if ln_element_type_id = ln_sal_element_type_id then
1215 GLB_RR_SAL_BASIS_ELEMENT := 'Y';
1216 hr_utility.trace('GLB_RR_SAL_BASIS_ELEMENT Matches = '
1217 || GLB_RR_SAL_BASIS_ELEMENT);
1218 end if;
1219 end if; -- if GLB_RR_ORIGINAL_ENTRY_ID <>
1220
1221 -- No checks required for Reduce Regular Element
1222 -- Only 'Regular' elements should be reduced.
1223 -- Regular elements are Elements with 'Earnings' classification
1224 -- and 'Regular' category
1225 if (GLB_RED_REG_ELE = 'N' and GLB_REG_ELEM = 'REG') then
1226 -- Add the adjust amount passed in and find the difference between
1227 -- Regular Earnings and Reduce Regular Earnings
1228 ln_diff_earn := template_earning - red_reg_earnings +
1229 red_reg_adjust_amount;
1230 if (ln_diff_earn >= 0 or red_reg_raise_error = 'N') then
1231 hr_utility.trace('Earnigs greated than Reduce Regular');
1232
1233 ln_reduced_template_earnings := template_earning - red_reg_earnings;
1234 if ln_reduced_template_earnings <= 0 then
1235 reduced_template_earnings := 0;
1236 else
1237 reduced_template_earnings := ln_reduced_template_earnings;
1238 end if; -- if
1239 hr_utility.trace('Reduced Template Earnings = '
1240 ||reduced_template_earnings);
1241 else
1242 hr_utility.trace('Else of earnings FLSA');
1243 reduced_template_earnings := 0;
1244 stop_run_flag := 'Y';
1245 hr_utility.trace(' stop_run_flag = '||stop_run_flag);
1246 end if; -- if (template_earning >=
1247
1248 -- Add the adjust hours passed in and find the difference between
1249 -- Regular Hours and Reduce Regular Hours
1250 ln_diff_hours := hours_passed - red_reg_hours +
1251 red_reg_adjust_hours;
1252 if (ln_diff_hours >= 0 or red_reg_raise_error = 'N') then
1253 hr_utility.trace('Hours greater than Red Reg Hours');
1254
1255 ln_reduced_hours_passed := hours_passed - red_reg_hours;
1256 if ln_reduced_hours_passed <=0 then
1257 reduced_hours_passed := 0;
1258 else
1259 reduced_hours_passed := ln_reduced_hours_passed;
1260 end if; -- if
1261 hr_utility.trace('Reduced Hours Passed = ' ||reduced_hours_passed);
1262 else
1263 hr_utility.trace('Else of hours FLSA');
1264 reduced_hours_passed := 0;
1265 stop_run_flag := 'Y';
1266 hr_utility.trace(' stop_run_flag = '||stop_run_flag);
1267 end if; -- if hours_passed >=
1268 end if; -- if GLB_RED_REG_ELE = 'N'
1269
1270 -- Override stopr_run_flag to 'N' when no error is to be raised
1271 if red_reg_raise_error = 'N' then
1272 stop_run_flag := 'N';
1273 hr_utility.trace('Overriden Value of Stop Run Flag to N');
1274 end if; -- if red_reg_raise_error =
1275
1276 hr_utility.trace('OUT stop_run_flag = ' || stop_run_flag);
1277 hr_utility.trace('OUT reduced_template_earnings = ' || reduced_template_earnings);
1278 hr_utility.trace('OUT reduced_hours_passed = ' || reduced_hours_passed);
1279 -- Return Values
1280 -- 'Y' ==> Salary Element
1281 -- Old formulae should work as instead of sending 'N' for
1282 -- elements that are not Salary Basis we are passing other
1283 -- values
1284 -- 'REDREG' ==> Reduce Regular Elements
1285 -- 'REG' ==> Regular Elements that are not attached to Salary Basis
1286 if GLB_RED_REG_ELE = 'N' then
1287 if GLB_RR_SAL_BASIS_ELEMENT = 'N' then
1288 if GLB_REG_ELEM = 'REG' then
1289 lv_return_val := 'REG';
1290 hr_utility.trace('Returning Regular Element');
1291 else
1292 lv_return_val := 'NONREG';
1293 hr_utility.trace('Returning Regular Element');
1294 end if; -- if GLB_REG_ELEM = 'REG'
1295 else
1296 lv_return_val := 'Y';
1297 hr_utility.trace('Returning Salary Basis');
1298 end if; -- if GLB_RR_SAL_BASIS_ELEMENT =
1299 else
1300 lv_return_val := 'REDREG';
1301 hr_utility.trace('Returning Reduce Regular');
1302 end if; -- if GLB_RED_REG_ELE =
1303
1304 return lv_return_val;
1305
1306 end;
1307
1308
1309 function get_time_definition(
1310 TIME_DEFINITION_ID NUMBER)
1311 RETURN VARCHAR2 is
1312
1313 cursor c_get_time_defn(cp_time_definition_id number) is
1314 select definition_name
1315 from pay_time_definitions
1316 where time_definition_id = cp_time_definition_id;
1317
1318 begin
1319
1320 -- Initialize GLB_TIME_DEFINITION_ID
1321 if GLB_TIME_DEFINITION_ID is NULL then
1322 GLB_TIME_DEFINITION_ID := -9999;
1323 end if;
1324
1325 hr_utility.trace(' GLB_TIME_DEFINITION_ID = '||
1326 to_char(GLB_TIME_DEFINITION_ID));
1327
1328 if GLB_TIME_DEFINITION_ID <> TIME_DEFINITION_ID then
1329
1330 open c_get_time_defn(TIME_DEFINITION_ID);
1331 fetch c_get_time_defn into GLB_TIME_DEFINITION_NAME;
1332 close c_get_time_defn ;
1333
1334 hr_utility.trace(' GLB_TIME_DEFINITION_NAME = '||
1335 GLB_TIME_DEFINITION_NAME);
1336
1337 if GLB_TIME_DEFINITION_NAME = 'Non Allocated Time Definition' then
1338 GLB_FLSA_TIME_DEFN := 'N';
1339 else
1340 GLB_FLSA_TIME_DEFN := 'Y';
1341 end if;
1342 end if;
1343
1344 hr_utility.trace(' GLB_FLSA_TIME_DEFN = '||
1345 GLB_FLSA_TIME_DEFN);
1346 --hr_utility.trace_off;
1347
1348 return GLB_FLSA_TIME_DEFN;
1349
1350
1351 end;
1352
1353
1354 end pay_ff_functions;