[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_ELIG
Source
1 Package Body BEN_EXT_ELIG AS
2 /* $Header: benxelig.pkb 120.6 2007/09/05 02:22:35 tjesumic noship $ */
3
4
5
6 g_package varchar2(33) := ' ben_ext_elig.'; -- Global package name
7
8 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9 TYPE t_varchar2_30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
10 TYPE t_varchar2_600 IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
11 TYPE t_date IS TABLE OF Date INDEX BY BINARY_INTEGER;
12
13
14 --
15 -- ----------------------------------------------------------------------------
16 -- |------< get_rt_info >-----------------------------------------------------|
17 -- ----------------------------------------------------------------------------
18 --
19 Procedure get_rt_info(
20 p_elig_per_elctbl_chc_id in number
21 ) IS
22 --
23 l_proc varchar2(72) := g_package||'get_rt_info';
24 --
25 /*
26 cursor ee_pre_tax_c is
27 select
28 sum(nvl(ecr1.val,ecr2.val)
29 from ben_elig_per_elctbl_chc ele,
30 ben_enrt_rt ecr1,
31 ben_enrt_rt ecr2,
32 ben_enrt_bnft enb
33 where
34 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
35 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
36 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
37 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
38 and nvl(ecr1.tx_typ_cd,ecr2.tx_typ_cd) = 'PRETAX'
39 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC');
40 */
41
42 cursor ee_pre_tax_c is
43 select
44 sum(ecr1.val)
45 from ben_elig_per_elctbl_chc ele,
46 ben_enrt_rt ecr1
47 where
48 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
49 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
50 and ecr1.tx_typ_cd ='PRETAX'
51 and ecr1.acty_typ_cd in ('EEPLC', 'EEIC','EEPYC','PBC','PBC2','PXC') ;
52
53
54
55 cursor ee_pre_tax_enb_c is
56 select
57 sum(ecr2.val)
58 from ben_elig_per_elctbl_chc ele,
59 ben_enrt_rt ecr2,
60 ben_enrt_bnft enb
61 where
62 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
63 and ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
64 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
65 and ecr2.tx_typ_cd='PRETAX'
66 and ecr2.acty_typ_cd IN ('EEPLC','EEIC','EEPYC','PBC','PBC2','PXC') ;
67
68
69
70 --
71 /*
72 cursor ee_after_tax_c is
73 select
74 sum(nvl(ecr1.val,ecr2.val))
75 from ben_elig_per_elctbl_chc ele,
76 ben_enrt_rt ecr1,
77 ben_enrt_rt ecr2,
78 ben_enrt_bnft enb
79 where
80 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
81 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
82 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
83 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
84 and nvl(ecr1.tx_typ_cd,ecr2.tx_typ_cd) = 'AFTERTAX'
85 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC');
86 */
87
88 cursor ee_after_tax_c is
89 select
90 sum(ecr1.val)
91 from ben_elig_per_elctbl_chc ele,
92 ben_enrt_rt ecr1
93 where
94 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
95 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
96 and ecr1.tx_typ_cd='AFTERTAX'
97 and ecr1.acty_typ_cd in ('EEPLC', 'EEIC' , 'EEPYC', 'PBC', 'PBC2', 'PXC')
98 ;
99
100
101
102 cursor ee_after_tax_enb_c is
103 select
104 sum(ecr2.val)
105 from ben_elig_per_elctbl_chc ele,
106 ben_enrt_rt ecr2,
107 ben_enrt_bnft enb
108 where
109 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
110 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
111 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
112 and ecr2.tx_typ_cd='AFTERTAX'
113 and ecr2.acty_typ_cd IN ('EEPLC', 'EEIC','EEPYC', 'PBC', 'PBC2', 'PXC')
114 ;
115
116
117 --
118 /*
119 cursor ee_ttl_c is
120 select
121 sum(nvl(ecr1.val,ecr2.val))
122 from ben_elig_per_elctbl_chc ele,
123 ben_enrt_rt ecr1,
124 ben_enrt_rt ecr2,
125 ben_enrt_bnft enb
126 where
127 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
128 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
129 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
130 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
131 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC');
132 */
133
134 cursor ee_ttl_c is
135 select
136 sum(ecr1.val)
137 from ben_elig_per_elctbl_chc ele,
138 ben_enrt_rt ecr1
139 where
140 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
141 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
142 and ecr1.acty_typ_cd in ('EEPLC', 'EEIC' , 'EEPYC', 'PBC', 'PBC2', 'PXC')
143 ;
144
145
146
147 cursor ee_ttl_enb_c is
148 select
149 sum(ecr2.val)
150 from ben_elig_per_elctbl_chc ele,
151 ben_enrt_rt ecr2,
152 ben_enrt_bnft enb
153 where
154 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
155 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
156 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
157 and ecr2.acty_typ_cd IN ('EEPLC', 'EEIC','EEPYC', 'PBC', 'PBC2', 'PXC')
158 ;
159
160 --
161 /*
162 cursor er_ttl_c is
163 select
164 sum(nvl(ecr1.val,ecr2.val))
165 from ben_elig_per_elctbl_chc ele,
166 ben_enrt_rt ecr1,
167 ben_enrt_rt ecr2,
168 ben_enrt_bnft enb
169 where
170 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
171 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
172 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
173 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
174 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('ERPYC', 'ERMPLC', 'ERC');
175 */
176
177 cursor er_ttl_c is
178 select
179 sum(ecr1.val)
180 from ben_elig_per_elctbl_chc ele,
181 ben_enrt_rt ecr1
182 where
183 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
184 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
185 and ecr1.acty_typ_cd in ('ERPYC', 'ERMPLC', 'ERC')
186 ;
187
188
189
190 cursor er_ttl_enb_c is
191 select
192 sum(ecr2.val)
193 from ben_elig_per_elctbl_chc ele,
194 ben_enrt_rt ecr2,
195 ben_enrt_bnft enb
196 where
197 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
198 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
199 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
200 and ecr2.acty_typ_cd IN ('ERPYC', 'ERMPLC', 'ERC')
201 ;
202
203
204 --
205 /*
206 cursor ee_ttl_dist_c is
207 select
208 sum(nvl(ecr1.val,ecr2.val))
209 from ben_elig_per_elctbl_chc ele,
210 ben_enrt_rt ecr1,
211 ben_enrt_rt ecr2,
212 ben_enrt_bnft enb
213 where
214 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
215 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
216 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
217 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
218 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPYD', 'EERIID', 'PBD', 'PXD', 'PXD1');
219 */
220
221 cursor ee_ttl_dist_c is
222 select
223 sum(ecr1.val)
224 from ben_elig_per_elctbl_chc ele,
225 ben_enrt_rt ecr1
226 where
227 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
228 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
229 and ecr1.acty_typ_cd in ('EEPYD', 'EERIID', 'PBD', 'PXD', 'PXD1')
230 ;
231
232
233
234 cursor ee_ttl_dist_enb_c is
235 select
236 sum(ecr2.val)
237 from ben_elig_per_elctbl_chc ele,
238 ben_enrt_rt ecr2,
239 ben_enrt_bnft enb
240 where
241 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
242 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
243 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
244 and ecr2.acty_typ_cd IN ('EEPYD', 'EERIID', 'PBD', 'PXD', 'PXD1')
245 ;
246
247
248 --
249 /*
250 cursor er_ttl_dist_c is
251 select
252 sum(nvl(ecr1.val,ecr2.val))
253 from ben_elig_per_elctbl_chc ele,
254 ben_enrt_rt ecr1,
255 ben_enrt_rt ecr2,
256 ben_enrt_bnft enb
257 where
258 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
259 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
260 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
261 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
262 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('ERPYD', 'ERD');
263 */
264
265 cursor er_ttl_dist_c is
266 select
267 sum(ecr1.val)
268 from ben_elig_per_elctbl_chc ele,
269 ben_enrt_rt ecr1
270 where
271 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
272 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
273 and ecr1.acty_typ_cd in ('ERPYD', 'ERD')
274 ;
275
276
277
278 cursor er_ttl_dist_enb_c is
279 select
280 sum(ecr2.val)
281 from ben_elig_per_elctbl_chc ele,
282 ben_enrt_rt ecr2,
283 ben_enrt_bnft enb
284 where
285 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
286 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
287 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
288 and ecr2.acty_typ_cd IN ('ERPYD', 'ERD')
289 ;
290
291
292 --
293 /*
294 cursor ttl_oth_rt_c is
295 select
296 sum(nvl(ecr1.val,ecr2.val))
297 from ben_elig_per_elctbl_chc ele,
298 ben_enrt_rt ecr1,
299 ben_enrt_rt ecr2,
300 ben_enrt_bnft enb
301 where
302 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
303 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
304 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
305 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
306 and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) NOT IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD',
307 'PXD1', 'ERPYD', 'ERD', 'EEPLC',
308 'EEIC', 'EEPYC', 'PBC', 'PBC2',
309 'PXC', 'ERPYC', 'ERMPLC', 'ERC');
310 */
311
312 cursor ttl_oth_rt_c is
313 select
314 sum(ecr1.val)
315 from ben_elig_per_elctbl_chc ele,
316 ben_enrt_rt ecr1
317 where
318 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
319 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
320 and ecr1.acty_typ_cd not in ('EEPYD', 'EEPRIID', 'PBD', 'PXD',
321 'PXD1', 'ERPYD', 'ERD', 'EEPLC',
322 'EEIC', 'EEPYC', 'PBC', 'PBC2',
323 'PXC', 'ERPYC', 'ERMPLC', 'ERC')
324 ;
325
326
327 cursor ttl_oth_rt_enb_c is
328 select
329 sum(ecr2.val)
330 from ben_elig_per_elctbl_chc ele,
331 ben_enrt_rt ecr2,
332 ben_enrt_bnft enb
333 where
334 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
335 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
336 and enb.enrt_bnft_id = ecr2.enrt_bnft_id
337 and ecr2.acty_typ_cd not IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD',
338 'PXD1', 'ERPYD', 'ERD', 'EEPLC',
339 'EEIC', 'EEPYC', 'PBC', 'PBC2',
340 'PXC', 'ERPYC', 'ERMPLC', 'ERC')
341 ;
342
343 --
344 cursor c_min_max_rt1 is
345 select
346 enr.mn_elcn_val,
347 enr.mx_elcn_val,
348 enr.incrmt_elcn_val,
349 enr.dflt_val
350 from ben_enrt_rt enr
351 where enr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
352 --
353 cursor c_min_max_rt2 is
354 select
355 enr.mn_elcn_val,
356 enr.mx_elcn_val,
357 enr.incrmt_elcn_val,
358 enr.dflt_val
359 from ben_enrt_rt enr,
360 ben_enrt_bnft bnf
361 where bnf.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
362 and bnf.enrt_bnft_id = enr.enrt_bnft_id;
363
364
365 -----cwb 2832419
366 cursor ttl_type_cd_c(c_typ_cd varchar2) is
367 select
368 sum(nvl(ecr1.val,ecr2.val))
369 from ben_elig_per_elctbl_chc ele,
370 ben_enrt_rt ecr1,
371 ben_enrt_rt ecr2,
372 ben_enrt_bnft enb
373 where
374 p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
375 ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
376 and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
377 and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
378 and (ecr1.acty_typ_cd = c_typ_cd or (ecr1.acty_typ_cd is null and ecr2.acty_typ_cd = c_typ_cd)) ;
379
380
381 l_rate_total number ;
382 l_enb_rate_total number ;
383
384 --
385 begin
386 --
387 hr_utility.set_location('Entering'||l_proc, 5);
388 hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 5);
389
390 ben_ext_person.g_elig_ee_pre_tax_cost := null ;
391 ben_ext_person.g_elig_ee_after_tax_cost := null ;
392 ben_ext_person.g_elig_ee_ttl_cost := null ;
393 ben_ext_person.g_elig_er_ttl_cost := null ;
394 ben_ext_person.g_elig_ee_ttl_distribution := null ;
395 ben_ext_person.g_elig_er_ttl_distribution := null ;
396 ben_ext_person.g_elig_ttl_other_rate := null ;
397
398 --
399 l_rate_total := 0 ;
400 l_enb_rate_total := 0 ;
401 open ee_pre_tax_c;
402 fetch ee_pre_tax_c into l_rate_total;
403 close ee_pre_tax_c;
404
405
406 open ee_pre_tax_enb_c;
407 fetch ee_pre_tax_enb_c into l_enb_rate_total;
408 close ee_pre_tax_enb_c;
409
410 if l_rate_total is not null or l_enb_rate_total is not null then
411 ben_ext_person.g_elig_ee_pre_tax_cost := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
412 end if ;
413 hr_utility.set_location('g_elig_ee_pre_tax_cost '|| nvl(l_rate_total,0) ||'/'||nvl(l_enb_rate_total,0)||'/'|| ben_ext_person.g_elig_ee_pre_tax_cost , 5);
414 --
415 l_rate_total := 0 ;
416 l_enb_rate_total := 0 ;
417
418 open ee_after_tax_c;
419 fetch ee_after_tax_c into l_rate_total;
420 close ee_after_tax_c;
421
422 open ee_after_tax_enb_c;
423 fetch ee_after_tax_enb_c into l_enb_rate_total;
424 close ee_after_tax_enb_c;
425
426 if l_rate_total is not null or l_enb_rate_total is not null then
427 ben_ext_person.g_elig_ee_after_tax_cost := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
428 end if ;
429
430 hr_utility.set_location('g_elig_ee_after_tax_cost '|| nvl(l_rate_total,0) ||'/'||nvl(l_enb_rate_total,0)||'/'|| ben_ext_person.g_elig_ee_after_tax_cost , 5);
431 --
432 l_rate_total := 0 ;
433 l_enb_rate_total := 0 ;
434
435 open ee_ttl_c;
436 fetch ee_ttl_c into l_rate_total;
437 close ee_ttl_c;
438
439
440 open ee_ttl_enb_c;
441 fetch ee_ttl_enb_c into l_enb_rate_total;
442 close ee_ttl_enb_c;
443
444
445 if l_rate_total is not null or l_enb_rate_total is not null then
446 ben_ext_person.g_elig_ee_ttl_cost := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
447 end if ;
448 --
449
450 l_rate_total := 0 ;
451 l_enb_rate_total := 0 ;
452
453 open er_ttl_c;
454 fetch er_ttl_c into l_rate_total;
455 close er_ttl_c;
456
457 open er_ttl_enb_c;
458 fetch er_ttl_enb_c into l_enb_rate_total;
459 close er_ttl_enb_c;
460
461 if l_rate_total is not null or l_enb_rate_total is not null then
462 ben_ext_person.g_elig_er_ttl_cost := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
463 end if ;
464 --
465 l_rate_total := 0 ;
466 l_enb_rate_total := 0 ;
467
468 open ee_ttl_dist_c;
469 fetch ee_ttl_dist_c into l_rate_total;
470 close ee_ttl_dist_c;
471
472
473 open ee_ttl_dist_enb_c;
474 fetch ee_ttl_dist_enb_c into l_enb_rate_total;
475 close ee_ttl_dist_enb_c;
476
477 if l_rate_total is not null or l_enb_rate_total is not null then
478 ben_ext_person.g_elig_ee_ttl_distribution := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
479 end if ;
480 hr_utility.set_location('g_elig_ee_ttl_distribution '|| nvl(l_rate_total,0) ||'/'||nvl(l_enb_rate_total,0)||'/'|| ben_ext_person.g_elig_ee_ttl_distribution , 5);
481 --
482
483 l_rate_total := 0 ;
484 l_enb_rate_total := 0 ;
485
486 open er_ttl_dist_c;
487 fetch er_ttl_dist_c into l_rate_total;
488 close er_ttl_dist_c;
489
490
491 open er_ttl_dist_enb_c;
492 fetch er_ttl_dist_enb_c into l_enb_rate_total;
493 close er_ttl_dist_enb_c;
494
495 if l_rate_total is not null or l_enb_rate_total is not null then
496 ben_ext_person.g_elig_er_ttl_distribution := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
497 end if ;
498 hr_utility.set_location('g_elig_er_ttl_distribution '|| nvl(l_rate_total,0) ||'/'||nvl(l_enb_rate_total,0)||'/'|| ben_ext_person.g_elig_er_ttl_distribution , 5);
499 --
500 l_rate_total := 0 ;
501 l_enb_rate_total := 0 ;
502
503 open ttl_oth_rt_c;
504 fetch ttl_oth_rt_c into l_rate_total;
505 close ttl_oth_rt_c;
506
507 open ttl_oth_rt_enb_c;
508 fetch ttl_oth_rt_enb_c into l_enb_rate_total;
509 close ttl_oth_rt_enb_c;
510
511 if l_rate_total is not null or l_enb_rate_total is not null then
512 ben_ext_person.g_elig_ttl_other_rate := nvl(l_rate_total,0) + nvl(l_enb_rate_total,0) ;
513 end if ;
514
515 --
516 open c_min_max_rt1;
517 fetch c_min_max_rt1 into
518 ben_ext_person.g_elig_min_amt,
519 ben_ext_person.g_elig_max_amt,
520 ben_ext_person.g_elig_incr_amt,
521 ben_ext_person.g_elig_dflt_amt;
522 -- if not found then try another path.
523 if c_min_max_rt1%NOTFOUND then
524 open c_min_max_rt2;
525 fetch c_min_max_rt2 into
526 ben_ext_person.g_elig_min_amt,
527 ben_ext_person.g_elig_max_amt,
528 ben_ext_person.g_elig_incr_amt,
529 ben_ext_person.g_elig_dflt_amt;
530 close c_min_max_rt2;
531 end if;
532 --
533 close c_min_max_rt1;
534 --
535 --cwb 2832419
536 open ttl_type_cd_c('CWBDB');
537 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_dst_bdgt;
538 close ttl_type_cd_c ;
539
540 open ttl_type_cd_c('CWBES');
541 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_elig_salary;
542 close ttl_type_cd_c ;
543
544 open ttl_type_cd_c('CWBGP');
545 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_grant_price;
546 close ttl_type_cd_c ;
547
548
549 open ttl_type_cd_c('CWBMR1');
550 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_misc_rate_1;
551 close ttl_type_cd_c ;
552
553 open ttl_type_cd_c('CWBMR2');
554 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_misc_rate_2;
555 close ttl_type_cd_c ;
556
557 open ttl_type_cd_c('CWBMR3');
558 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_misc_rate_3;
559 close ttl_type_cd_c ;
560
561 open ttl_type_cd_c('CWBOS');
562 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_other_salary;
563 close ttl_type_cd_c ;
564
565 open ttl_type_cd_c('CWBR');
566 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_reserve ;
567 close ttl_type_cd_c ;
568
569 open ttl_type_cd_c('CWBRA');
570 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_recomond_amt;
571 close ttl_type_cd_c ;
572
573 open ttl_type_cd_c('CWBSS');
574 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_stated_salary;
575 close ttl_type_cd_c ;
576
577 open ttl_type_cd_c('CWBTC');
578 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_tot_compensation;
579 close ttl_type_cd_c ;
580
581 open ttl_type_cd_c('CWBWB');
582 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_worksheet_bdgt;
583 close ttl_type_cd_c ;
584
585 open ttl_type_cd_c('CWBWS');
586 fetch ttl_type_cd_c into ben_ext_person.g_elig_ee_cwb_worksheet_amt;
587 close ttl_type_cd_c ;
588
589 hr_utility.set_location('Exiting'||l_proc, 15);
590 --
591 End get_rt_info;
592 --
593 -- ----------------------------------------------------------------------------
594 -- |------< init_detl_globals >------------------------------------------------|
595 -- ----------------------------------------------------------------------------
596 --
597 Procedure init_detl_globals IS
598 --
599 l_proc varchar2(72) := g_package||'init_detl_globals';
600 --
601 Begin
602 --
603 hr_utility.set_location('Entering'||l_proc, 5);
604 --
605 ben_ext_person.g_elig_enrt_strt_dt := null;
606 ben_ext_person.g_elig_enrt_end_dt := null;
607 ben_ext_person.g_elig_dflt_enrt_dt := null;
608 ben_ext_person.g_elig_uom := null;
609 ben_ext_person.g_elig_pl_name := null;
610 ben_ext_person.g_elig_opt_name := null;
611 ben_ext_person.g_elig_cvg_amt := null;
612 ben_ext_person.g_elig_cvg_min_amt := null;
613 ben_ext_person.g_elig_cvg_max_amt := null;
614 ben_ext_person.g_elig_cvg_inc_amt := null;
615 ben_ext_person.g_elig_cvg_dfl_amt := null;
616 ben_ext_person.g_elig_cvg_dfl_flg := null;
617 ben_ext_person.g_elig_cvg_seq_no := null;
618 ben_ext_person.g_elig_cvg_onl_flg := null;
619 ben_ext_person.g_elig_cvg_calc_mthd := null;
620 ben_ext_person.g_elig_cvg_bnft_typ := null;
621 ben_ext_person.g_elig_cvg_bnft_uom := null;
622 ben_ext_person.g_elig_pl_ord_no := null;
623 ben_ext_person.g_elig_opt_ord_no := null;
624 ben_ext_person.g_elig_pl_id := null;
625 ben_ext_person.g_elig_pl_typ_name := null;
626 ben_ext_person.g_elig_opt_id := null;
627 ben_ext_person.g_elig_min_amt := null;
628 ben_ext_person.g_elig_max_amt := null;
629 ben_ext_person.g_elig_incr_amt := null;
630 ben_ext_person.g_elig_dflt_amt := null;
631 ben_ext_person.g_elig_elec_made_dt := null;
632 ben_ext_person.g_elig_program_id := null;
633 ben_ext_person.g_elig_program_name := null;
634 ben_ext_person.g_elig_er_ttl_cost := null;
635 ben_ext_person.g_elig_ee_ttl_cost := null;
636 ben_ext_person.g_elig_ee_after_tax_cost := null;
637 ben_ext_person.g_elig_ee_pre_tax_cost := null;
638 ben_ext_person.g_elig_total_premium_amt := null;
639 ben_ext_person.g_elig_total_premium_uom := null;
640 ben_ext_person.g_elig_rpt_group_name := null;
641 ben_ext_person.g_elig_rpt_group_id := null;
642 ben_ext_person.g_elig_pl_yr_strdt := null;
643 ben_ext_person.g_elig_pl_yr_enddt := null;
644 ben_ext_person.g_elig_pl_seq_num := null;
645 ben_ext_person.g_elig_pip_seq_num := null;
646 ben_ext_person.g_elig_ptp_seq_num := null;
647 ben_ext_person.g_elig_oip_seq_num := null;
648 ben_ext_person.g_elig_flex_01 := null;
649 ben_ext_person.g_elig_flex_02 := null;
650 ben_ext_person.g_elig_flex_03 := null;
651 ben_ext_person.g_elig_flex_04 := null;
652 ben_ext_person.g_elig_flex_05 := null;
653 ben_ext_person.g_elig_flex_06 := null;
654 ben_ext_person.g_elig_flex_07 := null;
655 ben_ext_person.g_elig_flex_08 := null;
656 ben_ext_person.g_elig_flex_09 := null;
657 ben_ext_person.g_elig_flex_10 := null;
658 ben_ext_person.g_elig_plan_flex_01 := null;
659 ben_ext_person.g_elig_plan_flex_02 := null;
660 ben_ext_person.g_elig_plan_flex_03 := null;
661 ben_ext_person.g_elig_plan_flex_04 := null;
662 ben_ext_person.g_elig_plan_flex_05 := null;
663 ben_ext_person.g_elig_plan_flex_06 := null;
664 ben_ext_person.g_elig_plan_flex_07 := null;
665 ben_ext_person.g_elig_plan_flex_08 := null;
666 ben_ext_person.g_elig_plan_flex_09 := null;
667 ben_ext_person.g_elig_plan_flex_10 := null;
668 ben_ext_person.g_elig_pgm_flex_01 := null;
669 ben_ext_person.g_elig_pgm_flex_02 := null;
670 ben_ext_person.g_elig_pgm_flex_03 := null;
671 ben_ext_person.g_elig_pgm_flex_04 := null;
672 ben_ext_person.g_elig_pgm_flex_05 := null;
673 ben_ext_person.g_elig_pgm_flex_06 := null;
674 ben_ext_person.g_elig_pgm_flex_07 := null;
675 ben_ext_person.g_elig_pgm_flex_08 := null;
676 ben_ext_person.g_elig_pgm_flex_09 := null;
677 ben_ext_person.g_elig_pgm_flex_10 := null;
678 ben_ext_person.g_elig_ptp_flex_01 := null;
679 ben_ext_person.g_elig_ptp_flex_02 := null;
680 ben_ext_person.g_elig_ptp_flex_03 := null;
681 ben_ext_person.g_elig_ptp_flex_04 := null;
682 ben_ext_person.g_elig_ptp_flex_05 := null;
683 ben_ext_person.g_elig_ptp_flex_06 := null;
684 ben_ext_person.g_elig_ptp_flex_07 := null;
685 ben_ext_person.g_elig_ptp_flex_08 := null;
686 ben_ext_person.g_elig_ptp_flex_09 := null;
687 ben_ext_person.g_elig_ptp_flex_10 := null;
688 ben_ext_person.g_elig_pl_in_pgm_flex_01 := null;
689 ben_ext_person.g_elig_pl_in_pgm_flex_02 := null;
690 ben_ext_person.g_elig_pl_in_pgm_flex_03 := null;
691 ben_ext_person.g_elig_pl_in_pgm_flex_04 := null;
692 ben_ext_person.g_elig_pl_in_pgm_flex_05 := null;
693 ben_ext_person.g_elig_pl_in_pgm_flex_06 := null;
694 ben_ext_person.g_elig_pl_in_pgm_flex_07 := null;
695 ben_ext_person.g_elig_pl_in_pgm_flex_08 := null;
696 ben_ext_person.g_elig_pl_in_pgm_flex_09 := null;
697 ben_ext_person.g_elig_pl_in_pgm_flex_10 := null;
698 ben_ext_person.g_elig_opt_in_pl_flex_01 := null;
699 ben_ext_person.g_elig_opt_in_pl_flex_02 := null;
700 ben_ext_person.g_elig_opt_in_pl_flex_03 := null;
701 ben_ext_person.g_elig_opt_in_pl_flex_04 := null;
702 ben_ext_person.g_elig_opt_in_pl_flex_05 := null;
703 ben_ext_person.g_elig_opt_in_pl_flex_06 := null;
704 ben_ext_person.g_elig_opt_in_pl_flex_07 := null;
705 ben_ext_person.g_elig_opt_in_pl_flex_08 := null;
706 ben_ext_person.g_elig_opt_in_pl_flex_09 := null;
707 ben_ext_person.g_elig_opt_in_pl_flex_10 := null;
708 ben_ext_person.g_elig_opt_flex_01 := null;
709 ben_ext_person.g_elig_opt_flex_02 := null;
710 ben_ext_person.g_elig_opt_flex_03 := null;
711 ben_ext_person.g_elig_opt_flex_04 := null;
712 ben_ext_person.g_elig_opt_flex_05 := null;
713 ben_ext_person.g_elig_opt_flex_06 := null;
714 ben_ext_person.g_elig_opt_flex_07 := null;
715 ben_ext_person.g_elig_opt_flex_08 := null;
716 ben_ext_person.g_elig_opt_flex_09 := null;
717 ben_ext_person.g_elig_opt_flex_10 := null;
718 ben_ext_person.g_elig_ler_id := null;
719 ben_ext_person.g_elig_lfevt_name := null;
720 ben_ext_person.g_elig_lfevt_status := null;
721 ben_ext_person.g_elig_lfevt_note_dt := null;
722 ben_ext_person.g_elig_lfevt_ocrd_dt := null;
723 ben_ext_person.g_elig_per_elctbl_chc_id := null;
724 ----2559743
725 ben_ext_person.g_elig_pl_fd_name := null ;
726 ben_ext_person.g_elig_pl_fd_code := null ;
727 ben_ext_person.g_elig_pgm_fd_name := null ;
728 ben_ext_person.g_elig_pgm_fd_code := null ;
729 ben_ext_person.g_elig_opt_fd_name := null ;
730 ben_ext_person.g_elig_opt_fd_code := null ;
731 ben_ext_person.g_elig_pl_typ_fd_name := null ;
732 ben_ext_person.g_elig_pl_typ_fd_code := null ;
733 ben_ext_person.g_elig_opt_pl_fd_name := null ;
734 ben_ext_person.g_elig_opt_pl_fd_code := null ;
735 ben_ext_person.g_elig_pl_pgm_fd_name := null ;
736 ben_ext_person.g_elig_pl_pgm_fd_code := null ;
737 ben_ext_person.g_elig_pl_typ_pgm_fd_name := null ;
738 ben_ext_person.g_elig_pl_typ_pgm_fd_code := null ;
739 ---cwb 2832419
740 ben_ext_person.g_elig_ee_cwb_dst_bdgt := null ;
741 ben_ext_person.g_elig_ee_cwb_misc_rate_1 := null ;
742 ben_ext_person.g_elig_ee_cwb_elig_salary := null ;
743 ben_ext_person.g_elig_ee_cwb_misc_rate_2 := null ;
744 ben_ext_person.g_elig_ee_cwb_grant_price := null ;
745 ben_ext_person.g_elig_ee_cwb_other_salary := null ;
746 ben_ext_person.g_elig_ee_cwb_reserve := null ;
747 ben_ext_person.g_elig_ee_cwb_recomond_amt := null ;
748 ben_ext_person.g_elig_ee_cwb_stated_salary := null ;
749 ben_ext_person.g_elig_ee_cwb_tot_compensation := null ;
750 ben_ext_person.g_elig_ee_cwb_worksheet_bdgt := null ;
751 ben_ext_person.g_elig_ee_cwb_elig_salary := null ;
752
753 --cobra letter
754 ben_ext_person.g_elig_cobra_payment_dys := null;
755 ben_ext_person.g_elig_cobra_admin_name := null;
756 ben_ext_person.g_elig_cobra_admin_org_name:= null;
757 ben_ext_person.g_elig_cobra_admin_addr1 := null;
758 ben_ext_person.g_elig_cobra_admin_addr2 := null;
759 ben_ext_person.g_elig_cobra_admin_addr3 := null;
760 ben_ext_person.g_elig_cobra_admin_city := null;
761 ben_ext_person.g_elig_cobra_admin_state := null;
762 ben_ext_person.g_elig_cobra_admin_country := null;
763 ben_ext_person.g_elig_cobra_admin_zip := null;
764 ben_ext_person.g_elig_cobra_admin_phone := null;
765
766 --
767 hr_utility.set_location('Exiting'||l_proc, 15);
768 --
769 End init_detl_globals;
770 --
771 -- ----------------------------------------------------------------------------
772 -- |------< init_detl_drvd_fctr_globals >-------------------------------------|
773 -- ----------------------------------------------------------------------------
774 --
775 Procedure init_detl_drvd_fctr_globals IS
776 --
777 l_proc varchar2(72) := g_package||'init_detl_drvd_fctr_globals';
778 --
779 Begin
780 --
781 hr_utility.set_location('Entering'||l_proc, 5);
782 --
783 ben_ext_person.g_elig_age_val := null;
784 ben_ext_person.g_elig_los_val := null;
785 ben_ext_person.g_elig_age_uom := null;
786 ben_ext_person.g_elig_los_uom := null;
787 ben_ext_person.g_elig_comp_amt := null;
788 ben_ext_person.g_elig_comp_amt_uom := null;
789 ben_ext_person.g_elig_cmbn_age_n_los := null;
790 ben_ext_person.g_elig_hrs_wkd := null;
791 ben_ext_person.g_elig_pct_fl_tm := null;
792 --
793 --
794 hr_utility.set_location('Exiting'||l_proc, 15);
795 --
796 End init_detl_drvd_fctr_globals;
797 --
798 -- ----------------------------------------------------------------------------
799 -- |------< main >-----------------------------------------------------|
800 -- ----------------------------------------------------------------------------
801 --
802 Procedure main
803 (p_person_id in number,
804 p_ext_rslt_id in number,
805 p_ext_file_id in number,
806 p_data_typ_cd in varchar2,
807 p_ext_typ_cd in varchar2,
808 p_chg_evt_cd in varchar2,
809 p_business_group_id in number,
810 p_effective_date in date) is
811 --
812 l_proc varchar2(72) := g_package||'main';
813 --
814 l_include varchar2(1) := 'Y';
815 --
816 /*
817 The following cursor will be splited into many cursor
818 cursor c_elig(p_person_id number) is
819 select + FIRST_ROWS(1) BEN_EXT_ELIG.main.c_elig
820 pler.ler_id ler_id,
821 pler.person_id person_id,
822 pler.per_in_ler_stat_cd per_in_ler_stat_cd,
823 pler.lf_evt_ocrd_dt lf_evt_ocrd_dt,
824 pler.ntfn_dt ntfn_dt,
825 echc.pl_ordr_num pl_seq_num,
826 echc.plip_ordr_num plip_seq_num,
827 echc.ptip_ordr_num ptip_seq_num,
828 echc.oipl_ordr_num oipl_seq_num,
829 echc.pl_id pl_id,
830 echc.epe_attribute1 flex_01,
831 echc.epe_attribute2 flex_02,
832 echc.epe_attribute3 flex_03,
833 echc.epe_attribute4 flex_04,
834 echc.epe_attribute5 flex_05,
835 echc.epe_attribute6 flex_06,
836 echc.epe_attribute7 flex_07,
837 echc.epe_attribute8 flex_08,
838 echc.epe_attribute9 flex_09,
839 echc.epe_attribute10 flex_10,
840 pl.name pl_name,
841 pl.short_name pl_fd_name,
842 pl.short_code pl_fd_code,
843 pl.pln_attribute1 pl_flex_01,
844 pl.pln_attribute2 pl_flex_02,
845 pl.pln_attribute3 pl_flex_03,
846 pl.pln_attribute4 pl_flex_04,
847 pl.pln_attribute5 pl_flex_05,
848 pl.pln_attribute6 pl_flex_06,
849 pl.pln_attribute7 pl_flex_07,
850 pl.pln_attribute8 pl_flex_08,
851 pl.pln_attribute9 pl_flex_09,
852 pl.pln_attribute10 pl_flex_10,
853 ptp.name pl_type_name,
854 ptp.short_name ptp_fd_name,
855 ptp.short_code ptp_fd_code,
856 ptp.ptp_attribute1 ptp_flex_01,
857 ptp.ptp_attribute2 ptp_flex_02,
858 ptp.ptp_attribute3 ptp_flex_03,
859 ptp.ptp_attribute4 ptp_flex_04,
860 ptp.ptp_attribute5 ptp_flex_05,
861 ptp.ptp_attribute6 ptp_flex_06,
862 ptp.ptp_attribute7 ptp_flex_07,
863 ptp.ptp_attribute8 ptp_flex_08,
864 ptp.ptp_attribute9 ptp_flex_09,
865 ptp.ptp_attribute10 ptp_flex_10,
866 plip.short_name plip_fd_name,
867 plip.short_code plip_fd_code,
868 plip.ordr_num pl_ord_no,
869 plip.cpp_attribute1 plip_flex_01,
870 plip.cpp_attribute2 plip_flex_02,
871 plip.cpp_attribute3 plip_flex_03,
872 plip.cpp_attribute4 plip_flex_04,
873 plip.cpp_attribute5 plip_flex_05,
874 plip.cpp_attribute6 plip_flex_06,
875 plip.cpp_attribute7 plip_flex_07,
876 plip.cpp_attribute8 plip_flex_08,
877 plip.cpp_attribute9 plip_flex_09,
878 plip.cpp_attribute10 plip_flex_10,
879 echc.elig_per_elctbl_chc_id elig_per_elctbl_chc_id,
880 echc.enrt_cvg_strt_dt enrt_cvg_strt_dt,
881 echc.yr_perd_id yr_perd_id,
882 echc.pl_typ_id pl_typ_id,
883 echc.last_update_date last_update_date,
884 echc.per_in_ler_id per_in_ler_id,
885 echc.prtt_enrt_rslt_id prtt_enrt_rslt_id,
886 opt.name opt_name,
887 opt.opt_id opt_id,
888 opt.short_name opt_fd_name,
889 opt.short_code opt_fd_code,
890 oipl.short_name oipl_fd_name,
891 oipl.short_code oipl_fd_code,
892 oipl.ordr_num opt_ord_no,
893 oipl.cop_attribute1 oipl_flex_01,
894 oipl.cop_attribute2 oipl_flex_02,
895 oipl.cop_attribute3 oipl_flex_03,
896 oipl.cop_attribute4 oipl_flex_04,
897 oipl.cop_attribute5 oipl_flex_05,
898 oipl.cop_attribute6 oipl_flex_06,
899 oipl.cop_attribute7 oipl_flex_07,
900 oipl.cop_attribute8 oipl_flex_08,
901 oipl.cop_attribute9 oipl_flex_09,
902 oipl.cop_attribute10 oipl_flex_10,
903 ptip.short_name ptip_fd_name,
904 ptip.short_code ptip_fd_code,
905 enb.val cvg_amt,
906 enb.mn_val mn_val,
907 enb.mx_val mx_val,
908 enb.dflt_val dflt_val,
909 enb.incrmt_val incrmt_val,
910 decode(enb.enrt_bnft_id , null,echc.dflt_flag, enb.dflt_flag) dflt_flag,
911 enb.nnmntry_uom nnmntry_uom,
912 enb.bnft_typ_cd bnft_typ_cd,
913 enb.entr_val_at_enrt_flag entr_val_at_enrt_flag,
914 enb.cvg_mlt_cd cvg_mlt_cd,
915 enb.ordr_num ordr_num,
916 ppopl.enrt_perd_strt_dt enrt_strt_dt,
917 ppopl.enrt_perd_end_dt enrt_end_dt,
918 ppopl.dflt_enrt_dt dflt_enrt_dt,
919 ppopl.uom uom,
920 ppopl.elcns_made_dt elcn_made_dt,
921 pgm.pgm_id program_id,
922 pgm.name program_name,
923 pgm.short_name pgm_fd_name,
924 pgm.short_code pgm_fd_code,
925 pgm.pgm_attribute1 pgm_flex_01,
926 pgm.pgm_attribute2 pgm_flex_02,
927 pgm.pgm_attribute3 pgm_flex_03,
928 pgm.pgm_attribute4 pgm_flex_04,
929 pgm.pgm_attribute5 pgm_flex_05,
930 pgm.pgm_attribute6 pgm_flex_06,
931 pgm.pgm_attribute7 pgm_flex_07,
932 pgm.pgm_attribute8 pgm_flex_08,
933 pgm.pgm_attribute9 pgm_flex_09,
934 pgm.pgm_attribute10 pgm_flex_10,
935 opt.opt_attribute1 opt_flex_01,
936 opt.opt_attribute2 opt_flex_02,
937 opt.opt_attribute3 opt_flex_03,
938 opt.opt_attribute4 opt_flex_04,
939 opt.opt_attribute5 opt_flex_05,
940 opt.opt_attribute6 opt_flex_06,
941 opt.opt_attribute7 opt_flex_07,
942 opt.opt_attribute8 opt_flex_08,
943 opt.opt_attribute9 opt_flex_09,
944 opt.opt_attribute10 opt_flex_10,
945 pl.cobra_pymt_due_dy_num
946 from ben_per_in_ler pler,
947 ben_elig_per_elctbl_chc echc,
948 ben_pil_elctbl_chc_popl ppopl,
949 ben_enrt_bnft enb,
950 ben_opt_f opt,
951 ben_pl_f pl,
952 ben_plip_f plip,
953 ben_oipl_f oipl,
954 ben_pgm_f pgm,
955 ben_pl_typ_f ptp,
956 ben_ptip_f ptip -- 2732104
957 where pler.person_id = p_person_id
958 and pler.per_in_ler_id = echc.per_in_ler_id
959 and echc.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
960 and echc.elctbl_flag = 'Y'
961 and echc.pgm_id = pgm.pgm_id(+) --removed -1 nvl statement
962 and echc.pl_id = pl.pl_id(+) --removed -1 nvl statement
963 and oipl.opt_id = opt.opt_id(+) --removed -1 nvl statement
964 and echc.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
965 and echc.oipl_id = oipl.oipl_id(+) --removed -1 nvl statement
966 and echc.plip_id = plip.plip_id(+) --removed -1 nvl statement
967 and echc.ptip_id = ptip.ptip_id(+) --removed -1 nvl statement -- 2732104
968 and pl.pl_typ_id = ptp.pl_typ_id(+) --removed -1 nvl statement
969 and p_effective_date
970 between nvl(pl.effective_start_date,p_effective_date)
971 and nvl(pl.effective_end_date ,p_effective_date)
972 and p_effective_date
973 between nvl(opt.effective_start_date,p_effective_date)
974 and nvl(opt.effective_end_date ,p_effective_date)
975 and p_effective_date
976 between nvl(plip.effective_start_date,p_effective_date)
977 and nvl(plip.effective_end_date ,p_effective_date)
978 and p_effective_date
979 between nvl(oipl.effective_start_date,p_effective_date)
980 and nvl(oipl.effective_end_date ,p_effective_date)
981 and p_effective_date
982 between nvl(ptp.effective_start_date,p_effective_date)
983 and nvl(ptp.effective_end_date ,p_effective_date)
984 and p_effective_date
985 between nvl(pgm.effective_start_date,p_effective_date)
986 and nvl(pgm.effective_end_date ,p_effective_date)
987 and p_effective_date
988 between nvl(ptip.effective_start_date,p_effective_date)
989 and nvl(ptip.effective_end_date ,p_effective_date)
990 ;
991 */
992
993 --- Variale for collection
994
995 l_elig_ler_id_va t_number;
996 l_elig_person_id_va t_number;
997 l_elig_per_in_ler_stat_cd_va t_varchar2_30;
998 l_elig_lf_evt_ocrd_dt_va t_date ;
999 l_elig_ntfn_dt_va t_date ;
1000 l_elig_pl_seq_num_va t_number ;
1001 l_elig_plip_seq_num_va t_number ;
1002 l_elig_ptip_seq_num_va t_number ;
1003 l_elig_oipl_seq_num_va t_number ;
1004 l_elig_pl_id_va t_number ;
1005 l_elig_flex_01_va t_varchar2_600;
1006 l_elig_flex_02_va t_varchar2_600;
1007 l_elig_flex_03_va t_varchar2_600;
1008 l_elig_flex_04_va t_varchar2_600;
1009 l_elig_flex_05_va t_varchar2_600;
1010 l_elig_flex_06_va t_varchar2_600;
1011 l_elig_flex_07_va t_varchar2_600;
1012 l_elig_flex_08_va t_varchar2_600;
1013 l_elig_flex_09_va t_varchar2_600;
1014 l_elig_flex_10_va t_varchar2_600;
1015 l_elig_per_elctbl_chc_id_va t_number ;
1016 l_elig_enrt_cvg_strt_dt_va t_date ;
1017 l_elig_yr_perd_id_va t_number ;
1018 l_elig_pl_typ_id_va t_number ;
1019 l_elig_plip_id_va t_number ;
1020 l_elig_ptip_id_va t_number ;
1021 l_elig_last_update_date_va t_date ;
1022 l_elig_per_in_ler_id_va t_number ;
1023 l_elig_prtt_enrt_rslt_id_va t_number ;
1024 l_elig_program_id_va t_number ;
1025 l_elig_oipl_id_va t_number ;
1026 l_elig_enrt_strt_dt_va t_date ;
1027 l_elig_enrt_end_dt_va t_date ;
1028 l_elig_dflt_enrt_dt_va t_date ;
1029 l_elig_uom_va t_varchar2_30;
1030 l_elig_elcn_made_dt_va t_date ;
1031 l_elig_cvg_amt_va t_number ;
1032 l_elig_mn_val_va t_number ;
1033 l_elig_mx_val_va t_number ;
1034 l_elig_dflt_val_va t_number ;
1035 l_elig_incrmt_val_va t_number ;
1036 l_elig_enrt_bnft_id_va t_number ;
1037 l_elig_echc_dflt_flag_va t_varchar2_30 ;
1038 l_elig_dflt_flag_va t_varchar2_30 ;
1039 l_elig_nnmntry_uom_va t_varchar2_30 ;
1040 l_elig_bnft_typ_cd_va t_varchar2_30 ;
1041 l_elig_entr_val_at_flag_va t_varchar2_30 ;
1042 l_elig_cvg_mlt_cd_va t_varchar2_30 ;
1043 l_elig_ordr_num_va t_number ;
1044
1045
1046 ---
1047
1048 -- splited main cursor extract data from transaction
1049 cursor c_elig(p_person_id number) is
1050 select /*+ FIRST_ROWS(1) BEN_EXT_ELIG.main.c_elig */
1051 pler.ler_id ler_id,
1052 pler.person_id person_id,
1053 pler.per_in_ler_stat_cd per_in_ler_stat_cd,
1054 pler.lf_evt_ocrd_dt lf_evt_ocrd_dt,
1055 pler.ntfn_dt ntfn_dt,
1056 echc.pl_ordr_num pl_seq_num,
1057 echc.plip_ordr_num plip_seq_num,
1058 echc.ptip_ordr_num ptip_seq_num,
1059 echc.oipl_ordr_num oipl_seq_num,
1060 echc.pl_id pl_id,
1061 echc.epe_attribute1 flex_01,
1062 echc.epe_attribute2 flex_02,
1063 echc.epe_attribute3 flex_03,
1064 echc.epe_attribute4 flex_04,
1065 echc.epe_attribute5 flex_05,
1066 echc.epe_attribute6 flex_06,
1067 echc.epe_attribute7 flex_07,
1068 echc.epe_attribute8 flex_08,
1069 echc.epe_attribute9 flex_09,
1070 echc.epe_attribute10 flex_10,
1071 echc.elig_per_elctbl_chc_id elig_per_elctbl_chc_id,
1072 echc.enrt_cvg_strt_dt enrt_cvg_strt_dt,
1073 echc.yr_perd_id yr_perd_id,
1074 echc.pl_typ_id pl_typ_id,
1075 echc.plip_id plip_id,
1076 echc.ptip_id ptip_id,
1077 echc.last_update_date last_update_date,
1078 echc.per_in_ler_id per_in_ler_id,
1079 echc.prtt_enrt_rslt_id prtt_enrt_rslt_id,
1080 echc.pgm_id program_id,
1081 echc.oipl_id ,
1082 ppopl.enrt_perd_strt_dt enrt_strt_dt,
1083 ppopl.enrt_perd_end_dt enrt_end_dt,
1084 ppopl.dflt_enrt_dt dflt_enrt_dt,
1085 ppopl.uom uom,
1086 ppopl.elcns_made_dt elcn_made_dt,
1087 enb.val cvg_amt,
1088 enb.mn_val mn_val,
1089 enb.mx_val mx_val,
1090 enb.dflt_val dflt_val,
1091 enb.incrmt_val incrmt_val,
1092 --decode(enb.enrt_bnft_id , null,echc.dflt_flag, enb.dflt_flag) dflt_flag,/* bug 5292 */
1093 enb.enrt_bnft_id enrt_bnft_id,
1094 echc.dflt_flag echc_dflt_flag ,
1095 enb.dflt_flag dflt_flag,
1096 enb.nnmntry_uom nnmntry_uom,
1097 enb.bnft_typ_cd bnft_typ_cd,
1098 enb.entr_val_at_enrt_flag entr_val_at_enrt_flag,
1099 enb.cvg_mlt_cd cvg_mlt_cd,
1100 enb.ordr_num ordr_num
1101 from ben_per_in_ler pler,
1102 ben_elig_per_elctbl_chc echc,
1103 ben_pil_elctbl_chc_popl ppopl,
1104 ben_enrt_bnft enb
1105 where pler.person_id = p_person_id
1106 and pler.per_in_ler_id = echc.per_in_ler_id
1107 and echc.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
1108 and echc.elctbl_flag = 'Y'
1109 and echc.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
1110 ;
1111
1112
1113
1114 cursor c_oipl (p_oipl_id number
1115 , p_effective_date date) is
1116 select oipl.opt_id opt_id,
1117 oipl.short_name oipl_fd_name,
1118 oipl.short_code oipl_fd_code,
1119 oipl.ordr_num opt_ord_no,
1120 oipl.cop_attribute1 oipl_flex_01,
1121 oipl.cop_attribute2 oipl_flex_02,
1122 oipl.cop_attribute3 oipl_flex_03,
1123 oipl.cop_attribute4 oipl_flex_04,
1124 oipl.cop_attribute5 oipl_flex_05,
1125 oipl.cop_attribute6 oipl_flex_06,
1126 oipl.cop_attribute7 oipl_flex_07,
1127 oipl.cop_attribute8 oipl_flex_08,
1128 oipl.cop_attribute9 oipl_flex_09,
1129 oipl.cop_attribute10 oipl_flex_10
1130 from ben_oipl_f oipl
1131 where oipl_id = p_oipl_id
1132 and p_effective_date
1133 between oipl.effective_start_date and oipl.effective_end_date
1134 ;
1135
1136
1137 cursor c_pl (p_pl_id number ,
1138 p_effective_date date) is
1139 select pl.name pl_name,
1140 pl.short_name pl_fd_name,
1141 pl.short_code pl_fd_code,
1142 pl.pln_attribute1 pl_flex_01,
1143 pl.pln_attribute2 pl_flex_02,
1144 pl.pln_attribute3 pl_flex_03,
1145 pl.pln_attribute4 pl_flex_04,
1146 pl.pln_attribute5 pl_flex_05,
1147 pl.pln_attribute6 pl_flex_06,
1148 pl.pln_attribute7 pl_flex_07,
1149 pl.pln_attribute8 pl_flex_08,
1150 pl.pln_attribute9 pl_flex_09,
1151 pl.pln_attribute10 pl_flex_10,
1152 pl.cobra_pymt_due_dy_num
1153 from ben_pl_f pl
1154 where pl.pl_id = p_pl_id
1155 and p_effective_date
1156 between pl.effective_start_date and pl.effective_end_date
1157 ;
1158
1159
1160 cursor c_opt (p_opt_id number ,
1161 p_effective_date date) is
1162 select opt.name opt_name,
1163 opt.short_name opt_fd_name,
1164 opt.short_code opt_fd_code,
1165 opt.opt_attribute1 opt_flex_01,
1166 opt.opt_attribute2 opt_flex_02,
1167 opt.opt_attribute3 opt_flex_03,
1168 opt.opt_attribute4 opt_flex_04,
1169 opt.opt_attribute5 opt_flex_05,
1170 opt.opt_attribute6 opt_flex_06,
1171 opt.opt_attribute7 opt_flex_07,
1172 opt.opt_attribute8 opt_flex_08,
1173 opt.opt_attribute9 opt_flex_09,
1174 opt.opt_attribute10 opt_flex_10
1175 from ben_opt_f opt
1176 where opt.opt_id = p_opt_id
1177 and p_effective_date
1178 between opt.effective_start_date and opt.effective_end_date
1179 ;
1180
1181
1182 cursor c_pgm_info (p_pgm_id number ,
1183 p_effective_date date) is
1184 select pgm.name program_name,
1185 pgm.short_name pgm_fd_name,
1186 pgm.short_code pgm_fd_code,
1187 pgm.pgm_attribute1 pgm_flex_01,
1188 pgm.pgm_attribute2 pgm_flex_02,
1189 pgm.pgm_attribute3 pgm_flex_03,
1190 pgm.pgm_attribute4 pgm_flex_04,
1191 pgm.pgm_attribute5 pgm_flex_05,
1192 pgm.pgm_attribute6 pgm_flex_06,
1193 pgm.pgm_attribute7 pgm_flex_07,
1194 pgm.pgm_attribute8 pgm_flex_08,
1195 pgm.pgm_attribute9 pgm_flex_09,
1196 pgm.pgm_attribute10 pgm_flex_10
1197 from ben_pgm_f pgm
1198 where pgm.pgm_id = p_pgm_id
1199 and p_effective_date
1200 between pgm.effective_start_date and pgm.effective_end_date
1201 ;
1202
1203
1204 cursor c_ptp (p_pl_typ_id number ,
1205 p_effective_date date) is
1206 select ptp.name pl_type_name,
1207 ptp.short_name ptp_fd_name,
1208 ptp.short_code ptp_fd_code,
1209 ptp.ptp_attribute1 ptp_flex_01,
1210 ptp.ptp_attribute2 ptp_flex_02,
1211 ptp.ptp_attribute3 ptp_flex_03,
1212 ptp.ptp_attribute4 ptp_flex_04,
1213 ptp.ptp_attribute5 ptp_flex_05,
1214 ptp.ptp_attribute6 ptp_flex_06,
1215 ptp.ptp_attribute7 ptp_flex_07,
1216 ptp.ptp_attribute8 ptp_flex_08,
1217 ptp.ptp_attribute9 ptp_flex_09,
1218 ptp.ptp_attribute10 ptp_flex_10
1219 from ben_pl_typ_f ptp
1220 where p_pl_typ_id = ptp.pl_typ_id
1221 and p_effective_date
1222 between ptp.effective_start_date and ptp.effective_end_date
1223 ;
1224
1225
1226
1227
1228 cursor c_plip (p_plip_id number ,
1229 p_effective_date date) is
1230 select plip.short_name plip_fd_name,
1231 plip.short_code plip_fd_code,
1232 plip.ordr_num pl_ord_no,
1233 plip.cpp_attribute1 plip_flex_01,
1234 plip.cpp_attribute2 plip_flex_02,
1235 plip.cpp_attribute3 plip_flex_03,
1236 plip.cpp_attribute4 plip_flex_04,
1237 plip.cpp_attribute5 plip_flex_05,
1238 plip.cpp_attribute6 plip_flex_06,
1239 plip.cpp_attribute7 plip_flex_07,
1240 plip.cpp_attribute8 plip_flex_08,
1241 plip.cpp_attribute9 plip_flex_09,
1242 plip.cpp_attribute10 plip_flex_10
1243 from ben_plip_f plip
1244 where p_plip_id = plip.plip_id
1245 and p_effective_date
1246 between plip.effective_start_date and plip.effective_end_date
1247 ;
1248
1249
1250 cursor c_ptip (p_ptip_id number ,
1251 p_effective_date date) is
1252 select ptip.short_name ptip_fd_name,
1253 ptip.short_code ptip_fd_code
1254 from ben_ptip_f ptip
1255 where p_ptip_id = ptip.ptip_id
1256 and p_effective_date
1257 between ptip.effective_start_date and ptip.effective_end_date
1258 ;
1259
1260
1261
1262
1263 --
1264 cursor c_pl_elig(l_pl_id number,l_ler_id number, l_person_id number) is
1265 select eper.pl_id,
1266 eper.age_val,
1267 eper.age_uom,
1268 eper.los_val,
1269 eper.los_uom,
1270 eper.comp_ref_amt,
1271 eper.comp_ref_uom,
1272 eper.cmbn_age_n_los_val,
1273 eper.hrs_wkd_val,
1274 eper.pct_fl_tm_val
1275 from ben_elig_per_f eper
1276 where eper.person_id = l_person_id
1277 and eper.ler_id = l_ler_id
1278 and eper.pl_id = l_pl_id
1279 and eper.pgm_id is null
1280 and p_effective_date
1281 between eper.effective_start_date
1282 and eper.effective_end_date;
1283 --
1284 cursor c_pgm(l_pl_id number,l_ler_id number, l_person_id number) is
1285 select eper.pl_id,
1286 eper.age_val,
1287 eper.age_uom,
1288 eper.los_val,
1289 eper.los_uom,
1290 eper.comp_ref_amt,
1291 eper.comp_ref_uom,
1292 eper.cmbn_age_n_los_val,
1293 eper.hrs_wkd_val,
1294 eper.pct_fl_tm_val
1295 from ben_elig_per_f eper
1296 where eper.person_id = l_person_id
1297 and eper.ler_id = l_ler_id
1298 and eper.pl_id = l_pl_id
1299 and eper.pgm_id is not null
1300 and p_effective_date
1301 between eper.effective_start_date
1302 and eper.effective_end_date;
1303 --
1304 cursor c_prem_tot(l_elig_per_elctbl_chc_id number) is
1305 select sum(epr.val),
1306 epr.uom
1307 from ben_enrt_prem epr
1308 where epr.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
1309 group by epr.uom;
1310 --
1311 cursor c_rpt_grp(l_elig_per_elctbl_chc_id number) is
1312 select grp.rptg_grp_id,
1313 grp.name
1314 from ben_elig_per_elctbl_chc chc,
1315 ben_popl_rptg_grp_f prpg,
1316 ben_rptg_grp grp
1317 where chc.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
1318 and chc.pl_id = prpg.pl_id
1319 and prpg.rptg_grp_id = grp.rptg_grp_id;
1320 --
1321 cursor c_pl_yr(l_elig_per_elctbl_chc_id number) is
1322 select yrpr.start_date,
1323 yrpr.end_date
1324 from ben_elig_per_elctbl_chc chc,
1325 ben_yr_perd yrpr
1326 where chc.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
1327 and chc.yr_perd_id = yrpr.yr_perd_id;
1328 --
1329 cursor c_ler (cp_per_in_ler_id number) is
1330 select ler.ler_id
1331 ,ler.name
1332 ,pler.per_in_ler_stat_cd
1333 ,pler.ntfn_dt
1334 ,pler.lf_evt_ocrd_dt
1335 from ben_per_in_ler pler
1336 ,ben_ler_f ler
1337 where pler.person_id = p_person_id
1338 and pler.per_in_ler_id = cp_per_in_ler_id
1339 and pler.ler_id = ler.ler_id
1340 and p_effective_date
1341 between ler.effective_start_date
1342 and ler.effective_end_date;
1343 --
1344 l_pl_id ben_pl_f.pl_id%type;
1345 --
1346
1347 -- for cobra aetter
1348 cursor c_cbradm_pl (p_pl_id number) is
1349 select cpr.name admin_name,
1350 cpo.organization_id
1351 from ben_popl_org_role_f cpr,
1352 ben_popl_org_f cpo
1353 where
1354 cpo.pl_id = p_pl_id
1355 AND cpr.popl_org_id = cpo.popl_org_id
1356 and cpr.org_role_typ_cd = 'ADM'
1357 and p_effective_date between cpr.effective_Start_date
1358 and cpr.effective_end_date
1359 and p_effective_date between cpo.effective_Start_date
1360 and cpo.effective_end_date ;
1361
1362 cursor c_cbradm_pgm (p_pgm_id number) is
1363 select cpr.name admin_name,
1364 cpo.organization_id
1365 from ben_popl_org_role_f cpr,
1366 ben_popl_org_f cpo
1367 where
1368 cpo.pgm_id = p_pgm_id
1369 AND cpr.popl_org_id = cpo.popl_org_id
1370 and cpr.org_role_typ_cd = 'ADM'
1371 and p_effective_date between cpr.effective_Start_date
1372 and cpr.effective_end_date
1373 and p_effective_date between cpo.effective_Start_date
1374 and cpo.effective_end_date ;
1375
1376
1377 cursor c_cbradm_adr(p_organization_id number) is
1378 select org.name admin_org_name,
1379 loc.address_line_1 loc_addr1,
1380 loc.address_line_2 loc_addr2,
1381 loc.address_line_3 loc_addr3,
1382 loc.town_or_city loc_city,
1383 loc.region_2 loc_state,
1384 loc.postal_code loc_zip,
1385 loc.country loc_country,
1386 loc.telephone_number_1 loc_phone
1387 from hr_all_organization_units org
1388 ,hr_locations loc
1389 where p_organization_id = org.organization_id
1390 AND org.location_id = loc.location_id ;
1391
1392 l_organization_id number;
1393
1394
1395 Begin
1396 --
1397 hr_utility.set_location('Entering'||l_proc, 5);
1398 --
1399 init_detl_globals;
1400 --
1401 ---FOR elig IN c_elig(p_person_id) LOOP
1402 --- the loop changed into an bulk collect
1403
1404 open c_elig(p_person_id) ;
1405 fetch c_elig bulk collect into
1406 l_elig_ler_id_va ,
1407 l_elig_person_id_va ,
1408 l_elig_per_in_ler_stat_cd_va ,
1409 l_elig_lf_evt_ocrd_dt_va ,
1410 l_elig_ntfn_dt_va ,
1411 l_elig_pl_seq_num_va ,
1412 l_elig_plip_seq_num_va ,
1413 l_elig_ptip_seq_num_va ,
1414 l_elig_oipl_seq_num_va ,
1415 l_elig_pl_id_va ,
1416 l_elig_flex_01_va ,
1417 l_elig_flex_02_va ,
1418 l_elig_flex_03_va ,
1419 l_elig_flex_04_va ,
1420 l_elig_flex_05_va ,
1421 l_elig_flex_06_va ,
1422 l_elig_flex_07_va ,
1423 l_elig_flex_08_va ,
1424 l_elig_flex_09_va ,
1425 l_elig_flex_10_va ,
1426 l_elig_per_elctbl_chc_id_va ,
1427 l_elig_enrt_cvg_strt_dt_va ,
1428 l_elig_yr_perd_id_va ,
1429 l_elig_pl_typ_id_va ,
1430 l_elig_plip_id_va ,
1431 l_elig_ptip_id_va ,
1432 l_elig_last_update_date_va ,
1433 l_elig_per_in_ler_id_va ,
1434 l_elig_prtt_enrt_rslt_id_va ,
1435 l_elig_program_id_va ,
1436 l_elig_oipl_id_va ,
1437 l_elig_enrt_strt_dt_va ,
1438 l_elig_enrt_end_dt_va ,
1439 l_elig_dflt_enrt_dt_va ,
1440 l_elig_uom_va ,
1441 l_elig_elcn_made_dt_va ,
1442 l_elig_cvg_amt_va ,
1443 l_elig_mn_val_va ,
1444 l_elig_mx_val_va ,
1445 l_elig_dflt_val_va ,
1446 l_elig_incrmt_val_va ,
1447 l_elig_enrt_bnft_id_va ,
1448 l_elig_echc_dflt_flag_va ,
1449 l_elig_dflt_flag_va ,
1450 l_elig_nnmntry_uom_va ,
1451 l_elig_bnft_typ_cd_va ,
1452 l_elig_entr_val_at_flag_va ,
1453 l_elig_cvg_mlt_cd_va ,
1454 l_elig_ordr_num_va ;
1455
1456 close c_elig ;
1457
1458 for i IN 1 .. l_elig_per_elctbl_chc_id_va.count
1459 Loop
1460
1461
1462 -- determine the default flag. The decode was removed from sql
1463 --decode(enb.enrt_bnft_id , null,echc.dflt_flag, enb.dflt_flag) dflt_flag,
1464 -- by default the variable holds enb.dflt_flag value
1465
1466 if l_elig_enrt_bnft_id_va(i) is null then
1467 l_elig_dflt_flag_va(i) := l_elig_echc_dflt_flag_va(i) ;
1468 end if ;
1469
1470 --
1471 l_include := 'Y';
1472
1473 -- we need to get option id for inclusion validation
1474 -- fusion try to cache the plan setup information
1475 -- intialize the variable
1476 ben_ext_person.g_elig_opt_id := null ;
1477 ben_ext_person.g_elig_opt_pl_fd_name := null ;
1478 ben_ext_person.g_elig_opt_pl_fd_code := null ;
1479 ben_ext_person.g_elig_opt_ord_no := null ;
1480 ben_ext_person.g_elig_opt_in_pl_flex_01 := null ;
1481 ben_ext_person.g_elig_opt_in_pl_flex_02 := null ;
1482 ben_ext_person.g_elig_opt_in_pl_flex_03 := null ;
1483 ben_ext_person.g_elig_opt_in_pl_flex_04 := null ;
1484 ben_ext_person.g_elig_opt_in_pl_flex_05 := null ;
1485 ben_ext_person.g_elig_opt_in_pl_flex_06 := null ;
1486 ben_ext_person.g_elig_opt_in_pl_flex_07 := null ;
1487 ben_ext_person.g_elig_opt_in_pl_flex_08 := null ;
1488 ben_ext_person.g_elig_opt_in_pl_flex_09 := null ;
1489 ben_ext_person.g_elig_opt_in_pl_flex_10 := null ;
1490
1491 if l_elig_oipl_id_va(i) is not null then
1492 open c_oipl(l_elig_oipl_id_va(i), p_effective_date) ;
1493 fetch c_oipl into
1494 ben_ext_person.g_elig_opt_id ,
1495 ben_ext_person.g_elig_opt_pl_fd_name ,
1496 ben_ext_person.g_elig_opt_pl_fd_code ,
1497 ben_ext_person.g_elig_opt_ord_no ,
1498 ben_ext_person.g_elig_opt_in_pl_flex_01,
1499 ben_ext_person.g_elig_opt_in_pl_flex_02,
1500 ben_ext_person.g_elig_opt_in_pl_flex_03,
1501 ben_ext_person.g_elig_opt_in_pl_flex_04,
1502 ben_ext_person.g_elig_opt_in_pl_flex_05,
1503 ben_ext_person.g_elig_opt_in_pl_flex_06,
1504 ben_ext_person.g_elig_opt_in_pl_flex_07,
1505 ben_ext_person.g_elig_opt_in_pl_flex_08,
1506 ben_ext_person.g_elig_opt_in_pl_flex_09,
1507 ben_ext_person.g_elig_opt_in_pl_flex_10
1508 ;
1509 close c_oipl ;
1510 end if ;
1511
1512
1513 --
1514 ben_ext_evaluate_inclusion.evaluate_eligibility_incl
1515 (p_elct_pl_id => l_elig_pl_id_va(i),
1516 p_elct_enrt_strt_dt => l_elig_enrt_cvg_strt_dt_va(i),
1517 p_elct_yrprd_id => l_elig_yr_perd_id_va(i),
1518 p_elct_pgm_id => l_elig_program_id_va(i),
1519 p_elct_pl_typ_id => l_elig_pl_typ_id_va(i),
1520 p_elct_opt_id => ben_ext_person.g_elig_opt_id ,
1521 p_elct_last_upd_dt => l_elig_last_update_date_va(i),
1522 p_elct_per_in_ler_id => l_elig_per_in_ler_id_va(i),
1523 p_elct_ler_id => l_elig_ler_id_va(i),
1524 p_elct_per_in_ler_stat_cd => l_elig_per_in_ler_stat_cd_va(i),
1525 p_elct_lf_evt_ocrd_dt => l_elig_lf_evt_ocrd_dt_va(i),
1526 p_elct_ntfn_dt => l_elig_ntfn_dt_va(i),
1527 p_prtt_enrt_rslt_id => l_elig_prtt_enrt_rslt_id_va(i),
1528 p_effective_date => p_effective_date,
1529 p_include => l_include
1530 );
1531 --
1532 IF l_include = 'Y' THEN
1533 -- assign eligibility info to global variables
1534 --
1535 --- get plan setup information
1536 ben_ext_person.g_elig_pl_name := null ;
1537 ben_ext_person.g_elig_pl_fd_name := null ;
1538 ben_ext_person.g_elig_pl_fd_code := null ;
1539 ben_ext_person.g_elig_plan_flex_01 := null ;
1540 ben_ext_person.g_elig_plan_flex_02 := null ;
1541 ben_ext_person.g_elig_plan_flex_03 := null ;
1542 ben_ext_person.g_elig_plan_flex_04 := null ;
1543 ben_ext_person.g_elig_plan_flex_05 := null ;
1544 ben_ext_person.g_elig_plan_flex_06 := null ;
1545 ben_ext_person.g_elig_plan_flex_07 := null ;
1546 ben_ext_person.g_elig_plan_flex_08 := null ;
1547 ben_ext_person.g_elig_plan_flex_09 := null ;
1548 ben_ext_person.g_elig_plan_flex_10 := null ;
1549 ben_ext_person.g_elig_cobra_payment_dys := null ;
1550
1551
1552 if l_elig_pl_id_va(i) is not null then
1553 open c_pl(l_elig_pl_id_va(i),p_effective_date) ;
1554 fetch c_pl into
1555 ben_ext_person.g_elig_pl_name ,
1556 ben_ext_person.g_elig_pl_fd_name ,
1557 ben_ext_person.g_elig_pl_fd_code ,
1558 ben_ext_person.g_elig_plan_flex_01,
1559 ben_ext_person.g_elig_plan_flex_02,
1560 ben_ext_person.g_elig_plan_flex_03,
1561 ben_ext_person.g_elig_plan_flex_04,
1562 ben_ext_person.g_elig_plan_flex_05,
1563 ben_ext_person.g_elig_plan_flex_06,
1564 ben_ext_person.g_elig_plan_flex_07,
1565 ben_ext_person.g_elig_plan_flex_08,
1566 ben_ext_person.g_elig_plan_flex_09,
1567 ben_ext_person.g_elig_plan_flex_10,
1568 ben_ext_person.g_elig_cobra_payment_dys
1569 ;
1570 close c_pl ;
1571 end if ;
1572
1573 ben_ext_person.g_elig_opt_name := null;
1574 ben_ext_person.g_elig_opt_fd_name := null;
1575 ben_ext_person.g_elig_opt_fd_code := null;
1576 ben_ext_person.g_elig_opt_flex_01 := null;
1577 ben_ext_person.g_elig_opt_flex_02 := null;
1578 ben_ext_person.g_elig_opt_flex_03 := null;
1579 ben_ext_person.g_elig_opt_flex_04 := null;
1580 ben_ext_person.g_elig_opt_flex_05 := null;
1581 ben_ext_person.g_elig_opt_flex_06 := null;
1582 ben_ext_person.g_elig_opt_flex_07 := null;
1583 ben_ext_person.g_elig_opt_flex_08 := null;
1584 ben_ext_person.g_elig_opt_flex_09 := null;
1585 ben_ext_person.g_elig_opt_flex_10 := null;
1586
1587 if ben_ext_person.g_elig_opt_id is not null then
1588 open c_opt (ben_ext_person.g_elig_opt_id,p_effective_date) ;
1589 fetch c_opt into
1590 ben_ext_person.g_elig_opt_name ,
1591 ben_ext_person.g_elig_opt_fd_name ,
1592 ben_ext_person.g_elig_opt_fd_code ,
1593 ben_ext_person.g_elig_opt_flex_01 ,
1594 ben_ext_person.g_elig_opt_flex_02 ,
1595 ben_ext_person.g_elig_opt_flex_03 ,
1596 ben_ext_person.g_elig_opt_flex_04 ,
1597 ben_ext_person.g_elig_opt_flex_05 ,
1598 ben_ext_person.g_elig_opt_flex_06 ,
1599 ben_ext_person.g_elig_opt_flex_07 ,
1600 ben_ext_person.g_elig_opt_flex_08 ,
1601 ben_ext_person.g_elig_opt_flex_09 ,
1602 ben_ext_person.g_elig_opt_flex_10
1603 ;
1604 close c_opt ;
1605 end if ;
1606
1607 ben_ext_person.g_elig_program_name := null ;
1608 ben_ext_person.g_elig_pgm_fd_name := null ;
1609 ben_ext_person.g_elig_pgm_fd_code := null ;
1610 ben_ext_person.g_elig_pgm_flex_01 := null ;
1611 ben_ext_person.g_elig_pgm_flex_02 := null ;
1612 ben_ext_person.g_elig_pgm_flex_03 := null ;
1613 ben_ext_person.g_elig_pgm_flex_04 := null ;
1614 ben_ext_person.g_elig_pgm_flex_05 := null ;
1615 ben_ext_person.g_elig_pgm_flex_06 := null ;
1616 ben_ext_person.g_elig_pgm_flex_07 := null ;
1617 ben_ext_person.g_elig_pgm_flex_08 := null ;
1618 ben_ext_person.g_elig_pgm_flex_09 := null ;
1619 ben_ext_person.g_elig_pgm_flex_10 := null ;
1620
1621
1622 if l_elig_program_id_va(i) is not null then
1623 open c_pgm_info (l_elig_program_id_va(i),p_effective_date) ;
1624 fetch c_pgm_info into
1625 ben_ext_person.g_elig_program_name ,
1626 ben_ext_person.g_elig_pgm_fd_name ,
1627 ben_ext_person.g_elig_pgm_fd_code ,
1628 ben_ext_person.g_elig_pgm_flex_01 ,
1629 ben_ext_person.g_elig_pgm_flex_02 ,
1630 ben_ext_person.g_elig_pgm_flex_03 ,
1631 ben_ext_person.g_elig_pgm_flex_04 ,
1632 ben_ext_person.g_elig_pgm_flex_05 ,
1633 ben_ext_person.g_elig_pgm_flex_06 ,
1634 ben_ext_person.g_elig_pgm_flex_07 ,
1635 ben_ext_person.g_elig_pgm_flex_08 ,
1636 ben_ext_person.g_elig_pgm_flex_09 ,
1637 ben_ext_person.g_elig_pgm_flex_10
1638 ;
1639 close c_pgm_info ;
1640 end if ;
1641
1642 ben_ext_person.g_elig_pl_typ_name := null ;
1643 ben_ext_person.g_elig_pl_typ_fd_name := null ;
1644 ben_ext_person.g_elig_pl_typ_fd_code := null ;
1645 ben_ext_person.g_elig_ptp_flex_01 := null ;
1646 ben_ext_person.g_elig_ptp_flex_02 := null ;
1647 ben_ext_person.g_elig_ptp_flex_03 := null ;
1648 ben_ext_person.g_elig_ptp_flex_04 := null ;
1649 ben_ext_person.g_elig_ptp_flex_05 := null ;
1650 ben_ext_person.g_elig_ptp_flex_06 := null ;
1651 ben_ext_person.g_elig_ptp_flex_07 := null ;
1652 ben_ext_person.g_elig_ptp_flex_08 := null ;
1653 ben_ext_person.g_elig_ptp_flex_09 := null ;
1654 ben_ext_person.g_elig_ptp_flex_10 := null ;
1655
1656
1657 if l_elig_pl_typ_id_va(i) is not null then
1658 open c_ptp (l_elig_pl_typ_id_va(i),p_effective_date) ;
1659 fetch c_ptp into
1660 ben_ext_person.g_elig_pl_typ_name ,
1661 ben_ext_person.g_elig_pl_typ_fd_name ,
1662 ben_ext_person.g_elig_pl_typ_fd_code ,
1663 ben_ext_person.g_elig_ptp_flex_01 ,
1664 ben_ext_person.g_elig_ptp_flex_02 ,
1665 ben_ext_person.g_elig_ptp_flex_03 ,
1666 ben_ext_person.g_elig_ptp_flex_04 ,
1667 ben_ext_person.g_elig_ptp_flex_05 ,
1668 ben_ext_person.g_elig_ptp_flex_06 ,
1669 ben_ext_person.g_elig_ptp_flex_07 ,
1670 ben_ext_person.g_elig_ptp_flex_08 ,
1671 ben_ext_person.g_elig_ptp_flex_09 ,
1672 ben_ext_person.g_elig_ptp_flex_10
1673 ;
1674 close c_ptp ;
1675 end if ;
1676
1677 ben_ext_person.g_elig_pl_pgm_fd_name := null ;
1678 ben_ext_person.g_elig_pl_pgm_fd_code := null ;
1679 ben_ext_person.g_elig_pl_ord_no := null ;
1680 ben_ext_person.g_elig_pl_in_pgm_flex_01 := null ;
1681 ben_ext_person.g_elig_pl_in_pgm_flex_02 := null ;
1682 ben_ext_person.g_elig_pl_in_pgm_flex_03 := null ;
1683 ben_ext_person.g_elig_pl_in_pgm_flex_04 := null ;
1684 ben_ext_person.g_elig_pl_in_pgm_flex_05 := null ;
1685 ben_ext_person.g_elig_pl_in_pgm_flex_06 := null ;
1686 ben_ext_person.g_elig_pl_in_pgm_flex_07 := null ;
1687 ben_ext_person.g_elig_pl_in_pgm_flex_08 := null ;
1688 ben_ext_person.g_elig_pl_in_pgm_flex_09 := null ;
1689 ben_ext_person.g_elig_pl_in_pgm_flex_10 := null ;
1690
1691
1692
1693 if l_elig_plip_id_va(i) is not null then
1694 open c_plip (l_elig_plip_id_va(i),p_effective_date) ;
1695 fetch c_plip into
1696 ben_ext_person.g_elig_pl_pgm_fd_name ,
1697 ben_ext_person.g_elig_pl_pgm_fd_code ,
1698 ben_ext_person.g_elig_pl_ord_no ,
1699 ben_ext_person.g_elig_pl_in_pgm_flex_01 ,
1700 ben_ext_person.g_elig_pl_in_pgm_flex_02 ,
1701 ben_ext_person.g_elig_pl_in_pgm_flex_03 ,
1702 ben_ext_person.g_elig_pl_in_pgm_flex_04 ,
1703 ben_ext_person.g_elig_pl_in_pgm_flex_05 ,
1704 ben_ext_person.g_elig_pl_in_pgm_flex_06 ,
1705 ben_ext_person.g_elig_pl_in_pgm_flex_07 ,
1706 ben_ext_person.g_elig_pl_in_pgm_flex_08 ,
1707 ben_ext_person.g_elig_pl_in_pgm_flex_09 ,
1708 ben_ext_person.g_elig_pl_in_pgm_flex_10
1709 ;
1710
1711 close c_plip ;
1712 end if ;
1713
1714
1715
1716 ben_ext_person.g_elig_pl_typ_pgm_fd_name := null ;
1717 ben_ext_person.g_elig_pl_typ_pgm_fd_code := null ;
1718
1719 if l_elig_ptip_id_va(i) is not null then
1720 open c_ptip (l_elig_ptip_id_va(i),p_effective_date) ;
1721 fetch c_ptip into
1722 ben_ext_person.g_elig_pl_typ_pgm_fd_name ,
1723 ben_ext_person.g_elig_pl_typ_pgm_fd_code
1724 ;
1725 close c_ptip ;
1726 end if ;
1727
1728
1729
1730 ben_ext_person.g_elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id_va(i);
1731 ben_ext_person.g_elig_cvg_amt := l_elig_cvg_amt_va(i);
1732 ben_ext_person.g_elig_cvg_min_amt := l_elig_mn_val_va(i);
1733 ben_ext_person.g_elig_cvg_max_amt := l_elig_mx_val_va(i);
1734 ben_ext_person.g_elig_cvg_inc_amt := l_elig_incrmt_val_va(i);
1735 ben_ext_person.g_elig_cvg_dfl_amt := l_elig_dflt_val_va(i);
1736 ben_ext_person.g_elig_cvg_dfl_flg := l_elig_dflt_flag_va(i);
1737 ben_ext_person.g_elig_cvg_seq_no := l_elig_ordr_num_va(i);
1738 ben_ext_person.g_elig_cvg_onl_flg := l_elig_entr_val_at_flag_va(i);
1739 ben_ext_person.g_elig_cvg_calc_mthd := l_elig_cvg_mlt_cd_va(i);
1740 ben_ext_person.g_elig_cvg_bnft_typ := l_elig_bnft_typ_cd_va(i);
1741 ben_ext_person.g_elig_cvg_bnft_uom := l_elig_nnmntry_uom_va(i);
1742 ben_ext_person.g_elig_enrt_strt_dt := l_elig_enrt_strt_dt_va(i);
1743 ben_ext_person.g_elig_enrt_end_dt := l_elig_enrt_end_dt_va(i);
1744 ben_ext_person.g_elig_dflt_enrt_dt := l_elig_dflt_enrt_dt_va(i);
1745 ben_ext_person.g_elig_uom := l_elig_uom_va(i);
1746 ben_ext_person.g_elig_pl_id := l_elig_pl_id_va(i);
1747 ben_ext_person.g_elig_pl_typ_id := l_elig_pl_typ_id_va(i);
1748 ben_ext_person.g_elig_elec_made_dt := l_elig_elcn_made_dt_va(i);
1749 ben_ext_person.g_elig_program_id := l_elig_program_id_va(i);
1750 ben_ext_person.g_elig_pl_seq_num := l_elig_pl_seq_num_va(i);
1751 ben_ext_person.g_elig_pip_seq_num := l_elig_plip_seq_num_va(i);
1752 ben_ext_person.g_elig_ptp_seq_num := l_elig_ptip_seq_num_va(i);
1753 ben_ext_person.g_elig_oip_seq_num := l_elig_oipl_seq_num_va(i);
1754 ben_ext_person.g_elig_flex_01 := l_elig_flex_01_va(i);
1755 ben_ext_person.g_elig_flex_02 := l_elig_flex_02_va(i);
1756 ben_ext_person.g_elig_flex_03 := l_elig_flex_03_va(i);
1757 ben_ext_person.g_elig_flex_04 := l_elig_flex_04_va(i);
1758 ben_ext_person.g_elig_flex_05 := l_elig_flex_05_va(i);
1759 ben_ext_person.g_elig_flex_06 := l_elig_flex_06_va(i);
1760 ben_ext_person.g_elig_flex_07 := l_elig_flex_07_va(i);
1761 ben_ext_person.g_elig_flex_08 := l_elig_flex_08_va(i);
1762 ben_ext_person.g_elig_flex_09 := l_elig_flex_09_va(i);
1763 ben_ext_person.g_elig_flex_10 := l_elig_flex_10_va(i);
1764 --
1765 --
1766 init_detl_drvd_fctr_globals;
1767 --
1768 open c_pl_elig(l_elig_pl_id_va(i) ,l_elig_ler_id_va(i) ,l_elig_person_id_va(i));
1769 --
1770 fetch c_pl_elig into l_pl_id,
1771 ben_ext_person.g_elig_age_val,
1772 ben_ext_person.g_elig_age_uom,
1773 ben_ext_person.g_elig_los_val,
1774 ben_ext_person.g_elig_los_uom,
1775 ben_ext_person.g_elig_comp_amt,
1776 ben_ext_person.g_elig_comp_amt_uom,
1777 ben_ext_person.g_elig_cmbn_age_n_los,
1778 ben_ext_person.g_elig_hrs_wkd,
1779 ben_ext_person.g_elig_pct_fl_tm;
1780 --
1781 if c_pl_elig%notfound then
1782 --
1783 open c_pgm(l_elig_pl_id_va(i) ,l_elig_ler_id_va(i) ,l_elig_person_id_va(i));
1784 --
1785 fetch c_pgm into l_pl_id,
1786 ben_ext_person.g_elig_age_val,
1787 ben_ext_person.g_elig_age_uom,
1788 ben_ext_person.g_elig_los_val,
1789 ben_ext_person.g_elig_los_uom ,
1790 ben_ext_person.g_elig_comp_amt,
1791 ben_ext_person.g_elig_comp_amt_uom,
1792 ben_ext_person.g_elig_cmbn_age_n_los,
1793 ben_ext_person.g_elig_hrs_wkd,
1794 ben_ext_person.g_elig_pct_fl_tm;
1795 --
1796 close c_pgm;
1797 --
1798 end if;
1799 --
1800 close c_pl_elig;
1801 --
1802 -- Addition rate informations
1803 --
1804 if ben_extract.g_chcrt_csr = 'Y' then
1805 --
1806 get_rt_info( p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id_va(i));
1807 --
1808 end if;
1809 --
1810 -- Premium informations
1811 --
1812
1813 if ben_extract.g_eprem_csr = 'Y' then
1814 --
1815 open c_prem_tot(l_elig_per_elctbl_chc_id_va(i));
1816 --
1817 fetch c_prem_tot into ben_ext_person.g_elig_total_premium_amt
1818 , ben_ext_person.g_elig_total_premium_uom;
1819 --
1820 close c_prem_tot;
1821 --
1822 end if;
1823 --
1824 --
1825 -- Reporting group informations
1826 --
1827 if ben_extract.g_ergrp_csr = 'Y' then
1828 --
1829 open c_rpt_grp(l_elig_per_elctbl_chc_id_va(i));
1830 --
1831 fetch c_rpt_grp into ben_ext_person.g_elig_rpt_group_id,
1832 ben_ext_person.g_elig_rpt_group_name;
1833 --
1834 close c_rpt_grp;
1835 --
1836 end if;
1837 --
1838 --
1839 -- Plan Year informations
1840 --
1841 if ben_extract.g_eplyr_csr = 'Y' then
1842 --
1843 open c_pl_yr(l_elig_per_elctbl_chc_id_va(i));
1844 --
1845 fetch c_pl_yr into ben_ext_person.g_elig_pl_yr_strdt,
1846 ben_ext_person.g_elig_pl_yr_enddt;
1847 --
1848 close c_pl_yr;
1849 --
1850 end if;
1851 --
1852 -- Life Event informations
1853 --
1854 if ben_extract.g_eler_csr = 'Y' then
1855 --
1856 open c_ler(l_elig_per_in_ler_id_va(i)) ;
1857 --
1858 fetch c_ler into ben_ext_person.g_elig_ler_id,
1859 ben_ext_person.g_elig_lfevt_name,
1860 ben_ext_person.g_elig_lfevt_status,
1861 ben_ext_person.g_elig_lfevt_note_dt,
1862 ben_ext_person.g_elig_lfevt_ocrd_dt;
1863 --
1864 close c_ler;
1865 --
1866 end if;
1867
1868 --
1869 -- cobra admin information
1870 hr_utility.set_location(' cobra cursor ' || ben_Extract.g_cbradm_csr ,160);
1871 if ben_Extract.g_cbradm_csr = 'Y' then
1872
1873 hr_utility.set_location('getting cobra admin ' ,160);
1874 open c_cbradm_pl(l_elig_pl_id_va(i));
1875 fetch c_cbradm_pl into ben_ext_person.g_elig_cobra_admin_name ,
1876 l_organization_id ;
1877 if c_cbradm_pl%notfound then
1878 open c_cbradm_pgm(l_elig_program_id_va(i));
1879 fetch c_cbradm_pgm into ben_ext_person.g_elig_cobra_admin_name ,
1880 l_organization_id ;
1881 close c_cbradm_pgm ;
1882 end if ;
1883 close c_cbradm_pl ;
1884 hr_utility.set_location(' cobra admin ' || ben_ext_person.g_elig_cobra_admin_name ,160);
1885 --- cobra admin address
1886 if l_organization_id is not null then
1887
1888 hr_utility.set_location('getting cobra admin address ' ,160);
1889 open c_cbradm_adr(l_organization_id ) ;
1890 fetch c_cbradm_adr into
1891 ben_ext_person.g_elig_cobra_admin_org_name
1892 ,ben_ext_person.g_elig_cobra_admin_addr1
1893 ,ben_ext_person.g_elig_cobra_admin_addr2
1894 ,ben_ext_person.g_elig_cobra_admin_addr3
1895 ,ben_ext_person.g_elig_cobra_admin_city
1896 ,ben_ext_person.g_elig_cobra_admin_state
1897 ,ben_ext_person.g_elig_cobra_admin_zip
1898 ,ben_ext_person.g_elig_cobra_admin_country
1899 ,ben_ext_person.g_elig_cobra_admin_phone;
1900 close c_cbradm_adr ;
1901 hr_utility.set_location(' cobra org admin ' || ben_ext_person.g_elig_cobra_admin_org_name ,160);
1902 hr_utility.set_location(' cobra org city ' || ben_ext_person.g_elig_cobra_admin_city ,160);
1903 end if ;
1904 end if ;
1905 -- cobra end
1906
1907 -- format and write
1908 --
1909 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
1910 p_ext_file_id => p_ext_file_id,
1911 p_data_typ_cd => p_data_typ_cd,
1912 p_ext_typ_cd => p_ext_typ_cd,
1913 p_rcd_typ_cd => 'D', --detail
1914 p_low_lvl_cd => 'G', --eligibility?
1915 p_person_id => p_person_id,
1916 p_chg_evt_cd => null,
1917 p_business_group_id => p_business_group_id,
1918 p_effective_date => p_effective_date);
1919 --
1920 -- Call Eligible Dependents routine
1921 --
1922 if ben_extract.g_eligdpnt_lvl = 'Y' then
1923 --
1924 ben_ext_elig_dpnt.main
1925 (p_person_id => p_person_id,
1926 p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id_va(i),
1927 p_ext_rslt_id => p_ext_rslt_id,
1928 p_ext_file_id => p_ext_file_id,
1929 p_data_typ_cd => p_data_typ_cd,
1930 p_ext_typ_cd => p_ext_typ_cd,
1931 p_chg_evt_cd => p_chg_evt_cd,
1932 p_business_group_id => p_business_group_id,
1933 p_effective_date => p_effective_date);
1934 --
1935 end if;
1936 --
1937 END IF;
1938 --
1939 END LOOP;
1940 --
1941 hr_utility.set_location('Exiting'||l_proc, 15);
1942 --
1943 END;
1944 END;