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