[Home] [Help]
1175: l_enrld_or_cvrd boolean := false;
1176: --
1177: cursor c_chk_enrld is
1178: select null
1179: from ben_prtt_enrt_rslt_f pen
1180: ,ben_pl_f pln
1181: ,ben_oipl_f cop
1182: ,ben_opt_f opt
1183: where pen.person_id = p_person_id
1221: and opt.effective_end_date (+);
1222: --
1223: cursor c_chk_cvrd is
1224: select null
1225: from ben_prtt_enrt_rslt_f pen
1226: ,ben_elig_cvrd_dpnt_f pdp
1227: where pdp.dpnt_person_id = p_person_id
1228: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1229: and pen.prtt_enrt_rslt_stat_cd is null
1296: ,p_business_group_id in number) return date is
1297: --
1298: l_proc varchar2(80) := g_package||'.get_max_enrt_cvg_thru_dt';
1299: l_exists varchar2(1);
1300: l_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
1301: l_enrt_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
1302: l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
1303: --
1304: cursor c_get_enrt_cvg_thru_dt is
1297: --
1298: l_proc varchar2(80) := g_package||'.get_max_enrt_cvg_thru_dt';
1299: l_exists varchar2(1);
1300: l_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
1301: l_enrt_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
1302: l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
1303: --
1304: cursor c_get_enrt_cvg_thru_dt is
1305: select max(pen.enrt_cvg_thru_dt)
1302: l_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%type;
1303: --
1304: cursor c_get_enrt_cvg_thru_dt is
1305: select max(pen.enrt_cvg_thru_dt)
1306: from ben_prtt_enrt_rslt_f pen
1307: where pen.person_id = p_person_id
1308: and pen.prtt_enrt_rslt_stat_cd is null
1309: and pen.sspndd_flag = 'N'
1310: and pen.pgm_id = nvl(p_pgm_id,pen.pgm_id)
1322: --
1323: cursor c_get_dpnt_cvg_thru_dt is
1324: select max(pdp.cvg_thru_dt)
1325: from ben_elig_cvrd_dpnt_f pdp
1326: ,ben_prtt_enrt_rslt_f pen
1327: where pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1328: and pen.prtt_enrt_rslt_stat_cd is null
1329: and pdp.dpnt_person_id = p_person_id
1330: and pen.pgm_id = p_pgm_id
1774: l_ptip_id ben_ptip_f.ptip_id%type;
1775: l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
1776: l_cqb_ptip_id ben_ptip_f.ptip_id%type;
1777: l_poe_lvl_cd ben_pgm_f.poe_lvl_cd%type;
1778: l_enrld_person_id ben_prtt_enrt_rslt_f.person_id%type;
1779: l_proc varchar2(80) := g_package||'.update_dpnt_cobra_info';
1780: l_arra_event boolean;
1781: --
1782: cursor c_get_cbr_quald_bnf
1799: --
1800: cursor c_get_pgm_id
1801: is
1802: select pen.*
1803: from ben_prtt_enrt_rslt_f pen
1804: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1805: and pen.prtt_enrt_rslt_stat_cd is null
1806: and p_effective_date
1807: between pen.effective_start_date and pen.effective_end_date
3539: pen.pl_typ_id,
3540: pen.person_id,
3541: pen.effective_start_date,
3542: pen.effective_end_date
3543: from ben_prtt_enrt_rslt_f pen,
3544: ben_oipl_f opt
3545: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3546: and opt.oipl_id(+)=pen.oipl_id
3547: and pen.prtt_enrt_rslt_stat_cd is null
3604: )
3605: is
3606: select distinct
3607: yp.start_date,yp.end_date
3608: from ben_prtt_enrt_rslt_f pen,
3609: ben_popl_yr_perd pyp,
3610: ben_yr_perd yp
3611: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3612: and c_effective_date <= pen.effective_end_date
3625: ,c_plan_year_strt_dt in date) is
3626: SELECT prv.rt_strt_dt
3627: ,prv.rt_end_dt
3628: ,prv.ann_rt_val
3629: FROM ben_prtt_enrt_rslt_f pen
3630: ,ben_prtt_rt_val prv
3631: WHERE pen.person_id = c_person_id
3632: AND pen.pgm_id = c_pgm_id
3633: AND pen.business_group_id = c_business_group_id
4102: ,prv.rt_end_dt
4103: ,prv.ann_rt_val
4104: ,pen.prtt_enrt_rslt_id
4105: ,prv.mlt_cd
4106: FROM ben_prtt_enrt_rslt_f pen
4107: ,ben_prtt_rt_val prv
4108: ,ben_acty_base_rt_f abr
4109: WHERE pen.person_id = c_person_id
4110: AND pen.pgm_id = c_pgm_id
4142: ,prv.ann_rt_val
4143: ,pen.prtt_enrt_rslt_id
4144: ,prv.mlt_cd
4145: ,abr.name
4146: FROM ben_prtt_enrt_rslt_f pen
4147: ,ben_prtt_rt_val prv
4148: ,ben_acty_base_rt_f abr
4149: WHERE pen.person_id = c_person_id
4150: AND pen.pgm_id = c_pgm_id
4463: SELECT pen.pgm_id
4464: ,pen.pl_typ_id
4465: ,abr.element_type_id
4466: ,abr.input_value_id
4467: FROM ben_prtt_enrt_rslt_f pen
4468: ,ben_prtt_rt_val prv
4469: ,ben_acty_base_rt_f abr
4470: WHERE pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
4471: AND c_effective_date BETWEEN pen.enrt_cvg_strt_dt
4614: ,prv.rt_end_dt
4615: ,prv.ann_rt_val
4616: ,pen.prtt_enrt_rslt_id
4617: ,prv.mlt_cd
4618: FROM ben_prtt_enrt_rslt_f pen
4619: ,ben_prtt_rt_val prv
4620: ,ben_acty_base_rt_f abr
4621: WHERE pen.person_id = c_person_id
4622: AND pen.pgm_id = c_pgm_id
4650: ,prv.rt_end_dt
4651: ,prv.ann_rt_val
4652: ,pen.prtt_enrt_rslt_id
4653: ,prv.mlt_cd
4654: FROM ben_prtt_enrt_rslt_f pen
4655: ,ben_prtt_rt_val prv
4656: ,ben_acty_base_rt_f abr
4657: WHERE pen.person_id = c_person_id
4658: AND pen.pgm_id = c_pgm_id
4678: )
4679: is
4680: select distinct
4681: yp.start_date,yp.end_date
4682: from ben_prtt_enrt_rslt_f pen,
4683: ben_popl_yr_perd pyp,
4684: ben_yr_perd yp
4685: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
4686: and pen.prtt_enrt_rslt_stat_cd is null