[Home] [Help]
PACKAGE BODY: APPS.BEN_CAGRELP_CACHE
Source
1 package body ben_cagrelp_cache as
2 /* $Header: benelpc1.pkb 120.0 2005/05/28 08:57:11 appldev noship $ */
3 --
4 -- Declare globals
5 --
6 g_package varchar2(50) := 'ben_cagrelp_cache.';
7 g_hash_key number := ben_hash_utility.get_hash_key;
8 g_hash_jump number := ben_hash_utility.get_hash_jump;
9 --
10 g_elpegn_lookup ben_cache.g_cache_lookup_table;
11 g_elpegn_nxelenum pls_integer;
12 g_elpegn_inst g_elp_cache := g_elp_cache();
13 g_elpegn_cached pls_integer := 0;
14 --
15 procedure elpegn_getdets
16 (p_effective_date in date
17 ,p_eligy_prfl_id in number
18 --
19 ,p_inst_set in out nocopy g_elp_cache
20 )
21 is
22 --
23 l_proc varchar2(72) := g_package||'elpegn_getdets';
24 --
25 l_inst_set g_elp_cache := g_elp_cache();
26 --
27 l_elenum pls_integer;
28 --
29 cursor c_instance
30 (c_eligy_prfl_id number
31 ,c_effective_date date
32 )
33 is
34 select tab.eligy_prfl_id,
35 tab.elig_gndr_prte_id pk_id,
36 'EGN' short_code,
37 tab.sex,
38 tab.excld_flag,
39 tab.criteria_score,
40 tab.criteria_weight
41 from ben_elig_gndr_prte_f tab
42 where tab.eligy_prfl_id = c_eligy_prfl_id
43 and c_effective_date
44 between tab.effective_start_date and tab.effective_end_date
45 order by tab.eligy_prfl_id,
46 decode(tab.excld_flag,'Y',1,2);
47 --
48 begin
49 --
50 l_elenum := 1;
51 --
52 for instrow in c_instance
53 (c_eligy_prfl_id => p_eligy_prfl_id
54 ,c_effective_date => p_effective_date
55 )
56 loop
57 --
58 l_inst_set.extend(1);
59 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
60 l_inst_set(l_elenum).pk_id := instrow.pk_id;
61 l_inst_set(l_elenum).short_code := instrow.short_code;
62 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
63 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
64 l_inst_set(l_elenum).v230_val := instrow.sex;
65 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
66 l_elenum := l_elenum+1;
67 --
68 end loop;
69 --
70 p_inst_set := l_inst_set;
71 --
72 exception
73 --
74 when no_data_found then
75 --
76 null;
77 --
78 end elpegn_getdets;
79 --
80 procedure elpemp_getdets
81 (p_effective_date in date
82 ,p_eligy_prfl_id in number
83 --
84 ,p_inst_set in out nocopy g_elp_cache
85 )
86 is
87 --
88 l_proc varchar2(72) := g_package||'elpemp_getdets';
89 --
90 l_inst_set g_elp_cache := g_elp_cache();
91 --
92 l_elenum pls_integer;
93 --
94 cursor c_instance
95 (c_eligy_prfl_id number
96 ,c_effective_date date
97 )
98 is
99 select tab.eligy_prfl_id,
100 tab.elig_mrtl_sts_prte_id pk_id,
101 'EMS' short_code,
102 tab.marital_status,
103 tab.excld_flag,
104 tab.criteria_score,
105 tab.criteria_weight
106 from ben_elig_mrtl_sts_prte_f tab
107 where tab.eligy_prfl_id = c_eligy_prfl_id
108 and c_effective_date
109 between tab.effective_start_date and tab.effective_end_date
110 order by tab.eligy_prfl_id,
111 decode(tab.excld_flag,'Y',1,2);
112 --
113 begin
114 --
115 l_elenum := 1;
116 --
117 for instrow in c_instance
118 (c_eligy_prfl_id => p_eligy_prfl_id
119 ,c_effective_date => p_effective_date
120 )
121 loop
122 --
123 l_inst_set.extend(1);
124 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
125 l_inst_set(l_elenum).pk_id := instrow.pk_id;
126 l_inst_set(l_elenum).short_code := instrow.short_code;
127 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
128 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
129 l_inst_set(l_elenum).v230_val := instrow.marital_status;
130 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
131 l_elenum := l_elenum+1;
132 --
133 end loop;
134 --
135 p_inst_set := l_inst_set;
136 --
137 exception
138 --
139 when no_data_found then
140 --
141 null;
142 --
143 end elpemp_getdets;
144 --
145 procedure elpect_getdets
146 (p_effective_date in date
147 ,p_eligy_prfl_id in number
148 --
149 ,p_inst_set in out nocopy g_elp_cache
150 )
151 is
152 --
153 l_proc varchar2(72) := g_package||'elpect_getdets';
154 --
155 l_inst_set g_elp_cache := g_elp_cache();
156 --
157 l_elenum pls_integer;
158 --
159 cursor c_instance
160 (c_eligy_prfl_id number
161 ,c_effective_date date
162 )
163 is
164 select tab.eligy_prfl_id,
165 tab.elig_dsblty_ctg_prte_id pk_id,
166 'ECT' short_code,
167 tab.category,
168 tab.excld_flag,
169 tab.criteria_score,
170 tab.criteria_weight
171 from ben_elig_dsblty_ctg_prte_f tab
172 where tab.eligy_prfl_id = c_eligy_prfl_id
173 and c_effective_date
174 between tab.effective_start_date and tab.effective_end_date
175 order by tab.eligy_prfl_id,
176 decode(tab.excld_flag,'Y',1,2);
177 --
178 begin
179 --
180 l_elenum := 1;
181 --
182 for instrow in c_instance
183 (c_eligy_prfl_id => p_eligy_prfl_id
184 ,c_effective_date => p_effective_date
185 )
186 loop
187 --
188 l_inst_set.extend(1);
189 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
190 l_inst_set(l_elenum).pk_id := instrow.pk_id;
191 l_inst_set(l_elenum).short_code := instrow.short_code;
192 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
193 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
194 l_inst_set(l_elenum).v230_val := instrow.category;
195 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
196 l_elenum := l_elenum+1;
197 --
198 end loop;
199 --
200 p_inst_set := l_inst_set;
201 --
202 exception
203 --
204 when no_data_found then
205 --
206 null;
207 --
208 end elpect_getdets;
209 --
210 procedure elpedr_getdets
211 (p_effective_date in date
212 ,p_eligy_prfl_id in number
213 --
214 ,p_inst_set in out nocopy g_elp_cache
215 )
216 is
217 --
218 l_proc varchar2(72) := g_package||'elpedr_getdets';
219 --
220 l_inst_set g_elp_cache := g_elp_cache();
221 --
222 l_elenum pls_integer;
223 --
224 cursor c_instance
225 (c_eligy_prfl_id number
226 ,c_effective_date date
227 )
228 is
229 select tab.eligy_prfl_id,
230 tab.elig_dsblty_rsn_prte_id pk_id,
231 'EDR' short_code,
232 tab.reason,
233 tab.excld_flag,
234 tab.criteria_score,
235 tab.criteria_weight
236 from ben_elig_dsblty_rsn_prte_f tab
237 where tab.eligy_prfl_id = c_eligy_prfl_id
238 and c_effective_date
239 between tab.effective_start_date and tab.effective_end_date
240 order by tab.eligy_prfl_id,
241 decode(tab.excld_flag,'Y',1,2);
242 --
243 begin
244 --
245 l_elenum := 1;
246 --
247 for instrow in c_instance
248 (c_eligy_prfl_id => p_eligy_prfl_id
249 ,c_effective_date => p_effective_date
250 )
251 loop
252 --
253 l_inst_set.extend(1);
254 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
255 l_inst_set(l_elenum).pk_id := instrow.pk_id;
256 l_inst_set(l_elenum).short_code := instrow.short_code;
257 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
258 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
259 l_inst_set(l_elenum).v230_val := instrow.reason;
260 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
261 l_elenum := l_elenum+1;
262 --
263 end loop;
264 --
265 p_inst_set := l_inst_set;
266 --
267 exception
268 --
269 when no_data_found then
270 --
271 null;
272 --
273 end elpedr_getdets;
274 --
275 procedure elpedd_getdets
276 (p_effective_date in date
277 ,p_eligy_prfl_id in number
278 --
279 ,p_inst_set in out nocopy g_elp_cache
280 )
281 is
282 --
283 l_proc varchar2(72) := g_package||'elpedd_getdets';
284 --
285 l_inst_set g_elp_cache := g_elp_cache();
286 --
287 l_elenum pls_integer;
288 --
289 cursor c_instance
290 (c_eligy_prfl_id number
291 ,c_effective_date date
292 )
293 is
294 select tab.eligy_prfl_id,
295 tab.elig_dsblty_dgr_prte_id pk_id,
296 'EDD' short_code,
297 tab.degree,
298 tab.excld_flag,
299 tab.criteria_score,
300 tab.criteria_weight
301 from ben_elig_dsblty_dgr_prte_f tab
302 where tab.eligy_prfl_id = c_eligy_prfl_id
303 and c_effective_date
304 between tab.effective_start_date and tab.effective_end_date
305 order by tab.eligy_prfl_id,
306 decode(tab.excld_flag,'Y',1,2);
307 --
308 begin
309 --
310 l_elenum := 1;
311 --
312 for instrow in c_instance
313 (c_eligy_prfl_id => p_eligy_prfl_id
314 ,c_effective_date => p_effective_date
315 )
316 loop
317 --
318 l_inst_set.extend(1);
319 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
320 l_inst_set(l_elenum).pk_id := instrow.pk_id;
321 l_inst_set(l_elenum).short_code := instrow.short_code;
322 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
323 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
324 l_inst_set(l_elenum).num_val := instrow.degree;
325 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
326 l_elenum := l_elenum+1;
327 --
328 end loop;
329 --
330 p_inst_set := l_inst_set;
331 --
332 exception
333 --
334 when no_data_found then
335 --
336 null;
337 --
338 end elpedd_getdets;
339 --
340 procedure elpest_getdets
341 (p_effective_date in date
342 ,p_eligy_prfl_id in number
343 --
344 ,p_inst_set in out nocopy g_elp_cache
345 )
346 is
347 --
348 l_proc varchar2(72) := g_package||'elpest_getdets';
349 --
350 l_inst_set g_elp_cache := g_elp_cache();
351 --
352 l_elenum pls_integer;
353 --
354 cursor c_instance
355 (c_eligy_prfl_id number
356 ,c_effective_date date
357 )
358 is
359 select tab.eligy_prfl_id,
360 tab.elig_suppl_role_prte_id pk_id,
361 'EST' short_code,
362 tab.job_id,
363 tab.job_group_id,
364 tab.excld_flag,
365 tab.criteria_score,
366 tab.criteria_weight
367 from ben_elig_suppl_role_prte_f tab
368 where tab.eligy_prfl_id = c_eligy_prfl_id
369 and c_effective_date
370 between tab.effective_start_date and tab.effective_end_date
371 order by tab.eligy_prfl_id,
372 decode(tab.excld_flag,'Y',1,2);
373 --
374 begin
375 --
376 l_elenum := 1;
377 --
378 for instrow in c_instance
379 (c_eligy_prfl_id => p_eligy_prfl_id
380 ,c_effective_date => p_effective_date
381 )
382 loop
383 --
384 l_inst_set.extend(1);
385 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
386 l_inst_set(l_elenum).pk_id := instrow.pk_id;
387 l_inst_set(l_elenum).short_code := instrow.short_code;
388 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
389 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
390 l_inst_set(l_elenum).num_val := instrow.job_id;
391 l_inst_set(l_elenum).num_val1 := instrow.job_group_id;
392 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
393 l_elenum := l_elenum+1;
394 --
395 end loop;
396 --
397 p_inst_set := l_inst_set;
398 --
399 exception
400 --
401 when no_data_found then
402 --
403 null;
404 --
405 end elpest_getdets;
406 --
407 procedure elpeqt_getdets
408 (p_effective_date in date
409 ,p_eligy_prfl_id in number
410 --
411 ,p_inst_set in out nocopy g_elp_cache
412 )
413 is
414 --
415 l_proc varchar2(72) := g_package||'elpeqt_getdets';
416 --
417 l_inst_set g_elp_cache := g_elp_cache();
418 --
419 l_elenum pls_integer;
420 --
421 cursor c_instance
422 (c_eligy_prfl_id number
423 ,c_effective_date date
424 )
425 is
426 select tab.eligy_prfl_id,
427 tab.elig_qual_titl_prte_id pk_id,
428 'EQT' short_code,
429 tab.title,
430 tab.QUALIFICATION_TYPE_ID,
431 tab.excld_flag,
432 tab.criteria_score,
433 tab.criteria_weight
434 from ben_elig_qual_titl_prte_f tab
435 where tab.eligy_prfl_id = c_eligy_prfl_id
436 and c_effective_date
437 between tab.effective_start_date and tab.effective_end_date
438 order by tab.eligy_prfl_id,
439 decode(tab.excld_flag,'Y',1,2);
440 --
441 begin
442 --
443 l_elenum := 1;
444 --
445 for instrow in c_instance
446 (c_eligy_prfl_id => p_eligy_prfl_id
447 ,c_effective_date => p_effective_date
448 )
449 loop
450 --
451 l_inst_set.extend(1);
452 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
453 l_inst_set(l_elenum).pk_id := instrow.pk_id;
454 l_inst_set(l_elenum).short_code := instrow.short_code;
455 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
456 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
457 l_inst_set(l_elenum).v230_val := instrow.title;
458 l_inst_set(l_elenum).num_val := instrow.QUALIFICATION_TYPE_ID;
459 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
460 l_elenum := l_elenum+1;
461 --
462 end loop;
463 --
464 p_inst_set := l_inst_set;
465 --
466 exception
467 --
468 when no_data_found then
469 --
470 null;
471 --
472 end elpeqt_getdets;
473 --
474 procedure elpeps_getdets
475 (p_effective_date in date
476 ,p_eligy_prfl_id in number
477 --
478 ,p_inst_set in out nocopy g_elp_cache
479 )
480 is
481 --
482 l_proc varchar2(72) := g_package||'elpeps_getdets';
483 --
484 l_inst_set g_elp_cache := g_elp_cache();
485 --
486 l_elenum pls_integer;
487 --
488 cursor c_instance
489 (c_eligy_prfl_id number
490 ,c_effective_date date
491 )
492 is
493 select tab.eligy_prfl_id,
494 tab.elig_pstn_prte_id pk_id,
495 'EPT' short_code,
496 tab.position_id,
497 tab.excld_flag,
498 tab.criteria_score,
499 tab.criteria_weight
500 from ben_elig_pstn_prte_f tab
501 where tab.eligy_prfl_id = c_eligy_prfl_id
502 and c_effective_date
503 between tab.effective_start_date and tab.effective_end_date
504 order by tab.eligy_prfl_id,
505 decode(tab.excld_flag,'Y',1,2);
506 --
507 begin
508 --
509 l_elenum := 1;
510 --
511 for instrow in c_instance
512 (c_eligy_prfl_id => p_eligy_prfl_id
513 ,c_effective_date => p_effective_date
514 )
515 loop
516 --
517 l_inst_set.extend(1);
518 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
519 l_inst_set(l_elenum).pk_id := instrow.pk_id;
520 l_inst_set(l_elenum).short_code := instrow.short_code;
521 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
522 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
523 l_inst_set(l_elenum).num_val := instrow.position_id;
524 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
525 l_elenum := l_elenum+1;
526 --
527 end loop;
528 --
529 p_inst_set := l_inst_set;
530 --
531 exception
532 --
533 when no_data_found then
534 --
535 null;
536 --
537 end elpeps_getdets;
538 --
539 procedure elpepn_getdets
540 (p_effective_date in date
541 ,p_eligy_prfl_id in number
542 --
543 ,p_inst_set in out nocopy g_elp_cache
544 )
545 is
546 --
547 l_proc varchar2(72) := g_package||'elpepn_getdets';
548 --
549 l_inst_set g_elp_cache := g_elp_cache();
550 --
551 l_elenum pls_integer;
552 --
553 cursor c_instance
554 (c_eligy_prfl_id number
555 ,c_effective_date date
556 )
557 is
558 select tab.eligy_prfl_id,
559 tab.elig_prbtn_perd_prte_id pk_id,
560 'EPP' short_code,
561 tab.PROBATION_PERIOD,
562 tab.PROBATION_UNIT,
563 tab.excld_flag,
564 tab.criteria_score,
565 tab.criteria_weight
566 from ben_elig_prbtn_perd_prte_f tab
567 where tab.eligy_prfl_id = c_eligy_prfl_id
568 and c_effective_date
569 between tab.effective_start_date and tab.effective_end_date
570 order by tab.eligy_prfl_id,
571 decode(tab.excld_flag,'Y',1,2);
572 --
573 begin
574 --
575 l_elenum := 1;
576 --
577 for instrow in c_instance
578 (c_eligy_prfl_id => p_eligy_prfl_id
579 ,c_effective_date => p_effective_date
580 )
581 loop
582 --
583 l_inst_set.extend(1);
584 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
585 l_inst_set(l_elenum).pk_id := instrow.pk_id;
586 l_inst_set(l_elenum).short_code := instrow.short_code;
587 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
588 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
589 l_inst_set(l_elenum).num_val := instrow.PROBATION_PERIOD;
590 l_inst_set(l_elenum).v230_val := instrow.PROBATION_UNIT;
591 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
592 l_elenum := l_elenum+1;
593 --
594 end loop;
595 --
596 p_inst_set := l_inst_set;
597 --
598 exception
599 --
600 when no_data_found then
601 --
602 null;
603 --
604 end elpepn_getdets;
605 --
606 procedure elpesp_getdets
607 (p_effective_date in date
608 ,p_eligy_prfl_id in number
609 --
610 ,p_inst_set in out nocopy g_elp_cache
611 )
612 is
613 --
614 l_proc varchar2(72) := g_package||'elpesp_getdets';
615 --
616 l_inst_set g_elp_cache := g_elp_cache();
617 --
618 l_elenum pls_integer;
619 --
620 cursor c_instance
621 (c_eligy_prfl_id number
622 ,c_effective_date date
623 )
624 is
625 select tab.eligy_prfl_id,
626 tab.elig_sp_clng_prg_prte_id pk_id,
627 'ESP' short_code,
628 tab.special_ceiling_step_id,
629 tab.excld_flag,
630 tab.criteria_score,
631 tab.criteria_weight
632 from ben_elig_sp_clng_prg_prte_f tab
633 where tab.eligy_prfl_id = c_eligy_prfl_id
634 and c_effective_date
635 between tab.effective_start_date and tab.effective_end_date
636 order by tab.eligy_prfl_id,
637 decode(tab.excld_flag,'Y',1,2);
638 --
639 begin
640 --
641 l_elenum := 1;
642 --
643 for instrow in c_instance
644 (c_eligy_prfl_id => p_eligy_prfl_id
645 ,c_effective_date => p_effective_date
646 )
647 loop
648 --
649 l_inst_set.extend(1);
650 l_inst_set(l_elenum).eligy_prfl_id := instrow.eligy_prfl_id;
651 l_inst_set(l_elenum).pk_id := instrow.pk_id;
652 l_inst_set(l_elenum).short_code := instrow.short_code;
653 l_inst_set(l_elenum).criteria_score := instrow.criteria_score;
654 l_inst_set(l_elenum).criteria_weight := instrow.criteria_weight;
655 l_inst_set(l_elenum).v230_val := instrow.special_ceiling_step_id;
656 l_inst_set(l_elenum).excld_flag := instrow.excld_flag;
657 l_elenum := l_elenum+1;
658 --
659 end loop;
660 --
661 p_inst_set := l_inst_set;
662 --
663 exception
664 --
665 when no_data_found then
666 --
667 null;
668 --
669 end elpesp_getdets;
670 --
671 procedure clear_down_cache
672 is
673 --
674 --
675 begin
676 --
677
678 --
679 -- Grab back memory
680 --
681 begin
682 --
683 dbms_session.free_unused_user_memory;
684 --
685 end;
686 --
687 end clear_down_cache;
688 --
689 end ben_cagrelp_cache;
690 --