1 package body ben_derive_part_and_rate_prem as
2 /* $Header: bendrpre.pkb 115.6 2002/10/23 00:58:11 ikasire noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Derive Participation and Rate Premium Routine
13 Purpose
14 This package is used to return or retrieve information that is
15 needed for rates and or factors.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 23 Mar 00 G Perry 115.0 Created.
20 27 Jun 00 G Perry 115.1 Added age_calc_rl support.
21 17-jan-01 tilak 115.2 derived factor validation changed
22 from > max to > max +1
23 16-Nov-01 ikasire 115.3 Bug 2101937 - Fixed the error in the ceil
24 condition of version 116.2 in all cursors.
25 03-Nov-01 ikasire 115.4 Bug 2101937 - fixed the typo in the
26 version 115.3
27 22-Oct-02 ikasire 115.6 Bug 2502763 add more parameters to clf
28 routine
29 */
30 --------------------------------------------------------------------------------
31 --
32 g_package varchar2(80) := 'ben_derive_part_and_rate_prem';
33 g_hash_key number := ben_hash_utility.get_hash_key;
34 g_hash_jump number := ben_hash_utility.get_hash_jump;
35 --
36 procedure get_los_pl_rate
37 (p_pl_id in number,
38 p_old_val in number default null,
39 p_new_val in number default null,
40 p_business_group_id in number,
41 p_effective_date in date,
42 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
43 --
44 l_package varchar2(80) := g_package||'.get_los_pl_rate';
45 --
46 -- Define Cursor
47 --
48 cursor c1 is
49 select p_pl_id,
50 'Y',
51 lsf.los_det_cd,
52 lsf.los_dt_to_use_cd,
53 lsf.use_overid_svc_dt_flag,
54 lsf.los_uom,
55 lsf.los_det_rl,
56 lsf.los_dt_to_use_rl,
57 lsf.los_calc_rl,
58 lsf.rndg_cd,
59 lsf.rndg_rl,
60 lsf.mn_los_num,
61 lsf.mx_los_num
62 from ben_los_fctr lsf,
63 ben_los_rt_f lsr,
64 ben_vrbl_rt_prfl_f vpf,
65 ben_actl_prem_vrbl_rt_f apv,
66 ben_actl_prem_f apr
67 where apr.pl_id = p_pl_id
68 and apr.business_group_id = p_business_group_id
69 and p_effective_date
70 between apr.effective_start_date
71 and apr.effective_end_date
72 and apr.actl_prem_id = apv.actl_prem_id
73 and apr.business_group_id = apv.business_group_id
74 and p_effective_date
75 between apv.effective_start_date
76 and apv.effective_end_date
77 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
78 and apv.business_group_id = vpf.business_group_id
79 and p_effective_date
80 between vpf.effective_start_date
81 and vpf.effective_end_date
82 and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
83 and vpf.business_group_id = lsr.business_group_id
84 and p_effective_date
85 between lsr.effective_start_date
86 and lsr.effective_end_date
87 and lsr.los_fctr_id = lsf.los_fctr_id
88 and lsr.business_group_id = lsf.business_group_id
89 and ((p_new_val is not null and
90 p_old_val is not null and
91 p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
92 -- p_new_val < ceil(nvl(lsf.mx_los_num,p_new_val)+ 0.001 ) )
93 p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
94 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
95 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
96 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ))
97 or
98 (p_new_val is not null and
99 p_old_val is not null and
100 (p_new_val < nvl(lsf.mn_los_num,p_new_val) or
101 -- p_new_val >= ceil(nvl(lsf.mx_los_num,p_new_val))+ 0.001 ) and
102 p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
103 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
104 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
105 nvl(lsf.mx_los_num,p_new_val)+0.000000001 )) and
106 p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
107 -- p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+ 0.001 )
108 p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
109 ,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
110 nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
111 nvl(lsf.mx_los_num,p_old_val)+0.000000001 ))
112 or
113 (p_new_val is null and
114 p_old_val is null));
115 --
116 l_index binary_integer;
117 l_not_hash_found boolean;
118 --
119 begin
120 --
121 -- hr_utility.set_location ('Entering '||l_package,10);
122 --
123 -- Steps to do process
124 --
125 -- 1) Try and get value from cache
126 -- 2) If can get from cache then copy to output record
127 -- 3) If can't get from cache do db hit and then
128 -- copy to cache record and then copy to output record.
129 --
130 -- Get hashed index value
131 --
132 l_index := mod(p_pl_id,g_hash_key);
133 --
134 if not g_cache_pl_los_rt_rec.exists(l_index) then
135 --
136 -- Lets store the hash value in this index
137 --
138 raise no_data_found;
139 --
140 else
141 --
142 -- If it does exist make sure its the right one
143 --
144 if g_cache_pl_los_rt_rec(l_index).id <> p_pl_id then
145 --
146 -- Loop through the hash using the jump routine to check further
147 -- indexes
148 --
149 l_not_hash_found := false;
150 --
151 while not l_not_hash_found loop
152 --
153 l_index := l_index+g_hash_jump;
154 --
155 -- Check if the hash index exists, if not we can use it
156 --
157 if not g_cache_pl_los_rt_rec.exists(l_index) then
158 --
159 -- Lets store the hash value in the index
160 --
161 raise no_data_found;
162 --
163 else
164 --
165 -- Make sure the index is the correct one
166 --
167 if g_cache_pl_los_rt_rec(l_index).id = p_pl_id then
168 --
169 -- We have a match so the hashed value has been stored before
170 --
171 l_not_hash_found := true;
172 --
173 end if;
174 --
175 end if;
176 --
177 end loop;
178 --
179 end if;
180 --
181 end if;
182 --
183 -- If p_old_val and p_new_val is set this means we are trying to retrieve
184 -- the correct rate for the calculated value.
185 -- Previously we just cached the first rate we
186 -- found since we needed the determination code, the correct age,los code,etc
187 -- By killing the cache and forcing the value to be removed we cache the
188 -- correct rate profile for the case we need.
189 --
190 if p_old_val is not null and p_new_val is not null then
191 --
192 raise no_data_found;
193 --
194 end if;
195 --
196 p_rec := g_cache_pl_los_rt_rec(l_index);
197 --
198 exception
199 --
200 when no_data_found then
201 --
202 -- The record has not been cached yet so lets cache it
203 --
204 open c1;
205 --
206 fetch c1 into g_cache_pl_los_rt_rec(l_index);
207 if p_old_val is null and p_new_val is null then
208 --
209 if c1%notfound then
210 --
211 g_cache_pl_los_rt_rec(l_index).id := p_pl_id;
212 g_cache_pl_los_rt_rec(l_index).exist := 'N';
213 --
214 end if;
215 --
216 end if;
217 --
218 p_rec := g_cache_pl_los_rt_rec(l_index);
219 --
220 close c1;
221 --
222 end get_los_pl_rate;
223 --
224 procedure get_los_oipl_rate
225 (p_oipl_id in number,
226 p_old_val in number default null,
227 p_new_val in number default null,
228 p_business_group_id in number,
229 p_effective_date in date,
230 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
231 --
232 l_package varchar2(80) := g_package||'.get_los_oipl_rate';
233 --
234 -- Define Cursor
235 --
236 cursor c1 is
237 select p_oipl_id,
238 'Y',
239 lsf.los_det_cd,
240 lsf.los_dt_to_use_cd,
241 lsf.use_overid_svc_dt_flag,
242 lsf.los_uom,
243 lsf.los_det_rl,
244 lsf.los_dt_to_use_rl,
245 lsf.los_calc_rl,
246 lsf.rndg_cd,
247 lsf.rndg_rl,
248 lsf.mn_los_num,
249 lsf.mx_los_num
250 from ben_los_fctr lsf,
251 ben_los_rt_f lsr,
252 ben_vrbl_rt_prfl_f vpf,
253 ben_actl_prem_vrbl_rt_f apv,
254 ben_actl_prem_f apr
255 where apr.oipl_id = p_oipl_id
256 and apr.business_group_id = p_business_group_id
257 and p_effective_date
258 between apr.effective_start_date
259 and apr.effective_end_date
260 and apr.actl_prem_id = apv.actl_prem_id
261 and apr.business_group_id = apv.business_group_id
262 and p_effective_date
263 between apv.effective_start_date
264 and apv.effective_end_date
265 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
266 and apv.business_group_id = vpf.business_group_id
267 and p_effective_date
268 between vpf.effective_start_date
269 and vpf.effective_end_date
270 and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
271 and vpf.business_group_id = lsr.business_group_id
272 and p_effective_date
273 between lsr.effective_start_date
274 and lsr.effective_end_date
275 and lsr.los_fctr_id = lsf.los_fctr_id
276 and lsr.business_group_id = lsf.business_group_id
277 and ((p_new_val is not null and
278 p_old_val is not null and
279 p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
280 --p_new_val < ceil(nvl(lsf.mx_los_num,p_new_val)+0.001) )
281 p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
282 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
283 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
284 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ))
285 or
286 (p_new_val is not null and
287 p_old_val is not null and
288 (p_new_val < nvl(lsf.mn_los_num,p_new_val) or
289 -- p_new_val >= ceil(nvl(lsf.mx_los_num,p_new_val))+0.001) and
290 p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
291 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
292 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
293 nvl(lsf.mx_los_num,p_new_val)+0.000000001 )) and
294 p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
295 --p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+0.001)
296 p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
297 ,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
298 nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
299 nvl(lsf.mx_los_num,p_old_val)+0.000000001 ))
300 or
301 (p_new_val is null and
302 p_old_val is null));
303 --
304 --
305 l_index binary_integer;
306 l_not_hash_found boolean;
307 --
308 begin
309 --
310 -- hr_utility.set_location ('Entering '||l_package,10);
311 --
312 -- Steps to do process
313 --
314 -- 1) Try and get value from cache
315 -- 2) If can get from cache then copy to output record
316 -- 3) If can't get from cache do db hit and then
317 -- copy to cache record and then copy to output record.
318 --
319 -- Get hashed index value
320 --
321 l_index := mod(p_oipl_id,g_hash_key);
322 --
323 if not g_cache_oipl_los_rt_rec.exists(l_index) then
324 --
325 -- Lets store the hash value in this index
326 --
327 raise no_data_found;
328 --
329 else
330 --
331 -- If it does exist make sure its the right one
332 --
333 if g_cache_oipl_los_rt_rec(l_index).id <> p_oipl_id then
334 --
335 -- Loop through the hash using the jump routine to check further
336 -- indexes
337 --
338 l_not_hash_found := false;
339 --
340 while not l_not_hash_found loop
341 --
342 l_index := l_index+g_hash_jump;
343 --
344 -- Check if the hash index exists, if not we can use it
345 --
346 if not g_cache_oipl_los_rt_rec.exists(l_index) then
347 --
348 -- Lets store the hash value in the index
349 --
350 raise no_data_found;
351 --
352 else
353 --
354 -- Make sure the index is the correct one
355 --
356 if g_cache_oipl_los_rt_rec(l_index).id = p_oipl_id then
357 --
358 -- We have a match so the hashed value has been stored before
359 --
360 l_not_hash_found := true;
361 --
362 end if;
363 --
364 end if;
365 --
366 end loop;
367 --
368 end if;
369 --
370 end if;
371 --
372 -- If p_old_val and p_new_val is set this means we are trying to retrieve
373 -- the correct rate for the calculated value.
374 -- Previously we just cached the first rate we
375 -- found since we needed the determination code, the correct age,los code,etc
376 -- By killing the cache and forcing the value to be removed we cache the
377 -- correct rate profile for the case we need.
378 --
379 if p_old_val is not null and p_new_val is not null then
380 --
381 raise no_data_found;
382 --
383 end if;
384 --
385 p_rec := g_cache_oipl_los_rt_rec(l_index);
386 --
387 -- hr_utility.set_location ('Leaving '||l_package,10);
388 --
389 exception
390 --
391 when no_data_found then
392 --
393 -- The record has not been cached yet so lets cache it
394 --
395 open c1;
396 --
397 fetch c1 into g_cache_oipl_los_rt_rec(l_index);
398 if p_old_val is null and p_new_val is null then
399 --
400 if c1%notfound then
401 --
402 g_cache_oipl_los_rt_rec(l_index).id := p_oipl_id;
403 g_cache_oipl_los_rt_rec(l_index).exist := 'N';
404 --
405 end if;
406 --
407 end if;
408 --
409 p_rec := g_cache_oipl_los_rt_rec(l_index);
410 --
411 close c1;
412 --
413 end get_los_oipl_rate;
414 --
415 procedure get_los_rate
416 (p_pl_id in number,
417 p_oipl_id in number,
418 p_old_val in number default null,
419 p_new_val in number default null,
420 p_business_group_id in number,
421 p_effective_date in date,
422 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
423 --
424 l_package varchar2(80) := g_package||'.get_los_rate';
425 --
426 begin
427 --
428 -- hr_utility.set_location ('Entering '||l_package,10);
429 --
430 -- Derive which data type we are dealing with
431 --
432 if p_pl_id is not null then
433 --
434 get_los_pl_rate(p_pl_id => p_pl_id,
435 p_old_val => p_old_val,
436 p_new_val => p_new_val,
437 p_business_group_id => p_business_group_id,
438 p_effective_date => p_effective_date,
439 p_rec => p_rec);
440 --
441 elsif p_oipl_id is not null then
442 --
443 get_los_oipl_rate(p_oipl_id => p_oipl_id,
444 p_old_val => p_old_val,
445 p_new_val => p_new_val,
446 p_business_group_id => p_business_group_id,
447 p_effective_date => p_effective_date,
448 p_rec => p_rec);
449 --
450 end if;
451 --
452 -- hr_utility.set_location ('Leaving '||l_package,10);
453 --
454 end get_los_rate;
455 --
456 procedure get_age_pl_rate
457 (p_pl_id in number,
458 p_old_val in number default null,
459 p_new_val in number default null,
460 p_business_group_id in number,
461 p_effective_date in date,
462 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
463 --
464 l_package varchar2(80) := g_package||'.get_age_pl_rate';
465 --
466 -- Define Cursor
467 --
468 cursor c1 is
469 select p_pl_id,
470 'Y',
471 agf.age_det_cd,
472 agf.age_to_use_cd,
473 agf.age_uom,
474 agf.age_det_rl,
475 agf.rndg_cd,
476 agf.rndg_rl,
477 agf.age_calc_rl,
478 agf.mn_age_num,
479 agf.mx_age_num
480 from ben_age_fctr agf,
481 ben_age_rt_f art,
482 ben_vrbl_rt_prfl_f vpf,
483 ben_actl_prem_vrbl_rt_f apv,
484 ben_actl_prem_f apr
485 where apr.pl_id = p_pl_id
486 and apr.business_group_id = p_business_group_id
487 and p_effective_date
488 between apr.effective_start_date
489 and apr.effective_end_date
490 and apr.actl_prem_id = apv.actl_prem_id
491 and apr.business_group_id = apv.business_group_id
492 and p_effective_date
493 between apv.effective_start_date
494 and apv.effective_end_date
495 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
496 and apv.business_group_id = vpf.business_group_id
497 and p_effective_date
498 between vpf.effective_start_date
499 and vpf.effective_end_date
500 and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
501 and vpf.business_group_id = art.business_group_id
502 and p_effective_date
503 between art.effective_start_date
504 and art.effective_end_date
505 and art.age_fctr_id = agf.age_fctr_id
506 and art.business_group_id = agf.business_group_id
507 and ((p_new_val is not null and
508 p_old_val is not null and
509 p_new_val >= nvl(agf.mn_age_num,p_new_val) and
510 -- p_new_val < ceil(nvl(agf.mx_age_num,p_new_val)+0.001))
511 p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
512 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
513 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
514 nvl(agf.mx_age_num,p_new_val)+0.000000001 ))
515 or
516 (p_new_val is not null and
517 p_old_val is not null and
518 (p_new_val < nvl(agf.mn_age_num,p_new_val) or
519 -- p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001) and
520 p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
521 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
522 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
523 nvl(agf.mx_age_num,p_new_val)+0.000000001 )) and
524 p_old_val >= nvl(agf.mn_age_num,p_old_val) and
525 -- p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001)
526 p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
527 ,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
528 nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
529 nvl(agf.mx_age_num,p_old_val)+0.000000001 ))
530 or
531 (p_new_val is null and
532 p_old_val is null));
533 --
534 --
535 l_index binary_integer;
536 l_not_hash_found boolean;
537 --
538 begin
539 --
540 -- hr_utility.set_location ('Entering '||l_package,10);
541 --
542 -- Steps to do process
543 --
544 -- 1) Try and get value from cache
545 -- 2) If can get from cache then copy to output record
546 -- 3) If can't get from cache do db hit and then
547 -- copy to cache record and then copy to output record.
548 --
549 -- Get hashed index value
550 --
551 l_index := mod(p_pl_id,g_hash_key);
552 --
553 if not g_cache_pl_age_rt_rec.exists(l_index) then
554 --
555 -- Lets store the hash value in this index
556 --
557 raise no_data_found;
558 --
559 else
560 --
561 -- If it does exist make sure its the right one
562 --
563 if g_cache_pl_age_rt_rec(l_index).id <> p_pl_id then
564 --
565 -- Loop through the hash using the jump routine to check further
566 -- indexes
567 --
568 l_not_hash_found := false;
569 --
570 while not l_not_hash_found loop
571 --
572 l_index := l_index+g_hash_jump;
573 --
574 -- Check if the hash index exists, if not we can use it
575 --
576 if not g_cache_pl_age_rt_rec.exists(l_index) then
577 --
578 -- Lets store the hash value in the index
579 --
580 raise no_data_found;
581 --
582 else
583 --
584 -- Make sure the index is the correct one
585 --
586 if g_cache_pl_age_rt_rec(l_index).id = p_pl_id then
587 --
588 -- We have a match so the hashed value has been stored before
589 --
590 l_not_hash_found := true;
591 --
592 end if;
593 --
594 end if;
595 --
596 end loop;
597 --
598 end if;
599 --
600 end if;
601 --
602 -- If p_old_val ind p_new_val is set this means we are trying to retrieve
603 -- the correct rate for the calculated value.
604 -- Previously we just cached the first rate we
605 -- found since we needed the determination code, the correct age,los code,etc
606 -- By killing the cache and forcing the value to be removed we cache the
607 -- correct rate profile for the case we need.
608 --
609 if p_old_val is not null and p_new_val is not null then
610 --
611 raise no_data_found;
612 --
613 end if;
614 --
615 p_rec := g_cache_pl_age_rt_rec(l_index);
616 --
617 -- hr_utility.set_location ('Leaving '||l_package,10);
618 --
619 exception
620 --
621 when no_data_found then
622 --
623 -- The record has not been cached yet so lets cache it
624 --
625 open c1;
626 --
627 fetch c1 into g_cache_pl_age_rt_rec(l_index);
628 if p_old_val is null and p_new_val is null then
629 --
630 if c1%notfound then
631 --
632 g_cache_pl_age_rt_rec(l_index).id := p_pl_id;
633 g_cache_pl_age_rt_rec(l_index).exist := 'N';
634 --
635 end if;
636 --
637 end if;
638 --
639 p_rec := g_cache_pl_age_rt_rec(l_index);
640 --
641 close c1;
642 --
643 end get_age_pl_rate;
644 --
645 procedure get_age_oipl_rate
646 (p_oipl_id in number,
647 p_old_val in number default null,
648 p_new_val in number default null,
649 p_business_group_id in number,
650 p_effective_date in date,
651 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
652 --
653 l_package varchar2(80) := g_package||'.get_age_oipl_rate';
654 --
655 -- Define Cursor
656 --
657 cursor c1 is
658 select p_oipl_id,
659 'Y',
660 agf.age_det_cd,
661 agf.age_to_use_cd,
662 agf.age_uom,
663 agf.age_det_rl,
664 agf.rndg_cd,
665 agf.rndg_rl,
666 agf.age_calc_rl,
667 agf.mn_age_num,
668 agf.mx_age_num
669 from ben_age_fctr agf,
670 ben_age_rt_f art,
671 ben_vrbl_rt_prfl_f vpf,
672 ben_actl_prem_vrbl_rt_f apv,
673 ben_actl_prem_f apr
674 where apr.oipl_id = p_oipl_id
675 and apr.business_group_id = p_business_group_id
676 and p_effective_date
677 between apr.effective_start_date
678 and apr.effective_end_date
679 and apr.actl_prem_id = apv.actl_prem_id
680 and apr.business_group_id = apv.business_group_id
681 and p_effective_date
682 between apv.effective_start_date
683 and apv.effective_end_date
684 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
685 and apv.business_group_id = vpf.business_group_id
686 and p_effective_date
687 between vpf.effective_start_date
688 and vpf.effective_end_date
689 and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
690 and vpf.business_group_id = art.business_group_id
691 and p_effective_date
692 between art.effective_start_date
693 and art.effective_end_date
694 and art.age_fctr_id = agf.age_fctr_id
695 and art.business_group_id = agf.business_group_id
696 and ((p_new_val is not null and
697 p_old_val is not null and
698 p_new_val >= nvl(agf.mn_age_num,p_new_val) and
699 --p_new_val < ceil(nvl(agf.mx_age_num,p_new_val)+0.001 ))
700 p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
701 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
702 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
703 nvl(agf.mx_age_num,p_new_val)+0.000000001 ))
704 or
705 (p_new_val is not null and
706 p_old_val is not null and
707 (p_new_val < nvl(agf.mn_age_num,p_new_val) or
708 -- p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001 ) and
709 p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
710 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
711 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
712 nvl(agf.mx_age_num,p_new_val)+0.000000001 )) and
713 p_old_val >= nvl(agf.mn_age_num,p_old_val) and
714 -- p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001 )
715 p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
716 ,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
717 nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
718 nvl(agf.mx_age_num,p_old_val)+0.000000001 ))
719 or
720 (p_new_val is null and
721 p_old_val is null));
722 --
723 --
724 l_index binary_integer;
725 l_not_hash_found boolean;
726 --
727 begin
728 --
729 -- hr_utility.set_location ('Entering '||l_package,10);
730 --
731 -- Steps to do process
732 --
733 -- 1) Try and get value from cache
734 -- 2) If can get from cache then copy to output record
735 -- 3) If can't get from cache do db hit and then
736 -- copy to cache record and then copy to output record.
737 --
738 -- Get hashed index value
739 --
740 l_index := mod(p_oipl_id,g_hash_key);
741 --
742 if not g_cache_oipl_age_rt_rec.exists(l_index) then
743 --
744 -- Lets store the hash value in this index
745 --
746 raise no_data_found;
747 --
748 else
749 --
750 -- If it does exist make sure its the right one
751 --
752 if g_cache_oipl_age_rt_rec(l_index).id <> p_oipl_id then
753 --
754 -- Loop through the hash using the jump routine to check further
755 -- indexes
756 --
757 l_not_hash_found := false;
758 --
759 while not l_not_hash_found loop
760 --
761 l_index := l_index+g_hash_jump;
762 --
763 -- Check if the hash index exists, if not we can use it
764 --
765 if not g_cache_oipl_age_rt_rec.exists(l_index) then
766 --
767 -- Lets store the hash value in the index
768 --
769 raise no_data_found;
770 --
771 else
772 --
773 -- Make sure the index is the correct one
774 --
775 if g_cache_oipl_age_rt_rec(l_index).id = p_oipl_id then
776 --
777 -- We have a match so the hashed value has been stored before
778 --
779 l_not_hash_found := true;
780 --
781 end if;
782 --
783 end if;
784 --
785 end loop;
786 --
787 end if;
788 --
789 end if;
790 --
791 -- If p_old_val and p_new_val is set this means we are trying to retrieve
792 -- the correct rate for the calculated value.
793 -- Previously we just cached the first rate we
794 -- found since we needed the determination code, the correct age,los code,etc
795 -- By killing the cache and forcing the value to be removed we cache the
796 -- correct rate profile for the case we need.
797 --
798 if p_old_val is not null and p_new_val is not null then
799 --
800 raise no_data_found;
801 --
802 end if;
803 --
804 p_rec := g_cache_oipl_age_rt_rec(l_index);
805 --
806 -- hr_utility.set_location ('Leaving '||l_package,10);
807 --
808 exception
809 --
810 when no_data_found then
811 --
812 -- The record has not been cached yet so lets cache it
813 --
814 open c1;
815 --
816 fetch c1 into g_cache_oipl_age_rt_rec(l_index);
817 if p_old_val is null and p_new_val is null then
818 --
819 if c1%notfound then
820 --
821 g_cache_oipl_age_rt_rec(l_index).id := p_oipl_id;
822 g_cache_oipl_age_rt_rec(l_index).exist := 'N';
823 --
824 end if;
825 --
826 end if;
827 --
828 p_rec := g_cache_oipl_age_rt_rec(l_index);
829 --
830 close c1;
831 --
832 end get_age_oipl_rate;
833 --
834 procedure get_age_rate
835 (p_pl_id in number,
836 p_oipl_id in number,
837 p_old_val in number default null,
838 p_new_val in number default null,
839 p_business_group_id in number,
840 p_effective_date in date,
841 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
842 --
843 l_package varchar2(80) := g_package||'.get_age_rate';
844 --
845 begin
846 --
847 -- hr_utility.set_location ('Entering '||l_package,10);
848 --
849 -- Derive which data type we are dealing with
850 --
851 if p_pl_id is not null then
852 --
853 get_age_pl_rate(p_pl_id => p_pl_id,
854 p_old_val => p_old_val,
855 p_new_val => p_new_val,
856 p_business_group_id => p_business_group_id,
857 p_effective_date => p_effective_date,
858 p_rec => p_rec);
859 --
860 elsif p_oipl_id is not null then
861 --
862 get_age_oipl_rate(p_oipl_id => p_oipl_id,
863 p_old_val => p_old_val,
864 p_new_val => p_new_val,
865 p_business_group_id => p_business_group_id,
866 p_effective_date => p_effective_date,
867 p_rec => p_rec);
868 --
869 end if;
870 --
871 -- hr_utility.set_location ('Leaving '||l_package,10);
872 --
873 end get_age_rate;
874 --
875 procedure get_comp_pl_rate
876 (p_pl_id in number,
877 p_old_val in number default null,
878 p_new_val in number default null,
879 p_business_group_id in number,
880 p_effective_date in date,
881 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
882 --
883 l_package varchar2(80) := g_package||'.get_comp_pl_rate';
884 --
885 -- Define Cursor
886 --
887 cursor c1 is
888 select p_pl_id,
889 'Y',
890 clf.comp_lvl_uom,
891 clf.comp_src_cd,
892 clf.comp_lvl_det_cd,
893 clf.comp_lvl_det_rl,
894 clf.rndg_cd,
895 clf.rndg_rl,
896 clf.mn_comp_val,
897 clf.mx_comp_val,
898 clf.bnfts_bal_id,
899 clf.defined_balance_id,
900 clf.sttd_sal_prdcty_cd,
901 clf.comp_lvl_fctr_id,
902 clf.comp_calc_rl
903 from ben_comp_lvl_fctr clf,
904 ben_comp_lvl_rt_f clr,
905 ben_vrbl_rt_prfl_f vpf,
906 ben_actl_prem_vrbl_rt_f apv,
907 ben_actl_prem_f apr
908 where apr.pl_id = p_pl_id
909 and apr.business_group_id = p_business_group_id
910 and p_effective_date
911 between apr.effective_start_date
912 and apr.effective_end_date
913 and apr.actl_prem_id = apv.actl_prem_id
914 and apr.business_group_id = apv.business_group_id
915 and p_effective_date
916 between apv.effective_start_date
917 and apv.effective_end_date
918 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
919 and apv.business_group_id = vpf.business_group_id
920 and p_effective_date
921 between vpf.effective_start_date
922 and vpf.effective_end_date
923 and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
924 and vpf.business_group_id = clr.business_group_id
925 and p_effective_date
926 between clr.effective_start_date
927 and clr.effective_end_date
928 and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
929 and clr.business_group_id = clf.business_group_id
930 and ((p_new_val is not null and
931 p_old_val is not null and
932 /**
933 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
934 p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+ 0.001) )
935 or
936 (p_new_val is not null and
937 p_old_val is not null and
938 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
939 p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+ 0.001) and
940 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
941 p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+ 0.001)
942 */
943 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
944 -- p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+ 0.001 ) )
945 p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
946 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
947 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
948 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ))
949 or
950 (p_new_val is not null and
951 p_old_val is not null and
952 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
953 -- p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+ 0.001 ) and
954 p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
955 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
956 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
957 nvl(clf.mx_comp_val,p_new_val)+0.000000001 )) and
958 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
959 -- p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+ 0.001 )
960 p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
961 ,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
962 nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
963 nvl(clf.mx_comp_val,p_old_val)+0.000000001 ))
964 or
965 (p_new_val is null and
966 p_old_val is null));
967 --
968 --
969 l_index binary_integer;
970 l_not_hash_found boolean;
971 --
972 begin
973 --
974 -- hr_utility.set_location ('Entering '||l_package,10);
975 --
976 -- Steps to do process
977 --
978 -- 1) Try and get value from cache
979 -- 2) If can get from cache then copy to output record
980 -- 3) If can't get from cache do db hit and then
981 -- copy to cache record and then copy to output record.
982 --
983 -- Get hashed index value
984 --
985 l_index := mod(p_pl_id,g_hash_key);
986 --
987 if not g_cache_pl_clf_rt_rec.exists(l_index) then
988 --
989 -- Lets store the hash value in this index
990 --
991 raise no_data_found;
992 --
993 else
994 --
995 -- If it does exist make sure its the right one
996 --
997 if g_cache_pl_clf_rt_rec(l_index).id <> p_pl_id then
998 --
999 -- Loop through the hash using the jump routine to check further
1000 -- indexes
1001 --
1002 l_not_hash_found := false;
1003 --
1004 while not l_not_hash_found loop
1005 --
1006 l_index := l_index+g_hash_jump;
1007 --
1008 -- Check if the hash index exists, if not we can use it
1009 --
1010 if not g_cache_pl_clf_rt_rec.exists(l_index) then
1011 --
1012 -- Lets store the hash value in the index
1013 --
1014 raise no_data_found;
1015 --
1016 else
1017 --
1018 -- Make sure the index is the correct one
1019 --
1020 if g_cache_pl_clf_rt_rec(l_index).id = p_pl_id then
1021 --
1022 -- We have a match so the hashed value has been stored before
1023 --
1024 l_not_hash_found := true;
1025 --
1026 end if;
1027 --
1028 end if;
1029 --
1030 end loop;
1031 --
1032 end if;
1033 --
1034 end if;
1035 --
1036 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1037 -- the correct rate for the calculated value.
1038 -- Previously we just cached the first rate we
1039 -- found since we needed the determination code, the correct age,los code,etc
1040 -- By killing the cache and forcing the value to be removed we cache the
1041 -- correct rate profile for the case we need.
1042 --
1043 if p_old_val is not null and p_new_val is not null then
1044 --
1045 raise no_data_found;
1046 --
1047 end if;
1048 --
1049 p_rec := g_cache_pl_clf_rt_rec(l_index);
1050 --
1051 -- hr_utility.set_location ('Leaving '||l_package,10);
1052 --
1053 exception
1054 --
1055 when no_data_found then
1056 --
1057 -- The record has not been cached yet so lets cache it
1058 --
1059 open c1;
1060 --
1061 fetch c1 into g_cache_pl_clf_rt_rec(l_index);
1062 if p_old_val is null and p_new_val is null then
1063 --
1064 if c1%notfound then
1065 --
1066 g_cache_pl_clf_rt_rec(l_index).id := p_pl_id;
1067 g_cache_pl_clf_rt_rec(l_index).exist := 'N';
1068 --
1069 end if;
1070 --
1071 end if;
1072 --
1073 p_rec := g_cache_pl_clf_rt_rec(l_index);
1074 --
1075 close c1;
1076 --
1077 end get_comp_pl_rate;
1078 --
1079 procedure get_comp_oipl_rate
1080 (p_oipl_id in number,
1081 p_old_val in number default null,
1082 p_new_val in number default null,
1083 p_business_group_id in number,
1084 p_effective_date in date,
1085 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
1086 --
1087 l_package varchar2(80) := g_package||'.get_comp_oipl_rate';
1088 --
1089 -- Define Cursor
1090 --
1091 cursor c1 is
1092 select p_oipl_id,
1093 'Y',
1094 clf.comp_lvl_uom,
1095 clf.comp_src_cd,
1096 clf.comp_lvl_det_cd,
1097 clf.comp_lvl_det_rl,
1098 clf.rndg_cd,
1099 clf.rndg_rl,
1100 clf.mn_comp_val,
1101 clf.mx_comp_val,
1102 clf.bnfts_bal_id,
1103 clf.defined_balance_id,
1104 clf.sttd_sal_prdcty_cd,
1105 clf.comp_lvl_fctr_id,
1106 clf.comp_calc_rl
1107 from ben_comp_lvl_fctr clf,
1108 ben_comp_lvl_rt_f clr,
1109 ben_vrbl_rt_prfl_f vpf,
1110 ben_actl_prem_vrbl_rt_f apv,
1111 ben_actl_prem_f apr
1112 where apr.oipl_id = p_oipl_id
1113 and apr.business_group_id = p_business_group_id
1114 and p_effective_date
1115 between apr.effective_start_date
1116 and apr.effective_end_date
1117 and apr.actl_prem_id = apv.actl_prem_id
1118 and apr.business_group_id = apv.business_group_id
1119 and p_effective_date
1120 between apv.effective_start_date
1121 and apv.effective_end_date
1122 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1123 and apv.business_group_id = vpf.business_group_id
1124 and p_effective_date
1125 between vpf.effective_start_date
1126 and vpf.effective_end_date
1127 and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
1128 and vpf.business_group_id = clr.business_group_id
1129 and p_effective_date
1130 between clr.effective_start_date
1131 and clr.effective_end_date
1132 and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
1133 and clr.business_group_id = clf.business_group_id
1134 and ((p_new_val is not null and
1135 p_old_val is not null and
1136 /**
1137 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
1138 p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+0.001) )
1139 or
1140 (p_new_val is not null and
1141 p_old_val is not null and
1142 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
1143 p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+0.001) and
1144 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
1145 p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+0.001)
1146 */
1147 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
1148 -- p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+ 0.001 ) )
1149 p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1150 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1151 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1152 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ))
1153 or
1154 (p_new_val is not null and
1155 p_old_val is not null and
1156 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
1157 -- p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+ 0.001 ) and
1158 p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1159 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1160 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1161 nvl(clf.mx_comp_val,p_new_val)+0.000000001 )) and
1162 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
1163 -- p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+ 0.001 )
1164 p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
1165 ,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
1166 nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
1167 nvl(clf.mx_comp_val,p_old_val)+0.000000001 ))
1168 or
1169 (p_new_val is null and
1170 p_old_val is null));
1171 --
1172 --
1173 l_index binary_integer;
1174 l_not_hash_found boolean;
1175 --
1176 begin
1177 --
1178 -- hr_utility.set_location ('Entering '||l_package,10);
1179 --
1180 -- Steps to do process
1181 --
1182 -- 1) Try and get value from cache
1183 -- 2) If can get from cache then copy to output record
1184 -- 3) If can't get from cache do db hit and then
1185 -- copy to cache record and then copy to output record.
1186 --
1187 -- Get hashed index value
1188 --
1189 l_index := mod(p_oipl_id,g_hash_key);
1190 --
1191 if not g_cache_oipl_clf_rt_rec.exists(l_index) then
1192 --
1193 -- Lets store the hash value in this index
1194 --
1195 raise no_data_found;
1196 --
1197 else
1198 --
1199 -- If it does exist make sure its the right one
1200 --
1201 if g_cache_oipl_clf_rt_rec(l_index).id <> p_oipl_id then
1202 --
1203 -- Loop through the hash using the jump routine to check further
1204 -- indexes
1205 --
1206 l_not_hash_found := false;
1207 --
1208 while not l_not_hash_found loop
1209 --
1210 l_index := l_index+g_hash_jump;
1211 --
1212 -- Check if the hash index exists, if not we can use it
1213 --
1214 if not g_cache_oipl_clf_rt_rec.exists(l_index) then
1215 --
1216 -- Lets store the hash value in the index
1217 --
1218 raise no_data_found;
1219 --
1220 else
1221 --
1222 -- Make sure the index is the correct one
1223 --
1224 if g_cache_oipl_clf_rt_rec(l_index).id = p_oipl_id then
1225 --
1226 -- We have a match so the hashed value has been stored before
1227 --
1228 l_not_hash_found := true;
1229 --
1230 end if;
1231 --
1232 end if;
1233 --
1234 end loop;
1235 --
1236 end if;
1237 --
1238 end if;
1239 --
1240 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1241 -- the correct rate for the calculated value.
1242 -- Previously we just cached the first rate we
1243 -- found since we needed the determination code, the correct age,los code,etc
1244 -- By killing the cache and forcing the value to be removed we cache the
1245 -- correct rate profile for the case we need.
1246 --
1247 if p_old_val is not null and p_new_val is not null then
1248 --
1249 raise no_data_found;
1250 --
1251 end if;
1252 --
1253 p_rec := g_cache_oipl_clf_rt_rec(l_index);
1254 --
1255 -- hr_utility.set_location ('Leaving '||l_package,10);
1256 --
1257 exception
1258 --
1259 when no_data_found then
1260 --
1261 -- The record has not been cached yet so lets cache it
1262 --
1263 open c1;
1264 --
1265 fetch c1 into g_cache_oipl_clf_rt_rec(l_index);
1266 if p_old_val is null and p_new_val is null then
1267 --
1268 if c1%notfound then
1269 --
1270 g_cache_oipl_clf_rt_rec(l_index).id := p_oipl_id;
1271 g_cache_oipl_clf_rt_rec(l_index).exist := 'N';
1272 --
1273 end if;
1274 --
1275 end if;
1276 --
1277 p_rec := g_cache_oipl_clf_rt_rec(l_index);
1278 --
1279 close c1;
1280 --
1281 end get_comp_oipl_rate;
1282 --
1283 procedure get_comp_rate
1284 (p_pl_id in number,
1285 p_oipl_id in number,
1286 p_old_val in number default null,
1287 p_new_val in number default null,
1288 p_business_group_id in number,
1289 p_effective_date in date,
1290 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
1291 --
1292 l_package varchar2(80) := g_package||'.get_comp_rate';
1293 --
1294 begin
1295 --
1296 -- hr_utility.set_location ('Entering '||l_package,10);
1297 --
1298 -- Derive which data type we are dealing with
1299 --
1300 if p_pl_id is not null then
1301 --
1302 get_comp_pl_rate(p_pl_id => p_pl_id,
1303 p_old_val => p_old_val,
1304 p_new_val => p_new_val,
1305 p_business_group_id => p_business_group_id,
1306 p_effective_date => p_effective_date,
1307 p_rec => p_rec);
1308 --
1309 elsif p_oipl_id is not null then
1310 --
1311 get_comp_oipl_rate(p_oipl_id => p_oipl_id,
1312 p_old_val => p_old_val,
1313 p_new_val => p_new_val,
1314 p_business_group_id => p_business_group_id,
1315 p_effective_date => p_effective_date,
1316 p_rec => p_rec);
1317 --
1318 end if;
1319 --
1320 -- hr_utility.set_location ('Leaving '||l_package,10);
1321 --
1322 end get_comp_rate;
1323 --
1324 procedure get_comb_pl_rate
1325 (p_pl_id in number,
1326 p_old_val in number default null,
1327 p_new_val in number default null,
1328 p_business_group_id in number,
1329 p_effective_date in date,
1330 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
1331 --
1332 l_package varchar2(80) := g_package||'.get_comb_pl_rate';
1333 --
1334 -- Define Cursor
1335 --
1336 cursor c1 is
1337 select p_pl_id,
1338 'Y',
1339 cla.los_fctr_id,
1340 cla.age_fctr_id,
1341 cla.cmbnd_min_val,
1342 cla.cmbnd_max_val
1343 from ben_cmbn_age_los_fctr cla,
1344 ben_cmbn_age_los_rt_f cmr,
1345 ben_vrbl_rt_prfl_f vpf,
1346 ben_actl_prem_vrbl_rt_f apv,
1347 ben_actl_prem_f apr
1348 where apr.pl_id = p_pl_id
1349 and apr.business_group_id = p_business_group_id
1350 and p_effective_date
1351 between apr.effective_start_date
1352 and apr.effective_end_date
1353 and apr.actl_prem_id = apv.actl_prem_id
1354 and apr.business_group_id = apv.business_group_id
1355 and p_effective_date
1356 between apv.effective_start_date
1357 and apv.effective_end_date
1358 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1359 and apv.business_group_id = vpf.business_group_id
1360 and p_effective_date
1361 between vpf.effective_start_date
1362 and vpf.effective_end_date
1363 and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
1364 and vpf.business_group_id = cmr.business_group_id
1365 and p_effective_date
1366 between cmr.effective_start_date
1367 and cmr.effective_end_date
1368 and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
1369 and cmr.business_group_id = cla.business_group_id
1370 and ((p_new_val is not null and
1371 p_old_val is not null and
1372 /**
1373 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
1374 p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+0.001) )
1375 or
1376 (p_new_val is not null and
1377 p_old_val is not null and
1378 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
1379 p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+0.001) and
1380 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
1381 p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+0.001)
1382 */
1383 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
1384 -- p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+ 0.001 ) )
1385 p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
1386 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
1387 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
1388 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ))
1389 or
1390 (p_new_val is not null and
1391 p_old_val is not null and
1392 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
1393 -- p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+ 0.001 ) and
1394 p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
1395 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
1396 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
1397 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 )) and
1398 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
1399 -- p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+ 0.001 )
1400 p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
1401 ,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
1402 nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
1403 nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ))
1404 or
1405 (p_new_val is null and
1406 p_old_val is null));
1407 --
1408 --
1409 l_index binary_integer;
1410 l_not_hash_found boolean;
1411 --
1412 begin
1413 --
1414 -- hr_utility.set_location ('Entering '||l_package,10);
1415 --
1416 -- Steps to do process
1417 --
1418 -- 1) Try and get value from cache
1419 -- 2) If can get from cache then copy to output record
1420 -- 3) If can't get from cache do db hit and then
1421 -- copy to cache record and then copy to output record.
1422 --
1423 -- Get hashed index value
1424 --
1425 l_index := mod(p_pl_id,g_hash_key);
1426 --
1427 if not g_cache_pl_cla_rt_rec.exists(l_index) then
1428 --
1429 -- Lets store the hash value in this index
1430 --
1431 raise no_data_found;
1432 --
1433 else
1434 --
1435 -- If it does exist make sure its the right one
1436 --
1437 if g_cache_pl_cla_rt_rec(l_index).id <> p_pl_id then
1438 --
1439 -- Loop through the hash using the jump routine to check further
1440 -- indexes
1441 --
1442 l_not_hash_found := false;
1443 --
1444 while not l_not_hash_found loop
1445 --
1446 l_index := l_index+g_hash_jump;
1447 --
1448 -- Check if the hash index exists, if not we can use it
1449 --
1450 if not g_cache_pl_cla_rt_rec.exists(l_index) then
1451 --
1452 -- Lets store the hash value in the index
1453 --
1454 raise no_data_found;
1455 --
1456 else
1457 --
1458 -- Make sure the index is the correct one
1459 --
1460 if g_cache_pl_cla_rt_rec(l_index).id = p_pl_id then
1461 --
1462 -- We have a match so the hashed value has been stored before
1463 --
1464 l_not_hash_found := true;
1465 --
1466 end if;
1467 --
1468 end if;
1469 --
1470 end loop;
1471 --
1472 end if;
1473 --
1474 end if;
1475 --
1476 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1477 -- the correct rate for the calculated value.
1478 -- Previously we just cached the first rate we
1479 -- found since we needed the determination code, the correct age,los code,etc
1480 -- By killing the cache and forcing the value to be removed we cache the
1481 -- correct rate profile for the case we need.
1482 --
1483 if p_old_val is not null and p_new_val is not null then
1484 --
1485 raise no_data_found;
1486 --
1487 end if;
1488 --
1489 p_rec := g_cache_pl_cla_rt_rec(l_index);
1490 --
1491 -- hr_utility.set_location ('Leaving '||l_package,10);
1492 --
1493 exception
1494 --
1495 when no_data_found then
1496 --
1497 -- The record has not been cached yet so lets cache it
1498 --
1499 open c1;
1500 --
1501 fetch c1 into g_cache_pl_cla_rt_rec(l_index);
1502 if p_old_val is null and p_new_val is null then
1503 --
1504 if c1%notfound then
1505 --
1506 g_cache_pl_cla_rt_rec(l_index).id := p_pl_id;
1507 g_cache_pl_cla_rt_rec(l_index).exist := 'N';
1508 --
1509 end if;
1510 --
1511 end if;
1512 --
1513 p_rec := g_cache_pl_cla_rt_rec(l_index);
1514 --
1515 close c1;
1516 --
1517 end get_comb_pl_rate;
1518 --
1519 procedure get_comb_oipl_rate
1520 (p_oipl_id in number,
1521 p_old_val in number default null,
1522 p_new_val in number default null,
1523 p_business_group_id in number,
1524 p_effective_date in date,
1525 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
1526 --
1527 l_package varchar2(80) := g_package||'.get_comb_oipl_rate';
1528 --
1529 -- Define Cursor
1530 --
1531 cursor c1 is
1532 select p_oipl_id,
1533 'Y',
1534 cla.los_fctr_id,
1535 cla.age_fctr_id,
1536 cla.cmbnd_min_val,
1537 cla.cmbnd_max_val
1538 from ben_cmbn_age_los_fctr cla,
1539 ben_cmbn_age_los_rt_f cmr,
1540 ben_vrbl_rt_prfl_f vpf,
1541 ben_actl_prem_vrbl_rt_f apv,
1542 ben_actl_prem_f apr
1543 where apr.oipl_id = p_oipl_id
1544 and apr.business_group_id = p_business_group_id
1545 and p_effective_date
1546 between apr.effective_start_date
1547 and apr.effective_end_date
1548 and apr.actl_prem_id = apv.actl_prem_id
1549 and apr.business_group_id = apv.business_group_id
1550 and p_effective_date
1551 between apv.effective_start_date
1552 and apv.effective_end_date
1553 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1554 and apv.business_group_id = vpf.business_group_id
1555 and p_effective_date
1556 between vpf.effective_start_date
1557 and vpf.effective_end_date
1558 and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
1559 and vpf.business_group_id = cmr.business_group_id
1560 and p_effective_date
1561 between cmr.effective_start_date
1562 and cmr.effective_end_date
1563 and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
1564 and cmr.business_group_id = cla.business_group_id
1565 and ((p_new_val is not null and
1566 p_old_val is not null and
1567 /**
1568 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
1569 p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+0.001) )
1570 or
1571 (p_new_val is not null and
1572 p_old_val is not null and
1573 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
1574 p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+0.001) and
1575 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
1576 p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+0.001)
1577 */
1578 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
1579 -- p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+ 0.001 ) )
1580 p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
1581 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
1582 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
1583 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ))
1584 or
1585 (p_new_val is not null and
1586 p_old_val is not null and
1587 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
1588 -- p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+ 0.001 ) and
1589 p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
1590 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
1591 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
1592 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 )) and
1593 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
1594 -- p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+ 0.001 )
1595 p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
1596 ,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
1597 nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
1598 nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ))
1599 or
1600 (p_new_val is null and
1601 p_old_val is null));
1602 --
1603 --
1604 l_index binary_integer;
1605 l_not_hash_found boolean;
1606 --
1607 begin
1608 --
1609 -- hr_utility.set_location ('Entering '||l_package,10);
1610 --
1611 -- Steps to do process
1612 --
1613 -- 1) Try and get value from cache
1614 -- 2) If can get from cache then copy to output record
1615 -- 3) If can't get from cache do db hit and then
1616 -- copy to cache record and then copy to output record.
1617 --
1618 -- Get hashed index value
1619 --
1620 l_index := mod(p_oipl_id,g_hash_key);
1621 --
1622 if not g_cache_oipl_cla_rt_rec.exists(l_index) then
1623 --
1624 -- Lets store the hash value in this index
1625 --
1626 raise no_data_found;
1627 --
1628 else
1629 --
1630 -- If it does exist make sure its the right one
1631 --
1632 if g_cache_oipl_cla_rt_rec(l_index).id <> p_oipl_id then
1633 --
1634 -- Loop through the hash using the jump routine to check further
1635 -- indexes
1636 --
1637 l_not_hash_found := false;
1638 --
1639 while not l_not_hash_found loop
1640 --
1641 l_index := l_index+g_hash_jump;
1642 --
1643 -- Check if the hash index exists, if not we can use it
1644 --
1645 if not g_cache_oipl_cla_rt_rec.exists(l_index) then
1646 --
1647 -- Lets store the hash value in the index
1648 --
1649 raise no_data_found;
1650 --
1651 else
1652 --
1653 -- Make sure the index is the correct one
1654 --
1655 if g_cache_oipl_cla_rt_rec(l_index).id = p_oipl_id then
1656 --
1657 -- We have a match so the hashed value has been stored before
1658 --
1659 l_not_hash_found := true;
1660 --
1661 end if;
1662 --
1663 end if;
1664 --
1665 end loop;
1666 --
1667 end if;
1668 --
1669 end if;
1670 --
1671 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1672 -- the correct rate for the calculated value.
1673 -- Previously we just cached the first rate we
1674 -- found since we needed the determination code, the correct age,los code,etc
1675 -- By killing the cache and forcing the value to be removed we cache the
1676 -- correct rate profile for the case we need.
1677 --
1678 if p_old_val is not null and p_new_val is not null then
1679 --
1680 raise no_data_found;
1681 --
1682 end if;
1683 --
1684 p_rec := g_cache_oipl_cla_rt_rec(l_index);
1685 --
1686 -- hr_utility.set_location ('Leaving '||l_package,10);
1687 --
1688 exception
1689 --
1690 when no_data_found then
1691 --
1692 -- The record has not been cached yet so lets cache it
1693 --
1694 open c1;
1695 --
1696 fetch c1 into g_cache_oipl_cla_rt_rec(l_index);
1697 if p_old_val is null and p_new_val is null then
1698 --
1699 if c1%notfound then
1700 --
1701 g_cache_oipl_cla_rt_rec(l_index).id := p_oipl_id;
1702 g_cache_oipl_cla_rt_rec(l_index).exist := 'N';
1703 --
1704 end if;
1705 --
1706 end if;
1707 --
1708 p_rec := g_cache_oipl_cla_rt_rec(l_index);
1709 --
1710 close c1;
1711 --
1712 end get_comb_oipl_rate;
1713 --
1714 procedure get_comb_rate
1715 (p_pl_id in number,
1716 p_oipl_id in number,
1717 p_old_val in number default null,
1718 p_new_val in number default null,
1719 p_business_group_id in number,
1720 p_effective_date in date,
1721 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
1722 --
1723 l_package varchar2(80) := g_package||'.get_comb_rate';
1724 --
1725 begin
1726 --
1727 -- hr_utility.set_location ('Entering '||l_package,10);
1728 --
1729 -- Derive which data type we are dealing with
1730 --
1731 if p_pl_id is not null then
1732 --
1733 get_comb_pl_rate(p_pl_id => p_pl_id,
1734 p_old_val => p_old_val,
1735 p_new_val => p_new_val,
1736 p_business_group_id => p_business_group_id,
1737 p_effective_date => p_effective_date,
1738 p_rec => p_rec);
1739 --
1740 elsif p_oipl_id is not null then
1741 --
1742 get_comb_oipl_rate(p_oipl_id => p_oipl_id,
1743 p_old_val => p_old_val,
1744 p_new_val => p_new_val,
1745 p_business_group_id => p_business_group_id,
1746 p_effective_date => p_effective_date,
1747 p_rec => p_rec);
1748 --
1749 end if;
1750 --
1751 -- hr_utility.set_location ('Leaving '||l_package,10);
1752 --
1753 end get_comb_rate;
1754 --
1755 procedure get_pct_pl_rate
1756 (p_pl_id in number,
1757 p_old_val in number default null,
1758 p_new_val in number default null,
1759 p_business_group_id in number,
1760 p_effective_date in date,
1761 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
1762 --
1763 l_package varchar2(80) := g_package||'.get_pct_pl_rate';
1764 --
1765 -- Define Cursor
1766 --
1767 cursor c1 is
1768 select p_pl_id,
1769 'Y',
1770 pff.use_prmry_asnt_only_flag,
1771 pff.use_sum_of_all_asnts_flag,
1772 pff.rndg_cd,
1773 pff.rndg_rl,
1774 pff.mn_pct_val,
1775 pff.mx_pct_val
1776 from ben_pct_fl_tm_fctr pff,
1777 ben_pct_fl_tm_rt_f pfr,
1778 ben_vrbl_rt_prfl_f vpf,
1779 ben_actl_prem_vrbl_rt_f apv,
1780 ben_actl_prem_f apr
1781 where apr.pl_id = p_pl_id
1782 and apr.business_group_id = p_business_group_id
1783 and p_effective_date
1784 between apr.effective_start_date
1785 and apr.effective_end_date
1786 and apr.actl_prem_id = apv.actl_prem_id
1787 and apr.business_group_id = apv.business_group_id
1788 and p_effective_date
1789 between apv.effective_start_date
1790 and apv.effective_end_date
1791 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1792 and apv.business_group_id = vpf.business_group_id
1793 and p_effective_date
1794 between vpf.effective_start_date
1795 and vpf.effective_end_date
1796 and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
1797 and vpf.business_group_id = pfr.business_group_id
1798 and p_effective_date
1799 between pfr.effective_start_date
1800 and pfr.effective_end_date
1801 and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
1802 and pfr.business_group_id = pff.business_group_id
1803 and ((p_new_val is not null and
1804 p_old_val is not null and
1805 p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
1806 (p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
1807 or
1808 (p_new_val is not null and
1809 p_old_val is not null and
1810 (p_new_val < nvl(pff.mn_pct_val,p_new_val) or
1811 (p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
1812 p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
1813 (p_old_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
1814 or
1815 (p_new_val is null and
1816 p_old_val is null));
1817 --
1818 --
1819 l_index binary_integer;
1820 l_not_hash_found boolean;
1821 --
1822 begin
1823 --
1824 -- hr_utility.set_location ('Entering '||l_package,10);
1825 --
1826 -- Steps to do process
1827 --
1828 -- 1) Try and get value from cache
1829 -- 2) If can get from cache then copy to output record
1830 -- 3) If can't get from cache do db hit and then
1831 -- copy to cache record and then copy to output record.
1832 --
1833 -- Get hashed index value
1834 --
1835 l_index := mod(p_pl_id,g_hash_key);
1836 --
1837 if not g_cache_pl_pff_rt_rec.exists(l_index) then
1838 --
1839 -- Lets store the hash value in this index
1840 --
1841 raise no_data_found;
1842 --
1843 else
1844 --
1845 -- If it does exist make sure its the right one
1846 --
1847 if g_cache_pl_pff_rt_rec(l_index).id <> p_pl_id then
1848 --
1849 -- Loop through the hash using the jump routine to check further
1850 -- indexes
1851 --
1852 l_not_hash_found := false;
1853 --
1854 while not l_not_hash_found loop
1855 --
1856 l_index := l_index+g_hash_jump;
1857 --
1858 -- Check if the hash index exists, if not we can use it
1859 --
1860 if not g_cache_pl_pff_rt_rec.exists(l_index) then
1861 --
1862 -- Lets store the hash value in the index
1863 --
1864 raise no_data_found;
1865 --
1866 else
1867 --
1868 -- Make sure the index is the correct one
1869 --
1870 if g_cache_pl_pff_rt_rec(l_index).id = p_pl_id then
1871 --
1872 -- We have a match so the hashed value has been stored before
1873 --
1874 l_not_hash_found := true;
1875 --
1876 end if;
1877 --
1878 end if;
1879 --
1880 end loop;
1881 --
1882 end if;
1883 --
1884 end if;
1885 --
1886 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1887 -- the correct rate for the calculated value.
1888 -- Previously we just cached the first rate we
1889 -- found since we needed the determination code, the correct age,los code,etc
1890 -- By killing the cache and forcing the value to be removed we cache the
1891 -- correct rate profile for the case we need.
1892 --
1893 if p_old_val is not null and p_new_val is not null then
1894 --
1895 raise no_data_found;
1896 --
1897 end if;
1898 --
1899 p_rec := g_cache_pl_pff_rt_rec(l_index);
1900 --
1901 -- hr_utility.set_location ('Leaving '||l_package,10);
1902 --
1903 exception
1904 --
1905 when no_data_found then
1906 --
1907 -- The record has not been cached yet so lets cache it
1908 --
1909 open c1;
1910 --
1911 fetch c1 into g_cache_pl_pff_rt_rec(l_index);
1912 if p_old_val is null and p_new_val is null then
1913 --
1914 if c1%notfound then
1915 --
1916 g_cache_pl_pff_rt_rec(l_index).id := p_pl_id;
1917 g_cache_pl_pff_rt_rec(l_index).exist := 'N';
1918 --
1919 end if;
1920 --
1921 end if;
1922 --
1923 p_rec := g_cache_pl_pff_rt_rec(l_index);
1924 --
1925 close c1;
1926 --
1927 end get_pct_pl_rate;
1928 --
1929 procedure get_pct_oipl_rate
1930 (p_oipl_id in number,
1931 p_old_val in number default null,
1932 p_new_val in number default null,
1933 p_business_group_id in number,
1934 p_effective_date in date,
1935 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
1936 --
1937 l_package varchar2(80) := g_package||'.get_pct_oipl_rate';
1938 --
1939 -- Define Cursor
1940 --
1941 cursor c1 is
1942 select p_oipl_id,
1943 'Y',
1944 pff.use_prmry_asnt_only_flag,
1945 pff.use_sum_of_all_asnts_flag,
1946 pff.rndg_cd,
1947 pff.rndg_rl,
1948 pff.mn_pct_val,
1949 pff.mx_pct_val
1950 from ben_pct_fl_tm_fctr pff,
1951 ben_pct_fl_tm_rt_f pfr,
1952 ben_vrbl_rt_prfl_f vpf,
1953 ben_actl_prem_vrbl_rt_f apv,
1954 ben_actl_prem_f apr
1955 where apr.oipl_id = p_oipl_id
1956 and apr.business_group_id = p_business_group_id
1957 and p_effective_date
1958 between apr.effective_start_date
1959 and apr.effective_end_date
1960 and apr.actl_prem_id = apv.actl_prem_id
1961 and apr.business_group_id = apv.business_group_id
1962 and p_effective_date
1963 between apv.effective_start_date
1964 and apv.effective_end_date
1965 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1966 and apv.business_group_id = vpf.business_group_id
1967 and p_effective_date
1968 between vpf.effective_start_date
1969 and vpf.effective_end_date
1970 and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
1971 and vpf.business_group_id = pfr.business_group_id
1972 and p_effective_date
1973 between pfr.effective_start_date
1974 and pfr.effective_end_date
1975 and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
1976 and pfr.business_group_id = pff.business_group_id
1977 and ((p_new_val is not null and
1978 p_old_val is not null and
1979 p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
1980 (p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
1981 or
1982 (p_new_val is not null and
1983 p_old_val is not null and
1984 (p_new_val < nvl(pff.mn_pct_val,p_new_val) or
1985 (p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
1986 p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
1987 (p_old_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
1988 or
1989 (p_new_val is null and
1990 p_old_val is null));
1991 --
1992 --
1993 l_index binary_integer;
1994 l_not_hash_found boolean;
1995 --
1996 begin
1997 --
1998 -- hr_utility.set_location ('Entering '||l_package,10);
1999 --
2000 -- Steps to do process
2001 --
2002 -- 1) Try and get value from cache
2003 -- 2) If can get from cache then copy to output record
2004 -- 3) If can't get from cache do db hit and then
2005 -- copy to cache record and then copy to output record.
2006 --
2007 -- Get hashed index value
2008 --
2009 l_index := mod(p_oipl_id,g_hash_key);
2010 --
2011 if not g_cache_oipl_pff_rt_rec.exists(l_index) then
2012 --
2013 -- Lets store the hash value in this index
2014 --
2015 raise no_data_found;
2016 --
2017 else
2018 --
2019 -- If it does exist make sure its the right one
2020 --
2021 if g_cache_oipl_pff_rt_rec(l_index).id <> p_oipl_id then
2022 --
2023 -- Loop through the hash using the jump routine to check further
2024 -- indexes
2025 --
2026 l_not_hash_found := false;
2027 --
2028 while not l_not_hash_found loop
2029 --
2030 l_index := l_index+g_hash_jump;
2031 --
2032 -- Check if the hash index exists, if not we can use it
2033 --
2034 if not g_cache_oipl_pff_rt_rec.exists(l_index) then
2035 --
2036 -- Lets store the hash value in the index
2037 --
2038 raise no_data_found;
2039 --
2040 else
2041 --
2042 -- Make sure the index is the correct one
2043 --
2044 if g_cache_oipl_pff_rt_rec(l_index).id = p_oipl_id then
2045 --
2046 -- We have a match so the hashed value has been stored before
2047 --
2048 l_not_hash_found := true;
2049 --
2050 end if;
2051 --
2052 end if;
2053 --
2054 end loop;
2055 --
2056 end if;
2057 --
2058 end if;
2059 --
2060 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2061 -- the correct rate for the calculated value.
2062 -- Previously we just cached the first rate we
2063 -- found since we needed the determination code, the correct age,los code,etc
2064 -- By killing the cache and forcing the value to be removed we cache the
2065 -- correct rate profile for the case we need.
2066 --
2067 if p_old_val is not null and p_new_val is not null then
2068 --
2069 raise no_data_found;
2070 --
2071 end if;
2072 --
2073 p_rec := g_cache_oipl_pff_rt_rec(l_index);
2074 --
2075 -- hr_utility.set_location ('Leaving '||l_package,10);
2076 --
2077 exception
2078 --
2079 when no_data_found then
2080 --
2081 -- The record has not been cached yet so lets cache it
2082 --
2083 open c1;
2084 --
2085 fetch c1 into g_cache_oipl_pff_rt_rec(l_index);
2086 if p_old_val is null and p_new_val is null then
2087 --
2088 if c1%notfound then
2089 --
2090 g_cache_oipl_pff_rt_rec(l_index).id := p_oipl_id;
2091 g_cache_oipl_pff_rt_rec(l_index).exist := 'N';
2092 --
2093 end if;
2094 --
2095 end if;
2096 --
2097 p_rec := g_cache_oipl_pff_rt_rec(l_index);
2098 --
2099 close c1;
2100 --
2101 end get_pct_oipl_rate;
2102 --
2103 procedure get_pct_rate
2104 (p_pl_id in number,
2105 p_oipl_id in number,
2106 p_old_val in number default null,
2107 p_new_val in number default null,
2108 p_business_group_id in number,
2109 p_effective_date in date,
2110 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
2111 --
2112 l_package varchar2(80) := g_package||'.get_pct_rate';
2113 --
2114 begin
2115 --
2116 -- hr_utility.set_location ('Entering '||l_package,10);
2117 --
2118 -- Derive which data type we are dealing with
2119 --
2120 if p_pl_id is not null then
2121 --
2122 get_pct_pl_rate(p_pl_id => p_pl_id,
2123 p_old_val => p_old_val,
2124 p_new_val => p_new_val,
2125 p_business_group_id => p_business_group_id,
2126 p_effective_date => p_effective_date,
2127 p_rec => p_rec);
2128 --
2129 elsif p_oipl_id is not null then
2130 --
2131 get_pct_oipl_rate(p_oipl_id => p_oipl_id,
2132 p_old_val => p_old_val,
2133 p_new_val => p_new_val,
2134 p_business_group_id => p_business_group_id,
2135 p_effective_date => p_effective_date,
2136 p_rec => p_rec);
2137 --
2138 end if;
2139 --
2140 -- hr_utility.set_location ('Leaving '||l_package,10);
2141 --
2142 end get_pct_rate;
2143 --
2144 procedure get_hours_pl_rate
2145 (p_pl_id in number,
2146 p_old_val in number default null,
2147 p_new_val in number default null,
2148 p_business_group_id in number,
2149 p_effective_date in date,
2150 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
2151 --
2152 l_package varchar2(80) := g_package||'.get_hours_pl_rate';
2153 --
2154 -- Define Cursor
2155 --
2156 cursor c1 is
2157 select p_pl_id,
2158 'Y',
2159 hwf.hrs_src_cd,
2160 hwf.hrs_wkd_det_cd,
2161 hwf.hrs_wkd_det_rl,
2162 hwf.rndg_cd,
2163 hwf.rndg_rl,
2164 hwf.defined_balance_id,
2165 hwf.bnfts_bal_id,
2166 hwf.mn_hrs_num,
2167 hwf.mx_hrs_num,
2168 hwf.once_r_cntug_cd,
2169 hwf.hrs_wkd_calc_rl
2170 from ben_hrs_wkd_in_perd_fctr hwf,
2171 ben_hrs_wkd_in_perd_rt_f hwr,
2172 ben_vrbl_rt_prfl_f vpf,
2173 ben_actl_prem_vrbl_rt_f apv,
2174 ben_actl_prem_f apr
2175 where apr.pl_id = p_pl_id
2176 and apr.business_group_id = p_business_group_id
2177 and p_effective_date
2178 between apr.effective_start_date
2179 and apr.effective_end_date
2180 and apr.actl_prem_id = apv.actl_prem_id
2181 and apr.business_group_id = apv.business_group_id
2182 and p_effective_date
2183 between apv.effective_start_date
2184 and apv.effective_end_date
2185 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2186 and apv.business_group_id = vpf.business_group_id
2187 and p_effective_date
2188 between vpf.effective_start_date
2189 and vpf.effective_end_date
2190 and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
2191 and vpf.business_group_id = hwr.business_group_id
2192 and p_effective_date
2193 between hwr.effective_start_date
2194 and hwr.effective_end_date
2195 and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
2196 and hwr.business_group_id = hwf.business_group_id
2197 and ((p_new_val is not null and
2198 p_old_val is not null and
2199 /**
2200 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
2201 p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+0.001) )
2202 or
2203 (p_new_val is not null and
2204 p_old_val is not null and
2205 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
2206 p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001) and
2207 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
2208 p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+0.001)
2209 */
2210 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
2211 -- p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+ 0.001 ) )
2212 p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
2213 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
2214 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
2215 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ))
2216 or
2217 (p_new_val is not null and
2218 p_old_val is not null and
2219 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
2220 -- p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+ 0.001 ) and
2221 p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
2222 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
2223 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
2224 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 )) and
2225 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
2226 -- p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+ 0.001 )
2227 p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
2228 ,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
2229 nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
2230 nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ))
2231 or
2232 (p_new_val is null and
2233 p_old_val is null));
2234 --
2235 --
2236 l_index binary_integer;
2237 l_not_hash_found boolean;
2238 --
2239 begin
2240 --
2241 -- hr_utility.set_location ('Entering '||l_package,10);
2242 --
2243 -- Steps to do process
2244 --
2245 -- 1) Try and get value from cache
2246 -- 2) If can get from cache then copy to output record
2247 -- 3) If can't get from cache do db hit and then
2248 -- copy to cache record and then copy to output record.
2249 --
2250 -- Get hashed index value
2251 --
2252 l_index := mod(p_pl_id,g_hash_key);
2253 --
2254 if not g_cache_pl_hwf_rt_rec.exists(l_index) then
2255 --
2256 -- Lets store the hash value in this index
2257 --
2258 raise no_data_found;
2259 --
2260 else
2261 --
2262 -- If it does exist make sure its the right one
2263 --
2264 if g_cache_pl_hwf_rt_rec(l_index).id <> p_pl_id then
2265 --
2266 -- Loop through the hash using the jump routine to check further
2267 -- indexes
2268 --
2269 l_not_hash_found := false;
2270 --
2271 while not l_not_hash_found loop
2272 --
2273 l_index := l_index+g_hash_jump;
2274 --
2275 -- Check if the hash index exists, if not we can use it
2276 --
2277 if not g_cache_pl_hwf_rt_rec.exists(l_index) then
2278 --
2279 -- Lets store the hash value in the index
2280 --
2281 raise no_data_found;
2282 --
2283 else
2284 --
2285 -- Make sure the index is the correct one
2286 --
2287 if g_cache_pl_hwf_rt_rec(l_index).id = p_pl_id then
2288 --
2289 -- We have a match so the hashed value has been stored before
2290 --
2291 l_not_hash_found := true;
2292 --
2293 end if;
2294 --
2295 end if;
2296 --
2297 end loop;
2298 --
2299 end if;
2300 --
2301 end if;
2302 --
2303 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2304 -- the correct rate for the calculated value.
2305 -- Previously we just cached the first rate we
2306 -- found since we needed the determination code, the correct age,los code,etc
2307 -- By killing the cache and forcing the value to be removed we cache the
2308 -- correct rate profile for the case we need.
2309 --
2310 if p_old_val is not null and p_new_val is not null then
2311 --
2312 raise no_data_found;
2313 --
2314 end if;
2315 --
2316 p_rec := g_cache_pl_hwf_rt_rec(l_index);
2317 --
2318 -- hr_utility.set_location ('Leaving '||l_package,10);
2319 --
2320 exception
2321 --
2322 when no_data_found then
2323 --
2324 -- The record has not been cached yet so lets cache it
2325 --
2326 open c1;
2327 --
2328 fetch c1 into g_cache_pl_hwf_rt_rec(l_index);
2329 if p_old_val is null and p_new_val is null then
2330 --
2331 if c1%notfound then
2332 --
2333 g_cache_pl_hwf_rt_rec(l_index).id := p_pl_id;
2334 g_cache_pl_hwf_rt_rec(l_index).exist := 'N';
2335 --
2336 end if;
2337 --
2338 end if;
2339 --
2340 p_rec := g_cache_pl_hwf_rt_rec(l_index);
2341 --
2342 close c1;
2343 --
2344 end get_hours_pl_rate;
2345 --
2346 procedure get_hours_oipl_rate
2347 (p_oipl_id in number,
2348 p_old_val in number default null,
2349 p_new_val in number default null,
2350 p_business_group_id in number,
2351 p_effective_date in date,
2352 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
2353 --
2354 l_package varchar2(80) := g_package||'.get_hours_oipl_rate';
2355 --
2356 -- Define Cursor
2357 --
2358 cursor c1 is
2359 select p_oipl_id,
2360 'Y',
2361 hwf.hrs_src_cd,
2362 hwf.hrs_wkd_det_cd,
2363 hwf.hrs_wkd_det_rl,
2364 hwf.rndg_cd,
2365 hwf.rndg_rl,
2366 hwf.defined_balance_id,
2367 hwf.bnfts_bal_id,
2368 hwf.mn_hrs_num,
2369 hwf.mx_hrs_num,
2370 hwf.once_r_cntug_cd,
2371 hwf.hrs_wkd_calc_rl
2372 from ben_hrs_wkd_in_perd_fctr hwf,
2373 ben_hrs_wkd_in_perd_rt_f hwr,
2374 ben_vrbl_rt_prfl_f vpf,
2375 ben_actl_prem_vrbl_rt_f apv,
2376 ben_actl_prem_f apr
2377 where apr.oipl_id = p_oipl_id
2378 and apr.business_group_id = p_business_group_id
2379 and p_effective_date
2380 between apr.effective_start_date
2381 and apr.effective_end_date
2382 and apr.actl_prem_id = apv.actl_prem_id
2383 and apr.business_group_id = apv.business_group_id
2384 and p_effective_date
2385 between apv.effective_start_date
2386 and apv.effective_end_date
2387 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2388 and apv.business_group_id = vpf.business_group_id
2389 and p_effective_date
2390 between vpf.effective_start_date
2391 and vpf.effective_end_date
2392 and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
2393 and vpf.business_group_id = hwr.business_group_id
2394 and p_effective_date
2395 between hwr.effective_start_date
2396 and hwr.effective_end_date
2397 and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
2398 and hwr.business_group_id = hwf.business_group_id
2399 and ((p_new_val is not null and
2400 p_old_val is not null and
2401 /**
2402 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
2403 p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+0.001))
2404 or
2405 (p_new_val is not null and
2406 p_old_val is not null and
2407 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
2408 p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001) and
2409 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
2410 p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val)) +0.001)
2411 */
2412 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
2413 -- p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+ 0.001 ) )
2414 p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
2415 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
2416 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
2417 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ))
2418 or
2419 (p_new_val is not null and
2420 p_old_val is not null and
2421 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
2422 -- p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+ 0.001 ) and
2423 p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
2424 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
2425 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
2426 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 )) and
2427 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
2428 -- p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+ 0.001 )
2429 p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
2430 ,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
2431 nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
2432 nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ))
2433 or
2434 (p_new_val is null and
2435 p_old_val is null));
2436 --
2437 --
2438 l_index binary_integer;
2439 l_not_hash_found boolean;
2440 --
2441 begin
2442 --
2443 -- hr_utility.set_location ('Entering '||l_package,10);
2444 --
2445 -- Steps to do process
2446 --
2447 -- 1) Try and get value from cache
2448 -- 2) If can get from cache then copy to output record
2449 -- 3) If can't get from cache do db hit and then
2450 -- copy to cache record and then copy to output record.
2451 --
2452 -- Get hashed index value
2453 --
2454 l_index := mod(p_oipl_id,g_hash_key);
2455 --
2456 if not g_cache_oipl_hwf_rt_rec.exists(l_index) then
2457 --
2458 -- Lets store the hash value in this index
2459 --
2460 raise no_data_found;
2461 --
2462 else
2463 --
2464 -- If it does exist make sure its the right one
2465 --
2466 if g_cache_oipl_hwf_rt_rec(l_index).id <> p_oipl_id then
2467 --
2468 -- Loop through the hash using the jump routine to check further
2469 -- indexes
2470 --
2471 l_not_hash_found := false;
2472 --
2473 while not l_not_hash_found loop
2474 --
2475 l_index := l_index+g_hash_jump;
2476 --
2477 -- Check if the hash index exists, if not we can use it
2478 --
2479 if not g_cache_oipl_hwf_rt_rec.exists(l_index) then
2480 --
2481 -- Lets store the hash value in the index
2482 --
2483 raise no_data_found;
2484 --
2485 else
2486 --
2487 -- Make sure the index is the correct one
2488 --
2489 if g_cache_oipl_hwf_rt_rec(l_index).id = p_oipl_id then
2490 --
2491 -- We have a match so the hashed value has been stored before
2492 --
2493 l_not_hash_found := true;
2494 --
2495 end if;
2496 --
2497 end if;
2498 --
2499 end loop;
2500 --
2501 end if;
2502 --
2503 end if;
2504 --
2505 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2506 -- the correct rate for the calculated value.
2507 -- Previously we just cached the first rate we
2508 -- found since we needed the determination code, the correct age,los code,etc
2509 -- By killing the cache and forcing the value to be removed we cache the
2510 -- correct rate profile for the case we need.
2511 --
2512 if p_old_val is not null and p_new_val is not null then
2513 --
2514 raise no_data_found;
2515 --
2516 end if;
2517 --
2518 p_rec := g_cache_oipl_hwf_rt_rec(l_index);
2519 --
2520 -- hr_utility.set_location ('Leaving '||l_package,10);
2521 --
2522 exception
2523 --
2524 when no_data_found then
2525 --
2526 -- The record has not been cached yet so lets cache it
2527 --
2528 open c1;
2529 --
2530 fetch c1 into g_cache_oipl_hwf_rt_rec(l_index);
2531 if p_old_val is null and p_new_val is null then
2532 --
2533 if c1%notfound then
2534 --
2535 g_cache_oipl_hwf_rt_rec(l_index).id := p_oipl_id;
2536 g_cache_oipl_hwf_rt_rec(l_index).exist := 'N';
2537 --
2538 end if;
2539 --
2540 end if;
2541 --
2542 p_rec := g_cache_oipl_hwf_rt_rec(l_index);
2543 --
2544 close c1;
2545 --
2546 end get_hours_oipl_rate;
2547 --
2548 procedure get_hours_rate
2549 (p_pl_id in number,
2550 p_oipl_id in number,
2551 p_old_val in number default null,
2552 p_new_val in number default null,
2553 p_business_group_id in number,
2554 p_effective_date in date,
2555 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
2556 --
2557 l_package varchar2(80) := g_package||'.get_hours_rate';
2558 --
2559 begin
2560 --
2561 -- hr_utility.set_location ('Entering '||l_package,10);
2562 --
2563 -- Derive which data type we are dealing with
2564 --
2565 if p_pl_id is not null then
2566 --
2567 get_hours_pl_rate(p_pl_id => p_pl_id,
2568 p_old_val => p_old_val,
2569 p_new_val => p_new_val,
2570 p_business_group_id => p_business_group_id,
2571 p_effective_date => p_effective_date,
2572 p_rec => p_rec);
2573 --
2574 elsif p_oipl_id is not null then
2575 --
2576 get_hours_oipl_rate(p_oipl_id => p_oipl_id,
2577 p_old_val => p_old_val,
2578 p_new_val => p_new_val,
2579 p_business_group_id => p_business_group_id,
2580 p_effective_date => p_effective_date,
2581 p_rec => p_rec);
2582 --
2583 end if;
2584 --
2585 -- hr_utility.set_location ('Leaving '||l_package,10);
2586 --
2587 end get_hours_rate;
2588 --
2589 procedure clear_down_cache is
2590 --
2591 l_package varchar2(80) := g_package||'.clear_down_cache';
2592 --
2593 begin
2594 --
2595 -- hr_utility.set_location ('Entering '||l_package,10);
2596 --
2597 -- Clear down all caches
2598 --
2599 g_cache_pl_los_rt_rec.delete;
2600 g_cache_oipl_los_rt_rec.delete;
2601 g_cache_pl_age_rt_rec.delete;
2602 g_cache_oipl_age_rt_rec.delete;
2603 g_cache_pl_clf_rt_rec.delete;
2604 g_cache_oipl_clf_rt_rec.delete;
2605 g_cache_pl_cla_rt_rec.delete;
2606 g_cache_oipl_cla_rt_rec.delete;
2607 g_cache_pl_pff_rt_rec.delete;
2608 g_cache_oipl_pff_rt_rec.delete;
2609 g_cache_pl_hwf_rt_rec.delete;
2610 g_cache_oipl_hwf_rt_rec.delete;
2611 --
2612 -- hr_utility.set_location ('Leaving '||l_package,10);
2613 --
2614 end clear_down_cache;
2615 --
2616 end ben_derive_part_and_rate_prem;
[Home] [Help]