[Home] [Help]
PACKAGE BODY: APPS.BEN_PEP_CACHE1
Source
1 package body ben_pep_cache1 as
2 /* $Header: benppch1.pkb 120.1 2007/11/14 15:14:40 rtagarra noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 25-Aug-03 mhoyes Created.
15 115.1 28-Aug-03 mhoyes - Added get_currplnpep_dets.
16 115.2 13-Sep-03 mhoyes Tuning.
17 115.3 30-Sep-03 mhoyes More Tuning.
18 115.4 01-Feb-04 mhoyes - Bug 3412822: Split c_current_elig into
19 four cursors in get_currpepcobj_prtnstrtdt.
20 115.5 18-Feb-04 mhoyes - Bug 3412822. Revamp of eligibility cache.
21 115.6 24-Feb-04 mhoyes - Bug 3412822. More eligibility cache tuning.
22 115.7 08-Apr-04 mhoyes - Bug 3412822. More eligibility cache tuning.
23 115.8 14-Nov-07 rtagarra -- Bug 5941500 : Fixed cursors c_pilplnip_dets and c_pilpln_dets
24 -----------------------------------------------------------------------------
25 */
26 --
27 -- Globals.
28 --
29 g_package varchar2(50) := 'ben_pep_cache1.';
30 --
31 procedure get_curroiplippep_dets
32 (p_comp_obj_tree_row in out NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
33 ,p_person_id in number
34 ,p_effective_date in date
35 --
36 ,p_inst_row in out NOCOPY ben_pep_cache.g_pep_rec
37 )
38 is
39 --
40 l_proc varchar2(72) := 'get_curroiplippep_dets';
41 --
42 l_inst_row ben_pep_cache.g_pep_rec;
43 --
44 cursor c_piloiplip_dets
45 (c_person_id in number
46 ,c_effective_date in date
47 ,c_pgm_id in number
48 ,c_plip_id in number
49 )
50 is
51 select /*+ benppch1.get_curroiplippep_dets.c_piloiplip_dets */
52 pep.elig_per_id
53 from ben_elig_per_f pep,
54 ben_per_in_ler pil
55 where pep.person_id = c_person_id
56 and pep.pgm_id = c_pgm_id
57 and pep.plip_id = c_plip_id
58 and c_effective_date
59 between pep.effective_start_date and pep.effective_end_date
60 and pil.per_in_ler_id=pep.per_in_ler_id
61 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
62 --
63 cursor c_nopiloiplip_dets
64 (c_person_id in number
65 ,c_effective_date in date
66 ,c_pgm_id in number
67 ,c_plip_id in number
68 )
69 is
70 select /*+ benppch1.get_curroiplippep_dets.c_nopiloiplip_dets */
71 pep.elig_per_id
72 from ben_elig_per_f pep
73 where pep.person_id = c_person_id
74 and pep.per_in_ler_id is null
75 and pep.pgm_id = c_pgm_id
76 and pep.plip_id = c_plip_id
77 and c_effective_date
78 between pep.effective_start_date and pep.effective_end_date;
79 --
80 cursor c_piloiplipnip_dets
81 (c_person_id in number
82 ,c_effective_date in date
83 ,c_plip_id in number
84 )
85 is
86 select /*+ benppch1.get_curroiplippep_dets.c_piloiplipnip_dets */
87 pep.elig_per_id
88 from ben_elig_per_f pep,
89 ben_per_in_ler pil
90 where pep.person_id = c_person_id
91 and pep.pgm_id is null
92 and pep.plip_id = c_plip_id
93 and c_effective_date
94 between pep.effective_start_date and pep.effective_end_date
95 and pil.per_in_ler_id=pep.per_in_ler_id
96 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
97 --
98 cursor c_nopiloiplipnip_dets
99 (c_person_id in number
100 ,c_effective_date in date
101 ,c_plip_id in number
102 )
103 is
104 select /*+ benppch1.get_curroiplippep_dets.c_nopiloiplipnip_dets */
105 pep.elig_per_id
106 from ben_elig_per_f pep
107 where pep.person_id = c_person_id
108 and pep.per_in_ler_id is null
109 and pep.pgm_id is null
110 and pep.plip_id = c_plip_id
111 and c_effective_date
112 between pep.effective_start_date and pep.effective_end_date;
113 --
114 begin
115 --
116 if p_comp_obj_tree_row.par_pgm_id is not null
117 then
118 --
119 open c_piloiplip_dets
120 (c_person_id => p_person_id
121 ,c_effective_date => p_effective_date
122 ,c_pgm_id => p_comp_obj_tree_row.par_pgm_id
123 ,c_plip_id => p_comp_obj_tree_row.par_plip_id
124 );
125 fetch c_piloiplip_dets into l_inst_row.elig_per_id;
126 close c_piloiplip_dets;
127 --
128 if l_inst_row.elig_per_id is null
129 then
130 --
131 open c_nopiloiplip_dets
132 (c_person_id => p_person_id
133 ,c_effective_date => p_effective_date
134 ,c_pgm_id => p_comp_obj_tree_row.par_pgm_id
135 ,c_plip_id => p_comp_obj_tree_row.par_plip_id
136 );
137 fetch c_nopiloiplip_dets into l_inst_row.elig_per_id;
138 close c_nopiloiplip_dets;
139 --
140 end if;
141 --
142 else
143 --
144 open c_piloiplipnip_dets
145 (c_person_id => p_person_id
146 ,c_effective_date => p_effective_date
147 ,c_plip_id => p_comp_obj_tree_row.par_plip_id
148 );
149 fetch c_piloiplipnip_dets into l_inst_row.elig_per_id;
150 close c_piloiplipnip_dets;
151 --
152 if l_inst_row.elig_per_id is null
153 then
154 --
155 open c_nopiloiplipnip_dets
156 (c_person_id => p_person_id
157 ,c_effective_date => p_effective_date
158 ,c_plip_id => p_comp_obj_tree_row.par_plip_id
159 );
160 fetch c_nopiloiplipnip_dets into l_inst_row.elig_per_id;
161 close c_nopiloiplipnip_dets;
162 --
163 end if;
164 --
165 end if;
166 --
167 p_inst_row := l_inst_row;
168 --
169 end get_curroiplippep_dets;
170 --
171 procedure get_currplnpep_dets
172 (p_comp_obj_tree_row in out NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
173 ,p_person_id in number
174 ,p_effective_date in date
175 --
176 ,p_inst_row in out NOCOPY ben_pep_cache.g_pep_rec
177 )
178 is
179 --
180 l_proc varchar2(72) := 'get_currplnpep_dets';
181 --
182 l_inst_row ben_pep_cache.g_pep_rec;
183 --
184 -- Cursor to grab the PK of elig_per record to join the elig opt record to
185 -- for first time'rs only
186 --
187 cursor c_pilpln_dets
188 (c_person_id in number
189 ,c_effective_date in date
190 ,c_pgm_id in number
191 ,c_pl_id in number
192 )
193 is
194 select /*+ benppch1.get_currplnpep_dets.c_pilpln_dets */
195 pep.elig_per_id,
196 pep.prtn_strt_dt,
197 pep.prtn_end_dt
198 from ben_elig_per_f pep,
199 ben_per_in_ler pil
200 where pep.person_id = c_person_id
201 and pep.pgm_id = c_pgm_id
202 and pep.pl_id = c_pl_id
203 and c_effective_date
204 between pep.effective_start_date and pep.effective_end_date
205 and pil.per_in_ler_id=pep.per_in_ler_id
206 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
207 order by pep.per_in_ler_id desc ; -- Bug 5941500
208 --
209 cursor c_nopilpln_dets
210 (c_person_id in number
211 ,c_effective_date in date
212 ,c_pgm_id in number
213 ,c_pl_id in number
214 )
215 is
216 select /*+ benppch1.get_currplnpep_dets.c_nopilpln_dets */
217 pep.elig_per_id,
218 pep.prtn_strt_dt,
219 pep.prtn_end_dt
220 from ben_elig_per_f pep
221 where pep.person_id = c_person_id
222 and pep.per_in_ler_id is null
223 and pep.pgm_id = c_pgm_id
224 and pep.pl_id = c_pl_id
225 and c_effective_date
226 between pep.effective_start_date and pep.effective_end_date;
227 --
228 cursor c_pilplnip_dets
229 (c_person_id in number
230 ,c_effective_date in date
231 ,c_pl_id in number
232 )
233 is
234 select /*+ benppch1.get_currplnpep_dets.c_pilplnip_dets */
235 pep.elig_per_id,
236 pep.prtn_strt_dt,
237 pep.prtn_end_dt
238 from ben_elig_per_f pep,
239 ben_per_in_ler pil
240 where pep.person_id = c_person_id
241 and pep.pgm_id is null
242 and pep.pl_id = c_pl_id
243 and c_effective_date
244 between pep.effective_start_date and pep.effective_end_date
245 and pil.per_in_ler_id=pep.per_in_ler_id
246 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
247 order by pep.per_in_ler_id desc ;-- Bug 5941500
248 --
249 cursor c_nopilplnip_dets
250 (c_person_id in number
251 ,c_effective_date in date
252 ,c_pl_id in number
253 )
254 is
255 select /*+ benppch1.get_currplnpep_dets.c_nopilplnip_dets */
256 pep.elig_per_id,
257 pep.prtn_strt_dt,
258 pep.prtn_end_dt
259 from ben_elig_per_f pep
260 where pep.person_id = c_person_id
261 and pep.per_in_ler_id is null
262 and pep.pgm_id is null
263 and pep.pl_id = c_pl_id
264 and c_effective_date
265 between pep.effective_start_date and pep.effective_end_date;
266 --
267 begin
268 --
269 if p_comp_obj_tree_row.par_pgm_id is not null
270 then
271 --
272 open c_pilpln_dets
273 (c_person_id => p_person_id
274 ,c_effective_date => p_effective_date
275 ,c_pgm_id => p_comp_obj_tree_row.par_pgm_id
276 ,c_pl_id => p_comp_obj_tree_row.par_pl_id
277 );
278 fetch c_pilpln_dets into l_inst_row.elig_per_id,
279 l_inst_row.prtn_strt_dt,
280 l_inst_row.prtn_end_dt;
281 close c_pilpln_dets;
282 --
283 if l_inst_row.elig_per_id is null
284 then
285 --
286 open c_nopilpln_dets
287 (c_person_id => p_person_id
288 ,c_effective_date => p_effective_date
289 ,c_pgm_id => p_comp_obj_tree_row.par_pgm_id
290 ,c_pl_id => p_comp_obj_tree_row.par_pl_id
291 );
292 fetch c_nopilpln_dets into l_inst_row.elig_per_id,
293 l_inst_row.prtn_strt_dt,
294 l_inst_row.prtn_end_dt;
295 close c_nopilpln_dets;
296 --
297 end if;
298 --
299 else
300 --
301 open c_pilplnip_dets
302 (c_person_id => p_person_id
303 ,c_effective_date => p_effective_date
304 ,c_pl_id => p_comp_obj_tree_row.par_pl_id
305 );
306 fetch c_pilplnip_dets into l_inst_row.elig_per_id,
307 l_inst_row.prtn_strt_dt,
308 l_inst_row.prtn_end_dt;
309 close c_pilplnip_dets;
310 --
311 if l_inst_row.elig_per_id is null
312 then
313 --
314 open c_nopilplnip_dets
315 (c_person_id => p_person_id
316 ,c_effective_date => p_effective_date
317 ,c_pl_id => p_comp_obj_tree_row.par_pl_id
318 );
319 fetch c_nopilplnip_dets into l_inst_row.elig_per_id,
320 l_inst_row.prtn_strt_dt,
321 l_inst_row.prtn_end_dt;
322 close c_nopilplnip_dets;
323 --
324 end if;
325 --
326 end if;
327 --
328 p_inst_row := l_inst_row;
329 --
330 end get_currplnpep_dets;
331 --
332 procedure get_currpepcobj_cache
333 (p_person_id in number
334 ,p_pgm_id in number
335 ,p_ptip_id in number default null
336 ,p_pl_id in number
337 ,p_plip_id in number default null
338 ,p_opt_id in number
339 ,p_effective_date in date
340 --
341 ,p_ecrpep_rec in out NOCOPY g_ecrpep_rec
342 )
343 is
344 --
345 l_proc varchar2(72) := 'get_currpepcobj_cache';
346 --
347 l_pep_row ben_derive_part_and_rate_facts.g_cache_structure;
348 l_epo_row ben_derive_part_and_rate_facts.g_cache_structure;
349 --
350 l_prtn_strt_dt date;
351 l_prtn_ovridn_flag varchar2(30);
352 l_prtn_ovridn_thru_dt date;
353 l_rt_age_val number;
354 l_rt_los_val number;
355 l_rt_hrs_wkd_val number;
356 l_rt_cmbn_age_n_los_val number;
357 l_per_in_ler_id number;
358 l_elig_per_id number;
359 l_elig_per_opt_id number;
360 --
361 begin
362 --
363 -- Check mandatory parameters
364 --
365 if p_person_id is null
366 or p_effective_date is null
367 then
368 --
369 p_ecrpep_rec.prtn_strt_dt := null;
370 p_ecrpep_rec.prtn_ovridn_flag := null;
371 p_ecrpep_rec.prtn_ovridn_thru_dt := null;
372 p_ecrpep_rec.rt_age_val := null;
373 p_ecrpep_rec.rt_los_val := null;
374 p_ecrpep_rec.rt_hrs_wkd_val := null;
375 p_ecrpep_rec.rt_cmbn_age_n_los_val := null;
376 p_ecrpep_rec.per_in_ler_id := null;
377 p_ecrpep_rec.elig_per_id := null;
378 p_ecrpep_rec.elig_per_opt_id := null;
379 return;
380 --
381 end if;
382 --
383 if p_opt_id is not null
384 then
385 --
386 ben_pep_cache.get_pilepo_dets
387 (p_person_id => p_person_id
388 ,p_business_group_id => null
389 ,p_effective_date => p_effective_date
390 ,p_pgm_id => p_pgm_id
391 ,p_pl_id => p_pl_id
392 ,p_plip_id => p_plip_id
393 ,p_opt_id => p_opt_id
394 ,p_date_sync => TRUE
395 ,p_inst_row => l_epo_row
396 );
397 --
398 l_prtn_strt_dt := l_epo_row.prtn_strt_dt;
399 l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
400 l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
401 l_rt_age_val := l_epo_row.rt_age_val;
402 l_rt_los_val := l_epo_row.rt_los_val;
403 l_rt_hrs_wkd_val := l_epo_row.rt_hrs_wkd_val;
404 l_rt_cmbn_age_n_los_val := l_epo_row.rt_cmbn_age_n_los_val;
405 l_per_in_ler_id := l_epo_row.per_in_ler_id;
406 l_elig_per_id := l_epo_row.elig_per_id;
407 l_elig_per_opt_id := l_epo_row.elig_per_opt_id;
408 --
409 else
410 --
411 ben_pep_cache.get_pilpep_dets
412 (p_person_id => p_person_id
413 ,p_business_group_id => null
414 ,p_effective_date => p_effective_date
415 ,p_pgm_id => p_pgm_id
416 ,p_pl_id => p_pl_id
417 ,p_plip_id => p_plip_id
418 ,p_ptip_id => p_ptip_id
419 ,p_date_sync => TRUE
420 ,p_inst_row => l_pep_row
421 );
422 --
423 l_prtn_strt_dt := l_pep_row.prtn_strt_dt;
424 l_prtn_ovridn_flag := l_pep_row.prtn_ovridn_flag;
425 l_prtn_ovridn_thru_dt := l_pep_row.prtn_ovridn_thru_dt;
426 l_rt_age_val := l_pep_row.rt_age_val;
427 l_rt_los_val := l_pep_row.rt_los_val;
428 l_rt_hrs_wkd_val := l_pep_row.rt_hrs_wkd_val;
429 l_rt_cmbn_age_n_los_val := l_pep_row.rt_cmbn_age_n_los_val;
430 l_per_in_ler_id := l_pep_row.per_in_ler_id;
431 l_elig_per_id := l_pep_row.elig_per_id;
432 l_elig_per_opt_id := null;
433 --
434 end if;
435 --
436 p_ecrpep_rec.prtn_strt_dt := l_prtn_strt_dt;
437 p_ecrpep_rec.prtn_ovridn_flag := l_prtn_ovridn_flag;
438 p_ecrpep_rec.prtn_ovridn_thru_dt := l_prtn_ovridn_thru_dt;
439 p_ecrpep_rec.rt_age_val := l_rt_age_val;
440 p_ecrpep_rec.rt_los_val := l_rt_los_val;
441 p_ecrpep_rec.rt_hrs_wkd_val := l_rt_hrs_wkd_val;
442 p_ecrpep_rec.rt_cmbn_age_n_los_val := l_rt_cmbn_age_n_los_val;
443 p_ecrpep_rec.per_in_ler_id := l_per_in_ler_id;
444 p_ecrpep_rec.elig_per_id := l_elig_per_id;
445 p_ecrpep_rec.elig_per_opt_id := l_elig_per_opt_id;
446 --
447 end get_currpepcobj_cache;
448 --
449 end ben_pep_cache1;