1 package body ben_life_object as
2 /* $Header: benlerde.pkb 120.0 2005/05/28 09:05:51 appldev noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Life Event Object Caching Routine
13 Purpose
14 This package is used to return life event object information.
15 History
16 Version Date Who What?
17 --------- --------- ---------- --------------------------------------------
18 115.0 25-Jun-2000 gperry Created
19 115.1 03-Apr-2000 mhoyes - Commented out nocopy trace statements.
20 115.2 09-Nov-2000 mhoyes - Removed + 0s.
21 115.3 13-Dec-2002 kmahendr Nocopy changes
22 -----------------------------------------------------------------------------
23 */
24 --
25 g_package varchar2(30) := 'ben_life_object.';
26 --
27 -- Set object routines
28 --
29 procedure set_object(p_rec in ben_css_rltd_per_per_in_ler_f%rowtype) is
30 --
31 l_proc varchar2(80) := g_package||'set_object';
32 l_index binary_integer;
33 --
34 begin
35 --
36 -- hr_utility.set_location('Entering '||l_proc,10);
37 --
38 l_index := nvl(g_cache_css_rec.count,0)+1;
39 --
40 g_cache_css_rec(l_index) := p_rec;
41 --
42 -- hr_utility.set_location('Leaving '||l_proc,10);
43 --
44 end set_object;
45 --
46 procedure set_object(p_rec in ben_ler_f%rowtype) is
47 --
48 l_proc varchar2(80) := g_package||'set_object';
49 l_index binary_integer;
50 --
51 begin
52 --
53 -- hr_utility.set_location('Entering '||l_proc,10);
54 --
55 l_index := nvl(g_cache_ler_rec.count,0)+1;
56 --
57 g_cache_ler_rec(l_index) := p_rec;
58 --
59 -- hr_utility.set_location('Leaving '||l_proc,10);
60 --
61 end set_object;
62 --
63 procedure set_css_ler_object(p_rec in ben_cache.g_cache_lookup) is
64 --
65 l_proc varchar2(80) := g_package||'set_css_ler_object';
66 l_index binary_integer;
67 l_not_hash_found boolean;
68 --
69 begin
70 --
71 -- hr_utility.set_location('Entering '||l_proc,10);
72 --
73 -- 1) get hash index
74 -- 2) If hash index is not used use hash index
75 -- 3) If hash index is used and correct then do nothing
76 -- 4) If hash index is used and not correct then try next hash index
77 --
78 -- Get hashed index value
79 --
80 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.id);
81 --
82 if not g_cache_css_ler_rec.exists(l_index) then
83 --
84 -- Lets store the hash value in this index
85 --
86 g_cache_css_ler_rec(l_index) := p_rec;
87 --
88 else
89 --
90 -- If it does exist check if its the right one
91 --
92 if g_cache_css_ler_rec(l_index).id <> p_rec.id then
93 --
94 -- Loop through the hash using the jump routine to check further
95 -- indexes
96 --
97 l_not_hash_found := false;
98 --
99 while not l_not_hash_found loop
100 --
101 l_index := ben_hash_utility.get_next_hash_index(p_hash_index =>l_index);
102 --
103 -- Check if the hash index exists, if not we can use it
104 --
105 if not g_cache_css_ler_rec.exists(l_index) then
106 --
107 -- Lets store the hash value in the index
108 --
109 g_cache_css_ler_rec(l_index) := p_rec;
110 l_not_hash_found := true;
111 --
112 else
113 --
114 -- Make sure the index is the correct one
115 --
116 if g_cache_css_ler_rec(l_index).id = p_rec.id then
117 --
118 -- We have a match so the hashed value has been stored before
119 --
120 l_not_hash_found := true;
121 --
122 end if;
123 --
124 end if;
125 --
126 end loop;
127 --
128 end if;
129 --
130 end if;
131 --
132 -- hr_utility.set_location('Leaving '||l_proc,10);
133 --
134 end set_css_ler_object;
135 --
136 procedure set_ler_ler_object(p_rec in ben_cache.g_cache_lookup) is
137 --
138 l_proc varchar2(80) := g_package||'set_ler_ler_object';
139 l_index binary_integer;
140 l_not_hash_found boolean;
141 --
142 begin
143 --
144 -- hr_utility.set_location('Entering '||l_proc,10);
145 --
146 -- 1) get hash index
147 -- 2) If hash index is not used use hash index
148 -- 3) If hash index is used and correct then do nothing
149 -- 4) If hash index is used and not correct then try next hash index
150 --
151 -- Get hashed index value
152 --
153 l_index := ben_hash_utility.get_hashed_index(p_id => p_rec.id);
154 --
155 if not g_cache_ler_ler_rec.exists(l_index) then
156 --
157 -- Lets store the hash value in this index
158 --
159 g_cache_ler_ler_rec(l_index) := p_rec;
160 --
161 else
162 --
163 -- If it does exist check if its the right one
164 --
165 if g_cache_ler_ler_rec(l_index).id <> p_rec.id then
166 --
167 -- Loop through the hash using the jump routine to check further
168 -- indexes
169 --
170 l_not_hash_found := false;
171 --
172 while not l_not_hash_found loop
173 --
174 l_index := ben_hash_utility.get_next_hash_index(p_hash_index =>l_index);
175 --
176 -- Check if the hash index exists, if not we can use it
177 --
178 if not g_cache_ler_ler_rec.exists(l_index) then
179 --
180 -- Lets store the hash value in the index
181 --
182 g_cache_ler_ler_rec(l_index) := p_rec;
183 l_not_hash_found := true;
184 --
185 else
186 --
187 -- Make sure the index is the correct one
188 --
189 if g_cache_ler_ler_rec(l_index).id = p_rec.id then
190 --
191 -- We have a match so the hashed value has been stored before
192 --
193 l_not_hash_found := true;
194 --
195 end if;
196 --
197 end if;
198 --
199 end loop;
200 --
201 end if;
202 --
203 end if;
204 --
205 -- hr_utility.set_location('Leaving '||l_proc,10);
206 --
207 end set_ler_ler_object;
208 --
209 -- Set object alternate route routines
210 --
211 procedure set_css_object
212 (p_ler_id in number,
213 p_business_group_id in number,
214 p_effective_date in date,
215 p_rec out nocopy g_cache_css_table) is
216 --
217 l_proc varchar2(80) := g_package||'set_css_object';
218 --
219 cursor c1 is
220 select css.*
221 from ben_css_rltd_per_per_in_ler_f css
222 where css.ler_id = p_ler_id
223 and css.business_group_id = p_business_group_id;
224 --
225 l_css_rec ben_life_object.g_cache_css_table;
226 l_rec ben_css_rltd_per_per_in_ler_f%rowtype;
227 l_css_ler_rec ben_cache.g_cache_lookup;
228 l_start_index number;
229 l_end_index number;
230 l_num_recs number := 0;
231 --
232 begin
233 --
234 -- hr_utility.set_location('Entering '||l_proc,10);
235 --
236 -- 1) Get record from database.
237 -- 2) If record not found then raise error.
238 -- 3) Pass record to set_object routine.
239 --
240 open c1;
241 --
242 loop
243 --
244 fetch c1 into l_rec;
245 exit when c1%notfound;
246 --
247 set_object(p_rec => l_rec);
248 --
249 l_num_recs := l_num_recs +1;
250 --
251 if l_num_recs = 1 then
252 --
253 l_css_ler_rec.starttorele_num := g_cache_css_rec.count;
254 --
255 end if;
256 --
257 if l_rec.ler_id = p_ler_id and
258 p_effective_date
259 between l_rec.effective_start_date
260 and l_rec.effective_end_date then
261 --
262 l_css_rec(l_css_rec.count+1) := l_rec;
263 --
264 end if;
265 --
266 end loop;
267 --
268 close c1;
269 --
270 l_css_ler_rec.id := p_ler_id;
271 --
272 if l_css_ler_rec.starttorele_num is not null then
273 --
274 l_css_ler_rec.endtorele_num := g_cache_css_rec.count;
275 --
276 end if;
277 --
278 -- Save master details to cache structure
279 --
280 set_css_ler_object(p_rec => l_css_ler_rec);
281 --
282 p_rec := l_css_rec;
283 --
284 -- hr_utility.set_location('Leaving '||l_proc,10);
285 --
286 end set_css_object;
287 --
288 procedure set_ler_object
289 (p_ler_id in number,
290 p_business_group_id in number,
291 p_effective_date in date,
292 p_rec out nocopy ben_ler_f%rowtype) is
293 --
294 l_proc varchar2(80) := g_package||'set_ler_object';
295 --
296 cursor c1 is
297 select ler.*
298 from ben_ler_f ler
299 where ler.business_group_id = p_business_group_id;
300 --
301 l_ler_rec ben_life_object.g_cache_ler_table;
302 l_rec ben_ler_f%rowtype;
303 l_ler_ler_rec ben_cache.g_cache_lookup;
304 l_start_index number;
305 l_end_index number;
306 l_num_recs number := 0;
307 --
308 begin
309 --
310 -- hr_utility.set_location('Entering '||l_proc,10);
311 --
312 -- 1) Get record from database.
313 -- 2) If record not found then raise error.
314 -- 3) Pass record to set_object routine.
315 --
316 open c1;
317 --
318 loop
319 --
320 fetch c1 into l_rec;
321 exit when c1%notfound;
322 --
323 set_object(p_rec => l_rec);
324 --
325 l_num_recs := l_num_recs +1;
326 --
327 if l_num_recs = 1 then
328 --
329 l_ler_ler_rec.starttorele_num := g_cache_ler_rec.count;
330 --
331 end if;
332 --
333 if l_rec.ler_id = p_ler_id and
334 p_effective_date
335 between l_rec.effective_start_date
336 and l_rec.effective_end_date then
337 --
338 p_rec := l_rec;
339 --
340 end if;
341 --
342 end loop;
343 --
344 close c1;
345 --
346 l_ler_ler_rec.id := p_ler_id;
347 --
348 if l_ler_ler_rec.starttorele_num is not null then
349 --
350 l_ler_ler_rec.endtorele_num := g_cache_ler_rec.count;
351 --
352 end if;
353 --
354 -- Save master details to cache structure
355 --
356 set_ler_ler_object(p_rec => l_ler_ler_rec);
357 --
358 -- hr_utility.set_location('Leaving '||l_proc,10);
359 --
360 end set_ler_object;
361 --
362 -- Get object routines
363 --
364 procedure get_object(p_ler_id in number,
365 p_rec out nocopy ben_ler_f%rowtype) is
366 --
367 l_proc varchar2(80) := g_package||'get_object';
368 l_index binary_integer;
369 l_not_hash_found boolean;
370 l_env_rec ben_env_object.g_global_env_rec_type;
371 --
372 begin
373 --
374 -- hr_utility.set_location('Entering '||l_proc,10);
375 --
376 -- 1) Get hashed index
377 -- 2) If hashed index is correct person_id then return person_id
378 -- 3) If hashed index is not correct person_id then check next index
379 -- 4) Repest 3 until correct person_id found, if not found raise error.
380 --
381 -- Get hashed index value
382 --
383 l_index := ben_hash_utility.get_hashed_index(p_id => p_ler_id);
384 --
385 if g_cache_ler_ler_rec.exists(l_index) then
386 --
387 -- Lets get the hashed record.
388 --
389 if g_cache_ler_ler_rec(l_index).id = p_ler_id then
390 --
391 null;
392 --
393 else
394 --
395 -- We need to loop through all the hashed indexes
396 --
397 l_not_hash_found := false;
398 --
399 while not l_not_hash_found loop
400 --
401 l_index := ben_hash_utility.get_next_hash_index(p_hash_index =>l_index);
402 --
403 -- Check if the hash index exists, if not error
404 --
405 if not g_cache_ler_ler_rec.exists(l_index) then
406 --
407 -- Raise an error as we are trying to retrieve a non cached object
408 --
409 raise no_data_found;
410 --
411 else
412 --
413 -- Make sure the index is the correct one
414 --
415 if g_cache_ler_ler_rec(l_index).id = p_ler_id then
416 --
417 -- We have a match
418 --
419 l_not_hash_found := true;
420 --
421 end if;
422 --
423 end if;
424 --
425 end loop;
426 --
427 end if;
428 --
429 else
430 --
431 raise no_data_found;
432 --
433 end if;
434 --
435 -- At this point we have the master record so we can derive the set of
436 -- children by looping through the relevant records.
437 --
438 ben_env_object.get(p_rec => l_env_rec);
439 --
440 if g_cache_ler_ler_rec(l_index).starttorele_num is not null then
441 --
442 for l_count in g_cache_ler_ler_rec(l_index).starttorele_num..
443 g_cache_ler_ler_rec(l_index).endtorele_num loop
444 --
445 if g_cache_ler_rec(l_count).ler_id = p_ler_id and
446 nvl(l_env_rec.lf_evt_ocrd_dt,l_env_rec.effective_date)
447 between g_cache_ler_rec(l_count).effective_start_date
448 and g_cache_ler_rec(l_count).effective_end_date then
449 --
450 p_rec := g_cache_ler_rec(l_count);
451 --
452 end if;
453 --
454 end loop;
455 --
456 end if;
457 --
458 -- hr_utility.set_location('Leaving '||l_proc,10);
459 --
460 exception
461 --
462 when no_data_found then
463 --
464 ben_env_object.get(p_rec => l_env_rec);
465 --
466 set_ler_object(p_ler_id => p_ler_id,
467 p_business_group_id => l_env_rec.business_group_id,
468 p_effective_date => nvl(l_env_rec.lf_evt_ocrd_dt,
469 l_env_rec.effective_date),
470 p_rec => p_rec);
471 --
472 end get_object;
473 --
474 procedure get_object(p_ler_id in number,
475 p_rec out nocopy g_cache_css_table) is
476 --
477 l_proc varchar2(80) := g_package||'get_object';
478 l_index binary_integer;
479 l_not_hash_found boolean;
480 l_env_rec ben_env_object.g_global_env_rec_type;
481 l_rec ben_life_object.g_cache_css_table;
482 --
483 begin
484 --
485 -- hr_utility.set_location('Entering '||l_proc,10);
486 --
487 -- 1) Get hashed index
488 -- 2) If hashed index is correct person_id then return person_id
489 -- 3) If hashed index is not correct person_id then check next index
490 -- 4) Repest 3 until correct person_id found, if not found raise error.
491 --
492 -- Get hashed index value
493 --
494 l_index := ben_hash_utility.get_hashed_index(p_id => p_ler_id);
495 --
496 if g_cache_css_ler_rec.exists(l_index) then
497 --
498 -- Lets get the hashed record.
499 --
500 if g_cache_css_ler_rec(l_index).id = p_ler_id then
501 --
502 null;
503 --
504 else
505 --
506 -- We need to loop through all the hashed indexes
507 --
508 l_not_hash_found := false;
509 --
510 while not l_not_hash_found loop
511 --
512 l_index := ben_hash_utility.get_next_hash_index(p_hash_index =>l_index);
513 --
514 -- Check if the hash index exists, if not error
515 --
516 if not g_cache_css_ler_rec.exists(l_index) then
517 --
518 -- Raise an error as we are trying to retrieve a non cached object
519 --
520 raise no_data_found;
521 --
522 else
523 --
524 -- Make sure the index is the correct one
525 --
526 if g_cache_css_ler_rec(l_index).id = p_ler_id then
527 --
528 -- We have a match
529 --
530 l_not_hash_found := true;
531 --
532 end if;
533 --
534 end if;
535 --
536 end loop;
537 --
538 end if;
539 --
540 else
541 --
542 raise no_data_found;
543 --
544 end if;
545 --
546 -- At this point we have the master record so we can derive the set of
547 -- children by looping through the relevant records.
548 --
549 ben_env_object.get(p_rec => l_env_rec);
550 --
551 if g_cache_css_ler_rec(l_index).starttorele_num is not null then
552 --
553 for l_count in g_cache_css_ler_rec(l_index).starttorele_num..
554 g_cache_css_ler_rec(l_index).endtorele_num loop
555 --
556 if g_cache_css_rec(l_count).ler_id = p_ler_id and
557 nvl(l_env_rec.lf_evt_ocrd_dt,l_env_rec.effective_date)
558 between g_cache_css_rec(l_count).effective_start_date
559 and g_cache_css_rec(l_count).effective_end_date then
560 --
561 l_rec(l_rec.count+1) := g_cache_css_rec(l_count);
562 --
563 end if;
564 --
565 end loop;
566 --
567 end if;
568 --
569 -- Assign l_rec to p_rec
570 --
571 p_rec := l_rec;
572 --
573 -- hr_utility.set_location('Leaving '||l_proc,10);
574 --
575 exception
576 --
577 when no_data_found then
578 --
579 ben_env_object.get(p_rec => l_env_rec);
580 --
581 set_css_object(p_ler_id => p_ler_id,
582 p_business_group_id => l_env_rec.business_group_id,
583 p_effective_date => nvl(l_env_rec.lf_evt_ocrd_dt,
584 l_env_rec.effective_date),
585 p_rec => l_rec);
586 --
587 p_rec := l_rec;
588 --
589 end get_object;
590 --
591 procedure clear_down_cache is
592 --
593 begin
594 --
595 g_cache_ler_rec.delete;
596 g_cache_ler_ler_rec.delete;
597 g_cache_css_rec.delete;
598 g_cache_css_ler_rec.delete;
599 --
600 end clear_down_cache;
601 --
602 end ben_life_object;
[Home] [Help]