[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oipl.auto_enrt_flag,
oipl.auto_enrt_mthd_rl,
oipl.crnt_enrt_prclds_chg_flag,
oipl.dflt_flag,
oipl.enrt_cd,
oipl.enrt_rl,
oipl.ler_chg_oipl_enrt_id,
oipl.stl_elig_cant_chg_flag
FROM ben_ler_chg_oipl_enrt_f oipl
WHERE oipl.oipl_id = p_oipl_id
AND oipl.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date;
SELECT pgm.auto_enrt_mthd_rl,
pgm.crnt_enrt_prclds_chg_flag,
pgm.enrt_cd,
pgm.enrt_mthd_cd,
pgm.enrt_rl,
pgm.ler_chg_pgm_enrt_id,
pgm.stl_elig_cant_chg_flag
FROM ben_ler_chg_pgm_enrt_f pgm
WHERE pgm.pgm_id = p_pgm_id
AND pgm.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN pgm.effective_start_date
AND pgm.effective_end_date;
SELECT ptip.crnt_enrt_prclds_chg_flag,
ptip.enrt_cd,
ptip.enrt_mthd_cd,
ptip.enrt_rl,
ptip.ler_chg_ptip_enrt_id,
ptip.stl_elig_cant_chg_flag,
ptip.tco_chg_enrt_cd
FROM ben_ler_chg_ptip_enrt_f ptip
WHERE ptip.ptip_id = p_ptip_id
AND ptip.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN ptip.effective_start_date
AND ptip.effective_end_date;
SELECT plip.auto_enrt_mthd_rl,
plip.crnt_enrt_prclds_chg_flag,
plip.dflt_flag,
plip.enrt_cd,
plip.enrt_mthd_cd,
plip.enrt_rl,
plip.ler_chg_plip_enrt_id,
plip.stl_elig_cant_chg_flag,
plip.tco_chg_enrt_cd
FROM ben_ler_chg_plip_enrt_f plip
WHERE plip.plip_id = p_plip_id
AND plip.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN plip.effective_start_date
AND plip.effective_end_date;
SELECT pl_nip.auto_enrt_mthd_rl,
pl_nip.crnt_enrt_prclds_chg_flag,
pl_nip.dflt_flag,
pl_nip.enrt_cd,
pl_nip.enrt_mthd_cd,
pl_nip.enrt_rl,
pl_nip.ler_chg_pl_nip_enrt_id,
pl_nip.stl_elig_cant_chg_flag,
pl_nip.tco_chg_enrt_cd
FROM ben_ler_chg_pl_nip_enrt_f pl_nip
WHERE pl_nip.pl_id = p_pl_id
AND pl_nip.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN pl_nip.effective_start_date
AND pl_nip.effective_end_date;
select /*+ c_getpenesd ben_enrolment_requirements1 */
rslt.effective_start_date,
rslt.enrt_cvg_thru_dt
from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
where rslt.person_id = c_per_id
and ler.ler_id=rslt.ler_id
and rslt.prtt_enrt_rslt_stat_cd is null
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
and rslt.business_group_id = c_bgp_id;
SELECT MAX(pil.procd_dt)
FROM ben_per_in_ler pil
-- CWB changes
,ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
and l_lf_evt_ocrd_dt between
ler.effective_start_date and ler.effective_end_date
AND pil.business_group_id = p_business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('BCKDT', 'VOIDD')
AND pil.procd_dt IS NOT NULL;
SELECT MAX(pil.bckt_dt)
FROM ben_per_in_ler pil
-- CWB changes
,ben_ler_f ler
,ben_ptnl_ler_for_per plr
WHERE pil.person_id = p_person_id
AND pil.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
and l_lf_evt_ocrd_dt between
ler.effective_start_date and ler.effective_end_date
AND pil.business_group_id = p_business_group_id
AND pil.ler_id = p_ler_id
AND pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
AND pil.bckt_dt IS NOT NULL
and pil.per_in_ler_stat_cd = 'BCKDT' -- 3063867
and pil.ptnl_ler_for_per_id = plr.ptnl_ler_for_per_id --3248770
and plr.ptnl_ler_for_per_stat_cd <> 'VOIDD' ;
select max(rslt.effective_start_date)
from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
where rslt.person_id = p_person_id
and ler.ler_id=rslt.ler_id
-- and rslt.prtt_enrt_rslt_stat_cd NOT IN ('BCKDT', 'VOIDD')
and rslt.prtt_enrt_rslt_stat_cd is null
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
and rslt.business_group_id = p_business_group_id
and rslt.enrt_cvg_thru_dt = hr_api.g_eot; -- Bug 4388226 - End-dated suspended enrl shudn't be picked up.