[Home] [Help]
PACKAGE BODY: APPS.BEN_RT_ASNT_CACHE
Source
1 package body ben_rt_asnt_cache as
2 /* $Header: bertasch.pkb 115.0 2004/02/02 11:54:44 kmahendr noship $ */
3 --
4 /*
5 * +==============================================================================+
6 * | Copyright (c) 1997 Oracle Corporation
7 * |
8 * | Redwood Shores, California, USA
9 * |
10 * | All rights reserved.
11 * |
12 * +==============================================================================+
13 * --
14 * History
15 * Version Date Author Comments
16 * --------------------------------------------
17 * 115.0 30-Jan-04 kmahendr created
18 * --------------------------------------------
19 */
20 -- Globals.
21 --
22 g_package varchar2(50) := 'ben_rt_asnt_cache.';
23 --
24 g_asntlookup ben_cache.g_cache_lookup_table;
25 g_nxelenum number;
26 g_asntinst ben_rt_asnt_cache.g_rt_asnt_inst_tbl;
27 g_asntcached pls_integer := 0;
28 --
29 g_hash_key pls_integer := 1299827;
30 g_hash_jump pls_integer := 100;
31 --
32 procedure write_asntcache
33 (p_effective_date in date
34 ,p_vrbl_rt_prfl_id in number default hr_api.g_number
35 --
36 ,p_hv out nocopy pls_integer
37 )
38 is
39 --
40 l_proc varchar2(72) := 'write_asntcache';
41 --
42 --
43 l_hv pls_integer;
44 l_not_hash_found boolean;
45 l_torrwnum pls_integer;
46 l_starttorele_num pls_integer;
47 l_asntlookup_rec ben_cache.g_cache_lookup;
48 --
49 cursor c_asr is
50 select asr.VRBL_RT_PRFL_ID,
51 ass.formula_id,
52 asr.excld_flag
53 from ben_asnt_set_rt_f asr,
54 hr_assignment_sets ass
55 where p_effective_date
56 between asr.effective_start_date
57 and asr.effective_end_date
58 and asr.assignment_set_id = ass.assignment_set_id
59 and asr.VRBL_RT_PRFL_ID = p_VRBL_RT_PRFL_ID
60 order by asr.VRBL_RT_PRFL_ID,
61 decode(asr.excld_flag,'Y',1,2),
62 asr.ordr_num;
63 l_instance c_asr%rowtype;
64 --
65 begin
66 --
67 hr_utility.set_location(' Entering '||l_proc,10);
68 --
69 -- Get the instance details
70 --
71 l_hv := mod(nvl(p_vrbl_rt_prfl_id,1),g_hash_key);
72 if g_asntlookup.exists(l_hv) then
73 --
74 if nvl(g_asntlookup(l_hv).id,-1) = nvl(p_vrbl_rt_prfl_id,-1)
75 then
76 --
77 null;
78 --
79 else
80 --
81 l_not_hash_found := false;
82 --
83 -- Loop until un-allocated has value is derived
84 --
85 while not l_not_hash_found loop
86 --
87 l_hv := l_hv+g_hash_jump;
88 --
89 -- Check if the hash index exists, and compare the values
90 --
91 if g_asntlookup.exists(l_hv) then
92 if nvl(g_asntlookup(l_hv).id,-1) = nvl(p_vrbl_rt_prfl_id,-1)
93 then
94 --
95 l_not_hash_found := true;
96 exit;
97 --
98 else
99 --
100 l_not_hash_found := false;
101 --
102 end if;
103 --
104 else
105 --
106 exit;
107 --
108 end if;
109 --
110 end loop;
111 --
112 end if;
113 --
114 end if;
115 g_asntlookup(l_hv).id := p_vrbl_rt_prfl_id;
116 --
117 hr_utility.set_location(' Dn Look '||l_proc,10);
118 --
119 l_starttorele_num := nvl(g_nxelenum,0);
120 l_torrwnum := l_starttorele_num;
121 --
122 open c_asr;
123 loop
124 fetch c_asr into l_instance;
125 if c_asr%notfound then
126 exit;
127 end if;
128 --
129 hr_utility.set_location('C Asr',11);
130 g_asntinst(l_torrwnum).id := l_instance.vrbl_rt_prfl_id;
131 g_asntinst(l_torrwnum).formula_id := l_instance.formula_id;
132 g_asntinst(l_torrwnum).excld_flag := l_instance.excld_flag;
133 --
134 l_torrwnum := l_torrwnum+1;
135
136 end loop;
137 close c_asr;
138 if l_torrwnum > nvl(g_nxelenum,0)
139 then
140 --
141 g_asntlookup(l_hv).starttorele_num := l_starttorele_num;
142 g_asntlookup(l_hv).endtorele_num := l_torrwnum-1;
143 g_nxelenum := l_torrwnum;
144 --
145 p_hv := l_hv;
146 --
147 else
148 --
149 -- Delete and free PGA with assignment
150 --
151 g_asntlookup.delete(l_hv);
152 g_asntlookup(l_hv) := l_asntlookup_rec;
153 --
154 p_hv := null;
155 --
156 end if;
157 --
158 hr_utility.set_location(' Leaving '||l_proc,10);
159 end write_asntcache;
160 --
161 procedure get_rt_asnt_cache
162 (p_vrbl_rt_prfl_id in number
163 ,p_effective_date in date
164 ,p_lf_evt_ocrd_dt in date
165 ,p_business_group_id in number
166 ,p_inst_set out nocopy ben_rt_asnt_cache.g_rt_asnt_inst_tbl
167 ,p_inst_count out nocopy number
168 )
169 is
170 --
171 l_inst_set ben_rt_asnt_cache.g_rt_asnt_inst_tbl;
172 --
173 l_hv pls_integer;
174 l_hash_found boolean;
175 l_insttorrw_num pls_integer;
176 l_torrwnum pls_integer;
177 --
178 l_clash_count pls_integer;
179 l_proc varchar2(300):= 'get_rt_asnt_cache';
180 --
181 begin
182 --
183 if g_asntcached = 0
184 then
185 --
186 -- Build the cache
187 --
188 clear_down_cache;
189 --
190 g_asntcached := 1;
191 --
192 end if;
193 -- hr_utility.set_location(' Derive hv '||l_proc,10);
194 ---- Get the instance details
195 --
196 l_hv := mod(nvl(p_vrbl_rt_prfl_id,1),g_hash_key);
197 --
198 -- Check if hashed value is already allocated
199 --
200 l_hash_found := false;
201 --
202 if g_asntlookup.exists(l_hv) then
203 --
204 if nvl(g_asntlookup(l_hv).id,-1) = nvl(p_vrbl_rt_prfl_id,-1)
205 then
206 --
207 null;
208 --
209 else
210 --
211 l_hash_found := false;
212 --
213 -- Loop until un-allocated has value is derived
214 --
215 l_clash_count := 0;
216 --
217 while not l_hash_found loop
218 --
219 l_hv := l_hv+g_hash_jump;
220 --
221 if g_asntlookup.exists(l_hv) then
222 --
223 -- Check if the hash index exists, and compare the values
224 --
225 if nvl(g_asntlookup(l_hv).id,-1) = nvl(p_vrbl_rt_prfl_id,-1)
226 then
227 --
228 l_hash_found := true;
229 exit;
230 --
231 else
232 --
233 l_clash_count := l_clash_count+1;
234 l_hash_found := false;
235 --
236 end if;
237 --
238 -- Check for high clash counts and defrag
239 --
240 if l_clash_count > 50
241 then
242 --
243 l_hv := null;
244 clear_down_cache;
245 exit;
246 --
247 end if;
248 --
249 else
250 --
251 l_hv := null;
252 exit;
253 --
254 end if;
255 --
256 end loop;
257 end if;
258 --
259 else
260 --
261 l_hv := null;
262 --
263 end if;
264 --
265 if l_hv is null
266 then
267 --
268 write_asntcache
269 (p_effective_date => p_effective_date
270 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
271 --
272 ,p_hv => l_hv
273 );
274 --
275 end if;
276 --
277 hr_utility.set_location(' Got hv '||l_proc,10);
278 --
279 if l_hv is not null then
280 --
281 l_torrwnum := 0;
282 --
283 hr_utility.set_location(' Get loop '||l_proc,10);
284 for l_insttorrw_num in g_asntlookup(l_hv).starttorele_num ..
285 g_asntlookup(l_hv).endtorele_num
286 loop
287 --
288 hr_utility.set_location('Instance set'||g_asntinst(l_insttorrw_num).formula_id,12);
289 l_inst_set(l_torrwnum) := g_asntinst(l_insttorrw_num);
290 l_torrwnum := l_torrwnum+1;
291 --
292 end loop;
293 --
294 hr_utility.set_location(' Dn Get loop '||l_proc,10);
295 --
296 end if;
297 --
298 p_inst_set := l_inst_set;
299 p_inst_count := l_inst_set.count;
300 --
301 hr_utility.set_location(' Leaving '||l_proc,10);
302 exception
303 --
304 when no_data_found then
305 --
306 p_inst_set := l_inst_set;
307 p_inst_count := 0;
308 --
309 end get_rt_asnt_cache;
310 --
311 procedure clear_down_cache
312 is
313 --
314 l_asntlookup ben_cache.g_cache_lookup_table;
315 l_asntinst ben_rt_asnt_cache.g_rt_asnt_inst_tbl;
316 --
317 begin
318 --
319 -- On demand cache structures
320 --
321 g_asntlookup := l_asntlookup;
322 g_asntinst := l_asntinst;
323 g_asntcached := 0;
324 g_nxelenum := null;
325 --
326 -- Grab back memory
327 --
328 begin
329 --
330 dbms_session.free_unused_user_memory;
331 --
332 end;
333 --
334 end clear_down_cache;
335 end;