[Home] [Help]
PACKAGE BODY: APPS.BEN_COMP_OBJECT_LIST
Source
1 PACKAGE BODY ben_comp_object_list AS
2 /* $Header: bebmbcol.pkb 120.3 2007/04/19 11:07:23 rtagarra noship $ */
3 --
4 g_package VARCHAR2(50) := 'ben_comp_object_list.';
5 --
6 g_prev_lf_evt_ocrd_dt DATE;
7 g_prev_per_org_id NUMBER;
8 --
9 PROCEDURE init_comp_object_list_globals IS
10 --
11 l_package VARCHAR2(80) := g_package || '.init_comp_object_list_globals';
12 --
13 BEGIN
14 hr_utility.set_location('Entering ' || l_package, 10);
15 --
16 g_prev_lf_evt_ocrd_dt := NULL;
17 g_prev_per_org_id := NULL;
18 --
19 hr_utility.set_location('Leaving ' || l_package, 10);
20 END init_comp_object_list_globals;
21 --
22 FUNCTION set_flag_bit_val(
23 p_business_group_id IN NUMBER
24 ,p_effective_date IN DATE
25 ,p_drvbl_fctr_prtn_elig_flag IN VARCHAR2
26 ,p_drvbl_fctr_apls_rts_flag IN VARCHAR2
27 ,p_pgm_id IN NUMBER DEFAULT NULL
28 ,p_pl_id IN NUMBER DEFAULT NULL
29 ,p_oipl_id IN NUMBER DEFAULT NULL
30 ,p_plip_id IN NUMBER DEFAULT NULL
31 ,p_ptip_id IN NUMBER DEFAULT NULL
32 ,p_oiplip_id IN NUMBER DEFAULT NULL)
33 RETURN BINARY_INTEGER IS
34 --
35 l_package VARCHAR2(80) := g_package || '.set_flag_bit_val';
36 l_inst_count NUMBER;
37 l_eligprof_dets ben_elp_cache.g_cobcep_cache;
38 l_age_flag BOOLEAN := FALSE;
39 l_los_flag BOOLEAN := FALSE;
40 l_cmp_flag BOOLEAN := FALSE;
41 l_pft_flag BOOLEAN := FALSE;
42 l_hrw_flag BOOLEAN := FALSE;
43 l_cal_flag BOOLEAN := FALSE;
44 l_age_rt_flag NUMBER := 0;
45 l_los_rt_flag NUMBER := 0;
46 l_cmp_rt_flag NUMBER := 0;
47 l_pft_rt_flag NUMBER := 0;
48 l_hrw_rt_flag NUMBER := 0;
49 l_cal_rt_flag NUMBER := 0;
50 l_prem_age_rt_flag NUMBER := 0;
51 l_prem_los_rt_flag NUMBER := 0;
52 l_prem_cmp_rt_flag NUMBER := 0;
53 l_prem_pft_rt_flag NUMBER := 0;
54 l_prem_hrw_rt_flag NUMBER := 0;
55 l_prem_cal_rt_flag NUMBER := 0;
56 l_cvg_age_rt_flag NUMBER := 0;
57 l_cvg_los_rt_flag NUMBER := 0;
58 l_cvg_cmp_rt_flag NUMBER := 0;
59 l_cvg_pft_rt_flag NUMBER := 0;
60 l_cvg_hrw_rt_flag NUMBER := 0;
61 l_cvg_cal_rt_flag NUMBER := 0;
62 l_flag_bit_val BINARY_INTEGER := 0;
63 l_cobj_id number;
64 --START Option Level Rates
65 l_oipl_abr_count number;
66 l_opt_id number;
67 --END Option Level Rates
68 --
69 l_sql VARCHAR2(32000)
70 := 'select count(*), sum(decode(vpf.rt_age_flag,''Y'',1,0)),
71 sum(decode(vpf.rt_los_flag,''Y'',1,0)),
72 sum(decode(vpf.rt_comp_lvl_flag,''Y'',1,0)),
73 sum(decode(vpf.rt_pct_fl_tm_flag,''Y'',1,0)),
74 sum(decode(vpf.rt_hrs_wkd_flag,''Y'',1,0)),
75 sum(decode(vpf.rt_cmbn_age_los_flag,''Y'',1,0))
76 from ben_vrbl_rt_prfl_f vpf,
77 ben_acty_vrbl_rt_f avr,
78 ben_acty_base_rt_f abr
79 where abr.{OBJECT} = :cobj_id
80 and abr.business_group_id = :business_group_id
81 and :abr_effective_date
82 between abr.effective_start_date
83 and abr.effective_end_date
84 and abr.acty_base_rt_id = avr.acty_base_rt_id
85 and avr.business_group_id = abr.business_group_id
86 and :avr_effective_date
87 between avr.effective_start_date
88 and avr.effective_end_date
89 and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
90 and vpf.business_group_id = avr.business_group_id
91 and :vpf_effective_date
92 between vpf.effective_start_date
93 and vpf.effective_end_date';
94 --
95 l_prem_sql VARCHAR2(2000)
96 := 'select sum(decode(vpf.rt_age_flag,''Y'',1,0)),
97 sum(decode(vpf.rt_los_flag,''Y'',1,0)),
98 sum(decode(vpf.rt_comp_lvl_flag,''Y'',1,0)),
99 sum(decode(vpf.rt_pct_fl_tm_flag,''Y'',1,0)),
100 sum(decode(vpf.rt_hrs_wkd_flag,''Y'',1,0)),
101 sum(decode(vpf.rt_cmbn_age_los_flag,''Y'',1,0))
102 from ben_vrbl_rt_prfl_f vpf,
103 ben_actl_prem_vrbl_rt_f apv,
104 ben_actl_prem_f apr
105 where apr.{OBJECT} = :cobj_id
106 and apr.business_group_id = :business_group_id
107 and :apr_effective_date
108 between apr.effective_start_date
109 and apr.effective_end_date
110 and apr.actl_prem_id = apv.actl_prem_id
111 and apv.business_group_id = apr.business_group_id
112 and :apv_effective_date
113 between apv.effective_start_date
114 and apv.effective_end_date
115 and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
116 and vpf.business_group_id = apr.business_group_id
117 and :vpf_effective_date
118 between vpf.effective_start_date
119 and vpf.effective_end_date';
120 --
121 l_cvg_sql VARCHAR2(2000)
122 := 'select sum(decode(vpf.rt_age_flag,''Y'',1,0)),
123 sum(decode(vpf.rt_los_flag,''Y'',1,0)),
124 sum(decode(vpf.rt_comp_lvl_flag,''Y'',1,0)),
125 sum(decode(vpf.rt_pct_fl_tm_flag,''Y'',1,0)),
126 sum(decode(vpf.rt_hrs_wkd_flag,''Y'',1,0)),
127 sum(decode(vpf.rt_cmbn_age_los_flag,''Y'',1,0))
128 from ben_vrbl_rt_prfl_f vpf,
129 ben_bnft_vrbl_rt_f bvr,
130 ben_cvg_amt_calc_mthd_f ccm
131 where ccm.{OBJECT} = :cobj_id
132 and ccm.business_group_id = :business_group_id
133 and :ccm_effective_date
134 between ccm.effective_start_date
135 and ccm.effective_end_date
136 and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
137 and bvr.business_group_id = ccm.business_group_id
138 and :bvr_effective_date
139 between bvr.effective_start_date
140 and bvr.effective_end_date
141 and bvr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
142 and vpf.business_group_id = bvr.business_group_id
143 and :vpf_effective_date
144 between vpf.effective_start_date
145 and vpf.effective_end_date';
146 --
147 l_opt_sql VARCHAR2(2000)
148 := 'select opt_id
149 from ben_oipl_f otp
150 where otp.oipl_id = :otp_oipl_id
151 and :otp_effective_date
152 between otp.effective_start_date
153 and otp.effective_end_date
154 and otp.business_group_id = :business_group_id' ;
155 --
156 BEGIN
157 --
158 hr_utility.set_location('Entering ' || l_package, 10);
159 --
160 IF p_drvbl_fctr_prtn_elig_flag = 'Y' THEN
161 --
162 ben_elp_cache.cobcep_getdets(p_business_group_id=> p_business_group_id
163 ,p_effective_date => p_effective_date
164 ,p_pgm_id => p_pgm_id
165 ,p_pl_id => p_pl_id
166 ,p_oipl_id => p_oipl_id
167 ,p_plip_id => p_plip_id
168 ,p_ptip_id => p_ptip_id
169 ,p_inst_set => l_eligprof_dets
170 ,p_inst_count => l_inst_count);
171 --
172 -- Loop through all profiles and set the boolean expressions on if the flag
173 -- is set.
174 --
175 IF l_inst_count > 0 THEN
176 --
177 FOR l_count IN l_eligprof_dets.FIRST .. l_eligprof_dets.LAST LOOP
178 --
179 IF l_eligprof_dets(l_count).elig_age_flag = 'Y'
180 AND NOT l_age_flag THEN
181 --
182 l_age_flag := TRUE;
183 --
184 END IF;
185 --
186 IF l_eligprof_dets(l_count).elig_los_flag = 'Y'
187 AND NOT l_los_flag THEN
188 --
189 l_los_flag := TRUE;
190 --
191 END IF;
192 --
193 IF l_eligprof_dets(l_count).elig_comp_lvl_flag = 'Y'
194 AND NOT l_cmp_flag THEN
195 --
196 l_cmp_flag := TRUE;
197 --
198 END IF;
199 --
200 IF l_eligprof_dets(l_count).elig_pct_fl_tm_flag = 'Y'
201 AND NOT l_pft_flag THEN
202 --
203 l_pft_flag := TRUE;
204 --
205 END IF;
206 --
207 IF l_eligprof_dets(l_count).elig_hrs_wkd_flag = 'Y'
208 AND NOT l_hrw_flag THEN
209 --
210 l_hrw_flag := TRUE;
211 --
212 END IF;
213 --
214 IF l_eligprof_dets(l_count).elig_cmbn_age_los_flag = 'Y'
215 AND NOT l_cal_flag THEN
216 --
217 l_cal_flag := TRUE;
218 --
219 END IF;
220 --
221 END LOOP;
222 --
223 IF l_age_flag THEN
224 --
225 l_flag_bit_val :=
226 l_flag_bit_val + ben_manage_life_events.g_age_flag;
227 --
228 END IF;
229 --
230 IF l_los_flag THEN
231 --
232 l_flag_bit_val :=
233 l_flag_bit_val + ben_manage_life_events.g_los_flag;
234 --
235 END IF;
236 --
237 IF l_cmp_flag THEN
238 --
239 l_flag_bit_val :=
240 l_flag_bit_val + ben_manage_life_events.g_cmp_flag;
241 --
242 END IF;
243 --
244 IF l_pft_flag THEN
245 --
246 l_flag_bit_val :=
247 l_flag_bit_val + ben_manage_life_events.g_pft_flag;
248 --
249 END IF;
250 --
251 IF l_hrw_flag THEN
252 --
253 l_flag_bit_val :=
254 l_flag_bit_val + ben_manage_life_events.g_hrw_flag;
255 --
256 END IF;
257 --
258 IF l_cal_flag THEN
259 --
260 l_flag_bit_val :=
261 l_flag_bit_val + ben_manage_life_events.g_cal_flag;
262 --
263 END IF;
264 --
265 END IF;
266 --
267 END IF;
268 --
269 hr_utility.set_location('Attempting to set drvbl_fctr_apls_rts_flag ' ||
270 l_package
271 ,10);
272 --
273 -- OIPLIP are special cases so we have to check if there are rates attached
274 --
275 IF p_drvbl_fctr_apls_rts_flag = 'Y'
276 OR p_oiplip_id IS NOT NULL THEN
277 --
278 -- BUILD SQL statement, we have to bind just the column name
279 --
280 IF p_pgm_id IS NOT NULL THEN
281 --
282 l_sql := REPLACE(l_sql, '{OBJECT}', 'pgm_id');
283 /*
284 l_sql := REPLACE(l_sql, '{OBJECT_VALUE}', p_pgm_id);
285 */
286 l_cobj_id := p_pgm_id;
287 --
288 ELSIF p_pl_id IS NOT NULL THEN
289 --
290 l_sql := REPLACE(l_sql, '{OBJECT}', 'pl_id');
291 /*
292 l_sql := REPLACE(l_sql, '{OBJECT_VALUE}', p_pl_id);
293 */
294 l_prem_sql := REPLACE(l_prem_sql, '{OBJECT}', 'pl_id');
295 /*
296 l_prem_sql := REPLACE(l_prem_sql, '{OBJECT_VALUE}', p_pl_id);
297 */
298 l_cvg_sql := REPLACE(l_cvg_sql, '{OBJECT}', 'pl_id');
299 /*
300 l_cvg_sql := REPLACE(l_cvg_sql, '{OBJECT_VALUE}', p_pl_id);
301 */
302 l_cobj_id := p_pl_id;
303 --
304 ELSIF p_oipl_id IS NOT NULL THEN
305 --
306 l_sql := REPLACE(l_sql, '{OBJECT}', 'oipl_id');
307 /*
308 l_sql := REPLACE(l_sql, '{OBJECT_VALUE}', p_oipl_id);
309 */
310 l_prem_sql := REPLACE(l_prem_sql, '{OBJECT}', 'oipl_id');
311 /*
312 l_prem_sql := REPLACE(l_prem_sql, '{OBJECT_VALUE}', p_oipl_id);
313 */
314 l_cvg_sql := REPLACE(l_cvg_sql, '{OBJECT}', 'oipl_id');
315 /*
316 l_cvg_sql := REPLACE(l_cvg_sql, '{OBJECT_VALUE}', p_oipl_id);
317 */
318 l_cobj_id := p_oipl_id;
319 --
320 ELSIF p_plip_id IS NOT NULL THEN
321 --
322 l_sql := REPLACE(l_sql, '{OBJECT}', 'plip_id');
323 /*
324 l_sql := REPLACE(l_sql, '{OBJECT_VALUE}', p_plip_id);
325 */
326 l_cvg_sql := REPLACE(l_cvg_sql, '{OBJECT}', 'plip_id');
327 /*
328 l_cvg_sql := REPLACE(l_cvg_sql, '{OBJECT_VALUE}', p_plip_id);
329 */
330 l_cobj_id := p_plip_id;
331 --
332 ELSIF p_ptip_id IS NOT NULL THEN
333 --
334 l_sql := REPLACE(l_sql, '{OBJECT}', 'ptip_id');
335 /*
336 l_sql := REPLACE(l_sql, '{OBJECT_VALUE}', p_ptip_id);
337 */
338 l_cobj_id := p_ptip_id;
339 --
340 ELSIF p_oiplip_id IS NOT NULL THEN
341 --
342 l_sql := REPLACE(l_sql, '{OBJECT}', 'oiplip_id');
343 /*
344 l_sql := REPLACE(l_sql, '{OBJECT_VALUE}', p_oiplip_id);
345 */
346 l_cobj_id := p_oiplip_id;
347 --
348 END IF;
349 --
350 -- Dynamically bind the rest of the variables
351 --
352 IF p_pl_id IS NOT NULL
353 OR p_oipl_id IS NOT NULL THEN
354 --
355 EXECUTE IMMEDIATE l_sql
356 INTO l_oipl_abr_count, l_age_rt_flag, l_los_rt_flag, l_cmp_rt_flag, l_pft_rt_flag, l_hrw_rt_flag, l_cal_rt_flag
357 USING l_cobj_id
358 ,p_business_group_id
359 ,p_effective_date
360 ,p_effective_date
361 ,p_effective_date;
362 --
363 --START Option Level Rates
364 if l_oipl_abr_count = 0 and p_oipl_id is not null then
365 --
366 EXECUTE IMMEDIATE l_opt_sql
367 INTO l_opt_id
368 USING p_oipl_id
369 ,p_effective_date
370 ,p_business_group_id ;
371 --
372 if l_opt_id is not null then
373 --
374 l_sql := REPLACE(l_sql, '{OBJECT}', 'opt_id');
375 --
376 EXECUTE IMMEDIATE l_sql
377 INTO l_oipl_abr_count, l_age_rt_flag, l_los_rt_flag, l_cmp_rt_flag,
378 l_pft_rt_flag, l_hrw_rt_flag, l_cal_rt_flag
379 USING l_opt_id
380 ,p_business_group_id
381 ,p_effective_date
382 ,p_effective_date
383 ,p_effective_date;
384 --
385 end if;
386 --
387 end if;
388 --END Option Level Rates
389 --
390 EXECUTE IMMEDIATE l_prem_sql
391 INTO l_prem_age_rt_flag, l_prem_los_rt_flag, l_prem_cmp_rt_flag, l_prem_pft_rt_flag, l_prem_hrw_rt_flag, l_prem_cal_rt_flag
392 USING l_cobj_id
393 ,p_business_group_id
394 ,p_effective_date
395 ,p_effective_date
396 ,p_effective_date;
397 EXECUTE IMMEDIATE l_cvg_sql
398 INTO l_cvg_age_rt_flag, l_cvg_los_rt_flag, l_cvg_cmp_rt_flag, l_cvg_pft_rt_flag, l_cvg_hrw_rt_flag, l_cvg_cal_rt_flag
399 USING l_cobj_id
400 ,p_business_group_id
401 ,p_effective_date
402 ,p_effective_date
403 ,p_effective_date;
404 --
405 l_age_rt_flag :=
406 NVL(l_age_rt_flag, 0) + NVL(l_prem_age_rt_flag, 0) +
407 NVL(l_cvg_age_rt_flag, 0);
408 --
412 --
409 l_los_rt_flag :=
410 NVL(l_los_rt_flag, 0) + NVL(l_prem_los_rt_flag, 0) +
411 NVL(l_cvg_los_rt_flag, 0);
413 l_cmp_rt_flag :=
414 NVL(l_cmp_rt_flag, 0) + NVL(l_prem_cmp_rt_flag, 0) +
415 NVL(l_cvg_cmp_rt_flag, 0);
416 --
417 l_pft_rt_flag :=
418 NVL(l_pft_rt_flag, 0) + NVL(l_prem_pft_rt_flag, 0) +
419 NVL(l_cvg_pft_rt_flag, 0);
420 --
421 l_hrw_rt_flag :=
422 NVL(l_hrw_rt_flag, 0) + NVL(l_prem_hrw_rt_flag, 0) +
423 NVL(l_cvg_hrw_rt_flag, 0);
424 --
425 l_cal_rt_flag :=
426 NVL(l_cal_rt_flag, 0) + NVL(l_prem_cal_rt_flag, 0) +
427 NVL(l_cvg_cal_rt_flag, 0);
428 --
429 ELSIF p_plip_id IS NOT NULL THEN
430 --
431 EXECUTE IMMEDIATE l_sql
432 INTO l_oipl_abr_count,l_age_rt_flag, l_los_rt_flag, l_cmp_rt_flag, l_pft_rt_flag, l_hrw_rt_flag, l_cal_rt_flag
433 USING l_cobj_id
434 ,p_business_group_id
435 ,p_effective_date
436 ,p_effective_date
437 ,p_effective_date;
438 EXECUTE IMMEDIATE l_cvg_sql
439 INTO l_cvg_age_rt_flag, l_cvg_los_rt_flag, l_cvg_cmp_rt_flag, l_cvg_pft_rt_flag, l_cvg_hrw_rt_flag, l_cvg_cal_rt_flag
440 USING l_cobj_id
441 ,p_business_group_id
442 ,p_effective_date
443 ,p_effective_date
444 ,p_effective_date;
445 --
446 l_age_rt_flag := NVL(l_age_rt_flag, 0) + NVL(l_cvg_age_rt_flag, 0);
447 --
448 l_los_rt_flag := NVL(l_los_rt_flag, 0) + NVL(l_cvg_los_rt_flag, 0);
449 --
450 l_cmp_rt_flag := NVL(l_cmp_rt_flag, 0) + NVL(l_cvg_cmp_rt_flag, 0);
451 --
452 l_pft_rt_flag := NVL(l_pft_rt_flag, 0) + NVL(l_cvg_pft_rt_flag, 0);
453 --
454 l_hrw_rt_flag := NVL(l_hrw_rt_flag, 0) + NVL(l_cvg_hrw_rt_flag, 0);
455 --
456 l_cal_rt_flag := NVL(l_cal_rt_flag, 0) + NVL(l_cvg_cal_rt_flag, 0);
457 --
458 ELSE
459 --
460 EXECUTE IMMEDIATE l_sql
461 INTO l_oipl_abr_count, l_age_rt_flag, l_los_rt_flag, l_cmp_rt_flag, l_pft_rt_flag, l_hrw_rt_flag, l_cal_rt_flag
462 USING l_cobj_id
463 ,p_business_group_id
464 ,p_effective_date
465 ,p_effective_date
466 ,p_effective_date;
467 --
468 END IF;
469 --
470 IF l_age_rt_flag > 0 THEN
471 --
472 l_flag_bit_val :=
473 l_flag_bit_val + ben_manage_life_events.g_age_rt_flag;
474 --
475 END IF;
476 --
477 IF l_los_rt_flag > 0 THEN
478 --
479 l_flag_bit_val :=
480 l_flag_bit_val + ben_manage_life_events.g_los_rt_flag;
481 --
482 END IF;
483 --
484 IF l_cmp_rt_flag > 0 THEN
485 --
486 l_flag_bit_val :=
487 l_flag_bit_val + ben_manage_life_events.g_cmp_rt_flag;
488 --
489 END IF;
490 --
491 IF l_pft_rt_flag > 0 THEN
492 --
493 l_flag_bit_val :=
494 l_flag_bit_val + ben_manage_life_events.g_pft_rt_flag;
495 --
496 END IF;
497 --
498 IF l_hrw_rt_flag > 0 THEN
499 --
500 l_flag_bit_val :=
501 l_flag_bit_val + ben_manage_life_events.g_hrw_rt_flag;
502 --
503 END IF;
504 --
505 IF l_cal_rt_flag > 0 THEN
506 --
507 l_flag_bit_val :=
508 l_flag_bit_val + ben_manage_life_events.g_cal_rt_flag;
509 --
510 END IF;
511 --
512 END IF;
513 hr_utility.set_location('l_flag_bit_val '|| l_flag_bit_val, 10);
514 --
515 -- If there is a derived factor attached to a comp object and the
516 -- derivable factor parameter is 'NONE', set the derivable factor
517 -- parameter to 'ASC' so the derived factor is evaluated.
518 -- Bug 2894200.
519 --
520 if (l_flag_bit_val <> 0) AND (ben_manage_life_events.g_derivable_factors = 'NONE') then
521 hr_utility.set_location('g_derivable_factors '|| ben_manage_life_events.g_derivable_factors, 10);
522 ben_manage_life_events.g_derivable_factors := 'ASC';
523 fnd_message.set_name('BEN','BEN_93605_RESET_DRVD_FCTR_PARM');
524 benutils.write(p_text => fnd_message.get);
525 end if;
526 --
527 RETURN l_flag_bit_val;
528 --
529 hr_utility.set_location('Leaving ' || l_package, 10);
530 --
531 END set_flag_bit_val;
532 --
533 PROCEDURE load_cache(
534 p_pl_id IN NUMBER DEFAULT NULL
535 ,p_pgm_id IN NUMBER DEFAULT NULL
536 ,p_oipl_id IN NUMBER DEFAULT NULL
537 ,p_plip_id IN NUMBER DEFAULT NULL
538 ,p_ptip_id IN NUMBER DEFAULT NULL
539 ,p_oiplip_id IN NUMBER DEFAULT NULL
540 ,p_pl_nip IN VARCHAR2 DEFAULT 'N'
541 ,p_trk_inelig_per_flag IN VARCHAR2 DEFAULT 'N'
542 ,p_par_pgm_id IN NUMBER DEFAULT NULL
543 ,p_par_ptip_id IN NUMBER DEFAULT NULL
544 ,p_par_plip_id IN NUMBER DEFAULT NULL
545 ,p_par_pl_id IN NUMBER DEFAULT NULL
549 --
546 ,p_par_opt_id IN NUMBER DEFAULT NULL
547 ,p_flag_bit_val IN BINARY_INTEGER DEFAULT NULL
548 ,p_oiplip_flag_bit_val IN BINARY_INTEGER DEFAULT NULL) IS
550 l_package VARCHAR2(80) := g_package || '.load_cache';
551 l_count NUMBER;
552 --
553 BEGIN
554 --
555 hr_utility.set_location('Entering ' || l_package, 10);
556 --
557 -- Load cache with comp object details
558 --
559 IF NOT ben_manage_life_events.g_cache_proc_object.EXISTS(1) THEN
560 --
561 l_count := 1;
562 --
563 ELSE
564 --
565 l_count := ben_manage_life_events.g_cache_proc_object.LAST + 1;
566 --
567 END IF;
568 --
569 ben_manage_life_events.g_cache_proc_object(l_count).pl_id :=
570 p_pl_id;
571 ben_manage_life_events.g_cache_proc_object(l_count).pgm_id :=
572 p_pgm_id;
573 ben_manage_life_events.g_cache_proc_object(l_count).oipl_id :=
574 p_oipl_id;
575 ben_manage_life_events.g_cache_proc_object(l_count).plip_id :=
576 p_plip_id;
577 ben_manage_life_events.g_cache_proc_object(l_count).ptip_id :=
578 p_ptip_id;
579 ben_manage_life_events.g_cache_proc_object(l_count).oiplip_id :=
580 p_oiplip_id;
581 ben_manage_life_events.g_cache_proc_object(l_count).pl_nip :=
582 p_pl_nip;
583 ben_manage_life_events.g_cache_proc_object(l_count).trk_inelig_per_flag :=
584 p_trk_inelig_per_flag;
585 ben_manage_life_events.g_cache_proc_object(l_count).par_pgm_id :=
586 p_par_pgm_id;
587 ben_manage_life_events.g_cache_proc_object(l_count).par_ptip_id :=
588 p_par_ptip_id;
589 ben_manage_life_events.g_cache_proc_object(l_count).par_plip_id :=
590 p_par_plip_id;
591 ben_manage_life_events.g_cache_proc_object(l_count).par_pl_id :=
592 p_par_pl_id;
593 ben_manage_life_events.g_cache_proc_object(l_count).par_opt_id :=
594 p_par_opt_id;
595 ben_manage_life_events.g_cache_proc_object(l_count).flag_bit_val :=
596 p_flag_bit_val;
597 ben_manage_life_events.g_cache_proc_object(l_count).oiplip_flag_bit_val :=
598 p_oiplip_flag_bit_val;
599 --
600 hr_utility.set_location('Leaving ' || l_package, 10);
601 --
602 END load_cache;
603 --
604 PROCEDURE cache_working_data(
605 p_business_group_id IN NUMBER
606 ,p_effective_date IN DATE) IS
607 --
608 l_package VARCHAR2(80) := g_package || '.cache_working_data';
609 l_meaning hr_lookups.meaning%TYPE;
610 l_lookup_code hr_lookups.lookup_code%TYPE;
611 l_ler_id ben_ler_f.ler_id%TYPE;
612 --
613 CURSOR c_comp_object_meanings IS
614 SELECT hr.meaning
615 ,hr.lookup_code
616 FROM hr_lookups hr
617 WHERE hr.lookup_type = 'BEN_COMP_OBJ'
618 AND hr.enabled_flag = 'Y'
619 AND p_effective_date BETWEEN NVL(hr.start_date_active
620 ,p_effective_date)
621 AND NVL(hr.end_date_active, p_effective_date)
622 AND hr.lookup_code IN ('PLIP', 'PGM', 'PLTYP', 'PTIP', 'PL', 'OIPL');
623 --
624 BEGIN
625 --
626 hr_utility.set_location('Entering ' || l_package, 10);
627 --
628 -- Set up cache details for all comp object types
629 --
630 OPEN c_comp_object_meanings;
631 --
632 LOOP
633 --
634 -- Fetch all values from the cursor and cache depending on code
635 --
636 FETCH c_comp_object_meanings INTO l_meaning, l_lookup_code;
637 EXIT WHEN c_comp_object_meanings%NOTFOUND;
638 --
639 IF l_lookup_code = 'PLIP' THEN
640 --
641 ben_manage_life_events.g_cache_comp_objects.plip := l_meaning;
642 --
643 ELSIF l_lookup_code = 'PGM' THEN
644 --
645 ben_manage_life_events.g_cache_comp_objects.pgm := l_meaning;
646 --
647 ELSIF l_lookup_code = 'PLTYP' THEN
648 --
649 ben_manage_life_events.g_cache_comp_objects.pltyp := l_meaning;
650 --
651 ELSIF l_lookup_code = 'PTIP' THEN
652 --
653 ben_manage_life_events.g_cache_comp_objects.ptip := l_meaning;
654 --
655 ELSIF l_lookup_code = 'PL' THEN
656 --
657 ben_manage_life_events.g_cache_comp_objects.pl := l_meaning;
658 --
659 ELSIF l_lookup_code = 'OIPL' THEN
660 --
664 --
661 ben_manage_life_events.g_cache_comp_objects.oipl := l_meaning;
662 --
663 END IF;
665 END LOOP;
666 --
667 CLOSE c_comp_object_meanings;
668 --
669 ben_manage_life_events.g_cached_objects := TRUE;
670 --
671 hr_utility.set_location('Leaving ' || l_package, 10);
672 --
673 END cache_working_data;
674 --
675 PROCEDURE write_multi_session_cache(
676 p_effective_date IN DATE
677 ,p_business_group_id IN NUMBER
678 ,p_mode in varchar2
679 ,p_pgm_id IN NUMBER
680 ,p_pl_id IN NUMBER
681 ,p_no_programs in varchar2
682 ,p_no_plans in varchar2
683 ,p_pl_typ_id IN NUMBER
684 --
685 ,p_comp_obj_cache_id OUT NOCOPY NUMBER
686 )
687 IS
688 --
689 PRAGMA AUTONOMOUS_TRANSACTION;
690 --
691 l_package VARCHAR2(80)
692 := g_package || '.write_multi_session_cache';
693 --
694 l_comp_obj_cache_row_id_va benutils.g_number_table := benutils.g_number_table();
695 l_comp_obj_cache_id_va benutils.g_number_table := benutils.g_number_table();
696 l_pl_id_va benutils.g_number_table := benutils.g_number_table();
697 l_pgm_id_va benutils.g_number_table := benutils.g_number_table();
698 l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
699 l_ptip_id_va benutils.g_number_table := benutils.g_number_table();
700 l_plip_id_va benutils.g_number_table := benutils.g_number_table();
701 l_pl_nip_va benutils.g_v2_30_table := benutils.g_v2_30_table();
702 l_elig_tran_state_va benutils.g_v2_30_table := benutils.g_v2_30_table();
703 l_trk_inelig_per_flag_va benutils.g_v2_30_table := benutils.g_v2_30_table();
704 l_par_pgm_id_va benutils.g_number_table := benutils.g_number_table();
705 l_par_ptip_id_va benutils.g_number_table := benutils.g_number_table();
706 l_par_plip_id_va benutils.g_number_table := benutils.g_number_table();
707 l_par_pl_id_va benutils.g_number_table := benutils.g_number_table();
708 l_par_opt_id_va benutils.g_number_table := benutils.g_number_table();
709 l_flag_bit_val_va benutils.g_number_table := benutils.g_number_table();
710 l_oiplip_flag_bit_val_va benutils.g_number_table := benutils.g_number_table();
711 l_oiplip_id_va benutils.g_number_table := benutils.g_number_table();
712 --
713 l_comp_obj_cache_id NUMBER;
714 l_count number;
715 l_seqnextval number;
716 --
717 cursor c_getseq
718 is
719 select ben_comp_obj_cache_row_s.nextval
720 from sys.dual;
721
722 --
723 BEGIN
724 --
725 INSERT INTO ben_comp_obj_cache
726 (
727 comp_obj_cache_id
728 ,effective_date
729 ,business_group_id
730 ,timestamp
731 ,mode_cd
732 ,pgm_id
733 ,pl_id
734 ,no_programs
735 ,no_plans
736 ,pl_typ_id)
737 VALUES(
738 ben_comp_obj_cache_s.nextval
739 ,p_effective_date
740 ,p_business_group_id
741 ,SYSDATE
742 ,p_mode
743 ,p_pgm_id
744 ,p_pl_id
745 ,p_no_programs
746 ,p_no_plans
747 ,p_pl_typ_id
748 )
749 RETURNING comp_obj_cache_id
750 INTO l_comp_obj_cache_id;
751 --
752 COMMIT;
753 --
754 select count(*) into l_count
755 from ben_comp_obj_cache
756 where business_group_id = p_business_group_id
757 and effective_date = p_effective_date;
758
759 IF ben_manage_life_events.g_cache_proc_object.COUNT > 0 THEN
760 --
761 -- Add details to varray
762 --
763 FOR ele_num IN
764 ben_manage_life_events.g_cache_proc_object.FIRST
765 .. ben_manage_life_events.g_cache_proc_object.LAST
766 LOOP
767 --
768 open c_getseq;
769 fetch c_getseq into l_seqnextval;
770 close c_getseq;
771 --
772 l_comp_obj_cache_row_id_va.extend(1);
773 l_comp_obj_cache_row_id_va(ele_num) := l_seqnextval;
774 --
775 l_comp_obj_cache_id_va.extend(1);
776 l_comp_obj_cache_id_va(ele_num) := l_comp_obj_cache_id;
777 --
778 l_pl_id_va.extend(1);
779 l_pl_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).pl_id;
780 --
781 l_pgm_id_va.extend(1);
782 l_pgm_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).pgm_id;
783 --
784 l_oipl_id_va.extend(1);
785 l_oipl_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).oipl_id;
786 --
787 l_ptip_id_va.extend(1);
788 l_ptip_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).ptip_id;
789 --
790 l_plip_id_va.extend(1);
791 l_plip_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).plip_id;
792 --
796 l_elig_tran_state_va.extend(1);
793 l_pl_nip_va.extend(1);
794 l_pl_nip_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).pl_nip;
795 --
797 l_elig_tran_state_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).elig_tran_state;
798 --
799 l_trk_inelig_per_flag_va.extend(1);
800 l_trk_inelig_per_flag_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).trk_inelig_per_flag;
801 --
802 l_par_pgm_id_va.extend(1);
803 l_par_pgm_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).par_pgm_id;
804 --
805 l_par_ptip_id_va.extend(1);
806 l_par_ptip_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).par_ptip_id;
807 --
808 l_par_plip_id_va.extend(1);
809 l_par_plip_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).par_plip_id;
810 --
811 l_par_pl_id_va.extend(1);
812 l_par_pl_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).par_pl_id;
813 --
814 l_par_opt_id_va.extend(1);
815 l_par_opt_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).par_opt_id;
816 --
817 l_flag_bit_val_va.extend(1);
818 l_flag_bit_val_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).flag_bit_val;
819 --
820 l_oiplip_flag_bit_val_va.extend(1);
821 l_oiplip_flag_bit_val_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).oiplip_flag_bit_val;
822 --
823 l_oiplip_id_va.extend(1);
824 l_oiplip_id_va(ele_num) := ben_manage_life_events.g_cache_proc_object(ele_num).oiplip_id;
825 --
826 END LOOP;
827 --
828 if l_comp_obj_cache_row_id_va.count > 0 then
829 --
830 FORALL i IN l_comp_obj_cache_row_id_va.FIRST .. l_comp_obj_cache_row_id_va.LAST
831 INSERT INTO ben_comp_obj_cache_row
832 (comp_obj_cache_row_id
833 ,comp_obj_cache_id
834 ,pl_id
835 ,pgm_id
836 ,oipl_id
837 ,ptip_id
838 ,plip_id
839 ,pl_nip
840 ,elig_tran_state
841 ,trk_inelig_per_flag
842 ,par_pgm_id
843 ,par_ptip_id
844 ,par_plip_id
845 ,par_pl_id
846 ,par_opt_id
847 ,flag_bit_val
848 ,oiplip_flag_bit_val
849 ,oiplip_id
850 )
851 VALUES
852 (l_comp_obj_cache_row_id_va(i)
853 ,l_comp_obj_cache_id_va(i)
854 ,l_pl_id_va(i)
855 ,l_pgm_id_va(i)
856 ,l_oipl_id_va(i)
857 ,l_ptip_id_va(i)
858 ,l_plip_id_va(i)
859 ,l_pl_nip_va(i)
860 ,l_elig_tran_state_va(i)
861 ,l_trk_inelig_per_flag_va(i)
862 ,l_par_pgm_id_va(i)
863 ,l_par_ptip_id_va(i)
864 ,l_par_plip_id_va(i)
865 ,l_par_pl_id_va(i)
866 ,l_par_opt_id_va(i)
867 ,l_flag_bit_val_va(i)
868 ,l_oiplip_flag_bit_val_va(i)
869 ,l_oiplip_id_va(i)
870 );
871 --
872 end if;
873 --
874 end if;
875 --
876 COMMIT;
877 p_comp_obj_cache_id := l_comp_obj_cache_id;
878 END write_multi_session_cache;
879 --
880 PROCEDURE flush_multi_session_cache(
881 p_effective_date IN DATE DEFAULT NULL
882 ,p_business_group_id IN NUMBER DEFAULT NULL) IS
883 --
884 PRAGMA AUTONOMOUS_TRANSACTION;
885 --
886 l_package VARCHAR2(80) := g_package || '.flush_multi_session_cache';
887 --
888 l_coc_id_va benutils.g_number_table := benutils.g_number_table();
889 --
890 cursor c_flushcocs
891 (c_bgp_id number
892 ,c_eff_date date
893 )
894 is
895 select coc.comp_obj_cache_id
896 from ben_comp_obj_cache coc
897 where coc.business_group_id = c_bgp_id
898 and coc.effective_date = c_eff_date;
899 --
900 BEGIN
901 --
902 IF p_business_group_id IS NOT NULL
903 and p_effective_date IS NOT NULL
904 THEN
905 --
906 open c_flushcocs
907 (c_bgp_id => p_business_group_id
908 ,c_eff_date => p_effective_date
909 );
910 fetch c_flushcocs BULK COLLECT INTO l_coc_id_va;
911 close c_flushcocs;
912 --
913 if l_coc_id_va.count > 0
914 then
915 --
916 forall ccrelenum in l_coc_id_va.first..l_coc_id_va.last
917 delete from ben_comp_obj_cache_row ccr
918 where ccr.comp_obj_cache_id = l_coc_id_va(ccrelenum);
919 --
920 end if;
921 --
922
923 /*
924 DELETE
925 FROM ben_comp_obj_cache_row cjr
926 WHERE EXISTS(SELECT NULL
927 FROM ben_comp_obj_cache cjc
928 WHERE cjc.comp_obj_cache_id = cjr.comp_obj_cache_id
929 AND cjc.business_group_id = p_business_group_id
930 AND cjc.effective_date = p_effective_date
931 );
932 --
936 WHERE business_group_id = p_business_group_id
933 */
934 DELETE
935 FROM ben_comp_obj_cache
937 AND effective_date = p_effective_date;
938 --
939 ELSE
940 --
941 DELETE
942 FROM ben_comp_obj_cache_row cjr;
943 --
944 DELETE
945 FROM ben_comp_obj_cache;
946 --
947 END IF;
948 --
949 COMMIT;
950 --
951 END flush_multi_session_cache;
952 --
953 PROCEDURE build_comp_object_list(
954 p_benefit_action_id IN NUMBER DEFAULT -1
955 ,p_comp_selection_rule_id IN NUMBER DEFAULT NULL
956 ,p_effective_date IN DATE
957 ,p_pgm_id IN NUMBER DEFAULT NULL
958 ,p_business_group_id IN NUMBER DEFAULT NULL
959 ,p_pl_id IN NUMBER DEFAULT NULL
960 ,p_oipl_id IN NUMBER DEFAULT NULL
961 --
962 -- PB : 5422 :
963 -- Pass on the asnd_lf_evt_dt
964 --
965 ,p_asnd_lf_evt_dt IN DATE DEFAULT NULL
966 -- ,p_popl_enrt_typ_cycl_id IN NUMBER DEFAULT NULL
967 ,p_no_programs IN VARCHAR2 DEFAULT 'N'
968 ,p_no_plans IN VARCHAR2 DEFAULT 'N'
969 ,p_rptg_grp_id IN NUMBER DEFAULT NULL
970 ,p_pl_typ_id IN NUMBER DEFAULT NULL
971 ,p_opt_id IN NUMBER DEFAULT NULL
972 ,p_eligy_prfl_id IN NUMBER DEFAULT NULL
973 ,p_vrbl_rt_prfl_id IN NUMBER DEFAULT NULL
974 ,p_thread_id IN NUMBER DEFAULT NULL
975 ,p_mode IN VARCHAR2
976 --
977 -- PB : Helathnet change
978 --
979 ,p_person_id in number default null
980 ,p_lmt_prpnip_by_org_flag in varchar2 default 'N') IS
981 --
982 l_package VARCHAR2(80)
983 := g_package || '.build_comp_object_list';
984 --
985 l_per_org_id NUMBER;
986 --
987 TYPE cur_type IS REF CURSOR;
988 c_chgdata cur_type;
989 --
990 TYPE v2_set IS TABLE OF VARCHAR2(30)
991 INDEX BY BINARY_INTEGER;
992 --
993 type pgm_rec is record
994 (pgm_id ben_pgm_f.pgm_id%type
995 ,drvbl_fctr_prtn_elig_flag ben_pgm_f.drvbl_fctr_prtn_elig_flag%type
996 ,drvbl_fctr_apls_rts_flag ben_pgm_f.drvbl_fctr_apls_rts_flag%type
997 ,trk_inelig_per_flag ben_pgm_f.trk_inelig_per_flag%type
998 );
999 --
1000 l_plninst_set ben_pln_cache.g_bgppln_cache;
1001 l_copinst_set ben_cop_cache.g_bgpcop_cache;
1002 --
1003 l_oipl_id ben_oipl_f.oipl_id%TYPE;
1004 l_pl_id ben_pl_f.pl_id%TYPE;
1005 l_pgm_id ben_pgm_f.pgm_id%TYPE;
1006 l_pgm pgm_rec;
1007 l_pln ben_pln_cache.g_bgppln_rec;
1008 l_cop ben_cop_cache.g_bgpcop_rec;
1009 l_plip ben_plip_f%ROWTYPE;
1010 l_ptip ben_ptip_f%ROWTYPE;
1011 l_opt ben_opt_f%ROWTYPE;
1012 l_oiplip ben_oiplip_f%ROWTYPE;
1013 l_epa ben_prtn_elig_f%ROWTYPE;
1014 l_plnnip_set ben_pln_cache.g_nipplnpln_cache;
1015 l_inst_count NUMBER;
1016 l_plnrow_num BINARY_INTEGER;
1017 --
1018 l_ptp_opt_typ_cd varchar2(30);
1019 --
1020 CURSOR c_pgm
1021 IS
1022 select pgm.pgm_id,
1023 pgm.drvbl_fctr_prtn_elig_flag,
1024 pgm.drvbl_fctr_apls_rts_flag,
1025 pgm.trk_inelig_per_flag
1026 FROM -- ben_popl_yr_perd cpy
1027 ben_pgm_f pgm
1028 -- ,ben_yr_perd yrp
1029 WHERE pgm.business_group_id = p_business_group_id
1030 AND pgm.pgm_id = NVL(p_pgm_id, pgm.pgm_id)
1031 AND pgm.pgm_stat_cd = 'A'
1032 AND (
1033 pgm.pgm_typ_cd NOT IN ('COBRANFLX', 'COBRAFLX')
1034 OR p_mode NOT IN ('L', 'U'))
1035 -- GRADE/STEP
1036 AND ( (p_mode in('T', 'G') and pgm.pgm_typ_cd = 'GSP') OR
1037 (p_mode <> 'G' and pgm.pgm_typ_cd <> 'GSP')
1038 )
1039 AND p_effective_date BETWEEN pgm.effective_start_date
1040 AND pgm.effective_end_date
1041 AND p_mode not in ('D','I')
1042 AND (p_mode = 'G' or
1043 exists (select null
1044 from ben_yr_perd yrp,
1045 ben_popl_yr_perd cpy
1046 where cpy.pgm_id = pgm.pgm_id
1047 AND cpy.yr_perd_id = yrp.yr_perd_id
1048 AND cpy.business_group_id = pgm.business_group_id
1049 AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
1050
1051 AND pgm.alws_unrstrctd_enrt_flag =
1052 DECODE(p_mode, 'U', 'Y', pgm.alws_unrstrctd_enrt_flag)
1053 /* Make sure that program being linked to covers all the
1054 plan types that may or may not have been stated by the
1055 user. (PTIP)*/
1056 AND (
1057 EXISTS
1058 (SELECT NULL
1059 FROM ben_ptip_f ctp
1060 WHERE ctp.pgm_id = pgm.pgm_id
1061 AND ctp.pl_typ_id = NVL(p_pl_typ_id, ctp.pl_typ_id)
1062 AND ctp.business_group_id = pgm.business_group_id
1066 variable rate profile that has been specified by
1063 AND p_effective_date BETWEEN ctp.effective_start_date
1064 AND ctp.effective_end_date
1065 /* Make sure that the plan type in program is of the
1067 the user. */
1068 AND (
1069 EXISTS
1070 (SELECT NULL
1071 FROM ben_acty_base_rt_f abr
1072 ,ben_acty_vrbl_rt_f avr
1073 ,ben_vrbl_rt_prfl_f vpf
1074 WHERE abr.ptip_id = ctp.ptip_id
1075 AND abr.business_group_id =
1076 ctp.business_group_id
1077 AND p_effective_date BETWEEN abr.effective_start_date
1078 AND abr.effective_end_date
1079 AND avr.acty_base_rt_id =
1080 abr.acty_base_rt_id
1081 AND avr.business_group_id =
1082 abr.business_group_id
1083 AND p_effective_date BETWEEN avr.effective_start_date
1084 AND avr.effective_end_date
1085 AND vpf.vrbl_rt_prfl_id =
1086 avr.vrbl_rt_prfl_id
1087 AND vpf.business_group_id =
1088 avr.business_group_id
1089 AND vpf.vrbl_rt_prfl_id =
1090 p_vrbl_rt_prfl_id
1091 AND p_effective_date BETWEEN vpf.effective_start_date
1092 AND vpf.effective_end_date)
1093 OR p_vrbl_rt_prfl_id IS NULL))
1094 OR p_pl_typ_id IS NULL)
1095 /* Make sure that program being linked to covers all the
1096 plans that may or may not have been stated by the
1097 user. (PLIP)*/
1098 AND (
1099 EXISTS
1100 (SELECT NULL
1101 FROM ben_plip_f cpp
1102 WHERE cpp.pgm_id = pgm.pgm_id
1103 AND cpp.pl_id = NVL(p_pl_id, cpp.pl_id)
1104 AND cpp.business_group_id = pgm.business_group_id
1105 AND cpp.plip_stat_cd = 'A'
1106 AND p_effective_date BETWEEN cpp.effective_start_date
1107 AND cpp.effective_end_date
1108 /* Make sure that plan being linked to is of the
1109 eligibility profile that has been specified by
1110 the user. */
1111 AND (
1112 EXISTS
1113 (SELECT NULL
1114 FROM ben_prtn_elig_f epa2
1115 ,ben_prtn_elig_prfl_f cep
1116 ,ben_eligy_prfl_f elp
1117 WHERE epa2.pl_id = cpp.pl_id
1118 AND epa2.business_group_id =
1119 cpp.business_group_id
1120 AND p_effective_date BETWEEN epa2.effective_start_date
1121 AND epa2.effective_end_date
1122 AND cep.prtn_elig_id =
1123 epa2.prtn_elig_id
1124 AND cep.business_group_id =
1125 epa2.business_group_id
1126 AND p_effective_date BETWEEN cep.effective_start_date
1127 AND cep.effective_end_date
1128 AND elp.eligy_prfl_id =
1129 cep.eligy_prfl_id
1130 AND elp.business_group_id =
1131 cep.business_group_id
1132 AND elp.eligy_prfl_id =
1133 p_eligy_prfl_id
1134 AND p_effective_date BETWEEN elp.effective_start_date
1135 AND elp.effective_end_date)
1136 OR p_eligy_prfl_id IS NULL)
1137 /* Make sure that plan being linked to is of the
1138 reporting group that has been specified by
1139 the user. */
1140 AND (
1141 EXISTS
1142 (SELECT NULL
1143 FROM ben_rptg_grp bnr
1144 ,ben_popl_rptg_grp_f rgr
1145 WHERE bnr.rptg_grp_id = p_rptg_grp_id
1146 AND nvl(bnr.business_group_id,cpp.business_group_id) =
1147 cpp.business_group_id
1148 AND rgr.rptg_grp_id = bnr.rptg_grp_id
1152 nvl(bnr.business_group_id,rgr.business_group_id)
1149 AND p_effective_date BETWEEN rgr.effective_start_date
1150 AND rgr.effective_end_date
1151 AND rgr.business_group_id =
1153 AND rgr.pl_id = cpp.pl_id)
1154 OR p_rptg_grp_id IS NULL)
1155 /* Make sure that plan being linked to is of the
1156 variable rate profile that has been specified
1157 by the user. */
1158 AND (
1159 EXISTS
1160 (SELECT NULL
1161 FROM ben_acty_base_rt_f abr
1162 ,ben_acty_vrbl_rt_f avr
1163 ,ben_vrbl_rt_prfl_f vpf
1164 WHERE abr.pl_id = cpp.pl_id
1165 AND abr.business_group_id =
1166 pgm.business_group_id
1167 AND p_effective_date BETWEEN abr.effective_start_date
1168 AND abr.effective_end_date
1169 AND avr.acty_base_rt_id =
1170 abr.acty_base_rt_id
1171 AND avr.business_group_id =
1172 abr.business_group_id
1173 AND p_effective_date BETWEEN avr.effective_start_date
1174 AND avr.effective_end_date
1175 AND vpf.vrbl_rt_prfl_id =
1176 avr.vrbl_rt_prfl_id
1177 AND vpf.business_group_id =
1178 avr.business_group_id
1179 AND vpf.vrbl_rt_prfl_id =
1180 p_vrbl_rt_prfl_id
1181 AND p_effective_date BETWEEN vpf.effective_start_date
1182 AND vpf.effective_end_date)
1183 OR p_vrbl_rt_prfl_id IS NULL))
1184 OR p_pl_id IS NULL)
1185 /* Make sure that program being linked to covers all the
1186 options that may or may not have been stated by the
1187 user. (OIPL) */
1188 AND (
1189 EXISTS
1190 (SELECT NULL
1191 FROM ben_oipl_f cop, ben_opt_f opt
1192 WHERE cop.pl_id = NVL(p_pl_id, cop.pl_id)
1193 AND cop.opt_id = p_opt_id
1194 AND cop.oipl_stat_cd = 'A'
1195 AND cop.business_group_id = pgm.business_group_id
1196 AND p_effective_date BETWEEN cop.effective_start_date
1197 AND cop.effective_end_date
1198 AND opt.opt_id = cop.opt_id
1199 AND opt.business_group_id = cop.business_group_id
1200 AND p_effective_date BETWEEN opt.effective_start_date
1201 AND opt.effective_end_date
1202 /* Make sure that the option in the plan
1203 being linked to is of the eligibility
1204 profile that has been specified by the user. */
1205 AND (
1206 EXISTS
1207 (SELECT NULL
1208 FROM ben_prtn_elig_f epa2
1209 ,ben_prtn_elig_prfl_f cep
1210 ,ben_eligy_prfl_f elp
1211 WHERE epa2.oipl_id = cop.oipl_id
1212 AND epa2.business_group_id =
1213 cop.business_group_id
1214 AND p_effective_date BETWEEN epa2.effective_start_date
1215 AND epa2.effective_end_date
1216 AND cep.prtn_elig_id =
1217 epa2.prtn_elig_id
1218 AND cep.business_group_id =
1219 epa2.business_group_id
1220 AND p_effective_date BETWEEN cep.effective_start_date
1221 AND cep.effective_end_date
1222 AND elp.eligy_prfl_id =
1223 cep.eligy_prfl_id
1224 AND elp.business_group_id =
1225 cep.business_group_id
1226 AND elp.eligy_prfl_id =
1227 p_eligy_prfl_id
1228 AND p_effective_date BETWEEN elp.effective_start_date
1229 AND elp.effective_end_date)
1230 OR p_eligy_prfl_id IS NULL)
1231 /* Make sure that the options in plan being
1232 linked to is of the variable rate profile
1236 (SELECT NULL
1233 that has been specified by the user. */
1234 AND (
1235 EXISTS
1237 FROM ben_acty_base_rt_f abr
1238 ,ben_acty_vrbl_rt_f avr
1239 ,ben_vrbl_rt_prfl_f vpf
1240 WHERE abr.oipl_id = cop.oipl_id
1241 AND abr.business_group_id =
1242 cop.business_group_id
1243 AND p_effective_date BETWEEN abr.effective_start_date
1244 AND abr.effective_end_date
1245 AND avr.acty_base_rt_id =
1246 abr.acty_base_rt_id
1247 AND avr.business_group_id =
1248 abr.business_group_id
1249 AND p_effective_date BETWEEN avr.effective_start_date
1250 AND avr.effective_end_date
1251 AND vpf.vrbl_rt_prfl_id =
1252 avr.vrbl_rt_prfl_id
1253 AND vpf.business_group_id =
1254 avr.business_group_id
1255 AND vpf.vrbl_rt_prfl_id =
1256 p_vrbl_rt_prfl_id
1257 AND p_effective_date BETWEEN vpf.effective_start_date
1258 AND vpf.effective_end_date)
1259 OR p_vrbl_rt_prfl_id IS NULL))
1260 OR p_opt_id IS NULL)
1261 /* Make sure that program being linked to is of the
1262 variable rate profile that has been specified by the user. */
1263 AND (
1264 EXISTS
1265 (SELECT NULL
1266 FROM ben_acty_base_rt_f abr
1267 ,ben_acty_vrbl_rt_f avr
1268 ,ben_vrbl_rt_prfl_f vpf
1269 WHERE abr.pgm_id = pgm.pgm_id
1270 AND abr.business_group_id = pgm.business_group_id
1271 AND p_effective_date BETWEEN abr.effective_start_date
1272 AND abr.effective_end_date
1273 AND avr.acty_base_rt_id = abr.acty_base_rt_id
1274 AND avr.business_group_id = abr.business_group_id
1275 AND p_effective_date BETWEEN avr.effective_start_date
1276 AND avr.effective_end_date
1277 AND vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
1278 AND vpf.business_group_id = avr.business_group_id
1279 AND vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
1280 AND p_effective_date BETWEEN vpf.effective_start_date
1281 AND vpf.effective_end_date)
1282 OR p_vrbl_rt_prfl_id IS NULL)
1283 /* Make sure that program being linked to is of the
1284 reporting group that has been specified by the user. */
1285 AND (
1286 EXISTS
1287 (SELECT NULL
1288 FROM ben_rptg_grp bnr, ben_popl_rptg_grp_f rgr
1289 WHERE bnr.rptg_grp_id = p_rptg_grp_id
1290 AND nvl(bnr.business_group_id,pgm.business_group_id)
1291 = pgm.business_group_id
1292 AND rgr.rptg_grp_id = bnr.rptg_grp_id
1293 AND p_effective_date BETWEEN rgr.effective_start_date
1294 AND rgr.effective_end_date
1295 AND rgr.business_group_id =
1296 nvl(bnr.business_group_id,rgr.business_group_id)
1297 AND rgr.pgm_id = pgm.pgm_id)
1298 OR p_rptg_grp_id IS NULL)
1299 /* Make sure that program being linked to is of the
1300 eligibility profile that has been specified by the user. */
1301 AND (
1302 EXISTS
1303 (SELECT NULL
1304 FROM ben_prtn_elig_f epa2
1305 ,ben_prtn_elig_prfl_f cep
1306 ,ben_eligy_prfl_f elp
1307 WHERE epa2.pgm_id = pgm.pgm_id
1308 AND epa2.business_group_id = pgm.business_group_id
1309 AND p_effective_date BETWEEN epa2.effective_start_date
1310 AND epa2.effective_end_date
1311 AND cep.prtn_elig_id = epa2.prtn_elig_id
1312 AND cep.business_group_id = epa2.business_group_id
1313 AND p_effective_date BETWEEN cep.effective_start_date
1314 AND cep.effective_end_date
1315 AND elp.eligy_prfl_id = cep.eligy_prfl_id
1316 AND elp.business_group_id = cep.business_group_id
1317 AND elp.eligy_prfl_id = p_eligy_prfl_id
1318 AND p_effective_date BETWEEN elp.effective_start_date
1322 enrollment type cycle that has been specified by the user. */
1319 AND elp.effective_end_date)
1320 OR p_eligy_prfl_id IS NULL)
1321 /* Make sure that program being linked to is of the
1323 AND (
1324 EXISTS
1325 (SELECT NULL
1326 FROM ben_popl_enrt_typ_cycl_f pet,
1327 ben_enrt_perd enp
1328 WHERE pet.pgm_id = pgm.pgm_id
1329 AND p_effective_date BETWEEN pet.effective_start_date
1330 AND pet.effective_end_date
1331 AND pet.popl_enrt_typ_cycl_id =
1332 enp.popl_enrt_typ_cycl_id
1333 AND enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
1334 /* PB : 5422 AND enp.strt_dt =
1335 (SELECT enp1.strt_dt
1336 FROM ben_enrt_perd enp1
1337 WHERE enp1.enrt_perd_id =
1338 p_popl_enrt_typ_cycl_id) */
1339 AND enp.business_group_id = pet.business_group_id)
1340 OR p_asnd_lf_evt_dt IS NULL)
1341 /* Make sure that program being linked to org id of the person
1342 if the program selection is limited based on person's org id. */
1343 AND (
1344 EXISTS
1345 (SELECT NULL
1346 FROM ben_popl_org_f cpo,
1347 ben_popl_org_role_f cpr
1348 WHERE cpo.pgm_id = pgm.pgm_id
1349 AND p_effective_date BETWEEN cpo.effective_start_date
1350 AND cpo.effective_end_date
1351 AND cpo.popl_org_id = cpr.popl_org_id
1352 AND p_effective_date BETWEEN cpr.effective_start_date
1353 AND cpr.effective_end_date
1354 AND cpo.business_group_id = cpr.business_group_id
1355 AND cpr.org_role_typ_cd = 'POPLOWNR'
1356 AND cpo.organization_id = l_per_org_id)
1357 OR p_lmt_prpnip_by_org_flag = 'N'
1358 OR l_per_org_id IS NULL)
1359 -- PB 5422 OR p_popl_enrt_typ_cycl_id IS NULL)
1360 ORDER BY pgm.name;
1361 --
1362 CURSOR c_pgm2 IS
1363 select pgm.pgm_id,
1364 pgm.drvbl_fctr_prtn_elig_flag,
1365 pgm.drvbl_fctr_apls_rts_flag,
1366 pgm.trk_inelig_per_flag
1367 FROM ben_popl_yr_perd cpy
1368 ,ben_pgm_f pgm
1369 ,ben_yr_perd yrp
1370 WHERE pgm.business_group_id = p_business_group_id
1371 AND pgm.pgm_stat_cd = 'A'
1372 AND pgm.pgm_typ_cd LIKE 'COBRA%'
1373 AND p_effective_date BETWEEN pgm.effective_start_date
1374 AND pgm.effective_end_date
1375 AND cpy.pgm_id = pgm.pgm_id
1376 AND cpy.yr_perd_id = yrp.yr_perd_id
1377 AND cpy.business_group_id = pgm.business_group_id
1378 AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date
1379 AND pgm.alws_unrstrctd_enrt_flag =
1380 DECODE(p_mode, 'U', 'Y', pgm.alws_unrstrctd_enrt_flag)
1381 /* Make sure that program being linked to org id of the person
1382 if the program selection is limited based on person's org id. */
1383 AND (
1384 EXISTS
1385 (SELECT NULL
1386 FROM ben_popl_org_f cpo,
1387 ben_popl_org_role_f cpr
1388 WHERE cpo.pgm_id = pgm.pgm_id
1389 AND p_effective_date BETWEEN cpo.effective_start_date
1390 AND cpo.effective_end_date
1391 AND cpo.popl_org_id = cpr.popl_org_id
1392 AND p_effective_date BETWEEN cpr.effective_start_date
1393 AND cpr.effective_end_date
1394 AND cpo.business_group_id = cpr.business_group_id
1395 AND cpr.org_role_typ_cd = 'POPLOWNR'
1396 AND cpo.organization_id = l_per_org_id)
1397 OR p_lmt_prpnip_by_org_flag = 'N'
1398 OR l_per_org_id IS NULL)
1399 ORDER BY pgm.name;
1400 --
1401 CURSOR c_pln_nip
1402 is
1403 select pln.pl_id,
1404 pln.pl_typ_id,
1405 ptp.opt_typ_cd,
1406 pln.drvbl_fctr_prtn_elig_flag,
1407 pln.drvbl_fctr_apls_rts_flag,
1408 pln.trk_inelig_per_flag
1409 FROM ben_pl_f pln,
1410 ben_pl_typ_f ptp
1411 -- ben_yr_perd yrp,
1412 -- ben_popl_yr_perd cpy
1413 WHERE pln.business_group_id = p_business_group_id
1414 AND p_effective_date BETWEEN pln.effective_start_date
1415 AND pln.effective_end_date
1416 /* Bug No 4402873 Added condition to retrieve plans with code as
1417 'May Not be in Program' so that the plans with code as
1418 'Must be in Program' and not included in the program
1419 shall not get picked up*/
1420 and pln.pl_cd = 'MYNTBPGM'
1421 and pln.pl_typ_id = ptp.pl_typ_id
1425 or may not have been stated by the user. */
1422 and p_effective_date
1423 between ptp.effective_start_date and ptp.effective_end_date
1424 /* Make sure that the plan is not in the plip table.
1426 AND (p_mode IN ('P','G','D') or -- ICM
1427 exists (select null
1428 from ben_yr_perd yrp,
1429 ben_popl_yr_perd cpy
1430 where cpy.pl_id = pln.pl_id
1431 AND cpy.yr_perd_id = yrp.yr_perd_id
1432 AND cpy.business_group_id = pln.business_group_id
1433 AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
1434 AND pln.pl_stat_cd = 'A'
1435 AND pln.alws_unrstrctd_enrt_flag =
1436 DECODE(p_mode, 'U', 'Y','D','Y', pln.alws_unrstrctd_enrt_flag) -- ICM
1437 -- CWB changes
1438 -- ABSENCES : pickup only absence plans
1439 AND ((p_mode = 'W' and ptp.opt_typ_cd = 'CWB') or
1440 (p_mode = 'M' and ptp.opt_typ_cd = 'ABS') or
1441 (p_mode = 'P' and ptp.opt_typ_cd = 'PERACT') or
1442 (p_mode = 'I' and ptp.opt_typ_cd = 'COMP') or -- iREC changes
1443 (p_mode = 'D' and ptp.opt_typ_cd = 'ICM') or
1444 (p_mode not in ('W','M', 'P','D') and ptp.opt_typ_cd not in ('CWB','ABS', 'PERACT','ICM')) -- ICM
1445 )
1446 AND ptp.opt_typ_cd <> 'GDRLDR'
1447 AND NOT EXISTS(SELECT NULL
1448 FROM ben_plip_f cpp
1449 WHERE cpp.pl_id = pln.pl_id)
1450 /* We only want to report on these plans when pgm_id is null */
1451 AND p_pgm_id IS NULL
1452 AND pln.pl_id = NVL(p_pl_id, pln.pl_id)
1453 /* Make sure that plan being linked to covers all the options that may
1454 or may not have been stated by the user. */
1455
1456 AND (
1457 EXISTS
1458 (SELECT NULL
1459 FROM ben_oipl_f cop
1460 WHERE cop.opt_id = p_opt_id
1461 AND cop.pl_id = pln.pl_id
1462 AND cop.oipl_stat_cd = 'A'
1463 AND cop.business_group_id = pln.business_group_id
1464 AND p_effective_date BETWEEN cop.effective_start_date
1465 AND cop.effective_end_date)
1466 OR p_opt_id IS NULL)
1467 /* Make sure that plan being linked to is in the correct benefit group */
1468 AND (
1469 EXISTS
1470 (SELECT NULL
1471 FROM ben_rptg_grp bnr, ben_popl_rptg_grp_f rgr
1472 WHERE bnr.rptg_grp_id = nvl(p_rptg_grp_id, bnr.rptg_grp_id) --irec
1473 AND nvl(bnr.business_group_id,pln.business_group_id)
1474 = pln.business_group_id
1475 AND rgr.rptg_grp_id = bnr.rptg_grp_id
1476 AND p_effective_date BETWEEN rgr.effective_start_date
1477 AND rgr.effective_end_date
1478 AND rgr.business_group_id =
1479 nvl(bnr.business_group_id,rgr.business_group_id)
1480 AND rgr.pl_id = pln.pl_id
1481 AND nvl(bnr.rptg_prps_cd, 'X') = decode (p_mode, 'I', 'IREC',nvl(bnr.rptg_prps_cd, 'X')) -- irec
1482 )
1483 OR
1484 (p_rptg_grp_id IS NULL
1485 and p_mode <>'I' -- iRec
1486 )
1487 )
1488 /* Make sure that plan being linked to is of the variable rate profile
1489 that has been specified by the user. */
1490 AND (
1491 EXISTS
1492 (SELECT NULL
1493 FROM ben_acty_base_rt_f abr
1494 ,ben_acty_vrbl_rt_f avr
1495 ,ben_vrbl_rt_prfl_f vpf
1496 WHERE abr.pl_id = pln.pl_id
1497 AND abr.business_group_id = pln.business_group_id
1498 AND p_effective_date BETWEEN abr.effective_start_date
1499 AND abr.effective_end_date
1500 AND avr.acty_base_rt_id = abr.acty_base_rt_id
1501 AND avr.business_group_id = abr.business_group_id
1502 AND p_effective_date BETWEEN avr.effective_start_date
1503 AND avr.effective_end_date
1504 AND vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
1505 AND vpf.business_group_id = avr.business_group_id
1506 AND vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
1507 AND p_effective_date BETWEEN vpf.effective_start_date
1508 AND vpf.effective_end_date)
1509 OR p_vrbl_rt_prfl_id IS NULL)
1510 AND (
1511 EXISTS
1512 (SELECT NULL
1513 FROM ben_prtn_elig_f epa2
1514 ,ben_prtn_elig_prfl_f cep
1515 ,ben_eligy_prfl_f elp
1516 WHERE epa2.pl_id = pln.pl_id
1517 AND epa2.business_group_id = pln.business_group_id
1518 AND p_effective_date BETWEEN epa2.effective_start_date
1522 AND p_effective_date BETWEEN cep.effective_start_date
1519 AND epa2.effective_end_date
1520 AND cep.prtn_elig_id = epa2.prtn_elig_id
1521 AND cep.business_group_id = epa2.business_group_id
1523 AND cep.effective_end_date
1524 AND elp.eligy_prfl_id = cep.eligy_prfl_id
1525 AND elp.business_group_id = cep.business_group_id
1526 AND elp.eligy_prfl_id = p_eligy_prfl_id
1527 AND p_effective_date BETWEEN elp.effective_start_date
1528 AND elp.effective_end_date)
1529 OR p_eligy_prfl_id IS NULL)
1530 AND (
1531 EXISTS
1532 (SELECT NULL
1533 FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
1534 WHERE pet.pl_id = pln.pl_id
1535 AND p_effective_date BETWEEN pet.effective_start_date
1536 AND pet.effective_end_date
1537 AND pet.popl_enrt_typ_cycl_id =
1538 enp.popl_enrt_typ_cycl_id
1539 AND enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
1540
1541 AND enp.business_group_id = pet.business_group_id)
1542 OR p_asnd_lf_evt_dt IS NULL)
1543 AND (
1544 EXISTS
1545 (SELECT NULL
1546 FROM ben_popl_org_f cpo,
1547 ben_popl_org_role_f cpr
1548 WHERE cpo.pl_id = pln.pl_id
1549 AND p_effective_date BETWEEN cpo.effective_start_date
1550 AND cpo.effective_end_date
1551 AND cpo.popl_org_id = cpr.popl_org_id
1552 AND p_effective_date BETWEEN cpr.effective_start_date
1553 AND cpr.effective_end_date
1554 AND cpo.business_group_id = cpr.business_group_id
1555 AND cpr.org_role_typ_cd = 'POPLOWNR'
1556 AND cpo.organization_id = l_per_org_id)
1557 OR p_lmt_prpnip_by_org_flag = 'N'
1558 OR l_per_org_id IS NULL)
1559 ORDER BY nvl(pln.ordr_num,999999999999999),pln.name;
1560 --
1561 CURSOR c_pln2
1562 IS
1563 select pln.pl_id,
1564 pln.pl_typ_id,
1565 ptp.opt_typ_cd,
1566 pln.drvbl_fctr_prtn_elig_flag,
1567 pln.drvbl_fctr_apls_rts_flag,
1568 pln.trk_inelig_per_flag
1569 FROM ben_pl_f pln,
1570 ben_pl_typ_f ptp,
1571 -- ben_popl_yr_perd cpy,
1572 -- ben_yr_perd yrp,
1573 ben_plip_f plp,
1574 ben_ptip_f ctp
1575 WHERE pln.business_group_id = p_business_group_id
1576 AND pln.pl_id = plp.pl_id
1577 AND p_effective_date BETWEEN pln.effective_start_date
1578 AND pln.effective_end_date
1579 and pln.pl_typ_id = ptp.pl_typ_id
1580 and p_effective_date
1581 between ptp.effective_start_date and ptp.effective_end_date
1582 AND plp.pgm_id = l_pgm_id
1583 AND plp.business_group_id = pln.business_group_id
1584 AND plp.plip_stat_cd = 'A'
1585 AND pln.pl_stat_cd = 'A'
1586 AND plp.alws_unrstrctd_enrt_flag =
1587 DECODE(p_mode, 'U', 'Y', plp.alws_unrstrctd_enrt_flag)
1588 AND p_effective_date BETWEEN plp.effective_start_date
1589 AND plp.effective_end_date
1590
1591 AND (p_mode = 'G' or
1592 exists (select null
1593 from ben_yr_perd yrp,
1594 ben_popl_yr_perd cpy
1595 where cpy.pl_id = pln.pl_id
1596 AND cpy.yr_perd_id = yrp.yr_perd_id
1597 AND cpy.business_group_id = pln.business_group_id
1598 AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
1599
1600 AND ctp.pl_typ_id = pln.pl_typ_id
1601 AND ctp.pgm_id = l_pgm_id
1602 AND ctp.business_group_id = pln.business_group_id
1603 AND ctp.ptip_stat_cd = 'A'
1604 AND p_effective_date BETWEEN ctp.effective_start_date
1605 AND ctp.effective_end_date
1606 ORDER BY pln.name;
1607 --
1608 CURSOR c_oipl2
1609 IS
1610 select cop.oipl_id,
1611 cop.opt_id,
1612 cop.drvbl_fctr_prtn_elig_flag,
1613 cop.drvbl_fctr_apls_rts_flag,
1614 cop.trk_inelig_per_flag
1615 FROM ben_oipl_f cop
1616 ,ben_opt_f opt
1617 -- ,ben_popl_yr_perd cpy
1618 -- ,ben_yr_perd yrp
1619 ,ben_pl_f pln
1620 WHERE cop.business_group_id = p_business_group_id
1621 AND p_effective_date BETWEEN cop.effective_start_date
1622 AND cop.effective_end_date
1623 AND cop.pl_id = pln.pl_id
1624 AND cop.oipl_stat_cd = 'A'
1625 AND pln.pl_id = l_pl_id
1626 AND pln.business_group_id = cop.business_group_id
1627 AND pln.pl_stat_cd = 'A'
1628 AND p_effective_date BETWEEN pln.effective_start_date
1629 AND pln.effective_end_date
1630 AND cop.opt_id = opt.opt_id
1634
1631 AND opt.business_group_id = cop.business_group_id
1632 AND p_effective_date BETWEEN opt.effective_start_date
1633 AND opt.effective_end_date
1635 AND (p_mode in ('G','D') or
1636 exists (select null
1637 from ben_yr_perd yrp,
1638 ben_popl_yr_perd cpy
1639 where cpy.pl_id = cop.pl_id
1640 AND cpy.yr_perd_id = yrp.yr_perd_id
1641 AND cpy.business_group_id = cop.business_group_id
1642 AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
1643 ORDER BY cop.ordr_num;
1644 --
1645 CURSOR c_plip IS
1646 SELECT cpp.*
1647 FROM ben_plip_f cpp
1648 WHERE cpp.business_group_id = p_business_group_id
1649 AND cpp.pl_id = l_pl_id
1650 AND cpp.pgm_id = l_pgm_id
1651 AND cpp.plip_stat_cd = 'A'
1652 AND p_effective_date BETWEEN cpp.effective_start_date
1653 AND cpp.effective_end_date;
1654 --
1655 CURSOR c_ptip IS
1656 SELECT ctp.*
1657 FROM ben_ptip_f ctp
1658 WHERE ctp.business_group_id = p_business_group_id
1659 AND ctp.pgm_id = l_pgm_id
1660 AND ctp.pl_typ_id = l_pln.pl_typ_id
1661 AND ctp.ptip_stat_cd = 'A'
1662 AND p_effective_date BETWEEN ctp.effective_start_date
1663 AND ctp.effective_end_date;
1664 --
1665 CURSOR c_oiplip IS
1666 SELECT opp.*
1667 FROM ben_oiplip_f opp
1668 WHERE opp.business_group_id = p_business_group_id
1669 AND opp.plip_id = l_plip.plip_id
1670 AND opp.oipl_id = l_cop.oipl_id
1671 AND p_effective_date BETWEEN opp.effective_start_date
1672 AND opp.effective_end_date;
1673 --
1674 CURSOR c_multisesscache(
1675 c_effective_date IN DATE
1676 ,c_business_group_id IN NUMBER) IS
1677 SELECT comp_obj_cache_id
1678 ,timestamp
1679 ,mode_cd
1680 ,pgm_id
1681 ,pl_id
1682 ,no_programs
1683 ,no_plans
1684 ,pl_typ_id
1685 FROM ben_comp_obj_cache
1686 WHERE business_group_id = c_business_group_id
1687 AND effective_date = c_effective_date;
1688 --
1689 CURSOR c_chgpgm(
1690 c_timestamp IN DATE) IS
1691 SELECT NULL
1692 FROM ben_pgm_f
1693 WHERE c_timestamp < last_update_date;
1694 --
1695 l_tabname_set v2_set;
1696 --
1697 l_flag_bit_val BINARY_INTEGER := 0;
1698 l_oiplip_flag_bit_val BINARY_INTEGER := 0;
1699 --
1700 l_rebuild_list BOOLEAN;
1701 l_bypass_cache BOOLEAN;
1702 --
1703 l_comp_obj_cache_id NUMBER;
1704 l_comp_obj_cache_row_id NUMBER;
1705 l_rule_pass_pgm_id NUMBER;
1706 --
1707 l_timestamp DATE;
1708 l_v2dummy NUMBER(1);
1709 --
1710 l_query_str LONG;
1711 --
1712 l_per_rec per_all_people_f%rowtype;
1713 l_ass_rec per_all_assignments_f%rowtype;
1714 l_date_changed boolean := FALSE;
1715 l_org_changed boolean := FALSE;
1716 --
1717 -- Added for # 3330283
1718 cursor c_unres_cache_only IS
1719 select distinct alws_unrstrctd_enrt_flag
1720 from ben_pgm_f
1721 where pgm_id in ( SELECT distinct bcocr.pgm_id
1722 FROM ben_comp_obj_cache_row bcocr
1723 WHERE bcocr.comp_obj_cache_id = l_comp_obj_cache_id )
1724 order by 1;
1725 --
1726 l_unres_cache_only varchar2(1) ;
1727 -- # 3330283
1728 cursor c_unres_cache_plnip_only IS
1729 select distinct alws_unrstrctd_enrt_flag
1730 from ben_pl_f
1731 where pl_id in ( SELECT distinct bcocr.pl_id
1732 FROM ben_comp_obj_cache_row bcocr
1733 WHERE bcocr.comp_obj_cache_id = l_comp_obj_cache_id
1734 and bcocr.pl_nip = 'Y' )
1735 order by 1;
1736 --
1737 l_unres_cache_plnip_only varchar2(1) ;
1738 l_mode_cd varchar2(30);
1739 l_pgm_id2 number;
1740 l_pl_id2 number;
1741 l_no_programs varchar2(30);
1742 l_no_plans varchar2(30);
1743 l_pl_typ_id number;
1744
1745 --
1746 BEGIN
1747 --
1748 hr_utility.set_location('Entering ' || l_package, 10);
1749 hr_utility.set_location('date ' || p_effective_date, 10);
1750 --
1751 -- PB : Helathnet change :
1752 -- Get the person organization id, if it changes then
1753 -- comp object list needs to be rebuilt.
1754 --
1755 l_per_org_id := null;
1756 --
1757 IF p_lmt_prpnip_by_org_flag = 'Y' then
1758 --
1759 -- Get the organization id.
1760 --
1761 ben_person_object.get_object(p_person_id => p_person_id,
1762 p_rec => l_ass_rec);
1763 --
1764 hr_utility.set_location('l_ass.assignment_id ' || l_ass_rec.assignment_id, 111);
1765 l_per_org_id := l_ass_rec.organization_id;
1766 --
1770 -- to refresh the comp object cache.
1767 -- Check if the organization id for the comp object
1768 -- cache has changed since the previous call to build
1769 -- comp object. If there is no change then we do not need
1771 --
1772 IF g_prev_per_org_id IS NULL THEN
1773 --
1774 g_prev_per_org_id := l_ass_rec.organization_id;
1775 l_org_changed := TRUE;
1776 --
1777 ELSIF g_prev_per_org_id = l_ass_rec.organization_id
1778 AND ben_manage_life_events.g_cache_proc_object.COUNT > 0 THEN
1779 --
1780 l_org_changed := FALSE;
1781 --
1782 ELSE
1783 --
1784 g_prev_per_org_id := l_ass_rec.organization_id;
1785 l_org_changed := TRUE;
1786 --
1787 END IF;
1788 --
1789 END IF;
1790 hr_utility.set_location('g_prev_per_org_id ' || l_ass_rec.assignment_id, 111);
1791 --
1792 -- Check if the effective date for the comp object
1793 -- cache has changed since the previous call to build
1794 -- comp object. If there is no change then we do not need
1795 -- to refresh the comp object cache.
1796 --
1797 IF g_prev_lf_evt_ocrd_dt IS NULL THEN
1798 --
1799 g_prev_lf_evt_ocrd_dt := p_effective_date;
1800 l_date_changed := TRUE;
1801 --
1802 ELSIF g_prev_lf_evt_ocrd_dt = p_effective_date
1803 AND ben_manage_life_events.g_cache_proc_object.COUNT > 0 THEN
1804 --
1805 if p_lmt_prpnip_by_org_flag = 'N' then
1806 --
1807 RETURN;
1808 --
1809 else
1810 --
1811 if not l_org_changed then
1812 --
1813 return;
1814 --
1815 end if;
1816 --
1817 end if;
1818 --
1819 ELSE
1820 --
1821 g_prev_lf_evt_ocrd_dt := p_effective_date;
1822 l_date_changed := TRUE;
1823 --
1824 END IF;
1825 --
1826 -- Copy benefit action id to global in benutils package
1827 --
1828 benutils.g_benefit_action_id := p_benefit_action_id;
1829 benutils.g_thread_id := p_thread_id;
1830 --
1831 -- Clear cache structures
1832 --
1833 -- PB : Healthnet change.
1834 -- if only org_id changed then need not remove the caching.
1835 --
1836 if l_date_changed or
1837 p_lmt_prpnip_by_org_flag = 'N'
1838 then
1839 --
1840 hr_utility.set_location('Clear caches ' || l_package, 10);
1841 --
1842 ben_comp_object_list1.refresh_eff_date_caches;
1843 --
1844 hr_utility.set_location('Done Clear caches ' || l_package, 10);
1845 --
1846 end if;
1847 --
1848 -- Check if the comp object list exists in the multi session cache
1849 --
1850 -- Clear the comp object list
1851 --
1852 ben_manage_life_events.g_cache_proc_object.delete;
1853 --
1854 l_rebuild_list := TRUE;
1855 l_bypass_cache := TRUE;
1856 --
1857 -- When parameters are set or collective agreement mode
1858 -- we must do a force build
1859 --
1860 IF p_pgm_id IS NOT NULL
1861 OR p_pl_id IS NOT NULL
1862 OR p_opt_id IS NOT NULL
1863 OR p_rptg_grp_id IS NOT NULL
1864 OR p_vrbl_rt_prfl_id IS NOT NULL
1865 OR p_eligy_prfl_id IS NOT NULL
1866 OR p_asnd_lf_evt_dt IS NOT NULL
1867 OR p_lmt_prpnip_by_org_flag = 'Y'
1868 -- CWB Changes .
1869 -- ABSENCES rebuild cache as multiple life events are processed.
1870 OR p_mode in ( 'A', 'W', 'M', 'G')
1871
1872 THEN
1873 --
1874 l_bypass_cache := TRUE;
1875 --
1876 ELSE
1877 --
1878 l_bypass_cache := FALSE;
1879 --
1880 -- Check if a multi sessiob comp object list exists in the cache
1881 -- tables
1882 --
1883 OPEN c_multisesscache(c_effective_date=> p_effective_date
1884 ,c_business_group_id => p_business_group_id);
1885 FETCH c_multisesscache INTO l_comp_obj_cache_id
1886 ,l_timestamp
1887 ,l_mode_cd
1888 ,l_pgm_id2
1889 ,l_pl_id2
1890 ,l_no_programs
1891 ,l_no_plans
1892 ,l_pl_typ_id;
1893 IF c_multisesscache%FOUND THEN
1894 --
1895 --check the parameters first
1896 if l_mode_cd = p_mode and nvl(l_pgm_id2,-1) = nvl(p_pgm_id, -1) and
1897 nvl(l_pl_id2,-1) = nvl(p_pl_id,-1) and l_no_programs = p_no_programs
1898 and l_no_plans = p_no_plans and nvl(l_pl_typ_id,-1) = nvl(p_pl_typ_id,-1)
1899 then
1900 -- Check if the multi session cache information is in sync
1901 -- with the database
1902 --
1903 -- - Check comp object level changes
1904 --
1905 l_tabname_set(0) := 'ben_pgm_f';
1906 l_tabname_set(1) := 'ben_ptip_f';
1907 l_tabname_set(2) := 'ben_plip_f';
1908 l_tabname_set(3) := 'ben_pl_f';
1909 l_tabname_set(4) := 'ben_opt_f';
1910 l_tabname_set(5) := 'ben_oipl_f';
1911 l_tabname_set(6) := 'ben_oiplip_f';
1912 --
1916 --
1913 l_tabname_set(7) := 'ben_prtn_elig_f';
1914 l_tabname_set(8) := 'ben_prtn_elig_prfl_f';
1915 l_tabname_set(9) := 'ben_eligy_prfl_f';
1917 l_tabname_set(10) := 'ben_popl_yr_perd';
1918 l_tabname_set(11) := 'ben_yr_perd';
1919 l_tabname_set(12) := 'ben_rptg_grp';
1920 l_tabname_set(13) := 'ben_popl_rptg_grp_f';
1921 --
1922 l_tabname_set(10) := 'ben_popl_yr_perd';
1923 l_tabname_set(11) := 'ben_yr_perd';
1924 l_tabname_set(12) := 'ben_rptg_grp';
1925 l_tabname_set(13) := 'ben_popl_rptg_grp';
1926 --
1927 l_tabname_set(14) := 'ben_enrt_perd_for_pl_f';
1928 l_tabname_set(15) := 'ben_enrt_perd';
1929 l_tabname_set(16) := 'ben_popl_enrt_typ_cycl_f'; --
1930 --
1931 l_tabname_set(17) := 'ben_vrbl_rt_prfl_f';
1932 l_tabname_set(18) := 'ben_acty_vrbl_rt_f';
1933 l_tabname_set(19) := 'ben_acty_base_rt_f';
1934 l_tabname_set(20) := 'ben_actl_prem_vrbl_rt_f';
1935 l_tabname_set(21) := 'ben_actl_prem_f';
1936 l_tabname_set(22) := 'ben_bnft_vrbl_rt_f';
1937 l_tabname_set(23) := 'ben_cvg_amt_calc_mthd_f';
1938 --
1939 FOR tabele_num IN l_tabname_set.FIRST .. l_tabname_set.LAST LOOP
1940 --
1941 l_query_str :=
1942 ' select 1 ' || ' from sys.dual' || ' where exists(select null' ||
1943 ' from ' ||
1944 l_tabname_set(tabele_num) ||
1945 ' where :timestamp < last_update_date)';
1946 --
1947 --
1948 -- Strage but if a table has no rows PLSQL lets %found be successful
1949 -- for dynamic SQL. My workaround is to use a number assignment.
1950 --
1951 l_v2dummy := 0;
1952 OPEN c_chgdata FOR l_query_str USING l_timestamp;
1953 FETCH c_chgdata INTO l_v2dummy;
1954 --
1955 -- Following on from MH above
1956 -- Actually put the test in for it not returning rows
1957 -- by using the var setup
1958 -- line was IF c_chgdata%FOUND THEN
1959 -- tm 01-Mar-2001
1960 --
1961 IF (c_chgdata%FOUND AND l_v2dummy >0 ) THEN
1962 --
1963 -- Clear all cache information for all effective dates
1964 --
1965 flush_multi_session_cache
1966 (p_business_group_id => p_business_group_id
1967 ,p_effective_date => p_effective_date
1968 );
1969 --
1970 l_rebuild_list := TRUE;
1971 --
1972 CLOSE c_chgdata;
1973 EXIT;
1974 --
1975 ELSE
1976 --
1977 l_rebuild_list := FALSE;
1978 --
1979 END IF;
1980 CLOSE c_chgdata;
1981 --
1982 END LOOP;
1983 --
1984 else
1985 --
1986 flush_multi_session_cache
1987 (p_business_group_id => p_business_group_id
1988 ,p_effective_date => p_effective_date
1989 );
1990 --
1991 l_rebuild_list := TRUE;
1992 --
1993 end if; -- parameter check
1994 --
1995 END IF;
1996 CLOSE c_multisesscache;
1997 --
1998 END IF;
1999 --
2000 -- Check if the comp object list should be re-built
2001 --
2002 --
2003 -- Start # bug 3330283
2004 --
2005 /*
2006 IF l_rebuild_list = FALSE and l_bypass_cache = FALSE THEN
2007 --
2008 If p_mode = 'L' then
2009 -- check whether multi cache exists
2010 --
2011 OPEN c_multisesscache(c_effective_date=> p_effective_date
2012 ,c_business_group_id => p_business_group_id);
2013 FETCH c_multisesscache INTO l_comp_obj_cache_id, l_timestamp;
2014 --
2015 hr_utility.set_location('fetched multi cache ' , 111);
2016 IF c_multisesscache%FOUND THEN
2017 -- cache exists
2018 -- check if cache has only unrestricted programs
2019 --
2020 hr_utility.set_location('found multi cache ' , 111);
2021 open c_unres_cache_only ;
2022 fetch c_unres_cache_only into l_unres_cache_only ;
2023 if c_unres_cache_only%FOUND then
2024 --
2025 if l_unres_cache_only = 'N' then
2026 -- then non- unrestricted pgms also exist so continue
2027 null ;
2028 else
2029 -- only unrst pgms exist
2030 -- reset l_rebuild_list to TRUE
2031 hr_utility.set_location(' reset rebuild list pgms ' , 111);
2032 l_rebuild_list := TRUE;
2033 end if;
2034 --
2035 else
2036 -- as multisesscache exists and is not for pgm
2037 -- chk if it is for plnip
2038 -- 3889987
2039 open c_unres_cache_plnip_only ;
2040 fetch c_unres_cache_plnip_only into l_unres_cache_plnip_only ;
2041 if c_unres_cache_plnip_only%FOUND then
2042 --
2043 if l_unres_cache_plnip_only = 'N' then
2044 -- then non- unrestricted plns also exist so continue
2048 -- reset l_rebuild_list to TRUE
2045 null ;
2046 else
2047 -- only unrst plns exist
2049 hr_utility.set_location(' reset rebuild list plans ' , 112);
2050 l_rebuild_list := TRUE;
2051 end if;
2052 --
2053 end if ;
2054 close c_unres_cache_plnip_only ;
2055 end if;
2056 close c_unres_cache_only ;
2057 ELSE
2058 -- cache doesnt exist ?? continue
2059 null;
2060
2061 END IF;
2062 --
2063 CLOSE c_multisesscache ;
2064 --
2065 end if;
2066 --
2067 END IF;
2068 --
2069 -- End # bug 3330283
2070 */
2071 --
2072 hr_utility.set_location('rebuild cache ' || l_package, 111);
2073 IF l_rebuild_list
2074 OR l_bypass_cache THEN
2075 --
2076 -- Flush all existing multi session cache information
2077 -- for the effective date
2078 --
2079 if p_lmt_prpnip_by_org_flag = 'N'
2080 and l_rebuild_list
2081 then
2082 --
2083 flush_multi_session_cache(p_business_group_id => p_business_group_id
2084
2085 ,p_effective_date => p_effective_date
2086
2087 );
2088 --
2089 end if;
2090 --
2091 IF p_no_programs = 'N' and p_mode not in ('I','D') THEN -- irec -- ICM
2092 --
2093 hr_utility.set_location(l_package || ' Opening c_pgm loop ', 11);
2094 OPEN c_pgm;
2095 hr_utility.set_location(l_package || ' Opened c_pgm loop ', 11);
2096 --
2097 LOOP
2098 --
2099 hr_utility.set_location(l_package || ' Start c_pgm loop ', 12);
2100 FETCH c_pgm INTO l_pgm;
2101 hr_utility.set_location(l_package || ' Fetch c_pgm loop ', 14);
2102 EXIT WHEN c_pgm%NOTFOUND;
2103 --
2104 l_pgm_id := l_pgm.pgm_id;
2105 --
2106 hr_utility.set_location(l_package || ' c_pgm LC ', 16);
2107 --
2108 -- Only set the flag bit if we have rates or profiles attached
2109 --
2110 IF l_pgm.drvbl_fctr_prtn_elig_flag = 'Y'
2111 OR l_pgm.drvbl_fctr_apls_rts_flag = 'Y' THEN
2112 --
2113 l_flag_bit_val :=
2114 set_flag_bit_val(p_business_group_id=> p_business_group_id
2115 ,p_effective_date => p_effective_date
2116 ,p_drvbl_fctr_prtn_elig_flag => l_pgm.drvbl_fctr_prtn_elig_flag
2117 ,p_drvbl_fctr_apls_rts_flag => l_pgm.drvbl_fctr_apls_rts_flag
2118 ,p_pgm_id => l_pgm.pgm_id
2119 ,p_pl_id => NULL
2120 ,p_oipl_id => NULL
2121 ,p_oiplip_id => NULL
2122 ,p_plip_id => NULL
2123 ,p_ptip_id => NULL);
2124 --
2125 ELSE
2126 --
2127 l_flag_bit_val := 0;
2128 --
2129 END IF;
2130 --
2131 load_cache(p_pgm_id => l_pgm_id
2132 ,p_par_pgm_id => l_pgm_id
2133 ,p_flag_bit_val => l_flag_bit_val
2134 ,p_oiplip_flag_bit_val => 0
2135 ,p_trk_inelig_per_flag => l_pgm.trk_inelig_per_flag);
2136 --
2137 hr_utility.set_location(l_package || ' Start c_pln loop ', 20);
2138 ben_pln_cache.bgpcpp_getdets(p_business_group_id=> p_business_group_id
2139 ,p_effective_date => p_effective_date
2140 ,p_mode => p_mode
2141 ,p_pgm_id => l_pgm_id
2142 ,p_pl_id => p_pl_id
2143 ,p_opt_id => p_opt_id
2144 ,p_rptg_grp_id => p_rptg_grp_id
2145 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2146 ,p_eligy_prfl_id => p_eligy_prfl_id
2147 -- 5422 : PB
2148 ,p_asnd_lf_evt_dt => p_asnd_lf_evt_dt
2149 -- ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
2150 --
2151 ,p_inst_set => l_plninst_set
2152 );
2153 hr_utility.set_location(l_package || ' Fetch c_pln loop ', 22);
2154 --
2155 IF l_plninst_set.COUNT > 0 THEN
2156 --
2157 FOR plnelenum IN l_plninst_set.FIRST .. l_plninst_set.LAST LOOP
2158 --
2159 l_pln := l_plninst_set(plnelenum);
2160 l_pl_id := l_plninst_set(plnelenum).pl_id;
2161 l_ptp_opt_typ_cd := l_plninst_set(plnelenum).ptp_opt_typ_cd;
2162 --
2163 hr_utility.set_location(l_package || ' Dn PLN OSR ', 16);
2164 --
2165 -- In collective agreement mode only process CAGR opt types
2166 --
2167 if l_ptp_opt_typ_cd <> 'CAGR'
2168 and p_mode = 'A'
2169 then
2170 --
2171 null;
2172 --
2173 else
2174 --
2175 -- We have to work out the PLIP and PTIP ids for the
2176 -- plan in program we are dealing with
2177 -- There will ALWAYS be a PLIP_ID but we cannot guarantee
2181 OPEN c_ptip;
2178 -- a PTIP id.
2179 --
2180 hr_utility.set_location(l_package || ' open c_ptip ', 16);
2182 --
2183 FETCH c_ptip INTO l_ptip;
2184 --
2185 CLOSE c_ptip;
2186 --
2187 -- Only set the flag bit if we have rates or profiles attached
2188 --
2189 IF l_ptip.drvbl_fctr_prtn_elig_flag = 'Y'
2190 OR l_ptip.drvbl_fctr_apls_rts_flag = 'Y' THEN
2191 --
2192 l_flag_bit_val :=
2193 set_flag_bit_val(p_business_group_id=> p_business_group_id
2194 ,p_effective_date => p_effective_date
2195 ,p_drvbl_fctr_prtn_elig_flag => l_ptip.drvbl_fctr_prtn_elig_flag
2196 ,p_drvbl_fctr_apls_rts_flag => l_ptip.drvbl_fctr_apls_rts_flag
2197 ,p_pgm_id => NULL
2198 ,p_pl_id => NULL
2199 ,p_oipl_id => NULL
2200 ,p_oiplip_id => NULL
2201 ,p_plip_id => NULL
2202 ,p_ptip_id => l_ptip.ptip_id);
2203 --
2204 ELSE
2205 --
2206 l_flag_bit_val := 0;
2207 --
2208 END IF;
2209 --
2210 load_cache(p_ptip_id => l_ptip.ptip_id
2211 ,p_par_pgm_id => l_pgm_id
2212 ,p_par_ptip_id => l_ptip.ptip_id
2213 ,p_flag_bit_val => l_flag_bit_val
2214 ,p_oiplip_flag_bit_val => 0
2215 ,p_trk_inelig_per_flag => l_ptip.trk_inelig_per_flag);
2216 --
2217 hr_utility.set_location(l_package || ' open c_plip ', 16);
2218 OPEN c_plip;
2219 --
2220 FETCH c_plip INTO l_plip;
2221 --
2222 CLOSE c_plip;
2223 --
2224 -- Only set the flag bit if we have rates or profiles attached
2225 --
2226 IF l_plip.drvbl_fctr_prtn_elig_flag = 'Y'
2227 OR l_plip.drvbl_fctr_apls_rts_flag = 'Y' THEN
2228 --
2229 l_flag_bit_val :=
2230 set_flag_bit_val(p_business_group_id=> p_business_group_id
2231 ,p_effective_date => p_effective_date
2232 ,p_drvbl_fctr_prtn_elig_flag => l_plip.drvbl_fctr_prtn_elig_flag
2233 ,p_drvbl_fctr_apls_rts_flag => l_plip.drvbl_fctr_apls_rts_flag
2234 ,p_pgm_id => NULL
2235 ,p_pl_id => NULL
2236 ,p_oipl_id => NULL
2237 ,p_oiplip_id => NULL
2238 ,p_plip_id => l_plip.plip_id
2239 ,p_ptip_id => NULL);
2240 --
2241 ELSE
2242 --
2243 l_flag_bit_val := 0;
2244 --
2245 END IF;
2246 --
2247 load_cache(p_plip_id => l_plip.plip_id
2248 ,p_par_pgm_id => l_pgm_id
2249 ,p_par_ptip_id => l_ptip.ptip_id
2250 ,p_par_plip_id => l_plip.plip_id
2251 --RCHASE
2252 ,p_par_pl_id => l_pl_id
2253 ,p_flag_bit_val => l_flag_bit_val
2254 ,p_oiplip_flag_bit_val => 0
2255 ,p_trk_inelig_per_flag => l_plip.trk_inelig_per_flag);
2256 --
2257 -- Only set the flag bit if we have rates or profiles attached
2258 --
2259 IF l_pln.drvbl_fctr_prtn_elig_flag = 'Y'
2260 OR l_pln.drvbl_fctr_apls_rts_flag = 'Y' THEN
2261 --
2262 l_flag_bit_val :=
2263 set_flag_bit_val(p_business_group_id=> p_business_group_id
2264 ,p_effective_date => p_effective_date
2265 ,p_drvbl_fctr_prtn_elig_flag => l_pln.drvbl_fctr_prtn_elig_flag
2266 ,p_drvbl_fctr_apls_rts_flag => l_pln.drvbl_fctr_apls_rts_flag
2267 ,p_pgm_id => NULL
2268 ,p_pl_id => l_pln.pl_id
2269 ,p_oipl_id => NULL
2270 ,p_oiplip_id => NULL
2271 ,p_plip_id => NULL
2272 ,p_ptip_id => NULL);
2273 --
2274 ELSE
2275 --
2276 l_flag_bit_val := 0;
2277 --
2278 END IF;
2279 --
2280 load_cache(p_pl_id => l_pl_id
2281 ,p_par_pgm_id => l_pgm_id
2282 ,p_par_ptip_id => l_ptip.ptip_id
2283 ,p_par_plip_id => l_plip.plip_id
2284 ,p_par_pl_id => l_pl_id
2285 ,p_flag_bit_val => l_flag_bit_val
2286 ,p_oiplip_flag_bit_val => 0
2287 ,p_trk_inelig_per_flag => l_pln.trk_inelig_per_flag);
2288 --
2292 ,p_opt_id => p_opt_id
2289 ben_cop_cache.bgpcop_getdets(p_effective_date=> p_effective_date
2290 ,p_business_group_id => p_business_group_id
2291 ,p_pl_id => l_pl_id
2293 ,p_eligy_prfl_id => p_eligy_prfl_id
2294 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2295 ,p_mode => p_mode
2296 --
2297 ,p_inst_set => l_copinst_set);
2298 --
2299 IF l_copinst_set.COUNT > 0 THEN
2300 --
2301 FOR copelenum IN l_copinst_set.FIRST .. l_copinst_set.LAST LOOP
2302 --
2303 l_cop := l_copinst_set(copelenum);
2304 l_oipl_id := l_copinst_set(copelenum).oipl_id;
2305 --
2306 hr_utility.set_location(l_package || ' c_oipl load_cache '
2307 ,15);
2308 --
2309 -- Only set the flag bit if we have rates or profiles attached
2310 --
2311 IF l_cop.drvbl_fctr_prtn_elig_flag = 'Y'
2312 OR l_cop.drvbl_fctr_apls_rts_flag = 'Y' THEN
2313 --
2314 l_flag_bit_val :=
2315 set_flag_bit_val(p_business_group_id=> p_business_group_id
2316 ,p_effective_date => p_effective_date
2317 ,p_drvbl_fctr_prtn_elig_flag => l_cop.drvbl_fctr_prtn_elig_flag
2318 ,p_drvbl_fctr_apls_rts_flag => l_cop.drvbl_fctr_apls_rts_flag
2319 ,p_pgm_id => NULL
2320 ,p_pl_id => NULL
2321 ,p_oipl_id => l_cop.oipl_id
2322 ,p_oiplip_id => NULL
2323 ,p_plip_id => NULL
2324 ,p_ptip_id => NULL);
2325 --
2326 ELSE
2327 --
2328 l_flag_bit_val := 0;
2329 --
2330 END IF;
2331 --
2332 -- Handle oiplip case
2333 --
2334 -- If oiplip does not exist then set flag to 0
2335 --
2336 l_oiplip_flag_bit_val := 0;
2337 --
2338 OPEN c_oiplip;
2339 --
2340 FETCH c_oiplip INTO l_oiplip;
2341 --
2342 IF c_oiplip%FOUND THEN
2343 --
2344 -- Try and derive bit value for oiplip record
2345 --
2346 l_oiplip_flag_bit_val :=
2347 set_flag_bit_val(p_business_group_id=> p_business_group_id
2348 ,p_effective_date => p_effective_date
2349 ,p_drvbl_fctr_prtn_elig_flag => 'N'
2350 ,p_drvbl_fctr_apls_rts_flag => 'N'
2351 ,p_pgm_id => NULL
2352 ,p_pl_id => NULL
2353 ,p_oipl_id => NULL
2354 ,p_oiplip_id => l_oiplip.oiplip_id
2355 ,p_plip_id => NULL
2356 ,p_ptip_id => NULL);
2357 --
2358 END IF;
2359 --
2360 CLOSE c_oiplip;
2361 --
2362 load_cache(p_oipl_id => l_oipl_id
2363 ,p_oiplip_id => l_oiplip.oiplip_id
2364 ,p_par_pgm_id => l_pgm_id
2365 ,p_par_ptip_id => l_ptip.ptip_id
2366 ,p_par_plip_id => l_plip.plip_id
2367 ,p_par_pl_id => l_pl_id
2368 ,p_par_opt_id => l_cop.opt_id
2369 ,p_flag_bit_val => l_flag_bit_val
2370 ,p_oiplip_flag_bit_val => l_oiplip_flag_bit_val
2371 ,p_trk_inelig_per_flag => l_cop.trk_inelig_per_flag);
2372 --
2373 hr_utility.set_location(l_package || ' End c_oipl loop ', 20);
2374 END LOOP;
2375 --
2376 END IF;
2377 --
2378 END IF;
2379 --
2380 hr_utility.set_location(l_package || ' End c_pln loop ', 20);
2381 END LOOP;
2382 --
2383 END IF;
2384 --
2385 hr_utility.set_location(l_package || ' End c_pgm loop ', 15);
2386 END LOOP;
2387 --
2388 CLOSE c_pgm;
2389 --
2390 END IF;
2391 hr_utility.set_location(l_package || ' Done c_pgm ', 20);
2392 --
2393 -- get the stragglers, the plans that aren't in a program
2394 -- added p_no_programs = N so that if only programs, plnip are not included
2395 IF (p_mode <> 'G') and
2396 (( p_no_plans = 'N' AND p_pgm_id IS NULL and p_no_programs = 'Y') or
2400 --
2397 ( p_no_plans = 'N' and p_no_programs = 'N' and p_pgm_id is null)
2398 )
2399 THEN
2401 OPEN c_pln_nip;
2402 --
2403 LOOP
2404 --
2405 hr_utility.set_location(l_package || ' Start c_pln_nip ', 30);
2406 FETCH c_pln_nip INTO l_pln;
2407 hr_utility.set_location(l_package || ' Fetch c_pln_nip = '||
2408 l_pln.pl_id, 32);
2409 EXIT WHEN c_pln_nip%NOTFOUND;
2410 l_pl_id := l_pln.pl_id;
2411 l_ptp_opt_typ_cd := l_pln.ptp_opt_typ_cd;
2412 --
2413 hr_utility.set_location(l_package || ' PLNNIP LC ', 16);
2414 --
2415 -- In collective agreement mode only process CAGR opt types
2416 --
2417 if l_ptp_opt_typ_cd <> 'CAGR'
2418 and p_mode = 'A'
2419 then
2420 --
2421 null;
2422 --
2423 else
2424 --
2425 -- Only set the flag bit if we have rates or profiles attached
2426 --
2427 IF l_pln.drvbl_fctr_prtn_elig_flag = 'Y'
2428 OR l_pln.drvbl_fctr_apls_rts_flag = 'Y' THEN
2429 --
2430 l_flag_bit_val :=
2431 set_flag_bit_val(p_business_group_id=> p_business_group_id
2432 ,p_effective_date => p_effective_date
2433 ,p_drvbl_fctr_prtn_elig_flag => l_pln.drvbl_fctr_prtn_elig_flag
2434 ,p_drvbl_fctr_apls_rts_flag => l_pln.drvbl_fctr_apls_rts_flag
2435 ,p_pgm_id => NULL
2436 ,p_pl_id => l_pln.pl_id
2437 ,p_oipl_id => NULL
2438 ,p_oiplip_id => NULL
2439 ,p_plip_id => NULL
2440 ,p_ptip_id => NULL);
2441 --
2442 ELSE
2443 --
2444 l_flag_bit_val := 0;
2445 --
2446 END IF;
2447 --
2448 load_cache(p_pl_id => l_pl_id
2449 ,p_pl_nip => 'Y'
2450 ,p_par_pl_id => l_pl_id
2451 ,p_flag_bit_val => l_flag_bit_val
2452 ,p_oiplip_flag_bit_val => 0
2453 ,p_trk_inelig_per_flag => l_pln.trk_inelig_per_flag);
2454 --
2455 hr_utility.set_location(l_package || ' fetch c_oipl 1', 20);
2456 ben_cop_cache.bgpcop_getdets(p_effective_date=> p_effective_date
2457 ,p_business_group_id => p_business_group_id
2458 ,p_pl_id => l_pl_id
2459 ,p_opt_id => p_opt_id
2460 ,p_eligy_prfl_id => p_eligy_prfl_id
2461 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
2462 ,p_mode => p_mode
2463 --
2464 ,p_inst_set => l_copinst_set);
2465 hr_utility.set_location(l_package || ' Dn fetch c_oipl 1', 20);
2466 hr_utility.set_location(' Number of oipls' || l_copinst_set.COUNT
2467 ,20);
2468 --
2469 IF l_copinst_set.COUNT > 0 THEN
2470 --
2471 FOR copelenum IN l_copinst_set.FIRST .. l_copinst_set.LAST LOOP
2472 --
2473 l_cop := l_copinst_set(copelenum);
2474 l_oipl_id := l_copinst_set(copelenum).oipl_id;
2475 --
2476 hr_utility.set_location(l_package || ' OIPL2 LC ', 16);
2477 --
2478 -- Only set the flag bit if we have rates or profiles attached
2479 --
2480 IF l_cop.drvbl_fctr_prtn_elig_flag = 'Y'
2481 OR l_cop.drvbl_fctr_apls_rts_flag = 'Y' THEN
2482 --
2483 l_flag_bit_val :=
2484 set_flag_bit_val(p_business_group_id=> p_business_group_id
2485 ,p_effective_date => p_effective_date
2486 ,p_drvbl_fctr_prtn_elig_flag => l_cop.drvbl_fctr_prtn_elig_flag
2487 ,p_drvbl_fctr_apls_rts_flag => l_cop.drvbl_fctr_apls_rts_flag
2488 ,p_pgm_id => NULL
2489 ,p_pl_id => NULL
2490 ,p_oipl_id => l_cop.oipl_id
2491 ,p_oiplip_id => NULL
2492 ,p_plip_id => NULL
2493 ,p_ptip_id => NULL);
2494 --
2495 ELSE
2496 --
2497 l_flag_bit_val := 0;
2498 --
2499 END IF;
2500 --
2501 load_cache(p_oipl_id => l_oipl_id
2502 ,p_pl_nip => 'N'
2503 ,p_par_pl_id => l_pl_id
2504 ,p_par_opt_id => l_cop.opt_id
2505 ,p_flag_bit_val => l_flag_bit_val
2506 ,p_oiplip_flag_bit_val => 0
2507 ,p_trk_inelig_per_flag => l_cop.trk_inelig_per_flag);
2508 --
2509 END LOOP;
2510 --
2511 hr_utility.set_location(l_package || ' close c_oipl: ', 34);
2512 END IF;
2513 --
2517 --
2514 END IF;
2515 --
2516 END LOOP;
2518 CLOSE c_pln_nip;
2519 --
2520 END IF;
2521 hr_utility.set_location(l_package || ' Done c_pln_nip ', 30);
2522 --
2523 -- Always check for cobra programs last if mode is unrestricted or
2524 -- life event
2525 --
2526 IF p_mode IN ('L', 'U') THEN
2527 OPEN c_pgm2;
2528 --
2529 LOOP
2530 --
2531 hr_utility.set_location(l_package || ' fetch c_pgm2 ', 30);
2532 FETCH c_pgm2 INTO l_pgm;
2533 hr_utility.set_location(l_package || ' fetched c_pgm2 ', 30);
2534 EXIT WHEN c_pgm2%NOTFOUND;
2535 --
2536 l_pgm_id := l_pgm.pgm_id;
2537 --
2538 --
2539 hr_utility.set_location(l_package || ' PGM2 LC ', 16);
2540 --
2541 -- Only set the flag bit if we have rates or profiles attached
2542 --
2543 IF l_pgm.drvbl_fctr_prtn_elig_flag = 'Y'
2544 OR l_pgm.drvbl_fctr_apls_rts_flag = 'Y' THEN
2545 --
2546 l_flag_bit_val :=
2547 set_flag_bit_val(p_business_group_id=> p_business_group_id
2548 ,p_effective_date => p_effective_date
2549 ,p_drvbl_fctr_prtn_elig_flag => l_pgm.drvbl_fctr_prtn_elig_flag
2550 ,p_drvbl_fctr_apls_rts_flag => l_pgm.drvbl_fctr_apls_rts_flag
2551 ,p_pgm_id => l_pgm.pgm_id
2552 ,p_pl_id => NULL
2553 ,p_oipl_id => NULL
2554 ,p_oiplip_id => NULL
2555 ,p_plip_id => NULL
2556 ,p_ptip_id => NULL);
2557 --
2558 ELSE
2559 --
2560 l_flag_bit_val := 0;
2561 --
2562 END IF;
2563 --
2564 load_cache(p_pgm_id => l_pgm_id
2565 ,p_par_pgm_id => l_pgm_id
2566 ,p_flag_bit_val => l_flag_bit_val
2567 ,p_oiplip_flag_bit_val => 0
2568 ,p_trk_inelig_per_flag => l_pgm.trk_inelig_per_flag);
2569 --
2570 IF p_no_plans = 'N' THEN
2571 --
2572 OPEN c_pln2;
2573 --
2574 LOOP
2575 --
2576 FETCH c_pln2 INTO l_pln;
2577 EXIT WHEN c_pln2%NOTFOUND;
2578 l_pl_id := l_pln.pl_id;
2579 l_ptp_opt_typ_cd := l_pln.ptp_opt_typ_cd;
2580 --
2581 if l_ptp_opt_typ_cd <> 'CAGR'
2582 and p_mode = 'A'
2583 then
2584 --
2585 null;
2586 --
2587 else
2588 --
2589 -- We have to work out the PLIP and PTIP ids for the
2590 -- plan in program we are dealing with
2591 -- There will ALWAYS be a PLIP_ID but we cannot guarantee
2592 -- a PTIP id.
2593 --
2594 OPEN c_ptip;
2595 --
2596 FETCH c_ptip INTO l_ptip;
2597 --
2598 CLOSE c_ptip;
2599 --
2600 hr_utility.set_location('PTIP being cached ' || l_ptip.ptip_id
2601 ,10);
2602 --
2603 -- Only set the flag bit if we have rates or profiles attached
2604 --
2605 IF l_ptip.drvbl_fctr_prtn_elig_flag = 'Y'
2606 OR l_ptip.drvbl_fctr_apls_rts_flag = 'Y' THEN
2607 --
2608 l_flag_bit_val :=
2609 set_flag_bit_val(p_business_group_id=> p_business_group_id
2610 ,p_effective_date => p_effective_date
2611 ,p_drvbl_fctr_prtn_elig_flag => l_ptip.drvbl_fctr_prtn_elig_flag
2612 ,p_drvbl_fctr_apls_rts_flag => l_ptip.drvbl_fctr_apls_rts_flag
2613 ,p_pgm_id => NULL
2614 ,p_pl_id => NULL
2615 ,p_oipl_id => NULL
2616 ,p_oiplip_id => NULL
2617 ,p_plip_id => NULL
2618 ,p_ptip_id => l_ptip.ptip_id);
2619 --
2620 ELSE
2621 --
2622 l_flag_bit_val := 0;
2623 --
2624 END IF;
2625 --
2626 load_cache(p_ptip_id => l_ptip.ptip_id
2627 ,p_par_pgm_id => l_pgm_id
2628 ,p_par_ptip_id => l_ptip.ptip_id
2629 ,p_flag_bit_val => l_flag_bit_val
2630 ,p_oiplip_flag_bit_val => 0
2631 ,p_trk_inelig_per_flag => l_ptip.trk_inelig_per_flag);
2632 --
2633 OPEN c_plip;
2634 --
2635 FETCH c_plip INTO l_plip;
2636 --
2637 CLOSE c_plip;
2638 --
2639 -- Only set the flag bit if we have rates or profiles attached
2640 --
2644 l_flag_bit_val :=
2641 IF l_plip.drvbl_fctr_prtn_elig_flag = 'Y'
2642 OR l_plip.drvbl_fctr_apls_rts_flag = 'Y' THEN
2643 --
2645 set_flag_bit_val(p_business_group_id=> p_business_group_id
2646 ,p_effective_date => p_effective_date
2647 ,p_drvbl_fctr_prtn_elig_flag => l_plip.drvbl_fctr_prtn_elig_flag
2648 ,p_drvbl_fctr_apls_rts_flag => l_plip.drvbl_fctr_apls_rts_flag
2649 ,p_pgm_id => NULL
2650 ,p_pl_id => NULL
2651 ,p_oipl_id => NULL
2652 ,p_oiplip_id => NULL
2653 ,p_plip_id => l_plip.plip_id
2654 ,p_ptip_id => NULL);
2655 --
2656 ELSE
2657 --
2658 l_flag_bit_val := 0;
2659 --
2660 END IF;
2661 --
2662 load_cache(p_plip_id => l_plip.plip_id
2663 ,p_par_pgm_id => l_pgm_id
2664 ,p_par_ptip_id => l_ptip.ptip_id
2665 ,p_par_plip_id => l_plip.plip_id
2666 --RCHASE
2667 ,p_par_pl_id => l_pl_id
2668 ,p_flag_bit_val => l_flag_bit_val
2669 ,p_oiplip_flag_bit_val => 0
2670 ,p_trk_inelig_per_flag => l_plip.trk_inelig_per_flag);
2671 --
2672 hr_utility.set_location(l_package || ' PLN2 LC ', 16);
2673 --
2674 -- Only set the flag bit if we have rates or profiles attached
2675 --
2676 IF l_pln.drvbl_fctr_prtn_elig_flag = 'Y'
2677 OR l_pln.drvbl_fctr_apls_rts_flag = 'Y' THEN
2678 --
2679 l_flag_bit_val :=
2680 set_flag_bit_val(p_business_group_id=> p_business_group_id
2681 ,p_effective_date => p_effective_date
2682 ,p_drvbl_fctr_prtn_elig_flag => l_pln.drvbl_fctr_prtn_elig_flag
2683 ,p_drvbl_fctr_apls_rts_flag => l_pln.drvbl_fctr_apls_rts_flag
2684 ,p_pgm_id => NULL
2685 ,p_pl_id => l_pln.pl_id
2686 ,p_oipl_id => NULL
2687 ,p_oiplip_id => NULL
2688 ,p_plip_id => NULL
2689 ,p_ptip_id => NULL);
2690 --
2691 ELSE
2692 --
2693 l_flag_bit_val := 0;
2694 --
2695 END IF;
2696 load_cache(p_pl_id => l_pl_id
2697 ,p_par_pgm_id => l_pgm_id
2698 ,p_par_ptip_id => l_ptip.ptip_id
2699 ,p_par_plip_id => l_plip.plip_id
2700 ,p_par_pl_id => l_pl_id
2701 ,p_flag_bit_val => l_flag_bit_val
2702 ,p_oiplip_flag_bit_val => 0
2703 ,p_trk_inelig_per_flag => l_pln.trk_inelig_per_flag);
2704 --
2705 OPEN c_oipl2;
2706 --
2707 LOOP
2708 --
2709 FETCH c_oipl2 INTO l_cop;
2710 EXIT WHEN c_oipl2%NOTFOUND;
2711 l_oipl_id := l_cop.oipl_id;
2712 --
2713 hr_utility.set_location(l_package || ' c_oipl2 LC ', 30);
2714 --
2715 -- Only set the flag bit if we have rates or profiles attached
2716 --
2717 IF l_cop.drvbl_fctr_prtn_elig_flag = 'Y'
2718 OR l_cop.drvbl_fctr_apls_rts_flag = 'Y' THEN
2719 --
2720 l_flag_bit_val :=
2721 set_flag_bit_val(p_business_group_id=> p_business_group_id
2722 ,p_effective_date => p_effective_date
2723 ,p_drvbl_fctr_prtn_elig_flag => l_cop.drvbl_fctr_prtn_elig_flag
2724 ,p_drvbl_fctr_apls_rts_flag => l_cop.drvbl_fctr_apls_rts_flag
2725 ,p_pgm_id => NULL
2726 ,p_pl_id => NULL
2727 ,p_oipl_id => l_cop.oipl_id
2728 ,p_oiplip_id => NULL
2729 ,p_plip_id => NULL
2730 ,p_ptip_id => NULL);
2731 --
2732 ELSE
2733 --
2734 l_flag_bit_val := 0;
2735 --
2736 END IF;
2737 --RCHASE Add for bug 1531030. Was not getting oiplip info.
2738 l_oiplip_flag_bit_val := 0;
2739 --
2740 OPEN c_oiplip;
2741 --hr_utility.set_location('l_plip.plip_id:'||to_char(l_plip.plip_id), 1999);
2742 --hr_utility.set_location('l_cop.oipl_id:'||to_char(l_cop.oipl_id), 1999);
2743 --hr_utility.set_location('l_oipl_id:'||to_char(l_oipl_id), 1999);
2744 --
2745 FETCH c_oiplip INTO l_oiplip;
2746 --
2750 -- Try and derive bit value for oiplip record
2747 IF c_oiplip%FOUND THEN
2748 --hr_utility.set_location('OIPLIP_ID:'||to_char(l_oiplip.oiplip_id), 1999);
2749 --
2751 --
2752 l_oiplip_flag_bit_val :=
2753 set_flag_bit_val(p_business_group_id=> p_business_group_id
2754 ,p_effective_date => p_effective_date
2755 ,p_drvbl_fctr_prtn_elig_flag => 'N'
2756 ,p_drvbl_fctr_apls_rts_flag => 'N'
2757 ,p_pgm_id => NULL
2758 ,p_pl_id => NULL
2759 ,p_oipl_id => NULL
2760 ,p_oiplip_id => l_oiplip.oiplip_id
2761 ,p_plip_id => NULL
2762 ,p_ptip_id => NULL);
2763 --hr_utility.set_location('l_oiplip_flag_bit_val:'||l_oiplip_flag_bit_val, 1999);
2764 --
2765 END IF;
2766 --
2767 CLOSE c_oiplip;
2768 --
2769 --hr_utility.set_location('Loading Cache:', 1999);
2770 load_cache(p_oipl_id => l_oipl_id
2771 ,p_oiplip_id => l_oiplip.oiplip_id
2772 ,p_par_pgm_id => l_pgm_id
2773 ,p_par_ptip_id => l_ptip.ptip_id
2774 ,p_par_plip_id => l_plip.plip_id
2775 ,p_par_pl_id => l_pl_id
2776 ,p_par_opt_id => l_cop.opt_id
2777 ,p_flag_bit_val => l_flag_bit_val
2778 ,p_oiplip_flag_bit_val => l_oiplip_flag_bit_val
2779 ,p_trk_inelig_per_flag => l_cop.trk_inelig_per_flag);
2780 --
2781 --hr_utility.set_location(l_package || ' End c_oipl loop ', 20);
2782 --
2783 --RCHASE End Add
2784 --RCHASE
2785 --load_cache(p_oipl_id => l_oipl_id
2786 -- ,p_par_pgm_id => l_pgm_id
2787 -- ,p_par_ptip_id => l_ptip.ptip_id
2788 -- ,p_par_plip_id => l_plip.plip_id
2789 -- ,p_par_pl_id => l_pl_id
2790 -- ,p_par_opt_id => l_opt_rec.opt_id
2791 -- ,p_flag_bit_val => l_flag_bit_val
2792 -- ,p_oiplip_flag_bit_val => 0
2793 -- ,p_trk_inelig_per_flag => l_cop.trk_inelig_per_flag);
2794 --RCHASE End
2795 --
2796 END LOOP;
2797 --
2798 CLOSE c_oipl2;
2799 --
2800 end if;
2801 --
2802 END LOOP;
2803 --
2804 CLOSE c_pln2;
2805 --
2806 END IF;
2807 --
2808 END LOOP;
2809 --
2810 CLOSE c_pgm2;
2811 --
2812 END IF;
2813 --
2814 -- Do not write cache for a bypass cache
2815 --
2816 IF NOT l_bypass_cache THEN
2817 --
2818 -- Write comp object list to the multi session cache
2819 --
2820 write_multi_session_cache(p_effective_date=> p_effective_date
2821 ,p_business_group_id => p_business_group_id
2822 ,p_mode => p_mode
2823 ,p_pgm_id => p_pgm_id
2824 ,p_pl_id => p_pl_id
2825 ,p_no_programs => p_no_programs
2826 ,p_no_plans => p_no_plans
2827 ,p_pl_typ_id => p_pl_typ_id
2828 ,p_comp_obj_cache_id => l_comp_obj_cache_id);
2829 --
2830 END IF;
2831 --
2832 END IF;
2833 --
2834 -- Check if to bypass the cache
2835 --
2836 IF NOT l_bypass_cache THEN
2837 --
2838 -- Populate the local comp object list from the database version
2839 --
2840 ben_comp_object_list1.populate_comp_object_list
2841 (p_comp_obj_cache_id => l_comp_obj_cache_id
2842 ,p_business_group_id => p_business_group_id
2843 ,p_comp_selection_rule_id => p_comp_selection_rule_id
2844 ,p_effective_date => p_effective_date
2845 );
2846 --
2847 end if;
2848 --
2849 hr_utility.set_location(l_package || ' Done c_pgm2 ', 40);
2850 IF NOT ben_manage_life_events.g_cache_proc_object.EXISTS(1) THEN
2851 --
2852 -- Different exceptions for different modes, if selection then this is
2853 -- a critical error, if anything else then its not a critical error
2854 --
2855 fnd_message.set_name('BEN', 'BEN_91664_BENMNGLE_NO_OBJECTS');
2856 --
2857 IF p_mode IN ('S', 'T') THEN
2858 --
2859 fnd_message.raise_error;
2860 --
2861 ELSE
2862 --
2863 RAISE ben_manage_life_events.g_record_error;
2864 --
2865 END IF;
2866 --
2867 END IF;
2868 --
2869 hr_utility.set_location(l_package || ' cache_working_data ', 60);
2870 cache_working_data(p_business_group_id=> p_business_group_id
2874 --
2871 ,p_effective_date => p_effective_date);
2872 --
2873 hr_utility.set_location('Leaving ' || l_package, 100);
2875 END build_comp_object_list;
2876 --
2877 /* GSP Rate Sync */
2878 procedure build_gsp_rate_sync_coobj_list
2879 (p_effective_date IN DATE
2880 ,p_business_group_id IN NUMBER DEFAULT NULL
2881 ,p_pgm_id IN NUMBER DEFAULT NULL
2882 ,p_pl_id IN NUMBER DEFAULT NULL
2883 ,p_opt_id IN NUMBER DEFAULT NULL
2884 ,p_plip_id IN NUMBER DEFAULT NULL
2885 ,p_ptip_id IN NUMBER DEFAULT NULL
2886 ,p_oipl_id IN NUMBER DEFAULT NULL
2887 ,p_oiplip_id IN NUMBER DEFAULT NULL
2888 ,p_person_id in number default null
2889 ) is
2890 --
2891 l_proc varchar2(80);
2892 l_drvbl_fctr_prtn_elig_flag varchar2(30);
2893 l_drvbl_fctr_apls_rts_flag varchar2(30);
2894 l_trk_inelig_per_flag varchar2(30);
2895 l_flag_bit_val BINARY_INTEGER := 0;
2896 l_oiplip_flag_bit_val BINARY_INTEGER := 0;
2897 --
2898 cursor c_pgm (cv_pgm_id number) is
2899 select pgm.drvbl_fctr_prtn_elig_flag, pgm.drvbl_fctr_apls_rts_flag, pgm.trk_inelig_per_flag
2900 from ben_pgm_f pgm
2901 where pgm.pgm_id = cv_pgm_id
2902 and pgm.pgm_stat_cd = 'A'
2903 and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
2904 --
2905 CURSOR c_ptip (cv_ptip_id number) IS
2906 SELECT ctp.drvbl_fctr_prtn_elig_flag, ctp.drvbl_fctr_apls_rts_flag, ctp.trk_inelig_per_flag
2907 FROM ben_ptip_f ctp
2908 WHERE ctp.ptip_id = cv_ptip_id
2909 AND ctp.ptip_stat_cd = 'A'
2910 AND p_effective_date BETWEEN ctp.effective_start_date AND ctp.effective_end_date;
2911 --
2912 CURSOR c_pln (cv_pl_id number) IS
2913 SELECT pln.drvbl_fctr_prtn_elig_flag, pln.drvbl_fctr_apls_rts_flag, pln.trk_inelig_per_flag
2914 FROM ben_pl_f pln
2915 WHERE pln.pl_id = cv_pl_id
2916 AND pln.pl_stat_cd = 'A'
2917 AND p_effective_date BETWEEN pln.effective_start_date AND pln.effective_end_date;
2918 --
2919 CURSOR c_plip (cv_plip_id number) IS
2920 SELECT cpp.drvbl_fctr_prtn_elig_flag, cpp.drvbl_fctr_apls_rts_flag, cpp.trk_inelig_per_flag
2921 FROM ben_plip_f cpp
2922 WHERE cpp.plip_id = cv_plip_id
2923 AND cpp.plip_stat_cd = 'A'
2924 AND p_effective_date BETWEEN cpp.effective_start_date AND cpp.effective_end_date;
2925 --
2926 CURSOR c_oipl (cv_oipl_id number) IS
2927 SELECT cop.drvbl_fctr_prtn_elig_flag, cop.drvbl_fctr_apls_rts_flag, cop.trk_inelig_per_flag
2928 FROM ben_oipl_f cop
2929 WHERE cop.oipl_id = cv_oipl_id
2930 AND cop.oipl_stat_cd = 'A'
2931 AND p_effective_date BETWEEN cop.effective_start_date AND cop.effective_end_date;
2932 --
2933 begin
2934 --
2935 l_proc := g_package || '.build_gsp_rate_sync_coobj_list';
2936 --
2937 hr_utility.set_location('Entering ' || l_proc, 10);
2938 --
2939 if p_plip_id is not null
2940 then
2941 --
2942 hr_utility.set_location('Populate g_cache_proc_object for PGM_ID = ' || p_pgm_id, 15);
2943 --
2944 -- Clear the comp object list
2945 --
2946 ben_manage_life_events.g_cache_proc_object.delete;
2947 --
2948 open c_pgm (p_pgm_id);
2949 --
2950 fetch c_pgm into l_drvbl_fctr_prtn_elig_flag, l_drvbl_fctr_apls_rts_flag, l_trk_inelig_per_flag;
2951 --
2952 if c_pgm%found
2953 then
2954 --
2955 if l_drvbl_fctr_prtn_elig_flag = 'Y' or l_drvbl_fctr_apls_rts_flag = 'Y'
2956 then
2957 --
2958 l_flag_bit_val := set_flag_bit_val(p_business_group_id => p_business_group_id
2959 ,p_effective_date => p_effective_date
2960 ,p_drvbl_fctr_prtn_elig_flag => l_drvbl_fctr_prtn_elig_flag
2961 ,p_drvbl_fctr_apls_rts_flag => l_drvbl_fctr_apls_rts_flag
2962 ,p_pgm_id => p_pgm_id
2963 ,p_pl_id => NULL
2964 ,p_oipl_id => NULL
2965 ,p_oiplip_id => NULL
2966 ,p_plip_id => NULL
2967 ,p_ptip_id => NULL);
2968 --
2969 else
2970 --
2971 l_flag_bit_val := 0;
2972 --
2973 end if;
2974 --
2975 load_cache(p_pgm_id => p_pgm_id
2976 ,p_par_pgm_id => p_pgm_id
2977 ,p_flag_bit_val => l_flag_bit_val
2978 ,p_oiplip_flag_bit_val => 0
2979 ,p_trk_inelig_per_flag => l_trk_inelig_per_flag);
2980 --
2981 end if;
2982 --
2983 close c_pgm;
2984 --
2985 hr_utility.set_location('Populate g_cache_proc_object for PLIP_ID = ' || p_plip_id, 15);
2986 --
2987 open c_plip (p_plip_id);
2988 --
2992 then
2989 fetch c_plip into l_drvbl_fctr_prtn_elig_flag, l_drvbl_fctr_apls_rts_flag, l_trk_inelig_per_flag;
2990 --
2991 if c_plip%found
2993 --
2994 if l_drvbl_fctr_prtn_elig_flag = 'Y' OR l_drvbl_fctr_apls_rts_flag = 'Y'
2995 then
2996 --
2997 l_flag_bit_val := set_flag_bit_val(p_business_group_id => p_business_group_id
2998 ,p_effective_date => p_effective_date
2999 ,p_drvbl_fctr_prtn_elig_flag => l_drvbl_fctr_prtn_elig_flag
3000 ,p_drvbl_fctr_apls_rts_flag => l_drvbl_fctr_apls_rts_flag
3001 ,p_pgm_id => NULL
3002 ,p_pl_id => NULL
3003 ,p_oipl_id => NULL
3004 ,p_oiplip_id => NULL
3005 ,p_plip_id => p_plip_id
3006 ,p_ptip_id => NULL);
3007 --
3008 else
3009 --
3010 l_flag_bit_val := 0;
3011 --
3012 end if;
3013 --
3014 load_cache (p_plip_id => p_plip_id
3015 ,p_par_pgm_id => p_pgm_id
3016 ,p_par_ptip_id => p_ptip_id
3017 ,p_par_plip_id => p_plip_id
3018 ,p_par_pl_id => p_pl_id
3019 ,p_flag_bit_val => l_flag_bit_val
3020 ,p_oiplip_flag_bit_val => 0
3021 ,p_trk_inelig_per_flag => l_trk_inelig_per_flag);
3022 --
3023 end if; /* c_plip%found */
3024 --
3025 close c_plip;
3026 --
3027 hr_utility.set_location('Populate g_cache_proc_object for PL_ID = ' || p_pl_id, 15);
3028 --
3029 open c_pln (p_pl_id);
3030 --
3031 fetch c_pln into l_drvbl_fctr_prtn_elig_flag, l_drvbl_fctr_apls_rts_flag, l_trk_inelig_per_flag;
3032 --
3033 if c_pln%found
3034 then
3035 --
3036 if l_drvbl_fctr_prtn_elig_flag = 'Y' or l_drvbl_fctr_apls_rts_flag = 'Y'
3037 then
3038 --
3039 l_flag_bit_val := set_flag_bit_val(p_business_group_id=> p_business_group_id
3040 ,p_effective_date => p_effective_date
3041 ,p_drvbl_fctr_prtn_elig_flag => l_drvbl_fctr_prtn_elig_flag
3042 ,p_drvbl_fctr_apls_rts_flag => l_drvbl_fctr_apls_rts_flag
3043 ,p_pgm_id => NULL
3044 ,p_pl_id => NULL
3045 ,p_oipl_id => NULL
3046 ,p_oiplip_id => NULL
3047 ,p_plip_id => p_plip_id
3048 ,p_ptip_id => NULL);
3049 --
3050 else
3051 --
3052 l_flag_bit_val := 0;
3053 --
3054 end if;
3055 --
3056 load_cache(p_pl_id => p_pl_id
3057 ,p_par_pgm_id => p_pgm_id
3058 ,p_par_ptip_id => p_ptip_id
3059 ,p_par_plip_id => p_plip_id
3063 ,p_trk_inelig_per_flag => l_trk_inelig_per_flag);
3060 ,p_par_pl_id => p_pl_id
3061 ,p_flag_bit_val => l_flag_bit_val
3062 ,p_oiplip_flag_bit_val => 0
3064 --
3065 end if;
3066 --
3067 close c_pln;
3068 --
3069 if p_oipl_id is not null
3070 then
3071 --
3072 hr_utility.set_location('Populate g_cache_proc_object for OIPL_ID = ' || p_oipl_id, 25);
3073 --
3074 open c_oipl (p_oipl_id);
3075 --
3076 fetch c_oipl into l_drvbl_fctr_prtn_elig_flag, l_drvbl_fctr_apls_rts_flag, l_trk_inelig_per_flag;
3077 --
3078 if c_oipl%found
3079 then
3080 --
3081 if l_drvbl_fctr_prtn_elig_flag = 'Y' OR l_drvbl_fctr_apls_rts_flag = 'Y'
3082 then
3083 --
3084 l_flag_bit_val := set_flag_bit_val(p_business_group_id => p_business_group_id
3085 ,p_effective_date => p_effective_date
3086 ,p_drvbl_fctr_prtn_elig_flag => l_drvbl_fctr_prtn_elig_flag
3087 ,p_drvbl_fctr_apls_rts_flag => l_drvbl_fctr_apls_rts_flag
3088 ,p_pgm_id => NULL
3089 ,p_pl_id => NULL
3090 ,p_oipl_id => NULL
3091 ,p_oiplip_id => p_oipl_id
3092 ,p_plip_id => NULL
3093 ,p_ptip_id => NULL);
3094 --
3095 else
3096 --
3097 l_flag_bit_val := 0;
3098 --
3099 end if;
3100 --
3101 l_oiplip_flag_bit_val := set_flag_bit_val(p_business_group_id=> p_business_group_id
3102 ,p_effective_date => p_effective_date
3103 ,p_drvbl_fctr_prtn_elig_flag => 'N'
3104 ,p_drvbl_fctr_apls_rts_flag => 'N'
3105 ,p_pgm_id => NULL
3106 ,p_pl_id => NULL
3107 ,p_oipl_id => NULL
3108 ,p_oiplip_id => p_oiplip_id
3109 ,p_plip_id => NULL
3110 ,p_ptip_id => NULL);
3111 --
3112 load_cache (p_oipl_id => p_oipl_id
3113 ,p_oiplip_id => p_oiplip_id
3114 ,p_par_pgm_id => p_pgm_id
3115 ,p_par_ptip_id => p_ptip_id
3116 ,p_par_plip_id => p_plip_id
3117 ,p_par_pl_id => p_pl_id
3118 ,p_par_opt_id => p_opt_id
3119 ,p_flag_bit_val => l_flag_bit_val
3120 ,p_oiplip_flag_bit_val => l_oiplip_flag_bit_val
3121 ,p_trk_inelig_per_flag => l_trk_inelig_per_flag);
3122 --
3123 end if; /* c_oipl%found */
3124 --
3125 close c_oipl;
3126 --
3127
3128 end if; /* p_oipl_id is not null */
3129 end if; /* p_plip_id is not null */
3130
3131 --
3132 hr_utility.set_location('Leaving ' || l_proc, 20);
3133 --
3134 end build_gsp_rate_sync_coobj_list;
3135 END ben_comp_object_list;