[Home] [Help]
PACKAGE BODY: APPS.BEN_EVALUATE_RATE_MATRIX
Source
1 Package Body ben_evaluate_rate_matrix as
2 /* $Header: benrtmtx.pkb 120.1 2005/12/23 01:30:41 mmudigon noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 -- Package Variables
9 --
10 TYPE node_tbl_typ IS TABLE OF varchar2(1) INDEX BY binary_integer;
11 g_node_tbl node_tbl_typ ;
12
13 g_debug boolean := hr_utility.debug_enabled;
14 g_package varchar2(33) := 'ben_evaluate_rate_matrix.';
15 --
16 -- ----------------------------------------------------------------------------
17 -- | Procedure Definitions |
18 -- ----------------------------------------------------------------------------
19 --
20 -- Public procedure to determine rate given a person and rate type
21 --
22 procedure determine_rate
23 (p_person_id number default null,
24 p_assignment_id number default null,
25 p_criteria_rate_defn_id number,
26 p_effective_date date,
27 p_business_group_id number,
28 p_rate_tab out nocopy rate_tab) is
29
30 cursor c_pl is
31 select distinct pln.pl_id
32 from ben_pl_f pln,
33 pqh_rate_matrix_nodes rmn
34 where p_effective_date between pln.effective_start_date
35 and pln.effective_end_date
36 and pln.business_group_id = p_business_group_id
37 and pln.pl_id = rmn.pl_id
38 and pln.pl_stat_cd = 'A';
39
40 cursor c_rate_matrix(p_pl_id number) is
41 select level,
42 rmn.rate_matrix_node_id,
43 rmn.pl_id,
44 rmn.node_name,
45 rmn.parent_node_id,
46 rmn.eligy_prfl_id,
47 rmn.business_group_id,
48 rmn.level_number
49 from pqh_rate_matrix_nodes rmn
50 start with rmn.rate_matrix_node_id in
51 (select rate_matrix_node_id
52 from pqh_rate_matrix_nodes
53 where business_group_id = p_business_group_id
54 and pl_id = p_pl_id
55 and parent_node_id is null)
56 connect by prior rmn.rate_matrix_node_id = rmn.parent_node_id;
57 l_rate_matrix_rec c_rate_matrix%rowtype;
58
59 cursor c_rt is
60 select rate_matrix_node_id,
61 rate_matrix_rate_id,
62 min_rate_value,
63 max_rate_value,
64 mid_rate_value,
65 rate_value
66 from pqh_rate_matrix_rates_f
67 where rate_matrix_node_id = l_rate_matrix_rec.rate_matrix_node_id
68 and p_effective_date between effective_start_date
69 and effective_end_date
70 and criteria_rate_defn_id = p_criteria_rate_defn_id;
71 l_rt_rec c_rt%rowtype;
72
73 l_rt_counter number := 0;
74 l_pl_id number;
75 l_mtx_pl_id number;
76 l_eligprof_tab ben_evaluate_elig_profiles.proftab;
77 l_parent_eligible varchar2(1);
78 l_node_eligible varchar2(1);
79 l_proc varchar2(61) := g_package||'determine_rate';
80
81 begin
82
83 g_debug := hr_utility.debug_enabled;
84
85 if g_debug then
86 hr_utility.set_location('Entering :'||l_proc,5);
87 hr_utility.set_location('p_person_id '||p_person_id,5);
88 hr_utility.set_location('p_assignment_id '||p_assignment_id,5);
89 hr_utility.set_location('p_criteria_rate_defn_id '||p_criteria_rate_defn_id,5);
90 hr_utility.set_location('p_effective_date '||p_effective_date,5);
91 end if;
92
93 hr_api.mandatory_arg_error(p_api_name => l_proc,
94 p_argument => 'effective_date',
95 p_argument_value => p_effective_date);
96
97 hr_api.mandatory_arg_error(p_api_name => l_proc,
98 p_argument => 'business_group_id',
99 p_argument_value => p_business_group_id);
100
101 hr_api.mandatory_arg_error(p_api_name => l_proc,
102 p_argument => 'criteria_rate_defn_id',
103 p_argument_value => p_criteria_rate_defn_id);
104
105 ben_per_asg_elig.clear_down_cache;
106
107 open c_pl;
108 loop
109 fetch c_pl into l_mtx_pl_id;
110 if c_pl%notfound then
111 exit;
112 end if;
113
114 hr_utility.set_location('l_mtx_pl_id '||l_mtx_pl_id,5);
115
116 open c_rate_matrix(l_mtx_pl_id);
117 loop
118 fetch c_rate_matrix into l_rate_matrix_rec;
119 if c_rate_matrix%notfound then
120 exit;
121 end if;
122
123 if g_debug then
124 hr_utility.set_location(lpad('+',(l_rate_matrix_rec.level-1),'+')||l_rate_matrix_rec.node_name,10);
125 hr_utility.set_location('elp id '||l_rate_matrix_rec.eligy_prfl_id,10);
126 hr_utility.set_location('pl id '||l_rate_matrix_rec.pl_id,10);
127 hr_utility.set_location('bg id '||l_rate_matrix_rec.business_group_id,10);
128 end if;
129
130 l_parent_eligible := 'N';
131 l_node_eligible := 'N';
132
133 if l_rate_matrix_rec.level = 1 then
134 g_node_tbl.delete;
135 l_pl_id := l_rate_matrix_rec.pl_id;
136 l_node_eligible := ben_per_asg_elig.elp_eligible(p_person_id,p_assignment_id,l_rate_matrix_rec.eligy_prfl_id,p_effective_date,l_mtx_pl_id,l_rate_matrix_rec.pl_id,p_business_group_id);
137 else
138 if g_node_tbl.exists(l_rate_matrix_rec.parent_node_id) then
139 l_parent_eligible := g_node_tbl(l_rate_matrix_rec.parent_node_id);
140 if l_parent_eligible = 'Y' then
141 l_node_eligible := ben_per_asg_elig.elp_eligible(p_person_id,p_assignment_id,l_rate_matrix_rec.eligy_prfl_id,p_effective_date,l_mtx_pl_id,l_rate_matrix_rec.pl_id,p_business_group_id);
142 else
143 l_node_eligible := 'N';
144 end if;
145 else
146 l_node_eligible := 'N';
147 end if;
148 end if;
149
150 g_node_tbl(l_rate_matrix_rec.rate_matrix_node_id) := l_node_eligible ;
151
152 if l_node_eligible = 'Y' then
153
154 l_rt_rec := null;
155 open c_rt;
156 fetch c_rt into l_rt_rec;
157 close c_rt;
158
159 if l_rt_rec.rate_matrix_node_id is not null and
160 l_pl_id is not null then
161 l_rt_counter := p_rate_tab.count +1;
162 p_rate_tab(l_rt_counter).pl_id := l_pl_id;
163 p_rate_tab(l_rt_counter).rate_matrix_node_id := l_rt_rec.rate_matrix_node_id;
164 p_rate_tab(l_rt_counter).rate_matrix_rate_id := l_rt_rec.rate_matrix_rate_id;
165 p_rate_tab(l_rt_counter).min_rate_value := l_rt_rec.min_rate_value;
166 p_rate_tab(l_rt_counter).max_rate_value := l_rt_rec.max_rate_value;
167 p_rate_tab(l_rt_counter).mid_rate_value := l_rt_rec.mid_rate_value;
168 p_rate_tab(l_rt_counter).rate_value := l_rt_rec.rate_value;
169 p_rate_tab(l_rt_counter).level_number := l_rate_matrix_rec.level_number;
170 end if;
171 end if;
172
173 end loop;
174 close c_rate_matrix;
175
176 end loop;
177 close c_pl;
178
179 if g_debug then
180 hr_utility.set_location('Leaving :'||l_proc,5);
181 end if;
182
183 end determine_rate;
184
185 end ben_evaluate_rate_matrix;