1 Package ben_pen_bus AUTHID CURRENT_USER as
2 /* $Header: bepenrhi.pkh 120.3 2011/07/03 08:17:27 pvelvano ship $ */
3 --
4 -- MH - Split union cursor g_c1 into g_enrolled and g_epenotenrolled to
5 -- improve scalability.
6 --
7 cursor g_enrolled
8 (c_effective_date date
9 ,c_business_group_id number
10 ,c_person_id number
11 ,c_pgm_id number
12 ,c_include_erl varchar2
13 )
14 is
15 select pen.pgm_id,
16 pen.ptip_id,
17 pen.pl_typ_id,
18 /*Bug#: 3255596: Fetching plip_id from CPP record
19 when plip_id is null in EPE record*/
20 NVL(epe.plip_id, cpp.plip_id) plip_id,
21 /* End Bug#: 3255596: change */
22 pen.pl_id,
23 pen.oipl_id,
24 pen.enrt_cvg_strt_dt,
25 pen.enrt_cvg_thru_dt,
26 pen.prtt_enrt_rslt_id,
27 pen.RPLCS_SSPNDD_RSLT_ID,
28 pen.SSPNDD_FLAG,
29 'N' interim_flag,
30 pen.person_id,
31 0 Calc_interm,
32 nvl(pen.bnft_amt,0) bnft_amt,
33 pen.uom,
34 epe.elig_per_elctbl_chc_id,
35 epe.MUST_ENRL_ANTHR_PL_ID,
36 'N' dpnt_cvd_by_othr_apls_flag,
37 -9999999999999999999999999999999999999 opt_id
38 from ben_prtt_enrt_rslt_f pen,
39 ben_elig_per_elctbl_chc epe,
40 /*Bug#: 3255596: Fetching plip_id from CPP record
41 when plip_id is null in EPE record*/
42 ben_plip_f cpp,
43 ben_ler_f ler --10248940
44 /* End Bug#: 3255596: change */
45 /*
46 ,
47 ben_oipl_f cop,
48 ben_pl_f pln
49 */
50 where pen.person_id = c_person_id
51 and pen.ler_id = ler.ler_id
52 and c_effective_date between ler.effective_start_date and ler.effective_end_date
53 and pen.prtt_enrt_rslt_stat_cd is null
54 and nvl(pen.pgm_id,-999999) = c_pgm_id
55 /*
56 Bug 5425 : Following 2 lines are commented as enrollment
57 which are created in future due to benmngle run in future
58 will not be picked up. Also added check on eef = eot
59 and p_effective_date between
60 pen.effective_start_date and pen.effective_end_date -1
61 */
62 and pen.effective_end_date = hr_api.g_eot
63 -- 2159253 for enforcing minimum limitation - need to remove comp. objects ending in future
64 -- and pen.enrt_cvg_thru_dt >= c_effective_date
65 --Bug 4361013 fix need to get the ended coverage also in case of
66 --overriden enrollments.
67 and ( pen.enrt_cvg_thru_dt = hr_api.g_eot or
68 (pen.enrt_cvg_thru_dt >= c_effective_date and nvl(pen.enrt_ovridn_flag,'N') = 'Y' and ler.typ_cd <> 'SCHEDDU')) --10248940
69 and pen.effective_end_date >= pen.enrt_cvg_thru_dt
70 -- Bug 2677804 Why do we need to exclude this.
71 -- If a participant intinues in the same enrollment
72 -- the pen contains this info also .
73 -- and (pen.ENRT_OVRID_THRU_DT is NULL
74 -- or pen.ENRT_OVRID_THRU_DT < c_effective_date)
75 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
76 and pen.per_in_ler_id = epe.per_in_ler_id (+)
77 and pen.comp_lvl_cd not in ('PLANFC','PLANIMP')
78 and (epe.per_in_ler_id is null or
79 exists (select null
80 from ben_per_in_ler pil
81 where pil.per_in_ler_id = epe.per_in_ler_id
82 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
83 )
84 /*Bug#: 3255596: Fetching plip_id from CPP record
85 when plip_id is null in EPE record*/
86 and nvl(cpp.pgm_id(+),-999999) = c_pgm_id
87 and cpp.pl_id(+) = pen.pl_id
88 and cpp.business_group_id(+) = c_business_group_id
89 and c_effective_date between cpp.effective_start_date(+) and cpp.effective_end_date(+)
90 /* End Bug#: 3255596: change */
91 /*
92 and pen.oipl_id = cop.oipl_id (+)
93 and c_effective_date
94 between cop.effective_start_date (+) and cop.effective_end_date (+)
95 and pen.pl_id = pln.pl_id
96 and c_effective_date
97 between pln.effective_start_date and pln.effective_end_date
98 */
99 and (c_include_erl = 'Y' or
100 not exists (select null
101 from ben_enrt_bnft enb
102 where pen.prtt_enrt_rslt_id = enb.prtt_enrt_rslt_id
103 and enb.cvg_mlt_cd = 'ERL'))
104 order by 1,2,3,4,5,6,7;
105 --
106 cursor g_epenotenrolled
107 (c_effective_date date
108 ,c_business_group_id number
109 ,c_person_id number
110 ,c_pgm_id number
111 )
112 is
113 --
114 -- Added union (below) to pick up choices which are not enrolled in
115 -- so that we can check minimums when not enrolled in anything.
116 -- jcarpent 13-jul-1999
117 -- added epe.pgm_id check
118 -- jcarpent 11-oct-1999
119 select distinct
120 epe.pgm_id,
121 epe.ptip_id,
122 epe.pl_typ_id,
123 epe.plip_id,
124 epe.pl_id,
125 epe.oipl_id,
126 pen.enrt_cvg_strt_dt,
127 pen.enrt_cvg_thru_dt,
128 pen.prtt_enrt_rslt_id,
129 pen.RPLCS_SSPNDD_RSLT_ID,
130 pen.SSPNDD_FLAG,
131 'N' interim_flag,
132 pil.person_id,
133 0 Calc_interm,
134 nvl(pen.bnft_amt,0) bnft_amt,
135 pen.uom,
136 epe.elig_per_elctbl_chc_id,
137 epe.MUST_ENRL_ANTHR_PL_ID,
138 'N' dpnt_cvd_by_othr_apls_flag,
139 -9999999999999999999999999999999999999 opt_id
140 /*
141 cop.opt_id
142 */
143 from ben_prtt_enrt_rslt_f pen,
144 ben_elig_per_elctbl_chc epe,
145 ben_pil_elctbl_chc_popl pel,
146 /*
147 ben_oipl_f cop,
148 */
149 ben_per_in_ler pil
150 where pil.person_id = c_person_id
151 and pil.per_in_ler_stat_cd = 'STRTD'
152 and epe.per_in_ler_id = pil.per_in_ler_id
153 and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
154 and (pel.auto_asnd_dt is not null OR
155 pel.dflt_asnd_dt is not null OR
156 pel.elcns_made_dt is not null)
157 and nvl(epe.pgm_id,-999999) = c_pgm_id
158 and c_effective_date between
159 pen.effective_start_date(+) and pen.effective_end_date(+) -1
160 and pen.effective_end_date(+) >= pen.enrt_cvg_strt_dt(+)
161 and pen.enrt_cvg_thru_dt (+) >= c_effective_date
162 and pen.prtt_enrt_rslt_stat_cd(+) is null
163 --
164 -- nvl allows the null condition to be outer joined
165 -- Bug 2677804 Why do we need to exclude this.
166 -- If a participant intinues in the same enrollment
167 -- the pen contains this info also .
168 -- and nvl(pen.ENRT_OVRID_THRU_DT(+),c_effective_date-1)
169 -- < c_effective_date
170 and pen.prtt_enrt_rslt_id(+) = epe.prtt_enrt_rslt_id
171 and epe.comp_lvl_cd not in ('PLANFC','PLANIMP')
172 --
173 -- make sure enrt does not exist.
174 --
175 -- Bug No. 6454197 Added code to enforce limitation for enrollment at plantype
176 and (pen.prtt_enrt_rslt_id is null
177 or (pen.prtt_enrt_rslt_id is not null
178 and pen.enrt_cvg_thru_dt <> hr_api.g_eot))
179 /*
180 and epe.oipl_id = cop.oipl_id (+)
181 and c_effective_date between
182 cop.effective_start_date (+) and cop.effective_end_date (+)
183 */
184 /* Bug 10435034: On reprocessing a LE, if Participant is eligible for a new Plan in a new
185 Plan Type, do not consider the new plan type for multi row validation*/
186 and ( (ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL
187 and epe.pl_typ_id in (select distinct epe1.pl_typ_id
188 from ben_elig_per_elctbl_chc epe1
189 where epe1.per_in_ler_id = ben_manage_life_events.g_bckdt_per_in_ler_id)
190 )
191 or (ben_manage_life_events.g_bckdt_per_in_ler_id is null)
192 )
193 order by 1,2,3,4,5,6,7;
194 --
195 /*
196 cursor g_c1
197 (p_effective_date date
198 ,p_business_group_id number
199 ,p_person_id number
200 ,p_pgm_id number
201 )
202 is
203 select pen.pgm_id,
204 pen.ptip_id,
205 pen.pl_typ_id,
206 epe.plip_id,
207 pen.pl_id,
208 pen.oipl_id,
209 pen.enrt_cvg_strt_dt,
210 pen.enrt_cvg_thru_dt,
211 pen.prtt_enrt_rslt_id,
212 pen.RPLCS_SSPNDD_RSLT_ID,
213 pen.SSPNDD_FLAG,
214 'N' interim_flag,
215 pen.person_id,
216 0 Calc_interm,
217 nvl(pen.bnft_amt,0) bnft_amt,
218 pen.uom,
219 epe.elig_per_elctbl_chc_id,
220 epe.MUST_ENRL_ANTHR_PL_ID,
221 pln.dpnt_cvd_by_othr_apls_flag,
222 cop.opt_id
223 from ben_prtt_enrt_rslt_f pen,
224 ben_elig_per_elctbl_chc epe,
225 ben_oipl_f cop,
226 ben_pl_f pln
227 where pen.person_id = p_person_id
228 and pen.prtt_enrt_rslt_stat_cd is null
229 and nvl(pen.pgm_id,-999999) = nvl(p_pgm_id, -999999)
230 */
231 /*
232 Bug 5425 : Following 2 lines are commented as enrollment
233 which are created in future due to benmngle run in future
234 will not be picked up. Also added check on eef = eot
235 and p_effective_date between
236 pen.effective_start_date and pen.effective_end_date -1
237 */
238 /*
239 and pen.effective_end_date = hr_api.g_eot
240 and pen.enrt_cvg_thru_dt >= p_effective_date
241 and pen.effective_end_date >= pen.enrt_cvg_thru_dt
242 and (pen.ENRT_OVRID_THRU_DT is NULL
243 or pen.ENRT_OVRID_THRU_DT < p_effective_date)
244 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
245 and pen.per_in_ler_id = epe.per_in_ler_id (+)
246 and pen.oipl_id = cop.oipl_id (+)
247 and p_effective_date between
248 cop.effective_start_date (+) and cop.effective_end_date (+)
249 and pen.comp_lvl_cd not in ('PLANFC','PLANIMP')
250 and (epe.per_in_ler_id is null or
251 exists (select null
252 from ben_per_in_ler pil
253 where pil.per_in_ler_id = epe.per_in_ler_id
254 and pil.business_group_id = epe.business_group_id
255 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')))
256 and pen.pl_id = pln.pl_id
257 and p_effective_date between
258 pln.effective_start_date and pln.effective_end_date
259 UNION
260 --
261 -- Added union (below) to pick up choices which are not enrolled in
262 -- so that we can check minimums when not enrolled in anything.
263 -- jcarpent 13-jul-1999
264 -- added epe.pgm_id check
265 -- jcarpent 11-oct-1999
266 select distinct
267 epe.pgm_id,
268 epe.ptip_id,
269 epe.pl_typ_id,
270 epe.plip_id,
271 epe.pl_id,
272 epe.oipl_id,
273 pen.enrt_cvg_strt_dt,
274 pen.enrt_cvg_thru_dt,
275 pen.prtt_enrt_rslt_id,
276 pen.RPLCS_SSPNDD_RSLT_ID,
277 pen.SSPNDD_FLAG,
278 'N' interim_flag,
279 pil.person_id,
280 0 Calc_interm,
281 nvl(pen.bnft_amt,0) bnft_amt,
282 pen.uom,
283 epe.elig_per_elctbl_chc_id,
284 epe.MUST_ENRL_ANTHR_PL_ID,
285 'N' dpnt_cvd_by_othr_apls_flag,
286 cop.opt_id
287 from ben_prtt_enrt_rslt_f pen,
288 ben_elig_per_elctbl_chc epe,
289 ben_oipl_f cop,
290 ben_per_in_ler pil
291 where pil.person_id = p_person_id
292 and pil.per_in_ler_stat_cd = 'STRTD'
293 and epe.per_in_ler_id = pil.per_in_ler_id
294 and nvl(epe.pgm_id,-999999) = nvl(p_pgm_id, -999999)
295 and p_effective_date between
296 pen.effective_start_date(+) and pen.effective_end_date(+) -1
297 and pen.effective_end_date(+) >= pen.enrt_cvg_strt_dt(+)
298 and pen.enrt_cvg_thru_dt (+) >= p_effective_date
299 and pen.prtt_enrt_rslt_stat_cd(+) is null
300 and nvl(pen.ENRT_OVRID_THRU_DT(+),p_effective_date-1)
301 < p_effective_date -- nvl allows the null condition to be
302 -- outer joined
303 and pen.prtt_enrt_rslt_id(+) = epe.prtt_enrt_rslt_id
304 and epe.comp_lvl_cd not in ('PLANFC','PLANIMP')
305 and pen.prtt_enrt_rslt_id is null -- make sure enrt does not exist.
306 and epe.oipl_id = cop.oipl_id (+)
307 and p_effective_date between
308 cop.effective_start_date (+) and cop.effective_end_date (+)
309 order by 1,2,3,4,5,6,7;
310 --
311 */
312 --
313 type enrt_table is table of g_enrolled%rowtype index by binary_integer;
314 --
315 g_enrt_tbl enrt_table;
316 g_comp_obj_cnt integer := 0;
317 --
318 cursor g_c_pl (p_effective_date date
319 ,p_business_group_id ben_pl_f.business_group_id%type
320 ,p_pl_id ben_pl_f.pl_typ_id%type
321 ) is
322 select pl_id,
323 name,
324 pl_typ_id,
325 -999999999999999 ptip_id,
326 0 interim_flag,
327 mn_opts_rqd_num,
328 mn_cvg_rl, mn_cvg_rqd_amt,
329 mx_opts_alwd_num,
330 mx_cvg_alwd_amt, mx_cvg_rl,
331 mx_cvg_incr_alwd_amt,
332 mx_cvg_wcfn_amt,
333 mx_cvg_incr_wcf_alwd_amt,
334 0 tot_opt_enrld,
335 0.0 tot_cvg_amt,
336 0.0 prev_cvg_amt,
337 0.0 tot_cvg_amt_no_interim
338 from ben_pl_f
339 where pl_id = p_pl_id and
340 business_group_id = p_business_group_id and
341 p_effective_date between
342 effective_start_date and effective_end_date
343 ;
344 type pl_table is table of g_c_pl%rowtype index by binary_integer;
345 g_pl_tbl pl_table;
346 g_pl_cnt integer := 0;
347 cursor g_c_pl_typ (p_effective_date date,
348 p_business_group_id ben_pl_typ_f.business_group_id%type,
349 p_pl_typ_id ben_pl_typ_f.pl_typ_id%type) is
350 select pl_typ_id, name,
351 mx_enrl_alwd_num,
352 mn_enrl_rqd_num,
353 0 tot_pl_enrld,
354 0.0 tot_cvg_amt,
355 'N' dpnt_cvd_by_othr_apls_flag,
356 0.0 tot_cvg_amt_no_interim
357 from ben_pl_typ_f
358 where pl_typ_id = p_pl_typ_id
359 and business_group_id = p_business_group_id
360 and p_effective_date between
361 effective_start_date and effective_end_date
362 ;
363 type pl_typ_table is table of g_c_pl_typ%rowtype index by binary_integer;
364 g_pl_typ_tbl pl_typ_table;
365 g_pl_typ_cnt integer := 0;
366 cursor g_c_ptip(p_effective_date date,
367 p_business_group_id ben_ptip_f.business_group_id%type,
368 p_ptip_id ben_ptip_f.ptip_id%type
369 ) is
370 select ptip.ptip_id,
371 ptip.pgm_id,
372 ptip.pl_typ_id,
373 ptip.MX_ENRD_ALWD_OVRID_NUM,
374 ptip.no_mx_pl_typ_ovrid_flag,
375 ptip.MN_ENRD_RQD_OVRID_NUM,
376 ptip.no_mn_pl_typ_overid_flag,
377 ptip.MX_CVG_ALWD_AMT,
378 ptip.SBJ_TO_SPS_LF_INS_MX_FLAG,
379 ptip.SBJ_TO_DPNT_LF_INS_MX_FLAG,
380 ptip.USE_TO_SUM_EE_LF_INS_FLAG,
381 ptip.COORD_CVG_FOR_ALL_PLS_FLAG,
382 0 tot_pl_enrld,
383 0.0 tot_cvg_amt,
384 plt.name,
385 'N' dpnt_cvd_by_othr_apls_flag,
386 0.0 tot_cvg_amt_no_interim
387 from ben_ptip_f ptip, ben_pl_typ_f plt
388 where ptip.ptip_id = p_ptip_id
389 and ptip.pl_typ_id = plt.pl_typ_id
390 and ptip.business_group_id = p_business_group_id
391 and p_effective_date between
392 ptip.effective_start_date and ptip.effective_end_date
393 and p_effective_date between
394 plt.effective_start_date and plt.effective_end_date
395 ;
396 type ptip_table is table of g_c_ptip%rowtype index by binary_integer;
397 g_ptip_tbl ptip_table;
398 g_ptip_cnt integer := 0;
399 g_tot_ee_lf_ins_amt number := 0; -- integer := 0;
400 g_tot_sps_lf_ins_amt number := 0; -- integer := 0;
401 g_tot_dpnt_lf_ins_amt number := 0; -- integer := 0;
402
403 g_tot_ee_lf_ins_amt_no number := 0;
404 g_tot_sps_lf_ins_amt_no number := 0;
405 g_tot_dpnt_lf_ins_amt_no number := 0;
406
407 g_mx_dpnt_pct_prtt_lf integer := 0;
408 g_mx_sps_pct_prtt_lf integer := 0;
409 cursor g_c_pgm (p_effective_date date,
410 p_business_group_id ben_pgm_f.business_group_id%type,
411 p_pgm_id ben_pgm_f.pgm_id%type
412 ) is
413 select pgm_id, name,
414 MX_DPNT_PCT_PRTT_LF_AMT,
415 MX_SPS_PCT_PRTT_LF_AMT,
416 COORD_CVG_FOR_ALL_PLS_FLG
417 from ben_pgm_f
418 where pgm_id = p_pgm_id
419 and business_group_id = p_business_group_id
420 and p_effective_date between
421 effective_start_date and effective_end_date
422 ;
423 g_pgm_rec g_c_pgm%rowtype;
424 --
425 Procedure cache_enrt_info
426 (p_effective_date in date
427 ,p_business_group_id in number
428 ,p_person_id in number
429 ,p_pgm_id in number
430 ,p_assignment_id in number
431 ,p_include_erl in varchar2 default 'N'
432 );
433 --
434 --
435 -- ---------------------------------------------------------------------------
436 -- |---------------------< return_legislation_code >-------------------------|
437 -- ---------------------------------------------------------------------------
438 --
439 -- Description:
440 -- Return the legislation code for a specific primary key value
441 --
442 -- Prerequisites:
443 -- The primary key identified by p_prtt_enrt_rslt_id already exists.
444 --
445 -- In Arguments:
446 -- p_prtt_enrt_rslt_id
447 --
448 -- Post Success:
449 -- If the value is found this function will return the values business
450 -- group legislation code.
451 --
452 -- Post Failure:
453 -- An error is raised if the value does not exist.
454 --
455 -- Access Status:
456 -- Internal Development Use Only.
457 --
458 function return_legislation_code
459 (p_prtt_enrt_rslt_id in number) return varchar2;
460 --
461 --
462 -- ----------------------------------------------------------------------------
463 -- |---------------------------< insert_validate >----------------------------|
464 -- ----------------------------------------------------------------------------
465 -- {Start Of Comments}
466 --
467 -- Description:
468 -- This procedure controls the execution of all insert business rules
469 -- validation.
470 --
471 -- Prerequisites:
472 -- This private procedure is called from ins procedure.
473 --
474 -- In Parameters:
475 -- A Pl/Sql record structre.
476 --
477 -- Post Success:
478 -- Processing continues.
479 --
480 -- Post Failure:
481 -- If a business rules fails the error will not be handled by this procedure
482 -- unless explicity coded.
483 --
484 -- Developer Implementation Notes:
485 -- For insert, your business rules should be executed from this procedure and
486 -- should ideally (unless really necessary) just be straight procedure or
487 -- function calls. Try and avoid using conditional branching logic.
488 --
489 -- Access Status:
490 -- Internal Row Handler Use Only.
491 --
492 -- {End Of Comments}
493 -- ----------------------------------------------------------------------------
494 Procedure insert_validate
495 (p_rec in ben_pen_shd.g_rec_type,
496 p_effective_date in date,
497 p_datetrack_mode in varchar2,
498 p_validation_start_date in date,
499 p_validation_end_date in date);
500 --
501 -- ----------------------------------------------------------------------------
502 -- |---------------------------< update_validate >----------------------------|
503 -- ----------------------------------------------------------------------------
504 -- {Start Of Comments}
505 --
506 -- Description:
507 -- This procedure controls the execution of all update business rules
508 -- validation.
509 --
510 -- Prerequisites:
511 -- This private procedure is called from upd procedure.
512 --
513 -- In Parameters:
514 -- A Pl/Sql record structre.
515 --
516 -- Post Success:
517 -- Processing continues.
518 --
519 -- Post Failure:
520 -- If a business rules fails the error will not be handled by this procedure
521 -- unless explicity coded.
522 --
523 -- Developer Implementation Notes:
524 -- For update, your business rules should be executed from this procedure and
525 -- should ideally (unless really necessary) just be straight procedure or
526 -- function calls. Try and avoid using conditional branching logic.
527 --
528 -- Access Status:
529 -- Internal Row Handler Use Only.
530 --
531 -- {End Of Comments}
532 -- ----------------------------------------------------------------------------
533 Procedure update_validate
534 (p_rec in ben_pen_shd.g_rec_type,
535 p_effective_date in date,
536 p_datetrack_mode in varchar2,
537 p_validation_start_date in date,
538 p_validation_end_date in date);
539 --
540 -- ----------------------------------------------------------------------------
541 -- |---------------------------< delete_validate >----------------------------|
542 -- ----------------------------------------------------------------------------
543 -- {Start Of Comments}
544 --
545 -- Description:
546 -- This procedure controls the execution of all delete business rules
547 -- validation.
548 --
549 -- Prerequisites:
550 -- This private procedure is called from del procedure.
551 --
552 -- In Parameters:
553 -- A Pl/Sql record structre.
554 --
555 -- Post Success:
556 -- Processing continues.
557 --
558 -- Post Failure:
559 -- If a business rules fails the error will not be handled by this procedure
560 -- unless explicity coded.
561 --
562 -- Developer Implementation Notes:
563 -- For delete, your business rules should be executed from this procedure and
564 -- should ideally (unless really necessary) just be straight procedure or
565 -- function calls. Try and avoid using conditional branching logic.
566 --
567 -- Access Status:
568 -- Internal Row Handler Use Only.
569 --
570 -- {End Of Comments}
571 -- ----------------------------------------------------------------------------
572 Procedure delete_validate
573 (p_rec in ben_pen_shd.g_rec_type,
574 p_effective_date in date,
575 p_datetrack_mode in varchar2,
576 p_validation_start_date in date,
577 p_validation_end_date in date);
578 --
579 --
580 procedure multi_rows_edit
581 (p_person_id in number
582 ,p_effective_date in date
583 ,p_business_group_id in number
584 ,p_pgm_id in number
585 ,p_include_erl in varchar2 default 'N'
586 );
587 --
588 Procedure manage_per_type_usages
589 (p_person_id in number
590 ,p_business_group_id in number
591 ,p_effective_date in date
592 );
593 --
594 end ben_pen_bus;