The following lines contain the word 'select', 'insert', 'update' or 'delete':
14-Jun-2002 pabodla 115.7 Do not select the contingent worker
assignment when assignment data is
fetched.
13-Dec-2002 kmahendr 115.8 Nocopy Changes
10-feb-2005 mmudigon 115.9 Bug 4157759. Changes to cursor c1 in get_pil
17-feb-2005 bmanyam 115.10 Bug 4187137. Changed get_pil to query always.
18-feb-2005 bmanyam 115.11 Bug 4187137. Changed get_pil to query always.
22-Feb-2008 rtagarra 115.12 Bug 6840074
15-Nov-2010 stee 115.14 Bug 9401100. Added ctfn_rqd_flag to
c1.
------------------------------------------------------------------------------
*/
g_package varchar2(30) := 'ben_global_enrt.';
select epe.per_in_ler_id
,epe.pil_elctbl_chc_popl_id
,epe.prtt_enrt_rslt_id
,epe.pgm_id
,epe.pl_id
,epe.pl_typ_id
,epe.plip_id
,epe.ptip_id
,epe.oipl_id
,epe.business_group_id
,epe.object_version_number
,epe.comp_lvl_cd
,epe.crntly_enrd_flag
,epe.alws_dpnt_dsgn_flag
,epe.ctfn_rqd_flag -- 9401100
,epe.dpnt_cvg_strt_dt_cd
,epe.dpnt_cvg_strt_dt_rl
,epe.enrt_cvg_strt_dt
,epe.erlst_deenrt_dt
,epe.enrt_cvg_strt_dt_cd
,epe.enrt_cvg_strt_dt_rl
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
select pel.per_in_ler_id
,pel.pgm_id
,pel.pl_id
,pel.lee_rsn_id
,pel.enrt_perd_id
,pel.uom
,pel.acty_ref_perd_cd
from ben_pil_elctbl_chc_popl pel
where pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id ;
select pil.person_id
,pil.ler_id
,pil.lf_evt_ocrd_dt
,ler.typ_cd
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and ler.ler_id(+) = pil.ler_id
and pil.lf_evt_ocrd_dt between ler.effective_start_date(+)
and ler.effective_end_date(+) ;
select enb.ordr_num
,enb.val
,enb.bnft_typ_cd
,enb.cvg_mlt_cd
,enb.nnmntry_uom
,enb.object_version_number
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id ;
select asg.payroll_id
from per_all_assignments_f asg,
per_assignment_status_types ast
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.active_flag = 'Y'
and ast.primary_flag = 'P'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
order by decode(asg.assignment_type, 'E', 1, 'B', 2, 3);
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id and
pen.pl_id=p_pl_id
and nvl(pen.pgm_id,-1)=nvl(p_pgm_id,-1)
and nvl(pen.oipl_id,-1)=nvl(p_oipl_id,-1)
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date
between pen.effective_start_date
and pen.effective_end_date;