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