1 package body ben_derive_part_and_rate_cvg as
2 /* $Header: bendrcvg.pkb 115.6 2002/10/23 00:55:05 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 Coverage 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 26 Jun 00 G Perry 115.1 Added age_calc_rl support.
21 17-jan-01 tilak 116.2 derived faction validation with max
22 changed from > max to > max +1
23 17-Nov-01 ikasire 116.3 Bug 2101937 - Fixed the error in the ceil
24 condition of version 116.2 in all cursors.
25 03-Dec-01 ikasire 116.4 Bug 2101937 - fixed the typo of version 116.3
26 22-Oct-02 ikasire 116.6 Bug 2502763 Added more parameters to clf routines
27 */
28 --------------------------------------------------------------------------------
29 --
30 g_package varchar2(80) := 'ben_derive_part_and_rate_cvg';
31 g_hash_key number := ben_hash_utility.get_hash_key;
32 g_hash_jump number := ben_hash_utility.get_hash_jump;
33 --
34 procedure get_los_pl_rate
35 (p_pl_id in number,
36 p_old_val in number default null,
37 p_new_val in number default null,
38 p_business_group_id in number,
39 p_effective_date in date,
40 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
41 --
42 l_package varchar2(80) := g_package||'.get_los_pl_rate';
43 --
44 -- Define Cursor
45 --
46 cursor c1 is
47 select p_pl_id,
48 'Y',
49 lsf.los_det_cd,
50 lsf.los_dt_to_use_cd,
51 lsf.use_overid_svc_dt_flag,
52 lsf.los_uom,
53 lsf.los_det_rl,
54 lsf.los_dt_to_use_rl,
55 lsf.los_calc_rl,
56 lsf.rndg_cd,
57 lsf.rndg_rl,
58 lsf.mn_los_num,
59 lsf.mx_los_num
60 from ben_los_fctr lsf,
61 ben_los_rt_f lsr,
62 ben_vrbl_rt_prfl_f vpf,
63 ben_bnft_vrbl_rt_f avr,
64 ben_cvg_amt_calc_mthd_f abr
65 where abr.pl_id = p_pl_id
66 and abr.business_group_id = p_business_group_id
67 and p_effective_date
68 between abr.effective_start_date
69 and abr.effective_end_date
70 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
71 and abr.business_group_id = avr.business_group_id
72 and p_effective_date
73 between avr.effective_start_date
74 and avr.effective_end_date
75 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
76 and avr.business_group_id = vpf.business_group_id
77 and p_effective_date
78 between vpf.effective_start_date
79 and vpf.effective_end_date
80 and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
81 and vpf.business_group_id = lsr.business_group_id
82 and p_effective_date
83 between lsr.effective_start_date
84 and lsr.effective_end_date
85 and lsr.los_fctr_id = lsf.los_fctr_id
86 and lsr.business_group_id = lsf.business_group_id
87 and ((p_new_val is not null and
88 p_old_val is not null and
89 p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
90 --p_new_val < ceil( nvl(lsf.mx_los_num,p_new_val) + 0.001 ) )
91 p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
92 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
93 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
94 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) )
95 or
96 (p_new_val is not null and
97 p_old_val is not null and
98 (p_new_val < nvl(lsf.mn_los_num,p_new_val) or
99 --p_new_val >= ceil( nvl(lsf.mx_los_num,p_new_val))+ 0.001 ) and
100 p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
101 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
102 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
103 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) ) and
104
105 p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
106 --p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+ 0.001 )
107 p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
108 ,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
109 nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
110 nvl(lsf.mx_los_num,p_old_val)+0.000000001 ) )
111 or
112 (p_new_val is null and
113 p_old_val is null));
114 --
115 l_index binary_integer;
116 l_not_hash_found boolean;
117 --
118 begin
119 --
120 -- hr_utility.set_location ('Entering '||l_package,10);
121 --
122 -- Steps to do process
123 --
124 -- 1) Try and get value from cache
125 -- 2) If can get from cache then copy to output record
126 -- 3) If can't get from cache do db hit and then
127 -- copy to cache record and then copy to output record.
128 --
129 -- Get hashed index value
130 --
131 l_index := mod(p_pl_id,g_hash_key);
132 --
133 if not g_cache_pl_los_rt_rec.exists(l_index) then
134 --
135 -- Lets store the hash value in this index
136 --
137 raise no_data_found;
138 --
139 else
140 --
141 -- If it does exist make sure its the right one
142 --
143 if g_cache_pl_los_rt_rec(l_index).id <> p_pl_id then
144 --
145 -- Loop through the hash using the jump routine to check further
146 -- indexes
147 --
148 l_not_hash_found := false;
149 --
150 while not l_not_hash_found loop
151 --
152 l_index := l_index+g_hash_jump;
153 --
154 -- Check if the hash index exists, if not we can use it
155 --
156 if not g_cache_pl_los_rt_rec.exists(l_index) then
157 --
158 -- Lets store the hash value in the index
159 --
160 raise no_data_found;
161 --
162 else
163 --
164 -- Make sure the index is the correct one
165 --
166 if g_cache_pl_los_rt_rec(l_index).id = p_pl_id then
167 --
168 -- We have a match so the hashed value has been stored before
169 --
170 l_not_hash_found := true;
171 --
172 end if;
173 --
174 end if;
175 --
176 end loop;
177 --
178 end if;
179 --
180 end if;
181 --
182 -- If p_old_val and p_new_val is set this means we are trying to retrieve
183 -- the correct rate for the calculated value.
184 -- Previously we just cached the first rate we
185 -- found since we needed the determination code, the correct age,los code,etc
186 -- By killing the cache and forcing the value to be removed we cache the
187 -- correct rate profile for the case we need.
188 --
189 if p_old_val is not null and p_new_val is not null then
190 --
191 raise no_data_found;
192 --
193 end if;
194 --
195 p_rec := g_cache_pl_los_rt_rec(l_index);
196 --
197 exception
198 --
199 when no_data_found then
200 --
201 -- The record has not been cached yet so lets cache it
202 --
203 open c1;
204 --
205 fetch c1 into g_cache_pl_los_rt_rec(l_index);
206 if p_old_val is null and p_new_val is null then
207 --
208 if c1%notfound then
209 --
210 g_cache_pl_los_rt_rec(l_index).id := p_pl_id;
211 g_cache_pl_los_rt_rec(l_index).exist := 'N';
212 --
213 end if;
214 --
215 end if;
216 --
217 p_rec := g_cache_pl_los_rt_rec(l_index);
218 --
219 close c1;
220 --
221 end get_los_pl_rate;
222 --
223 procedure get_los_oipl_rate
224 (p_oipl_id in number,
225 p_old_val in number default null,
226 p_new_val in number default null,
227 p_business_group_id in number,
228 p_effective_date in date,
229 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
230 --
231 l_package varchar2(80) := g_package||'.get_los_oipl_rate';
232 --
233 -- Define Cursor
234 --
235 cursor c1 is
236 select p_oipl_id,
237 'Y',
238 lsf.los_det_cd,
239 lsf.los_dt_to_use_cd,
240 lsf.use_overid_svc_dt_flag,
241 lsf.los_uom,
242 lsf.los_det_rl,
243 lsf.los_dt_to_use_rl,
244 lsf.los_calc_rl,
245 lsf.rndg_cd,
246 lsf.rndg_rl,
247 lsf.mn_los_num,
248 lsf.mx_los_num
249 from ben_los_fctr lsf,
250 ben_los_rt_f lsr,
251 ben_vrbl_rt_prfl_f vpf,
252 ben_bnft_vrbl_rt_f avr,
253 ben_cvg_amt_calc_mthd_f abr
254 where abr.oipl_id = p_oipl_id
255 and abr.business_group_id = p_business_group_id
256 and p_effective_date
257 between abr.effective_start_date
258 and abr.effective_end_date
259 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
260 and abr.business_group_id = avr.business_group_id
261 and p_effective_date
262 between avr.effective_start_date
263 and avr.effective_end_date
264 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
265 and avr.business_group_id = vpf.business_group_id
266 and p_effective_date
267 between vpf.effective_start_date
268 and vpf.effective_end_date
269 and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
270 and vpf.business_group_id = lsr.business_group_id
271 and p_effective_date
272 between lsr.effective_start_date
273 and lsr.effective_end_date
274 and lsr.los_fctr_id = lsf.los_fctr_id
275 and lsr.business_group_id = lsf.business_group_id
276 and ((p_new_val is not null and
277 p_old_val is not null and
278 p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
279 --p_new_val < ceil( nvl(lsf.mx_los_num,p_new_val) + 0.001 ) )
280 p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
281 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
282 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
283 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) )
284 or
285 (p_new_val is not null and
286 p_old_val is not null and
287 (p_new_val < nvl(lsf.mn_los_num,p_new_val) or
288 --p_new_val >= ceil( nvl(lsf.mx_los_num,p_new_val))+ 0.001 ) and
289 p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
290 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
291 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
292 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) ) and
293
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 --
398 if p_old_val is null and p_new_val is null then
395 open c1;
396 --
397 fetch c1 into g_cache_oipl_los_rt_rec(l_index);
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_plip_rate
416 (p_plip_id in number,
417 p_old_val in number default null,
418 p_new_val in number default null,
419 p_business_group_id in number,
420 p_effective_date in date,
421 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
422 --
423 l_package varchar2(80) := g_package||'.get_los_plip_rate';
424 --
425 -- Define Cursor
426 --
427 cursor c1 is
428 select p_plip_id,
429 'Y',
430 lsf.los_det_cd,
431 lsf.los_dt_to_use_cd,
432 lsf.use_overid_svc_dt_flag,
433 lsf.los_uom,
434 lsf.los_det_rl,
435 lsf.los_dt_to_use_rl,
436 lsf.los_calc_rl,
437 lsf.rndg_cd,
438 lsf.rndg_rl,
439 lsf.mn_los_num,
440 lsf.mx_los_num
441 from ben_los_fctr lsf,
442 ben_los_rt_f lsr,
443 ben_vrbl_rt_prfl_f vpf,
444 ben_bnft_vrbl_rt_f avr,
445 ben_cvg_amt_calc_mthd_f abr
446 where abr.plip_id = p_plip_id
447 and abr.business_group_id = p_business_group_id
448 and p_effective_date
449 between abr.effective_start_date
450 and abr.effective_end_date
451 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
452 and abr.business_group_id = avr.business_group_id
453 and p_effective_date
454 between avr.effective_start_date
455 and avr.effective_end_date
456 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
457 and avr.business_group_id = vpf.business_group_id
458 and p_effective_date
459 between vpf.effective_start_date
460 and vpf.effective_end_date
461 and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
462 and vpf.business_group_id = lsr.business_group_id
463 and p_effective_date
464 between lsr.effective_start_date
465 and lsr.effective_end_date
466 and lsr.los_fctr_id = lsf.los_fctr_id
467 and lsr.business_group_id = lsf.business_group_id
468 and ((p_new_val is not null and
469 p_old_val is not null and
470 p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
471 --p_new_val < ceil( nvl(lsf.mx_los_num,p_new_val)+0.001))
472 p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
473 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
474 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
475 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) )
476 or
477 (p_new_val is not null and
478 p_old_val is not null and
479 (p_new_val < nvl(lsf.mn_los_num,p_new_val) or
480 --p_new_val >= ceil(nvl(lsf.mx_los_num,p_new_val))+0.001) and
481 p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
482 ,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
483 nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
484 nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) ) and
485 p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
486 --p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+0.001)
487 p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
488 ,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
489 nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
490 nvl(lsf.mx_los_num,p_old_val)+0.000000001 ) )
491 or
492 (p_new_val is null and
493 p_old_val is null));
494 --
495 --
496 l_index binary_integer;
497 l_not_hash_found boolean;
498 --
499 begin
500 --
501 -- hr_utility.set_location ('Entering '||l_package,10);
502 --
503 -- Steps to do process
504 --
505 -- 1) Try and get value from cache
506 -- 2) If can get from cache then copy to output record
507 -- 3) If can't get from cache do db hit and then
508 -- copy to cache record and then copy to output record.
509 --
510 -- Get hashed index value
511 --
512 l_index := mod(p_plip_id,g_hash_key);
513 --
514 if not g_cache_plip_los_rt_rec.exists(l_index) then
515 --
516 -- Lets store the hash value in this index
517 --
521 --
518 raise no_data_found;
519 --
520 else
522 -- If it does exist make sure its the right one
523 --
524 if g_cache_plip_los_rt_rec(l_index).id <> p_plip_id then
525 --
526 -- Loop through the hash using the jump routine to check further
527 -- indexes
528 --
529 l_not_hash_found := false;
530 --
531 while not l_not_hash_found loop
532 --
533 l_index := l_index+g_hash_jump;
534 --
535 -- Check if the hash index exists, if not we can use it
536 --
537 if not g_cache_plip_los_rt_rec.exists(l_index) then
538 --
539 -- Lets store the hash value in the index
540 --
541 raise no_data_found;
542 --
543 else
544 --
545 -- Make sure the index is the correct one
546 --
547 if g_cache_plip_los_rt_rec(l_index).id = p_plip_id then
548 --
549 -- We have a match so the hashed value has been stored before
550 --
551 l_not_hash_found := true;
552 --
553 end if;
554 --
555 end if;
556 --
557 end loop;
558 --
559 end if;
560 --
561 end if;
562 --
563 -- If p_old_val and p_new_val is set this means we are trying to retrieve
564 -- the correct rate for the calculated value.
565 -- Previously we just cached the first rate we
566 -- found since we needed the determination code, the correct age,los code,etc
567 -- By killing the cache and forcing the value to be removed we cache the
568 -- correct rate profile for the case we need.
569 --
570 if p_old_val is not null and p_new_val is not null then
571 --
572 raise no_data_found;
573 --
574 end if;
575 --
576 p_rec := g_cache_plip_los_rt_rec(l_index);
577 --
578 -- hr_utility.set_location ('Leaving '||l_package,10);
579 --
580 exception
581 --
582 when no_data_found then
583 --
584 -- The record has not been cached yet so lets cache it
585 --
586 open c1;
587 --
588 fetch c1 into g_cache_plip_los_rt_rec(l_index);
589 if p_old_val is null and p_new_val is null then
590 --
591 if c1%notfound then
592 --
593 g_cache_plip_los_rt_rec(l_index).id := p_plip_id;
594 g_cache_plip_los_rt_rec(l_index).exist := 'N';
595 --
596 end if;
597 --
598 end if;
599 --
600 p_rec := g_cache_plip_los_rt_rec(l_index);
601 --
602 close c1;
603 --
604 end get_los_plip_rate;
605 --
606 procedure get_los_rate
607 (p_pl_id in number,
608 p_oipl_id in number,
609 p_plip_id in number,
610 p_old_val in number default null,
611 p_new_val in number default null,
612 p_business_group_id in number,
613 p_effective_date in date,
614 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_los_rec_obj) is
615 --
616 l_package varchar2(80) := g_package||'.get_los_rate';
617 --
618 begin
619 --
620 -- hr_utility.set_location ('Entering '||l_package,10);
621 --
622 -- Derive which data type we are dealing with
623 --
624 if p_pl_id is not null then
625 --
626 get_los_pl_rate(p_pl_id => p_pl_id,
627 p_old_val => p_old_val,
628 p_new_val => p_new_val,
629 p_business_group_id => p_business_group_id,
630 p_effective_date => p_effective_date,
631 p_rec => p_rec);
632 --
633 elsif p_oipl_id is not null then
634 --
635 get_los_oipl_rate(p_oipl_id => p_oipl_id,
636 p_old_val => p_old_val,
637 p_new_val => p_new_val,
638 p_business_group_id => p_business_group_id,
639 p_effective_date => p_effective_date,
640 p_rec => p_rec);
641 --
642 elsif p_plip_id is not null then
643 --
644 get_los_plip_rate(p_plip_id => p_plip_id,
645 p_old_val => p_old_val,
646 p_new_val => p_new_val,
647 p_business_group_id => p_business_group_id,
648 p_effective_date => p_effective_date,
649 p_rec => p_rec);
650 --
651 end if;
652 --
653 -- hr_utility.set_location ('Leaving '||l_package,10);
654 --
655 end get_los_rate;
656 --
657 procedure get_age_pl_rate
658 (p_pl_id in number,
659 p_old_val in number default null,
660 p_new_val in number default null,
661 p_business_group_id in number,
662 p_effective_date in date,
663 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
664 --
665 l_package varchar2(80) := g_package||'.get_age_pl_rate';
666 --
670 select p_pl_id,
667 -- Define Cursor
668 --
669 cursor c1 is
671 'Y',
672 agf.age_det_cd,
673 agf.age_to_use_cd,
674 agf.age_uom,
675 agf.age_det_rl,
676 agf.rndg_cd,
677 agf.rndg_rl,
678 agf.age_calc_rl,
679 agf.mn_age_num,
680 agf.mx_age_num
681 from ben_age_fctr agf,
682 ben_age_rt_f art,
683 ben_vrbl_rt_prfl_f vpf,
684 ben_bnft_vrbl_rt_f avr,
685 ben_cvg_amt_calc_mthd_f abr
686 where abr.pl_id = p_pl_id
687 and abr.business_group_id = p_business_group_id
688 and p_effective_date
689 between abr.effective_start_date
690 and abr.effective_end_date
691 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
692 and abr.business_group_id = avr.business_group_id
693 and p_effective_date
694 between avr.effective_start_date
695 and avr.effective_end_date
696 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
697 and avr.business_group_id = vpf.business_group_id
698 and p_effective_date
699 between vpf.effective_start_date
700 and vpf.effective_end_date
701 and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
702 and vpf.business_group_id = art.business_group_id
703 and p_effective_date
704 between art.effective_start_date
705 and art.effective_end_date
706 and art.age_fctr_id = agf.age_fctr_id
707 and art.business_group_id = agf.business_group_id
708 and ((p_new_val is not null and
709 p_old_val is not null and
710 p_new_val >= nvl(agf.mn_age_num,p_new_val) and
711 --p_new_val < ceil( nvl(agf.mx_age_num,p_new_val)+0.001))
712 p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
713 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
714 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
715 nvl(agf.mx_age_num,p_new_val)+0.000000001 ) )
716 or
717 (p_new_val is not null and
718 p_old_val is not null and
719 (p_new_val < nvl(agf.mn_age_num,p_new_val) or
720 --p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001) and
721 p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
722 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
723 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
724 nvl(agf.mx_age_num,p_new_val)+0.000000001 ) ) and
725 p_old_val >= nvl(agf.mn_age_num,p_old_val) and
726 --p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001)
727 p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
728 ,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
729 nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
730 nvl(agf.mx_age_num,p_old_val)+0.000000001 ) )
731 or
732 (p_new_val is null and
733 p_old_val is null));
734 --
735 --
736 l_index binary_integer;
737 l_not_hash_found boolean;
738 --
739 begin
740 --
741 -- hr_utility.set_location ('Entering '||l_package,10);
742 --
743 -- Steps to do process
744 --
745 -- 1) Try and get value from cache
746 -- 2) If can get from cache then copy to output record
747 -- 3) If can't get from cache do db hit and then
748 -- copy to cache record and then copy to output record.
749 --
750 -- Get hashed index value
751 --
752 l_index := mod(p_pl_id,g_hash_key);
753 --
754 if not g_cache_pl_age_rt_rec.exists(l_index) then
755 --
756 -- Lets store the hash value in this index
757 --
758 raise no_data_found;
759 --
760 else
761 --
762 -- If it does exist make sure its the right one
763 --
764 if g_cache_pl_age_rt_rec(l_index).id <> p_pl_id then
765 --
766 -- Loop through the hash using the jump routine to check further
767 -- indexes
768 --
769 l_not_hash_found := false;
770 --
771 while not l_not_hash_found loop
772 --
773 l_index := l_index+g_hash_jump;
774 --
775 -- Check if the hash index exists, if not we can use it
776 --
777 if not g_cache_pl_age_rt_rec.exists(l_index) then
778 --
779 -- Lets store the hash value in the index
780 --
781 raise no_data_found;
782 --
783 else
784 --
785 -- Make sure the index is the correct one
786 --
787 if g_cache_pl_age_rt_rec(l_index).id = p_pl_id then
788 --
789 -- We have a match so the hashed value has been stored before
790 --
794 --
791 l_not_hash_found := true;
792 --
793 end if;
795 end if;
796 --
797 end loop;
798 --
799 end if;
800 --
801 end if;
802 --
803 -- If p_old_val ind p_new_val is set this means we are trying to retrieve
804 -- the correct rate for the calculated value.
805 -- Previously we just cached the first rate we
806 -- found since we needed the determination code, the correct age,los code,etc
807 -- By killing the cache and forcing the value to be removed we cache the
808 -- correct rate profile for the case we need.
809 --
810 if p_old_val is not null and p_new_val is not null then
811 --
812 raise no_data_found;
813 --
814 end if;
815 --
816 p_rec := g_cache_pl_age_rt_rec(l_index);
817 --
818 -- hr_utility.set_location ('Leaving '||l_package,10);
819 --
820 exception
821 --
822 when no_data_found then
823 --
824 -- The record has not been cached yet so lets cache it
825 --
826 open c1;
827 --
828 fetch c1 into g_cache_pl_age_rt_rec(l_index);
829 if p_old_val is null and p_new_val is null then
830 --
831 if c1%notfound then
832 --
833 g_cache_pl_age_rt_rec(l_index).id := p_pl_id;
834 g_cache_pl_age_rt_rec(l_index).exist := 'N';
835 --
836 end if;
837 --
838 end if;
839 --
840 p_rec := g_cache_pl_age_rt_rec(l_index);
841 --
842 close c1;
843 --
844 end get_age_pl_rate;
845 --
846 procedure get_age_oipl_rate
847 (p_oipl_id in number,
848 p_old_val in number default null,
849 p_new_val in number default null,
850 p_business_group_id in number,
851 p_effective_date in date,
852 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
853 --
854 l_package varchar2(80) := g_package||'.get_age_oipl_rate';
855 --
856 -- Define Cursor
857 --
858 cursor c1 is
859 select p_oipl_id,
860 'Y',
861 agf.age_det_cd,
862 agf.age_to_use_cd,
863 agf.age_uom,
864 agf.age_det_rl,
865 agf.rndg_cd,
866 agf.rndg_rl,
867 agf.age_calc_rl,
868 agf.mn_age_num,
869 agf.mx_age_num
870 from ben_age_fctr agf,
871 ben_age_rt_f art,
872 ben_vrbl_rt_prfl_f vpf,
873 ben_bnft_vrbl_rt_f avr,
874 ben_cvg_amt_calc_mthd_f abr
875 where abr.oipl_id = p_oipl_id
876 and abr.business_group_id = p_business_group_id
877 and p_effective_date
878 between abr.effective_start_date
879 and abr.effective_end_date
880 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
881 and abr.business_group_id = avr.business_group_id
882 and p_effective_date
883 between avr.effective_start_date
884 and avr.effective_end_date
885 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
886 and avr.business_group_id = vpf.business_group_id
887 and p_effective_date
888 between vpf.effective_start_date
889 and vpf.effective_end_date
890 and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
891 and vpf.business_group_id = art.business_group_id
892 and p_effective_date
893 between art.effective_start_date
894 and art.effective_end_date
895 and art.age_fctr_id = agf.age_fctr_id
896 and art.business_group_id = agf.business_group_id
897 and ((p_new_val is not null and
898 p_old_val is not null and
899 p_new_val >= nvl(agf.mn_age_num,p_new_val) and
900 --p_new_val < ceil(nvl(agf.mx_age_num,p_new_val)+0.001 ) )
901 p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
902 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
903 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
904 nvl(agf.mx_age_num,p_new_val)+0.000000001 ) )
905 or
906 (p_new_val is not null and
907 p_old_val is not null and
908 (p_new_val < nvl(agf.mn_age_num,p_new_val) or
909 --p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001 ) and
910 p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
911 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
912 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
913 nvl(agf.mx_age_num,p_new_val)+0.000000001 ) ) and
914 p_old_val >= nvl(agf.mn_age_num,p_old_val) and
915 --p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001 )
916 p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
917 ,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
921 (p_new_val is null and
918 nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
919 nvl(agf.mx_age_num,p_old_val)+0.000000001 ) )
920 or
922 p_old_val is null));
923 --
924 --
925 l_index binary_integer;
926 l_not_hash_found boolean;
927 --
928 begin
929 --
930 -- hr_utility.set_location ('Entering '||l_package,10);
931 --
932 -- Steps to do process
933 --
934 -- 1) Try and get value from cache
935 -- 2) If can get from cache then copy to output record
936 -- 3) If can't get from cache do db hit and then
937 -- copy to cache record and then copy to output record.
938 --
939 -- Get hashed index value
940 --
941 l_index := mod(p_oipl_id,g_hash_key);
942 --
943 if not g_cache_oipl_age_rt_rec.exists(l_index) then
944 --
945 -- Lets store the hash value in this index
946 --
947 raise no_data_found;
948 --
949 else
950 --
951 -- If it does exist make sure its the right one
952 --
953 if g_cache_oipl_age_rt_rec(l_index).id <> p_oipl_id then
954 --
955 -- Loop through the hash using the jump routine to check further
956 -- indexes
957 --
958 l_not_hash_found := false;
959 --
960 while not l_not_hash_found loop
961 --
962 l_index := l_index+g_hash_jump;
963 --
964 -- Check if the hash index exists, if not we can use it
965 --
966 if not g_cache_oipl_age_rt_rec.exists(l_index) then
967 --
968 -- Lets store the hash value in the index
969 --
970 raise no_data_found;
971 --
972 else
973 --
974 -- Make sure the index is the correct one
975 --
976 if g_cache_oipl_age_rt_rec(l_index).id = p_oipl_id then
977 --
978 -- We have a match so the hashed value has been stored before
979 --
980 l_not_hash_found := true;
981 --
982 end if;
983 --
984 end if;
985 --
986 end loop;
987 --
988 end if;
989 --
990 end if;
991 --
992 -- If p_old_val and p_new_val is set this means we are trying to retrieve
993 -- the correct rate for the calculated value.
994 -- Previously we just cached the first rate we
995 -- found since we needed the determination code, the correct age,los code,etc
996 -- By killing the cache and forcing the value to be removed we cache the
997 -- correct rate profile for the case we need.
998 --
999 if p_old_val is not null and p_new_val is not null then
1000 --
1001 raise no_data_found;
1002 --
1003 end if;
1004 --
1005 p_rec := g_cache_oipl_age_rt_rec(l_index);
1006 --
1007 -- hr_utility.set_location ('Leaving '||l_package,10);
1008 --
1009 exception
1010 --
1011 when no_data_found then
1012 --
1013 -- The record has not been cached yet so lets cache it
1014 --
1015 open c1;
1016 --
1017 fetch c1 into g_cache_oipl_age_rt_rec(l_index);
1018 if p_old_val is null and p_new_val is null then
1019 --
1020 if c1%notfound then
1021 --
1022 g_cache_oipl_age_rt_rec(l_index).id := p_oipl_id;
1023 g_cache_oipl_age_rt_rec(l_index).exist := 'N';
1024 --
1025 end if;
1026 --
1027 end if;
1028 --
1029 p_rec := g_cache_oipl_age_rt_rec(l_index);
1030 --
1031 close c1;
1032 --
1033 end get_age_oipl_rate;
1034 --
1035 procedure get_age_plip_rate
1036 (p_plip_id in number,
1037 p_old_val in number default null,
1038 p_new_val in number default null,
1039 p_business_group_id in number,
1040 p_effective_date in date,
1041 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
1042 --
1043 l_package varchar2(80) := g_package||'.get_age_plip_rate';
1044 --
1045 -- Define Cursor
1046 --
1047 cursor c1 is
1048 select p_plip_id,
1049 'Y',
1050 agf.age_det_cd,
1051 agf.age_to_use_cd,
1052 agf.age_uom,
1053 agf.age_det_rl,
1054 agf.rndg_cd,
1055 agf.rndg_rl,
1056 agf.age_calc_rl,
1057 agf.mn_age_num,
1058 agf.mx_age_num
1059 from ben_cvg_amt_calc_mthd_f abr,
1060 ben_bnft_vrbl_rt_f avr,
1061 ben_vrbl_rt_prfl_f vpf,
1062 ben_age_rt_f art,
1063 ben_age_fctr agf
1064 where abr.plip_id = p_plip_id
1065 and p_effective_date
1066 between abr.effective_start_date
1067 and abr.effective_end_date
1068 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
1069 and p_effective_date
1070 between avr.effective_start_date
1071 and avr.effective_end_date
1075 and vpf.effective_end_date
1072 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1073 and p_effective_date
1074 between vpf.effective_start_date
1076 and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
1077 and p_effective_date
1078 between art.effective_start_date
1079 and art.effective_end_date
1080 and art.age_fctr_id = agf.age_fctr_id
1081 and ((p_new_val is not null and
1082 p_old_val is not null and
1083 p_new_val >= nvl(agf.mn_age_num,p_new_val) and
1084 --p_new_val < ceil(nvl(agf.mx_age_num,p_new_val)+0.001 ) )
1085 p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
1086 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
1087 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
1088 nvl(agf.mx_age_num,p_new_val)+0.000000001 ) )
1089 or
1090 (p_new_val is not null and
1091 p_old_val is not null and
1092 (p_new_val < nvl(agf.mn_age_num,p_new_val) or
1093 --p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001 ) and
1094 p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
1095 ,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
1096 nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
1097 nvl(agf.mx_age_num,p_new_val)+0.000000001 ) ) and
1098 p_old_val >= nvl(agf.mn_age_num,p_old_val) and
1099 --p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001 )
1100 p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
1101 ,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
1102 nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
1103 nvl(agf.mx_age_num,p_old_val)+0.000000001 ) )
1104 or
1105 (p_new_val is null and
1106 p_old_val is null));
1107 --
1108 --
1109 l_index binary_integer;
1110 l_not_hash_found boolean;
1111 --
1112 begin
1113 --
1114 -- hr_utility.set_location ('Entering '||l_package,10);
1115 --
1116 -- Steps to do process
1117 --
1118 -- 1) Try and get value from cache
1119 -- 2) If can get from cache then copy to output record
1120 -- 3) If can't get from cache do db hit and then
1121 -- copy to cache record and then copy to output record.
1122 --
1123 -- Get hashed index value
1124 --
1125 l_index := mod(p_plip_id,g_hash_key);
1126 --
1127 if not g_cache_plip_age_rt_rec.exists(l_index) then
1128 --
1129 -- Lets store the hash value in this index
1130 --
1131 raise no_data_found;
1132 --
1133 else
1134 --
1135 -- If it does exist make sure its the right one
1136 --
1137 if g_cache_plip_age_rt_rec(l_index).id <> p_plip_id then
1138 --
1139 -- Loop through the hash using the jump routine to check further
1140 -- indexes
1141 --
1142 l_not_hash_found := false;
1143 --
1144 while not l_not_hash_found loop
1145 --
1146 l_index := l_index+g_hash_jump;
1147 --
1148 -- Check if the hash index exists, if not we can use it
1149 --
1150 if not g_cache_plip_age_rt_rec.exists(l_index) then
1151 --
1152 -- Lets store the hash value in the index
1153 --
1154 raise no_data_found;
1155 --
1156 else
1157 --
1158 -- Make sure the index is the correct one
1159 --
1160 if g_cache_plip_age_rt_rec(l_index).id = p_plip_id then
1161 --
1162 -- We have a match so the hashed value has been stored before
1163 --
1164 l_not_hash_found := true;
1165 --
1166 end if;
1167 --
1168 end if;
1169 --
1170 end loop;
1171 --
1172 end if;
1173 --
1174 end if;
1175 --
1176 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1177 -- the correct rate for the calculated value.
1178 -- Previously we just cached the first rate we
1179 -- found since we needed the determination code, the correct age,los code,etc
1180 -- By killing the cache and forcing the value to be removed we cache the
1181 -- correct rate profile for the case we need.
1182 --
1183 if p_old_val is not null and p_new_val is not null then
1184 --
1185 raise no_data_found;
1186 --
1187 end if;
1188 --
1189 p_rec := g_cache_plip_age_rt_rec(l_index);
1190 --
1191 -- hr_utility.set_location ('Leaving '||l_package,10);
1192 --
1193 exception
1194 --
1195 when no_data_found then
1196 --
1197 -- The record has not been cached yet so lets cache it
1198 --
1199 open c1;
1200 --
1201 fetch c1 into g_cache_plip_age_rt_rec(l_index);
1202 if p_old_val is null and p_new_val is null then
1203 --
1207 g_cache_plip_age_rt_rec(l_index).exist := 'N';
1204 if c1%notfound then
1205 --
1206 g_cache_plip_age_rt_rec(l_index).id := p_plip_id;
1208 --
1209 end if;
1210 --
1211 end if;
1212 --
1213 p_rec := g_cache_plip_age_rt_rec(l_index);
1214 --
1215 close c1;
1216 --
1217 end get_age_plip_rate;
1218 --
1219 procedure get_age_rate
1220 (p_pl_id in number,
1221 p_oipl_id in number,
1222 p_plip_id in number,
1223 p_old_val in number default null,
1224 p_new_val in number default null,
1225 p_business_group_id in number,
1226 p_effective_date in date,
1227 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_age_rec_obj) is
1228 --
1229 l_package varchar2(80) := g_package||'.get_age_rate';
1230 --
1231 begin
1232 --
1233 -- hr_utility.set_location ('Entering '||l_package,10);
1234 --
1235 -- Derive which data type we are dealing with
1236 --
1237 if p_pl_id is not null then
1238 --
1239 get_age_pl_rate(p_pl_id => p_pl_id,
1240 p_old_val => p_old_val,
1241 p_new_val => p_new_val,
1242 p_business_group_id => p_business_group_id,
1243 p_effective_date => p_effective_date,
1244 p_rec => p_rec);
1245 --
1246 elsif p_oipl_id is not null then
1247 --
1248 get_age_oipl_rate(p_oipl_id => p_oipl_id,
1249 p_old_val => p_old_val,
1250 p_new_val => p_new_val,
1251 p_business_group_id => p_business_group_id,
1252 p_effective_date => p_effective_date,
1253 p_rec => p_rec);
1254 --
1255 elsif p_plip_id is not null then
1256 --
1257 get_age_plip_rate(p_plip_id => p_plip_id,
1258 p_old_val => p_old_val,
1259 p_new_val => p_new_val,
1260 p_business_group_id => p_business_group_id,
1261 p_effective_date => p_effective_date,
1262 p_rec => p_rec);
1263 --
1264 end if;
1265 --
1266 -- hr_utility.set_location ('Leaving '||l_package,10);
1267 --
1268 end get_age_rate;
1269 --
1270 procedure get_comp_pl_rate
1271 (p_pl_id in number,
1272 p_old_val in number default null,
1273 p_new_val in number default null,
1274 p_business_group_id in number,
1275 p_effective_date in date,
1276 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
1277 --
1278 l_package varchar2(80) := g_package||'.get_comp_pl_rate';
1279 --
1280 -- Define Cursor
1281 --
1282 cursor c1 is
1283 select p_pl_id,
1284 'Y',
1285 clf.comp_lvl_uom,
1286 clf.comp_src_cd,
1287 clf.comp_lvl_det_cd,
1288 clf.comp_lvl_det_rl,
1289 clf.rndg_cd,
1290 clf.rndg_rl,
1291 clf.mn_comp_val,
1292 clf.mx_comp_val,
1293 clf.bnfts_bal_id,
1294 clf.defined_balance_id,
1295 clf.sttd_sal_prdcty_cd,
1296 clf.comp_lvl_fctr_id,
1297 clf.comp_calc_rl
1298 from ben_comp_lvl_fctr clf,
1299 ben_comp_lvl_rt_f clr,
1300 ben_vrbl_rt_prfl_f vpf,
1301 ben_bnft_vrbl_rt_f avr,
1302 ben_cvg_amt_calc_mthd_f abr
1303 where abr.pl_id = p_pl_id
1304 and abr.business_group_id = p_business_group_id
1305 and p_effective_date
1306 between abr.effective_start_date
1307 and abr.effective_end_date
1308 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
1309 and abr.business_group_id = avr.business_group_id
1310 and p_effective_date
1311 between avr.effective_start_date
1312 and avr.effective_end_date
1313 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1314 and avr.business_group_id = vpf.business_group_id
1315 and p_effective_date
1316 between vpf.effective_start_date
1317 and vpf.effective_end_date
1318 and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
1319 and vpf.business_group_id = clr.business_group_id
1320 and p_effective_date
1321 between clr.effective_start_date
1322 and clr.effective_end_date
1323 and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
1324 and clr.business_group_id = clf.business_group_id
1325 and ((p_new_val is not null and
1326 p_old_val is not null and
1327 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
1328 --p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+0.001 ) )
1329 p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1330 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1334 (p_new_val is not null and
1331 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1332 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) )
1333 or
1335 p_old_val is not null and
1336 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
1337 --p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+0.001 ) and
1338 p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1339 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1340 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1341 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) ) and
1342 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
1343 --p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+0.001 )
1344 p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
1345 ,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
1346 nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
1347 nvl(clf.mx_comp_val,p_old_val)+0.000000001 ) )
1348 or
1349 (p_new_val is null and
1350 p_old_val is null));
1351 --
1352 --
1353 l_index binary_integer;
1354 l_not_hash_found boolean;
1355 --
1356 begin
1357 --
1358 -- hr_utility.set_location ('Entering '||l_package,10);
1359 --
1360 -- Steps to do process
1361 --
1362 -- 1) Try and get value from cache
1363 -- 2) If can get from cache then copy to output record
1364 -- 3) If can't get from cache do db hit and then
1365 -- copy to cache record and then copy to output record.
1366 --
1367 -- Get hashed index value
1368 --
1369 l_index := mod(p_pl_id,g_hash_key);
1370 --
1371 if not g_cache_pl_clf_rt_rec.exists(l_index) then
1372 --
1373 -- Lets store the hash value in this index
1374 --
1375 raise no_data_found;
1376 --
1377 else
1378 --
1379 -- If it does exist make sure its the right one
1380 --
1381 if g_cache_pl_clf_rt_rec(l_index).id <> p_pl_id then
1382 --
1383 -- Loop through the hash using the jump routine to check further
1384 -- indexes
1385 --
1386 l_not_hash_found := false;
1387 --
1388 while not l_not_hash_found loop
1389 --
1390 l_index := l_index+g_hash_jump;
1391 --
1392 -- Check if the hash index exists, if not we can use it
1393 --
1394 if not g_cache_pl_clf_rt_rec.exists(l_index) then
1395 --
1396 -- Lets store the hash value in the index
1397 --
1398 raise no_data_found;
1399 --
1400 else
1401 --
1402 -- Make sure the index is the correct one
1403 --
1404 if g_cache_pl_clf_rt_rec(l_index).id = p_pl_id then
1405 --
1406 -- We have a match so the hashed value has been stored before
1407 --
1408 l_not_hash_found := true;
1409 --
1410 end if;
1411 --
1412 end if;
1413 --
1414 end loop;
1415 --
1416 end if;
1417 --
1418 end if;
1419 --
1420 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1421 -- the correct rate for the calculated value.
1422 -- Previously we just cached the first rate we
1423 -- found since we needed the determination code, the correct age,los code,etc
1424 -- By killing the cache and forcing the value to be removed we cache the
1425 -- correct rate profile for the case we need.
1426 --
1427 if p_old_val is not null and p_new_val is not null then
1428 --
1429 raise no_data_found;
1430 --
1431 end if;
1432 --
1433 p_rec := g_cache_pl_clf_rt_rec(l_index);
1434 --
1435 -- hr_utility.set_location ('Leaving '||l_package,10);
1436 --
1437 exception
1438 --
1439 when no_data_found then
1440 --
1441 -- The record has not been cached yet so lets cache it
1442 --
1443 open c1;
1444 --
1445 fetch c1 into g_cache_pl_clf_rt_rec(l_index);
1446 if p_old_val is null and p_new_val is null then
1447 --
1448 if c1%notfound then
1449 --
1450 g_cache_pl_clf_rt_rec(l_index).id := p_pl_id;
1451 g_cache_pl_clf_rt_rec(l_index).exist := 'N';
1452 --
1453 end if;
1454 --
1455 end if;
1456 --
1457 p_rec := g_cache_pl_clf_rt_rec(l_index);
1458 --
1459 close c1;
1460 --
1461 end get_comp_pl_rate;
1462 --
1463 procedure get_comp_oipl_rate
1464 (p_oipl_id in number,
1465 p_old_val in number default null,
1466 p_new_val in number default null,
1467 p_business_group_id in number,
1468 p_effective_date in date,
1469 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
1470 --
1471 l_package varchar2(80) := g_package||'.get_comp_oipl_rate';
1475 cursor c1 is
1472 --
1473 -- Define Cursor
1474 --
1476 select p_oipl_id,
1477 'Y',
1478 clf.comp_lvl_uom,
1479 clf.comp_src_cd,
1480 clf.comp_lvl_det_cd,
1481 clf.comp_lvl_det_rl,
1482 clf.rndg_cd,
1483 clf.rndg_rl,
1484 clf.mn_comp_val,
1485 clf.mx_comp_val,
1486 clf.bnfts_bal_id,
1487 clf.defined_balance_id,
1488 clf.sttd_sal_prdcty_cd,
1489 clf.comp_lvl_fctr_id,
1490 clf.comp_calc_rl
1491 from ben_comp_lvl_fctr clf,
1492 ben_comp_lvl_rt_f clr,
1493 ben_vrbl_rt_prfl_f vpf,
1494 ben_bnft_vrbl_rt_f avr,
1495 ben_cvg_amt_calc_mthd_f abr
1496 where abr.oipl_id = p_oipl_id
1497 and abr.business_group_id = p_business_group_id
1498 and p_effective_date
1499 between abr.effective_start_date
1500 and abr.effective_end_date
1501 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
1502 and abr.business_group_id = avr.business_group_id
1503 and p_effective_date
1504 between avr.effective_start_date
1505 and avr.effective_end_date
1506 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1507 and avr.business_group_id = vpf.business_group_id
1508 and p_effective_date
1509 between vpf.effective_start_date
1510 and vpf.effective_end_date
1511 and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
1512 and vpf.business_group_id = clr.business_group_id
1513 and p_effective_date
1514 between clr.effective_start_date
1515 and clr.effective_end_date
1516 and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
1517 and clr.business_group_id = clf.business_group_id
1518 and ((p_new_val is not null and
1519 p_old_val is not null and
1520 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
1521 --p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+ 0.001 ) )
1522 p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1523 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1524 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1525 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) )
1526 or
1527 (p_new_val is not null and
1528 p_old_val is not null and
1529 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
1530 --p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+ 0.001 ) and
1531 p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1532 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1533 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1534 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) ) and
1535 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
1536 --p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+ 0.001 )
1537 p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
1538 ,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
1539 nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
1540 nvl(clf.mx_comp_val,p_old_val)+0.000000001 ) )
1541 or
1542 (p_new_val is null and
1543 p_old_val is null));
1544 --
1545 --
1546 l_index binary_integer;
1547 l_not_hash_found boolean;
1548 --
1549 begin
1550 --
1551 -- hr_utility.set_location ('Entering '||l_package,10);
1552 --
1553 -- Steps to do process
1554 --
1555 -- 1) Try and get value from cache
1556 -- 2) If can get from cache then copy to output record
1557 -- 3) If can't get from cache do db hit and then
1558 -- copy to cache record and then copy to output record.
1559 --
1560 -- Get hashed index value
1561 --
1562 l_index := mod(p_oipl_id,g_hash_key);
1563 --
1564 if not g_cache_oipl_clf_rt_rec.exists(l_index) then
1565 --
1566 -- Lets store the hash value in this index
1567 --
1568 raise no_data_found;
1569 --
1570 else
1571 --
1572 -- If it does exist make sure its the right one
1573 --
1574 if g_cache_oipl_clf_rt_rec(l_index).id <> p_oipl_id then
1575 --
1576 -- Loop through the hash using the jump routine to check further
1577 -- indexes
1578 --
1579 l_not_hash_found := false;
1580 --
1581 while not l_not_hash_found loop
1582 --
1583 l_index := l_index+g_hash_jump;
1584 --
1585 -- Check if the hash index exists, if not we can use it
1586 --
1587 if not g_cache_oipl_clf_rt_rec.exists(l_index) then
1588 --
1589 -- Lets store the hash value in the index
1590 --
1591 raise no_data_found;
1592 --
1593 else
1594 --
1595 -- Make sure the index is the correct one
1596 --
1597 if g_cache_oipl_clf_rt_rec(l_index).id = p_oipl_id then
1601 l_not_hash_found := true;
1598 --
1599 -- We have a match so the hashed value has been stored before
1600 --
1602 --
1603 end if;
1604 --
1605 end if;
1606 --
1607 end loop;
1608 --
1609 end if;
1610 --
1611 end if;
1612 --
1613 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1614 -- the correct rate for the calculated value.
1615 -- Previously we just cached the first rate we
1616 -- found since we needed the determination code, the correct age,los code,etc
1617 -- By killing the cache and forcing the value to be removed we cache the
1618 -- correct rate profile for the case we need.
1619 --
1620 if p_old_val is not null and p_new_val is not null then
1621 --
1622 raise no_data_found;
1623 --
1624 end if;
1625 --
1626 p_rec := g_cache_oipl_clf_rt_rec(l_index);
1627 --
1628 -- hr_utility.set_location ('Leaving '||l_package,10);
1629 --
1630 exception
1631 --
1632 when no_data_found then
1633 --
1634 -- The record has not been cached yet so lets cache it
1635 --
1636 open c1;
1637 --
1638 fetch c1 into g_cache_oipl_clf_rt_rec(l_index);
1639 if p_old_val is null and p_new_val is null then
1640 --
1641 if c1%notfound then
1642 --
1643 g_cache_oipl_clf_rt_rec(l_index).id := p_oipl_id;
1644 g_cache_oipl_clf_rt_rec(l_index).exist := 'N';
1645 --
1646 end if;
1647 --
1648 end if;
1649 --
1650 p_rec := g_cache_oipl_clf_rt_rec(l_index);
1651 --
1652 close c1;
1653 --
1654 end get_comp_oipl_rate;
1655 --
1656 procedure get_comp_plip_rate
1657 (p_plip_id in number,
1658 p_old_val in number default null,
1659 p_new_val in number default null,
1660 p_business_group_id in number,
1661 p_effective_date in date,
1662 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
1663 --
1664 l_package varchar2(80) := g_package||'.get_comp_plip_rate';
1665 --
1666 -- Define Cursor
1667 --
1668 cursor c1 is
1669 select p_plip_id,
1670 'Y',
1671 clf.comp_lvl_uom,
1672 clf.comp_src_cd,
1673 clf.comp_lvl_det_cd,
1674 clf.comp_lvl_det_rl,
1675 clf.rndg_cd,
1676 clf.rndg_rl,
1677 clf.mn_comp_val,
1678 clf.mx_comp_val,
1679 clf.bnfts_bal_id,
1680 clf.defined_balance_id,
1681 clf.sttd_sal_prdcty_cd,
1682 clf.comp_lvl_fctr_id,
1683 clf.comp_calc_rl
1684 from ben_comp_lvl_fctr clf,
1685 ben_comp_lvl_rt_f clr,
1686 ben_vrbl_rt_prfl_f vpf,
1687 ben_bnft_vrbl_rt_f avr,
1688 ben_cvg_amt_calc_mthd_f abr
1689 where abr.plip_id = p_plip_id
1690 and abr.business_group_id = p_business_group_id
1691 and p_effective_date
1692 between abr.effective_start_date
1693 and abr.effective_end_date
1694 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
1695 and abr.business_group_id = avr.business_group_id
1696 and p_effective_date
1697 between avr.effective_start_date
1698 and avr.effective_end_date
1699 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1700 and avr.business_group_id = vpf.business_group_id
1701 and p_effective_date
1702 between vpf.effective_start_date
1703 and vpf.effective_end_date
1704 and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
1705 and vpf.business_group_id = clr.business_group_id
1706 and p_effective_date
1707 between clr.effective_start_date
1708 and clr.effective_end_date
1709 and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
1710 and clr.business_group_id = clf.business_group_id
1711 and ((p_new_val is not null and
1712 p_old_val is not null and
1713 p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
1714 --p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+0.001 ) )
1715 p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1716 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1717 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1718 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) )
1719 or
1720 (p_new_val is not null and
1721 p_old_val is not null and
1722 (p_new_val < nvl(clf.mn_comp_val,p_new_val) or
1723 --p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+0.001 ) and
1724 p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
1725 ,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
1726 nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
1727 nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) ) and
1731 ,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
1728 p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
1729 --p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+0.001 )
1730 p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
1732 nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
1733 nvl(clf.mx_comp_val,p_old_val)+0.000000001 ) )
1734 or
1735 (p_new_val is null and
1736 p_old_val is null));
1737 --
1738 --
1739 l_index binary_integer;
1740 l_not_hash_found boolean;
1741 --
1742 begin
1743 --
1744 -- hr_utility.set_location ('Entering '||l_package,10);
1745 --
1746 -- Steps to do process
1747 --
1748 -- 1) Try and get value from cache
1749 -- 2) If can get from cache then copy to output record
1750 -- 3) If can't get from cache do db hit and then
1751 -- copy to cache record and then copy to output record.
1752 --
1753 -- Get hashed index value
1754 --
1755 l_index := mod(p_plip_id,g_hash_key);
1756 --
1757 if not g_cache_plip_clf_rt_rec.exists(l_index) then
1758 --
1759 -- Lets store the hash value in this index
1760 --
1761 raise no_data_found;
1762 --
1763 else
1764 --
1765 -- If it does exist make sure its the right one
1766 --
1767 if g_cache_plip_clf_rt_rec(l_index).id <> p_plip_id then
1768 --
1769 -- Loop through the hash using the jump routine to check further
1770 -- indexes
1771 --
1772 l_not_hash_found := false;
1773 --
1774 while not l_not_hash_found loop
1775 --
1776 l_index := l_index+g_hash_jump;
1777 --
1778 -- Check if the hash index exists, if not we can use it
1779 --
1780 if not g_cache_plip_clf_rt_rec.exists(l_index) then
1781 --
1782 -- Lets store the hash value in the index
1783 --
1784 raise no_data_found;
1785 --
1786 else
1787 --
1788 -- Make sure the index is the correct one
1789 --
1790 if g_cache_plip_clf_rt_rec(l_index).id = p_plip_id then
1791 --
1792 -- We have a match so the hashed value has been stored before
1793 --
1794 l_not_hash_found := true;
1795 --
1796 end if;
1797 --
1798 end if;
1799 --
1800 end loop;
1801 --
1802 end if;
1803 --
1804 end if;
1805 --
1806 -- If p_old_val and p_new_val is set this means we are trying to retrieve
1807 -- the correct rate for the calculated value.
1808 -- Previously we just cached the first rate we
1809 -- found since we needed the determination code, the correct age,los code,etc
1810 -- By killing the cache and forcing the value to be removed we cache the
1811 -- correct rate profile for the case we need.
1812 --
1813 if p_old_val is not null and p_new_val is not null then
1814 --
1815 raise no_data_found;
1816 --
1817 end if;
1818 --
1819 p_rec := g_cache_plip_clf_rt_rec(l_index);
1820 --
1821 -- hr_utility.set_location ('Leaving '||l_package,10);
1822 --
1823 exception
1824 --
1825 when no_data_found then
1826 --
1827 -- The record has not been cached yet so lets cache it
1828 --
1829 open c1;
1830 --
1831 fetch c1 into g_cache_plip_clf_rt_rec(l_index);
1832 if p_old_val is null and p_new_val is null then
1833 --
1834 if c1%notfound then
1835 --
1836 g_cache_plip_clf_rt_rec(l_index).id := p_plip_id;
1837 g_cache_plip_clf_rt_rec(l_index).exist := 'N';
1838 --
1839 end if;
1840 --
1841 end if;
1842 --
1843 p_rec := g_cache_plip_clf_rt_rec(l_index);
1844 --
1845 close c1;
1846 --
1847 end get_comp_plip_rate;
1848 --
1849 procedure get_comp_rate
1850 (p_pl_id in number,
1851 p_oipl_id in number,
1852 p_plip_id in number,
1853 p_old_val in number default null,
1854 p_new_val in number default null,
1855 p_business_group_id in number,
1856 p_effective_date in date,
1857 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_clf_rec_obj) is
1858 --
1859 l_package varchar2(80) := g_package||'.get_comp_rate';
1860 --
1861 begin
1862 --
1863 -- hr_utility.set_location ('Entering '||l_package,10);
1864 --
1865 -- Derive which data type we are dealing with
1866 --
1867 if p_pl_id is not null then
1868 --
1869 get_comp_pl_rate(p_pl_id => p_pl_id,
1870 p_old_val => p_old_val,
1871 p_new_val => p_new_val,
1872 p_business_group_id => p_business_group_id,
1873 p_effective_date => p_effective_date,
1874 p_rec => p_rec);
1875 --
1876 elsif p_oipl_id is not null then
1880 p_new_val => p_new_val,
1877 --
1878 get_comp_oipl_rate(p_oipl_id => p_oipl_id,
1879 p_old_val => p_old_val,
1881 p_business_group_id => p_business_group_id,
1882 p_effective_date => p_effective_date,
1883 p_rec => p_rec);
1884 --
1885 elsif p_plip_id is not null then
1886 --
1887 get_comp_plip_rate(p_plip_id => p_plip_id,
1888 p_old_val => p_old_val,
1889 p_new_val => p_new_val,
1890 p_business_group_id => p_business_group_id,
1891 p_effective_date => p_effective_date,
1892 p_rec => p_rec);
1893 --
1894 end if;
1895 --
1896 -- hr_utility.set_location ('Leaving '||l_package,10);
1897 --
1898 end get_comp_rate;
1899 --
1900 procedure get_comb_pl_rate
1901 (p_pl_id in number,
1902 p_old_val in number default null,
1903 p_new_val in number default null,
1904 p_business_group_id in number,
1905 p_effective_date in date,
1906 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
1907 --
1908 l_package varchar2(80) := g_package||'.get_comb_pl_rate';
1909 --
1910 -- Define Cursor
1911 --
1912 cursor c1 is
1913 select p_pl_id,
1914 'Y',
1915 cla.los_fctr_id,
1916 cla.age_fctr_id,
1917 cla.cmbnd_min_val,
1918 cla.cmbnd_max_val
1919 from ben_cmbn_age_los_fctr cla,
1920 ben_cmbn_age_los_rt_f cmr,
1921 ben_vrbl_rt_prfl_f vpf,
1922 ben_bnft_vrbl_rt_f avr,
1923 ben_cvg_amt_calc_mthd_f abr
1924 where abr.pl_id = p_pl_id
1925 and abr.business_group_id = p_business_group_id
1926 and p_effective_date
1927 between abr.effective_start_date
1928 and abr.effective_end_date
1929 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
1930 and abr.business_group_id = avr.business_group_id
1931 and p_effective_date
1932 between avr.effective_start_date
1933 and avr.effective_end_date
1934 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
1935 and avr.business_group_id = vpf.business_group_id
1936 and p_effective_date
1937 between vpf.effective_start_date
1938 and vpf.effective_end_date
1939 and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
1940 and vpf.business_group_id = cmr.business_group_id
1941 and p_effective_date
1942 between cmr.effective_start_date
1943 and cmr.effective_end_date
1944 and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
1945 and cmr.business_group_id = cla.business_group_id
1946 and ((p_new_val is not null and
1947 p_old_val is not null and
1948 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
1949 --p_new_val < ceil( nvl(cla.cmbnd_max_val,p_new_val)+ 0.001 ) )
1950 p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
1951 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
1952 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
1953 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) )
1954 or
1955 (p_new_val is not null and
1956 p_old_val is not null and
1957 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
1958 --p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+0.001 ) and
1959 p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
1960 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
1961 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
1962 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) ) and
1963 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
1964 --p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+0.001 )
1965 p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
1966 ,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
1967 nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
1968 nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ) )
1969 or
1970 (p_new_val is null and
1971 p_old_val is null));
1972 --
1973 --
1974 l_index binary_integer;
1975 l_not_hash_found boolean;
1976 --
1977 begin
1978 --
1979 -- hr_utility.set_location ('Entering '||l_package,10);
1980 --
1981 -- Steps to do process
1982 --
1983 -- 1) Try and get value from cache
1984 -- 2) If can get from cache then copy to output record
1985 -- 3) If can't get from cache do db hit and then
1986 -- copy to cache record and then copy to output record.
1987 --
1988 -- Get hashed index value
1989 --
1990 l_index := mod(p_pl_id,g_hash_key);
1994 -- Lets store the hash value in this index
1991 --
1992 if not g_cache_pl_cla_rt_rec.exists(l_index) then
1993 --
1995 --
1996 raise no_data_found;
1997 --
1998 else
1999 --
2000 -- If it does exist make sure its the right one
2001 --
2002 if g_cache_pl_cla_rt_rec(l_index).id <> p_pl_id then
2003 --
2004 -- Loop through the hash using the jump routine to check further
2005 -- indexes
2006 --
2007 l_not_hash_found := false;
2008 --
2009 while not l_not_hash_found loop
2010 --
2011 l_index := l_index+g_hash_jump;
2012 --
2013 -- Check if the hash index exists, if not we can use it
2014 --
2015 if not g_cache_pl_cla_rt_rec.exists(l_index) then
2016 --
2017 -- Lets store the hash value in the index
2018 --
2019 raise no_data_found;
2020 --
2021 else
2022 --
2023 -- Make sure the index is the correct one
2024 --
2025 if g_cache_pl_cla_rt_rec(l_index).id = p_pl_id then
2026 --
2027 -- We have a match so the hashed value has been stored before
2028 --
2029 l_not_hash_found := true;
2030 --
2031 end if;
2032 --
2033 end if;
2034 --
2035 end loop;
2036 --
2037 end if;
2038 --
2039 end if;
2040 --
2041 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2042 -- the correct rate for the calculated value.
2043 -- Previously we just cached the first rate we
2044 -- found since we needed the determination code, the correct age,los code,etc
2045 -- By killing the cache and forcing the value to be removed we cache the
2046 -- correct rate profile for the case we need.
2047 --
2048 if p_old_val is not null and p_new_val is not null then
2049 --
2050 raise no_data_found;
2051 --
2052 end if;
2053 --
2054 p_rec := g_cache_pl_cla_rt_rec(l_index);
2055 --
2056 -- hr_utility.set_location ('Leaving '||l_package,10);
2057 --
2058 exception
2059 --
2060 when no_data_found then
2061 --
2062 -- The record has not been cached yet so lets cache it
2063 --
2064 open c1;
2065 --
2066 fetch c1 into g_cache_pl_cla_rt_rec(l_index);
2067 if p_old_val is null and p_new_val is null then
2068 --
2069 if c1%notfound then
2070 --
2071 g_cache_pl_cla_rt_rec(l_index).id := p_pl_id;
2072 g_cache_pl_cla_rt_rec(l_index).exist := 'N';
2073 --
2074 end if;
2075 --
2076 end if;
2077 --
2078 p_rec := g_cache_pl_cla_rt_rec(l_index);
2079 --
2080 close c1;
2081 --
2082 end get_comb_pl_rate;
2083 --
2084 procedure get_comb_oipl_rate
2085 (p_oipl_id in number,
2086 p_old_val in number default null,
2087 p_new_val in number default null,
2088 p_business_group_id in number,
2089 p_effective_date in date,
2090 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
2091 --
2092 l_package varchar2(80) := g_package||'.get_comb_oipl_rate';
2093 --
2094 -- Define Cursor
2095 --
2096 cursor c1 is
2097 select p_oipl_id,
2098 'Y',
2099 cla.los_fctr_id,
2100 cla.age_fctr_id,
2101 cla.cmbnd_min_val,
2102 cla.cmbnd_max_val
2103 from ben_cmbn_age_los_fctr cla,
2104 ben_cmbn_age_los_rt_f cmr,
2105 ben_vrbl_rt_prfl_f vpf,
2106 ben_bnft_vrbl_rt_f avr,
2107 ben_cvg_amt_calc_mthd_f abr
2108 where abr.oipl_id = p_oipl_id
2109 and abr.business_group_id = p_business_group_id
2110 and p_effective_date
2111 between abr.effective_start_date
2112 and abr.effective_end_date
2113 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
2114 and abr.business_group_id = avr.business_group_id
2115 and p_effective_date
2116 between avr.effective_start_date
2117 and avr.effective_end_date
2118 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2119 and avr.business_group_id = vpf.business_group_id
2120 and p_effective_date
2121 between vpf.effective_start_date
2122 and vpf.effective_end_date
2123 and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
2124 and vpf.business_group_id = cmr.business_group_id
2125 and p_effective_date
2126 between cmr.effective_start_date
2127 and cmr.effective_end_date
2128 and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
2129 and cmr.business_group_id = cla.business_group_id
2130 and ((p_new_val is not null and
2131 p_old_val is not null and
2132 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
2133 --p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+0.001 ) )
2134 p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
2138 or
2135 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
2136 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
2137 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) )
2139 (p_new_val is not null and
2140 p_old_val is not null and
2141 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
2142 --p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+0.001 ) and
2143 p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
2144 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
2145 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
2146 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) ) and
2147 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
2148 --p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+0.001 )
2149 p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
2150 ,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
2151 nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
2152 nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ) )
2153 or
2154 (p_new_val is null and
2155 p_old_val is null));
2156 --
2157 --
2158 l_index binary_integer;
2159 l_not_hash_found boolean;
2160 --
2161 begin
2162 --
2163 -- hr_utility.set_location ('Entering '||l_package,10);
2164 --
2165 -- Steps to do process
2166 --
2167 -- 1) Try and get value from cache
2168 -- 2) If can get from cache then copy to output record
2169 -- 3) If can't get from cache do db hit and then
2170 -- copy to cache record and then copy to output record.
2171 --
2172 -- Get hashed index value
2173 --
2174 l_index := mod(p_oipl_id,g_hash_key);
2175 --
2176 if not g_cache_oipl_cla_rt_rec.exists(l_index) then
2177 --
2178 -- Lets store the hash value in this index
2179 --
2180 raise no_data_found;
2181 --
2182 else
2183 --
2184 -- If it does exist make sure its the right one
2185 --
2186 if g_cache_oipl_cla_rt_rec(l_index).id <> p_oipl_id then
2187 --
2188 -- Loop through the hash using the jump routine to check further
2189 -- indexes
2190 --
2191 l_not_hash_found := false;
2192 --
2193 while not l_not_hash_found loop
2194 --
2195 l_index := l_index+g_hash_jump;
2196 --
2197 -- Check if the hash index exists, if not we can use it
2198 --
2199 if not g_cache_oipl_cla_rt_rec.exists(l_index) then
2200 --
2201 -- Lets store the hash value in the index
2202 --
2203 raise no_data_found;
2204 --
2205 else
2206 --
2207 -- Make sure the index is the correct one
2208 --
2209 if g_cache_oipl_cla_rt_rec(l_index).id = p_oipl_id then
2210 --
2211 -- We have a match so the hashed value has been stored before
2212 --
2213 l_not_hash_found := true;
2214 --
2215 end if;
2216 --
2217 end if;
2218 --
2219 end loop;
2220 --
2221 end if;
2222 --
2223 end if;
2224 --
2225 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2226 -- the correct rate for the calculated value.
2227 -- Previously we just cached the first rate we
2228 -- found since we needed the determination code, the correct age,los code,etc
2229 -- By killing the cache and forcing the value to be removed we cache the
2230 -- correct rate profile for the case we need.
2231 --
2232 if p_old_val is not null and p_new_val is not null then
2233 --
2234 raise no_data_found;
2235 --
2236 end if;
2237 --
2238 p_rec := g_cache_oipl_cla_rt_rec(l_index);
2239 --
2240 -- hr_utility.set_location ('Leaving '||l_package,10);
2241 --
2242 exception
2243 --
2244 when no_data_found then
2245 --
2246 -- The record has not been cached yet so lets cache it
2247 --
2248 open c1;
2249 --
2250 fetch c1 into g_cache_oipl_cla_rt_rec(l_index);
2251 if p_old_val is null and p_new_val is null then
2252 --
2253 if c1%notfound then
2254 --
2255 g_cache_oipl_cla_rt_rec(l_index).id := p_oipl_id;
2256 g_cache_oipl_cla_rt_rec(l_index).exist := 'N';
2257 --
2258 end if;
2259 --
2260 end if;
2261 --
2262 p_rec := g_cache_oipl_cla_rt_rec(l_index);
2263 --
2264 close c1;
2265 --
2266 end get_comb_oipl_rate;
2267 --
2268 procedure get_comb_plip_rate
2269 (p_plip_id in number,
2270 p_old_val in number default null,
2271 p_new_val in number default null,
2272 p_business_group_id in number,
2273 p_effective_date in date,
2277 --
2274 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
2275 --
2276 l_package varchar2(80) := g_package||'.get_comb_plip_rate';
2278 -- Define Cursor
2279 --
2280 cursor c1 is
2281 select p_plip_id,
2282 'Y',
2283 cla.los_fctr_id,
2284 cla.age_fctr_id,
2285 cla.cmbnd_min_val,
2286 cla.cmbnd_max_val
2287 from ben_cmbn_age_los_fctr cla,
2288 ben_cmbn_age_los_rt_f cmr,
2289 ben_vrbl_rt_prfl_f vpf,
2290 ben_bnft_vrbl_rt_f avr,
2291 ben_cvg_amt_calc_mthd_f abr
2292 where abr.plip_id = p_plip_id
2293 and abr.business_group_id = p_business_group_id
2294 and p_effective_date
2295 between abr.effective_start_date
2296 and abr.effective_end_date
2297 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
2298 and abr.business_group_id = avr.business_group_id
2299 and p_effective_date
2300 between avr.effective_start_date
2301 and avr.effective_end_date
2302 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2303 and avr.business_group_id = vpf.business_group_id
2304 and p_effective_date
2305 between vpf.effective_start_date
2306 and vpf.effective_end_date
2307 and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
2308 and vpf.business_group_id = cmr.business_group_id
2309 and p_effective_date
2310 between cmr.effective_start_date
2311 and cmr.effective_end_date
2312 and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
2313 and cmr.business_group_id = cla.business_group_id
2314 and ((p_new_val is not null and
2315 p_old_val is not null and
2316 p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
2317 --p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+ 0.001 ) )
2318 p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
2319 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
2320 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
2321 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) )
2322 or
2323 (p_new_val is not null and
2324 p_old_val is not null and
2325 (p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
2326 --p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+ 0.001 ) and
2327 p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
2328 ,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
2329 nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
2330 nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) ) and
2331 p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
2332 --p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+ 0.001 )
2333 p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
2334 ,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
2335 nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
2336 nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ) )
2337 or
2338 (p_new_val is null and
2339 p_old_val is null));
2340 --
2341 --
2342 l_index binary_integer;
2343 l_not_hash_found boolean;
2344 --
2345 begin
2346 --
2347 -- hr_utility.set_location ('Entering '||l_package,10);
2348 --
2349 -- Steps to do process
2350 --
2351 -- 1) Try and get value from cache
2352 -- 2) If can get from cache then copy to output record
2353 -- 3) If can't get from cache do db hit and then
2354 -- copy to cache record and then copy to output record.
2355 --
2356 -- Get hashed index value
2357 --
2358 l_index := mod(p_plip_id,g_hash_key);
2359 --
2360 if not g_cache_plip_cla_rt_rec.exists(l_index) then
2361 --
2362 -- Lets store the hash value in this index
2363 --
2364 raise no_data_found;
2365 --
2366 else
2367 --
2368 -- If it does exist make sure its the right one
2369 --
2370 if g_cache_plip_cla_rt_rec(l_index).id <> p_plip_id then
2371 --
2372 -- Loop through the hash using the jump routine to check further
2373 -- indexes
2374 --
2375 l_not_hash_found := false;
2376 --
2377 while not l_not_hash_found loop
2378 --
2379 l_index := l_index+g_hash_jump;
2380 --
2381 -- Check if the hash index exists, if not we can use it
2382 --
2383 if not g_cache_plip_cla_rt_rec.exists(l_index) then
2384 --
2385 -- Lets store the hash value in the index
2386 --
2387 raise no_data_found;
2388 --
2389 else
2390 --
2391 -- Make sure the index is the correct one
2392 --
2393 if g_cache_plip_cla_rt_rec(l_index).id = p_plip_id then
2394 --
2398 --
2395 -- We have a match so the hashed value has been stored before
2396 --
2397 l_not_hash_found := true;
2399 end if;
2400 --
2401 end if;
2402 --
2403 end loop;
2404 --
2405 end if;
2406 --
2407 end if;
2408 --
2409 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2410 -- the correct rate for the calculated value.
2411 -- Previously we just cached the first rate we
2412 -- found since we needed the determination code, the correct age,los code,etc
2413 -- By killing the cache and forcing the value to be removed we cache the
2414 -- correct rate profile for the case we need.
2415 --
2416 if p_old_val is not null and p_new_val is not null then
2417 --
2418 raise no_data_found;
2419 --
2420 end if;
2421 --
2422 p_rec := g_cache_plip_cla_rt_rec(l_index);
2423 --
2424 -- hr_utility.set_location ('Leaving '||l_package,10);
2425 --
2426 exception
2427 --
2428 when no_data_found then
2429 --
2430 -- The record has not been cached yet so lets cache it
2431 --
2432 open c1;
2433 --
2434 fetch c1 into g_cache_plip_cla_rt_rec(l_index);
2435 if p_old_val is null and p_new_val is null then
2436 --
2437 if c1%notfound then
2438 --
2439 g_cache_plip_cla_rt_rec(l_index).id := p_plip_id;
2440 g_cache_plip_cla_rt_rec(l_index).exist := 'N';
2441 --
2442 end if;
2443 --
2444 end if;
2445 --
2446 p_rec := g_cache_plip_cla_rt_rec(l_index);
2447 --
2448 close c1;
2449 --
2450 end get_comb_plip_rate;
2451 --
2452 procedure get_comb_rate
2453 (p_pl_id in number,
2454 p_oipl_id in number,
2455 p_plip_id in number,
2456 p_old_val in number default null,
2457 p_new_val in number default null,
2458 p_business_group_id in number,
2459 p_effective_date in date,
2460 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_cla_rec_obj) is
2461 --
2462 l_package varchar2(80) := g_package||'.get_comb_rate';
2463 --
2464 begin
2465 --
2466 -- hr_utility.set_location ('Entering '||l_package,10);
2467 --
2468 -- Derive which data type we are dealing with
2469 --
2470 if p_pl_id is not null then
2471 --
2472 get_comb_pl_rate(p_pl_id => p_pl_id,
2473 p_old_val => p_old_val,
2474 p_new_val => p_new_val,
2475 p_business_group_id => p_business_group_id,
2476 p_effective_date => p_effective_date,
2477 p_rec => p_rec);
2478 --
2479 elsif p_oipl_id is not null then
2480 --
2481 get_comb_oipl_rate(p_oipl_id => p_oipl_id,
2482 p_old_val => p_old_val,
2483 p_new_val => p_new_val,
2484 p_business_group_id => p_business_group_id,
2485 p_effective_date => p_effective_date,
2486 p_rec => p_rec);
2487 --
2488 elsif p_plip_id is not null then
2489 --
2490 get_comb_plip_rate(p_plip_id => p_plip_id,
2491 p_old_val => p_old_val,
2492 p_new_val => p_new_val,
2493 p_business_group_id => p_business_group_id,
2494 p_effective_date => p_effective_date,
2495 p_rec => p_rec);
2496 --
2497 end if;
2498 --
2499 -- hr_utility.set_location ('Leaving '||l_package,10);
2500 --
2501 end get_comb_rate;
2502 --
2503 procedure get_pct_pl_rate
2504 (p_pl_id in number,
2505 p_old_val in number default null,
2506 p_new_val in number default null,
2507 p_business_group_id in number,
2508 p_effective_date in date,
2509 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
2510 --
2511 l_package varchar2(80) := g_package||'.get_pct_pl_rate';
2512 --
2513 -- Define Cursor
2514 --
2515 cursor c1 is
2516 select p_pl_id,
2517 'Y',
2518 pff.use_prmry_asnt_only_flag,
2519 pff.use_sum_of_all_asnts_flag,
2520 pff.rndg_cd,
2521 pff.rndg_rl,
2522 pff.mn_pct_val,
2523 pff.mx_pct_val
2524 from ben_pct_fl_tm_fctr pff,
2525 ben_pct_fl_tm_rt_f pfr,
2526 ben_vrbl_rt_prfl_f vpf,
2527 ben_bnft_vrbl_rt_f avr,
2528 ben_cvg_amt_calc_mthd_f abr
2529 where abr.pl_id = p_pl_id
2530 and abr.business_group_id = p_business_group_id
2531 and p_effective_date
2532 between abr.effective_start_date
2533 and abr.effective_end_date
2534 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
2535 and abr.business_group_id = avr.business_group_id
2536 and p_effective_date
2540 and avr.business_group_id = vpf.business_group_id
2537 between avr.effective_start_date
2538 and avr.effective_end_date
2539 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2541 and p_effective_date
2542 between vpf.effective_start_date
2543 and vpf.effective_end_date
2544 and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
2545 and vpf.business_group_id = pfr.business_group_id
2546 and p_effective_date
2547 between pfr.effective_start_date
2548 and pfr.effective_end_date
2549 and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
2550 and pfr.business_group_id = pff.business_group_id
2551 and ((p_new_val is not null and
2552 p_old_val is not null and
2553 p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
2554 (p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
2555 or
2556 (p_new_val is not null and
2557 p_old_val is not null and
2558 (p_new_val < nvl(pff.mn_pct_val,p_new_val) or
2559 (p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
2560 p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
2561 (p_old_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
2562 or
2563 (p_new_val is null and
2564 p_old_val is null));
2565 --
2566 --
2567 l_index binary_integer;
2568 l_not_hash_found boolean;
2569 --
2570 begin
2571 --
2572 -- hr_utility.set_location ('Entering '||l_package,10);
2573 --
2574 -- Steps to do process
2575 --
2576 -- 1) Try and get value from cache
2577 -- 2) If can get from cache then copy to output record
2578 -- 3) If can't get from cache do db hit and then
2579 -- copy to cache record and then copy to output record.
2580 --
2581 -- Get hashed index value
2582 --
2583 l_index := mod(p_pl_id,g_hash_key);
2584 --
2585 if not g_cache_pl_pff_rt_rec.exists(l_index) then
2586 --
2587 -- Lets store the hash value in this index
2588 --
2589 raise no_data_found;
2590 --
2591 else
2592 --
2593 -- If it does exist make sure its the right one
2594 --
2595 if g_cache_pl_pff_rt_rec(l_index).id <> p_pl_id then
2596 --
2597 -- Loop through the hash using the jump routine to check further
2598 -- indexes
2599 --
2600 l_not_hash_found := false;
2601 --
2602 while not l_not_hash_found loop
2603 --
2604 l_index := l_index+g_hash_jump;
2605 --
2606 -- Check if the hash index exists, if not we can use it
2607 --
2608 if not g_cache_pl_pff_rt_rec.exists(l_index) then
2609 --
2610 -- Lets store the hash value in the index
2611 --
2612 raise no_data_found;
2613 --
2614 else
2615 --
2616 -- Make sure the index is the correct one
2617 --
2618 if g_cache_pl_pff_rt_rec(l_index).id = p_pl_id then
2619 --
2620 -- We have a match so the hashed value has been stored before
2621 --
2622 l_not_hash_found := true;
2623 --
2624 end if;
2625 --
2626 end if;
2627 --
2628 end loop;
2629 --
2630 end if;
2631 --
2632 end if;
2633 --
2634 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2635 -- the correct rate for the calculated value.
2636 -- Previously we just cached the first rate we
2637 -- found since we needed the determination code, the correct age,los code,etc
2638 -- By killing the cache and forcing the value to be removed we cache the
2639 -- correct rate profile for the case we need.
2640 --
2641 if p_old_val is not null and p_new_val is not null then
2642 --
2643 raise no_data_found;
2644 --
2645 end if;
2646 --
2647 p_rec := g_cache_pl_pff_rt_rec(l_index);
2648 --
2649 -- hr_utility.set_location ('Leaving '||l_package,10);
2650 --
2651 exception
2652 --
2653 when no_data_found then
2654 --
2655 -- The record has not been cached yet so lets cache it
2656 --
2657 open c1;
2658 --
2659 fetch c1 into g_cache_pl_pff_rt_rec(l_index);
2660 if p_old_val is null and p_new_val is null then
2661 --
2662 if c1%notfound then
2663 --
2664 g_cache_pl_pff_rt_rec(l_index).id := p_pl_id;
2665 g_cache_pl_pff_rt_rec(l_index).exist := 'N';
2666 --
2667 end if;
2668 --
2669 end if;
2670 --
2671 p_rec := g_cache_pl_pff_rt_rec(l_index);
2672 --
2673 close c1;
2674 --
2675 end get_pct_pl_rate;
2676 --
2677 procedure get_pct_oipl_rate
2678 (p_oipl_id in number,
2679 p_old_val in number default null,
2680 p_new_val in number default null,
2681 p_business_group_id in number,
2682 p_effective_date in date,
2683 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
2684 --
2688 --
2685 l_package varchar2(80) := g_package||'.get_pct_oipl_rate';
2686 --
2687 -- Define Cursor
2689 cursor c1 is
2690 select p_oipl_id,
2691 'Y',
2692 pff.use_prmry_asnt_only_flag,
2693 pff.use_sum_of_all_asnts_flag,
2694 pff.rndg_cd,
2695 pff.rndg_rl,
2696 pff.mn_pct_val,
2697 pff.mx_pct_val
2698 from ben_pct_fl_tm_fctr pff,
2699 ben_pct_fl_tm_rt_f pfr,
2700 ben_vrbl_rt_prfl_f vpf,
2701 ben_bnft_vrbl_rt_f avr,
2702 ben_cvg_amt_calc_mthd_f abr
2703 where abr.oipl_id = p_oipl_id
2704 and abr.business_group_id = p_business_group_id
2705 and p_effective_date
2706 between abr.effective_start_date
2707 and abr.effective_end_date
2708 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
2709 and abr.business_group_id = avr.business_group_id
2710 and p_effective_date
2711 between avr.effective_start_date
2712 and avr.effective_end_date
2713 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2714 and avr.business_group_id = vpf.business_group_id
2715 and p_effective_date
2716 between vpf.effective_start_date
2717 and vpf.effective_end_date
2718 and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
2719 and vpf.business_group_id = pfr.business_group_id
2720 and p_effective_date
2721 between pfr.effective_start_date
2722 and pfr.effective_end_date
2723 and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
2724 and pfr.business_group_id = pff.business_group_id
2725 and ((p_new_val is not null and
2726 p_old_val is not null and
2727 p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
2728 (p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
2729 or
2730 (p_new_val is not null and
2731 p_old_val is not null and
2732 (p_new_val < nvl(pff.mn_pct_val,p_new_val) or
2733 (p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
2734 p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
2735 (p_old_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
2736 or
2737 (p_new_val is null and
2738 p_old_val is null));
2739 --
2740 --
2741 l_index binary_integer;
2742 l_not_hash_found boolean;
2743 --
2744 begin
2745 --
2746 -- hr_utility.set_location ('Entering '||l_package,10);
2747 --
2748 -- Steps to do process
2749 --
2750 -- 1) Try and get value from cache
2751 -- 2) If can get from cache then copy to output record
2752 -- 3) If can't get from cache do db hit and then
2753 -- copy to cache record and then copy to output record.
2754 --
2755 -- Get hashed index value
2756 --
2757 l_index := mod(p_oipl_id,g_hash_key);
2758 --
2759 if not g_cache_oipl_pff_rt_rec.exists(l_index) then
2760 --
2761 -- Lets store the hash value in this index
2762 --
2763 raise no_data_found;
2764 --
2765 else
2766 --
2767 -- If it does exist make sure its the right one
2768 --
2769 if g_cache_oipl_pff_rt_rec(l_index).id <> p_oipl_id then
2770 --
2771 -- Loop through the hash using the jump routine to check further
2772 -- indexes
2773 --
2774 l_not_hash_found := false;
2775 --
2776 while not l_not_hash_found loop
2777 --
2778 l_index := l_index+g_hash_jump;
2779 --
2780 -- Check if the hash index exists, if not we can use it
2781 --
2782 if not g_cache_oipl_pff_rt_rec.exists(l_index) then
2783 --
2784 -- Lets store the hash value in the index
2785 --
2786 raise no_data_found;
2787 --
2788 else
2789 --
2790 -- Make sure the index is the correct one
2791 --
2792 if g_cache_oipl_pff_rt_rec(l_index).id = p_oipl_id then
2793 --
2794 -- We have a match so the hashed value has been stored before
2795 --
2796 l_not_hash_found := true;
2797 --
2798 end if;
2799 --
2800 end if;
2801 --
2802 end loop;
2803 --
2804 end if;
2805 --
2806 end if;
2807 --
2808 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2809 -- the correct rate for the calculated value.
2810 -- Previously we just cached the first rate we
2811 -- found since we needed the determination code, the correct age,los code,etc
2812 -- By killing the cache and forcing the value to be removed we cache the
2813 -- correct rate profile for the case we need.
2814 --
2815 if p_old_val is not null and p_new_val is not null then
2816 --
2817 raise no_data_found;
2818 --
2819 end if;
2820 --
2821 p_rec := g_cache_oipl_pff_rt_rec(l_index);
2822 --
2823 -- hr_utility.set_location ('Leaving '||l_package,10);
2824 --
2825 exception
2826 --
2827 when no_data_found then
2828 --
2829 -- The record has not been cached yet so lets cache it
2830 --
2834 if p_old_val is null and p_new_val is null then
2831 open c1;
2832 --
2833 fetch c1 into g_cache_oipl_pff_rt_rec(l_index);
2835 --
2836 if c1%notfound then
2837 --
2838 g_cache_oipl_pff_rt_rec(l_index).id := p_oipl_id;
2839 g_cache_oipl_pff_rt_rec(l_index).exist := 'N';
2840 --
2841 end if;
2842 --
2843 end if;
2844 --
2845 p_rec := g_cache_oipl_pff_rt_rec(l_index);
2846 --
2847 close c1;
2848 --
2849 end get_pct_oipl_rate;
2850 --
2851 procedure get_pct_plip_rate
2852 (p_plip_id in number,
2853 p_old_val in number default null,
2854 p_new_val in number default null,
2855 p_business_group_id in number,
2856 p_effective_date in date,
2857 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
2858 --
2859 l_package varchar2(80) := g_package||'.get_pct_plip_rate';
2860 --
2861 -- Define Cursor
2862 --
2863 cursor c1 is
2864 select p_plip_id,
2865 'Y',
2866 pff.use_prmry_asnt_only_flag,
2867 pff.use_sum_of_all_asnts_flag,
2868 pff.rndg_cd,
2869 pff.rndg_rl,
2870 pff.mn_pct_val,
2871 pff.mx_pct_val
2872 from ben_pct_fl_tm_fctr pff,
2873 ben_pct_fl_tm_rt_f pfr,
2874 ben_vrbl_rt_prfl_f vpf,
2875 ben_bnft_vrbl_rt_f avr,
2876 ben_cvg_amt_calc_mthd_f abr
2877 where abr.plip_id = p_plip_id
2878 and abr.business_group_id = p_business_group_id
2879 and p_effective_date
2880 between abr.effective_start_date
2881 and abr.effective_end_date
2882 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
2883 and abr.business_group_id = avr.business_group_id
2884 and p_effective_date
2885 between avr.effective_start_date
2886 and avr.effective_end_date
2887 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
2888 and avr.business_group_id = vpf.business_group_id
2889 and p_effective_date
2890 between vpf.effective_start_date
2891 and vpf.effective_end_date
2892 and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
2893 and vpf.business_group_id = pfr.business_group_id
2894 and p_effective_date
2895 between pfr.effective_start_date
2896 and pfr.effective_end_date
2897 and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
2898 and pfr.business_group_id = pff.business_group_id
2899 and ((p_new_val is not null and
2900 p_old_val is not null and
2901 p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
2902 (p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
2903 or
2904 (p_new_val is not null and
2905 p_old_val is not null and
2906 (p_new_val < nvl(pff.mn_pct_val,p_new_val) or
2907 (p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
2908 p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
2909 (p_new_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
2910 or
2911 (p_new_val is null and
2912 p_old_val is null));
2913 --
2914 --
2915 l_index binary_integer;
2916 l_not_hash_found boolean;
2917 --
2918 begin
2919 --
2920 -- hr_utility.set_location ('Entering '||l_package,10);
2921 --
2922 -- Steps to do process
2923 --
2924 -- 1) Try and get value from cache
2925 -- 2) If can get from cache then copy to output record
2926 -- 3) If can't get from cache do db hit and then
2927 -- copy to cache record and then copy to output record.
2928 --
2929 -- Get hashed index value
2930 --
2931 l_index := mod(p_plip_id,g_hash_key);
2932 --
2933 if not g_cache_plip_pff_rt_rec.exists(l_index) then
2934 --
2935 -- Lets store the hash value in this index
2936 --
2937 raise no_data_found;
2938 --
2939 else
2940 --
2941 -- If it does exist make sure its the right one
2942 --
2943 if g_cache_plip_pff_rt_rec(l_index).id <> p_plip_id then
2944 --
2945 -- Loop through the hash using the jump routine to check further
2946 -- indexes
2947 --
2948 l_not_hash_found := false;
2949 --
2950 while not l_not_hash_found loop
2951 --
2952 l_index := l_index+g_hash_jump;
2953 --
2954 -- Check if the hash index exists, if not we can use it
2955 --
2956 if not g_cache_plip_pff_rt_rec.exists(l_index) then
2957 --
2958 -- Lets store the hash value in the index
2959 --
2960 raise no_data_found;
2961 --
2962 else
2963 --
2964 -- Make sure the index is the correct one
2965 --
2966 if g_cache_plip_pff_rt_rec(l_index).id = p_plip_id then
2967 --
2968 -- We have a match so the hashed value has been stored before
2969 --
2970 l_not_hash_found := true;
2974 end if;
2971 --
2972 end if;
2973 --
2975 --
2976 end loop;
2977 --
2978 end if;
2979 --
2980 end if;
2981 --
2982 -- If p_old_val and p_new_val is set this means we are trying to retrieve
2983 -- the correct rate for the calculated value.
2984 -- Previously we just cached the first rate we
2985 -- found since we needed the determination code, the correct age,los code,etc
2986 -- By killing the cache and forcing the value to be removed we cache the
2987 -- correct rate profile for the case we need.
2988 --
2989 if p_old_val is not null and p_new_val is not null then
2990 --
2991 raise no_data_found;
2992 --
2993 end if;
2994 --
2995 p_rec := g_cache_plip_pff_rt_rec(l_index);
2996 --
2997 -- hr_utility.set_location ('Leaving '||l_package,10);
2998 --
2999 exception
3000 --
3001 when no_data_found then
3002 --
3003 -- The record has not been cached yet so lets cache it
3004 --
3005 open c1;
3006 --
3007 fetch c1 into g_cache_plip_pff_rt_rec(l_index);
3008 if p_old_val is null and p_new_val is null then
3009 --
3010 if c1%notfound then
3011 --
3012 g_cache_plip_pff_rt_rec(l_index).id := p_plip_id;
3013 g_cache_plip_pff_rt_rec(l_index).exist := 'N';
3014 --
3015 end if;
3016 --
3017 end if;
3018 --
3019 p_rec := g_cache_plip_pff_rt_rec(l_index);
3020 --
3021 close c1;
3022 --
3023 end get_pct_plip_rate;
3024 --
3025 procedure get_pct_rate
3026 (p_pl_id in number,
3027 p_oipl_id in number,
3028 p_plip_id in number,
3029 p_old_val in number default null,
3030 p_new_val in number default null,
3031 p_business_group_id in number,
3032 p_effective_date in date,
3033 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_pff_rec_obj) is
3034 --
3035 l_package varchar2(80) := g_package||'.get_pct_rate';
3036 --
3037 begin
3038 --
3039 -- hr_utility.set_location ('Entering '||l_package,10);
3040 --
3041 -- Derive which data type we are dealing with
3042 --
3043 if p_pl_id is not null then
3044 --
3045 get_pct_pl_rate(p_pl_id => p_pl_id,
3046 p_old_val => p_old_val,
3047 p_new_val => p_new_val,
3048 p_business_group_id => p_business_group_id,
3049 p_effective_date => p_effective_date,
3050 p_rec => p_rec);
3051 --
3052 elsif p_oipl_id is not null then
3053 --
3054 get_pct_oipl_rate(p_oipl_id => p_oipl_id,
3055 p_old_val => p_old_val,
3056 p_new_val => p_new_val,
3057 p_business_group_id => p_business_group_id,
3058 p_effective_date => p_effective_date,
3059 p_rec => p_rec);
3060 --
3061 elsif p_plip_id is not null then
3062 --
3063 get_pct_plip_rate(p_plip_id => p_plip_id,
3064 p_old_val => p_old_val,
3065 p_new_val => p_new_val,
3066 p_business_group_id => p_business_group_id,
3067 p_effective_date => p_effective_date,
3068 p_rec => p_rec);
3069 --
3070 end if;
3071 --
3072 -- hr_utility.set_location ('Leaving '||l_package,10);
3073 --
3074 end get_pct_rate;
3075 --
3076 procedure get_hours_pl_rate
3077 (p_pl_id in number,
3078 p_old_val in number default null,
3079 p_new_val in number default null,
3080 p_business_group_id in number,
3081 p_effective_date in date,
3082 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
3083 --
3084 l_package varchar2(80) := g_package||'.get_hours_pl_rate';
3085 --
3086 -- Define Cursor
3087 --
3088 cursor c1 is
3089 select p_pl_id,
3090 'Y',
3091 hwf.hrs_src_cd,
3092 hwf.hrs_wkd_det_cd,
3093 hwf.hrs_wkd_det_rl,
3094 hwf.rndg_cd,
3095 hwf.rndg_rl,
3096 hwf.defined_balance_id,
3097 hwf.bnfts_bal_id,
3098 hwf.mn_hrs_num,
3099 hwf.mx_hrs_num,
3100 hwf.once_r_cntug_cd,
3101 hwf.hrs_wkd_calc_rl
3102 from ben_hrs_wkd_in_perd_fctr hwf,
3103 ben_hrs_wkd_in_perd_rt_f hwr,
3104 ben_vrbl_rt_prfl_f vpf,
3105 ben_bnft_vrbl_rt_f avr,
3106 ben_cvg_amt_calc_mthd_f abr
3107 where abr.pl_id = p_pl_id
3108 and abr.business_group_id = p_business_group_id
3109 and p_effective_date
3110 between abr.effective_start_date
3111 and abr.effective_end_date
3112 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
3113 and abr.business_group_id = avr.business_group_id
3114 and p_effective_date
3115 between avr.effective_start_date
3119 and p_effective_date
3116 and avr.effective_end_date
3117 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
3118 and avr.business_group_id = vpf.business_group_id
3120 between vpf.effective_start_date
3121 and vpf.effective_end_date
3122 and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
3123 and vpf.business_group_id = hwr.business_group_id
3124 and p_effective_date
3125 between hwr.effective_start_date
3126 and hwr.effective_end_date
3127 and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
3128 and hwr.business_group_id = hwf.business_group_id
3129 and ((p_new_val is not null and
3130 p_old_val is not null and
3131 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
3132 --p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+0.001) )
3133 p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
3134 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
3135 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
3136 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) )
3137 or
3138 (p_new_val is not null and
3139 p_old_val is not null and
3140 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
3141 --p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001) and
3142 p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
3143 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
3144 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
3145 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) ) and
3146 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
3147 --p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val)) +0.001)
3148 p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
3149 ,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
3150 nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
3151 nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ) )
3152 or
3153 (p_new_val is null and
3154 p_old_val is null));
3155 --
3156 --
3157 l_index binary_integer;
3158 l_not_hash_found boolean;
3159 --
3160 begin
3161 --
3162 -- hr_utility.set_location ('Entering '||l_package,10);
3163 --
3164 -- Steps to do process
3165 --
3166 -- 1) Try and get value from cache
3167 -- 2) If can get from cache then copy to output record
3168 -- 3) If can't get from cache do db hit and then
3169 -- copy to cache record and then copy to output record.
3170 --
3171 -- Get hashed index value
3172 --
3173 l_index := mod(p_pl_id,g_hash_key);
3174 --
3175 if not g_cache_pl_hwf_rt_rec.exists(l_index) then
3176 --
3177 -- Lets store the hash value in this index
3178 --
3179 raise no_data_found;
3180 --
3181 else
3182 --
3183 -- If it does exist make sure its the right one
3184 --
3185 if g_cache_pl_hwf_rt_rec(l_index).id <> p_pl_id then
3186 --
3187 -- Loop through the hash using the jump routine to check further
3188 -- indexes
3189 --
3190 l_not_hash_found := false;
3191 --
3192 while not l_not_hash_found loop
3193 --
3194 l_index := l_index+g_hash_jump;
3195 --
3196 -- Check if the hash index exists, if not we can use it
3197 --
3198 if not g_cache_pl_hwf_rt_rec.exists(l_index) then
3199 --
3200 -- Lets store the hash value in the index
3201 --
3202 raise no_data_found;
3203 --
3204 else
3205 --
3206 -- Make sure the index is the correct one
3207 --
3208 if g_cache_pl_hwf_rt_rec(l_index).id = p_pl_id then
3209 --
3210 -- We have a match so the hashed value has been stored before
3211 --
3212 l_not_hash_found := true;
3213 --
3214 end if;
3215 --
3216 end if;
3217 --
3218 end loop;
3219 --
3220 end if;
3221 --
3222 end if;
3223 --
3224 -- If p_old_val and p_new_val is set this means we are trying to retrieve
3225 -- the correct rate for the calculated value.
3226 -- Previously we just cached the first rate we
3227 -- found since we needed the determination code, the correct age,los code,etc
3228 -- By killing the cache and forcing the value to be removed we cache the
3229 -- correct rate profile for the case we need.
3230 --
3231 if p_old_val is not null and p_new_val is not null then
3232 --
3233 raise no_data_found;
3234 --
3235 end if;
3236 --
3237 p_rec := g_cache_pl_hwf_rt_rec(l_index);
3238 --
3239 -- hr_utility.set_location ('Leaving '||l_package,10);
3240 --
3241 exception
3242 --
3243 when no_data_found then
3244 --
3248 --
3245 -- The record has not been cached yet so lets cache it
3246 --
3247 open c1;
3249 fetch c1 into g_cache_pl_hwf_rt_rec(l_index);
3250 if p_old_val is null and p_new_val is null then
3251 --
3252 if c1%notfound then
3253 --
3254 g_cache_pl_hwf_rt_rec(l_index).id := p_pl_id;
3255 g_cache_pl_hwf_rt_rec(l_index).exist := 'N';
3256 --
3257 end if;
3258 --
3259 end if;
3260 --
3261 p_rec := g_cache_pl_hwf_rt_rec(l_index);
3262 --
3263 close c1;
3264 --
3265 end get_hours_pl_rate;
3266 --
3267 procedure get_hours_oipl_rate
3268 (p_oipl_id in number,
3269 p_old_val in number default null,
3270 p_new_val in number default null,
3271 p_business_group_id in number,
3272 p_effective_date in date,
3273 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
3274 --
3275 l_package varchar2(80) := g_package||'.get_hours_oipl_rate';
3276 --
3277 -- Define Cursor
3278 --
3279 cursor c1 is
3280 select p_oipl_id,
3281 'Y',
3282 hwf.hrs_src_cd,
3283 hwf.hrs_wkd_det_cd,
3284 hwf.hrs_wkd_det_rl,
3285 hwf.rndg_cd,
3286 hwf.rndg_rl,
3287 hwf.defined_balance_id,
3288 hwf.bnfts_bal_id,
3289 hwf.mn_hrs_num,
3290 hwf.mx_hrs_num,
3291 hwf.once_r_cntug_cd,
3292 hwf.hrs_wkd_calc_rl
3293 from ben_hrs_wkd_in_perd_fctr hwf,
3294 ben_hrs_wkd_in_perd_rt_f hwr,
3295 ben_vrbl_rt_prfl_f vpf,
3296 ben_bnft_vrbl_rt_f avr,
3297 ben_cvg_amt_calc_mthd_f abr
3298 where abr.oipl_id = p_oipl_id
3299 and abr.business_group_id = p_business_group_id
3300 and p_effective_date
3301 between abr.effective_start_date
3302 and abr.effective_end_date
3303 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
3304 and abr.business_group_id = avr.business_group_id
3305 and p_effective_date
3306 between avr.effective_start_date
3307 and avr.effective_end_date
3308 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
3309 and avr.business_group_id = vpf.business_group_id
3310 and p_effective_date
3311 between vpf.effective_start_date
3312 and vpf.effective_end_date
3313 and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
3314 and vpf.business_group_id = hwr.business_group_id
3315 and p_effective_date
3316 between hwr.effective_start_date
3317 and hwr.effective_end_date
3318 and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
3319 and hwr.business_group_id = hwf.business_group_id
3320 and ((p_new_val is not null and
3321 p_old_val is not null and
3322 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
3323 --p_new_val < ceil( nvl(hwf.mx_hrs_num,p_new_val) +0.001))
3324 p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
3325 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
3326 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
3327 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) )
3328 or
3329 (p_new_val is not null and
3330 p_old_val is not null and
3331 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
3332 --p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001) and
3333 p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
3334 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
3335 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
3336 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) ) and
3337 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
3338 --p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+0.001)
3339 p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
3340 ,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
3341 nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
3342 nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ) )
3343 or
3344 (p_new_val is null and
3345 p_old_val is null));
3346 --
3347 --
3348 l_index binary_integer;
3349 l_not_hash_found boolean;
3350 --
3351 begin
3352 --
3353 -- hr_utility.set_location ('Entering '||l_package,10);
3354 --
3355 -- Steps to do process
3356 --
3357 -- 1) Try and get value from cache
3358 -- 2) If can get from cache then copy to output record
3359 -- 3) If can't get from cache do db hit and then
3360 -- copy to cache record and then copy to output record.
3361 --
3362 -- Get hashed index value
3363 --
3364 l_index := mod(p_oipl_id,g_hash_key);
3365 --
3366 if not g_cache_oipl_hwf_rt_rec.exists(l_index) then
3370 raise no_data_found;
3367 --
3368 -- Lets store the hash value in this index
3369 --
3371 --
3372 else
3373 --
3374 -- If it does exist make sure its the right one
3375 --
3376 if g_cache_oipl_hwf_rt_rec(l_index).id <> p_oipl_id then
3377 --
3378 -- Loop through the hash using the jump routine to check further
3379 -- indexes
3380 --
3381 l_not_hash_found := false;
3382 --
3383 while not l_not_hash_found loop
3384 --
3385 l_index := l_index+g_hash_jump;
3386 --
3387 -- Check if the hash index exists, if not we can use it
3388 --
3389 if not g_cache_oipl_hwf_rt_rec.exists(l_index) then
3390 --
3391 -- Lets store the hash value in the index
3392 --
3393 raise no_data_found;
3394 --
3395 else
3396 --
3397 -- Make sure the index is the correct one
3398 --
3399 if g_cache_oipl_hwf_rt_rec(l_index).id = p_oipl_id then
3400 --
3401 -- We have a match so the hashed value has been stored before
3402 --
3403 l_not_hash_found := true;
3404 --
3405 end if;
3406 --
3407 end if;
3408 --
3409 end loop;
3410 --
3411 end if;
3412 --
3413 end if;
3414 --
3415 -- If p_old_val and p_new_val is set this means we are trying to retrieve
3416 -- the correct rate for the calculated value.
3417 -- Previously we just cached the first rate we
3418 -- found since we needed the determination code, the correct age,los code,etc
3419 -- By killing the cache and forcing the value to be removed we cache the
3420 -- correct rate profile for the case we need.
3421 --
3422 if p_old_val is not null and p_new_val is not null then
3423 --
3424 raise no_data_found;
3425 --
3426 end if;
3427 --
3428 p_rec := g_cache_oipl_hwf_rt_rec(l_index);
3429 --
3430 -- hr_utility.set_location ('Leaving '||l_package,10);
3431 --
3432 exception
3433 --
3434 when no_data_found then
3435 --
3436 -- The record has not been cached yet so lets cache it
3437 --
3438 open c1;
3439 --
3440 fetch c1 into g_cache_oipl_hwf_rt_rec(l_index);
3441 if p_old_val is null and p_new_val is null then
3442 --
3443 if c1%notfound then
3444 --
3445 g_cache_oipl_hwf_rt_rec(l_index).id := p_oipl_id;
3446 g_cache_oipl_hwf_rt_rec(l_index).exist := 'N';
3447 --
3448 end if;
3449 --
3450 end if;
3451 --
3452 p_rec := g_cache_oipl_hwf_rt_rec(l_index);
3453 --
3454 close c1;
3455 --
3456 end get_hours_oipl_rate;
3457 --
3458 procedure get_hours_plip_rate
3459 (p_plip_id in number,
3460 p_old_val in number default null,
3461 p_new_val in number default null,
3462 p_business_group_id in number,
3463 p_effective_date in date,
3464 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
3465 --
3466 l_package varchar2(80) := g_package||'.get_hours_plip_rate';
3467 --
3468 -- Define Cursor
3469 --
3470 cursor c1 is
3471 select p_plip_id,
3472 'Y',
3473 hwf.hrs_src_cd,
3474 hwf.hrs_wkd_det_cd,
3475 hwf.hrs_wkd_det_rl,
3476 hwf.rndg_cd,
3477 hwf.rndg_rl,
3478 hwf.defined_balance_id,
3479 hwf.bnfts_bal_id,
3480 hwf.mn_hrs_num,
3481 hwf.mx_hrs_num,
3482 hwf.once_r_cntug_cd,
3483 hwf.hrs_wkd_calc_rl
3484 from ben_hrs_wkd_in_perd_fctr hwf,
3485 ben_hrs_wkd_in_perd_rt_f hwr,
3486 ben_vrbl_rt_prfl_f vpf,
3487 ben_bnft_vrbl_rt_f avr,
3488 ben_cvg_amt_calc_mthd_f abr
3489 where abr.plip_id = p_plip_id
3490 and abr.business_group_id = p_business_group_id
3491 and p_effective_date
3492 between abr.effective_start_date
3493 and abr.effective_end_date
3494 and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
3495 and abr.business_group_id = avr.business_group_id
3496 and p_effective_date
3497 between avr.effective_start_date
3498 and avr.effective_end_date
3499 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
3500 and avr.business_group_id = vpf.business_group_id
3501 and p_effective_date
3502 between vpf.effective_start_date
3503 and vpf.effective_end_date
3504 and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
3505 and vpf.business_group_id = hwr.business_group_id
3506 and p_effective_date
3507 between hwr.effective_start_date
3508 and hwr.effective_end_date
3509 and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
3510 and hwr.business_group_id = hwf.business_group_id
3511 and ((p_new_val is not null and
3512 p_old_val is not null and
3513 p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
3517 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
3514 --p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+0.001 ) )
3515 p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
3516 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
3518 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) )
3519 or
3520 (p_new_val is not null and
3521 p_old_val is not null and
3522 (p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
3523 --p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001 ) and
3524 p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
3525 ,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
3526 nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
3527 nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) ) and
3528 p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
3529 --p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+0.001 )
3530 p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
3531 ,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
3532 nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
3533 nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ) )
3534 or
3535 (p_new_val is null and
3536 p_old_val is null));
3537 --
3538 --
3539 l_index binary_integer;
3540 l_not_hash_found boolean;
3541 --
3542 begin
3543 --
3544 -- hr_utility.set_location ('Entering '||l_package,10);
3545 --
3546 -- Steps to do process
3547 --
3548 -- 1) Try and get value from cache
3549 -- 2) If can get from cache then copy to output record
3550 -- 3) If can't get from cache do db hit and then
3551 -- copy to cache record and then copy to output record.
3552 --
3553 -- Get hashed index value
3554 --
3555 l_index := mod(p_plip_id,g_hash_key);
3556 --
3557 if not g_cache_plip_hwf_rt_rec.exists(l_index) then
3558 --
3559 -- Lets store the hash value in this index
3560 --
3561 raise no_data_found;
3562 --
3563 else
3564 --
3565 -- If it does exist make sure its the right one
3566 --
3567 if g_cache_plip_hwf_rt_rec(l_index).id <> p_plip_id then
3568 --
3569 -- Loop through the hash using the jump routine to check further
3570 -- indexes
3571 --
3572 l_not_hash_found := false;
3573 --
3574 while not l_not_hash_found loop
3575 --
3576 l_index := l_index+g_hash_jump;
3577 --
3578 -- Check if the hash index exists, if not we can use it
3579 --
3580 if not g_cache_plip_hwf_rt_rec.exists(l_index) then
3581 --
3582 -- Lets store the hash value in the index
3583 --
3584 raise no_data_found;
3585 --
3586 else
3587 --
3588 -- Make sure the index is the correct one
3589 --
3590 if g_cache_plip_hwf_rt_rec(l_index).id = p_plip_id then
3591 --
3592 -- We have a match so the hashed value has been stored before
3593 --
3594 l_not_hash_found := true;
3595 --
3596 end if;
3597 --
3598 end if;
3599 --
3600 end loop;
3601 --
3602 end if;
3603 --
3604 end if;
3605 --
3606 -- If p_old_val and p_new_val is set this means we are trying to retrieve
3607 -- the correct rate for the calculated value.
3608 -- Previously we just cached the first rate we
3609 -- found since we needed the determination code, the correct age,los code,etc
3610 -- By killing the cache and forcing the value to be removed we cache the
3611 -- correct rate profile for the case we need.
3612 --
3613 if p_old_val is not null and p_new_val is not null then
3614 --
3615 raise no_data_found;
3616 --
3617 end if;
3618 --
3619 p_rec := g_cache_plip_hwf_rt_rec(l_index);
3620 --
3621 -- hr_utility.set_location ('Leaving '||l_package,10);
3622 --
3623 exception
3624 --
3625 when no_data_found then
3626 --
3627 -- The record has not been cached yet so lets cache it
3628 --
3629 open c1;
3630 --
3631 fetch c1 into g_cache_plip_hwf_rt_rec(l_index);
3632 if p_old_val is null and p_new_val is null then
3633 --
3634 if c1%notfound then
3635 --
3636 g_cache_plip_hwf_rt_rec(l_index).id := p_plip_id;
3637 g_cache_plip_hwf_rt_rec(l_index).exist := 'N';
3638 --
3639 end if;
3640 --
3641 end if;
3642 --
3643 p_rec := g_cache_plip_hwf_rt_rec(l_index);
3644 --
3645 close c1;
3646 --
3647 end get_hours_plip_rate;
3648 --
3649 procedure get_hours_rate
3650 (p_pl_id in number,
3651 p_oipl_id in number,
3655 p_business_group_id in number,
3652 p_plip_id in number,
3653 p_old_val in number default null,
3654 p_new_val in number default null,
3656 p_effective_date in date,
3657 p_rec out nocopy ben_derive_part_and_rate_cache.g_cache_hwf_rec_obj) is
3658 --
3659 l_package varchar2(80) := g_package||'.get_hours_rate';
3660 --
3661 begin
3662 --
3663 -- hr_utility.set_location ('Entering '||l_package,10);
3664 --
3665 -- Derive which data type we are dealing with
3666 --
3667 if p_pl_id is not null then
3668 --
3669 get_hours_pl_rate(p_pl_id => p_pl_id,
3670 p_old_val => p_old_val,
3671 p_new_val => p_new_val,
3672 p_business_group_id => p_business_group_id,
3673 p_effective_date => p_effective_date,
3674 p_rec => p_rec);
3675 --
3676 elsif p_oipl_id is not null then
3677 --
3678 get_hours_oipl_rate(p_oipl_id => p_oipl_id,
3679 p_old_val => p_old_val,
3680 p_new_val => p_new_val,
3681 p_business_group_id => p_business_group_id,
3682 p_effective_date => p_effective_date,
3683 p_rec => p_rec);
3684 --
3685 elsif p_plip_id is not null then
3686 --
3687 get_hours_plip_rate(p_plip_id => p_plip_id,
3688 p_old_val => p_old_val,
3689 p_new_val => p_new_val,
3690 p_business_group_id => p_business_group_id,
3691 p_effective_date => p_effective_date,
3692 p_rec => p_rec);
3693 --
3694 end if;
3695 --
3696 -- hr_utility.set_location ('Leaving '||l_package,10);
3697 --
3698 end get_hours_rate;
3699 --
3700 procedure clear_down_cache is
3701 --
3702 l_package varchar2(80) := g_package||'.clear_down_cache';
3703 --
3704 begin
3705 --
3706 -- hr_utility.set_location ('Entering '||l_package,10);
3707 --
3708 -- Clear down all caches
3709 --
3710 g_cache_pl_los_rt_rec.delete;
3711 g_cache_oipl_los_rt_rec.delete;
3712 g_cache_plip_los_rt_rec.delete;
3713 g_cache_pl_age_rt_rec.delete;
3714 g_cache_oipl_age_rt_rec.delete;
3715 g_cache_plip_age_rt_rec.delete;
3716 g_cache_pl_clf_rt_rec.delete;
3717 g_cache_oipl_clf_rt_rec.delete;
3718 g_cache_plip_clf_rt_rec.delete;
3719 g_cache_pl_cla_rt_rec.delete;
3720 g_cache_oipl_cla_rt_rec.delete;
3721 g_cache_plip_cla_rt_rec.delete;
3722 g_cache_pl_pff_rt_rec.delete;
3723 g_cache_oipl_pff_rt_rec.delete;
3724 g_cache_plip_pff_rt_rec.delete;
3725 g_cache_pl_hwf_rt_rec.delete;
3726 g_cache_oipl_hwf_rt_rec.delete;
3727 g_cache_plip_hwf_rt_rec.delete;
3728 --
3729 -- hr_utility.set_location ('Leaving '||l_package,10);
3730 --
3731 end clear_down_cache;
3732 --
3733 end ben_derive_part_and_rate_cvg;
[Home] [Help]