[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
to electable choice selection cursors
(c_instance) - to enable rates with
post enrollment rule calculated properly
115.11 12-Apr-04 kmahendr - Added three columns to cache.
-----------------------------------------------------------------------------
*/
--
-- Globals.
--
g_package varchar2(50) := 'ben_epe_cache.';
SELECT epe.elig_per_elctbl_chc_id,
epe.business_group_id,
pil.person_id,
pil.ler_id,
pil.LF_EVT_OCRD_DT,
pil.per_in_ler_stat_cd,
epe.per_in_ler_id,
epe.pgm_id,
epe.pl_typ_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.oiplip_id,
null opt_id,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt,
epe.prtt_enrt_rslt_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.enrt_cvg_strt_dt_rl,
epe.yr_perd_id,
epe.comp_lvl_cd,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.dflt_flag,
epe.ctfn_rqd_flag,
enb.enrt_bnft_id,
enb.val,
pel.acty_ref_perd_cd,
epe.elctbl_flag,
epe.object_version_number,
epe.alws_dpnt_dsgn_flag,
epe.dpnt_dsgn_cd,
epe.ler_chg_dpnt_cvg_cd,
epe.dpnt_cvg_strt_dt_cd,
epe.dpnt_cvg_strt_dt_rl,
epe.in_pndg_wkflow_flag,
epe.bnft_prvdr_pool_id,
epe.elig_flag,
epe.inelig_rsn_cd,
epe.fonm_cvg_strt_dt
/* removed the following as they are explicitly nulled later
9i compliance.,
null,
null,
null,
null,
null,
null,
null */
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_enrt_bnft enb,
ben_per_in_ler pil
WHERE epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
AND epe.per_in_ler_id = pil.per_in_ler_id
AND epe.per_in_ler_id = pel.per_in_ler_id
AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
--
-- Removed for EFC. This cache is intended to support all electable
-- choice information for all types of life event. It should not be
-- restricted to started life event electable choices only
--
/*
AND pil.per_in_ler_stat_cd = 'STRTD'
*/
-- added for unrestricted enhancement
and pil.per_in_ler_id = c_per_in_ler_id
AND pil.person_id = c_person_id
order by epe.PTIP_ORDR_NUM, PLIP_ORDR_NUM,
decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
PL_ORDR_NUM,
decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
g_pilepe_instance.delete;
SELECT epe.elig_per_elctbl_chc_id,
epe.business_group_id,
pil.person_id,
pil.ler_id,
pil.LF_EVT_OCRD_DT,
pil.per_in_ler_stat_cd,
epe.per_in_ler_id,
epe.pgm_id,
epe.pl_typ_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.oiplip_id,
null opt_id,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt,
epe.prtt_enrt_rslt_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.enrt_cvg_strt_dt_rl,
epe.yr_perd_id,
epe.comp_lvl_cd,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.dflt_flag,
epe.ctfn_rqd_flag,
enb.enrt_bnft_id,
enb.val,
pel.acty_ref_perd_cd,
/* removed the following as they are explicitly nulled later
9i compliance.,
null,
null,
null,
null,
null,
null,
null,
*/
epe.elctbl_flag,
epe.object_version_number,
epe.alws_dpnt_dsgn_flag,
epe.dpnt_dsgn_cd,
epe.ler_chg_dpnt_cvg_cd,
epe.dpnt_cvg_strt_dt_cd,
epe.dpnt_cvg_strt_dt_rl,
epe.in_pndg_wkflow_flag,
epe.bnft_prvdr_pool_id,
epe.elig_flag,
epe.inelig_rsn_cd,
epe.fonm_cvg_strt_dt
FROM ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
WHERE enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND epe.per_in_ler_id = pil.per_in_ler_id
AND epe.per_in_ler_id = pel.per_in_ler_id
AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pil.per_in_ler_id = c_per_in_ler_id
order by epe.PTIP_ORDR_NUM, PLIP_ORDR_NUM,
decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
PL_ORDR_NUM,
decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
g_enbepe_instance.delete;
SELECT epe.elig_per_elctbl_chc_id,
epe.business_group_id,
pil.person_id,
pil.ler_id,
pil.LF_EVT_OCRD_DT,
pil.per_in_ler_stat_cd,
epe.per_in_ler_id,
epe.pgm_id,
epe.pl_typ_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.oiplip_id,
null opt_id,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt,
epe.prtt_enrt_rslt_id,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.enrt_cvg_strt_dt_rl,
epe.yr_perd_id,
epe.comp_lvl_cd,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.dflt_flag,
epe.ctfn_rqd_flag,
enb.enrt_bnft_id,
enb.val,
pel.acty_ref_perd_cd,
epe.elctbl_flag,
epe.object_version_number,
epe.alws_dpnt_dsgn_flag,
epe.dpnt_dsgn_cd,
epe.ler_chg_dpnt_cvg_cd,
epe.dpnt_cvg_strt_dt_cd,
epe.dpnt_cvg_strt_dt_rl,
epe.in_pndg_wkflow_flag,
epe.bnft_prvdr_pool_id,
epe.elig_flag,
epe.inelig_rsn_cd,
epe.fonm_cvg_strt_dt
FROM ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_enrt_bnft enb,
ben_per_in_ler pil
WHERE epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
AND epe.per_in_ler_id = pil.per_in_ler_id
AND epe.per_in_ler_id = pel.per_in_ler_id
AND epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pil.per_in_ler_id = c_per_in_ler_id
order by epe.PTIP_ORDR_NUM, PLIP_ORDR_NUM,
decode(PL_ORDR_NUM, null, OIPL_ORDR_NUM, PL_ORDR_NUM),
PL_ORDR_NUM,
decode(PL_ORDR_NUM, null, null, OIPL_ORDR_NUM);
g_epe_instance.delete;
g_epe_instance.delete;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.oipl_id = c_oipl_id
AND epe.pgm_id = c_pgm_id
AND epe.per_in_ler_id = c_per_in_ler_id;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.oipl_id = c_oipl_id
AND epe.pgm_id IS NULL
AND epe.per_in_ler_id = c_per_in_ler_id;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.pl_id = c_pl_id
AND epe.oipl_id IS NULL
AND epe.pgm_id = c_pgm_id
AND epe.per_in_ler_id = c_per_in_ler_id;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.pl_id = c_pl_id
AND epe.oipl_id IS NULL
AND epe.pgm_id IS NULL
AND epe.per_in_ler_id = c_per_in_ler_id;
g_pilepe_instance.delete;
g_enbepe_instance.delete;
g_epe_instance.delete;