PACKAGE BODY: APPS.PER_ONEBOX_VACBAL_ACCRUAL

Source

1 PACKAGE BODY per_onebox_vacbal_accrual as
2 /* $Header: peonebvb.pkb 120.0 2006/06/02 18:02:49 jarthurt noship $ */
3 cursor get_plan_id_csr(p_person_id in number) is
4     select distinct paf.assignment_id,
5            pap.accrual_plan_id
6     from pay_accrual_plans pap,
7          per_assignments_f paf,
8          per_people_f ppf,
9          pay_element_types_f pet,
10          pay_element_entries_f pee,
11          pay_element_links_f pel
12 where pap.accrual_plan_element_type_id = pet.element_type_id
13 and pel.element_link_id = pee.element_link_id
14 and pel.element_type_id = pet.element_type_id
15 and pee.assignment_id = paf.assignment_id
16 and paf.person_id = ppf.person_id
17 and ppf.person_id = p_person_id
18 and pap.accrual_category = 'V'
19 and sysdate between paf.effective_start_date and paf.effective_end_date
20 and sysdate between ppf.effective_start_date and ppf.effective_end_date
21 and sysdate between pee.effective_start_date and pee.effective_end_date
22 and sysdate between pel.effective_start_date and pel.effective_end_date
23 and sysdate between pet.effective_start_date and pet.effective_end_date;
24 
25 function net_balance(p_person_id in number,
26                      p_date      in date) return number is
27 l_net_bal number := 0;
28 l_total_bal number := 0;
29 l_accrual_plan_id number := 0;
30 l_date date;
31 begin
32   for gpi in get_plan_id_csr(p_person_id)
33   loop
34     HR_PTO_VIEWS.Get_pto_ytd_net_entitlement(
35            p_assignment_id        => gpi.assignment_id
36           ,p_plan_id              => gpi.accrual_plan_id
37           ,p_calculation_date     => p_date
38           ,p_net_entitlement      => l_net_bal
39           ,p_last_accrual_date    => l_date);
40     l_total_bal := l_total_bal + l_net_bal;
41   end loop;
42 
43   return l_net_bal;
44 exception
45   when OTHERS then
46     l_total_bal := -99999;
47     return l_total_bal;
48 end net_balance;
49 
50 function time_off_taken(p_person_id in number,
51                         p_date      in date) return number is
52 l_time_off number := 0;
53 l_accrual_plan_id number := 0;
54 begin
55 for gpi in get_plan_id_csr(p_person_id)
56   loop
57     l_time_off := l_time_off + per_accrual_calc_functions.get_absence
58     (p_assignment_id    => gpi.assignment_id
59     ,p_plan_id          => gpi.accrual_plan_id
60     ,p_calculation_date => p_date
61     ,p_start_date       => fnd_date.canonical_to_date(substr(fnd_date.date_to_canonical(p_date), 1, 4) || '/01/01 00:00:00'));
62   end loop;
63   return l_time_off;
64 
65 exception
66   when OTHERS then
67     l_time_off := -99999;
68     return l_time_off;
69 end time_off_taken;
70 
71 end per_onebox_vacbal_accrual;